记一次 mysql 重构优化

前言

比较长一段时间没有写博客了,倒不是因为懒,主要现在工作主要内容CRUD居多,虽然现在接受的项目和 ElasticSearch 相关,但是感觉相关的内容在 ES 的手册都找的到,没必要当个复读机把这些问题都记录下来,所以博客这边还是写一点一般途径找不到的东西。这次分享的内容和一次后台页面查询优化相关,觉得可以讨论下,刚好最近也在和同学讨论 Mysql 相关的东西,于是记录下,权当分享了。这里顺便预告下,最近在看 《七周七并发编程》 与 《七周七理解多语言编程范型》 接下来的博客可能讨论这两本书相关的内容,顺便也推荐下这两本书,把这两本当做增长见识,理解一些语言特性来源很不错。

问题复盘

起因

本次重构优化的起因是后台的一个慢查询,和之前 Redis 那次优化的起因一样,因为之前负责这个项目的哥们写代码时用户量和数据量不是很多,所以整个页面可以很好的运行,但是随着后来数据的增长之前写的慢查询问题便暴露出来了

情况描述

当前后台有一个统计用户信息的页面,用于记录一部分用户相关的信息,因为是一部分用户,所以该表的自增主键 id 没有实际作用,有一个单独的字段用于记录在主表的用户 id ,需要展示的大部分信息也保存在主表内,此外还 Join 了另外两张信息表用于显示,此外还有一次视图查询用到了 group by id 来统计某项数据,也就是统计这项用户数据出现了问题。这几张表用于关联的字段都加上了聚簇索引也就是 innoDB 的索引,索引类型为 B+tree (非hash索引),用于显示用户表的数量在 1M 级别,用于信息显示的主用户表数量在 10M 级别且增长迅速。

重构分析

在接到需求的时候我先用了 Mysqlexplain 语句分析了下页面使用的查询语句,发现查询关联的几个信息表的时候是部分表查询,但是在子视图的时候是因为使用了 groupBy 语句 使用了全表查询, groupBy 生成的子视图的总数量在 1M 左右,这也是导致页面打开速度过慢的原因。

重构方案

在分析代码之前我考虑过用 Redis 保存 groupBy 的数据,设置一个时间锁,每次过期便重新生成,但是 group by 生成的数据需要排序,且每次查询结果生成的数据量很大,在代码逻辑层进行排序很有可能导致内存溢出等问题,同时我也觉得对1M级的数据进行排序是一件特别蠢的事情(我个人是不认为我写的排序能比写开源轮子的社区强),同时, Redis 在使用的时候还要考虑网络IO等问题,放到 Redis 内进行保存并不会比之前要强多少。
问题还是回到了视图查询身上,从需求方了解到:对groupBy的数据的实时性要求不高,且能访问这个页面的管理员权限要求较高,于是我想到了一个分而治之的方法。

重构

因为对数据实时性要求不到(可以允许1天以内的错误),所以我想到了直接使用定时任务,统计需要 groupBy 的数据,生成一张新表,只包含 id 和需要显示的数据于是就这么做了,在新表未使用索引的时候,页面显示时间已经由最初的 30S+ 变成了 10S 左右,于是给 id 加上了主键,查询时间变为了2s(这里面包含了用于统计条数的 count 语句 如果改为异步,可能在1s左右),算是完成了需求,之后顺便查看了下生成新表的时间在4S左右,于是在页面加了一个按钮生成新表的数据,使数据更精准了一些。

总结

这次优化算是扫出了我对于 Btree 的一些盲点,之前一直不明便btree的叶子节点为什么要保存为链表形式,为什么聚簇索引要保存所有信息,为什么加索引索引的字段最好为连续递增的Int字段,为什么要尽量避免groupBy语句的出现,一切的原因都是因为 Mysql 底层数据保存的形式,b+tree 可以更快的寻址查找磁盘的内容,同时Join可以更快的返回查询结果,为的都是在查询的时候尽量减少时间 (时间复杂度O(log(n)+m) 写了很久的CRUD都把Mysql当做一个黑箱程序来使用,真正了解了它的数据结构和算法才发现它的智慧所在。