MySQL索引核心概念
一、回表查询（Bookmark Lookup）
定义与原理
当通过二级索引进行查询时，若所需字段未完全包含在索引中，需要根据索引中的主键值回主键索引树查询完整数据行的过程。
执行流程
- 在二级索引树查找符合条件的索引条目
- 获取对应主键值
- 通过主键值到聚簇索引检索完整数据行
示例场景
表结构：
sql
sql
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(20),
INDEX idx_city (city)
);查询语句：
sql
SELECT * FROM users WHERE city = '北京';执行过程：
- 使用
idx_city索引找到所有city='北京'的记录的主键id - 逐条回主键索引树获取完整数据
性能影响
- 增加磁盘I/O次数
- 影响查询效率（约多出30-50%的时间）
二、索引覆盖（Covering Index）
核心概念
当查询字段完全包含在索引中时，无需回表即可获取数据。
优势特性
- 减少磁盘I/O
- 提升查询性能（速度可提升5-10倍）
- 利用索引顺序优化排序
实现方式
sql
-- 创建覆盖索引
CREATE INDEX idx_covering ON users(city, name, age);
-- 优化后的查询（所有字段均在索引中）
SELECT city, name, age FROM users WHERE city = '北京';设计要点
- 将SELECT字段加入组合索引
- 避免
SELECT * - 注意字段顺序优化
三、索引下推（Index Condition Pushdown, ICP）
技术原理
将WHERE条件中索引相关的过滤操作下推到存储引擎层处理，减少回表次数。
工作流程对比
无ICP时：
- 存储引擎检索索引
- 返回所有基础符合条件的记录
- Server层进行过滤
启用ICP后：
- 存储引擎在索引层进行过滤
- 仅返回完全符合条件的记录
效果示例
表结构：
sql
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
status TINYINT,
create_time DATETIME,
INDEX idx_composite (user_id, status)
);查询语句：
sql
SELECT * FROM orders
WHERE user_id = 1001
AND status > 2
AND create_time > '2023-01-01';执行优化：
- 存储引擎在
idx_composite索引中先过滤user_id=1001 AND status>2 - 仅回表查询满足条件的记录
- Server层再过滤
create_time条件
性能提升
- 减少50-70%的回表操作
- 特别适用于组合索引的部分列查询
四、综合应用策略
优化组合
索引覆盖+ICP：
sql
CREATE INDEX idx_optimize ON log_table(module, level, create_time);
SELECT module, error_code
FROM log_table
WHERE module = 'payment'
AND level > 2
AND create_time > '2023-06-01';监控工具
sql
-- 查看ICP使用情况
SHOW STATUS LIKE '%ICP%';
-- 分析索引使用
EXPLAIN ANALYZE SELECT ...;设计原则
- 优先考虑索引覆盖
- 合理使用组合索引
- 区分OLTP（在线事务处理）和OLAP（在线分析处理）场景
- 定期进行索引重组