索引

导学

索引是数据库性能优化的核心。没有索引，查询百万级数据可能需要数秒；有了合适的索引，同样的查询只需毫秒。本节从原理到实践，彻底讲透索引。

定义

索引（Index）：一种数据结构，存储了列值与行位置（或主键值）的映射关系。它让数据库可以快速定位数据，而无需扫描全表。

索引的本质：B+Tree

MySQL 5.7 的 InnoDB 存储引擎使用B+Tree作为索引数据结构。

B+Tree 的核心特征：

• 所有数据（或数据指针）都存储在叶子节点
• 叶子节点之间通过链表相连，支持高效的范围扫描
• 树高通常为 3-4 层，百万级数据只需 3-4 次 I/O

索引类型

聚簇索引（Clustered Index）

• 每个表只有一个聚簇索引，默认是主键索引
• 叶子节点直接存储完整的行数据
• 表数据本身就是按聚簇索引排序的

示例：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前数据状态（逻辑存储顺序，按主键排序）：

结果解读：虽然插入顺序是 1,2（自增），InnoDB 的聚簇索引会按 emp_id 物理排序存储。查询 WHERE emp_id = 1 时，直接通过 B+Tree 定位到叶子节点，读取完整行数据。

二级索引（Secondary Index）

• 叶子节点存储索引列值 + 主键值
• 查询时需要"回表"：先查二级索引找到主键，再用主键查聚簇索引

当前数据状态：employees 表有 2 条数据。

操作语句：

-- 在 emp_name 列上创建二级索引
CREATE INDEX idx_emp_name ON employees(emp_name);

-- 执行一个需要回表的查询
SELECT score FROM employees WHERE emp_name = '大翔';

执行过程分析：

1. 在 idx_emp_name 二级索引中查找 emp_name = '大翔'，找到对应的主键值 1
2. 用主键值 1 回到聚簇索引中查找完整行数据
3. 从聚簇索引中读取 score = 100

结果：

结果解读：这就是"回表"过程。如果查询只查 emp_name 列，则不需要回表；但只要查了不在索引中的列（如 score），就必须回表。

SQL 示例

场景一：创建索引

当前数据状态：创建演示表

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

SELECT * FROM employees;

操作语句：

-- 创建普通索引
CREATE INDEX idx_dept ON employees(dept);

-- 创建表时添加索引
CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    score DECIMAL(5,2),
    INDEX idx_emp_id (emp_id)
);

操作后数据状态：

SHOW INDEX FROM employees;

结果解读：idx_dept 是普通索引，允许重复值。现在执行 SELECT * FROM employees WHERE dept = '技术部' 时，MySQL 会使用 idx_dept 索引快速定位到符合条件的行，而不是扫描全表。

场景二：查看索引

当前数据状态：employees 表已有 PRIMARY 和 idx_dept 两个索引。

操作语句：

SHOW INDEX FROM employees;

操作后数据状态：

结果解读：

• Non_unique=0 表示唯一索引（主键），Non_unique=1 表示普通索引
• Cardinality 表示索引列的不重复值数量（预估值），Cardinality 越高，索引选择性越好
• Collation=A 表示按升序排列

场景三：删除索引

当前数据状态：employees 表有 idx_dept 索引。

操作语句：

DROP INDEX idx_dept ON employees;

SHOW INDEX FROM employees;

操作后数据状态：只剩 PRIMARY 索引。

结果解读：删除索引后，WHERE dept = '技术部' 的查询将退化为全表扫描，但写入操作会变快。

场景四：覆盖索引

当前数据状态：重新创建索引

CREATE INDEX idx_dept ON employees(dept);

操作语句：

-- 创建复合索引 (dept, emp_name)
CREATE INDEX idx_dept_name ON employees(dept, emp_name);

-- 查询 dept 和 emp_name 两列
SELECT dept, emp_name FROM employees WHERE dept = '技术部';

操作后数据状态：

结果解读：查询的列（dept, emp_name）都在 idx_dept_name 索引中，MySQL 直接从二级索引的叶子节点获取数据，无需回表查聚簇索引。这就是覆盖索引，是查询优化的重要手段。

对比需要回表的查询：

-- 需要回表，因为 score 不在 idx_dept_name 索引中
SELECT dept, emp_name, score FROM employees WHERE dept = '技术部';

执行过程：先查 idx_dept_name 找到主键，再用主键查聚簇索引获取 score。

场景五：索引加速 ORDER BY

当前数据状态：employees 表有 2 条数据。

无索引时的排序：

EXPLAIN SELECT * FROM employees ORDER BY score;

+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+

Using filesort 表示 MySQL 需要额外排序操作。

操作语句：

CREATE INDEX idx_score ON employees(score);

EXPLAIN SELECT * FROM employees ORDER BY score;

操作后数据状态：

+----+-------------+-----------+-------+---------------+----------+---------+------+------+-------+
| id | select_type | table     | type  | possible_keys | key      | key_len | ref  | rows | Extra |
+----+-------------+-----------+-------+---------------+----------+---------+------+------+-------+
|  1 | SIMPLE      | employees | index | NULL          | idx_score| 6       | NULL |    2 | NULL  |
+----+-------------+-----------+-------+---------------+----------+---------+------+------+-------+

结果解读：type=index 表示使用了索引扫描，Extra 中没有 Using filesort，说明 MySQL 直接利用索引的有序性返回结果，无需额外排序。

索引的代价

写入代价示例：

-- 创建两张相同的表，一个有索引，一个无索引
CREATE TABLE no_index_tbl (id INT PRIMARY KEY, val INT);
CREATE TABLE with_index_tbl (id INT PRIMARY KEY, val INT, INDEX idx_val (val));

-- 插入 10000 条数据对比
-- 无索引表更快，因为不需要维护额外的 B+Tree

常见误区

面试考点

Q：聚簇索引和非聚簇索引的区别？

聚簇索引叶子节点存完整行数据（InnoDB 主键索引）；非聚簇索引（二级索引）叶子节点存索引列值+主键值，需要回表。

Q：为什么主键推荐用自增整数？

自增主键是顺序插入，B+Tree 叶子节点顺序填充，页分裂少；UUID 随机插入导致频繁页分裂和碎片。

Q：什么是回表？如何减少回表？

通过二级索引查到主键后，再用主键查聚簇索引获取完整数据，这个过程叫回表。减少回表的方法：使用覆盖索引（查询列全在索引中），或索引下推（Index Condition Pushdown）。

小结

• 索引是 B+Tree 结构，让查询从 O(N) 降到 O(log N)
• InnoDB 主键索引是聚簇索引，叶子节点存完整行数据
• 二级索引需要回表，覆盖索引可避免回表
• 索引有维护代价，不是越多越好

下一章引子：普通索引允许重复值，但有些场景需要确保唯一性——这就是唯一索引。