Group by + Limit 的效率优化

背景

最近接手一个项目，清洗历史表中的数据，在原有表中添加一个新的字段，并根据user_id进行分组，查询到证件号、证件类型后，换取唯一编码，将唯一编码存入历史数据中，以达到未来替换user_id的效果。
清洗数据的大体思路，将数据库中带清洗数据分10段，每段交给一条线程负责处理，项目启动后，出现一个问题，查询数据库中的数据出现 timeOut，排查发现是因为 limit m,n 中的m太大，导致数据库扫描的数据块很大，所以出现超时。

优化过程

未优化前

未优化前的SQL

SELECT user_id,id_card,paper_type FROM company_user WHERE person_code = '' GROUP BY user_id LIMIT 500000,100;

执行时常 20s 以上，这是难以接受的

开始优化

查找了网上一些博客分析GROUP BY 与临时表的关系 :
　　1. 如果GROUP BY 的列没有索引,产生临时表.
　　2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
　　3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
　　4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
　　5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
　　6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.
网上搜索得知内联表查询一般的执行过程是：
1、执行FROM语句
2、执行ON过滤
3、添加外部行
4、执行where条件过滤
5、执行group by分组语句
6、执行having
7、select列表
8、执行distinct去重复数据
9、执行order by字句
10、执行limit字句

通过上面的规则分析，尝试减少临时表的出现，让GROUP BY 的列和select 的列相同，先执行GROUP BY 然后再查询，通过子查询使得GROUP BY 先执行，sql如下：

SELECT user_id,id_card,paper_type,realname FROM company_user WHERE user_id IN
(SELECT user_id FROM (SELECT user_id FROM company_user WHERE person_code = '' GROUP BY user_id ) a) LIMIT 500000,100

执行时间从20s缩小到 17s，没有特别大的改善，依然不能接受

尝试将 LIMIT 500000,100 放到，子查询中

SELECT user_id,id_card,paper_type,realname FROM company_user WHERE user_id IN
(SELECT user_id FROM (SELECT user_id FROM company_user WHERE person_code = '' GROUP BY user_id LIMIT 500000,100) a)

执行时间从17s缩小到 15s，没有特别大的改善，依然不能接受

从索引的角度触发，person_code 是个单列索引，user_id是个单列索引，但效果不理想，尝试将person_code 和 user_id 创建组合索引，sql 不变

SELECT user_id,id_card,paper_type,realname FROM company_user WHERE user_id IN
(SELECT user_id FROM (SELECT user_id FROM company_user WHERE person_code = '' GROUP BY user_id LIMIT 500000,100) a)

执行时间从15s缩小到 1.5s，效率提升10倍，对于清洗数据这样对时间要求不是特别高的操作，已经可以接受了。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/83671.html