Explain详解与索引最佳实践

EXPLAIN是一个非常重要的 SQL 工具，用于分析SQL 语句的执行计划，帮助开发者优化查询性能。它会显示 MySQL 如何执行你的 SELECT、DELETE、INSERT、UPDATE语句，包括使用了哪些索引、数据是如何被读取的等。

EXPLAIN 的基本语法

--5.7+
EXPLAIN SELECT * FROM 表名 WHERE 条件;

--5.6
EXPLAIN EXTENDED SELECT * FROM 表名 WHERE 条件;
EXPLAIN PARTITIONS SELECT * FROM 表名 WHERE 条件;

EXPLAIN 各字段详解

执行后返回的结果中，每一行代表一个表的访问方式，下面是常见字段说明：

字段名	含义
id	查询中每个子查询的标识符，越大优先级越高,id相同从上往下执行
select_type	查询类型，如simple：简单查询不含子查询和union，primary：复杂查询中最外层的select，subquery：包含在select中的子查询（不在from子句中），derived：包含在from子句中的子查询。mysql会将结果放在一个临时表中，也称派生表、衍生表（derived的英文含义）
table	正在访问的表名
partitions	分区信息（如果使用了分区表）
type	表的连接类型，是优化的关键字段
possible_keys	查询可能使用的索引
key	实际使用的索引
key_len	使用的索引长度
ref	与索引比较的列或const常量
rows	预计要扫描的行数（mysql估算）
filtered	经过条件过滤后保留的行的百分比
Extra	额外信息，比如是否使用临时表、是否排序等

rows * filtered/100可以估算将要和explain中前一个表进行连接的行数（前一个表指explain中id值比当前表id值小的表）

查看mysql优化的结果

shwo warnings;

关闭mysql5.7新特性对衍生表的合并优化（关闭更好地查看derived）

set session optimizer_switch='derived_merge=off';

type（访问类型）—— 性能优化核心指标

按效率由高到低排列如下（尽量让 type 趋近于前几种）：
一般来说得保证查询达到range级别，最好达到ref

type	含义
system	表只有一行（等同于 const）
const	通过主键或唯一索引一次命中
eq_ref	多表连接中通过主键查找
ref	使用非唯一索引,或者唯一索引（联合索引）的部分前缀查找
range	使用索引范围扫描
index	全索引扫描就可以拿到结果（效率不高但比全表好），通常在覆盖索引的时候出现，覆盖索引即二级索引无需回表即可拿到所有所需的数据。
ALL	全表扫描（最差，需优化）

key_len

key_len 是EXPLAIN 执行计划中一列，用来表示 MySQL 实际使用了多少字节的索引长度。
它反映的是 查询中用到的索引字段的字节数总和，而不是整个索引的大小，也不是字段本身的最大长度。

基本计算规则

关键看字段类型和索引定义：

数据类型	key_len（字节）
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT / INTEGER	4
BIGINT	8
CHAR(n)	n × 字符集字节数
VARCHAR(n)	n × 字符集字节数 + 2
DATETIME	8
DATE	3
TIMESTAMP	4
TEXT/BLOB	不支持索引或前缀索引
可为 NULL 字段	还需要多+1字节

⚠️ 字符集不同影响字节长度：

• utf8mb4：一个字符最多占 4 字节
• utf8：一个字符最多占 3 字节
• latin1：一个字符占 1 字节

Extra（额外信息）

常见值及含义：

Extra	含义
Using index	使用覆盖索引（不会回表，性能较好）
Using where	使用 WHERE 过滤条件
Using temporary	使用了临时表处理查询（需优化）
Using filesort	有可能结果集过大需要临时存入磁盘，使用了文件排序（需优化），不一定写入，但是没有索引很有可能结果集过大所以叫文件排序。
Using index condition	列不完全被索引覆盖，并且where条件中至少有一个被索引覆盖的列条件是范围查询
NULL	没有额外操作

复现Using index condition

CREATE TABLE `test`.`Untitled`  (
  `id` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `gender` int(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_age_gender`(`age`, `gender`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

EXPLAIN SELECT name FROM users WHERE  gender like 'male%';

复现Using temporary

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  product_id INT,
  order_date DATE,
  amount DECIMAL(10, 2)
);

EXPLAIN SELECT distinct user_id FROM orders;

复现Using filesort

EXPLAIN SELECT product_id FROM orders ORDER BY product_id;

索引最佳实践

由于mysql的优化涉及各种因素，在不同场景下表现可能略有差异，所以以下不一定百分百出现。

1. 全值匹配，完整地使用了联合索引中所有字段，并且是用等值匹配（=）的形式

2. 最左前缀法则，如果索引了多列，要遵循最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（顺序mysql会自动优化，但是推荐按顺序）
   

3. 不在索引列上做任何操作（计算、函数、（自动或手动，自动转换通常会把字符的一方转为数字，可能是列可能是常量）类型转换，等于号右侧的常量可以操作
   

4. 存储引擎不能使用索引中范围条件右边的列，因为在联合索引b+树中，会从左到右比较每个列的大小，比出大小就不会继续往下比，所以只看后续的列，是无序的！

5. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select * 语句

6. mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

7. is null,is not null一般情况下也无法使用索引

8. like以通配符开头’%ame’mysql索引失效会变成全表扫描

9. 字符串不加单引号索引失效（类型可能自动转换）

10. 少用or或in，使用它查询时，mysql不一定使用索引。mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

11. 范围查询优化
    给年龄加单值索引，将大范围拆分为多个小范围
    

使用总结