MySQL 索引结构与执行计划分析

三、索引（重点）

1. 概述和结构

索引极大提升了查询数据的速度，缺点就是索引列占用磁盘空间，降低表增删改的速度（update、insert、delete）。不过缺点不明显，因为主要用到查询。索引本质就是拿空间换时间。

不指定索引类型，默认就是 B+tree 索引。

index 这个单词本身的意思是"索引；指数；标志；指针"，动词是"为...编索引、把...编入索引"。理解了这层意思，就知道索引干的事就是给数据编一份目录。

索引结构简介：二叉树层级深，检索慢。Btree 和 B+tree 的演变过程详见视频 www.bilibili.com/video/BV1Kr4y1i7ru。

为什么 InnoDB 选 B+tree 而不是别的：

为什么 InnoDB 用 B+tree 而不是二叉树、B-tree 或 Hash

相对于二叉树，层级更少，搜索效率高
相对于 B-tree，B-tree 的叶子节点和非叶子节点都要存数据，一页里能放的键值就少，指针跟着少，要存同样多的数据只能加高树，性能下降
相对于 Hash 索引，B+tree 支持范围匹配和排序

Hash 索引的特点也说一下：只能用于对等比较（=、in），不支持范围查询（between、>、<），也没法利用索引完成排序，不过查询效率高，通常一次检索就够。

B+tree 的结构记三句话就够了：

非叶子节点 = 路标（只存"往哪走"）
叶子节点 = 真正的书页（存完整数据行）
叶子节点手拉手 = 链表（方便范围扫描）

2. 索引分类

一定要理解聚集索引和二级索引的结构，由此才能进行 SQL 优化。

按用途分，索引有这四种：

主键索引：针对表中主键创建的索引，默认自动创建，只能有一个，关键字 PRIMARY
唯一索引：避免同一个表中某数据列中的值重复，可以有多个，关键字 UNIQUE
常规索引：快速定位特定数据，可以有多个
全文索引：查找的是文本中的关键词，而不是比较索引中的值，可以有多个，关键字 FULLTEXT

在 InnoDB 里按存储形式分两种：

聚集索引（Clustered Index）：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，必须有而且只有一个
二级索引（Secondary Index）：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个

聚集索引的选取规则：

聚集索引选取规则：有主键用主键，没主键用第一个唯一索引，都没有就生成隐藏 rowid

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

有主键的情况：聚集索引的叶子节点的数据就是一行数据。

二级索引叶子节点的数据不是整行，而是主键 id：

通过二级索引查数据的过程（以 name 字段为例）：查 name 会先通过二级索引查到主键值，然后通过聚集索引查对应的行数据，这个过程称为回表查询。

所以结论是：查主键效率高，查其余字段需要回表查询。

InnoDB 的 B+Tree 高度通常是 2~4 层：

高度为 2：可以存约 2 万条数据
高度为 3：可以存约 2200 万条数据
高度为 4：可以存约 25 亿条数据

3. 语法和查询过程

3.1 建索引的语法

# 一个索引可以关联多个字段（联合索引）
# 创建索引
create [unique|fulltext] index [索引名称] on [表名](字段名, ...);

# 查看指定表的索引
show index from [表名];

# 删除指定表的索引
drop index [索引名称] on [表名];

准备数据：

# 创建用户表
create table tb_user (
    id int(11) not null auto_increment comment '用户id',
    name varchar(20) not null comment '姓名',
    phone varchar(20) not null comment '手机号',
    email varchar(50) not null comment '邮箱',
    profession varchar(50) default null comment '专业',
    age int(3) default null comment '年龄',
    gender char(1) default null comment '性别 1-男 2-女',
    status char(1) default null comment '状态',
    createtime datetime default null comment '创建时间',
    primary key (id)
) engine = innodb default charset = utf8mb4 comment = '用户表';

# 插入数据到用户表
insert into tb_user (
    id, name, phone, email, profession, age, gender, status, createtime
) values
(1, '吕布', '17799990000', '1vbu666@163.com', '软件工程', 23, 1, 6, '2001-02-02 00:00:00'),
(2, '曹操', '17799990001', 'caoca066@qq.com', '通讯工程', 33, 1, 0, '2001-03-05 00:00:00'),
(3, '赵云', '17799990002', '17799990019.com', '英语', 34, 1, 2, '2002-03-02 00:00:00'),
(4, '孙悟空', '17799990003', '177999908sina.com', '工程造价', 54, 1, 0, '2001-07-02 00:00:00'),
(5, '花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, 2, 1, '2001-04-22 00:00:00'),
(6, '大乔', '17799990005', 'daqiao66@sina.com', '舞蹈', 22, 2, 0, '2001-02-07 00:00:00'),
(7, '露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, 2, 0, '2001-02-08 00:00:00'),
(8, '程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, 1, 5, '2001-05-23 00:00:00'),
(9, '项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, 1, 0, '2001-09-18 00:00:00'),
(10, '白起', '17799990009', 'baig166@sina.com', '机械工程及其自动化', 27, 1, 2, '2001-08-16 00:00:00'),
(11, '韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, 1, 0, '2001-06-12 00:00:00'),
(12, '荆轲', '17799990011', 'jingke123@163.com', '会计', 29, 1, 0, '2001-05-11 00:00:00'),
(13, '兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, 1, 1, '2001-04-09 00:00:00'),
(14, '狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, 1, 2, '2001-04-10 00:00:00'),
(15, '貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, 2, 3, '2001-02-12 00:00:00'),
(16, '妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, 2, 0, '2001-01-30 00:00:00'),
(17, '半月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, 2, 0, '2000-05-03 00:00:00'),
(18, '赢政', '17799990017', '8839434342@qq.com', '化工', 38, 1, 1, '2001-08-08 00:00:00'),
(19, '狄仁杰', '17799990018', 'jujiamlm816@163.com', '国际贸易', 30, 1, 0, '2007-03-12 00:00:00'),
(20, '安琪拉', '17799990019', 'jdodmlh@126.com', '城市规划', 51, 2, 0, '2001-08-15 00:00:00'),
(21, '典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, 1, 2, '2000-04-12 00:00:00'),
(22, '廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, 1, 3, '2002-07-18 00:00:00'),
(23, '后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, 1, 0, '2002-03-10 00:00:00'),
(24, '姜子牙', '17799990023', '374838448@qq.com', '工程造价', 29, 1, 4, '2003-05-26 00:00:00');

建索引：

# 索引名称一般写为：idx_表名简写_字段名
# 给 name 字段建索引
create index idx_user_name on tb_user(name);
# 不指定索引结构默认使用B+tree

# 为 phone 创建 唯一 索引
create unique index idx_user_phone on tb_user(phone);

# 创建联合索引（注意顺序，索引名按照顺序写，之后会提到）
create index idx_user_pro_age_sta on tb_user(profession,age,status);

# 给 email 字段：
create index idx_user_email on tb_user(email);

# 删除
drop index idx_user_email on tb_user;

show index from tb_user 里那一列 Index_type 显示的是 BTREE，B+tree 属于 Btree 大类，所以显示成 BTREE 是正常的。

show index 结果里的 Column_name 列，红框是 name 字段的索引

联合索引会按照创建时的顺序分配序号，看 Seq_in_index 那一列：

create index 索引名 on tb_user(profession,age,status)

3.2 查询过程（联合索引的五种情况）

先记住两句话：主键索引就是聚集索引，除聚集索引（主键索引）外，所有索引统称二级索引。

# 主键查询（聚集索引）
select * from tb_user where id = 1;
# 直接走聚集索引，叶子节点拿到完整行数据，1 次 b+tree 查找

# 非主键查询（二级索引）
select * from tb_user where name = '吕布';
# 先走 idx_user_name 索引，叶子节点拿到主键值 id=1
# 再用 id=1 回表走聚集索引，拿到完整行数据
# 共 2 次 b+tree 查找

索引中值重复的时候，叶子节点是按 (name, id) 存的，所以相同 name 的行是连着的：

# 以普通索引 idx_user_name 为例，name 字段存在重复值

# 表数据示例
# id=1, name='吕布'
# id=5, name='吕布'
# id=9, name='吕布'

# 查询语句
select * from tb_user where name = '吕布';

# 查找过程
# 1. 走 idx_user_name 索引，b+tree 叶子节点按 (name, id) 存储
# 2. 定位到 name='吕布' 的第一个叶子节点
# 3. 沿叶子节点链表向后扫描，直到 name 不等于 '吕布'
# 4. 获取所有匹配的主键值：1, 5, 9
# 5. 依次回表聚集索引，返回完整行数据

# 叶子节点链表结构示意（idx_user_name）
# ('安琪拉', 20) -> ('白起', 10) -> ('曹操', 2) -> ('吕布', 1) -> ('吕布', 5) -> ('吕布', 9) -> ('吕布', 15) -> ('赵云', 3)
#                                                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#                                                          只遍历这一段，找到 '赵云' 即停止

叶子节点相同的字段值是连续的，不会出现被其他不一样的值隔开扫不到的情况。

# 若查询只针对索引字段本身，无需回表
# 只需二级索引的查询
select name from tb_user where name = '吕布';
# 走 idx_user_name 索引，叶子节点存储了 name 和 id
# 直接返回 name 值，无需回表

# 仍需回表的查询
select * from tb_user where name = '吕布';
# 二级索引查不到其他字段（phone、email 等），必须回表取完整行

联合索引 idx_user_pro_age_sta (profession, age, status) 的叶子节点按三列依次排序，所以条件怎么给，索引就用到哪一列：

# b+tree 叶子节点存储结构按 (profession, age, status) 排序
# 示例数据排列（简化为 (专业, 年龄, 状态, id)）
('软件工程', 23, 6, 1)
('软件工程', 23, 1, 5)
('软件工程', 31, 0, 16)
('软件工程', 40, 3, 15)
('工程造价', 29, 4, 24)
('工程造价', 44, 1, 13)
('工程造价', 54, 0, 4)
('通讯工程', 33, 0, 2)
# 先按 profession 排序，profession 相同再按 age 排序，age 相同再按 status 排序

# 情况1：条件匹配全部三个字段（索引完全生效）
select * from tb_user where profession = '软件工程' and age = 23 and status = 6;
# 直接定位到 (软件工程,23,6) 组合，精确查找

# 情况2：条件包含前两个字段（索引部分生效，中间字段生效）
select * from tb_user where profession = '软件工程' and age = 23;
# 定位到 (软件工程,23,*) 段，age 条件继续缩小区间

# 情况3：条件只包含第一个字段（索引生效，但只用到第一个字段）
select * from tb_user where profession = '软件工程';
# 定位到 '软件工程' 整个连续段

# 情况4：条件跳过第一个字段直接查第二个（索引失效）
select * from tb_user where age = 23;
# 跳过了 profession，无法利用有序性，全表扫描

# 情况5：条件包含第一个和第三个，跳过第二个（索引部分生效）
select * from tb_user where profession = '软件工程' and status = 6;
# 先定位到 '软件工程' 段，但 age 条件缺失，该段内全部扫描，再过滤 status
# profession 生效，status 不生效（因为跳过了 age）

那为什么不干脆给主键之外的全部字段建一个联合索引？

# 联合索引很灵活，为什么不直接给主键之外的 全部 字段建立联合索引？

索引体积膨胀：每个字段值都存入 b+tree 节点，索引文件可能超过表数据本身
写入性能暴跌：每次 insert/update 需维护 20 个字段排序，写入耗时增加数倍
维护成本：删除或修改字段时需重建索引，变更窗口延长
最左前缀限制：查询从 profession 开始才生效，查 email 仍走不了该索引
优化器选择困难：索引过长时优化器统计信息偏差，可能弃用该索引走全表扫描

# 正确做法：按业务查询模式精确设计
# 业务查询模式：用户端按手机号查 + 管理后台按专业+年龄筛选
# 分别建两个短索引即可
create index idx_phone on tb_user(phone);
create index idx_pro_age on tb_user(profession, age);

# 而不是把所有字段塞进一个索引

3.3 为什么非建索引不可

先看只靠主键索引会是什么下场：

场景	主键索引是否生效	查找效率
where id = 1	生效	极高（b+tree 直接定位）
where phone = '17799990000'	不生效	全表扫描，24 行数据遍历
where name like '吕布%'	不生效	全表扫描
order by createtime	不生效	全表扫描后排序
join 其他表 on phone	不生效	全表扫描驱动表

业务查询条件极少只用主键，多数场景用非主键字段过滤，为这些字段创建索引就能避免全表扫描。索引本质是空间换时间，每增加一个索引占用额外磁盘空间，但显著提升查询速度。

三种索引的取舍：

对比维度	普通索引（index）	唯一索引（unique index）	联合索引（复合索引）
字段数量	1个字段	1个字段	2个及以上字段
约束功能	无唯一性约束	强制字段值全局唯一	无唯一性约束
查询效率	等值查询 = 唯一索引	等值查询 = 普通索引	依赖最左前缀，部分场景效率更高
额外作用	仅加速查询	加速查询 + 保证数据唯一性	加速查询 + 减少回表（覆盖索引）
典型业务场景	高频查询字段（name）	业务唯一标识（phone、email）	多条件组合查询（where name + phone）
写入性能影响	插入/更新需维护索引	插入/更新需维护索引 + 额外唯一校验	插入/更新需维护多个字段排序，开销略高

为什么不直接手敲 SQL 查就行，为了查数据值得建那么多麻烦的索引吗？

索引是为业务服务的，不是给 sql 查询语法服务的（并非控制台手敲）。主要场景是客户端调用了大量 sql 查询，然后根据索引的类型进行查询，由此实现高效查询。

四、SQL 性能分析

4.1 先看执行频次

要优化查询语句，因为它用得非常多，而索引优化占主导。

# 查看数据库的增删改查访问频次
# 后面的"_"代表七个字符
show global status like 'Com_______';
# 查看 查询 为主还是 增删改 为主，前者就需要优化，后者基本不用优化
# 下面显示了增删改查的次数
Com_binlog,0
Com_commit,8
Com_delete,4
Com_import,0
Com_insert,32
Com_repair,0
Com_revoke,0
Com_select,2882
Com_signal,0
Com_update,9
Com_xa_end,0
# 查询占了绝大部分，需要优化

4.2 慢查询日志

只是知道 select 占比高还不够，得知道是哪些 sql 慢，这时候就靠慢查询日志。

mysql 的慢查询日志默认关闭：

阈值默认 10s：

要在 /etc/my.cnf 里配置：

# variables 译为变量
# 查看慢查询日志状态
show variables like 'slow_query_log';

# 查看慢查询阈值
show variables like 'long_query_time';

# 开启慢查询日志（临时生效，重启失效）
set global slow_query_log = 1;

# 设置慢查询阈值（临时生效，重启失效）
set global long_query_time = 2;

# 永久生效需修改配置文件 /etc/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/lib/mysql/mysql-slow.log

# mysql慢查询日志的信息位置：/var/lib/mysql/localhost-slow.log
# 慢查询日志只会记录慢查询的内容，比如

官方镜像的配置加载逻辑是：/etc/my.cnf 只是个入口，它会通过 !includedir 自动加载 /etc/mysql/conf.d/ 目录下的所有 .cnf 文件（镜像里该目录默认是空的，就是专门留给使用者加自定义配置的）。而且直接修改容器会在重建时丢失，最好修改挂载到 linux 的文件。

# inspect 看一下docker挂载
# 发现没有挂载 /etc/my.cnf，只能进入容器修改
        "Mounts": [
            {
                "Type": "bind",
                "Source": "/opt/mysql/conf.d",
                "Destination": "/etc/mysql/conf.d",
                "Mode": "",
                "RW": true,
                "Propagation": "rprivate"
            },
            {
                "Type": "bind",
                "Source": "/opt/mysql/logs",
                "Destination": "/var/log/mysql",
                "Mode": "",
                "RW": true,
                "Propagation": "rprivate"
            },
            {
                "Type": "bind",
                "Source": "/opt/mysql/data",
                "Destination": "/var/lib/mysql",
                "Mode": "",
                "RW": true,
                "Propagation": "rprivate"
            }
        ],

# 直接修改linux挂载文件
cat > /opt/mysql/conf.d/slow-query.cnf << 'EOF'
[mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/lib/mysql/mysql-slow.log
EOF

docker restart mysql01

改完再查，slow_query_log 变成 ON：

阈值也跟着变成了 2 秒：

# 查看docker容器内的文件
docker exec -it mysql01 cat /etc/my.cnf
# For advice on how to change settings please see
# https://dev.mysql.com/doc/refman/26.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

host-cache-size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
# 使用include-dir加载了该配置文件下的所有配置

为什么配置里必须写 [mysqld]？

自动加载和写 [mysqld] 是两回事。自动加载说的是不用在主配置里手动 include 这个文件，它会被自动包含进来；但被包含进来的内容，依然要按 MySQL 配置文件的语法写。

MySQL 配置文件是分段的格式：

[mysqld]
slow_query_log = 1
long_query_time = 2

[mysqld] 是段名，也叫分组名，表示下面这些配置项属于 MySQL 服务器程序（mysqld）。配置文件里可以有很多段：

[client] → 所有客户端程序读取
[mysql] → 命令行客户端 mysql 读取
[mysqld] → 服务器程序 mysqld 读取
[mysqldump] → mysqldump 工具读取

如果省略 [mysqld] 直接写：

slow_query_log = 1

这样 mysqld 会忽略这些配置，自然就不生效了。

conf.d 的加载细节：/etc/my.cnf 里最后一行 !includedir /etc/mysql/conf.d/ 就是自动加载的开关。!includedir 是 MySQL 配置文件的特殊指令，意思是"启动时自动把这个目录下所有 .cnf 文件都读进来"。加载流程是：

启动 mysqld
  ↓
读取 /etc/my.cnf
  ↓
遇到 !includedir /etc/mysql/conf.d/
  ↓
扫描该目录下所有 *.cnf 文件
  ↓
按文件名排序，依次加载
  ↓
所有配置项合并到同一个配置空间

几条具体规则：

只加载 .cnf 后缀的文件。/opt/mysql/conf.d/slow-query.cnf 会加载，/opt/mysql/conf.d/readme.txt 会被忽略
按文件名顺序加载（字典序）。比如 01-lower-case.cnf、02-slow-query.cnf、99-other.cnf 就是这个顺序，后面加载的配置会覆盖前面加载的同类配置
重复配置项以最后加载的为准。/etc/my.cnf 里写了 max_connections = 100，/opt/mysql/conf.d/my.cnf 里写了 max_connections = 200，最终生效的是 200，因为 conf.d 里的文件排在后面读

验证的时候有几个办法：

mysqld --validate-config 2>&1

mysqld --verbose --help 2>/dev/null | grep -A 1 "Default options"

mysql -uroot -p123456 -e "SHOW VARIABLES LIKE 'slow_query_log';"

看到 ON 就说明 slow-query.cnf 被正确加载了。mysqld --verbose --help 的输出里会列出 Default options are read from the following files in the given order:，那张表里没有 conf.d/ 是正常的 —— !includedir 是在 /etc/my.cnf 内部展开的，所以不会出现在这个列表里，但不代表它不读取。

一句话总结：!includedir 是主配置文件的"插件接口"，conf.d 目录就是插件目录。把自定义配置丢进去就自动生效，典型的"约定优于配置"。

4.3 show profiles

有些 sql 语句没到慢查询的线，比如 1.9s，这时候用 show profiles 看这些语句的耗费时间。

先看数据库支不支持这个命令，也就是看 have_profiling 参数是不是 yes：

@@have_profiling 为 YES，说明支持 show profiles

profiling 默认是关闭的，可以通过 set 在 session/global 级别开启：

select @@profiling;
set profiling = 1;

状态为 0 默认关闭，需要设置开启：

# profile 译为轮廓
# 执行一些查询sql后查看耗时情况
show profiles;

# 找指定的sql查询语句在各阶段耗时情况
show profile for query [query_id];

# 查看指定 query_id 的sql使用CPU情况
show profile cpu for query [query_id];

耗时显示单位默认为 s：

4.4 EXPLAIN

通过 explain 判断 sql 性能很重要。

# 直接在 select 语句之前加上关键字 explain / desc
explain select 字段列表 from 表名 where 条件;
返回内容
-> Table scan on tb_user  (cost=2.65 rows=24)

# 在 MySQL 8.0.18+ 中，EXPLAIN 支持三种格式：

# format=traditional → 传统的表格（表格）
# format=json → json 格式
# format=tree → 树形格式（默认）

explain format=traditional [查询sql]

拿个多对多的表来举例：

执行 sql 看 explain 输出：

explain 结果里 id、select_type、table 等列，红框标的是 id 和 table

逐列说明：

【id】：id 大的先执行，id 相同则从上到下
【select_type】：说明当前 sql 的查询类型
【type】：连接类型，性能由好到差为 null、system、const、eq_ref、ref、range、index、all

# type 连接类型，性能由好到差排列
# null        : 不访问任何表（如 select 1 + 1）
# system      : 表只有一行数据（系统表）
# const       : 主键或唯一索引等值查询，最多返回一行
# eq_ref      : 连接查询中被驱动表使用主键或唯一索引，每次返回一行
# ref         : 非唯一索引等值查询，可能返回多行
# range       : 索引范围扫描（between、>、<、like 前缀）
# index       : 全索引扫描（扫描整个索引树）
# all         : 全表扫描（无索引或索引失效）

【possible_key】：显示表中可能用到的索引
【key】：实际使用的索引，没有为 null
【key_len】：索引中使用的字节数（该值为索引字段最大可能长度，并非实际使用长度），在不损失精确性的前提下，长度越短越好
【rows】：mysql 认为必须要执行查询的行数，在 innodb 引擎的表中是一个估计值，可能并不总是准确的
【filtered】：表示返回结果的行数占需读取行数的百分比，值越大越好
【extra】：额外信息

extra 这列后面会反复用到，先在脑子里挂几个常见值：using index 是覆盖索引不用回表，using index condition 是索引条件下推（还要回表），using where 是回表之后才过滤，using filesort 是排序没走索引。下一页讲索引失效和使用规则的时候全靠它判断。

5. 使用规则（一）：最左前缀和几种失效

5.1 最左前缀法则

# 最左前缀法则定义：联合索引 (a, b, c) 中
# 查询条件必须从索引的最左列开始连续匹配，索引才能生效
# 跳过中间列或未从第一列开始，后续列索引失效（最左不可跳）

# 联合索引定义
create index idx_user_pro_age_sta on tb_user(profession, age, status);

# b+tree 叶子节点存储结构按 (profession, age, status) 排序
# 示例数据排列（简化为 (专业, 年龄, 状态, id)）
('软件工程', 23, 6, 1)
('软件工程', 23, 1, 5)
('软件工程', 31, 0, 16)
('软件工程', 40, 3, 15)
('工程造价', 29, 4, 24)
('工程造价', 44, 1, 13)
('工程造价', 54, 0, 4)
('通讯工程', 33, 0, 2)
# 先按 profession 排序，profession 相同再按 age 排序，age 相同再按 status 排序

# 情况1：条件匹配全部三个字段（索引完全生效）
explain select * from tb_user where profession = '软件工程' and age = 23 and status = 6;
# 直接定位到 (软件工程,23,6) 组合，精确查找

返回：-> Index lookup on tb_user using idx_user_pro_age_sta (profession = '软件工程', age = 23), with index condition: (tb_user.`status` = 6)  (cost=0.52 rows=2)


# 情况2：条件包含前两个字段（索引部分生效，中间字段生效）
select * from tb_user where profession = '软件工程' and age = 23;
# 定位到 (软件工程,23,*) 段，age 条件继续缩小区间

# 情况3：条件只包含第一个字段（索引生效，但只用到第一个字段）
select * from tb_user where profession = '软件工程';
# 定位到 '软件工程' 整个连续段

# 情况4：条件跳过第一个字段直接查第二个（索引失效）
select * from tb_user where age = 23;
# 跳过了 profession，无法利用有序性，全表扫描

# 情况5：条件包含第一个和第三个，跳过第二个（索引部分生效）
select * from tb_user where profession = '软件工程' and status = 6;
# 先定位到 '软件工程' 段，但 age 条件缺失，该段内全部扫描，再过滤 status
# profession 生效，status 不生效（因为跳过了 age）

上面那个返回结果是 explain 的 tree 格式（默认格式），逐段拆解一下：

# 返回信息解读（format=tree 格式）

# 输出：
# -> Index lookup on tb_user using idx_user_pro_age_sta (profession = '软件工程', age = 23), with index condition: (tb_user.`status` = 6)  (cost=0.52 rows=2)

# 逐段拆解：
# 1. "Index lookup on tb_user using idx_user_pro_age_sta"
#    → 使用了 idx_user_pro_age_sta 索引进行索引查找
#    → 定位到 profession = '软件工程' 且 age = 23 的索引段

# 2. "with index condition: (tb_user.`status` = 6)"
#    → 索引条件下推（index condition pushdown, icp）
#    → status = 6 在索引层过滤，而非回表后再过滤

# 3. "cost=0.52"
#    → 优化器估算的查询成本，单位是随机 io 成本（非实际耗时）

# 4. "rows=2"
#    → 估算返回行数为 2 行（即 status = 6 的行数）


# icp（索引条件下推）说明
# 无 icp：索引查 (profession, age) 匹配行 → 回表取整行 → 在 server 层过滤 status
# 有 icp：索引查 (profession, age) 匹配行 → 在索引层过滤 status → 仅对 status=6 的行回表
# icp 减少回表次数，是优化器自动开启的优化

# 当前查询执行流程
# 1. 索引查找定位到 (软件工程, 23) 段
# 2. 该段内有 2 行数据（status 分别为 1 和 6）
# 3. 索引条件下推：在索引层过滤 status=6
# 4. 仅对符合 status=6 的那一行回表取完整数据
# 5. 返回结果


# explain 返回信息明确写了：
# ... with index condition: (tb_user.`status` = 6)
# 
# "index condition" 即表示 status 在索引层参与了过滤
# 但关键点：status 不是用于缩小 b+tree 扫描范围，而是用于过滤索引中已扫描到的行

5.2 范围查询

# 联合索引 (profession, age, status)
# 规则：范围查询（>、<）右侧的列索引失效
# >=、<= 不属于范围查询，不影响右侧列

# 案例1：age > 30（范围查询）
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
# 索引使用情况：profession 生效，age 生效（用于筛选 >30 的范围），status 失效
# 原因：age 为范围查询，age 之后的 status 无法利用索引有序性继续缩小范围

返回：-> Index range scan on tb_user using idx_user_pro_age_sta over (profession = '软件工程' AND 30 < age), with index condition: ((tb_user.`status` = '0') and (tb_user.profession = '软件工程') and (tb_user.age > 30))  (cost=1.16 rows=2)


# 案例2：age >= 30（非范围查询）
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';
# 索引使用情况：profession 生效，age 生效，status 生效
# 原因：>= 属于等值查询范畴（查的是边界值），不破坏索引连续性

5.3 or 连接

# 规则：or 两侧都有索引才生效，否则失效
# 本质：or 相当于分别查询两个条件再合并结果
# 若一侧无索引，则需全表扫描，无法使用索引

# 索引定义
create index idx_profession on tb_user(profession);
create index idx_age on tb_user(age);
# status 无索引

# 生效（两侧都有索引）
explain select * from tb_user where profession = '软件工程' or age = 23;
# 走索引合并（index_merge），profession 和 age 各自走索引后取并集

# 失效（status 无索引）
explain select * from tb_user where profession = '软件工程' or status = '0';
# profession 索引生效但 status 无索引，or 导致全表扫描

# 失效（profession 有索引，age 有索引，但联合索引不适用）
create index idx_pro_age on tb_user(profession, age);
explain select * from tb_user where profession = '软件工程' or age = 23;
# 联合索引 (profession, age) 中 age 查询跳过 profession，无法使用该索引
# or 两侧均无独立索引，全表扫描
# 需为 profession 和 age 分别建立独立索引

5.4 数据分布的影响

# 如果 mysql 评估使用索引比全表更慢，则不使用索引

# 比如查到的数据在整张表的靠后位置，就可能走全表扫描

# 示例：表中 status 字段大部分值为 '0'，少数为 '1'
# 查询 status = '0' 时，优化器评估回表成本高于全表扫描，放弃索引
explain select * from tb_user where status = '0';   # 全表扫描
# 查询 status = '1' 时，返回行数少，使用索引
explain select * from tb_user where status = '1';   # 走索引

# 回表成本估算逻辑
# 二级索引查询 = 索引扫描 + 回表（随机 io）
# 若匹配行数超过表总行数 20%~30%，全表扫描（顺序 io）更快
# 优化器根据索引统计信息（cardinality）和实际数据分布决策

NULL 值多的时候优化器也会放弃索引：查 profession is null 走的是 idx_user_pro_age_sta，查 profession is not null 反而 key 变成 NULL 走全表扫描了。

profession is null 走索引，is not null 却全表扫描

5.5 失效的七种情况

前面那些规则，收在一起就是一张清单：

# 索引失效的七种情况

# 1. 最左前缀不满足（联合索引）
create index idx_a_b_c on tb_user(a, b, c);
where a = 1 and c = 3;          # a 生效，c 失效（跳过 b）
where b = 2;                    # 全部失效（未从 a 开始）

# 2. 范围查询（>、<）右侧列失效
where a = 1 and b > 2 and c = 3;  # a、b 生效，c 失效
# >=、<= 不影响右侧列，视为等值

# 3. 索引列参与计算或函数
where age + 1 = 24;             # 失效
where date(create_time) = '2026-08-23';  # 失效

# 4. 隐式类型转换
# phone 字段为 varchar 类型
where phone = 17799990000;      # 失效（数字转字符串比较，索引列隐式转换）
where phone = '17799990000';    # 生效（类型匹配）

# 5. like 左模糊
where name like '%云';           # 失效
where name like '赵%';           # 生效（右模糊）

# 6. or 两侧之一无索引
# profession 有索引，status 无索引
where profession = '软件工程' or status = '0';  # 整体失效

# 7. 数据分布导致优化器弃用索引
# status 字段 90% 为 '0'，10% 为 '1'
where status = '0';             # 全表扫描（回表成本高于全表）
where status = '1';             # 走索引（返回行数少）

SQL 提示、覆盖索引、前缀索引、联合索引、设计原则这几块接着下一页讲。

#MySQL #索引 #B+Tree #EXPLAIN

← 上一页MySQL 存储引擎与 Linux 安装下一页 →MySQL 索引使用规则与 SQL 优化