MySQL 高级：explain 分析 SQL，索引失效 & 常见优化场景

最新推荐文章于 2023-04-03 15:08:49 发布

π大星的日常

最新推荐文章于 2023-04-03 15:08:49 发布

阅读量1.1k

点赞数 1

分类专栏： java 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/m0_73311735/article/details/127586736

版权

本文介绍了如何通过`EXPLAIN`分析SQL查询性能，包括`id`、`select_type`、`type`等关键字段的含义。接着讨论了索引失效的多个场景，如不满足最左前缀、范围查询后、索引字段运算等，并给出了优化建议。最后，探讨了SQL优化的几个场景，如大批量插入、ORDER BY排序和GROUP BY分组的优化方法。

摘要由CSDN通过智能技术生成

本篇，我们先着重讲解如何分析，具体如何找到 SQL，后续的实战篇，我们再来详细谈一谈。

🎐【一、explain】分析 SQL

explain 中，包含了如下几个字段（不同版本可能会有所差异）：

看完是不是很懵，感觉好多要记忆的，别着急，下边我们通过实际案例，来加深记忆

id

id 字段是 select 查询的序列号，是一组数字，表示的是查询中执行 select 子句或者是操作表的顺序。id 情况有三种：

此处只是单表查询，id 只有一个

id 一样，则从上到下

id 不同，则 id 值越大，优先级越高

此处是嵌套子查询，最内部的子查询，自然是最先执行的

简而言之：

id 值越大，优先级越高；
id 值一样，则从上到下；

select_type

PRIMARY,SUBQUERY

DERIVED(需要临时表，自然比上述效率低)

type

结果值从最好到最坏以此是：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system > const > eq_ref > ref > range > index > ALL

一般至少要达到 range 级别，最好达到 ref 。

const

唯一索引，非关联查询

eq_ref，ref

eq_ref 跟 const 的区别是：两者都利用唯一索引，但前者是关联查询，后者只是普通查询？eq_ref 跟 ref 的区别：后者是非唯一索引

index，all

都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取。

不走索引就会遍历全表

possible_keys，key

possible_keys : 显示可能应用在这张表的索引，一个或多个。

key ：实际使用的索引，如果为 NULL，则没有使用索引。（可能是没有走索引，需要分析）

key_len : 表示索引中使用的字节数，在不损失精确性的前提下， 长度越短越好。

单列索引，那么需要将整个索引长度算进去；
多列索引，不是所有列都能用到，需要计算查询中实际用到的列。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。

当使用常量等值查询，显示 const
当关联查询时，会显示相应关联表的关联字段
如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为 func
其他情况为 null

id 是索引，而且是 id=1，一个常数，故 ref = const
user_id 不是索引，ref 直接为 null

t1.id 是索引，且=号后边不是常量，故显示 t1.id，即显示相应关联表的关联字段

rows

扫描行的数量，一般越小越好

用索引 rows 就为 1，无论是唯一索引还是非唯一索引
其他情况一般是全表扫描，rows 等于表的行数。

filtered

表里符合条件的记录数的所占的百分比。

extra

其他的额外的执行计划信息，在该列展示，需要把前两个优化为 using index。

using where

不同版本好像不一样

5.7：表示 MySQL 首先从数据表（存储引擎）中读取记录，返回给 MySQL 的 server 层，然后在 server 层过滤掉不满足条件的记录，即无法直接在存储引擎过滤掉。简单来说，就是查询时 where 中用的不是索引。

现在，我们知道怎么用 explain 来分析 SQL 语句了，自然可以来剖析我们的 SQL 语句的性能，不过早有先人给我们总结了几个需要优化的场景-->索引失效

🎐【二、索引失效】的几个场景

0. SQL 准备

create table `tb_seller` (	`sellerid` varchar (100),	`name` varchar (100),	`nickname` varchar (50),	`password` varchar (60),	`status` varchar (1),	`address` varchar (100),	`createtime` datetime,    primary key(`sellerid`))engine=innodb default charset=utf8mb4; 
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawe