优化定时任务MySQL数据库超时问题，慎用id主键

优化定时任务MySQL数据库超时问题，慎用id主键

实习过程中遇到了一个定时任务，随着数据的增加，因SQL超时导致任务失败……

---

背景介绍：

任务表索引情况：自增主键id、索引create_time。

定时任务功能：每天将表中的信息状态刷新，例：将时间大于2021.11.1且未过期的任务置为过期。因为根据时间顺序插入数据，因此可认为数据库前半部分任务状态均为已过期；后半部分均为未过期。

原SQL简单粗暴，从id > 0开始，查找到前100条满足条件的记录，然后更新状态，记录最后一条的id；再接着上次查询得到的id查下一百条。

SELECT
  *
FROM
  mission_info
WHERE
  id > 0
  AND create_time > '2021-11-01'
  AND expire_status = 0
LIMIT
  100

结果，这条SQL在数据量增大后耗时达到4700ms，原因：第一次查询要从id = 0查到id = 330w才能找到符合条件的数据：

不过这个逻辑在后续查询是没问题的，每次只取100条只需要10ms，因为从id = 330w 往后的数据均满足条件，只需要顺序读取100条即可。

不管怎样，因为第一次查询远超设定的2s时间上限，势必要优化这个逻辑。

---

超时原因分析：

原因一：坑人的id 主键

第一次查询需要从id = 0 飞跃到 id = 330w，这是一个耗时的过程。在初始条件id > 0下主键显然没任何帮助；只有create_time索引在 create_time > '2021-11-01' 这个条件下能真正帮助定位。

然而进行explain后发现MySQL只走了id主键，忽略了真正有用create_time索引：

假如在第一次查询去掉id > 0，数据库就会走能帮助定位的create_time索引，一下减少了2s的耗时！不过这个结果肯定还是严重超时的。

原因二：单次查询的行数太多

第一次查询时，需要从id = 0 一口气查到 id = 330w，即第一条符合日期且未过期的记录。那可以尝试拆分这次查询，先查询第一条符合日期的记录，再从大于这条记录的id开始查到未过期的记录。

很快啊，啪的一声，只需要10ms就借助create_time索引快速定位到 create_time > ‘2021-11-01’ 的记录，id = 208w。

接下来再从208w的id开始查询未过期的记录（id = 330w）。值得一提的是，此时 create_time > ‘2021-11-01’ 这个条件可有可无，因为208w之后的统统满足这个条件。当然他的存在也不会对查询速度有任何影响，因为这个过程不管是走主键还是走create_time索引都没太大帮助只能遍历过去（都满足条件呀，但有索引还是会快一点的）：

这一次查询还是比较费劲的，耗时1800ms。因为从208w到330w这个过程索引作用非常有限。考虑到压着2秒超时的底线，此方案也不能用。

---

解决方案：

方案一：加索引

给expire_status加上索引就什么事都没了。

方案二：转移数据库层压力到应用层

因为从id = 208w到id = 330w的区间，都满足 create_time > '2021-11-01' 的条件但又已过期，可认为是无效数据。但从原因二可知，从220w跳到330w对数据库的压力太大，因此只能舍弃效率，交给应用层从id = 208w开始，每次无脑取100条数据，再筛选其中未过期的数据：

每次顺序取数据只耗时10ms对数据库非常友好。虽然中间这100w数据都是无用数据（已过期），但毕竟是定时任务不追求效率，所以完全可以接受。

---

总结：

先通过一条 create_time > '2021-11-01' 的查询，耗时10ms，跳跃到id = 208w。

然后从此id开始递增取100条数据筛选并更新。应用层处理的总耗时虽然会远大于数据库层处理但一定是值得的。

耗时简单分析：
数据库处理：耗时参考原因二，消耗不到2s从208w定位到330w；
应用层处理：从208w到330w这个过程，光是考虑取数据的耗时，要取（330w - 208w）/ 100 = 112w次，每次耗时10ms，消耗3分多钟，完全可以接受，也与定时任务实际执行耗时吻合。