3. Explain详解与索引最佳实践

3.1 Explain使用与详解

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user	NULL	ref	idx_name_age_dpt	idx_name_age_dpt	1023	const	1	100.0	NULL
中	重要	中	低	重要	重要	重要	重要		一般	一般	一般

通过EXPLAIN或DESC命令获取MySQL如何执行SELECT语句信息，包括SELECT语句执行过程中表如何连接和连接的顺序

# 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT column_... FROM table_name_... where condition_...;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

• id: 表的执行顺序。id相同，则从上往下执行。如果id不相同，则值大的先执行

• select_type: 表示select的类型。

  • SIMPLE简单表（无表连接或子查询）

  • PRIMARY（主查询，即最外层的查询）

  • UNION（连接的第二个或者后面的查询）

  • SUBQUERY(子查询)

• type：表示连接类型。性能由好到差依次是：NULL，system、const、eq_ref、ref、range、index、all

  • NULL：不访问任何表

  • system：访问系统表

  • const：唯一索引查询 (select * from table_name where id = 1)

  • eq_ref: 连表查询的等值比较, 有唯一索引(select a.*, b.* from a,b where a.id = b.id 如果a.id是a唯一索引，则a为eq_ref )

  • ref：非唯一性索引查询 (select * from table_name where name = "zs")

  • range: 使用了索引, 范围查找

  • index：用了索引，但遍历整个索引树 (select * from table_name)

  • all: 全表扫描, 从聚集索引的叶子节点链表依次向后扫描

• possible_key: 显示可能应用在这张表上的索引，一个或多个

• key： 实际用到的索引

• key_len: 使用的索引字节数（索引最大可能长度）越短越好

• ref: 哪些列或者常量被用做索引列上的值. ref列显示为 NULL 时，这通常意味着查询没有使用索引进行等值比较

  • const: 使用常量用作索引的值

• rows：预估值，需要执行查询的行数

• filtered：查询结果行数占读取行数的百分比% (表头也在结果行中)

• Extra: 额外信息

  • using index: 使用覆盖索引: 查询的内容可以从使用的索引树中获取, 则用到了覆盖索引
  • using where: 使用where语句来处理结果, 并且查询的内容没有被索引覆盖

explain select * from user where name = "张三" ;
show warnings;

当执行上述sql后, 会展示出mysql对sql语句的优化

# Level	Code	Message
# Note	1003	/* select#1 */ select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`age` AS `age`,`test`.`user`.`dpt` AS `dpt`,`test`.`user`.`date` AS `date` from `test`.`user` where (`test`.`user`.`name` = '张三')

/* select#1 */ select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`age` AS `age`,`test`.`user`.`dpt` AS `dpt`,`test`.`user`.`date` AS `date` from `test`.`user` where (`test`.`user`.`name` = '张三')

如果一个查询的数据可以即可通过主键索引查询到, 也可以通过二级索引查询到, 那么优先会通过二级索引进行查询. 因为二级索引所占空间小, 查询效率高.( 硬盘读取到内存)

select id, name where name = "zs" # 二级索引name直接查到
select id, name where id = 1; # 主键索引id直接查到
select * from user where name = "zs"; # 二级索引查到主键，再进行回表查询

Extra 字段关键值	是否回表	含义解析
Using index	否	表示查询使用了覆盖索引，所需数据全部在索引中，无需回表到聚簇索引获取完整行。
Using index condition	否	表示使用了索引下推优化，过滤在存储引擎层进行，但仍然可能需要回表获取其他字段。
Using where	是	表示服务器层需要在从存储引擎拿到回表后的完整数据行后，再根据 WHERE 条件进行过滤。

3.2 从B+树底层分析常见索引优化原则

3.2.1 索引失效场景

模型数空运最快

1. 模糊查询： %放在了模糊匹配的头部

# 头部模糊匹配，索引失效
SELECT * FROM table_a WHERE name like '%三'; # 全表扫描 ALL

# 尾部模糊匹配，索引不失效
SELECT * FROM table_a WHERE name like "张%"

2. 数据类型： 发生隐式类型转换

where phone = 123; # 隐式类型转换，索引失效

3. 函数： 索引字段使用函数 或者进行了运算操作

运算操作之后后, 对应的结果在索引树中没有这个值. 无法定位

SUBSTR, UPPER, LOWER

4. 空值查询： is not null

如果MySQL评估使用索引比全表查询更慢,则不使用索引;

# 不走索引
explain select * from pms_attr where attr_id is not null;

5. 运算：where id - 1 = 3

6. 最左前缀： 使用联合索引跳过了中间某列

如果索引了多列（联合索引：一个索引关联了多个字段），要遵循最左前缀法则。最左前缀法则指从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，则索引将部分失效（后面的字段索引失效）

# 背景: table_a 表中有三个字段的联合索引 name age gender 

# 1. 联合索引全部生效
SELECT * FROM table_a where name = "zs" and age = 18 and gender = "man";  
SELECT * FROM table_a where age = 18 and gender = "man" and name = "zs"; # 也会生效，因为name存在

# 2. 联合索引部分生效 name字段生效
SELECT * FROM table_a where name = "zs" and gender = "man";

# 3. 联合索引全部失效
SELECT * FROM table_a where age = 18 and gender = "man";

在MySQL 8.0.13 版本引入了Skip Scan Range Access Method，它在一定条件下可以不遵守最左前缀原则，通过范围扫描的方式来代替了全表扫描。

7. 范围多条件：范围查询后再使用其他条件查询

范围查询结果集中，其后的列值不再有序.

#  最右侧列 gender 失效
SELECT * FROM table_a where name = "zs" and age > 18 and gender = "man";

# 使用大于等于 >= 则不失效
SELECT * FROM table_a where name = "zs" and age >= 18 and gender = "man";

数据量大的范围查询可能不走索引

8. 使用 OR

如果 OR 连接的多个条件中有一个没有使用索引，可能会导致索引失效。

9. 大小判断之后: between, >, < 后使用索引

#  最右侧列 gender 失效
SELECT * FROM table_a where name = "zs" and age > 18 and gender = "man";

# 使用大于等于 >= 则不失效
SELECT * FROM table_a where name = "zs" and age >= 18 and gender = "man";

优化原则	核心要点	背后的 B+ 树原理
最左前缀原则	联合索引的列顺序至关重要。查询条件必须包含联合索引的最左列。	B+ 树的键值按索引定义的顺序排序（先按第一列，再第二列，以此类推）。无法跳过左侧列直接利用右侧列的有序性。
覆盖索引	索引包含查询所需的所有字段，避免回表。	所有需要的数据已在索引的叶子节点中，引擎无需根据指针再次查找主键数据页（对于 InnoDB 辅助索引，避免了回表）。
高性能索引列选择	优先为高选择性（区分度高）的列创建索引。	B+ 树能快速过滤掉不匹配的数据。高选择性意味着每个键值能定位更少的行，检索效率更高。
避免索引失效和冗余	避免对索引列进行计算、使用函数、隐式类型转换或前导模糊查询。控制索引数量，删除冗余索引。	对列计算或使用函数会破坏索引键值的有序性，引擎无法直接比较。冗余索引增加维护负担（插入/更新需调整多棵树）且占用空间。
有序性利用	利用索引的天然有序性来优化 ORDER BY和 GROUP BY操作。	B+ 树的叶子节点通过双向链表按索引键顺序连接，支持高效的范围查询和顺序读取，避免昂贵的文件排序（filesort）。
前缀索引	对长字符串字段，仅索引其前 N 个字符。	显著减少单个索引条目的大小，使得每个索引页（Node） 能存储更多键值，降低树高，减少 I/O。但需平衡选择性与存储。

3.3 MySQL索引最佳实践

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

6. MySQL在使用不等于（!= 或 <>）的时候，无法使用索引

核心原则	核心要点	示例/说明
选择性原则	优先为区分度高（不同值多）的列创建索引。	用户ID、手机号等高唯一性字段效果佳；性别、状态等低区分度字段效果差。
最左前缀原则	联合索引的顺序至关重要。查询条件必须包含联合索引的最左列，才能有效利用。	索引 (a, b, c)，条件 WHERE a=1 AND b=2有效；WHERE b=2则无效。
覆盖索引	索引包含查询所需的所有字段，可避免回表，极大提升性能。	SELECT a, b FROM table WHERE a=1;若索引为 (a, b)，则无需回表。
避免冗余索引	精简索引数量。避免功能重叠的索引。	已有 (a, b)，再建 (a)则冗余。(a, b)可支持仅查 a的查询。