AMI 项目数据库优化方案

author：肖博

createTime：2021-04-27

updateTime：

项目的优化是在有问题的前提下进行的，所以首先要明确存在什么问题。P003早期版本因多个页面查询速度缓慢，测试提出了二十多个bug，所以在新的版本中针对这个问题做了一系列优化措施。

经过定位，明确了是数据的存取出现问题，所以针对MySQL做优化

MySQL的优化一般分为这几个步骤：

1.sql语句的优化

2.索引的优化

3.业务逻辑的优化

4.表设计的优化

5.利用辅助工具

6.MySQL服务器的优化

7.硬件优化

一、sql语句优化

（1）查询数据列表返回字段由随意返回改为精确返回，前端需要展示什么字段返回什么字段

（2）减少join，大表join非常消耗性能

（3）慎用like查询，避免造成索引失效

（4）简化sql复杂度，单条特别复杂的sql考虑分成多条查询语句，过多的sql逻辑最好在业务层处理

二、索引优化

去除无用、重复的索引，添加高效率索引

三、业务逻辑优化

（1）去除查询数据列表还需要请求P010接口的情况

（2）去除查询数据列表还需要调用redis情况

（3）数据校验先使用了布隆过滤器，不通过再调用数据库

（4）查询大数据量使用了流处理操作，防止jvm内存过大

（5）利用mybatis缓存，查询先走缓存，缓存失效再调用数据库

（6）根据查询语句中有过多join操作，为了减少join，由数据库join，改为内存join。

（7）一些常用但不经常改动的数据，使用了本地缓存。

（8）为了规避模糊查询造成的查询效率降低，精确查询又降低用户体检的矛盾，向产品经理提出单个字段模糊查询，列表精确查询的设计。

四、表设计优化

（1）去掉了一些没有用的字段

（2）针对每个字段长度的评估，根据实际情况减小了字段长度，修改了字段类型（经测试，一个占空间220M的表，dev_add字段长度由48改为20，空间减少使用了20M）

（3）查询语句中有过多join操作，根据数据库反三范式设计思想，增加了表的冗余，减少了join操作

（4）大数据量的表进行分表处理

五、工具

利用了第三方工具shardingsphere，提高了分表查询效率（没有特殊需求慎用，一些sql不支持，还有一些奇奇怪怪问题）。

六、MySQL服务器优化

优化MySQL配置参数

七、硬件优化

提供更好的服务器配置

八、存在的问题

虽然现在测试查询效率反应良好，但是我们不能只看到好的一面，其实还是有很多问题需要解决。

（1）测试是否是在理想状态下进行了（避开了密集抄表期）

（2）呈现的效果是否是因为建立在优良的服务配置基础下

（3）服务的伸缩性也是并发测试忽略掉的，伸缩性就是指，一个服务在不同的外界条件下都能够稳定的运行，比如客户要抄5000块表，就不需要那么好的配置。或者客户要求用抄5000块表需要的配置来抄60w块表，不追求抄表的效率，只要能稳定运行。总之，服务器配置的好坏只是影响了服务运转的效率，不会对服务的稳定性造成破坏

（4）后续增加权限控制会对查询效率产生很大影响

（5）数据分析整个模块查询效率目前没有想到好的处理方法

（6）目前用了MySQL主从架构，是否适用于P003，主从有一个无法回避的问题就是数据延迟，而P003存在大量的档案、配置的管理，经常的场景就是添加一条记录，前端立即会调用查询列表的接口，用户来确认添加的数据是否在列表中，如果从库有稍微的延时，就有很大可能查询不到刚刚添加的数据，造成用户的疑惑。而且P003整体是写多读少，这就造成了延时时长的增加，因为从库同步是排队进行的，所以有可能造成添加一条记录，要等好久才能同步到从库

（7）根据P003实际情况，业务数据和日志数据分开储存优于MySQL主从的方案，当然分开储存也可以用主从架构，只是业务数据只用主库即可，没有了日志数据的干扰，也用不着查询从库，而从库只做备份。

九、总结

对于MySQL的优化，一般按照sql语句、索引优化、业务逻辑优化、表设计优化的优化顺序，一般都能解决问题，MySQL服务器配置和硬件的配置只是对以上优化的补充，一味的追求服务硬件的配置，只会掩盖服务自身的问题。