• 技术文章 >数据库 >mysql教程

    MySQL如何才能提高响应速度

    醉折花枝作酒筹醉折花枝作酒筹2021-07-19 13:57:34转载156
    MySQL自身的局限性,很多站点都采用了MySQL+Memcached的经典架构,甚至一些网站放弃MySQL而采用NoSQL产品。不可否认,在做一些简单查询(尤其是PK查询)的时候,很多NoSQL产品比MySQL要快很多。

    一、概述

    二、应用场景

    MySQL自身的局限性,很多站点都采用了MySQL+Memcached的经典架构,甚至一些网站放弃MySQL而采用NoSQL产品,比如Redis/MongoDB等。不可否认,在做一些简单查询(尤其是PK查询)的时候,很多NoSQL产品比MySQL要快很多,而且前台网站上的80%以上查询都是简洁的查询业务。

    MySQL通过HandlerSocket插件提供了API访问接口,在我们的基准测试中,普通的R510服务器单实例Percona/XtraDB达到了72W+QPS(纯读),如果采用更强劲的CPU增加更多的网卡,理论上可以获得更高的性能。而同等条件下Memcached仅有40W+QPS(纯读),并且在R510上Memcached单实例已经无法提升性能,因为Memcached对内存的一把大锁限制了它的并发能力。

    Innodb引擎、按主键、unique key或索引搜索(也就是说它的SQL的where条件必须是这些);支持limit 语句、IN、INSERT/UPDATE/DELETE。

    HandlerSocket和NoSQL这两者主要的使用场景不同。HandlerSocket主要是用于改善MySQL,优化表格的增删改查以及表格的结构修改等操作,支持密集型CPU操作;而NoSQL作为缓存的功能,支持密集型I/O的操作。

    因此,当有需要的时候,可以结合这两者共同工作。

    三、原理

    HandlerSocket是MySQL的一个插件,集成在mysqld进程中;NoSQL无法实现的复杂查询等操作,仍然使用MySQL自身的关系型数据库实现。在运维层面,原来广泛使用的MySQL主从复制等经验继续发挥作用,相比其他NoSQL产品,数据安全更有保障,原理如图:

    可以看出,HandlerSocket绕过MySQL的SQL解析层(SQL Layer),直接访问MySQL存储层。另外,HandlerSocket采用epoll和worker thread/thread pooling网络架构,性能更高。

    MySQL的架构是“数据库管理”和“数据管理”分离,即MySQL Server+Storage Engine的模式。MySQL Server是直接与Client交互的一层,它负责管理连接线程,解析SQL生成执行计划,管理和实现视图、触发器、存储过程等这些与具体数据操作管理无关的事情,通过调用Handler API让存储引擎去操作具体的数据。Storage Engine通过继承实现Handler API的函数,负责直接与数据交互,数据存取实现(必须实现),事务实现(可选),索引实现(可选),数据缓存实现(可选)。

    HandlerSocket是在MySQL的内部组件,以MySQL Daemon Plugin的形式提供类似NoSQL的网络服务,它并不直接处理数据,只是侦听配置好的某个端口方式,接收采用NoSQL/API的通讯协议,然后通过MySQL内部的Handler API来调用存储引擎(例如InnoDB)处理数据。理论上,HanderSocket可以处理各种MySQL存储引擎,但是用MyISAM时,会出现插入的数据查不出来,这个实际上是构造行时第一字节没有初始化为0xff,初始化以后就没有问题,MyISAM也一样可以支持,但是为了更好地利用内存,用HandlerSocket都会搭配InnoDB存储引擎一起使用。

    从上图可以看出,HandlerSocket作为mysql客户端和mysql的中间层,取代mysql原生的部分数据、表格处理工作,采用多线程的方式,区分DDL和DML进行操作。这样的目的是保证在复杂处理的情况下,能够高效的进行处理。

    因为HandlerSocket是以MySQL Daemon Plugin形式存在,所以在应用中,可把MySQL当NoSQL使用。它最大的功能是实现了与存储引擎交互,比如InnoDB,而这不需要任何SQL方面的初始化开销。访问MySQL的TABLE时,当然也是需要open/close table的,但是它并不是每次都去open/close table,因为它会将以前访问过的table cache保存下来以重复使用,而opening/closing tables是最耗资源的,而且很容易引起互斥量的争夺,这样一来,对于提高性能非常有效。在流量变小时,HandlerSocket会close tables,所以它一般不会阻塞DDL。

    HandlerSocket与MySQL+Memcached的区别在哪呢?对比图1-2和图1-3,可从中看出其不同点,图1-3展示了典型的MySQL+Memecached的应用架构。因为Memcached的get操作比MySQL的内存中或磁盘上的主键查询要快很多,所以Memcached用于缓存数据库记录。若是HandlerSocket的查询速度和相应时间能与Memcached媲美,我们就可以考虑替换Memcached缓存记录的架构层。

    四、优势劣势

    HandlerSocket的优势和特点

    1) 支持多种查询模式

    HandlerSocket目前支持索引查询(主键索引和非主键的普通索引均可),索引范围扫描,LIMIT子句,也即支持增加、删除、修改、查询完整功能,但还不支持无法使用任何索引的操作。另外支持execute_multi() 一次网络传输多个Query请求,节省网络传输时间。

    2) 处理大量并发连接

    HandlerSocket的连接是轻量级的,因为HandlerSocket采用epoll() 和worker-thread/thread-pooling架构,而MySQL内部线程的数量是有限的(可以由my.cnf中的handlersocket_threads/handlersocket_threads_wr参数控制),所以即使建立上千万的网络连接到HandlerSocket,也不会消耗很多内存,它的稳定性不会受到任何影响(消耗太多的内存,会造成巨大的互斥竞争等其他问题,如bug#26590,bug#33948,bug#49169)。

    3) 优秀的性能

    HandlerSocket的性能见文章HandlerSocket的性能测试报告描述,相对于其它NoSQL产品,性能表现一点也不逊色,它不仅没有调用与SQL相关的函数,还优化了网络/并发相关的问题:

    4) 无重复缓存

    当使用Memcached缓存MySQL/InnoDB记录时,在Memcached和InnoDB Buffer Pool中均缓存了这些记录,因此效率非常低(实际上有两份数据,Memcached本身可能还需要做HA支持),而采用 HandlerSocket插件, 它直接访问 InnoDB 存储引擎,记录缓存在InnoDB Buffer Pool,于是其它SQL语句还可以重复使用缓存的数据。

    5) 无数据不一致的现象

    由于数据只存储在一个地方(InnoDB存储引擎缓存区内),不像使用Memcached时,需要在Memcached和MySQL之间维护数据一致性。

    6) 崩溃安全

    后端存储是InnoDB引擎,支持事务的ACID特性,能确保事务的安全性,即使设置innodb_flush_log_at_trx_commit=2,若数据库服务器崩溃时,也只会丢掉<= 1s的数据。

    7) SQL/NOSQL并存

    在许多情况下,我们仍然希望使用SQL(例如复杂的报表查询),而大多数NoSQL产品都不支持SQL接口,HandlerSocket仅仅是一个 MySQL 插件,我们依然可以通过MySQL客户端发送SQL语句,但当需要高吞吐量和快速响应时,则使用 HandlerSocket。

    8) 继承MySQL的功能

    因为HandlerSocket运行于MySQL,因此所有MySQL的功能依然被支持,例如:SQL、在线备份、复制、HA、监控等等。

    9) 不需要修改/重建MySQL

    因为HandlerSocket是一个插件并且开源,所以它支持从任何MySQL源码、甚至是第三方版本(例如Percona)构建,而无需对MySQL做出任何修改。

    10) 独立于存储引擎

    虽然我们只测试了MySQL-EnterpriseInnoDB和Percona XtraDB插件,但HandlerSocket理论上可以和任何存储引擎交互。MyISAM通过简单的修改也是可以被支持的,但是从数据缓存而利用内存的角度看这个意义不大。

    HandlerSocket的缺陷和注意事项

    1) 协议不兼容

    HandlerSocket API与Memcached API并不兼容,尽管它很容易使用,但仍然需要一点学习来学会如何与HandlerSocket交互。不过我们可以通过重载Memecached函数来翻译到HandlerSocket API。

    2) 没有安全功能

    与其它NoSQL数据库类似,HandlerSocket不支持安全功能,HandlerSocket的工作线程以系统用户权限运行,因此应用程序可以通过HandlerSocket协议访问所有的表对象,但是可以通过简单的修改协议,在my.cnf中增加一个配置项为密码,连接时通过这个配置的密码验证,当然也可以通过网络防火墙来过滤数据包。

    3) 对于磁盘IO密集的场景没有优势

    对于IO密集的应用场景,数据库每秒无法执行数千次查询,通常只有1-10%的CPU利用率,在这种情况下,SQL解析不会成为性能瓶颈,因此使用HandlerSocket没有什么优势,应当只在数据完全装载到内存的服务器上使用 HandlerSocket。但是对于PCI-E SSD(例如Fusion-IO)设备,每秒可以提供4w+ IOPS,并且IO设备本身消耗CPU比较大,使用HandlerSocket依然具有优势。

    五、安装

    注意:书上的安装方式已经过时了,版本也较低,不建议使用,建议使用官方的文档进行安装,Github地址:https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL

    安装文档:https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/installation.en.txt

    下载源码:可以通过github直接git clone或者是下载也行

    安装步骤:

    1. build Handlersocket

    ./autogen.sh
    ./configure --with-mysql-source=/work/mysql-5.1.50 --with-mysql-bindir=/work/mysql-5.1.50-linux-x86_64-glibc23/bin  --with-mysql-plugindir=/work/mysql-5.1.50-linux-x86_64-glibc23/lib/plugin

    注意:

    2. 编译

    make && make install

    3. 配置

    编译之后HandleSocket还不能使用,还需要在MySQL配置文件(my.cnf)中增加以下配置:

    [mysqld]
    # 绑定读请求端口
    loose_handlersocket_port = 9998
    # 绑定写请求端口
    loose_handlersocket_port_wr = 9999
    # 读请求线程数
    loose_handlersocket_threads = 16
    # 写请求线程数
    loose_handlersocket_threads_wr = 16
    # 设置最大接收连接数
    open_files_limit = 65535

    这里增加的这些主要是针对HandleSocket的配置,它有两个端口,9998读数据,9999写数据,但通过9998读的效率更高,这里设置处理读写的线程数均为16个,另外为了处理更多并发连接,设置能打开的文件描述符个数为65535

    此外,InnoDB的innodb_buffer_pool_size或MyISAM的key_buffy_size配置选项关系到缓存索引,所以尽可能设置大一些,这样才能发挥HandleSocket的潜力。

    4. 激活HandleSocket

    登陆MySQL执行

    mysql> install plugin handlersocket soname 'handlersocket.so';

    可以通过show processlist或show plugins来看到HandleSocket

    最后需要安装PHP的扩展包PHP HandlerSocket

    安装文档:https://github.com/tz-lom/HSPHP

    PHP用法:

    Select

    <?php 
    $c = new \HSPHP\ReadSocket();
    $c->connect();
    $id = $c->getIndexId('data_base_name', 'table_name', '', 'id,name,some,thing,more');
    $c->select($id, '=', array(42)); // SELECT WITH PRIMARY KEY
    $response = $c->readResponse();
    
    //SELECT with IN statement
    $c = new \HSPHP\ReadSocket();
    $c->connect();
    $id = $c->getIndexId('data_base_name', 'table_name', '', 'id,name,some,thing,more');
    $c->select($id, '=', array(0), 0, 0, array(1,42,3));
    $response = $c->readResponse();

    Update

    <?php
    $c = new \HSPHP\WriteSocket();
    $c->connect('localhost',9999);
    $id = $c->getIndexId('data_base_name','table_name','','k,v');
    $c->update($id,'=',array(100500),array(100500,42)); // Update row(k,v) with id 100500 to  k = 100500, v = 42
    $response = $c->readResponse(); // Has 1 if OK
    
    $c = new \HSPHP\WriteSocket();
    $c->connect('localhost',9999);
    $id = $c->getIndexId('data_base_name','table_name','','k,v');
    $c->update($id,'=',array(100500),array(100500,42), 2, 0, array(100501, 100502)); // Update rows where k IN (100501, 100502)
    $response = $c->readResponse(); // Has 1 if OK

    Delete

    <?php
    $c = new \HSPHP\WriteSocket();
    $c->connect('localhost',9999);
    $id = $c->getIndexId('data_base_name','table_name','','k,v');
    $c->delete($id,'=',array(100500));
    $response = $c->readResponse(); //return 1 if OK

    Insert

    <?php
    $c = new \HSPHP\WriteSocket();
    $c->connect('localhost',9999);
    $id = $c->getIndexId('data_base_name','table_name','','k,v');
    $c->insert($id,array(100500,'test\nvalue'));
    $response = $c->readResponse(); //return array() if OK

    相关推荐:《mysql教程

    以上就是MySQL如何才能提高响应速度的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:CSDN,如有侵犯,请联系admin@php.cn删除
    上一篇:mysql中如何实现按照特定顺序排序 下一篇:mysql如何在win10中配置环境变量
    VIP会员

    相关文章推荐

    • mysql死锁的原因和处理方法是什么• mysql图形化工具包括哪些• mysqli扩展如何在php7中使用• php插入数据mysql不显示中文怎么办• PHP7原生MySQL数据库操作有哪些

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网