一個使用者反映線上一個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速度更快些。

小結

做join的欄位儘量設計為型別完全相同。

慢查詢最佳化及分析

相關文章