平台数据激增破千万下的SQL优化_数据库技术

平台数据激增破千万下的SQL优化

背景

公司一直维护着学生体测平台，最近中标一个项目，从原本零零散散的录入体测数据，骤增到几天内上百万的用户及上千万的体测数据涌入系统，结果各种异常、慢查、连接拒绝，层出不穷...

过多的慢sql会导致数据库CPU飘升、连接无法及时释放、连接池超出限制，原本几毫秒的查询也迟迟无法得到响应，拖垮整个系统，而且系统中使用了消息队列，还导致了消息堆积，重启服务后，服务瞬间又被打满，内存飘升、频繁的full GC...之前也整理过不少sql优化的文章，但没多少用户时，很少去关注，用户骤增时，还是经历了一番“洗礼”，这里记录一下这几天的优化点。

索引使用

索引优化，是一个老生常谈的问题了。项目中，利用云平台或者配合druid搭建慢sql报警机制，可以筛选出执行过慢的语句，然后，再借助explain去分析（有关explain的用法之前做过详细讲解：sql调优之explain关键字详解）。

利用explain，可以快速的分析出缺失的索引，比如检查join或者order by中使用的字段，对于大表，有无索引会有几十甚至上百倍的效率差异。但索引也并非越多越好，过多的索引会对插入及更新造成比较大的影响。

explain之后，type为ALL的，即未使用到索引，多数还是比较容易处理的。这里顺带列举一些常见的索引失效场景：

未遵循最左前缀匹配导致索引失效

使用函数导致索引失效

select * from student where name = left('云端行笔666',4)。

计算导致索引失效

select * from student where id + 1 = 666。

类型转换导致索引失效

select * from student where convert(id,char) = '666'。

不等于(!= 或者<>)索引失效

select * from student where name != "云端行笔"。

like模糊匹配以通配符开头导致索引失效

select * from student where name like "%云端行笔"。

索引字段使用is not null导致失效

select * from student where name is not null。

OR前后存在非索引的列，索引失效

select * from student where id =1 or name = '云端'。

这里列举两个此次优化的示例：

示例一：find_in_set

业务中有这样一个场景，上级需要看到其所有下级的数据，如省及单位需要能查看其下所有市区县的学校。为了方便，之前将学生所隶属的学校以及其上级省市区单位，记录在一个字段sponsor_ids中，然后通过find_in_set查询，如下：

SELECT grade FROM enroll 
WHERE match_id = 60 AND FIND_IN_SET(36, sponsor_ids) 
GROUP BY grade ORDER BY grade

这样设计，代码写起来很方便，但数据量级大是，查询效率极其低下，因为find_in_set无法使用索引。

优化：事先查询所属下级，然后通过in查询使用索引

SELECT
	s.id
FROM
( SELECT * FROM sponsor WHERE `status` = 1 AND  superior_id IS NOT NULL order by level asc ) s,
  ( SELECT @pid := #{sponsorId} ) pd
WHERE
FIND_IN_SET( superior_id, @pid ) != 0
AND @pid := concat( @pid, ',', id )

SELECT grade FROM enroll 
WHERE match_id = 60 AND  sponsor_id in (36) 
GROUP BY grade ORDER BY grade

虽然在查询下级机构时，也使用了find_in_set，但机构表数据量有限，全表扫也无太大压力，而enroll报名表使用in查询后，效率明显提升，由几秒提升到几十毫秒。

示例二：联合索引

还有一些其他情况，比如：

SELECT sponsor_id, count(sponsor_id) AS sponsor_count FROM enroll 
WHERE match_id = 60 
GROUP BY sponsor_id

这句sql，是用于统计某次体测活动中，各个学校的报名人数。设计表时在match_id，sponsor_id上，分别建立的索引，报名人数不多时，效率还可以。但报名人数几十万之后，发现查询效率明显下降，需要几秒钟。explain分析如下：

平台数据激增破千万下的SQL优化

从上述的分析可以看出，查询使用了索引，但只用到了match_id这一个索引，而extra中显示了using temporary，即使用了中间表进行分组，并未用到索引，数据量达到一定量级后，中间表也会很大，效率自然也就降低了。

为此，针对该查询，建立了match_id和sponsor_id的联合索引，explain发现，不在使用中间表，实际查询效率也明显提升，大概几百毫秒。（使用到覆盖索引，不需要回表查询）

平台数据激增破千万下的SQL优化

批量插入与更新（避免循环单条插入）

批量操作，是业务中很常见的，比如批量导入学生，简单粗暴的一种做法就是，for循环，然后在循环中insert，如：

for (int i = 0; i < 50000; i++){
  Student student = new Student("云端行笔" + i,24,"北京市" + i,i + "号");
  studentMapper.insert(student);
}

<insert   parameterType="com.peng.Student">
  INSERT INTO TEST(ID,Student) VALUES(#{id},#{student});
</insert>

当插入百八十条数据时，不会觉得效有多低，但当插入上万条数据时，循环插入可能需要一两分钟甚至更久，这就无法忍受了。正确的打开方式：

<insert   parameterType="java.util.List">
  INSERT INTO TEST(ID,Student)
VALUES
  <foreach collection="list" item="item" index="index" separator="," >
    (#{item.id},#{item.student})
    </foreach>
</insert>

避免全表

业务中有这样一个需求，导入学生时，需要对比学生编号是否有重复。之前的实现方式是将student表中的所有数据，全都查出来，加载到内存，在内存中与导入的数据逐条对比。上百万的数据，而且还是select *，结果可想而知...

这种扫全表、select *的做法，回头来看，其实是很初级的，但实际开发中，很多时候，为了图省事儿，可能就随手把坑埋下去了...

无能为力的SQL

业务中有这样一个场景：学生参与体测活动，需要报名，因此，除了student学生表之外，还有一张enroll报名表，用于记录学生在多个体测活动中的报名状态。业务中需要统计所有未报名的学校，思路很简单，就是学生表和报名表取差集，然后统计差集中有哪些学校，SQL如下：

select distinct(s.sponsor_id) from student s
LEFT JOIN enroll e on e.student_id = s.id
where s.status = 1 and e.id is null

问题在于，一个地区会有几百万甚至上千万的学生，如此庞大的两张表取交集，太慢了.，优化好久也没思路...（欢迎评论区指点迷津）。SQL无力了，最终选择，将未报名的学校数据提前统计计算，放在缓存之中，有学生状态或者报名状态发生变更，则去更新。该思路也是类似于数据平台，千万级别的数据，实时统计，肯定会有效率问题，因此，往往会在数据服务中非实时计算。