MySQL如何才能提高回應速度

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。

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

• 表必須是Innodb引擎

#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相關的函數，也優化了網路/並發相關的問題：

• 更小的網路封包：和傳統MySQL 協定相比，HandlerSocket 協定更簡短，因此整個網路的流量更小。

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

• 將客戶端請求分組：當大量的並發請求到達HandlerSocket時，每個工作執行緒盡可能多地聚集請求，然後同時執行聚集起來的請求和傳回結果。這樣，透過犧牲一點響應時間，而大大地提高性能。例如，可以減少fsync()呼叫的次數，減少複製延遲。

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，若資料庫伺服器崩潰時，也只會丟掉

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

注意：

• with-mysql-source：MySQL原始程式碼目錄
• with-mysql-bindir：MySQL二進位執行檔目錄（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個，另外為了處理更多並發連接，設定能開啟的檔案描述符個數為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(&#39;data_base_name&#39;, &#39;table_name&#39;, &#39;&#39;, &#39;id,name,some,thing,more&#39;);
$c->select($id, &#39;=&#39;, array(42)); // SELECT WITH PRIMARY KEY
$response = $c->readResponse();

//SELECT with IN statement
$c = new \HSPHP\ReadSocket();
$c->connect();
$id = $c->getIndexId(&#39;data_base_name&#39;, &#39;table_name&#39;, &#39;&#39;, &#39;id,name,some,thing,more&#39;);
$c->select($id, &#39;=&#39;, array(0), 0, 0, array(1,42,3));
$response = $c->readResponse();

Update

<?php
$c = new \HSPHP\WriteSocket();
$c->connect(&#39;localhost&#39;,9999);
$id = $c->getIndexId(&#39;data_base_name&#39;,&#39;table_name&#39;,&#39;&#39;,&#39;k,v&#39;);
$c->update($id,&#39;=&#39;,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(&#39;localhost&#39;,9999);
$id = $c->getIndexId(&#39;data_base_name&#39;,&#39;table_name&#39;,&#39;&#39;,&#39;k,v&#39;);
$c->update($id,&#39;=&#39;,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(&#39;localhost&#39;,9999);
$id = $c->getIndexId(&#39;data_base_name&#39;,&#39;table_name&#39;,&#39;&#39;,&#39;k,v&#39;);
$c->delete($id,&#39;=&#39;,array(100500));
$response = $c->readResponse(); //return 1 if OK

Insert

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

相關推薦：《mysql教學》

以上是MySQL如何才能提高回應速度的詳細內容。更多資訊請關注PHP中文網其他相關文章！