mysql 中where 和 order by 的索引优化

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) 。不过需注意索引不宜过多。

分享到QQ空间

Comments are closed.