向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

mysql的where和order by 在一起时，order by 后的索引不能命中。

有这样的问题：
mysql> explain SELECT * FROM product WHERE compID = 120  LIMIT 0,15;
+—-+————-+————+——+——————+———–+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——+——————+———–+———+——-+——+——-+
| 1 | SIMPLE | product | ref | comp_prod,compID | comp_prod | 4 | const | 4 | |
+—-+————-+————+——+——————+———–+———+——-+——+——-+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM product WHERE compID =120  order by id desc LIMIT 0,15;
+—-+————-+————+——-+——————+———+———+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——-+——————+———+———+——+———-+————-+
| 1 | SIMPLE | product | index | comp_prod,compID | PRIMARY | 4 | NULL | 14556379 | Using where |
+—-+————-+————+——-+——————+———+———+——+———-+————-+

其中 id为主键

第二explain显示，order by 后面的索引没有使用到。

##### 搜索的资料###########3

在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行。这些情况包括：

· 对不同的关键字使用ORDER BY：

· SELECT * FROM t1 ORDER BY key1, key2；
· 对关键字的非连续元素使用ORDER BY：

· SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2；
· 混合ASC和DESC：

· SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
· 用于查询行的关键字与ORDER BY中所使用的不相同：

· SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
· 你正联接许多表，并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表。(这是EXPLAIN输出中的没有const联接类型的第1个表）。

· 有不同的ORDER BY和GROUP BY表达式。

· 使用的表索引的类型不能按顺序保存行。例如，对于HEAP表的HASH索引情况即如此。

所以在这里order by key 必然会扫表，你能做的，只能是利用key1，key2的索引。

#####################

经过自己的测试，还可以使用 联合索引。这里 alter table add index prod_comp(id,compID) 。不过需注意索引不宜过多。



来源：http://www.sphinxsearch.org/archives/19

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：http://jackxiang.com/post/3849/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！