2021-08-09 MySQL性能优化总结

MySQL性能优化层面

MySQL性能优化.png

SQL和索引层面优化

Explain

Explain结构.png
Explain-Type-Case.png
Explain-Type-Case.png
Explain-Extra.png

小结

• 嵌套查询一般是从外到内进行；小表驱动大表 -- 从temp大小考虑。

• eq_ref : 关联查询

• 索引条件下推、覆盖索引 -- 可能会出现possible_keys = null, 但是key != null 的情况。

• 注意：如果业务不允许存在空值，库表设计应该也不能为空值，因为数据库会专门为空值增加一个存储空间，造成资源的浪费。

• row -- 预估的行数，可以查看是否使用到了索引。

表结构和存储引擎的优化

表结构

• 表拆分：表数据（按照月份拆分），表结构（按照属性拆分）

  
  表拆分.png

• 表设计--字段设计，提供能够为一个列存储全部数据的最小的存储空间即可。

• 表设计--字符类型，变长情况使用varchar,固定长度使用--char

• 表设计--非空，非空字段尽量定义成NOT NULL提供默认值(索引设置层面考虑)。

存储引擎的选择

• 查询插入操作多的业务表——MyISAM

• 临时数据——Memeroy

• 大并发更新多的表——InnoDB

架构优化

缓存

集群、主从复制

主从复制原理.png
读写分离.png

分库分表

基于业务的垂直分库，基于业务、时间的水平分库

垂直分库.png
水平分库分表.png

操作系统、MySQL配置参数

服务端：增加连接或者回收空闲链接。

MySQL参数-连接.png

硬件

如：提升内存

其他层面：

• 服务器降级
  

  服务降级.png

• 分流——双11从原来的11.11 延伸到11.01~11.11

• 引入MQ 削峰

案例：MySQL性能下降，如何解决？

1. 服务器重启，

2. 然后通过命令观察一下服务状态,比如锁、连接、事务是否正常

   
   Mysql状态查看命令.png

show full prosslist -- 查看线程链接情况

show status -- 查看服务端的状态值

show engine innodb status -- 查看InnoDB存储引擎的状态值

3. 查看是否存在慢查询语句，

4. 如果存在，通过Explain 分析执行计划，查看如何优化。

5. 如果还是效果不好，看看字段设计或者库表设计是否合理，是否可以通过读写分类分库分表来优化。

6. 如果还是不好，看看是否可以通过业务层来综合优化。

案例：慢SQL问题？

1. 开启慢SQL查询日志——系统默认执行时间 > 10s的SQL为慢SQL

慢日志配置查看命令.png
慢SQL配置参数展示.png

MySQL慢查询日志过滤筛选命令：

MySQL慢查询日志过滤筛选命令.png

2. 分析查询基本情况

3. 找到慢的原因

   1. 查看执行计划——Explain

4. 对症下药

   1. 创建索引或者联合索引

   2. SQL语句优化

      SQL语句优化.png

3. 表结构优化（冗余、拆分、Not Null）、架构优化（缓存、读写分离、分库分表）

4. 业务层面优化，如检查必要条件十分必要。