深入浅出 MySQL 回表查询与索引优化

回表查询详解

基本概念

InnoDB存储引擎中的索引分为两种类型：

1. 聚簇索引：叶子节点存储完整的行记录数据
2. 辅助索引：叶子节点只存储索引字段值和对应记录的主键值

回表查询机制

回表查询是指当使用辅助索引进行查询时，需要两次访问索引树：

1. 第一次：通过辅助索引找到主键值
2. 第二次：用主键值到聚簇索引中查找完整记录

性能分析

• 需要两次索引查找
• 可能需要两次磁盘IO

覆盖索引详解

概念定义

覆盖索引是一种索引优化技术，当查询所需的所有列都包含在索引中时，可以直接使用索引数据来满足查询。

性能优势

• 减少I/O操作
• 提高查询速度
• 降低资源消耗

实现方法

-- 创建覆盖索引
ALTER TABLE users ADD INDEX idx_name_age (name, age);

-- 使用覆盖索引
SELECT name, age FROM users WHERE name = 'John';

最左前缀原则详解

原理说明

最左前缀原则规定在使用复合索引时，查询条件必须从索引的最左侧列开始才能有效利用索引。

具体表现

1. 从最左列开始查询：有效使用索引
2. 跳过中间列的失效：只能用到部分索引
3. 不包含最左列的完全失效：索引无法使用

特殊情况

1. 范围查询后的列失效
2. 优化器自动调整

MySQL LIKE 查询与索引使用

索引使用情况

1. ‘%name%‘：索引不起作用
2. ‘%name’：索引不起作用
3. ‘name%‘：索引可以起作用

MySQL 中 NULL 值对索引的影响

NULL 值的特性

1. 不能使用普通的等于(=)运算符比较，必须使用 IS NULL 或 IS NOT NULL
2. 任何包含 NULL 的算术运算结果都是 NULL
3. COUNT() 不会统计 NULL 值行

最佳实践

1. 尽量将列设置为 NOT NULL 并设置合理的默认值
2. NOT NULL 列通常有更好的查询性能