2.1 索引概述

2.1.1 介绍

索引 (index) 是帮助 MySQL 高效获取数据的数据结构 (有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用 (指向) 数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2.2 演示

考虑这样一张表：

1. 无索引情况：

   当执行 SELECT * FROM user WHERE age = 45; 时，在没有索引的情况下，MySQL 需要从表的第一行开始扫描，一直扫描到最后一行，这个过程称为全表扫描，性能很低。

   

2. 有索引情况：

   如果针对 age 字段建立了索引，假设索引结构是二叉树，那么会针对 age 这个字段建立一个二叉树的索引结构。

   

   通过这个索引结构，只需要扫描三次就可以找到 age = 45 的数据，极大地提高了查询效率。

2.3 特点

索引的特点可以概括为以下几点：

优势	劣势
提高数据检索的效率，降低数据库的 I/O 成本	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行 INSERT, UPDATE, DELETE 时，效率降低。

2.2 索引结构

2.2.1 概述

MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构	描述
B+Tree 索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash 索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree 索引	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text 索引	是一种通过建立倒排索引，快速匹配文档的方式。类似于 Lucene, Solr, ElasticSearch。

上述是 MySQL 中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况。

索引	InnoDB	MyISAM	Memory
B+tree 索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6 版本之后支持	支持	不支持

2.2.2 二叉树

假如 MySQL 的索引结构采用二叉树的数据结构，比较理想的结构如下：

但是，如果主键是顺序插入的，则会形成一个单向链表，结构如下：

所以，如果选择二叉树作为索引结构，会存在以下缺点：

• 顺序插入时，会形成一个链表，查询性能大大降低。
• 大数据量情况下，层级较深，检索速度慢。

2.2.3 B-Tree

虽然红黑树作为一种自平衡二叉树，在顺序插入数据时能保持平衡，但由于其本质仍然是二叉树，所以在面对大数据量时，层级会变得较深，导致检索速度变慢。

因此，MySQL 并没有选择二叉树或红黑树，而是选择了 B+Tree。为了更好地理解 B+Tree，首先介绍 B-Tree。

B-Tree（B 树）是一种多叉平衡查找树，与二叉树不同，B 树的每个节点可以有多个分支。例如，一个最大度数（max-degree）为 5 (5 阶) 的 B-Tree，其每个节点最多可以存储 4 个 key 和 5 个指针。

B-Tree 的特点包括：

• 对于 5 阶的 B 树，每个节点最多存储 4 个 key，对应 5 个指针。
• 当节点存储的 key 数量达到 5 时，会进行裂变，中间元素向上分裂。
• B 树中，非叶子节点和叶子节点都会存放数据。

2.2.4 B+Tree

B+Tree 是 B-Tree 的变种。以一个最大度数（max-degree）为 4 (4 阶) 的 B+Tree 为例，观察其结构。

B+Tree 的结构可以分为两部分：

• 索引部分（绿色框）： 仅用于索引数据，不存储实际数据。
• 数据存储部分（红色框）： 位于叶子节点，存储具体的数据。

B+Tree 与 B-Tree 的主要区别在于：

• 所有的数据都存储在叶子节点。
• 叶子节点形成一个单向链表，方便范围查询。
• 非叶子节点仅起到索引数据的作用，具体的数据都存储在叶子节点。

上述结构是标准的 B+Tree 数据结构。MySQL 对经典的 B+Tree 进行了优化，在原 B+Tree 的基础上，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的 B+Tree，从而提高了区间访问的性能，并有利于排序。

2.2.5 Hash

除了 B+Tree 索引，MySQL 还支持 Hash 索引。

哈希索引采用一定的 hash 算法，将键值转换成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。

当两个或多个键值映射到同一个槽位时，会产生 hash 冲突（也称为 hash 碰撞），可以通过链表来解决。

Hash 索引的特点包括：

• Hash 索引只能用于对等比较 (=, in)，不支持范围查询 (between, >, <, ...)。
• 无法利用索引完成排序操作。
• 查询效率高，通常（在没有 hash 冲突的情况下）只需要一次检索，效率通常高于 B+Tree 索引。

在 MySQL 中，支持 Hash 索引的是 Memory 存储引擎。InnoDB 存储引擎具有自适应 hash 功能，Hash 索引是由 InnoDB 根据 B+Tree 索引在特定条件下自动构建的。

2.3 索引分类

2.3.1 索引分类

在 MySQL 数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

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

2.3.2 聚集索引 & 二级索引

在 InnoDB 存储引擎中，根据索引的存储形式，索引可以分为以下两种：

• 聚集索引 (Clustered Index)：数据存储与索引放在一起，索引结构的叶子节点保存了行数据。每个表必须有且只能有一个聚集索引。
• 二级索引 (Secondary Index)：数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。一个表可以存在多个二级索引。

聚集索引的选取规则如下：

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

在数据库查询优化中，一个常见的性能考量是回表查询（Covering Index Overhead）。以下以具体 SQL 语句 SELECT * FROM user WHERE name = 'Arm'; 为例，分析其查询过程的细节，说明回表查询的实现机制。该查询的核心目的是通过 name 条件定位数据并返回所有列（SELECT *），这会导致额外的索引查找步骤。

查询过程划分为三个步骤，具体如下：

1. 在二级索引中匹配查找： 由于查询条件是 name = 'Arm'，系统首先访问 name 字段的二级索引。在该索引结构中，键值 'Arm' 仅存储了对应的主键值（例如 10），而不包含完整行数据。因此，索引查找仅返回主键信息，用于后续步骤。

2. 在聚集索引中获取完整数据： 查询要求返回所有列（通过 SELECT * 指定），因此系统需要获取整行数据。根据前一步骤获得的主键值 10，转入聚集索引进行查找。聚集索引基于主键组织数据，能直接定位主键 10 对应的完整行记录（行 row）。

3. 数据返回： 系统最终提取这一行的所有数据值，并返回给用户。整个过程结束。

这个过程被称为回表查询，因为它先在二级索引中找到主键值（一步查找），再“返回”到聚集索引中基于主键值获取完整行数据（另一步查找）。回表查询会增加额外的磁盘 I/O 和延迟，当查询涉及未覆盖的列时（如本例中的 SELECT *），它可能导致性能问题。

思考题

以下两条 SQL 语句，哪个执行效率高？为什么？

• A. SELECT * FROM user WHERE id = 10;
• B. SELECT * FROM user WHERE name = 'Arm';

备注：id 字段为主键，name 字段已创建索引（二级索引）。

分析与解释:

• A 语句的效率更高，理由是其直接利用聚集索引（主键索引）访问数据。
  • 当执行 A 语句时，InnoDB 引擎通过主键索引直接定位到对应的数据行（因为 id 是主键），查询过程仅需一次索引扫描即可返回完整数据，开销较低。
• B 语句的效率较低，因为需要经过回表查询过程。
  • name 字段的索引为二级索引（非聚集索引），查询需先扫描该索引以获取主键值（例如 id），再通过主键索引查询完整数据行。这种额外步骤增加了 I/O 操作和时延，尤其在大数据量场景下性能显著下降。

InnoDB 主键索引的 B+tree 高度如何估算？

关键假设（参数值取自用户输入）:

• 行大小：1 KB（1024 bytes）
• 页大小：16 KB = 16 × 1024 bytes = 16384 bytes
• 指针大小（child pointer 或者页指针）：6 bytes
• 主键大小（这里假设是 BIGINT）：8 bytes

计算原理:
B+tree 高度取决于索引节点存储结构： 非叶节点的每个条目要存 键值 + 指针，总大小要放进 16 KB 页面：

• 每个键（key）占 8 bytes
• 每个指针（pointer）占 6 bytes
• 如果一个节点有 n 个键，就会有 n+1 个指针

所以容量限制是：

$$n \times 8 + (n + 1) \times 6 \leq 16384$$

可得每个非叶节点 至多 放 1170 个键，指针数是 1171 个。

高度为 2 时的计算:

• 根节点（层 1）直接指向叶子页：根能有最多 1171 个子指针
• 每个叶子页存 16 行
• 总行数 ≈ 1171 × 16 = 18 736 行
• 结论: 若高度为 2，索引最多可存储约 18,000 条记录。

高度为 3 时的计算:

• 根 → 中间层 → 叶子层，总共两级索引节点
• 每个中间节点也能有 1171 个子指针
• 总行数 ≈ 1171 (root 指针) × 1171 (次级指针) × 16 (叶子行)
• 1171² × 16 ≈ 1 371 241 × 16 ≈ 21 939 856 行
• 结论: 若高度为 3，索引可存储约 2200 万条记录。

2.4 索引语法

1. 创建索引

   CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... );

2. 查看索引

   SHOW INDEX FROM table_name;

3. 删除索引

   DROP INDEX index_name ON table_name;

为了演示索引的创建过程，我们首先准备了一张名为 tb_user 的表，并插入了一些数据。

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

接下来，我们根据需求创建不同的索引。

• 为 name 字段创建索引。

  CREATE INDEX idx_user_name ON tb_user(name);

  由于 name 字段的值可能会重复，因此创建一个普通的索引即可。

• 为 phone 字段创建唯一索引。

  CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

  phone 手机号字段的值，是非空，且唯一的，因此需要创建唯一索引，保证数据的唯一性。

• 为 profession、age、status 创建联合索引。

  CREATE INDEX idx_user_pro_age_sta ON tb_user (profession, age, status);

  联合索引可以提高多列条件查询的效率。

• 为 email 建立合适的索引来提升查询效率。

  CREATE INDEX idx_email ON tb_user(email);

  为 email 字段创建普通索引，以提升查询效率。

最后，可以通过 SHOW INDEX 语句查看 tb_user 表的所有索引数据，以验证索引是否创建成功。

mysql> SHOW INDEX FROM tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_email            |            1 | email       | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.00 sec)

2.5 SQL 性能分析

2.5.1 SQL 执行频率

连接 MySQL 客户端成功后，可以使用 show [session|global] status 命令来查看服务器状态信息。session 关键字用于查看当前会话的状态，而 global 关键字用于查询全局数据。

通过以下 SQL 指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次：

# session 是查看当前会话;
# global 是查询全局数据;
SHOW GLOBAL STATUS LIKE 'Com_______';

上述 SHOW GLOBAL STATUS 命令使用 LIKE 'Com_______' 模式来匹配以 "Com" 开头，后面跟着 7 个任意字符的状态变量。这允许我们快速检索与命令执行相关的状态信息。

• Com_delete: 删除操作执行的次数。
• Com_insert: 插入操作执行的次数。
• Com_select: 查询操作执行的次数。
• Com_update: 更新操作执行的次数。

通过分析上述指令的输出结果，我们可以了解到当前数据库是以查询为主，还是以增删改为主。这为数据库优化提供了重要的参考依据。

• 增删改为主： 如果数据库以增删改操作为主，那么可能不需要过度关注索引优化。
• 查询为主： 如果数据库以查询操作为主，那么应该考虑对数据库的索引进行优化，以提高查询性能。

好的，下面是对您提供的课件内容整理的详细笔记。

2.5.2 慢查询日志

慢查询日志用于记录执行时间超过指定参数 long_query_time (单位：秒，默认 10 秒) 的所有 SQL 语句。默认情况下，MySQL 的慢查询日志是关闭的。

首先，可以通过以下 SQL 语句查看 slow_query_log 系统变量的状态：

SHOW VARIABLES LIKE 'slow_query_log';

要开启慢查询日志，需要在 MySQL 配置文件中进行如下配置：

# 开启 MySQL 慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完成后，需要重启 MySQL 服务器以使配置生效：

systemctl restart mysqld

然后，再次执行 SHOW VARIABLES LIKE 'slow_query_log'; 确认慢查询日志已开启。

通过分析慢查询日志，可以定位执行效率低的 SQL 语句，并有针对性地进行优化。

2.5.3 Profile 详情

show profiles 能够帮助我们了解 SQL 优化时的时间消耗情况。通过 have_profiling 参数，可以查看当前 MySQL 是否支持 profile 操作：

SELECT @@have_profiling ;

如果 MySQL 支持 profile 操作，但开关是关闭的，可以通过 SET 语句在 session 或 global 级别开启 profiling：

SET profiling = 1;

开启 profiling 后，执行的 SQL 语句都会被 MySQL 记录，并记录执行时间消耗。例如，执行以下 SQL 语句：

select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;

然后，通过以下指令查看 SQL 语句的执行耗时情况：

-- 查看每一条SQL的耗时基本情况
show profiles;

-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

show profiles; 可以查看每一条 SQL 语句的耗时基本情况。show profile for query query_id; 可以查看指定 SQL 语句在各个阶段的耗时情况，从而更详细地了解性能瓶颈所在。

2.5.4 Explain

EXPLAIN 或 DESC 命令可以获取 MySQL 如何执行 SELECT 语句的信息，包括表连接方式和连接顺序。

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

Explain 执行计划中各个字段的含义如下表所示：

字段	含义
id	SELECT 查询的序列号，表示查询中执行 SELECT 子句或者是操作表的顺序 (id 相同，执行顺序从上到下；id 不同，值越大，越先执行)。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE (简单表，即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询)、UNION (UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE 之后包含了子查询) 等。
type	表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为 NULL，则没有使用索引。
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rows	MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。

通过 EXPLAIN 命令，可以分析 SQL 语句的执行计划，从而判断 SQL 语句的性能瓶颈，并进行优化。例如，查看是否使用了索引，连接类型是否高效等。

好的，根据您提供的课件内容，我将为您整理出一份详细的笔记。

2.6 索引使用

2.6.1 验证索引效率

本节通过实例验证索引在提升数据查询性能方面的作用。以包含 1000 万条记录的 tb_sku 表为例，分别在无索引和有索引的情况下，针对不同字段进行查询，对比查询效率。

首先，通过以下 SQL 语句查询 tb_sku 表中的记录总数：

mysql> SELECT COUNT(*) FROM tb_sku;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (7.05 sec)

查询结果显示 tb_sku 表包含 1000 万条记录，耗时 7.05 秒。

由于 id 字段为主键，已经自动创建了主键索引，因此查询 id = 1 的记录速度很快，查询语句如下：

mysql> SELECT * FROM tb_sku WHERE id = 1;
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
| id | sn              | name      | price | num  | alert_num | image                                                                                                                 | images                                                                                                                | weight | create_time         | update_time         | category_name | brand_name | spec  | sale_num | comment_num | status |
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
|  1 | 100000003145001 | 华为Meta1 | 87901 | 9961 |       100 | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp |     10 | 2019-05-01 00:00:00 | 2019-05-01 00:00:00 | 真皮包        | viney      | 白色1 |       39 |           0 | 1      |
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
1 row in set (0.00 sec)

接下来，我们根据 sn 字段进行查询，sn 字段最初是没有索引的，查询语句如下：

mysql> SELECT * FROM tb_sku WHERE sn = '100000003145001';
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
| id | sn              | name      | price | num  | alert_num | image                                                                                                                 | images                                                                                                                | weight | create_time         | update_time         | category_name | brand_name | spec  | sale_num | comment_num | status |
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
|  1 | 100000003145001 | 华为Meta1 | 87901 | 9961 |       100 | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp |     10 | 2019-05-01 00:00:00 | 2019-05-01 00:00:00 | 真皮包        | viney      | 白色1 |       39 |           0 | 1      |
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
1 row in set (39.96 sec)

查询耗时 39.96 秒，效率较低。

为了提升查询效率，我们为 sn 字段创建一个索引，SQL 语句如下：

mysql> CREATE INDEX idx_sku_sn ON tb_sku (sn);
Query OK, 0 rows affected (1 min 6.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引耗时 1 分 6.77 秒。

创建索引后，再次执行相同的 SQL 语句：

mysql> SELECT * FROM tb_sku WHERE sn = '100000003145001';
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
| id | sn              | name      | price | num  | alert_num | image                                                                                                                 | images                                                                                                                | weight | create_time         | update_time         | category_name | brand_name | spec  | sale_num | comment_num | status |
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
|  1 | 100000003145001 | 华为Meta1 | 87901 | 9961 |       100 | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp |     10 | 2019-05-01 00:00:00 | 2019-05-01 00:00:00 | 真皮包        | viney      | 白色1 |       39 |           0 | 1      |
+----+-----------------+-----------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+-------+----------+-------------+--------+
1 row in set (0.00 sec)

此时查询耗时仅 0.00 秒，性能得到了显著提升。

通过以上实验可以得出结论：为经常用于查询的字段建立索引，可以显著提升查询性能。在建立索引前后，查询耗时不在一个数量级，充分体现了索引的重要性。

2.6.2 最左前缀法则

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

为了演示最左前缀法则，我们以 tb_user 表为例。首先，查看 tb_user 表已创建的索引。代码如下：

mysql> SHOW INDEX FROM tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_email            |            1 | email       | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.00 sec)

查询结果展示了 tb_user 表的索引信息，包括索引名、索引类型、索引包含的列等。其中，idx_user_pro_age_sta 是一个联合索引，涉及三个字段，顺序分别为 profession、age 和 status。

最左前缀法则指的是，查询时必须存在联合索引最左边的列，也就是 profession 字段，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

下面通过多个案例来演示最左前缀法则的具体应用。

满足最左前缀法则（所有列连续）

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age = 31 AND status = '0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | const,const,const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• 结果： 使用了 idx_user_pro_age_sta 索引，key_len 为 54，索引全部生效。Extra 列显示 Using index condition，表示使用了索引下推。
• 分析： 该查询包含了联合索引的所有字段，且顺序与索引一致，满足最左前缀法则，索引全部生效。

满足最左前缀法则（包含最左列和中间列）

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age = 31;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 49      | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 结果： 使用了 idx_user_pro_age_sta 索引，key_len 为 49，索引部分生效。Extra 列显示 NULL。
• 分析： 该查询包含了联合索引的最左边的两个字段，满足最左前缀法则，索引部分生效。

满足最左前缀法则（仅最左列）

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | const |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 结果： 使用了 idx_user_pro_age_sta 索引，key_len 为 47，索引部分生效。Extra 列显示 NULL。
• 分析： 该查询只包含了联合索引的最左边的字段，满足最左前缀法则，索引部分生效。

通过以上三个案例，我们可以推断出 profession 字段索引长度为 47，age 字段索引长度为 2，status 字段索引长度为 5。 key_len 的长度代表了索引的使用长度。

违反最左前缀法则（缺失最左列）

EXPLAIN SELECT * FROM tb_user WHERE age = 31 AND status = '0';

• 结果： 没有使用任何索引，key 为 NULL，key_len 为 NULL。Extra 列显示 Using where。
• 分析： 该查询跳过了联合索引的最左边的字段 profession，不满足最左前缀法则，索引失效。

违反最左前缀法则（仅非左列）

mysql> EXPLAIN SELECT * FROM tb_user WHERE status = '0';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 结果： 没有使用任何索引，key 为 NULL，key_len 为 NULL。Extra 列显示 Using where。
• 分析： 该查询跳过了联合索引的最左边的字段 profession 和 age，不满足最左前缀法则，索引失效。

部分违反最左前缀法则（跳跃中间列）

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND status = '0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | const |    4 |    10.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• 结果： 使用了 idx_user_pro_age_sta 索引，key_len 为 47。Extra 列显示 Using index condition。
• 分析： 该查询包含了联合索引的最左边的字段 profession，但是跳过了 age 字段，虽然满足最左前缀法则的基本条件，但是 age 字段后面的 status 字段索引不会被使用，索引部分生效。

条件顺序不影响规则

mysql> EXPLAIN SELECT * FROM tb_user WHERE age = 31 AND status = '0' AND profession = '软件工程';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | const,const,const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• 结果： 使用了 idx_user_pro_age_sta 索引，key_len 为 54。Extra 列显示 Using index condition。
• 分析： 该查询完全满足最左前缀法则，索引长度为 54，联合索引生效。

结论：

• 规则要点：查询必须包含联合索引的最左列（第一列），且列顺序在索引中不能跳跃。
• 索引长度影响：索引生效长度（key_len）随使用列数变化，全生效则长度最大（54），跳跃后长度缩短。
• 实践建议：设计查询时，确保最左列存在并避免列跳跃；可通过调整条件顺序优化，但需注意索引定义而非 SQL 编写顺序决定规则适用性。

2.6.3 范围查询

在使用联合索引时，如果出现范围查询（如 >、<），则范围查询右侧的列索引可能会失效。

使用 `>` 范围查询

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age > 30 AND status = '0';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 49      | NULL |    2 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• 结果：使用了 idx_user_pro_age_sta 索引，key_len 为 49。Extra 列显示 Using index condition。
• 分析：虽然使用了联合索引，但是索引长度为 49，说明 age > 30 之后的 status 字段没有走索引。这是因为 > 范围查询导致了索引失效。

使用 `>=` 范围查询

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age >= 30 AND status = '0';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | NULL |    2 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• 结果：使用了 idx_user_pro_age_sta 索引，key_len 为 54。Extra 列显示 Using index condition。
• 分析：通过边界包含运算符 (≥) 避免了索引失效问题。索引长度为 54（联合索引的最大长度），证明所有列（profession, age, status）均有效应用索引检索，实现了高效扫描。

在业务允许的情况下，为了充分利用联合索引，应尽可能使用 >= 或 <= 这类范围查询，避免使用 > 或 <。 使用 >= 或 <= 可以让优化器更好地利用索引，提高查询效率。

2.6.4 索引失效情况

索引在特定操作下会失效，以下为五种典型场景的分析：

2.6.4.1 索引列运算

在索引列上执行运算操作会导致索引失效。

直接等值查询

mysql> EXPLAIN SELECT * FROM tb_user WHERE phone = '17799990015';
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_phone | idx_user_phone | 46      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 结果：使用了 idx_user_phone 索引，key_len 为 46，Extra 列为 NULL。
• 分析：直接对 phone 字段进行等值匹配，索引生效。

函数运算

mysql> EXPLAIN SELECT * FROM tb_user WHERE SUBSTRING(phone, 10, 2) = '15';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 结果：key 为 NULL，Extra 列显示 Using where，表示需要通过全表扫描过滤数据。
• 分析：对 phone 字段使用了 SUBSTRING 函数，导致索引失效。

2.6.4.2 字符串不加引号

字符串类型字段未加引号时，隐式类型转换导致索引失效。

联合索引场景

-- 有效（status 加引号）
EXPLAIN SELECT * FROM tb_user WHERE profession='软件工程' AND age=31 AND status='0'; 

-- 失效（status 未加引号）
EXPLAIN SELECT * FROM tb_user WHERE profession='软件工程' AND age=31 AND status=0;

• key_len 从 54 降为 49，status 字段索引失效

单列索引场景

-- 有效
EXPLAIN SELECT * FROM tb_user WHERE phone='17799990015'; 

-- 失效
EXPLAIN SELECT * FROM tb_user WHERE phone=17799990015;

• 数值类型触发隐式转换，索引失效

2.6.4.3 模糊查询

LIKE 模糊查询中，只有尾部模糊匹配（'xxx%'）才能有效利用索引；头部模糊匹配（'%xxx' 或 '%xxx%'）会导致索引失效。

尾部匹配

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession LIKE '软件%';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | NULL |    4 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

尾部模糊匹配，可以使用 idx_user_pro_age_sta 索引，key_len 为 47。

头部匹配

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession LIKE '%工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

头部模糊匹配，索引失效。

全模糊

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession LIKE '%%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

完全模糊匹配，索引失效。

2.6.4.4 OR 连接条件

使用 OR 连接的多个条件，只有当 OR 前后条件中的列都存在索引时，索引才可能生效。如果 OR 中任一条件的列没有索引，那么涉及的索引都不会被用到。

示例

mysql> EXPLAIN SELECT * FROM tb_user WHERE id = 10 OR age = 23;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |   24 |    13.75 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

id 列有主键索引，但 age 列没有索引，导致查询没有使用任何索引。

示例

mysql> EXPLAIN SELECT * FROM tb_user WHERE phone = '17799990017' OR age = 23;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | idx_user_phone | NULL | NULL    | NULL |   24 |    13.75 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

phone 列有索引 idx_user_phone，但 age 列没有索引，导致查询没有使用任何索引。

2.6.4.5 数据分布影响

MySQL 优化器会根据数据分布情况评估索引的效率。如果 MySQL 评估使用索引比全表扫描更慢，则会放弃使用索引。

返回大量数据

mysql> EXPLAIN SELECT * FROM tb_user WHERE phone >= '17799990005';
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | idx_user_phone | NULL | NULL    | NULL |   24 |    79.17 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

phone 大于等于 '17799990005' 的数据量很大，MySQL 会选择全表扫描。

返回少量数据

mysql> EXPLAIN SELECT * FROM tb_user WHERE phone >= '17799990015';
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_phone | idx_user_phone | 46      | NULL |    9 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

phone 大于等于 '17799990015' 的数据量较小，MySQL 会选择使用索引 idx_user_phone。

IS NULL 和 IS NOT NULL 是否使用索引也取决于数据分布。

少量 `NULL` 值

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession IS NULL;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

profession 列包含少量 NULL 值，此时会使用索引 idx_user_pro_age_sta。

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession IS NOT NULL;
+----+-------------+---------+------------+------+----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | idx_user_pro_age_sta | NULL | NULL    | NULL |   24 |   100.00 | Using where |
+----+-------------+---------+------------+------+----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

profession 列可能包含大量非 NULL 值，查询不会使用索引。

2.6.5 SQL 提示

SQL 提示（SQL Hint）是数据库优化中的一个重要手段，它允许用户在 SQL 语句中加入特定的指示，以影响查询优化器选择执行计划，从而达到优化查询性能的目的。

为了后续的演示，首先删除之前测试用的 idx_user_age 和 idx_email 索引。

DROP INDEX idx_user_age ON tb_user;
DROP INDEX idx_email ON tb_user;

接下来通过案例演示 MySQL 自动选择索引的行为以及 SQL 提示的作用。

查询 `profession` 字段

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | const |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

优化器自动选择联合索引 idx_user_pro_age_sta，说明 MySQL 默认根据索引统计和查询条件评估最优路径。

为了对比，创建一个针对 profession 字段的单列索引：

CREATE INDEX idx_user_pro ON tb_user(profession);

再次查询 `profession = '软件工程'`

mysql> EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程';
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                     | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta,idx_user_pro | idx_user_pro_age_sta | 47      | const |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

优化器在 possible_keys 中列出 idx_user_pro_age_sta 和 idx_user_pro 两个候选索引，但最终选择 idx_user_pro_age_sta。这表明 MySQL 的内部评估机制忽略单列索引，优先使用联合索引以覆盖更多查询条件。

在某些情况下，MySQL 自动选择的索引可能并非最佳选择，或者用户希望强制使用特定的索引。此时，SQL 提示就显得尤为重要，它允许用户干预 MySQL 的索引选择过程。MySQL 提供了以下几种 SQL 提示：

1. USE INDEX：该提示是向 MySQL 建议使用指定的索引来完成查询。请注意，这仅仅是建议，MySQL 内部仍然会进行评估，最终可能不会采纳该建议。
2. IGNORE INDEX：该提示指示 MySQL 在查询时忽略一个或多个指定的索引。这意味着 MySQL 不会考虑这些索引，而会在剩余可用的索引中进行选择。
3. FORCE INDEX：该提示强制 MySQL 使用指定的索引。与 USE INDEX 不同，FORCE INDEX 具有更高的优先级，MySQL 会尽力使用该索引，即使优化器认为存在更优的选择。然而，如果强制使用的索引无法覆盖查询所需的所有列或条件，MySQL 仍可能拒绝使用该索引。

下面通过具体的 EXPLAIN 语句来演示这三种 SQL 提示的效果。

`USE INDEX` 示例

mysql> EXPLAIN SELECT * FROM tb_user USE INDEX(idx_user_pro) WHERE profession = '软件工程';
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro  | idx_user_pro | 47      | const |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

执行计划显示，该查询使用了 idx_user_pro 索引。这表明在本例中，MySQL 采纳了 USE INDEX 的建议。

`IGNORE INDEX` 示例

mysql> EXPLAIN SELECT * FROM tb_user IGNORE INDEX(idx_user_pro) WHERE profession = '软件工程';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | const |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

执行计划显示，由于 idx_user_pro 被忽略，该查询转而使用了 idx_user_pro_age_sta 索引。

`FORCE INDEX` 示例

mysql> EXPLAIN SELECT * FROM tb_user FORCE INDEX(idx_user_pro_age_sta) WHERE profession = '软件工程';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

执行计划显示，该查询被强制使用了 idx_user_pro_age_sta 索引。这证明了 FORCE INDEX 能够有效地覆盖 MySQL 优化器的自动选择行为。

SQL 提示通过简单语法（如 USE INDEX, IGNORE INDEX, FORCE INDEX）让开发者干预索引选择，增强查询的可控性。但需注意：

• 优先依赖自动优化器，仅在性能瓶颈或测试场景中使用提示。
• 强制提示可能引发性能风险，需结合执行计划评估。 在实际应用中，此技术常用于优化复杂查询或协调多索引竞争。

2.6.6 覆盖索引

覆盖索引是指查询操作中使用了索引，且所需返回的所有列均能在该索引结构中直接获取到（无需额外扫描数据行）。使用覆盖索引可优化查询性能，核心原则是减少 SELECT * 语句，避免回表查询。

以下四组 SQL 通过 EXPLAIN 分析执行计划，重点关注 Extra 列的差异：

SQL 语句	Extra 列
EXPLAIN SELECT id, profession FROM tb_user WHERE profession = '软件工程' AND age = 31 AND status = '0';	Using where; Using index
EXPLAIN SELECT id, profession, age, status FROM tb_user WHERE profession = '软件工程' AND age = 31 AND status = '0';	Using where; Using index
EXPLAIN SELECT id, profession, age, status, name FROM tb_user WHERE profession = '软件工程' AND age = 31 AND status = '0';	Using index condition
EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age = 31 AND status = '0';	Using index condition

对于 Extra 列的字段，其含义如下：

Extra	含义
Using where; Using index	索引覆盖查询，所有返回列在索引中已存在，无需回表操作
Using index condition	使用了索引，但需回表查询数据（返回列未完全包含在索引中）

造成 Extra 列的差异在于，idx_user_pro_age_sta 是一个二级索引，其叶子节点存储了索引字段 (profession, age, status) 和主键 id 的值。

• 当查询返回的数据仅包含 id、profession、age 和 status 时，可以直接通过该二级索引获取所需数据，无需回表查询。
• 如果查询返回的数据超出了上述范围，例如包含了 name 字段，则需要通过二级索引获取主键 id，再通过主键 id 扫描聚集索引，获取 name 字段的值，这个过程就是回表查询。

如何优化 SQL 语句

问题：优化以下 SQL（表字段：id, username, password, status）：

SELECT id, username, password FROM tb_user WHERE username = 'itcast';

最优方案：

• 创建联合索引覆盖查询列：

  CREATE INDEX idx_user_name_pass ON tb_user(username, password);

• 理由：
  • 索引 idx_user_name_pass 包含 username（查询条件）和 password（返回列）。
  • 返回列 id、username、password 均在索引中：
    • id 自动附加于二级索引叶子节点。
    • 避免回表，提升查询效率。

2.6.7 前缀索引

当字段类型为字符串（VARCHAR、TEXT、LONGTEXT 等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘 I/O，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

前缀索引使用方式如下：

CREATE INDEX idx_xxxx ON table_name(column(n));

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

mysql> SELECT COUNT(DISTINCT email) / COUNT(*) FROM tb_user;
+----------------------------------+
| COUNT(DISTINCT email) / COUNT(*) |
+----------------------------------+
|                           1.0000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(DISTINCT SUBSTRING(email, 1, 5)) / COUNT(*) FROM tb_user;
+---------------------------------------------------+
| COUNT(DISTINCT SUBSTRING(email, 1, 5)) / COUNT(*) |
+---------------------------------------------------+
|                                            0.9583 |
+---------------------------------------------------+
1 row in set (0.00 sec)

长度为 5 的前缀索引选择性以达到 0.9583。因此，可为 tb_user 表的 email 字段，建立长度为 5 的前缀索引：

CREATE INDEX idx_email_5 ON tb_user(email(5));

使用前缀索引查询时，流程如下：

SELECT * FROM tb_user WHERE email = 'lvbu666@163.com';

1. 查找前缀索引： 首先，MySQL 会使用 email 字段的前 5 个字符（即 'lvbu6'）在辅助索引（前缀索引）中查找匹配的索引项。
2. 定位数据行： 辅助索引的叶子节点存储了对应数据行的主键值（在这里是 id）。根据前缀 'lvbu6' 找到匹配的 id 值为 1。
3. 回表查询： 使用主键值 1，MySQL 会通过聚集索引（id 索引）找到完整的记录，即 id 为 1 的那一行数据。
4. 完整匹配： 因为前缀索引只使用了 email 的前 5 个字符，为了确保查询结果的准确性，MySQL 需要从存储引擎中读取完整的 email 字段，然后与查询条件 email = 'lvbu666@163.com' 进行完整匹配。
5. 返回结果： 如果完整匹配成功，则将该记录作为查询结果返回。

前缀索引通过牺牲一定的精确性来换取索引大小的减小和查询速度的提升。 在查询过程中，MySQL 首先利用前缀索引快速定位到可能匹配的数据行，然后通过回表查询和完整匹配来确保结果的准确性。 因此，选择合适的前缀长度对于平衡索引大小和查询性能至关重要。

2.6.8 单列索引与联合索引

索引可以分为单列索引和联合索引两种类型：

• 单列索引： 仅包含单个列的索引。
• 联合索引： 包含多个列的索引。

在 WHERE 子句中使用 AND 连接多个字段进行查询时，即使这些字段上都存在单列索引，MySQL 优化器通常只会选择其中一个索引来使用。 这意味着，未使用索引的字段需要通过回表查询来获取数据。

例如， tb_user 表在 phone 和 name 字段上都有单列索引，执行以下查询：

mysql> EXPLAIN SELECT id, phone, name FROM tb_user WHERE phone = '17799990010' AND name = '韩信';
+----+-------------+---------+------------+-------+------------------------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys                | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+------------------------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_phone,idx_user_name | idx_user_phone | 46      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+------------------------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN 的结果会显示 MySQL 选择了其中一个索引，并且需要回表查询。

为了优化多条件查询，可以创建包含多个查询字段的联合索引。 联合索引会将多个字段的值组合在一起构建索引，从而避免回表查询。

例如，可以为 tb_user 表的 phone 和 name 字段创建一个联合索引：

CREATE UNIQUE INDEX idx_user_phone_name ON tb_user(phone, name);

创建联合索引后，再次执行相同的查询，并强制使用该联合索引：

mysql> EXPLAIN SELECT id, phone, name FROM tb_user USE INDEX (idx_user_phone_name) WHERE phone = '17799990010' AND name = '韩信';
+----+-------------+---------+------------+-------+---------------------+---------------------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys       | key                 | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------------+---------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_phone_name | idx_user_phone_name | 248     | const,const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------------+---------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN 的结果会显示 MySQL 使用了联合索引 idx_user_phone_name，并且无需回表查询，因为该联合索引包含了 phone 和 name 的信息，并且叶子节点下挂的是对应的主键 id。

在业务场景中，如果存在多个查询条件，建议为查询字段建立联合索引，而非多个单列索引，以提高查询效率，避免回表查询。

2.7 索引设计原则

1. 数据量与查询频率：

   • 原则： 针对数据量较大且查询频繁的表建立索引。
   • 解释： 索引能够显著提升查询效率，但会增加存储空间和维护成本。因此，只对真正需要优化的表建立索引。

2. 查询条件、排序与分组字段：

   • 原则： 针对常作为查询条件 (WHERE)、排序 (ORDER BY)、分组 (GROUP BY) 操作的字段建立索引。
   • 解释： 这些操作是查询中最常见的操作，对这些字段建立索引可以直接定位到目标数据，避免全表扫描。

3. 区分度：

   • 原则： 尽量选择区分度高的列作为索引，尽量建立唯一索引。
   • 解释： 区分度越高，索引的效率越高。例如，性别字段的区分度较低，不适合建立索引。唯一索引的区分度最高，查询效率也最高。

4. 前缀索引：

   • 原则： 如果是字符串类型的字段，且字段长度较长，可以针对字段的特点建立前缀索引。
   • 解释： 对于长字符串字段，可以只对字段的前几个字符建立索引，减小索引大小，提高索引效率。需要注意的是，前缀索引可能会降低区分度，需要根据实际情况选择合适的前缀长度。

5. 联合索引：

   • 原则： 尽量使用联合索引，减少单列索引。
   • 解释：
     • 覆盖索引： 查询时，联合索引很多时候可以覆盖索引，避免回表，提高查询效率。
     • 节省空间： 联合索引可以减少索引的数量，节省存储空间。
   • 注意： 联合索引的字段顺序需要根据实际查询情况进行调整，遵循最左前缀原则。

6. 索引数量控制：

   • 原则： 要控制索引的数量，索引并非多多益善。
   • 解释： 索引越多，维护索引结构的代价越大，会影响增删改的效率。因此，需要根据实际情况权衡索引的数量。

7. NULL 值处理：

   • 原则： 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。
   • 解释： 当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。允许 NULL 值的列会使索引、索引统计和值的比较复杂化。NULL 值也可能占用额外的存储空间。