网站导航
> 技术文档 > Mysql 索引知识点大全【聚簇索引、二级索引、回表查询、索引下推、覆盖索引、联合索引】

Mysql 索引知识点大全【聚簇索引、二级索引、回表查询、索引下推、覆盖索引、联合索引】

网友: 技术文档 2025-09-03 10:28:04

MySQL 聚簇索引、二级索引、回表查询与索引下推详解

1. 聚簇索引 (Clustered Index)

基本概念

  • 定义：索引的叶子节点存储的是完整的数据行（数据与索引存储在一起）
  • 特性：
    • 每个InnoDB表有且只有一个聚簇索引
    • 物理存储顺序与索引顺序一致
    • 主键自动成为聚簇索引

创建规则

  1. 如果表定义了PRIMARY KEY，则使用主键作为聚簇索引
  2. 如果没有主键，选择第一个UNIQUE NOT NULL的列作为聚簇索引
  3. 如果以上都没有，InnoDB会创建一个隐藏的rowid作为聚簇索引

优势

  • 快速访问：通过索引可直接获取数据，无需二次查找
  • 范围查询高效：相邻数据物理存储在一起
  • 覆盖索引查询高效

2. 二级索引 (Secondary Index/Non-clustered Index)

基本概念

  • 定义：索引的叶子节点存储的是主键值（非聚簇索引）
  • 特性：
    • 每个表可以有多个二级索引
    • 叶子节点不包含完整数据行
    • 需要回表操作获取完整数据

结构示例

二级索引结构：索引列值 -> 主键值

常见类型

  • 普通索引
  • 唯一索引
  • 复合索引
  • 全文索引

3. 回表查询 (Bookmark Lookup)

概念

  • 当查询的列不全部包含在二级索引中时，需要通过二级索引找到主键值，再通过主键值到聚簇索引中查找完整记录的过程

执行流程

  1. 在二级索引中查找符合条件的记录，获取主键值
  2. 使用主键值到聚簇索引中查找完整数据行
  3. 返回查询结果

示例

-- 表结构CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(20), age INT, INDEX idx_age (age));-- 回表查询示例SELECT * FROM user WHERE age = 25;-- 1. 使用idx_age索引找到age=25的记录，获取主键id-- 2. 使用主键id到聚簇索引中查找完整记录

性能影响

  • 需要两次索引查找，性能较低
  • 可通过覆盖索引优化（查询列都包含在索引中）

4. 索引下推 (Index Condition Pushdown, ICP)

基本概念

  • **MySQL 5.6+**引入的优化技术
  • 原理：将WHERE条件中索引列的过滤条件下推到存储引擎层执行
  • 目的：减少回表操作次数，提高查询性能

工作流程（有ICP vs 无ICP）

无ICP时：

  1. 存储引擎根据索引查找记录
  2. 返回所有匹配索引条件的记录给Server层
  3. Server层根据其他条件过滤

有ICP时：

  1. 存储引擎根据索引查找记录
  2. 在存储引擎层就应用WHERE条件中的索引列过滤
  3. 只返回满足所有条件的记录给Server层

示例

-- 表结构CREATE TABLE employee ( id INT PRIMARY KEY, name VARCHAR(50), dept_id INT, salary INT, INDEX idx_dept_salary (dept_id, salary));-- 查询SELECT * FROM employee WHERE dept_id = 3 AND salary > 5000;-- 无ICP：先找到所有dept_id=3的记录，再在Server层过滤salary>5000-- 有ICP：在存储引擎层就过滤dept_id=3 AND salary>5000，减少回表次数

使用条件

  • 只能用于二级索引（非聚簇索引）
  • 适用于range、ref、eq_ref和ref_or_null访问方法
  • WHERE条件中的列必须都是索引列
  • 对于InnoDB表，ICP只适用于二级索引

索引下推的使用条件

ICP目标是减少全行记录读取，从而减少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表数据，也就不存在下推一说。

只能用于range、 ref、 eq_ref、ref_or_null访问方法；

where 条件中是用 and 而非 or 的时候。

ICP适用于分区表。

ICP不支持基于虚拟列上建立的索引，比如说函数索引

ICP不支持引用子查询作为条件。

ICP不支持存储函数作为条件，因为存储引擎无法调用存储函数。

查看ICP状态

-- 查看ICP是否开启SHOW VARIABLES LIKE \'optimizer_switch\';-- 输出中包含index_condition_pushdown=on-- 开启/关闭ICPSET optimizer_switch = \'index_condition_pushdown=on|off\';

5. 性能优化建议

  1. 合理设计索引：
    • 高频查询条件列建立索引
    • 考虑复合索引的顺序
  2. 减少回表：
    • 使用覆盖索引（查询列都包含在索引中）
    • 避免SELECT *，只查询需要的列
  3. 利用索引下推：
    • 确保WHERE条件中的索引列能够被下推
    • 复合索引中，将过滤性高的列放在前面
  4. 监控索引使用：
EXsql SELECT ...; -- 查看执行计划SHOW INDEX FROM table_name; -- 查看表索引

MySQL 覆盖索引详解

1. 覆盖索引基本概念

覆盖索引（Covering Index）是指一个查询的所有字段都包含在索引中，查询只需要通过索引就能获取所需数据，而无需回表查询数据行。

核心特点：

  • 查询的列都包含在索引中
  • 不需要访问数据行（避免回表操作）
  • 显著提高查询性能

2. 覆盖索引工作原理

普通索引查询流程：

  1. 通过索引树查找符合条件的索引记录
  2. 获取主键值
  3. 通过主键值回表查询完整数据行（回表操作）
  4. 返回结果

覆盖索引查询流程：

  1. 通过索引树查找符合条件的索引记录
  2. 直接从索引中获取所需列的值（无需回表）
  3. 返回结果

3. 覆盖索引的优势

  1. 减少I/O操作：避免访问数据行，减少磁盘I/O
  2. 提高查询速度：省去了回表操作的时间
  3. 减轻服务器负担：减少CPU和内存消耗
  4. 特别适合统计查询：如COUNT()、SUM()等聚合操作

4. 如何创建和使用覆盖索引

示例表结构：

CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50), age INT, city VARCHAR(50), INDEX idx_username_age (username, age));

覆盖索引查询示例：

-- 覆盖索引查询（所有查询列都在索引中）SELECT username, age FROM users WHERE username = \'john\';-- 非覆盖索引查询（需要回表获取city字段）SELECT username, age, city FROM users WHERE username = \'john\';

创建覆盖索引：

-- 为常用查询创建覆盖索引ALTER TABLE users ADD INDEX idx_cover_name_age_city (username, age, city);

5. 判断查询是否使用覆盖索引

使用EXsql命令查看执行计划，当Extra列显示Using index时，表示使用了覆盖索引：

EXsql SELECT username, age FROM users WHERE username = \'john\';

输出示例：

+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+| 1 | SIMPLE | users | NULL | ref | idx_username_age | idx_username_age | 203 | const | 1 | 100.00 | Using index |+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+

6. 覆盖索引的最佳实践

  1. 选择合适的列顺序：
    • 将高频查询条件列放在索引前面
    • 将需要返回的列放在索引后面
  2. 避免过度索引：
    • 只为真正需要的查询创建覆盖索引
    • 过多的索引会影响写入性能
  3. 考虑索引大小：
    • 大字段（如TEXT）不适合包含在覆盖索引中
    • 索引列总长度不宜过长
  4. 复合索引设计：
-- 好的设计：查询条件和返回列都包含在索引中CREATE INDEX idx_cover ON orders (customer_id, status, order_date, amount);-- 查询示例（使用覆盖索引）SELECT status, order_date, amount FROM orders WHERE customer_id = 100 AND status = \'completed\';

7. 覆盖索引的局限性

  1. 写入开销：维护更多列的索引会增加INSERT/UPDATE/DELETE的开销
  2. 存储空间：覆盖索引通常比普通索引占用更多空间
  3. 不适合所有查询：仅对特定查询模式有效
  4. 无法覆盖所有场景：当查询需要大量列时，创建覆盖索引可能不实际

8. 实际应用案例

案例1：用户信息查询

-- 常用查询：根据用户名获取用户年龄和城市SELECT age, city FROM users WHERE username = ?;-- 最佳索引设计CREATE INDEX idx_user_cover ON users (username, age, city);

案例2：订单统计

-- 常用查询：统计某客户已完成订单的总金额SELECT SUM(amount) FROM orders WHERE customer_id = ? AND status = \'completed\';-- 最佳索引设计CREATE INDEX idx_order_cover ON orders (customer_id, status, amount);

通过合理设计覆盖索引，可以显著提高这些常见查询的性能。

MySQL联合索引底层结构与最左前缀匹配原理

一、联合索引的底层结构

1. B+树结构

联合索引在InnoDB引擎中采用B+树结构存储，与单列索引结构类似，但有重要区别：

  • 索引键：由多个列的值按定义顺序拼接组成复合键
  • 排序规则：先按第一列排序，第一列相同按第二列排序，以此类推
  • 叶子节点：存储完整的索引键值+主键值（非聚簇索引）

2. 物理存储示例

对于索引INDEX idx_a_b_c (a, b, c)，索引键的存储形式为：

(a1,b1,c1) -> 主键1(a1,b1,c2) -> 主键2(a1,b2,c1) -> 主键3(a2,b1,c1) -> 主键4...

3. 与单列索引的区别

特性 单列索引 联合索引 索引键 单列值 多列组合值 排序方式 单列排序 多级排序 存储开销 较小 较大（存储更多列） 查询效率 适合单条件查询 适合多条件组合查询

二、最左前缀匹配原则

1. 基本概念

最左前缀匹配原则指联合索引按照从左到右的顺序使用，查询条件必须包含索引的第一列（最左列）才能使用索引。

2. 匹配规则详解

对于联合索引(a,b,c)：

✅ 能使用索引的情况：
  • WHERE a = 1
  • WHERE a = 1 AND b = 2
  • WHERE a = 1 AND b = 2 AND c = 3
  • WHERE a = 1 AND c = 3（部分使用，只用a列）
❌ 不能使用索引的情况：
  • WHERE b = 2
  • WHERE c = 3
  • WHERE b = 2 AND c = 3

3. 特殊匹配情况

查询条件 是否使用索引 说明 a > 1 ✅ 范围查询 使用a列索引 a = 1 AND b > 2 ✅ 部分范围 使用a、b列索引 a = 1 AND b LIKE \'abc%\' ✅ 前缀匹配 使用a、b列索引 a = 1 AND b LIKE \'%abc\' ❌ 后缀模糊 只用a列索引 a = 1 ORDER BY b ✅ 排序优化 使用a、b列索引

三、联合索引的实际应用

1. 索引设计建议

  1. 高频查询优先：将最常用的查询条件放在最左边
  2. 高区分度优先：选择性高的列（唯一值多）放在前面
  3. 考虑排序需求：ORDER BY/GROUP BY的列可加入索引
  4. 避免过度索引：通常3-4列的联合索引足够

2. 性能优化示例

-- 表结构CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, create_time DATETIME, status TINYINT, amount DECIMAL(10,2), INDEX idx_user_status_time (user_id, status, create_time));-- ✅ 高效查询（完全匹配）SELECT * FROM orders WHERE user_id = 1001 AND status = 1 ORDER BY create_time DESC;-- ✅ 高效查询（部分匹配）SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC;-- ❌ 低效查询（违反最左前缀）SELECT * FROM orders WHERE status = 1 AND create_time > \'2023-01-01\';

3. 使用EXsql验证

EXsql SELECT * FROM orders WHERE user_id = 1001 AND status = 1;

观察key列显示idx_user_status_time且type为ref表示索引有效使用

四、常见误区与注意事项

  1. 列顺序误区：
    • 错误认知：INDEX(a,b)和INDEX(b,a)是等价的
    • 实际情况：两者是完全不同的索引，适用不同查询
  2. 索引合并误区：
    • MySQL可能使用index_merge策略合并多个单列索引
    • 但通常不如设计良好的联合索引高效
  3. 索引长度限制：
    • InnoDB索引最大长度767字节（utf8mb4下约191字符）
    • 长字段不适合作为联合索引的前导列
  4. 更新代价：
    • 联合索引的更新代价高于单列索引
    • 频繁更新的列不宜放在索引中

通过合理设计联合索引并遵循最左前缀原则，可以显著提高查询性能，减少全表扫描。

网络标签：

相关问题