一次復合索引的調整
表test如下：
| Field | Type | Null | Key | Default | Extra |
+————-+———————–+——+—–+———+—————-+
| pid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| tid | mediumint(8) unsigned | NO | MUL | 0 | |
| showtime | int(11) | YES | | 0 | |
www.aiwalls.com
數據量200w+
原來有個復合索引建立在tid和showtime上的。
現有個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);
執行sql效率很低，請看explain www.aiwalls.com
+—-+————-+—————-+——-+———————-+———————-+———+——+——–+————-+
| 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記錄可以看到執行時間在0.7秒以上。
測試瞭下去掉showtime條件後隻使用tid和pid的復合索引效率是很高的，於是再次修改索引：
ALTER TABLE test ADD INDEX idx_tid_pid_showtime(tid,pid,showtime);
這樣效率大大提高,執行時間降到瞭0.1以下。 www.aiwalls.com
+—-+————-+——-+——-+—————————————————————————————+———————-+———+——+——+—————————–+
| 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行數據。
而 idx_tid_pid_showtime(tid,pid,showtime)這條索引的列順序和order by的順序不一樣，排序無法使用索引，而where條件使用索引效率很好，隻掃描瞭3290行數據，即使加上排序的時間也很快。
由此可見索引的添加也不能一味的以字段順序去適應排序的順序，雖然省略瞭排序所花的時間，但是在其他的開銷上遠大於少量數據排序帶來的影響。