MySQL 效能調校

常常出現在人們的口頭禪，”便宜沒好貨”，讓人錯認為開源的MySQL資料庫，其效能一定也不好。在我之前的經驗中就常常發現，這樣月暈效應現象讓許多人懶得去對MySQL作效能調校，將其效能的慢，視為理因如此。但殊不知，那只是個認知上的錯覺罷了。

因此，我想在此跟大家介紹一下，MySQL效能調校的方法，但不免俗的要先來個”免責宣言”，以免你又落入了另一個迷失。

首先，世上沒有所謂的”大還丹”（吃下去可以讓你增加一甲子的功力）。要知道，效能調校的心法就像是需要經年累月的苦練才可以練就的”九陽神功”一樣，不可能一篇文章就可以讓你成為效能調校的專家，也好像在玩線上遊戲一般，你一直需要靠打怪來累積經驗值。再來是，效能調校的技術中，也沒有所謂的萬靈藥。也就是，今天你用了一個調校的方法達到出乎意料的效能提昇，但並不表示在下個案例中你仍可以使用相同的方法，而得到相同的效果。所以，效能調校有輸有贏，失敗了別氣餒，再試一次就好。如果你可以以常理心來面對效能調校，那我們就可以開始言歸正傳了！

心法一：釐清資料庫效能調校的瓶頸

工程師們常常會低估效能調校的成果，但這還不是最慘的！最慘的是，老闆們常常有個錯覺，一旦進行資料庫效能調校後，系統就可以像飛龍一樣飛上天。這樣無形的壓力，也就是讓工程師一直遲遲不敢多跨出一步的兇手。所以，為了自保，我們必須給老闆一個正確的引導，讓他們知道資料庫效能調校的瓶頸在那裡。

首先，我們必須要先知道，資料庫是運行在作業系統上，運算的處理能力是決定於中央處理器的運算能力，資料的快取速度決定在記憶體的速度及大小，而最終資料是存放在磁碟的檔案中。”以上是廢話吧”，大部分的人都會這樣回答我。

但我反問的是，你知道你的CPU每秒可以處理多少個指令集，存放在記憶體的快取每秒可以處理多少資料量，而每秒鐘硬碟的I/O可以都取多少資料量。這些，很多人都答不出來，所以我也不勉強你也可以回答出來。最後，我只問你一個問題，那就是，你知道最能有效支援MySQL的作業系統是什麼呢？而你又是用什麼作業系統在執行MySQL？

我相信，大多數的人沒有辦法釐清系統本身的效能極限在哪裡，也就無法說服老闆效能調校的預期結果。所以想著這個吃力不討好的燙手山芋，還是丟給別人好了。但是，我卻不這麼認為，我反而覺得這是一個可以自我提昇的好機會。在漫畫”頭文字D”中，藤元拓海加入了高橋涼介的Project D車隊後，也因為了解了更多車子構造，引擎性能等，而成為更強的賽車手。所以，如果你真的無法駕馭你的跑車，看不出他的極速在哪裡，至少你也要找台跟你一樣的車，看看別人可以開多快，想辦法追上他的後尾燈吧！

心法二：SQL的調校

關於SQL的調校，我從三個角度來切入介紹：

1. SQL的慢查詢：

在我的經驗來說，百分之九十效能的問題，在做完SQL語法的改善之後，便可以充分的解決。所以，如何收集系統中，較慢的SQL語法，便成為一個重要的課題。

MySQL的預設設定中，並沒有啟用”慢查詢”日誌檔，所以，如果你要紀錄系統中較慢的查詢，你可以將下面兩個設定加入到my.cnf中。

1. log-slow-queries ##預設是關閉慢查詢日誌
2. long-qeury-time=5 ##預設是10秒

在啟用慢查詢日誌後，預設的日誌檔位置及檔名是：[MySQL資料目錄]/[主機名稱]-slow.log。

2. SQL語法與儲存引擎的選擇：

SQL語法和儲存引擎看似沒有太大的關聯，但是我想針對全文檢索的角度來探索這個問題。

現今的系統開始被要求有超炫的顯示畫面與超強的查詢能力，也因此，全文檢索的查詢，是系統中必備的功能。但在MySQL 5.6之前的版本，InnoDB有支援交易能力（ACID）而沒有支援全文檢索。所以，如果應用程式需要使用到全文檢索的功能，就需要將資料庫儲存引擎換成MyISAM。這往往會讓工程師進入天人交戰的抉擇。其實，還有另外一個小技巧可以解決這個問題，那就是使用”複合式”資料庫儲存引擎。也就是在需要全文檢索的資料表中，使用MyISAM儲存引擎，而需要支援交易的資料表，則使用InnoDB儲存引擎。最後，在將兩個資料表用一對一的方式關聯起來，則你可以同時享用到MyISAM及InnoDB儲存引擎的優點。

3. 索引數量的控制：

索引的目的就是在建立一個資料較小的索引樹，當應用程式在查詢資料時，可以先利用這顆樹取得資料定址，減少磁碟I/O，進而可以加速資料的存取。但是，使用者仍然需要避免使用過多的索引。因為，MySQL優化器中，首先會利用符合條件的索引先試撈一段資料，並決定出讀取最快的索引。所以，一旦索引的數目越多，花在優化的時間就越長，反而拉長查詢的時間。此外，索引的數目越多，在新增資料或更新資料時，連帶要更新的索引也越多，導致資料寫入的時間變長，寫入鎖定（Write Lock）的時間也變長。

但千萬別以為應用程式讀寫比很高（讀遠大於寫），就可以略它。因為寫入鎖定發生時，連帶的也會影響到該資料讀取的全部線程，整體的效能也會因此而大打折扣。我之前就有遇到一個案例，一張二十多個欄位的表格，有超過二十個索引，幾乎每個欄位都可以對應到一個索引。當客戶嫌這個資料表的查詢速度很慢時，試著加強硬體效能卻又達不到預期的效果，最後只需靠著砍掉不必要的索引，就讓查詢的速度爆增。當時，客戶就問到：索引的數量要控制在多少以內才不算太多？但是使用者又要求讓他可以用每個欄位來當作查詢的條件，這樣，每個欄位不都是要建立一個索引嗎？那該怎麼辦？

在我的經驗看來，索引的數目通常不需要超過五個，超過通常就開始會有效能的隱憂。至於使用者要求，在我看來，這是必須要去溝通的。因為，天下沒有白吃的午餐，使用者必須要依照使用的頻率，資料分佈的大小來決定索引的建立與否。舉例來說，性別就不是一個適合建立索引的欄位，因為這個欄位值的鑑別率太低。試想，你幾乎快達到全表掃描，卻只有一半的鑑別率，這樣，說不定”全表掃描”還比你用”性別索引＋資料讀取”的速度還快。所以，我會建議，識別度高的（95%）才建立索引。

心法三：網路系統應用的密技

1. IP：

在MySQL的預存函式中，有兩個跟IP有關函式，分別是inet_ntoa()及inet_aton()。

這兩個函式的設計原理是，如果使用者要儲存IP的資訊，如果使用”整數”的型別來儲存，只需要4個字元。但如果使用”字串”來儲存，則需要15個字元。一來一往，資料存取的速度就差了快三倍。inet_ntoa()這個函式就是用來將整數轉換成字串，而inet_aton()這個函式則是將字串轉換成整數。

範例如下：

mysql> CREATE TABLE testIP (
    ip_int int unsigned DEFAULT NULL,
    ip_char char(15) DEFAULT NULL,
    index ip_int (ip_int),
    index ip_char (ip_char)
) ENGINE=InnoDB;
mysql>
mysql> insert into testIP values(
    inet_aton('216.18.50.126'),'216.18.50.126'
);
mysql>
mysql> select inet_ntoa(ip_int),ip_char from testIP;
+-------------------+---------------+
| inet_ntoa(ip_int) | ip_char       |
+-------------------+---------------+
| 216.18.50.126     | 216.18.50.126 |
+-------------------+---------------+

mysql>
mysql> explain select * from testIP where ip_char='216.18.50.126';

+—-+————-+——–+——+—————+———+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——–+——+—————+———+———+——-+——+————-+
| 1 | SIMPLE | testIP | ref | ip_char | ip_char | 16 | const | 1 | Using where |
+—-+————-+——–+——+—————+———+———+——-+——+————-+

mysql> explain select * from testIP where ip_int=inet_aton('216.18.50.126');

+—-+————-+——–+——+—————+——–+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——–+——+—————+——–+———+——-+——+————-+
| 1 | SIMPLE | testIP | ref | ip_int | ip_int | 5 | const | 1 | Using where |
+—-+————-+——–+——+—————+——–+———+——-+——+————-+

從上面的執行計畫（explain）結果來看，使用純字串型態來儲存IP資訊的欄位，在查詢時，索引鍵長為16。而使用inet_aton()/inet_ntoa()將IP資訊的欄位以整數型態來儲存時，其索引鍵長為5。

2. MAC：

相同的，在MAC卡號上，也可以使用類似的方法。所以，我試著用48位元的資料來儲存資料。並利用內建函式hex()來作資料轉換。範例如下：

mysql> create table testMAC(
    mac_bit bit(48),
    mac_char char(17),
    index(mac_bit),
    index(mac_char));
mysql>
mysql> insert into testMAC values (
    x'00241DDC5548', '00:24:1D:DC:55:48'
);
mysql>
mysql> select hex(mac_bit), mac_char from testMAC;
+--------------+-------------------+
| hex(mac_bit) | mac_char          |
+--------------+-------------------+
| 241DDC5548   | 00:24:1D:DC:55:48 |
+--------------+-------------------+

內建的函數似乎看來不太符合使用，這個部份，我們之後在來加強~

mysql> explain select hex(mac_bit), mac_char from testMAC where mac_char='00:24:1D:DC:55:48';

+—-+————-+———-+——+—————+———-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———-+——+—————+———-+———+——-+——+————-+
| 1 | SIMPLE | testMAC2 | ref | mac_char | mac_char | 18 | const | 1 | Using where |
+—-+————-+———-+——+—————+———-+———+——-+——+————-+

mysql> explain select hex(mac_bit), mac_char from testMAC where mac_bit=x'00241DDC5548';

+—-+————-+———-+——+—————+———+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———-+——+—————+———+———+——-+——+————-+
| 1 | SIMPLE | testMAC2 | ref | mac_bit | mac_bit | 7 | const | 1 | Using where |
+—-+————-+———-+——+—————+———+———+——-+——+————-+

從上面的執行計畫（explain）結果來看，使用純字串型態來儲存MAC資訊的欄位，在查詢時，索引鍵長為18。而使用hex()將MAC資訊的欄位以整數型態來儲存時，其索引鍵長為7。

為了彌補函式的不足（前導0被消除及補足冒號間隔），可以在寫個函式來包裝/增強hex()函式。而函式又可分為兩種，一種是MySQL預存函式，另一種是使用者定義函式（UDF）。在此我要示範的是MySQL預存函式。

首先我們先來建立一個函式ether_atob()，這個函式主要的目的是在將字串轉換成位元。

其中特別要注意的是，函式宣告是，要定義這個函式回傳值是可預期的（相同的輸入值，一定會返回相同的結果），因為這樣的函式在SQL中使用時，才會套用索引。反例則像是random()函式，每次回傳值是不可預期的，則random的函式就不會套用索引。

## create stored function ether_atob(), from ascii to bit
## must define "deterministic", or explain will not use index
delimiter //
drop function if exists ether_atob//
create function ether_atob(sAscii char(17))
returns bit(48)
deterministic
Begin
declare bReturn bit(48);

set bReturn=unhex(replace(sAscii,':',''));
return bReturn;
end//
delimiter ;

再來我們要建立一個反向函式ether_btoa()，這個函式則是將位元轉換回字串。

## create stored function ether_btoa(), from bit to ascii
## must define "deterministic", or explain will not use index
delimiter //
drop function if exists ether_btoa//
create function ether_btoa(sBit bit(48))
returns char(17)
deterministic
begin
declare sReturn char(17);
set sReturn=lpad(hex(sBit),12,'0');
set sReturn=concat_ws(':',
substr(sReturn,1,2), substr(sReturn,3,2), substr(sReturn,5,2),
substr(sReturn,7,2), substr(sReturn,9,2), substr(sReturn,11,2)
);
return sReturn;
end//
delimiter ;

執行結果如下：

mysql> create table ether_table (b bit(48), a char(17), index(b), index(a));
Query OK, 0 rows affected (0.67 sec)
mysql>
mysql> insert into ether_table values (ether_atob('00:CD:EF:00:CD:EF'),'00:CD:EF:00:CD:EF');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select ether_btoa(b), a
from ether_table
where b=ether_atob('00:CD:EF:00:CD:EF');
+-------------------+-------------------+
| ether_btoa(b)     | a                 |
+-------------------+-------------------+
| 00:CD:EF:00:CD:EF | 00:CD:EF:00:CD:EF |
+-------------------+-------------------+
1 rows in set (0.01 sec )

mysql> explain select ether_btoa(b), a from ether_table where b=ether_atob('00:CD:EF:00:CD:EF');

+—-+————-+————-+——+—————+——+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————-+——+—————+——+———+——-+——+————-+
| 1 | SIMPLE | ether_table | ref | b | b | 7 | const | 1 | Using where |
+—-+————-+————-+——+—————+——+———+——-+——+————-+
1 row in set (0.00 sec)

有關於使用者定義函式（UDF），可以參考我的部落格：
http://stanley-huang.blogspot.com/2010/03/level-3-create-udf-for-storing-mac.html

心法四：其它的效能調校

1. 分散磁碟I/O

將MySQL的bin log與資料檔分散在不同的磁碟之中

2. MyISAM 設定

a. key_buffer_size=128M # default is 8M
b. bulk_insert_buffer_size=4194304 # default is 8M

3. InnoDB 組態設定

a. innodb_buffer_pool_size=32M # default 8M
b. innodb_log_buffer_size=8M # default 1M

4. 避免InnoDB表空間成為一個單一大檔案

在my.cnf中設定innodb_file_per_table，讓不同的資料表有獨立的資料表空間

在分散磁碟的I/O部份，如果你使用的是MySQL5.1之後的版本，你還可以是用data partition來分散磁碟I/O。

假設我們有十年的歷史資料要來作查詢。

首先我們先來建立一個以年份作區塊的partition資料表（test_partition_wp）及一個非partition（test_partition_np）資料表。並輸入測試資料（略）。

mysql> CREATE TABLE test_partition_wp(
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
) engine=InnoDB
PARTITION BY RANGE(YEAR(c3))
{
    partition p0 less than (2001),
    partition p1 less than (2002),
    partition p2 less than (2003),
    partition p3 less than (2004),
    partition p4 less than (2005),
    partition p5 less than (2006),
    partition p6 less than (2007),
    partition p7 less than (2008),
    partition p8 less than (2009),
    partition p9 less than (maxvalue)
};

mysql> CREATE TABLE test_partition_np(
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
) engine=InnoDB;

再來我們試著來作資料查詢，

mysql> select count(1) from test_partition_wp where year(c3)=2005;
+----------+
| count(1) |
+----------+
| 47358    |
+----------+
1 row in set (0.58 sec)
mysql>
mysql> select count(1) from test_partition_np where year(c3)=1995;
+----------+
| count(1) |
+----------+
| 47358    |
+----------+
1 row in set (0.53 sec)
mysql>

結果發現，非partition資料表的查詢比partition資料表快？為什麼？

相同的，這時又要請出我們的執行計畫來檢視一下了！

mysql> explain partitions select count(1) from test_partition_wp where year(c3)=1995\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_partition_wp
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12006556
Extra: Using where

找到兇手了！從partitions的訊息指出，這次的查詢並不是如我們所預期的，只在特定的partition搜尋資料，而是全partition掃描，這並不是我們要的結果。原因是為什麼？

主要的原因是在於我們的查詢條件是這樣下的，year(c3) = 2005。所以對儲存引擎來說，每一筆資料的c3欄位，都必需經過year()函式的轉換後，才能對目標值2005進行比對。換言之，因為儲存引擎沒有辦法對欄位進行預測及過濾，所以必須對全部的partition進行掃描才可得到資料。也因為如此，partition的資料表，不僅沒有幫忙減少I/O，反而讓儲存引擎需要將分開的資料結合起來，進而增加處理的時間。

所以，此時的SQL語法需要作些變動：

mysql> select count(1) from test_partition_wp where c3 between '1995/01/01' and '1995/12/31';
+----------+
| count(1) |
+----------+
| 47358    |
+----------+
1 row in set (0.04 sec) –這才是我要的結果!!!

mysql> select count(1) from test_partition_np where c3 between '1995/01/01' and '1995/12/31';
+----------+
| count(1) |
+----------+
| 47358    |
+----------+
1 row in set (0.62 sec)

再看一次執行計畫：

mysql> explain partitions select count(1) from test_partition_wp where c3 between '1995/01/01' and '1995/12/31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_partition_wp
partitions: p4
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12006556
Extra: Using where
1 row in set (0.00 sec)

這次我們就會發現，這次儲存引擎只會掃描一個partition。

效能調校技巧總結：

1. 使用執行計畫（explain）來檢視查詢。
2. 永遠紀錄慢查詢。
3. 避免在LIKE子查詢中比對＊號開頭的字串。
4. 將I/O分散。
5. 使用partition資料表。
6. 不要在重複的攔位上建立索引。
7. 使用inet_aton()/inet_ntoa()函式來處理IP資訊。
8. 最重要的是，僱用一個認證過得MySQL資料庫管理師。

後續 >> MySQL 儲存程式之儲存程序（Stored Routines）