MySQL数据库优化

环境介绍

负责开发的系统全省上线使用后，频繁发生宕机问题；起初频繁联系进行数据库扩容，但是无法从根本上解决问题。
系统数据库为MySQL双主(常见有主从、双主)

主从是对主操作数据，从会实时同步数据。反之对从操作，主不会同步数据，还有可能造成数据紊乱，导致主从失效。
主主则是无论对那一台操作，另一个都会同步数据。一般用作高容灾方案

由于频繁遭到投诉，便由我着手分析寻找优化方案

优化后

首先先看一下前后性能对比：

上图为数据库性能报表，业务频繁激增达到顶峰造成数据库宕机
后半部分为优化后的，可以明显看到性能趋于平稳并且可靠。

自6-27日优化后至今（2022-7-10）再无宕机事件发生。

优化思路

1. 梳理业务逻辑
   首先系统为“工单”驱动，数据库操作主要以数据的查询和添加为主，所以关注点应该重点放在查询操作上。根据目前系统使用情况，业务密集集中在图片的拍摄、工单信息查询；
   其中设计到的表大致分为：图片信息表、项目表、工单表、图片-项目关联表、项目-工单关联表；用户表、角色表、用户-角色关联表
   所以一个功能的完成需要多表联合查询居多。
2. 提取代码中的SQL并分析
   以优化的SQL其中之一为例：
   （1）首先提取出代码中的查询SQL，由于服务部署提供方提供的慢SQL查询日志没有参考价值，并且业务逻辑并不庞大繁杂，故自行进行分析：

SHOW PROCESSLIST -- 查看当前运行的数据库进行

使用上述命令，在用户使用时反复执行，可在查询结果的INFO列看到正在执行的SQL，如下图所示：

便将部分频繁出现的SQL拷贝出来进行分析
（2）使用EXPLAIN

可以看到该查询逻辑的SQL，
这个SQL对t_tsf_task是个扫全表的查询，一共8820行
给task表的project_id列加上索引，就可以达到ref级别，大幅提高效率

此类问题并不在少数，添加索引可有效提高。

（3）代码逻辑；我们可以看到业务运行较为平稳，但是仍有高峰出现

根据时间点分析，此功能为导出报表，数据量非常之庞大并且设计表众多，为了提高效率又在后台启用多线程进行查询，所以一旦执行导出，则数据库的处理压力就会上升

（此处设计为生产-消费模式，设计一个队列并且队列中只能存放一个任务，保证每次只能有一个导出任务进行，其他的任务则会等待）

所以在添加索引后，进一步提升性能就需要去深入代码逻辑；此处分析代码逻辑后，优化了查询逻辑，减少了不必要的数据库查询操作，优化后如图所示：

解释：
type
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况下，得保证查询至少达到range级别，最好能达到ref
3. 总结
（1）分析慢SQL，添加索引
（2）优化SQL查询逻辑，减少不必要的联表查询

关于怎么处理MySQL的慢查询？
1、开启慢查询日志，准确定位到哪个sql语句出现了问题
2、分析sql语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写
3、分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引
4、如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。