【MySQL】优化MySQL慢查询教程和Explain解析

---

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

---