MySQL性能优化（四）性能优化总结

MySQL数据库优化的层次和思路
MySQL数据库优化的工具

连接优化

服务端链接优化

1. 服务端高并发，连接数不够可以调大连接数。

show variables like 'max_connections'
-- max_connections	151

2. 适当调小链接超时时间

客户端连接优化

1. 使用数据库连接池技术。MyBatis自带的连接池、Durid、Hikari。
   连接池数量计算公式：connections = ((cpu_core_count * 2) + effective_spindle_count)。不宜配置过多，原因是CPU上下文切换需要消耗时间。

配置的优化

1. 数据库配置
   参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
2. 操作系统配置：磁盘阵列等。

架构优化

1. 缓存（Redis）
2. 数据库集群：复制技术https://dev.mysql.com/doc/refman/5.7/en/replication.html
   
   • 多数据源（读写分离）：mycat、AbstractRoutingDataSource、ShardingJDBC
   • 问题：同步延迟（主库并发、从库单线程导致）。解决方案1：可以采用全同步复制，但是会造成Master节点性能下降。MySQL默认是异步复制（对于Master节点来说，只要事务写入binlog提交了就会返回给客户端，并不会关心从库是否同步成功）。
   • 解决同步延迟更优的方案2：半同步复制。并不是等待所有的从库都写入成功才返回，而是只要有一个从库写入relay log就返回。但是启用半同步复制需要在主从节点安装对应插件。semisync_master.so、semisync_slave.so。

   install plugin rpl_semi_sync_master soname 'semisync_master.so';
   
   show variables like '%semi_sync%'
   -- 默认关闭，需要手动开启 
   

   • 解决同步延迟方案3：基于GTID（Global Transaction ID）复制。https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html

   show variables like '%gtid_mode%'
   -- gtid_mode	OFF
   -- 默认关闭需要手动开启
   

3. 分表：垂直分表、水平分表
4. 分库：垂直分库、水平分库

数据库高可用：

1. 主从：HaProxy + Keepalived
2. NDB cluster：https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-overview.html
3. Galera Cluster For MySQL
4. MHA：（Master-Master Replication Manager For MySQL） -> MMM：（MySQL Master High Available）
5. MRG：（MySQL Group Replication）

数据库慢查询

慢查询日志

开启慢查询日志会消耗性能

-- 慢查询配置
show variables like 'slow_query%';
-- 多长时间的查询才叫慢查询
show variables like 'long_query%';

mysql慢日志分析工具

mysqldumpslow --help
whereis mysqldumpslow

profiling工具

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

select @@profiling;
set @@profiling=1; 

表结构和存储引擎的优化

存储引擎：

表结构

如果能确定长度，就不要定义定长的字段，给默认值也不要允许空值。
存非文本字段，应该存储地址。而不是存储编码。仅量和业务表做拆分。
对于字段很多的表，对字段进行拆分。
也可以利用字段冗余来避免join，提升查询性能。

历史表：增长速度快。按照时间维度区分。按年月划分为12个区。

SQL与索引的优化

案例- 执行计划 Explain

更详细的explain：format=JSON

explain format=JSON select t.tname from teacher t group by tname;

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

drop table if exists course;
create table course(
    id int(3) default null,
    cname varchar(20) default null,
    tid int(3) default