mysql的sql语句百万级优化示例01

这个优化主要帮一个小伙伴优化的。

一、基本情况说明

大概情况如下：
1、数据量，不算大，175W左右，一般而言，正常的sql语句，mysql在百万以下，基本不用特意去优化，只要不是写的特别烂，都能3s内跑出来。
2、业务需求，有学生表和打卡表，学生表2W左右，打卡表175W左右，并且打卡表增加速度还是蛮快的，一天两万不到的速度增加，所以优化的必要性还是有的，而且目前就已经不满足业务需求了，超时了。具体需求是，统计某天没打卡的学生名单。
3、小伙伴给我的原生sql

SELECT
	stu.* 
FROM
	ttgis_user_student stu 
WHERE
	NOT EXISTS (
	SELECT
		checkin.id 
	FROM
		ttgis_temperature_checkin checkin 
	WHERE
		stu.id = checkin.userId 
		AND checkin.userType = 1 
		AND DATE_FORMAT( checkin.createTime, '%y-%m-%d' ) = DATE_FORMAT( '2021-12-15', '%y-%m-%d' ) 
	) 
	AND stu.stuCollege != '未知学院' 
	AND stu.stuCollege = '材料与化学工程学院' 
ORDER BY
	stu.stuClass ASC

二、sql分析与优化方向

1、第一步，不管啥情况，跑一遍再说，看看多少时间才能跑完。

这里我跑了五六遍吧，每次时间不一样，但大致是6s多一些，要求是至少3s内，最好控制在1s内。（客户要求每页至少50个学生信息，即分页至少50，并且查询后等待时间最多不超过3s，很正常的要求）

2、根据具体逻辑，思考sql语句是否可以优化（这个和第三点，可以换着来，理论上，应该第三点，然后再执行第二点，这里方便说明情况，就先第二点了）
她的想法是，EXISTS打卡表具体时间，即需求里的日期，查出哪天学生打卡了，然后在not反向给出那些没打卡的学生信息。因为反馈的是学生信息，不是打卡信息，那么select返回的，必然是学生表，然后根据学生表和打卡的联系id匹配，最后排序学生班级字段，再分页。学生表驱动打卡表，2w驱动175W，从这个角度来说没啥问题（但打卡表筛选后，并没有175W数据，如果全表扫描啥都不说了，不全表肯定数据量不大），但是因为要求返回学生信息，所以只能学生表去驱动。我和她讨论了一下，基本认为sql没啥大的问题，满足需求的同时没什么重大失误，就定于这个sql上去优化了。

3、查询表是否有索引，如果有，EXPLAIN对应的sql语句，看看是否走了索引，是否有文件排序与中间表之类的。
这里我看了她原来建的打卡表索引，userId 、userType、createTime分别这三个字段建立了单值索引，实际使用，只用了userid这一个索引。在sql不大改的前提下，我想走createTime这个索引，因为这个世界一限制，就到了2W左右打卡量了，数据量下降非常多。这时候我面前两条路，一个是删掉其他的索引，就剩createTime一个索引；二是想办法，保留其他索引的前提下，走createTime索引。这里我不了解她系统逻辑，不会知道其他索引有啥用，随意删肯定不合适，而且userid是学生表的主键，有主键索引在，想删都删不掉，所以我考虑想办法保留其他索引，走createTime索引。

至此，这段sql优化，就变成了，想办法走createTime索引。我问了她，userId 、userType、createTime这几个字段，是固定顺序，并且每次都有，自然而然想到了直接用组合索引即可，于是我直接建立了这几个字段的组合索引（注意建立组合索引的排序和查询排序得一致）。然后我看到了她createTime使用了DATE_FORMAT函数，考虑到函数会导致没法走索引，想都没想直接改了成checkin.createTime >= ‘2021-12-15 00:00:00’ AND checkin.createTime < '2021-12-16 00:00:00’这种大小的范围。最终改动后的sql语句是：

SELECT
	stu.* 
FROM
	ttgis_user_student stu 
WHERE
	NOT EXISTS (
	SELECT
		checkin.id 
	FROM
		ttgis_temperature_checkin checkin 
	WHERE
		stu.id = checkin.userId 
		AND checkin.userType = 1 
		AND checkin.createTime >= '2021-12-15 00:00:00' AND checkin.createTime < '2021-12-16 00:00:00'
	) 
	AND stu.stuCollege != '未知学院' 
	AND stu.stuCollege = '材料与化学工程学院' 
ORDER BY
	stu.stuClass ASC

然后创建了userId 、userType、createTime这几个字段的组合索引。

最终测试跑sql

时间从之前的6s+，到现在的0.06s+，完全符合时间要求，查询数据也一致，至此，这段sql优化算是完成了。

三、遇到了神奇的情况

本来优化完了，没啥问题了，但我闲的时候，又直接把她原来的sql，丢进去跑了一次，居然，也走了索引。。。

然后我人麻了，不是说好的，函数不走索引吗？然后我去百度DATE_FORMAT函数不走索引怎么办，大家都说，改范围（类似我上面那操作），第二种是between…and…，我折腾了半天也没啥线索。

最后偶然看到一个文章，说是，时间查询，限制到了总数据量的六分之一，都会走，我也不知道这个结论是否正确，就我这次优化情况来说，这个说法，好像能站得住脚，哈哈