【已解決】Mysql復合索引字段順序的問題

wangjj20

表test如下：
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| pid         | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| tid         | mediumint( unsigned | NO   | MUL | 0       |                |
| showtime    | int(11)               | YES  |     | 0       |                |

數(shù)據(jù)量200w+

原來有個復合索引建立在tid和showtime上的。
現(xiàn)有個sql
SELECT * FROM test  WHERE AND tid='47992' AND pid >=1660250 and showtime>0  ORDER BY p.showtime,p.pid LIMIT 0, 40;
于是將索引修改如下（tid為最左前綴不能改，有別的sql用到tid+showtime）：
ALTER TABLE test ADD INDEX idx_tid_showtime_pid(tid,showtime,pid);
執(zhí)行sql效率很低，請看explain
+----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+
| id | select_type | table          | type  | possible_keys        | key                  | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | test            | range | idx_tid_showtime_pid | idx_tid_showtime_pid | 8       | NULL | 123444 | Using where |
+----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+

感覺效率依然很低，通過slow-log記錄可以看到執(zhí)行時間在0.7秒以上。
測試了下去掉showtime條件后只使用tid和pid的復合索引效率是很高的，于是再次修改索引：
ALTER TABLE test ADD INDEX idx_tid_pid_showtime(tid,pid,showtime);
+----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys                                                                         | key                  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | test     | range | idx_tid_showtime_pid,idx_tid_pid_showtime | idx_tid_pid_showtime | 12      | NULL | 3290 | Using where; Using filesort |
+----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+------
這樣效率大大提高。

以上問題，從我個人理解上是這樣的，idx_tid_showtime_pid(tid,showtime,pid)這條索引滿足了使用索引order by的條件，所以需要掃描123444行數(shù)據(jù)。
而 idx_tid_pid_showtime(tid,pid,showtime)這條索引的列順序和order by的順序不一樣，排序無法使用索引，而where條件使用索引效率很好，只掃描了3290行數(shù)據(jù)，即使加上排序的時間也很快。

不知道真實情況是否這樣，求大神給出正確解釋。

cenalulu

樓主理解是正確的。
情況1的結果是 tid過濾完以后按照showtime,pid順序一個個檢查 pid的值是不是> 1660250 （從執(zhí)行結果來看需要掃描12萬行左右才能得到結果
情況2 的結果是 tid過濾完以后，繼續(xù)用pid索引過濾 > 1660250， 最后再進行排序。（從執(zhí)行計劃來看只需對2000行進行排序）

wangjj20

回復 2# cenalulu
感謝解答！
看來加索引這事還真的經(jīng)過實際的測試，如果tid+pid過濾后的數(shù)據(jù)大于123444，效率應該比不上第一條索引了。。

ruochen

索引就是個平衡的藝術

d414491305

為什么會趕不上第一條索引 搞不懂~回復 3# wangjj20