4、mysql性能优化总结

一、优化从何入手？
（一）连接数
1、客户端（jdbc连接池）、服务端（调大连接数）
a、druid的连接池默认为8个。hikari的连接池默认为10个
b、建议个数：连接池个数：cpu*2+1

（二）主从复制
1、原理：通过binlog，如图：

a、relay log：中继日志
2、如何让写操作去master执行，读操作去slave执行
   切面判断读写+spring提供的动态数据源：abstractRountingDataSource
3、通信规则
a、异步复制：master写入binlog就会给client返回success，不会关心slave是否同步成功。默认是异步复制
b、全同步复制：所有slave从master复制完毕，master才给client返回success
c、半同步复制：只要有一个slave将master的binlog写入到relaylog中，master就给client返回success。mysql需要安装插件：semisync_master.so（master安装）、semisync_slave.so（slave安装）
注：参数查看：show variables like '%semi_sync%';  默认是off，需要手动开启
4、使用gtid（global transaction id）复制  --->从库可以并行的执行sql语句，提高slave同步master的效率
a、参数：show global varibales like 'gtid_mode';   默认是off，需要手动开启

（三）主从复制高可用
1、主从 haproxy+keepalived（zookeeper）
2、NDB cluster
3、Galera Cluster for Mysql
4、MHA（Master-Master replication manager for Mysql）
   MMM（Mysql Master High Available）
5、MGR（Mysql Group Replication）

（四）分库分表
1、垂直分库分表：不容的表放在不同的库中
2、水平分库分表：将同一张表的数通过取模分别放在table_1、table_2表中

（五）调整慢日志
1、慢日志：show variables like 'slow_query%';  
a、slow_query_log：慢日志，默认开启，会消耗性能
b、slow_query_log_file：慢日志位置
2、慢日志阈值：show varibales like '%long_query';
a、long_query_time：默认10s会记录到慢日志
3、统计慢sql：whereis mysqldumpslow--->mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log.bak
4、统计慢sql：select @@profiling;--->set @@profiling=1--->执行sql--->show profiles;

（六）、查看mysql性能
1、查看innodb当前锁的情况：show status like 'innodb_row_lock_%';
innodb_row_lock_current_waits：当前等待回去锁的事务
innodb_row_lock_waits：mysql启动后总共等待的事务
2、查询当前事务语句：select * from infomation_schema.INNODB_TRX;
trx_stated：当前事务的状态，如：running、lock wait
trx_mysql_thread_id：当前线程的id。可以通过kill id，杀死这个线程。
3、查询当前用锁语句：select * from infomation_schema.INNODB_LOCKS;
lock_mode：哪种锁
4、查询当前等待锁语句：select * from infomation_schema.INNODB_LOCK_WAITS;
5、查询服务端线程的状态：show processlist; 或 select * from information_schema.processlist;
a、host：哪一个客户端
6、mysql启动后的各种状态：show status;
7、查看存储引擎的运行信息：show engine innodb status;

（七）数据库监控软件
1、mysqlsla

（八）explain执行计划说明

1、explain 查询出的结果：id不一样，先执行id大的，再执行id小的。若id一样大，先执行上面的，再执行下面的。
2、select_type：查询类型
    a、simple：如：select * from 表名;
    b、primary：嵌套查询的外面查询
    c、subquery：嵌套查询的子查询
    d、derived：查询时用到的临时表的查询，中间的临时表
3、type：连接类型
    a、const：按主键查询且只能查到一条数据。
    b、system：系统表且只有一行数据，如：select * from myslq.proxies priv;
    c、eq_ref：唯一性索引
    d、ref：非唯一性索引
    e、range：对索引字段进行范围查询
    f、index：查询索引字段的所有数据
    g、all：没有使用到索引
    h、null：不用访问表
注：sql优化最好达到range或ref的级别
4、key：实际使用的索引名称
5、rows：预计需要扫描的行数，越小越好
6、filtered：查询到的数据/扫描数据，越大越好
7、extra
    a、using where：server层进行过滤
    b、using index：用到了覆盖索引
    c、using index condition：用到了索引下推
    d、using filesort：用了索引选择数据，排序时用不到索引
    e、using temporary：对非索引列进行distinct、group by、join

 

参考：https://tech.meituan.com/2014/06/30/mysql-index.html