|
针对慢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等)
|