New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

踩到一个MySQL的坑,它选择了错误的索引 #5

Open
ericdum opened this Issue Nov 23, 2013 · 1 comment

Comments

Projects
None yet
2 participants
@ericdum
Owner

ericdum commented Nov 23, 2013

刚才收到服务器报警,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

This comment has been minimized.

Show comment
Hide comment
@jonneyzhang

jonneyzhang commented Aug 13, 2015

非常棒

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment