如何提高MySQL響應速度

如何提高MySQL響應速度，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

一、概述

二、應用場景

MySQL自身的局限性，很多站點都采用了MySQL+Memcached的經典架構，甚至一些網站放棄MySQL而采用NoSQL產品，比如Redis/MongoDB等。不可否認，在做一些簡單查詢（尤其是PK查詢）的時候，很多NoSQL產品比MySQL要快很多，而且前臺網站上的80%以上查詢都是簡潔的查詢業(yè)務。

MySQL通過HandlerSocket插件提供了API訪問接口，在我們的基準測試中，普通的R510服務器單實例Percona/XtraDB達到了72W+QPS（純讀），如果采用更強勁的CPU增加更多的網卡，理論上可以獲得更高的性能。而同等條件下Memcached僅有40W+QPS（純讀），并且在R510上Memcached單實例已經無法提升性能，因為Memcached對內存的一把大鎖限制了它的并發(fā)能力。

Innodb引擎、按主鍵、unique key或索引搜索（也就是說它的SQL的where條件必須是這些）；支持limit 語句、IN、INSERT/UPDATE/DELETE。

• 沒有主鍵、unique key或索引搜索不行！

• 表必須是Innodb引擎

HandlerSocket和NoSQL這兩者主要的使用場景不同。HandlerSocket主要是用于改善MySQL，優(yōu)化表格的增刪改查以及表格的結構修改等操作，支持密集型CPU操作；而NoSQL作為緩存的功能，支持密集型I/O的操作。

因此，當有需要的時候，可以結合這兩者共同工作。

三、原理

HandlerSocket是MySQL的一個插件，集成在mysqld進程中；NoSQL無法實現的復雜查詢等操作，仍然使用MySQL自身的關系型數據庫實現。在運維層面，原來廣泛使用的MySQL主從復制等經驗繼續(xù)發(fā)揮作用，相比其他NoSQL產品，數據安全更有保障，原理如圖：

可以看出，HandlerSocket繞過MySQL的SQL解析層（SQL Layer），直接訪問MySQL存儲層。另外，HandlerSocket采用epoll和worker thread/thread pooling網絡架構，性能更高。

MySQL的架構是“數據庫管理”和“數據管理”分離，即MySQL Server+Storage Engine的模式。MySQL Server是直接與Client交互的一層，它負責管理連接線程，解析SQL生成執(zhí)行計劃，管理和實現視圖、觸發(fā)器、存儲過程等這些與具體數據操作管理無關的事情，通過調用Handler API讓存儲引擎去操作具體的數據。Storage Engine通過繼承實現Handler API的函數，負責直接與數據交互，數據存取實現（必須實現），事務實現（可選），索引實現（可選），數據緩存實現（可選）。

HandlerSocket是在MySQL的內部組件，以MySQL Daemon Plugin的形式提供類似NoSQL的網絡服務，它并不直接處理數據，只是偵聽配置好的某個端口方式，接收采用NoSQL/API的通訊協議，然后通過MySQL內部的Handler API來調用存儲引擎（例如InnoDB）處理數據。理論上，HanderSocket可以處理各種MySQL存儲引擎，但是用MyISAM時，會出現插入的數據查不出來，這個實際上是構造行時第一字節(jié)沒有初始化為0xff，初始化以后就沒有問題，MyISAM也一樣可以支持，但是為了更好地利用內存，用HandlerSocket都會搭配InnoDB存儲引擎一起使用。

從上圖可以看出，HandlerSocket作為mysql客戶端和mysql的中間層，取代mysql原生的部分數據、表格處理工作，采用多線程的方式，區(qū)分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的區(qū)別在哪呢？對比圖1-2和圖1-3，可從中看出其不同點，圖1-3展示了典型的MySQL+Memecached的應用架構。因為Memcached的get操作比MySQL的內存中或磁盤上的主鍵查詢要快很多，所以Memcached用于緩存數據庫記錄。若是HandlerSocket的查詢速度和相應時間能與Memcached媲美，我們就可以考慮替換Memcached緩存記錄的架構層。

四、優(yōu)勢劣勢

HandlerSocket的優(yōu)勢和特點

1) 支持多種查詢模式

HandlerSocket目前支持索引查詢（主鍵索引和非主鍵的普通索引均可），索引范圍掃描，LIMIT子句，也即支持增加、刪除、修改、查詢完整功能，但還不支持無法使用任何索引的操作。另外支持execute_multi() 一次網絡傳輸多個Query請求，節(jié)省網絡傳輸時間。

2) 處理大量并發(fā)連接

HandlerSocket的連接是輕量級的，因為HandlerSocket采用epoll() 和worker-thread/thread-pooling架構，而MySQL內部線程的數量是有限的（可以由my.cnf中的handlersocket_threads/handlersocket_threads_wr參數控制），所以即使建立上千萬的網絡連接到HandlerSocket，也不會消耗很多內存，它的穩(wěn)定性不會受到任何影響（消耗太多的內存，會造成巨大的互斥競爭等其他問題，如bug#26590，bug#33948，bug#49169）。

3) 優(yōu)秀的性能

HandlerSocket的性能見文章HandlerSocket的性能測試報告描述，相對于其它NoSQL產品，性能表現一點也不遜色，它不僅沒有調用與SQL相關的函數，還優(yōu)化了網絡/并發(fā)相關的問題：

• 更小的網絡數據包：和傳統 MySQL 協議相比，HandlerSocket 協議更簡短，因此整個網絡的流量更小。

• 運行有限的MySQL內部線程數：參考上面的內容。

• 將客戶端請求分組：當大量的并發(fā)請求到達HandlerSocket時，每個工作線程盡可能多地聚集請求，然后同時執(zhí)行聚集起來的請求和返回結果。這樣，通過犧牲一點響應時間，而大大地提高性能。例如，可以減少fsync()調用的次數，減少復制延遲。

4) 無重復緩存

當使用Memcached緩存MySQL/InnoDB記錄時，在Memcached和InnoDB Buffer Pool中均緩存了這些記錄，因此效率非常低（實際上有兩份數據，Memcached本身可能還需要做HA支持），而采用 HandlerSocket插件, 它直接訪問 InnoDB 存儲引擎，記錄緩存在InnoDB Buffer Pool，于是其它SQL語句還可以重復使用緩存的數據。

5) 無數據不一致的現象

由于數據只存儲在一個地方（InnoDB存儲引擎緩存區(qū)內)，不像使用Memcached時，需要在Memcached和MySQL之間維護數據一致性。

6) 崩潰安全

后端存儲是InnoDB引擎，支持事務的ACID特性，能確保事務的安全性，即使設置innodb_flush_log_at_trx_commit=2，若數據庫服務器崩潰時，也只會丟掉<= 1s的數據。

7) SQL/NOSQL并存

在許多情況下，我們仍然希望使用SQL（例如復雜的報表查詢），而大多數NoSQL產品都不支持SQL接口，HandlerSocket僅僅是一個 MySQL 插件，我們依然可以通過MySQL客戶端發(fā)送SQL語句，但當需要高吞吐量和快速響應時，則使用 HandlerSocket。

8) 繼承MySQL的功能

因為HandlerSocket運行于MySQL，因此所有MySQL的功能依然被支持，例如：SQL、在線備份、復制、HA、監(jiān)控等等。

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密集的場景沒有優(yōu)勢

對于IO密集的應用場景，數據庫每秒無法執(zhí)行數千次查詢，通常只有1-10%的CPU利用率，在這種情況下，SQL解析不會成為性能瓶頸，因此使用HandlerSocket沒有什么優(yōu)勢，應當只在數據完全裝載到內存的服務器上使用 HandlerSocket。但是對于PCI-E SSD（例如Fusion-IO）設備，每秒可以提供4w+ IOPS，并且IO設備本身消耗CPU比較大，使用HandlerSocket依然具有優(yōu)勢。

五、安裝

注意：書上的安裝方式已經過時了，版本也較低，不建議使用，建議使用官方的文檔進行安裝，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

注意：

• with-mysql-source：MySQL源代碼目錄

• with-mysql-bindir：MySQL二進制可執(zhí)行文件目錄（mysql_config所在目錄）

• with-mysql-plugindir：MySQL插件目錄

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個，另外為了處理更多并發(fā)連接，設置能打開的文件描述符個數為65535

此外，InnoDB的innodb_buffer_pool_size或MyISAM的key_buffy_size配置選項關系到緩存索引，所以盡可能設置大一些，這樣才能發(fā)揮HandleSocket的潛力。

4. 激活HandleSocket

登陸MySQL執(zhí)行

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

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業(yè)資訊頻道，感謝您對億速云的支持。