[TOC] #### 1. 联合索引 --- MySQL 联合索引（也称复合索引）是在表的多个列上共同创建的一个索引，能极大的优化多条件查询的性能 它并非多个单列索引的简单叠加，而是一个将多列值组合在一起，并按照特定顺序进行排序和存储的 B+ 树结构 假设创建了一个联合索引 `(A, B, C)`，联合索引设计速查表： | 查询语句 | 索引使用情况 | 原因分析 | | ------------ | ------------ | ------------ | | A = 1 AND B = 2 AND C = 3 | 全用 | 完美匹配，效率最高 | | A = 1 AND B = 2 | A, B 有效 | 符合最左前缀原则 | | A = 1 | A 有效 | 符合最左前缀原则 | | B = 2 AND C = 3 | 失效 | 缺少最左列 A，索引无法使用 | | A = 1 AND C = 3 | 仅 A 有效 | 跳过了中间列 B, C 无法使用索引 | | A = 1 AND B > 10 AND C = 3 | A,B 有效 | B 是范围查询，导致 C 失效（范围列右侧失效）| | A = 1 ORDER BY B | A,B 有效 | 索引可用于排序优化 | #### 2. 最左侧原则 --- 最左侧原则：在使用联合索引时，查询条件必须从索引的最左边一列开始匹配，并且匹配过程不能跳过中间的列 这是理解和使用联合索引的基石，联合索引（a,b,c）的底层 B+ 树是按照（a,b,c）的顺序进行排序的，所以必须遵循该原则 接下来，我们通过一个案例来理解最左侧原则，运行以下命令创建一个测试表： ```sql CREATE TABLE `users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(30) DEFAULT NULL COMMENT '姓名', `age` tinyint(4) DEFAULT NULL COMMENT '年龄', `gender` char(1) DEFAULT NULL COMMENT '性别', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'; INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('liang', 18, '男'); INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('zhang', 20, '女'); INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('chen', 25, '男'); INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('sun', 30, '女'); ``` 运行以下命令分析 SQL 语句，可以发现是全表扫描数据 ```sql explain select * from users where name = 'liang' and age = 18 and gender = '男'; ``` 我们准备使用联合索引来优化这个 SQL，创建联合索引语法格式： + index_name：索引名称，为索引指定的名称 + table_name：指定表名，说明是给哪张表创建索引 ```sql create index index_name on table_name (column1, column2, ...); ``` 所以，我们可以运行以下命令创建联合索引： ```sql -- 相当于构建了一个按照 name > age > gender 顺序排序的数据结构 create index idx_name_age_gender on users (name, age, gender); ``` 联合索引创建之后，那么它在什么时候有效呢 ？需要认真思考一下这个问题 有效查询： + where name = 1（匹配最左列，有效） + where name = 1 and age = 2（匹配前两列，有效） + where name = 1 and age = 2 and gender = 3（完全匹配，有效） 无效或部分失效查询： + where age = 2（跳过最左列 name，完全失效） + where name = 1 and gender = 2（跳过中间列 age，仅 name 生效，gender 失效） ```sql -- 联合索引有效 explain select * from users where name = 'liang'; explain select * from users where name = 'liang' and age = 18; explain select * from users where name = 'liang' and age = 18 and gender = '男'; -- 联合索引无效 explain select * from users where age = 18; -- 仅 name 生效，gender 失效 explain select * from users where name = 'liang' and gender = '男'; ``` 底层原理：为什么要遵守这个原则 ？理解这个原则的关键在于理解 B+ 树的存储结构 联合索引在底层并不简单的三个字段并列，而是层级排序的。（可以把它想象为一本电话簿或字典） + 先 a 排序：就像电话簿先按 “姓氏” 排序 + a 相同，再按 b 排序：姓氏相同的人，再按名字排序 + a 和 b 都相同，再按 c 排序 为什么跳过 `a` 就不行 ？ + 因为 `b` 列的数据在全表中并不是有序的，它只在 `a` 相同的小组內是有序的 + 如果直接查 `b`，数据库就像在一本乱序的书中找字，只能全表扫描，此时联合索引根本就不会生效 为什么跳过 `b` 查 `c` 就不行 ？ + 同理，`c` 只有在 `a` 和 `b` 都确定的情况下才是有序的 + 如果只给了 `a` 和 `c`，数据库可以使用 `a` 快速定位到一大块区域，但在这块区域里，`c` 是乱序的 #### 3. 范围查询打断 --- 前面我们说的都是等值查询，但如果是范围查询和模糊查询呢 ？这两个场景容易踩坑，也是面试和实战中的高频考点 范围查询打断（范围查询后面的列索引失效）： + 在联合索引中，一旦某一列使用了范围查询（>、<、between）,该列右侧的所有列都无法再利用索引进行快速查找 为什么会这样 ？（底层原理） 这是因为联合索引的 B+ 树是按照从左到右的顺序构建的： + a 列：全局有序 + b 列：只有在 a 相同的情况下，才是有序的 + c 列：只有在 a 和 b 都相同的情况下，才是有序的 当你使用 `a = 1 and b > 10` 时： 数据库找到了 `a = 1` 的数据块，然后在这个块里找 `b > 10` 的数据。因为 `b` 是范围查找，它匹配到了多个 `b` 的值 在这些不同的 `b` 值下，`c` 列的数据是杂乱无章的（因为 `c` 只有在 `b` 固定时才有序），既然 `c` 是乱序的，索引树就无法利用二分查找来定位 `c`，只能遍历扫描 实战案例演示，假设联合索引为 `idx(a, b, c)`： | 查询语句 | 索引使用情况 | 解释说明 | | ------------ | ------------ | ------------ | | where a = 1 and b > 10 and c > 2 | a,b 有效，c 失效 | b 使用了范围，导致后面的 c 无法使用索引定位 | | where a = 1 and b = 2 and c > 5 | a,b,c 全有效 | 范围查询在最后一列，前面都是等值，所以都能用到 | | where a > 1 and b = 2 and c = 3 | a 有效 b,c 失效 | a 使用了范围，直接导致后面的 b,c 失效 | | where a = 1 and c > 5 | a 生效 c 失效 | 虽然 c 使用了范围，因为跳过了中间 b，所以 c 失效 | 注：通常我们将等值查询的列放在前面，范围查询的列放在最后，这样能最大化利用索引 #### 4. 模糊查询（like） --- 核心规则：模糊查询是否走索引，完全取决于通配符 `%` 的位置 假设索引为 `idx(name)` 或联合索引的最左列： | 模糊查询类型 | SQL 示例 | 索引情况 | 原理分析 | | ------------ | ------------ | ------------ | ------------ | | 前缀匹配 | `like "abc%"` | 生效 | 索引树是按照字符顺序排的，<br>`abc` 开头的字符串在树中是连续存储的，<br>数据库可以快速定位 `abc` 的起始位置并扫描 | | 后缀匹配 | `like "%abc"` | 失效 | 以 `abc` 结尾的字符串在索引树中是分散的，<br>无法通过索引定位，只能全表扫描 | | 包含匹配 | `like "%abc%"` | 失效 | 同上，数据在索引中无序，无法利用索引 | 联合索引中的模糊查询行为遵循 “最左前缀原则” 和 “范围打断原则” 的混合逻辑 场景 A：前缀匹配（视为等值） + `where a = 1 and b like '梁%' and c = 3` + 结果：`a`，`b`，`c` 全部生效 + 原因：`like '梁%'` 在索引中会被视为一个确定的范围起点，它不会打断后续列的有序性（类似于等值查询） 场景 B：后缀/包含匹配（视为全表扫描） + `where a = 1 and b like '%梁' and c = 3` + 结果：只有 `a` 生效，`b` 和 `c` 失效 + 原因：`b` 的查询无法利用索引，相当于在 `a = 1` 的结果集中做全表扫描 场景 C：前缀匹配作为范围（打断后续） + 虽然 `like '梁%'` 能走索引，但在某些严格定义下，它被视作一种范围 + 不过，在 MySQL 的联合索引中，`like '梁%'` 不会打断后续列（只要它是前缀匹配），MySQL 优化器做了处理 + 特例：如果是 `like 'ab%c'` （中间有通配符），则视为范围/失效，后续列无法使用索引 为了方便记忆，可以参考这张表： | 场景 | 关键特征 | 索引是否生效 | 建议 | | ------------ | ------------ | ------------ | ------------ | | 范围查询 | `>`、`<`、`between` | 当前列生效，后续列失效 | 将范围查询的列尽量放在索引的最后一列 | | 前缀匹配 | `LIKE 'abc%'` | 生效 | 尽量使用前缀匹配 | | 后缀/包含匹配 | `LIKE '%abc'` | 失效 | 如果必须后缀/包含匹配，考虑用全文索引或搜索引擎（Elasticsearch） | #### 5. 实际项目示例 --- 假设正在开发一个电商后台，有一张订单表 `orders`，数据量很大（百万级） ```sql CREATE TABLE `orders` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `order_no` varchar(64) NOT NULL COMMENT '订单编号', `user_id` bigint(20) NOT NULL COMMENT '用户ID', `merchant_id` bigint(20) NOT NULL COMMENT '商家ID', `status` varchar(20) NOT NULL COMMENT '订单状态：UNPAID, PAID, SHIPPED, FINISHED', `amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_order_no` (`order_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='电商订单表'; ``` 创建一个存储过程，用于生成测试数据 ```sql DELIMITER $$ CREATE PROCEDURE batch_insert_orders() BEGIN DECLARE i INT DEFAULT 1; DECLARE v_user_id BIGINT; DECLARE v_merchant_id BIGINT; DECLARE v_status VARCHAR(20); -- 开启事务，提高插入速度 START TRANSACTION; WHILE i <= 10000 DO -- 随机生成用户ID (800-999) SET v_user_id = FLOOR(800 + RAND() * 200); -- 随机生成商家ID (1001-1005) SET v_merchant_id = FLOOR(1001 + RAND() * 5); -- 随机状态 SET v_status = ELT(FLOOR(1 + RAND() * 4), 'UNPAID', 'PAID', 'SHIPPED', 'FINISHED'); INSERT INTO `orders` (`order_no`, `user_id`, `merchant_id`, `status`, `amount`, `create_time`) VALUES ( CONCAT('ORD_', DATE_FORMAT(NOW(), '%Y%m%d'), '_', LPAD(i, 6, '0')), v_user_id, v_merchant_id, v_status, ROUND(RAND() * 1000, 2), DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 30) DAY) -- 随机过去30天内的时间 ); SET i = i + 1; END WHILE; COMMIT; END$$ DELIMITER ; -- 调用存储过程执行插入 CALL batch_insert_orders(); ``` ##### 场景一：后台订单列表查询（最典型） 业务需求：运营人员经常在后台查询 `某个商家` 在 `特定日期范围内` 的 `待发货` 订单 ```sql -- 当前我们还没有加索引，现在查询是全表扫描 SELECT * FROM orders WHERE merchant_id = 1001 AND status = 'UNPAID' AND create_time > '2023-10-01'; ``` 索引设计策略：我们需要建立联合索引 `(merchant_id, status, create_time)` + merchant_id（等值查询）：这是最左列，用来先锁定是哪个商家的数据，过滤性最强 + status（等值查询）：在商家的数据里，再筛选出特定状态的订单 + create_time（范围查询）：最后处理时间范围。根据“最左前缀原则”和“范围截断规则”，必须放在联合索引的最后面 ```sql -- 创建联合索引之后，再执行上面命令分析 SQL 语句可以看到联合索引已被使用 create index idx_merchant_status_time on orders (merchant_id, status, create_time); ``` ##### 场景二：用户订单列表查询（排序优化） 业务需求：C 端用户查看 “我的订单”，通常按时间倒序排列 ```sql SELECT * FROM orders WHERE user_id = 888 ORDER BY create_time DESC LIMIT 20; ``` 索引设计策略：建立联合索引 `(user_id, create_time)` + 如果不把 `create_time` 加到索引里，会先查出该用户的所有订单，然后在内存中进行排序（Filesort），这在数据量比较大时非常慢 + 建立了联合索引后，索引树本身就是按照 `user_id` 分组，组内按照 `create_time` 排序的。数据库可以直接利用索引的有序性，从后往前读取20条数据，效率极高 ```sql create index idx_user_time on orders (user_id, create_time); ``` ##### 场景三：覆盖索引（无需回表，极致性能） 业务需求：在订单列表页，只需要展示 “订单号” 和 “当前状态”，不需要展示收获地址等大字段的详情 ```sql SELECT order_no, status FROM orders WHERE user_id = 888 AND create_time > '2023-01-01'; ``` 索引设计策略：建立联合索引 `(user_id, create_time, order_no, status)` + 这个索引包含了 `where` 条件用到的列，也包含了 `select` 查询的列 + MySQL 引擎发现索引树上已经有所需的所有数据，完全不需要回表（不需要去查主键索引拿数据），直接在索引树上遍历返回结果。这是查询速度的天花板 ```sql create index idx_uid_time_no_status on orders (user_id, create_time, order_no, status); ``` #### 6. 覆盖索引的理解 --- 在场景三中使用了覆盖索引，你可能不太理解什么是覆盖索引，我们来研究一下这个问题 首先，需要先了解一下数据库中的原理，在 MySQL 的 InnoDB 引擎里： + 主键索引（聚簇索引）：它的叶子节点存储了完整的行数据 + 普通索引（二级索引）：它的叶子节点只存储了索引列的值 + 主键的值 执行查询时，如果使用二级索引，通常会发生两件事： + 查二级索引：先在二级索引树上找到符合条件的记录，拿到主键 ID + 回表：拿着这个主键 ID，再去聚簇索引（主键索引）树上查找完整的行数据 “回表” 是一次额外的、昂贵的 `I/O` 操作，而覆盖索引的精髓就在于： + SQL 语句中 SELECT 的所有字段，恰好都包含在使用的这个二级索引中，无需回表拿着主键 ID 查询完整行数据 结合 `orders` 表来理解，回到场景三，建立的索引是：`(user_id, create_time, order_no, status)` ```sql SELECT order_no, status FROM orders WHERE user_id = 888 AND create_time > '2023-01-01'; ``` 为什么这个索引是覆盖索引 ？先来拆解一下 + where 条件字段：`user_id` 和 `create_time`。这两个字段是索引的最左两列，数据库会利用它们快速定位数据 + select 查询字段：`order_no` 和 `status`。这两个字段也包含我们建立的联合索引中 执行过程（使用覆盖索引）： + 数据库引擎在索引树上找到 `user_id = 888 AND create_time > '2023-01-01'` 的所有节点 + 它发现这些索引节点上已经直接存好了 `order_no` 和 `status` 的值 + 任务完成，直接把 `order_no` 和 `status` 返回。整个过程没有去查主键索引，也就没有发生回表 对比一下，如果不用覆盖索引会怎么样 ？ 假设我们的索引只是 `(user_id, create_time)`，那么执行过程需要回表： + 数据库引擎在索引树上找到 `user_id = 888 AND create_time > '2023-01-01'` 的所有节点 + 但是这个索引树上只有 `user_id`、`create_time` 和 `id`，它没有 `order_no` 和 `status` + 于是，数据库必须拿着找到的 `id`，回到主键索引树上去查找完整的行数据，才能拿到 `order_no` 和 `status` + 这个 “回到主键索引树查找” 的过程，就是 “回表” 总结：覆盖索引不是一种特殊的索引类型，而是一种高效的查询状态 + 核心思想：你需要的，索引里全都有 + 最大好处：避免回表，极大的减少了磁盘的 `I/O`，让查询速度飞快 + 如何判断：当你用 `explain` 分析 SQL 时，如果 `extra` 列显示为 `Using index`，那就说明用上了覆盖索引