文章目录
MySQL 如何定位并优化慢查询Sql
根据慢日志定位查询慢sql
使用explain等工具分析sql
修改sql或者尽量让sql走索引
根据慢日志定位查询慢sql
show variables like xxx 详解mysql运行时参数
show variables like '%QUERY%'; 慢查询
show STATUS LIKE '%slow_queries%';慢查询数量
set GLOBAL slow_query_log = on; 开启慢查询
set GLOBAL long_query_time = 1; 设置慢查询上限时间
1.开启运行时参数 show variables like xxx
show variables like '%QUERY%'; 慢查询参数
show STATUS LIKE '%slow_queries%';慢查询数量
2.设置慢查询日志和时间
set GLOBAL slow_query_log = on; 开启慢查询
set GLOBAL long_query_time = 1; 设置慢查询上限时间(需要删除连接重连)
3.制造慢SQL或者直接打开路径查看日志
4.利用explain分析执行
5.具体场景分析对比日志记录
# 修改SQL
# 使用索引
Explain 关键字段解析
ID字段
id是一组数字,代表多个表之间的查询顺序,或者包含子句查询语句中的顺序,id 总共分为三种情况
1.id 相同,执行顺序由上至下
2.id 不同,如果是子查询,id 号会递增
id 值越大优先级越高,越先被执行
3.id 相同和不同的情况同时存在
id相同的为一组,组内从上到到下执行,在所有组中,id越大,越优先执行
SELECT_TYPE 查询的类型
# simple 简单子查询
# primary 最外层子查询
# subquery 第一层子查询
在 select 或者 where 里包含了子查询
# derived 派生表
在 from 中包含的子查询
会被标记为衍生查询,会把查询结果放到一个临时表中
# union 出现在union后面的查询
# union result union联合查询获取结果的select
如果有两个 select 查询语句,他们之间用 union 连起来查询
那么第二个 select 会被标记为 union,union 的结果被标记为 union result
TABLE字段
表示这一行的数据是哪张表的数据
TYPE字段(优化重要信息)
一般来说保证查询至少达到range级别,最好达到ref
性能从好-->差
# system
表中只有一行记录,system 是 const 的特例,几乎不会出现这种情况,可以忽略不计
# const
将主键索引或者唯一索引放到 where 条件中查询,MySQL 可以将查询条件转变成一个常量,只匹配一行数据,索引一次就找到数据了
# eq_ref
同ref差不多,但返回结果只有一体记录
# ref
不是主键索引,也不是唯一索引
就是普通的索引,可能会返回多个符合条件的行
# range
只用一个索引来选择行,key列显示所用的索引
一般是在where子句里面出现between,and,>,<等查询符合
# index
也是读取全表,但是是从索引中读取
# All
全表查询(从磁盘中读取)
EXTRA字段(优化重要信息)
优化时候必看的重要信息
# Using index:
用了覆盖索引
# Using index condition:
用了条件索引(索引下推)
# Using where:
从索引查出来数据后继续用where条件过滤
# Using join buffer (Block Nested Loop):
join的时候利用了join buffer(优化策略:去除外连接、增大join buffer大小)
# Using filesort
:用了文件排序,排序的时候没有用到索引
# Using temporary:
用了临时表(优化策略:增加条件以减少结果集、增加索引,思路就是要么减少待排序的数量,要么就提前排好序)
# Start temporary, End temporary:
子查询的时候,可以优化成半连接,但是使用的是通过临时表来去重
# FirstMatch(tbl_name):
子查询的时候,可以优化成半连接,但是使用的是直接进行数据比较来去重
KEY & ROWS字段(优化重要信息)
possible_keys – 表示可能会用到的索引
此次查询中涉及字段上若存在索引,则会被列出来,表示可能会用到的索引,但并不是实际上一定会用到的索引
key – 实际使用的索引
key_len – 表示索引中使用的字节数
通过该属性可以知道在查询中使用的索引长度,注意:这个长度是最大可能长度,并非实际使用长度,在不损失精确性的情况下,长度越短查询效率越高
rows–大致估算行数
根据表信息统计以及索引的使用情况,大致估算说要找到所需记录需要读取的行数,rows 越小越好
常见的优化手段
- SQL语句中IN包含的值不应过多,不能超过200个,200个以内查询优化器计算成本时比较精准,超过200个是估算的成本,另外建议能用between就不要用in,这样就可以使用range索引了。
- SELECT语句务必指明字段名称:SELECT * 增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。
- 当只需要一条数据的时候,使用limit 1
- 排序时注意是否能用到索引
- 使用or时如果没有用到索引,可以改为union all 或者union
- 如果in不能用到索引,可以改成exists看是否能用到索引
- 使用合理的分页方式以提高分页的效率
- 不建议使用%前缀模糊查询
- 避免在where子句中对字段进行表达式操作
- 避免隐式类型转换
- 对于联合索引来说,要遵守最左前缀法则
- 必要时可以使用force index来强制查询走某个索引
- 对于联合索引来说,如果存在范围查询,比如between,>,<等条件时,会造成后面的索引字段失效。
- 尽量使用inner join,避免left join,让查询优化器来自动选择小表作为驱动表
- 必要时刻可以使用straight_join来指定驱动表,前提条件是本身是inner join