请教一条 mysql 慢查询问题

2020-02-22 14:29:49 +08:00
zuiye111  zuiye111
有一张表,大概 180w 条数据,有一条 sql,如下
```
select * from order where 1 = 1 and user_id = 12345 and mchcode = '56789' and (0 or order_state = 2 or order_state = 4 or order_state = 5 ) and (order_property_bit & 128)=128 and channel_type = 2 order by create_time desc LIMIT 1 OFFSET 0;
```
查询花了 5s,我 explain 了下这条 sql,大概是这样的

-------+---------------+-------------+---------+------+------+----------+-------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+-------+-------+-------------+---------+------+------+----------+-------------+
| index | mchcode | create_time | 5 | NULL | 1311 | 0.00 | Using where |
+-------------+-------------------------+------------+-------+---------------+----------

可见,确实用到了索引 create_time,但为何还是这么慢呢?
我的表索引结构大概是这样的
PRIMARY KEY (`auto_id`),
UNIQUE KEY `order_id` (`order_id`),
KEY `user_id` (`user_id`),
KEY `create_time` (`create_time`),
KEY `mchcode` (`mchcode`)

字段:`mchcode` varchar(20) DEFAULT NULL, `create_time` int(11) DEFAULT NULL, mchcode 是字符串,create_time 是时间戳,都建了索引

在查问题过程中,还发现一些很奇怪的问题,就是这条 sql,有时查又很快,快的时候,我 explain 时,发现索引用的是 mchcode,慢的时候,如上,索引用的是 create_time,为何同样一条 sql,会用不同的索引呢,为何用 mchcode 索引查时快,用 create_time 时慢呢?
4988 次点击
所在节点   MySQL  MySQL
45 条回复
jnduan
jnduan
2020-02-22 14:33:18 +08:00
看见 where 1 = 1 就头大
MoYi123
MoYi123
2020-02-22 14:47:04 +08:00
如果有非常大量相同的 mchcode 话,有可能会选择 create_time 索引。所以 reindex 一下看看吧。
ZehaiZhang
ZehaiZhang
2020-02-22 14:55:36 +08:00
@jnduan 应该是业务代码拼接的原因吧
smilepig
smilepig
2020-02-22 15:01:10 +08:00
查询的时候不一定用的是 explain 给的索引,如果不是一个实例的话;
用 create time 慢我觉得和 limit 有关;
goobai
goobai
2020-02-22 15:02:32 +08:00
试一试联合索引
smilepig
smilepig
2020-02-22 15:07:00 +08:00
说错了,create time 慢可能和 order by,limit 有关,记得之前遇到过一次索引失效的问题
GGGG430
GGGG430
2020-02-22 15:11:30 +08:00
首先该一下你的 sql:
select * from order
where user_id = 12345
and mchcode = '56789'
and order_state in (2,4,5)
and channel_type = 2;

然后增加索引(先不不考虑列选择性):
create index idx_xx on order (user_id, mchcode, order_state, channel_type)

最后业务层通过上面的 sql 应该查不来的结果集很小了, 手动代码处理:
(order_property_bit & 128) == 128
order by order by create_time desc LIMIT 1 OFFSET 0;

你试试
zuiye111
zuiye111
2020-02-22 15:16:44 +08:00
@smilepig 我把 order by create_time limit 1 去掉后,查询就很快,然后 explain 了下,这时走了 mchcode 索引。
所以问题就是,为何 create_time 上加了索引,然后 order by create_time,就变慢了
Sasasu
Sasasu
2020-02-22 15:16:51 +08:00
索引用的是 create_time: 因为 order by create_time , 按 create_time 顺序扫出所有的 id 再对每个 id 用 auto_id 找 mchcode user_id sorder_state 等等字段并 limit 1,最好复杂度 1 最坏扫全表

索引用的是 mchcode: 因为 mchcode = '56789',扫所有的 mchdoe= '56789' 的,取出 id 扫 auto_id 找 mchcode user_id sorder_state 等等字段, 按 time 排序 并 limit 1。最好复杂度=最坏复杂度,都是 mchcode = '56789' 的数量

优化器只能在最好复杂度和最坏复杂度之间蒙一个
Sasasu
Sasasu
2020-02-22 15:19:03 +08:00
你自己按 time 排序并 limit 1 复杂度 log n, 这种事就别让给数据库干
zuiye111
zuiye111
2020-02-22 15:19:27 +08:00
@GGGG430 我明白你的意思,尽量用联合索引,但这里还有个背景,业务方有很多个,并不都是通过这种方式查的,有的业务方可能不传 user_id,有的业务方只传 mchcode,所以这里单独建了索引
lasuar
2020-02-22 15:20:41 +08:00
SQL 优化建议:
1、or 关键字相邻字段必须建立索引(尽量不使用 or )
2、尽量不要在 where 语句汇中将条件字段进行运算(如函数调用、位运算等),否则不会用到索引
还有就是,你应该把排序去掉之后再 explain。
GGGG430
2020-02-22 15:24:52 +08:00
@zuiye111 如果是这样的话, 你仍然可以用我上面那个 sql, 只不过那个联合索引改成多个单列索引或者多个较小的联合索引, 但是这个(order_property_bit & 128)=128 语句一定要移出来在业务层处理, 这个会导致很多索引用不上, 而移出这行也就要导致移出后面的 order by 到业务层面处理
Sasasu
2020-02-22 15:28:50 +08:00
(0 or order_state = 2 or order_state = 4 or order_state = 5 ) and (order_property_bit & 128)=128 and channel_type = 2

这里堆经过 code gen 之后就是几个 if 而已,带一个和带一大对没什么区别
zuiye111
2020-02-22 15:28:56 +08:00
@Sasasu 如阁下所说,只要是 order by xxx 形式,即使 xxx 上建了索引,仍然有可能会扫全表?那么,什么情况会是你说的最好复杂的 1,什么情况下最坏扫全表?
其次,同样的 sql,explain 时,为何有时索引用 create_time,有时又用 mchcode? (大部分情况确实用的 mchcode 索引)我知道这个是优化器的一个策略,但为何会算出不同的策略呢?
zuiye111
2020-02-22 15:33:22 +08:00
@GGGG430 嗯,感谢指导,这些道理我也明白,尽量不要把运算搞到 sql 中,但这里的问题不是因为这个运算引起的,因为换使用 mchcode 索引时,查询仍然很快,使用 create_time 就很慢
Sasasu
2020-02-22 15:33:36 +08:00
order by xxx,xxx 上有索引所以 order by 不会触发排序

但是你有 filter, xxx 上的索引不够运行你的 filter,需要按主键(无论你用的是啥)取出你需要的字段做 filter。

xxx 上的索引扫到最后 limit 1 还得不到足够的结果就是最坏情况,扫全表
zuiye111
2020-02-22 15:36:28 +08:00
@lasuar 为啥要把排序去掉再 explain? 这样 explain 出来的结果,对原 sql 有指导意义吗
GGGG430
2020-02-22 15:43:55 +08:00
@zuiye111 很显然使用 mchcode 索引时(其`选择性`很大), 存储引擎该索引筛选出的结果集很小了啊, 而 create_time 并没有筛除任何数据啊, 你没看到用 create_time 索引时 filtered 这列为 0 吗? 你再看看用 mchcode 索引时, 这一列应该有几十了
zuiye111
2020-02-22 15:51:02 +08:00
@Sasasu 按你的解释,那我这 sql,由于有 filter,如果 mysql 使用了 create_time 索引,那必定会扫全表了?因为必须得一条一条的按 filter 过滤

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/646603

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX