MySQL调优

一、如何发现慢SQL

主要有两个方向：

方向一：通过第三方工具

通过压力测试进行发现哪些接口比较慢，然后再排查是否由SQL慢引起（如果项目框架体系比较完善，则一般由第三方工具可检测可追踪来进行发现）

方向二：在/etc/my.cnf中配置（永久配置）

slow_query_log=ON #开启慢查询日志
# 这里不指定默认路径为/var/lib/mysql/localhost-show.log
slow_query_log_file=/var/lib/mysql/mysql-slow.log  #慢查询日志的目录和文件名
long_query_time=1 #设置慢查询的阀值为1秒

临时配置：

# 查看是否开启慢SQL记录
show variables like 'slow_query_log';
# 打开记录慢SQL
set global slow_query_log = on;
# 查看设置慢SQL记录的时长阈值--达到多少s后则会被记录为慢SQL(默认为10s)
show variables like 'long_query_time';
# 全局设置慢SQL的时长阈值
set global long_query_time = 1;
# 当前session设置慢SQL的时长阈值 -- （这里要配置是因为global中配置了对当前session无效）
set long_query_time = 1;
# 查看是否存在慢SQL
show global status like 'slow_queries';

二、慢SQL如何调优

第一步：先通过Expain对SQL进行分析

需要注意的字段：

列名	说明
possible_keys	表示可能使用到的索引
key	实际命中的索引
key_len	实际使用的索引长度（一般在联合索引中使用这个字段进行分析，联合索引的所有索引字段是否都命中了）
Extra	优化建议（如：Using index condition表示查找命中了索引，但是需要进行回表查询）
type	表示索引的级别，由好到差：system>const>eq_ref>ref>range>index>all

补充-什么是回表查询：

是发生在二级索引（非聚簇索引）作为查询条件的时候，而查询结果中需要的字段包含非索引的字段，那么就需要先根据当前的索引查找的相应的主键，再由主键去检索到对应的字段数据，这种称为回表查询。
如：
select t.id, t.name, t.address from tb_shop t where t.name = '老街1号'
其中id为主键，name为普通索引；
由于二级索引存储的数据格式（B+树）为：非叶子节点存储指针，叶子节点存储主键；
聚簇索引存储的数据格式为：非叶子节点存储指针，叶子节点存储数据行；
所以当上面的SQL执行的时候，只根据name索引无法得到address字段值，还需要根据获取到的主键再去检索一次表来回显address字段值
这个过程就是回表查询

type类型说明：

级别	说明
system	查询mysql系统中的表
const	根据主键查询
eq_ref	根据主键索引/唯一索引查询
ref	索引查询
range	范围查询
index	索引树扫描
all	全表扫描

第二步根据分析结果进行调优

调优的前提：不能改变业务场景（或者说最好不要）

调优就是根据根据explain的分析结果进行优化，以提供检索效率。

调优建议：

1.索引最好能达到ref级别（包含）以上
2.避免使用select * ，在出现回表查询的时候，相关字段能否进行省略处理
3.出现or条件的时候，根据实际场景拆分为union all/union（union all相对比union好一些，少一次筛选）
4.最左匹配原则，通过key+key_len字段进行分析，判断联合索引是否覆盖
5.索引字段作为查询条件尽可能不要进行函数或者表达式操作，避免索引失效
6.索引字段尽可能避免存在为NULL的情况
7.以小表（数据量较小的）为驱动表，像inner join这种MySQL会自动进行优化（以小表为驱动），但是left join /right join这种就不会，需要自己根据实际情况把小表放在前面来作为驱动表；
8.遇到一些每次都要进行一次子查询的情况，而且还可能是全表扫描的子查询，如这种场景： 能不能先获取到对应的关联条件，然后在Java中进行条件组合后再查一次数据库（这个时候一般都能走索引），然后根据得到的结果再通过java回显到具体的字段中
9.应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE
10.用exists代替 select count(1)判断是否存在。count(1)比count(*)更有效率
11.当有一些因为字段类型不匹配的时候，如:int类型与varchar类型进行关联，可以使用cast(int类型字段 as char)来进行关联，有可能会走索引，需要实际场景实际分析

索引创建规则：

1.表的主键、外键必须有索引；
2.数据量超过 300 的表应该有索引；
3.经常与其他表进行连接的表，在连接字段上应该建立索引；
4.经常出现在 WHERE 子句中的字段，特别是大表的字段，应该建立索引；
5.索引应该建在选择性高的字段上；
6.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7.正确选择复合索引中的主列字段，一般是选择性较好的字段；
8.复合索引的几个字段是否经常同时以 AND 方式出现在 WHERE 子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
9.如果复合索引中包含的字段经常单独出现在 WHERE 子句中，则分解为多个单字段索引；
10.如果复合索引所包含的字段超过 3 个，那么仔细考虑其必要性，考虑减少复合的字段；
11.频繁进行数据操作的表，不要建立太多的索引；
12.尽量不要对数据库中某个含有大量重复的值的字段建立索引。