背景
由于我们在21年年末时线上举办了一次翻译比赛, 在晚间7点开赛后, 大量用户同时开始比赛瞬间涌入了服务器, 导致MYSQL产生超过10秒多的慢查询, 内存和CPU占用全部达到100%, 线上服务直接全部瘫痪, 由于MYSQL和SERVER服务都部署在k8s上, 我们通过skyworking链路定位到了一个奇怪的位置
业务代码
首先我们还是模拟一张简易的数据表
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`enabled` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '物理有效标志',
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '名称',
`classNumber` int(10) NOT NULL COMMENT '班级编号',
`score` int(5) NOT NULL COMMENT '成绩',
PRIMARY KEY (`id`) USING BTREE,
INDEX `I_classNumber`(`classNumber`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
具体业务代码如下
逻辑1
逻辑2
逻辑3: 聚合统计指定班级编号的学生
逻辑4
在聚合统计指定班级编号的学生, 及执行如下SQL时产生了慢查询
select count(*) from student where classNumber = 5 and enabled = 1;
临时补救
基于时间问题, 我们临时在student表上针对'enabled'字段建立普通索引'I_enabled', 索引建立后线上MYSQL峰值瞬间降低, 没有再产生任何慢查询
问题分析
我们先模拟线上数据, 插入6W条学生记录, 其中5W记录的班级编号都为5
问题复现
执行SQL分析
explain select count(*) from student where classNumber = 5 and enabled = 1;
获得以下结果
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | student | NULL | ref | I_classNumber | I_classNumber | const | Using where |
很明显使用了我们创建好了的'I_classNumber'索引, 在本地单次执行count后, 响应时间为0.2秒, 速度感觉还是可以的, 但当我们使用了Jmeter进行压测, 模拟100个并发, 循环10次后, 得到平均响应时间为2.8秒, 同时最大值超过了7秒
添加索引后结果
针对'enabled'字段建立普通索引'I_enabled', 执行SQL分析
explain select count(*) from student where classNumber = 5 and enabled = 1;
获得以下结果
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | student | NULL | index_merge | I_classNumber, I_enabled | I_classNumber, I_enabled | NULL | Using intersect(I_classNumber, I_enabled); Using where; Using index |
使用了Jmeter进行压测, 模拟100个并发, 循环10次后, 得到平均响应时间为0.9秒, 最大值约为2秒
结果对比
很明显, 在补充索引后速度得到了明显得提升, 通过explain得知type类型由ref转为了index_merge, 理论上来说是type的效率如下:
system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all
- ref: 非唯一性索引扫描，返回匹配某个单独值的所有行
- index_merge: 对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)
ref得效率是明显优于index_merge, 但是此时结果却相反
结论
- 原先: MYSQL通过I_classNumber索引查询到5W个主键ID, 之后回表获取对应全量数据, 再经过MYSQL的server进行过滤, 此步骤占用了较多内存和CPU, 之后进行count计算
- 变更后: MYSQL通过I_classNumber索引得到5W个主键ID, 通过I_enabled索引得到约5W2的主键ID, 之后计算获得主键ID交集, 然后回表count计算
思考
- 问题是解决了, 但是做法依旧存在问题, 索引合并的效率其实是比较低的, 从结论来看, 依旧是有回表的阶段
- 当数据膨胀至40W时, 再次进行压测, 得到平均响应时间为2.1秒, 最大值约为4秒, 过程为MYSQL通过I_classNumber索引得到5W个主键ID, 通过I_enabled索引得到约30W的主键ID, 之后计算获得交集, 后者命中的主键ID数量大大增加, 导致了在计算交集时产生了更多的内存与计算时间
- 最后利用索引覆盖, 增设联合索引I_classNumber_enabled, 再进行压测, 得到了最完美得数据, 平均响应时间为0.06秒
