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()这个函式则是将字符串转换成整数。
范例如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> CREATE TABLE testIP ( ip_int <span style="color: #ff0000;">int unsigned</span> 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> <span style="color: #ff0000;">explain</span> 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 |
+—-+————-+——–+——+—————+———+———+——-+——+————-+
|
1 |
mysql> <span style="color: #ff0000;">explain</span> 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()来作数据转换。范例如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> create table testMAC( mac_bit <span style="color: #ff0000;">bit(48)</span>, 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:<span style="color: #ff0000;">24</span>:1D:DC:55:48 | +--------------+-------------------+ |
内建的函数似乎看来不太符合使用，这个部份，我们之后在来加强~
|
1 |
mysql> <span style="color: #ff0000;">explain</span> 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 |
+—-+————-+———-+——+—————+———-+———+——-+——+————-+
|
1 |
mysql><span style="color: #ff0000;"> explain</span> 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的函式就不会套用索引。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
## 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) <span style="color: #ff0000;">deterministic</span> Begin declare bReturn bit(48);
set bReturn=unhex(replace(sAscii,':','')); return bReturn; end// delimiter ; |
再来我们要建立一个反向函式ether_btoa()，这个函式则是将位转换回字符串。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
## 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) <span style="color: #ff0000;">deterministic</span> 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 ; |
执行结果如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 )
<em id="__mceDel" style="font-size: 0.857142857rem; line-height: 1.714285714;">mysql> <span style="color: #ff0000;">explain</span> select ether_btoa(b), a from ether_table where b=ether_atob('00:CD:EF:00:CD:EF');</em> |
+—-+————-+————-+——+—————+——+———+——-+——+————-+
| 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）数据表。并输入测试数据（略）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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; |
再来我们试着来作数据查询，
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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数据表快？为什么？
相同的，这时又要请出我们的执行计划来检视一下了！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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: <span style="color: #ff0000;">p0,p1,p2,p3,p4,p5,p6,p7,p8,p9</span> 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语法需要作些变动：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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) <span style="color: #ff0000;">–这才是我要的结果!!!</span>
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) |
再看一次执行计划：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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: <span style="color: #ff0000;">p4</span> 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数据库管理师。