请教一条 mysql 慢查询问题

[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 时慢呢？

[jnduan]

看见 where 1 = 1 就头大

[MoYi123]

如果有非常大量相同的 mchcode 话，有可能会选择 create_time 索引。所以 reindex 一下看看吧。

[ZehaiZhang]

@jnduan 应该是业务代码拼接的原因吧

[smilepig]

查询的时候不一定用的是 explain 给的索引，如果不是一个实例的话；
用 create time 慢我觉得和 limit 有关；

[goobai]

试一试联合索引

[smilepig]

说错了，create time 慢可能和 order by，limit 有关，记得之前遇到过一次索引失效的问题

[GGGG430]

首先该一下你的 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]

@smilepig 我把 order by create_time limit 1 去掉后，查询就很快，然后 explain 了下，这时走了 mchcode 索引。
所以问题就是，为何 create_time 上加了索引，然后 order by create_time，就变慢了

[Sasasu]

索引用的是 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]

你自己按 time 排序并 limit 1 复杂度 log n, 这种事就别让给数据库干

[zuiye111]

@GGGG430 我明白你的意思，尽量用联合索引，但这里还有个背景，业务方有很多个，并不都是通过这种方式查的，有的业务方可能不传 user_id，有的业务方只传 mchcode，所以这里单独建了索引

[lasuar]

SQL 优化建议：
1、or 关键字相邻字段必须建立索引（尽量不使用 or ）
2、尽量不要在 where 语句汇中将条件字段进行运算（如函数调用、位运算等），否则不会用到索引
还有就是，你应该把排序去掉之后再 explain。

[GGGG430]

@zuiye111 如果是这样的话, 你仍然可以用我上面那个 sql, 只不过那个联合索引改成多个单列索引或者多个较小的联合索引, 但是这个(order_property_bit & 128)=128 语句一定要移出来在业务层处理, 这个会导致很多索引用不上, 而移出这行也就要导致移出后面的 order by 到业务层面处理

[Sasasu]

(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]

@Sasasu 如阁下所说，只要是 order by xxx 形式，即使 xxx 上建了索引，仍然有可能会扫全表？那么，什么情况会是你说的最好复杂的 1，什么情况下最坏扫全表？
其次，同样的 sql，explain 时，为何有时索引用 create_time，有时又用 mchcode? （大部分情况确实用的 mchcode 索引）我知道这个是优化器的一个策略，但为何会算出不同的策略呢？

[zuiye111]

@GGGG430 嗯，感谢指导，这些道理我也明白，尽量不要把运算搞到 sql 中，但这里的问题不是因为这个运算引起的，因为换使用 mchcode 索引时，查询仍然很快，使用 create_time 就很慢

[Sasasu]

order by xxx，xxx 上有索引所以 order by 不会触发排序

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

xxx 上的索引扫到最后 limit 1 还得不到足够的结果就是最坏情况，扫全表

[zuiye111]

@lasuar 为啥要把排序去掉再 explain? 这样 explain 出来的结果，对原 sql 有指导意义吗

[GGGG430]

@zuiye111 很显然使用 mchcode 索引时(其`选择性`很大), 存储引擎该索引筛选出的结果集很小了啊, 而 create_time 并没有筛除任何数据啊, 你没看到用 create_time 索引时 filtered 这列为 0 吗? 你再看看用 mchcode 索引时, 这一列应该有几十了

[zuiye111]

@Sasasu 按你的解释，那我这 sql，由于有 filter，如果 mysql 使用了 create_time 索引，那必定会扫全表了？因为必须得一条一条的按 filter 过滤