谨防掉坑 Mysql where条件+order By + limit 导致慢查询

问题描述

公司项目 Mysql偶尔报警，数据库超过10S没返回，导致客户端连接失败。

数据库表的数据量

700W+

建表语句

CREATE TABLE `basic_sku_detail` (
	`sku_id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`company_id` BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0' COMMENT '货主ID',
	`company_code` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '货主编码',
	`sku_code` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT 'SKU编号',
	PRIMARY KEY ( `sku_id` ) USING BTREE,
	UNIQUE KEY `uk_company_code_sku_no` ( `company_code`, `sku_code` ) USING BTREE,
UNIQUE KEY `uk_company_id_sku_no` ( `company_id`, `sku_code` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 8346825 DEFAULT CHARSET = utf8 COMMENT = '商品表'

报错信息

“ERROR: 2022-09-02 17:39:36 logId[764529163465179209] thread[workercoordinator.cronjob-task-worker-0] com.alibaba.druid.pool.DruidPooledStatement - CommunicationsException, druid version 1.1.23, jdbcUrl : jdbc:mysql://127.0.0.1:3306/oms_base?characterEncoding=utf8&allowMultiQueries=true&useUnicode=true&autoReconnect=true&useAffectedRows=true&useSSL=false&useTimezone=true&serverTimezone=GMT%2B8&connectTimeout=6000&socketTimeout=10000, testWhileIdle true, idle millis 10075, minIdle 5, poolingCount 4, timeBetweenEvictionRunsMillis 60000, lastValidIdleMillis 10075, driver com.mysql.jdbc.Driver, exceptionSorter com.alibaba.druid.pool.vendor.MySqlExceptionSorter currentId[f30ace7a4d0c4c86a134d664179c6bf2]”

lastValidIdleMillis 10075 表示上一次连接已经是10S前，而从druid配置上能看出来，socketTimeout是10S

客户端和服务器端通信失败

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
Communications link failure

问题跟进

根据logId搜索了详细上下文日志，发现是因为一条sql执行过慢，导致出现了上述异常。

找到真凶

select * from basic_sku_detail 
where company_id = 3 and is_present = 1 and sku_id > 0
order by sku_id asc
limit 0, 13;

把这条sql放到客户端发现果然查询耗时达到了10S以上，那为什么这条sql会执行这么久呢？

问题详解

原因

用explain命令查看一下使用的索引，发现这条sql使用的是主键索引，导致从id为1的数据开始查找，一直到满足where条件的记录才返回，这样必然慢。
expalin图片

返回字段key的值为PRIMARY，该语句走了主键索引。

为何会走主键索引，而没有走二级索引呢？

这跟Mysql的优化器有关。
当where条件和order by条件包含主键 且 limit条件特别小的时候，Mysql会认为选择主键索引能够优化查询和排序操作，并且由于limit特别小，会认为找到符合条件的这几条数据特别容易，所以会认为选择主键索引“成本最小”，就会选择了该索引。
（但是在这里Mysql的这个“认为”并不是正确的，使用其他索引能够极速返回）

解决

知道了原因，问题就好解决了。

方案一：强制索引force index

优点：保证一定会走指定索引。
缺点：项目利用的MyBatisPlus不支持force index语法，需要手写sql。

方案二：破坏三条件之一（不太推荐）

优点：不用手写sql，改造快。
缺点：mysql优化器不太稳，万一哪天根据它的策略又走了主键索引也没辙。

我们的场景是异步分页导出数据，通过条件id>0实现增量查询，order by实现每次查询的顺序一致，导出的数据结果有序，limit实现每次定长查询，避免爆内存（如果剩余要查询的条数大于限定的长度，则limit限定的长度，否则就limit剩余条数。这里恰好剩余条数只有13条）。因此三个条件缺一不可。

方案三：让主键参与运算（推荐）

利用mysql的索引特性：
参与运算的字段不会走索引。
比如这里排序操作可以写成order by id + 0就能解决问题。

优化后：

查询时间由10S变成0.5S

代码实现

QueryWrapper<BasicSkuDetailDAO> listCons = new QueryWrapper<>();
listCons.orderByAsc("sku_id + 0");
listCons.ge("sku_id", 0);
listCons.last("limit 10");
List<BasicSkuDetailDAO> basicSkuDetailDAOS = skuDetailMapper.selectList(listCons);
System.out.println(JsonUtil.toJsonString(basicSkuDetailDAOS));

参考文章：
https://zhuanlan.zhihu.com/p/356463167