请教一条 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 时慢呢？

[Sasasu]

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

[zuiye111]

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

[GGGG430]

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

[zuiye111]

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

[zuiye111]

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

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

[zuiye111]

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

[Sasasu]

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

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

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

[GGGG430]

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

[GGGG430]

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

[zuiye111]

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

[zuiye111]

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

[GGGG430]

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

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

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

[GGGG430]

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

[goodboy95]

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

[zuiye111]

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

[zuiye111]

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

[GGGG430]

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

[jnduan]

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

[gy123]

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