在MySQL的SQL查詢性能分析當(dāng)中，主要使用explain命令對(duì)查詢SQL語(yǔ)句的執(zhí)行情況進(jìn)行分析，包含查詢所涉及的表，查詢索引使用情況，排序情況等，用法的使用很簡(jiǎn)單，示例如下：
mysql> EXPLAIN SELECT DISTINCT(trade_date) FROM order WHERE user_id=1 ORDER BY trade_date DESC LIMIT 10; +----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | order | NULL | ref | PRIMARY,idx_user_trade|idx_user_trade| 768 | const | 20 | 100.00 | Using where; Using index | +----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.03 sec)
- 以上示例對(duì)應(yīng)order表的索引情況：在user_id和trade_date兩個(gè)字段建立了一個(gè)聯(lián)合索引。
KEY `idx_user_trade` (`user_id`,`trade_date`) USING BTREE
以上示例的explain命名輸出的各參數(shù)含義如下：
1.id：執(zhí)行的序號(hào)，這個(gè)語(yǔ)句沒(méi)有子查詢，故只有1，值越大越先執(zhí)行；
2.select_type：數(shù)據(jù)讀取類型，這里只是針對(duì)單表，也沒(méi)有UNION操作，故是SIMPLE，其他類型包括：PRIMARY, UNION RESULT, SUBQUERY等；
3.table：查詢涉及的表；
4.partitions：查詢涉及的表的哪些分區(qū)；
5.type：訪問(wèn)類型，指明了MySQL以何種方式查找表中符合條件的行，這個(gè)也是需要重點(diǎn)關(guān)注的一項(xiàng)指標(biāo)，包含的類型為：ALL, index, range, ref, eq_ref, const/system, NULL，性能依次變好，其中：
- ALL：為全表掃描，性能最差；
- index：為全索引掃描，性能通常也是不夠理想；
- range：為對(duì)索引進(jìn)行范圍掃描，然后返回對(duì)應(yīng)的數(shù)據(jù)行，如SQL包含BETWEEN，>=，IN()等語(yǔ)句時(shí)就是range；
- ref：為索引查找，返回匹配單個(gè)索引值的數(shù)據(jù)行，如果不是范圍查詢，則需要至少達(dá)到這個(gè)級(jí)別；
- eq_ref：也是索引查找，不過(guò)最多只返回一條記錄，通常是在主鍵或者唯一性索引上，性能較好；
- const/system：為表只有最多一個(gè)匹配行，直接讀取對(duì)應(yīng)的數(shù)據(jù)行，不需要查找索引再根據(jù)索引的結(jié)果讀取數(shù)據(jù)行，通常是主鍵或唯一性索引上有固定值的情形；
- NULL：在執(zhí)行階段不需要訪問(wèn)表，直接從索引返回需要的值。
6.possible_keys：該次查詢可以使用的索引；
7.key：該次查詢實(shí)際使用的索引；
8.key_len：使用索引時(shí)，所使用的索引值的最大字節(jié)數(shù)，當(dāng)type為NULL時(shí)，該值也為NULL；
9.ref：哪些字段或常量配合key將數(shù)據(jù)行從表中獲取出來(lái)；
10.rows：估計(jì)查找到所需要的行，大概需要掃描讀取多少數(shù)據(jù)行，這個(gè)值越小越好；即存儲(chǔ)引擎大概需要讀取并返回rows的值這么多行數(shù)據(jù)給server層；通常與下面的filtered一起分析，即如果rows為5，而實(shí)際需要的查詢結(jié)果為1條記錄，則filtered的值為大概為1/5；如下：
mysql> explain SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3 AND price BETWEEN 80 AND 80.999; +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | idx_ul_type | idx_id_type | 769 | const,const | 5 | 11.11 | Using where | +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
SQL執(zhí)行情況：這條SQL實(shí)際返回了一條記錄。
mysql> SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3 AND price BETWEEN 80 AND 80.999; 省略具體內(nèi)容 1 row in set (0.00 sec)
如果把price BETWEEN 80 AND 80.999的條件去掉，則返回5條記錄：
mysql> SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3; 省略具體內(nèi)容 5 rows in set (0.00 sec)
索引情況如下：在id和type的兩個(gè)列建了一個(gè)聯(lián)合索引，不包含price列，故由于price沒(méi)有索引，實(shí)際存儲(chǔ)引擎會(huì)讀取并返回5條記錄給server層，server層在根據(jù) price BETWEEN 80 AND 80.999再過(guò)濾最后剩下一條記錄。
KEY `idx_id_type` (`id`,`type`) USING BTREE
如果把price加到idx_id_type索引中，即idx_id_type (id,type,price) ，則執(zhí)行情況如下：
mysql> explain SELECT type, expire_date, id FROM test WHERE ul='11111' AND type=3 AND price BETWEEN 80 AND 80.999; +----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | test | NULL | range | idx_id_type | idx_id_type | 778 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
11.filtered：此查詢條件所過(guò)濾的數(shù)據(jù)的百分比，表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過(guò)濾后，剩下多少滿足查詢的記錄數(shù)量的比例；越大表示存儲(chǔ)引擎返回給server層的都是有用的，故說(shuō)明效率較高，通常都是100%；
12.Extra：此處執(zhí)行的額外信息，一次SQL查詢的Extra可以包含以下的一條或多條，包括：
- Using where：表示SQL語(yǔ)句存在WHERE條件，不是獲取全表數(shù)據(jù)，一般使用了WHERE條件都會(huì)有這個(gè)；
- Using index：表示使用覆蓋索引返回?cái)?shù)據(jù)，不需要訪問(wèn)表，通常是指該查詢性能較好；
- Using index condition：這個(gè)是在5.6版本后加入的新特性，主要作用是當(dāng)WHERE中的某個(gè)條件對(duì)應(yīng)的字段，如a，是加了索引的，但是無(wú)法使用，如使用了 a like ‘%abc%’ 這種SQL，在沒(méi)有這個(gè)特性之前，存儲(chǔ)引擎此時(shí)無(wú)法使用這個(gè)字段對(duì)應(yīng)的索引了，需要回表找到符合WHERE其他條件的數(shù)據(jù)行，即full row正行數(shù)據(jù)，然后傳給server層，最后在server層處理a like '%abc%'這個(gè)條件，即使a這個(gè)字段加了索引，這樣就存儲(chǔ)引擎就需要讀取較多的數(shù)據(jù)行和傳給server層較多的數(shù)據(jù)。擁有這個(gè)特性之后，存儲(chǔ)引擎層會(huì)使用索引來(lái)處理a like '%abc%'這個(gè)SQL，進(jìn)一步過(guò)濾，從而減少回表查詢的次數(shù)和傳給server層的數(shù)據(jù)量；詳見(jiàn)官方文檔：8.2.1.5 Index Condition Pushdown Optimization
- ，如下為詳細(xì)例子：
- Using temporary：表示使用了臨時(shí)表來(lái)進(jìn)行分組、排序或者多表join，通常表示查詢效率不高，需要優(yōu)化；
- Using filesort：表示需要使用一個(gè)外部文件索引來(lái)對(duì)結(jié)果進(jìn)行排序，而不是直接根據(jù)內(nèi)部索引順序從表中讀取數(shù)據(jù)，這個(gè)過(guò)程通常需要消耗比較大的CPU資源，需要優(yōu)化。
