MySQL优化的一个实战场景
笔者最近在开发的项目有一个告警模块，领导要求将所有的告警信息都记录到数据库，方便日后的追溯。由于告警信息可能比较多，快的话一天就有上百万条数据了，存不了几天查询就会变得很慢很卡，尤其在深分页的时候。基于此就提出了如下几点对MySQL查询的优化。
1. 不含筛选条件的优化
1.1 将数据总量自己维护，不再每次分页的时候都向数据库查询总条数
对于没有筛选条件的查询，每次分页查询的时候，除了查询对应的偏移量数据外，还需要查询总的数据量，这样可以根据总的数据量可以得出总页数。因此所有的分页查询都是两条语句，一条查询总条数，一条查询具体的数据。
由于InnoDB存储引擎无法维护全表的总量（因为InnoDB的MVCC特性导致，可参考《MySQL实战45讲》笔记 ——基础篇 - coderZoe的博客），因此每次执行查询总条数的时候都需要执行一遍全表扫描，在一台普通的服务器上测试当数据量达到200w的时候，光查询select count(*) from tableA就需要耗时4s，这代表每翻一页都需要至少4s的时间。对于这种情况有两种优化方案：
- 不使用InnoDB存储引擎改为MyISAM存储引擎，MyISAM存储引擎会缓存整张表的条数，因此MyISAM的查询总条数会很快。
- 自己维护一张表的数据总量。项目启动的时候先查询一下当前数据库的总条数，每次插入数据库后就更新当前数据的总条数。
目前采用方案2。
1.2 深分页的优化
当数据比较多，分页的页数比较大的时候，涉及到深分页问题，比如limit 10000,10，对于InnoDB存储引擎，它会挨个查询符合条件的行，每查到一条就扔掉一条，接着再查询，直到扔掉9999条数据后，对于符合的第10000条数据才会保留起来，然后再查询10条数据得到10000~1001、0的内容返回。很明显，分页的越深需要查询的条数越多，如果查询的是最后一页，那和全表扫描的性能是一模一样的。
对于不含筛选条件的深分页优化有很多种，这里简单介绍两种：
- 对于主键严格连续递增的数据库表，可以通过主键偏移来代替深分页，如同样是
limit 10000,10，我们先查询当前数据库表的最小id，假设是5，那么limit 10000,10就可以改写为where id between 10005 and 10015或者where id >= 10005 limit 10，这样MySQL扫描的条数就与页号无关了。 - 当分页过深的时候往往代表用户其实是在
“寻找”数据，因此当分页过深时，后端可以直接拒绝请求，而建议用户使用筛选功能来查询自己想要的数据。
目前采用方案1。
2. 包含筛选的优化
2.1 子查询优化
对于筛选语句，如：
select * from table_a where time >= '2022-12-05 00:00:00' limit 10000,10可以优化为：
select * from table_a where id in (
select id from (select id from
table_a
where time >= '2022-12-05 00:00:00'
limit 10000,10) as t
)或者还可以使用连接查询：
select * from table_a as a inner join (
select id from table_a where time >= '2022-12-05 00:00:00' limit 10000,10
)as b on a.id = b.id这主要是利用到了MySQL的索引覆盖，我们对每个要筛选的字段建立索引，这个索引就是非聚簇索引，对于非聚簇索引，除了包含索引列数据外，还有主键信息，因此只select id可以使用索引覆盖，不做回表操作。统一查询出需要的id后再回表得到结果，避免中间数据不必要的回表。
3. 全局的优化
3.1 拆表
告警数据库表在设计的时候，有一个很大的字段告警详情字段，在设计表的时候给这个字段分配的是varchar(10000)类型，很明显对于这种一张表中的大字段会增加查询的负担（包括是MySQL执行器与存储引擎间的负担、MySQL与后端的负担和后端与前端的负担），尤其在筛选和分页的时候。另外，大数据字段的存储也会很占用buffer_pool的使用，导致MySQL不断地刷盘，影响查询效率。基于此就将告警详情字段拆为一张单独的表，每次查询的时候不包含告警详情，当点击某条的时候再去告警详情表查询具体的详细信息。
3.2.增加buffer_pool缓存
增加innodb_buffer_pool_size的大小，由默认的256MB增加到4GB。
