当前位置：首页 > java >正文

MySQL索引：原理、类型与使用指南

java 2025/7/21 20:48:48

MySQL索引：原理、类型与使用指南

一、什么是MySQL索引？

索引是MySQL中一种用于快速查询和检索数据的数据结构，类似于书籍的目录。它通过对表中一列或多列的值进行排序和存储，使数据库在查询时无需扫描全表，从而大幅提升查询效率。
资料已经分类整理好：https://pan.quark.cn/s/f52968c518d3

二、索引的核心作用

加速查询：减少磁盘I/O操作，直接定位数据所在位置。
保证唯一性：唯一索引（Unique Index）可强制列值不重复（如主键索引）。
优化排序与分组：若查询涉及ORDER BY/GROUP BY，索引可避免额外排序操作。
加速表连接：在多表关联查询时，索引可快速匹配关联字段。

三、索引的数据结构

MySQL支持多种索引结构，不同存储引擎默认结构不同（如InnoDB默认使用B+树索引）：

B+树索引
- 结构特点：数据按顺序存储在叶子节点，非叶子节点仅存储索引值和指针，适合范围查询和排序。
- 适用场景：大多数场景（如WHERE条件、JOIN字段）。
哈希索引（Hash Index）
- 结构特点：通过哈希表存储索引值与数据地址的映射，查询时间复杂度为O(1)。
- 限制：仅支持等值查询（如WHERE id=1），不支持范围查询或排序。
全文索引（Full-Text Index）
- 适用场景：针对文本字段（如TEXT）的全文搜索（MySQL 5.6+ InnoDB支持）。
空间索引（Spatial Index）
- 适用场景：地理空间数据（如GEOMETRY类型）的查询。

四、索引的类型

索引类型	特点	创建语法
普通索引	最基本的索引，允许重复值。	`CREATE INDEX index_name ON table(column);`
唯一索引	索引列值唯一，可包含NULL（但只能有一个NULL）。	`CREATE UNIQUE INDEX index_name ON table(column);`
主键索引	特殊的唯一索引，不允许NULL，一张表只能有一个主键。	`ALTER TABLE table ADD PRIMARY KEY (column);`
组合索引（联合索引）	对多个列创建索引，遵循“最左匹配”原则（如索引(a,b,c)可匹配a、a+b、a+b+c）。	`CREATE INDEX index_name ON table(a, b, c);`
前缀索引	对字符串前N个字符创建索引，减少索引占用空间。	`CREATE INDEX index_name ON table(column(20));`
覆盖索引	索引包含查询所需的所有字段，无需回表查询（提升性能）。	通过组合索引实现，需包含所有查询列。

五、索引的创建与管理

1. 创建索引的时机

建议创建索引的场景：
- 经常作为查询条件（WHERE）、连接条件（JOIN）或排序字段（ORDER BY）的列。
- 高选择性列（列值唯一或重复率低，如性别重复率高，不适合索引）。
不建议创建索引的场景：
- 表数据量小（全表扫描可能更快）。
- 频繁更新的列（索引维护会增加写入成本）。
- 包含大量NULL值的列（不同存储引擎处理NULL的方式不同）。

2. 创建索引的语法

方式1：创建表时指定

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),email VARCHAR(100),INDEX idx_email (email), -- 普通索引UNIQUE INDEX idx_unique_email (email) -- 唯一索引
);

方式2：ALTER语句添加

ALTER TABLE users ADD INDEX idx_name (name); -- 普通索引
ALTER TABLE users ADD PRIMARY KEY (id); -- 主键索引（需确保列未被索引）

3. 查看索引

SHOW INDEX FROM table_name; -- 查看表中所有索引
EXPLAIN SELECT * FROM table WHERE condition; -- 通过执行计划分析索引使用情况

4. 删除索引

DROP INDEX index_name ON table_name; -- 删除普通索引
ALTER TABLE table_name DROP PRIMARY KEY; -- 删除主键索引（需先删除外键约束）

六、索引优化与注意事项

最左匹配原则
组合索引需按索引定义的顺序使用前列，例如索引(a, b, c)可匹配：
- WHERE a=1
- WHERE a=1 AND b=2
- WHERE a=1 AND b=2 AND c=3
  不匹配：WHERE b=2（未使用最左列a）。
避免索引失效的常见情况
- 查询条件包含函数或表达式（如WHERE YEAR(date)=2023，应改为WHERE date >= '2023-01-01'）。
- 列类型隐式转换（如WHERE id='123'，若id为INT类型，会触发类型转换导致索引失效）。
- 使用LIKE '%前缀%'（如WHERE name LIKE '%abc'，无法利用索引）。
- 对NULL值进行过滤（部分存储引擎会忽略NULL值的索引项）。

覆盖索引优化
设计查询时，尽量让索引包含所有需要的字段，避免回表查询。例如：

-- 索引包含email和name（覆盖查询）
CREATE INDEX idx_email_name ON users(email, name);
SELECT email, name FROM users WHERE email='test@example.com';

索引数量控制
单表索引不宜过多（建议不超过5个），否则会增加写入时的索引维护成本（插入/更新/删除都需更新索引）。

七、索引与性能分析工具

EXPLAIN命令：分析SQL执行计划，查看是否使用索引、扫描行数等。
```
EXPLAIN SELECT * FROM orders WHERE customer_id=123;
```
- 关键字段：
  - type：连接类型（const/eq_ref最优，ALL表示全表扫描，需优化）。
  - key：实际使用的索引名称。
  - rows：预估扫描的行数（越小越好）。
慢查询日志（Slow Query Log）：记录执行时间超过阈值的SQL，定位需要优化的查询。
MySQL Tuning Primitives：通过SHOW STATUS和SHOW VARIABLES监控索引使用情况（如Handler_read_rnd高表示大量全表扫描）。