mysql慢查询优化方法_MySQL查询优化

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

定位低效

SQL 执行慢有两种情况：

• 偶尔慢：DB 在刷新脏页
  • redo log 写满了
  • 内存不够用，要从 LRU 链表中淘汰
  • MySQL 认为系统空闲的时候
  • MySQL 关闭时
• 一直慢的原因：索引没有设计好、SQL 语句没写好、MySQL 选错了索引

’mysql慢查询优化
第一步：开启mysql慢查询日志，通过慢查询日志定位到执行较慢的SQL语句。
第二步：利用explain关键字可以模拟优化器执行SQL查询语句，来分析SQL查询语句。
第三步：通过查询的结果进行优化。

优化方式

（1）首先分析语句，看看是否包含了额外的数据，可能是查询了多余的行并抛弃掉了，也可能是加了结果中不需要的列，要对SQL语句进行分析和重写。
（2）分析优化器中索引的使用情况，要修改语句使得更可能的命中索引。比如使用组合索引的时候符合最左前缀匹配原则。not in,not like都不会走索引，可以优化为in.
（3）如果对语句的优化已经无法执行，可以考虑表中的数据是否太大，如果是的话可以横向和纵向的切表。

EXPLAIN

执行计划

通过 EXPLAIN 命令获取执行 SQL 语句的信息，包括在 SELECT 语句执行过程中如何连接和连接的顺序，执行计划在优化器优化完成后、执行器之前生成，然后执行器会调用存储引擎检索数据

查询 SQL 语句的执行计划：

EXPLAIN SELECT * FROM table_1 WHERE id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oj8fOeWd-1637292608952)(https://gitee.com/seazean/images/raw/master/DB/MySQL-explain查询SQL语句的执行计划.png)]

MySQL 执行计划的局限：

• 只是计划，不是执行 SQL 语句，可以随着底层优化器输入的更改而更改
• EXPLAIN 不会告诉显示关于触发器、存储过程的信息对查询的影响情况
• EXPLAIN 不考虑各种 Cache
• EXPLAIN 不能显示 MySQL 在执行查询时的动态，因为执行计划在执行查询之前生成
• EXPALIN 部分统计信息是估算的，并非精确值
• EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划
• EXPLAIN PLAN 显示的是在解释语句时数据库将如何运行 SQL 语句，由于执行环境和 EXPLAIN PLAN 环境的不同，此计划可能与 SQL 语句实际的执行计划不同

环境准备：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M7M1AyNV-1637292608955)(https://gitee.com/seazean/images/raw/master/DB/MySQL-执行计划环境准备.png)]

id

SQL 执行的顺序的标识，SQL 从大到小的执行

• id 相同时，执行顺序由上至下

  EXPLAIN SELECT * FROM t_role r, t_user u, user_role ur WHERE r.id = ur.role_id AND u.id = ur.user_id ;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KHmaiveK-1637292608956)(https://gitee.com/seazean/images/raw/master/DB/MySQL-explain之id相同.png)]

• id 不同时，id 值越大优先级越高，越先被执行

  EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))
  

  

• id 有相同也有不同时，id 相同的可以认为是一组，从上往下顺序执行；在所有的组中，id 的值越大的组，优先级越高，越先执行

  EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ; 
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PG8Bw4qL-1637292608959)(https://gitee.com/seazean/images/raw/master/DB/MySQL-explain之id相同和不同.png)]

select

表示查询中每个 select 子句的类型（简单 OR 复杂）

type

对表的访问方式，表示 MySQL 在表中找到所需行的方式，又称访问类型

从上到下，性能从差到好，一般来说需要保证查询至少达到 range 级别， 最好达到 ref

key

possible_keys：

• 指出 MySQL 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
• 如果该列是 NULL，则没有相关的索引

key：

• 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为 NULL
• 查询中若使用了覆盖索引，则该索引可能出现在 key 列表，不出现在 possible_keys

key_len：

• 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
• key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的
• 在不损失精确性的前提下，长度越短越好

Extra

其他的额外的执行计划信息，在该列展示：

• Using index：该值表示相应的 SELECT 操作中使用了覆盖索引（Covering Index）

• Using index condition：第一种情况是搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件，回表查询数据；第二种是使用了索引下推

• Using where：表示存储引擎收到记录后进行后过滤（Post-filter），如果查询操作未能使用索引，Using where 的作用是提醒我们 MySQL 将用 where 子句来过滤结果集，即需要回表查询

• Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询

• Using filesort：对数据使用外部排序算法，将取得的数据在内存中进行排序，这种无法利用索引完成的排序操作称为文件排序

• Using join buffer：说明在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果

• Impossible where：说明 where 语句会导致没有符合条件的行，通过收集统计信息不可能存在结果

• Select tables optimized away：说明仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

• No tables used：Query 语句中使用 from dual 或不含任何 from 子句