踩到一个MySQL的坑，它选择了错误的索引

[ericdum]

刚才收到服务器报警，MySQL连接数量剧增。查了一下日志，发现了一条极为简单地查询，但却耗费了14秒左右的查询时间。

SELECT ... 
FROM ... 
WHERE `user_id` = 000 AND `app` = "xxx" 
ORDER BY `time` 
LIMIT 1;

看了一下这个表的索引，这里user_id、app、time分别有一个索引。由于这个表的可搜索字段非常多，所以这些索引都是只有一层的。

用EXPLAIN解释一下发现这条查询没有使用我们期望的user_id来作为索引，而使用了time。rows为443万。

+----+-------------+--------+-------+------------------------+------------+---------+------+---------+-------------+
| id | select_type | table  | type  | possible_keys    | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+-------+------------------------+------------+---------+------+---------+-------------+
|  1 | SIMPLE      | xxxxxx | range | app,time,user_id | time | 4       | NULL | 4432792 | Using where |
+----+-------------+--------+-------+------------------------+------------+---------+------+---------+-------------+

这就是问题所在了，根据这个解释，这条查询要遍历、比较443万行数据才能返回结果。

做了几个小时的实验，搜了很多关于innoDB/MySQL索引选择的资料都没有很好地解释这个问题。

只是找到了造成选择time做索引的几个必要条件就是：

1. user_id做索引时rows足够大，本例中达到5000
2. 使用time作为排序条件
3. LIMIT足够小，本例中小于等于2

而造成查询过慢的原因还有：

1. 结果为空（本例中需要遍历443万行数据）

我猜测在MySQL的索引优化逻辑中先检测了user_id发现rows为5000，因为要time排序所以必然要遍历这5000行。

然后检查到time虽然有几百万行，但它是有序的，只要找到1、2（limit）个符合规则的结果就可以停止遍历。

所以MySQL认为使用time可能更有效。

对于本例，可以使用一个非常暴力的FORCE INDEX来固定选择user_id做索引。不过这样代码和数据库耦合度太高。

根据业务逻辑，可以换为由id来排序，效果立杆见影，再EXPLAIN一下。

+----+-------------+--------+------+------------------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys    | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | xxxxxx | ref  | app,time,user_id | user_id | 5       | const | 5010 | Using where |
+----+-------------+--------+------+------------------------+---------+---------+-------+------+-------------+

能力有限，先就这样了，有什么发现再来补充。

[jonneyzhang]

非常棒