请教一条 mysql 慢查询问题

有一张表，大概 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 时慢呢？

mchcode

key

索引

SQL

45 条回复 • 2020-02-23 14:47:54 +08:00

jnduan

2020-02-22 14:33:18 +08:00

看见 where 1 = 1 就头大

MoYi123

2020-02-22 14:47:04 +08:00

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

ZehaiZhang

2020-02-22 14:55:36 +08:00

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

smilepig

2020-02-22 15:01:10 +08:00

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

goobai

2020-02-22 15:02:32 +08:00 via Android

试一试联合索引

smilepig

2020-02-22 15:07:00 +08:00

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

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

2020-02-22 15:16:44 +08:00

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

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

2020-02-22 15:19:03 +08:00

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

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 过滤

Sasasu

2020-02-22 15:54:00 +08:00

过滤到一个 sql 就结束，比扫全表稍微好一点点

zuiye111

2020-02-22 15:54:29 +08:00

@GGGG430 你说的有点问题，explain 的这个 filtered 字段，是百分比。。。当用 mchcode 索引时，这列是 0.27

GGGG430

2020-02-22 15:56:46 +08:00

@zuiye111 是百分比啊, 存储引擎返回的数据在 server 层过滤后, 剩下多少满足查询的记录数量的比例
你能贴一下当用 mchcode 索引时的 explain 记录吗, 看一下 rows 这列值

zuiye111

2020-02-22 15:57:35 +08:00

@Sasasu 哦好像明白了，因为有 limit 1，所以过滤到一个就结束，假如没有 limit 1，索引又是 create_time，估计会扫全表，或者根本就没有满足 filter 条件的数据，也会扫全表。是这个道理吗？

zuiye111

2020-02-22 15:59:07 +08:00

@GGGG430 这是用 mchcode 时的 explain
------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+
type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+
ref | mchcode | mchcode | 63 | const | 1312 | 0.27 | Using index condition; Using where; Using filesort |
------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+

GGGG430

2020-02-22 16:00:27 +08:00

哪来的扫全表呢, rows: 预估需要扫描的行数, 你的值在使用 create_time 索引时才 1131

zuiye111

2020-02-22 16:05:20 +08:00

@GGGG430 嗯，这里我也有这个疑惑，用 mchcode 索引或者 create_time 索引，rows 都差不多，但查询时间相差巨大，所以解读 explain 时，关键是看哪个字段？ ref ？ rows ？

Sasasu

2020-02-22 16:06:26 +08:00

Using filesort 看来 mysql 选择了对时间做外排序，复杂度 nlogn，实际上只选出几个元素有 logn 的算法的。或许 limit 1 优化器没利用上

> rows: 预估需要扫描的行数, 你的值在使用 create_time 索引时才 1131

最好情况扫 1 行，最坏情况扫 180w 行，mysql 的神奇优化器预估扫 1131 行就行了，但实际执行估计扫了十几万行

GGGG430

2020-02-22 16:10:31 +08:00

我建议直接强制使用两种索引的情况下,分别打印一下时间耗在哪里, 一会贴出来看看
set profiling = 1;
select * from order ... force index (xxx);
show profiles;

GGGG430

2020-02-22 16:13:06 +08:00

@zuiye111 另外, filtered 这个值范围是 0-100, 你理解的 0-1 是错的

zuiye111

2020-02-22 16:16:57 +08:00

@Sasasu 使用 create_time 时，是 1311 哦
最好情况下扫 1 行，正好 create_time 最大的那行，满足 filter 条件。最坏的情况，扫到 create_time 最小的那行，还是不满足 filter 条件，是这样吗？
然后 rows 的值，只有参考意义，但实际扫了多少行，是不知道的，是这样吗？

zuiye111

2020-02-22 16:21:24 +08:00

@GGGG430 嗯，这个是线上生产哦，不敢随便乱搞。。。
force index 可以强制让 mysql 使用哪个索引，单纯测试可以用，但不是这里的解放办法

GGGG430

2020-02-22 16:22:01 +08:00

第一列 type:
ref: 使用非唯一索引(即非 unique,primary key)扫描或唯一索引的前缀扫描,
index: 索引全扫描, MySQL 遍历整个索引来查询匹配的行

这个估计表明 create_time 了扫描了过多的索引,

另外我觉得纠结这些没必要, 就像上面说的, 优化你的 sql 才是关键

GGGG430

2020-02-22 16:24:40 +08:00

force index 只是测试用一下, 只对当前 sql 有效, 不影响其他语句的

goodboy95

2020-02-22 16:26:08 +08:00

@jnduan where 1=1 猜测是系统的权限模块搞的鬼，因为我们系统就用 where 1=1 或者 1=-1 来控制用户能不能拿到数据……

zuiye111

2020-02-22 16:28:52 +08:00

慢的问题大概清楚了，但还有个问题不太清楚
同一条 sql，explain 时，为何有时用了 create_time 索引，有时又用 mchcode 索引？

zuiye111

2020-02-22 16:31:44 +08:00

@goodboy95 不用想那么复杂哈，这里就是写 sql 时为了拼接，仅此而已

GGGG430

2020-02-22 16:52:14 +08:00

msyql 优化器会根据非常多的优化策略后, 最终决定当前使用索引是哪个, 且线上数据随时都在变, 也会影响最终实际使用的索引, 这个估计一般的 dba 都没法回答你

jnduan

2020-02-22 17:26:10 +08:00

@goodboy95 何苦把这种判断下沉到 SQL 这一层面……本应该是业务层解决的问题……

gy123

2020-02-22 17:30:21 +08:00

1.where 最左原则
2.union all 代替 or
3.应用层解决
以上建议~

akira

2020-02-22 23:59:46 +08:00

order_property_bit & 128 这种用法不建议。如果确实必要，那还不如额外建个列来保存这个数值。

qza1212

2020-02-23 03:49:02 +08:00

#2 楼提到了问题的关键

mysql 查询优化器要么用 mchcode 索引要么用 create_time 索引（假如 where 只考虑 mchcode ）

1 如果用 mchcode 索引：从 mchcode 索引树里拿到所有满足条件的主键->回表->排序拿到第一个
优点是用到 mchcode 索引，查询比较快缺点是要排序

2 如果用 create_time 索引：对 create_time 索引树扫全表->每次都要回表判断 where 条件->一旦满足条件直接返回
优点是不用额外排序了，缺点是 where 条件过滤无法用到索引，只能全表扫描

所以如果 mchcode 过滤之后结果非常多，导致额外的排序非常耗时那么 2 比较好，反之 1 比较好
查询优化器根本无法判断，这种只有执行了才知道

sansanhehe

2020-02-23 09:03:12 +08:00

建立多列索引不香吗：user_id + mchcode + createtime

encro

2020-02-23 10:19:53 +08:00

1，大多数情况 create_time 索引不是需要的。。。要也是放在组合索引末尾，除非你前台大量 between 类查询。
2，Or 查询和 in 查询不能很好利用组合索引；
3, order_property_bit & 128 不能很好利用索引；
4，查询优化器自动预估，不知道你的 possible_keys 为什么没有 user_id

zuiye111

2020-02-23 14:47:54 +08:00

感谢上述各位大佬解答，很有参考意义