记一次 mysql 重构优化

前言

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

问题复盘

起因

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

情况描述

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

重构分析

在接到需求的时候我先用了 Mysql 的 explain 语句分析了下页面使用的查询语句，发现查询关联的几个信息表的时候是部分表查询，但是在子视图的时候是因为使用了 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当做一个黑箱程序来使用，真正了解了它的数据结构和算法才发现它的智慧所在。