踩坑系列——记录一次SQL优化实战

一、背景

定时任务提前N天给用户发送优惠券即将过期短信提醒，一张优惠券仅发送一次，短信提醒成功了就更新已发送标记，下次再次执行定时任务时就不再发送短信。

二、问题

定时任务统计未使用即将过期且未发送短信的数据时仅仅只是一个单表查询，在开发环境单表总数量比较少时看不出问题，但是在验收环境单表总数量150W+时，查询耗时非常久。针对这个慢sql的优化过程做个记录。

三、优化过程

• 第一版：没有索引

select count(1) from user_yhq 
where use_status=1 and is_send = 0 
and expired_time_end >= '2024-01-11 09:00:00' 
and expired_time_end <='2024-01-18 09:00:00'

查看日志这条sql执行耗时3.8s!!!
关于慢sql第一想法是能不能加索引呢，刚好这个表的这三个字段都没加索引于是提sql给这三个字段分别加的索引…

• 第二版：加单个索引

alter table user_yhq 
add index idx_use_status(use_status),
add index idx_is_send(is_send),
add index idx_expired_time_end(expired_time_end);

但是，理想总是美好的，现实又是另一回事了。加完这三个索引后再次执行发现并不明显，执行解释计划发现sql走的use_status索引，并没有走时间的索引。

当时测试表的总数据量为150W+，三个条件同时查询符合条件的数据只有60条，只查符合时间范围的数据为34W+，只查询未使用未发送不带时间范围的数据为83W+，按个人理解如果sql走时间范围查询能筛选更少的数据，那么走时间索引应该会更快，但是mysql并没有选择时间索引。

网上查了下其他人的文章有这样的说法：mysql走不走时间索引要看筛选的数据占比，比如符合条件的结果总数/总数量<10%走索引，如果占比20%mysql认为区分度不明显就走的全表。实际情况这个占比到底多少可能比10%还小，所以会出现时间范围条件的字段加了索引有时有效查询提升较大，有时没效果。
按这个说法我这里有效数据/总数量的占比20%了，也就不会走时间索引。（具体是否这样有待考究了）

既然mysql自己选不走时间索引，那有没有办法让它走时间索引呢？有的。使用force index()可以强制mysql走指定索引。

• 第三版：指定索引

select count(1) from user_yhq force index(索引名)
where use_status=1 and is_send = 0 
and expired_time_end >= '2024-01-11 09:00:00' 
and expired_time_end <='2024-01-18 09:00:00'

这里我指定的索引名就是时间字段加的索引idx_expired_time_end。执行这个sql发现提升很多，耗时0.9s了。
后来大佬看了说：“最好不要用这个，后面别人改了索引名就会导致查询报错”。

确实，这个写法只是临时使用，要想查询效率高长久之计当然还是优化索引，优化sql了。

• 第四版：组合索引

alter table user_yhq add index idx_use_status_send_expired(use_status,is_send,expired_time_end);

删掉前面加的三个单列的索引新加这个组合索引。（如果有排序、分组操作需要注意组合索引多个字段的顺序和sql语句中字段的顺序要保持一致）
再次执行查询，耗时仅0.3s了。

根据耗时来看，显然这个组合索引才是想要的最优方案。

比对每一种方案，同一个sql语句执行效率差别巨大，数据量的多少，sql语句，索引的使用等等因素都有影响，sql性能优化是一个不断尝试不断改进的过程。