[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`,那就说明用上了覆盖索引