小结：这个SQL不能利用 `displayorder` 索引排序的原因是，索引的第二个列 `invisible` 采用范围查询（RANGE），导致没办法继续利用联合索引完成对 `dateline` 字段的排序需求（而如果是 WHERE tid =? AND invisible IN(?, ?) AND dateline =? 这种情况下是完全可以用到整个联合索引的，注意下二者的区别）。
知道了这个原因，相应的优化解决办法也就清晰了：
创建一个新的索引 idx_tid_dateline，它只包括 tid、dateline 两个列即可（根据其他索引的统计信息，item_type 和 item_id 的基数太低，所以没包含在联合索引中。当然了，也可以考虑一并加上）。
我们再来看下采用新的索引后的执行计划：
| mysql> desc SELECT * FROM pre_forum_post WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 15G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: tid,displayorder,first,idx_tid_dateline key: idx_tid_dateline key_len: 3 ref: const rows: 703892 Extra: Using where |
可以看到，之前存在的 Using filesort 消失了，可以通过索引直接完成排序了。
不过，如果该热帖翻到较旧的历史回复时，相应的SQL还是不能使用新的索引：
| mysql> desc SELECT * FROM pre_forum_post WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: tid,displayorder,first,idx_tid_dateline key: displayorder key_len: 3 ref: const rows: 593371 Extra: Using where; Using filesort |
对比下如果建议优化器使用新索引的话，其执行计划是怎样的：
| mysql> desc SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: idx_tid_dateline key: idx_tid_dateline key_len: 3 ref: const rows: 703892 Extra: Using where |
可以看到，因为查询优化器认为后者需要扫描的行数远比前者多了11万多，因此认为前者效率更高。
事实上，在这个例子里，排序的代价更高，因此我们要优先消除排序，所以应该强制使用新的索引，也就是采用后面的执行计划，在相应的程序中指定索引。
最后，我们来看下热帖翻到很老的历史回复时，两个执行计划分别的profiling统计信息对比：
1、采用旧索引（displayorder）：
