MySQL如何才能提高響應(yīng)速度

mysql自身的局限性，很多站點(diǎn)都采用了mysql+memcached的經(jīng)典架構(gòu)，甚至一些網(wǎng)站放棄mysql而采用nosql產(chǎn)品。不可否認(rèn)，在做一些簡單查詢（尤其是pk查詢）的時候，很多nosql產(chǎn)品比mysql要快很多。

一、概述

二、應(yīng)用場景

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

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

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

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

• 表必須是Innodb引擎

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

因此，當(dāng)有需要的時候，可以結(jié)合這兩者共同工作。

三、原理

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

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

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

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

從上圖可以看出，HandlerSocket作為mysql客戶端和mysql的中間層，取代mysql原生的部分?jǐn)?shù)據(jù)、表格處理工作，采用多線程的方式，區(qū)分DDL和DML進(jìn)行操作。這樣的目的是保證在復(fù)雜處理的情況下，能夠高效的進(jìn)行處理。

因為HandlerSocket是以MySQL Daemon Plugin形式存在，所以在應(yīng)用中，可把MySQL當(dāng)NoSQL使用。它最大的功能是實現(xiàn)了與存儲引擎交互，比如InnoDB，而這不需要任何SQL方面的初始化開銷。訪問MySQL的TABLE時，當(dāng)然也是需要open/close table的，但是它并不是每次都去open/close table，因為它會將以前訪問過的table cache保存下來以重復(fù)使用，而opening/closing tables是最耗資源的，而且很容易引起互斥量的爭奪，這樣一來，對于提高性能非常有效。在流量變小時，HandlerSocket會close tables，所以它一般不會阻塞DDL。

HandlerSocket與MySQL+Memcached的區(qū)別在哪呢？對比圖1-2和圖1-3，可從中看出其不同點(diǎn)，圖1-3展示了典型的MySQL+Memecached的應(yīng)用架構(gòu)。因為Memcached的get操作比MySQL的內(nèi)存中或磁盤上的主鍵查詢要快很多，所以Memcached用于緩存數(shù)據(jù)庫記錄。若是HandlerSocket的查詢速度和相應(yīng)時間能與Memcached媲美，我們就可以考慮替換Memcached緩存記錄的架構(gòu)層。

四、優(yōu)勢劣勢

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

1) 支持多種查詢模式

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

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

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

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

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

• 更小的網(wǎng)絡(luò)數(shù)據(jù)包：和傳統(tǒng) MySQL 協(xié)議相比，HandlerSocket 協(xié)議更簡短，因此整個網(wǎng)絡(luò)的流量更小。

• 運(yùn)行有限的MySQL內(nèi)部線程數(shù)：參考上面的內(nèi)容。

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

4) 無重復(fù)緩存

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

5) 無數(shù)據(jù)不一致的現(xiàn)象

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

6) 崩潰安全

后端存儲是InnoDB引擎，支持事務(wù)的ACID特性，能確保事務(wù)的安全性，即使設(shè)置innodb_flush_log_at_trx_commit=2，若數(shù)據(jù)庫服務(wù)器崩潰時，也只會丟掉

7) SQL/NOSQL并存

在許多情況下，我們?nèi)匀幌Ｍ褂肧QL（例如復(fù)雜的報表查詢），而大多數(shù)NoSQL產(chǎn)品都不支持SQL接口，HandlerSocket僅僅是一個 MySQL 插件，我們依然可以通過MySQL客戶端發(fā)送SQL語句，但當(dāng)需要高吞吐量和快速響應(yīng)時，則使用 HandlerSocket。

8) 繼承MySQL的功能

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

9) 不需要修改/重建MySQL

因為HandlerSocket是一個插件并且開源，所以它支持從任何MySQL源碼、甚至是第三方版本（例如Percona）構(gòu)建，而無需對MySQL做出任何修改。

10) 獨(dú)立于存儲引擎

雖然我們只測試了MySQL-EnterpriseInnoDB和Percona XtraDB插件，但HandlerSocket理論上可以和任何存儲引擎交互。MyISAM通過簡單的修改也是可以被支持的，但是從數(shù)據(jù)緩存而利用內(nèi)存的角度看這個意義不大。

HandlerSocket的缺陷和注意事項

1) 協(xié)議不兼容

HandlerSocket API與Memcached API并不兼容，盡管它很容易使用，但仍然需要一點(diǎn)學(xué)習(xí)來學(xué)會如何與HandlerSocket交互。不過我們可以通過重載Memecached函數(shù)來翻譯到HandlerSocket API。

2) 沒有安全功能

與其它NoSQL數(shù)據(jù)庫類似，HandlerSocket不支持安全功能，HandlerSocket的工作線程以系統(tǒng)用戶權(quán)限運(yùn)行，因此應(yīng)用程序可以通過HandlerSocket協(xié)議訪問所有的表對象，但是可以通過簡單的修改協(xié)議，在my.cnf中增加一個配置項為密碼，連接時通過這個配置的密碼驗證，當(dāng)然也可以通過網(wǎng)絡(luò)防火墻來過濾數(shù)據(jù)包。

3) 對于磁盤IO密集的場景沒有優(yōu)勢

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

五、安裝

注意：書上的安裝方式已經(jīng)過時了，版本也較低，不建議使用，建議使用官方的文檔進(jìn)行安裝，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二進(jìn)制可執(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 #?讀請求線程數(shù) loose_handlersocket_threads?=?16 #?寫請求線程數(shù) loose_handlersocket_threads_wr?=?16 #?設(shè)置最大接收連接數(shù) open_files_limit?=?65535

這里增加的這些主要是針對HandleSocket的配置，它有兩個端口，9998讀數(shù)據(jù)，9999寫數(shù)據(jù)，但通過9998讀的效率更高，這里設(shè)置處理讀寫的線程數(shù)均為16個，另外為了處理更多并發(fā)連接，設(shè)置能打開的文件描述符個數(shù)為65535

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

4. 激活HandleSocket

登陸MySQL執(zhí)行

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

可以通過show processlist或show plugins來看到HandleSocket

最后需要安裝PHP的擴(kuò)展包PHP HandlerSocket

安裝文檔：https://github.com/tz-lom/HSPHP

PHP用法：

Select

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

Update

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

Delete

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

Insert

<?php $c = new HSPHPWriteSocket(); $c->connect('localhost',9999); $id?=?$c-&gt;getIndexId('data_base_name','table_name','','k,v'); $c-&gt;insert($id,array(100500,'testnvalue')); $response?=?$c-&gt;readResponse();?//return?array()?if?OK

相關(guān)推薦：《mysql教程》