MySQL的慢sql优化

针对慢sql优化思路整理，一共可以分为以下三步

一、开启慢查询

二、通过explain关键字进行sql语句的分析

三、具体的优化步骤

1、首先要避免全表扫描，检查是否有索引

2、查看索引是否生效(以下是索引失效的部分场景)

3、sql结构的优化

4、数据库表设计的优化

一、开启慢查询

开启慢查询有两种方式：

1⃣️修改配置文件

windows下：配置文件为my.ini，一般在mysql的安装目录下，或c:\\windows下

linux下：配置文件为my.cnf，一般在/etc下

在配置文件的[mysqld]下加入如下语句

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

2⃣️通过数据库开启慢查询

修改配置文件需要重启mysql sever进程mysqld才会生效，线上环境可以通过指令来开启慢查询

set global slow_query_log = 'ON'：设置参数为ON，开始慢查询
set global slow_query_log_file = '/slowlog/log.log'：记录超时的语句的文件夹
set global long_query_time = 2：超过该时间的语句会被记录到日志中

二、通过explain关键字进行分析

其中比较重要的几个参数

1、possible_keys：可能使用到的索引
2、key：实际上使用到的索引
3、type：查询使用的类型
4、rows：查询到需要的数据，扫描了多少行

对于type，从坏到好all < index < range < ref < eq_ref < const

一般保证达到range，ref最好（const写死一个常量、eq_ref针对主键或唯一键查询结果唯一、ref针对非唯一键查询多个、range索引范围查询、index全索引扫描、all全表扫描)

三、具体的优化步骤

1、首先要避免全表扫描，检查是否有索引

• 没有索引考虑是否建立索引(可以在经常需要查询的列上、需要排序的列上添加索引)

2、查看索引是否生效(以下是索引失效的部分场景)

• 不满足最左匹配原则
• like以%开头或者全模糊
• >、<号右边的索引会失效
• or也会导致索引失效(在or的两边都加索引才不会失效或者使用union(去重)、union all(不去重)替代or)
• !=、<>也会导致索引失效
• 在where子句中使用表达式操作、函数操作等

3、sql语句结构的优化

• 避免使用select*，不要返回不必要的字段，增加索引覆盖的概率
• 尽量减少子查询(子查询会创建临时表，查询完毕删除临时表)
• 调整where子句的连接顺序，将过滤数据多的条件放在前面，最快速度缩小结果集
• 尽量减少联表查询，联表查询是笛卡尔乘积的形式，检索的数据几何倍上升
• 一次查询的结果最好不要过大，可以使用分页查询

4、数据库表设计的优化

• 单表的字段最好不要超过20个。如果是比较大的表，有的字段使用的多，有的字段使用比较少，使用频率比较少的会拖慢查询速度
• 表的结构是否合理(范式、反范式需要结合实际场景来决定)
• 列类型的选择是否合适(能用int却用了bigint等)