共計 5839 個字符，預計需要花費 15 分鐘才能閱讀完成。
這篇文章主要介紹 mysql 中慢查詢優化的示例分析，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！
一個用戶反映線上一個 SQL 語句執行時間慢得無法接受。SQL 語句看上去很簡單（本文描述中修改了表名和字段名 ):
SELECT count(*) FROM a JOIN b ON a.`S` = b.`S` WHERE a.`L` 2014-03-30 00:55:00 AND a.`L` 2014-03-30 01:00:00 ;
且查詢需要的字段都建了索引，表結構如下：CREATE TABLE `a` (
`L` timestamp NOT NULL DEFAULT 2000-01-01 00:00:00 ,
`I` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`A` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`S` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`F` tinyint(4) DEFAULT NULL,
`V` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT ,
`N` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
KEY `IX_L` (`L`),
KEY `IX_I` (`I`),
KEY `IX_S` (`S`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `b` (
`R` timestamp NOT NULL DEFAULT 2000-01-01 00:00:00 ,
`V` varchar(32) DEFAULT NULL,
`U` varchar(32) DEFAULT NULL,
`C` varchar(16) DEFAULT NULL,
`S` varchar(64) DEFAULT NULL,
`I` varchar(64) DEFAULT NULL,
`E` bigint(32) DEFAULT NULL,
`ES` varchar(128) DEFAULT NULL,
KEY `IX_R` (`R`),
KEY `IX_C` (`C`),
KEY `IX_S` (`S`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
從語句看，這個查詢計劃很自然的，就應該是先用 a 作為驅動表，先后使用 a.L 和 b.S 這兩個索引。而實際上 explain 的結果卻是： +----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
| 1 | SIMPLE | b | index | IX_S | IX_S | 195 | NULL | 1038165 | Using index |
| 1 | SIMPLE | a | ref | IX_L,IX_S | IX_S | 195 | test.b.S | 1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+分析
從 explain 的結果看，查詢用了 b 作為驅動表。上一篇文章我們介紹到，MySQL 選擇 jion 順序是分別分析各種 join 順序的代價后，選擇最小代價的方法。這個 join 只涉及到兩個表，自然也與 optimizer_search_depth 無關。于是我們的問題就是，我們預期的那個 join 順序的為什么沒有被選中？MySQL Tips: MySQL 提供 straight_join 語法，強制設定連接順序。 explain SELECT count(*) FROM a straight_join b ON a.`S` = b.`S` WHERE a.`L` 2014-03-30 00:55:00 AND a.`L` 2014-03-30 01:00:00 ;
+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+
| 1 | SIMPLE | a | range | IX_L,IX_S | IX_L | 4 | NULL | 63 | Using where |
| 1 | SIMPLE | b | index | IX_S | IX_S | 195 | NULL | 1038165 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+MySQL Tips: explain 結果中，join 的查詢代價可以用依次連乘 rows 估算。join 順序對了，簡單的分析查詢代價：普通 join 是 1038165*1, straight_join 是 63*1038165. 貌似 MySQL 沒有錯。但一定哪里不對！發現異常
回到我們最初的設想。我們預計表 a 作為驅動表，是因為認為表 b 能夠用上 IX_S 索引，而實際上 staight_join 的時候確實用上了，但這個結果與我們預期的又不同。我們知道，索引的過濾性是決定了一個索引在查詢中是否會被選中的重要因素，那么是不是 b.S 的過濾性不好呢？MySQL Tips: show index from tbname 返回結果中 Cardinality 的值可以表明一個索引的過濾性。show index 的結果太多，也可以從 information_schema 表中取。mysql select * from information_schema.STATISTICS where table_name= b and index_name= IX_S \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: b
NON_UNIQUE: 1
INDEX_SCHEMA: test
INDEX_NAME: IX_S
SEQ_IN_INDEX: 1
COLUMN_NAME: S
COLLATION: A
CARDINALITY: 1038165 SUB_PART: NULL
PACKED: NULL
NULLABLE: YES
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
可以這個索引的 CARDINALITY: 1038165，已經很大了。那這個表的估算行是多少呢。show table status like b \G
*************************** 1. row ***************************
Name: b
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1038165 Avg_row_length: 114
Data_length: 119160832
Max_data_length: 0
Index_length: 109953024
Data_free: 5242880
Auto_increment: NULL
Create_time: 2014-05-23 00:24:25
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
從 Rows: 1038165 看出，IX_S 這個索引的區分度被認為非常好，已經近似于唯一索引。MySQL Tips: 在 show table status 結果中看到的 Rows 用于表示表的當前行數。對于 MyISAM 表這是一個精確值，但對 InnoDB 這是個估算值。 雖然是估算值，但優化器是以此為指導的，也就是說，上面的某個 explain 里面的數據完全不符合期望：staight_join 結果中第二行的 rows。目前為止
我們發現整個錯誤的邏輯是這樣的：以 a 為驅動表的執行計劃，由于索引 b.S 的 rows 估計為 1038165 導致優化器認為代價大于以 b 為驅動表。而實際上這個索引的區分度為 1.（當然對 explan 結果比較熟悉的同學會發現，第二行的 type 字段和 Extra 字段一起詭異了 )
也就是說，straight_join 得到的每一行去 b 中查詢的時候，都走了全表掃描。在 MySQL 里面出現這種情況的最常見的是類型轉換。比如一個字符串字段，雖然包含的是全數字，但查詢的時候傳入的不是字符串格式。在這個 case 里面，兩個都是字符串。因此，就是字符集相關了?；氐絻蓚€表結構，發現 S 字段的聲明差別在于 COLLATE utf8_bin -- 這個就是本 case 的根本原因了：a 表得到的 S 值是 utf8_bin，優化器認為類型不同，無法直接用上索引 b.IX_S 過濾。至于為什么還會用上索引，這個是因為覆蓋索引帶來“誤解”。MySQL Tips：若查詢的所有結果能夠從某個索引完全得到，則會優先用遍歷索引替代遍歷數據。 作為驗證，mysql explain SELECT * FROM a straight_JOIN b ON binary a.`S` = b.`S` WHERE a.`L` 2014-03-30 00:55:00 AND a.`L` 2014-03-30 01:00:00 ;
+—-+————-+——-+——-+—————+——+———+——+———+————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——+———+——+———+————————————————+
| 1 | SIMPLE | a | range | IX_L | IX_L | 4 | NULL | 63 | Using where |
| 1 | SIMPLE | b | ALL | IX_S | NULL | NULL | NULL | 1038165 | Range checked for each record (index map: 0x4) |
+—-+————-+——-+——-+—————+——+———+——+———+————————————————+
由于結果是 select *，無法使用覆蓋索引，因此第二行的 key 就顯示為 NULL. (筆者淚：要是早出這個結果查起來可方便多了）。
優化
當然最直接的想法就是修改兩個表的 S 字段的定義，改成相同即可。這個方法可以避免修改業務代碼，但 DDL 代價略大。這里提供兩種在 SQL 語句方面的優化。1、select count(*) from b join (select s from a WHERE a.`L` 2014-03-30 00:55:00 AND a.`L` 2014-03-30 01:00:00) ta on b.S=ta.s;
這個寫法比較直觀，需要注意最后 b.S 和 ta.S 的順序
2、SELECT count(*) FROM a JOIN b ON binary a.`S` = b.`S` WHERE a.`L` 2014-03-30 00:55:00 AND a.`L` 2014-03-30 01:00:00 ;
從前面的分析知道是由于 b.S 定義為 utf8_bin.
MySQL Tips: MySQL 中字符集命名規則中, XXX_bin 與 XXX 的區別為大小寫是否敏感。這里我們將 A.s 全部增加 binary 限定，先轉為小寫，就是將臨時結果集轉成 utf8_bin，之后使用 b.S 匹配時就能夠直接利用索引。其實兩個改寫方法的本質相同，區別是寫法 1 是隱式轉換。理論上說寫法 2 速度更快些。以上是“mysql 中慢查詢優化的示例分析”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業資訊頻道！
正文完
