慢SQL是什么原因导致的？如何定位和优化慢查询

慢sql

指执行时间超过预定阈值的sql，通常由long_query_time参数设定，默认为10秒。

定位

• 通过在配置文件my.cnf里的[mysqld] 下添加代码开启（重启后依然生效） 如下：

[mysqld] 
slow_query_log=1 //开启慢查询日志
slow_query_log_file=/var/lib/mysql/mysql-slow.log 	//慢查询日志存放路径   
long_query_time=3 //慢查询阈值
log_output=file
 

通过在mysql客户端执行命令开启（重启后失效） 如下：

set global slow_query_log = ‘on';
set global slow_query_log_file = ‘/var/lib/mysql/mysql-slow.log';
set global long_query_time = 3;

分析

通过执行 在对应sql前 添加 explain后的sql 得到该sql的执行计划 如下

    id  select_type  table    partitions  type    possible_keys  key       key_len  ref       rows  filtered  extra        
------  -----------  -------  ----------  ------  -------------  --------  -------  ------  ------  --------  -------------
     1  simple       student  (null)      index   (null)         name_age  68       (null)      10    100.00  using index  

主要关注 type、key、rows、extra

type

代表此次执行该sql所使用的索引类型

索引类型的效率：

null > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > all

• null指该sql并不需要访问表，或者取的是索引的最大值或最小值（只需取索引叶子节点的左端或右端）

• system指该sql所执行的表只有一行记录

• const该sql使用的是主键索引或唯一索引，只需通过一次索引就能找到记录

• eq_ref指该sql使用的是join查询，且能使用对应的索引找到唯一的记录（主键索引、唯一索引）

• ref指能用非唯一索引找到唯一的一条记录

• ref*_or_null *指在ref基础上索引列支持null值

• index_merge指使用了一个以上的索引进行组合查询（非联合索引）

range 指使用索引列进行范围条件查询如

=, <>, >, >=, <, <=, is null, <=>, between, in

• index 指使用该索引进行了全表遍历

• all 指未使用索引，直接进行全表遍历

key

实际上使用的索引名称（建立索引时定义），没有用索引则为null

rows

指mysql估计该sql通过索引（走了索引时）所要读取到server层的数据行数，同一sql在不同索引下，数值越小代表索引越好

filtered

估计读取到server层后没有被过滤掉的数据行数比例 n/rows*100%

extra

mysql的索引优化信息

• using index：使用覆盖索引
• using where：需要回表进行where条件数据过滤
• using index condition :使用索引下推
• using temporary：建立了个临时表，常见于使用了聚会函数、子查询等需要进一步对数据操作的sql
• using filesort: 对结果集进行排序，但是没有用到索引（索引失效）或该没有对应索引

索引优化

最左前缀匹配

联合索引建立时的索引顺序通过使用频率，字段区分度及范围查询（导致后面索引失效）来进行建立

如 sql:

select * from student where score=60 and finished_time >'2000-10-10'

建立索引

idx_score_finished（score,finished)

注：具体需根据业务场景进行判断

索引覆盖

在高频、少字段的sql中，考虑对字段加联合索引，实现索引覆盖

如 sql:

select name,score from student where finished_time ='2000-10-10'

建立索引

idx_finished_name_score

索引下推

对查询条件建立索引，减少回表到server层进行过滤的行数（当查询条件中存在不生效索引或不存在索引时，需要将数据传到server层进行过滤）

如 sql:

select score from student where name ='stu' and finished_time ='2000-10-10'

建立索引

idx_name_finished

表结构优化

数据类型

如果确定该列不为null（有默认值），勾选not null省去部分空间

数字类型

• 按数值范围选tinyint、int、bigint，避免空间浪费。确认数值无负数时，选择unsigned
• double类型的计算存在精度丢失问题，可改成int类型，小数在业务层进行处理，也可使用decimal

字符类型

• 避免使用text类型
• 在定长字符或长度差距不大的字段时，选用char能够节省空间（省去varchar的len部分）
• varchar的长度根据业务需求选择，太长会导致行数据读入内存时消耗过大（相比同长度但不同varchar类型长度的数据）

日期类型

• 对范围（1970-2038）无要求，使用timestamp，同精度存储大小为datetime的一半
• 只需要保存到日，可以用datetime，只需3字节

字符编码

如果对字符要求不高，可以不选utf等有冗余的编码方式，按需选字符集

注：有联表关系的表的表间关联键的字符集应保持一致，避免关联时字符集不同导致索引失效

​​范式与反范式平衡

可以根据业务需求进行反范式设计，即添加冗余字段

如: product表 kind表 可在product表上冗余kind_name字段

大字段如text会影响数据行加载到内存页的速度（大字段会行溢出，引入磁盘io），可以拆分为表，使用关联方式读取

sql优化

1. 按需select， 避免使用select*，减少网络io带宽消耗
2. 索引字段避免使用mysql的隐式转换，如bigint的id传int64，而不是字符串，避免索引失效
3. like避免将通配符加到左侧，避免不遵守最左前缀原则导致索引失效
4. 避免在索引列上使用mysql内置函数（server层），导致索引失效
5. 避免对索引列进行运算
6. 对索引列等值查询时，使用= 而不是<>
7. 多表操作如join，in时使用小表驱动大表的sql编写，减少关联数据量
8. 批量操作在业务层进行分批，sql中进行批量执行，避免并发数据插入带来的锁竞争
9. 使用limit减少每次回传的数据行数以及数据库回表行数，减少网络io及sql执行时间
10. 深分页使用延时分页优化：将符合条件的id limit查出来后再去主表联表，省去二次回表

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。