索引优化是 MySQL 性能优化的核心之一，合理使用索引可以显著提高查询效率，减少 CPU 和 IO 负担
| 优化点 | 方法 |
|---|---|
| 选择合适的索引列 | WHERE、JOIN、ORDER BY、GROUP BY 频繁使用的列 |
| 最左前缀原则 | 组合索引的查询字段顺序需匹配索引顺序 |
| 覆盖索引 | SELECT 仅查询索引中的字段，避免回表 |
| 避免索引失效 | 不使用计算、函数、OR、LIKE '%xxx%' |
| 分页优化 | 使用 id > offset 代替 LIMIT offset, count |
使用 EXPLAIN 分析查询 | 发现索引是否有效 |
1. 索引优化的基本原则
1.1 选择合适的索引列
- 频繁作为
WHERE、JOIN、ORDER BY、GROUP BY条件的列，优先考虑创建索引。 - 避免对低基数（重复值多）的列建索引（如
性别）。 - 组合索引（多列索引）比单列索引更高效，但要遵循 最左前缀原则。
1.2 避免索引失效
- 索引列不能参与计算、函数、类型转换，否则索引会失效。
- 避免
LIKE '%xxx%'，应使用LIKE 'xxx%'或全文索引。 - 避免
OR连接未索引的列，否则可能导致全表扫描。 - 尽量使用覆盖索引，减少回表。
2. 索引优化策略
2.1 主键优化
选择合适的主键
- 自增 ID (
AUTO_INCREMENT) 作为主键 是最佳实践，因为数据按照主键顺序存储，可以减少页分裂，提升插入性能。 - 避免使用 UUID 作为主键，因为它是无序的，会导致大量页分裂，降低性能。
ALTER TABLE users ADD PRIMARY KEY (id);
2.2 普通索引优化
创建索引，优化查询
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '张三';
避免全表扫描，提高查询效率。
2.3 组合索引（多列索引）
遵循最左前缀原则
CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE name = '张三' AND age > 18; -- 使用索引
SELECT * FROM users WHERE age > 18; -- 无法使用索引
最左前缀匹配：name 必须出现在查询条件中，否则索引不会生效。
2.4 覆盖索引
减少回表，提高查询性能
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三'; -- 覆盖索引，无需回表
如果 SELECT 语句中的字段全部在索引中，则 MySQL 直接从索引获取数据，无需回表查询主键索引，提高性能。
2.5 前缀索引（适用于长字符串）
降低索引存储开销
CREATE INDEX idx_email ON users(email(10)); -- 只索引前10个字符
适用于长字符串（如 email、url），可以节省存储空间，但会影响索引精确度。
3. 避免索引失效
3.1 避免对索引列进行计算
索引失效示例
SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引失效正确方式
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
3.2 避免 LIKE '%xxx%'
索引失效示例
SELECT * FROM users WHERE name LIKE '%张%'; -- 不能用索引正确方式
SELECT * FROM users WHERE name LIKE '张%'; -- 可以使用索引
如果需要模糊搜索，应使用 全文索引：
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库优化');
3.3 避免数据类型不匹配
索引失效示例
SELECT * FROM users WHERE phone = 13800138000; -- phone 是 VARCHAR 类型
正确方式
SELECT * FROM users WHERE phone = '13800138000'; -- 正确匹配类型
3.4 避免 OR 导致索引失效
索引失效示例
SELECT * FROM users WHERE name = '张三' OR age = 25; -- 可能导致全表扫描
正确方式
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 25;
3.5 避免 IS NULL 和 IS NOT NULL
索引失效示例
SELECT * FROM users WHERE age IS NOT NULL; -- 可能索引失效
正确方式
ALTER TABLE users SET age DEFAULT 0 NOT NULL; -- 避免 NULL
SELECT * FROM users WHERE age > 0; -- 使用索引
4. 分页优化
4.1 避免 LIMIT 偏移量过大，低效分页（扫描大量数据）
SELECT * FROM users ORDER BY id LIMIT 100000, 10;
高效分页
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;
使用 覆盖索引 + 关联查询：
SELECT * FROM users
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 10;
5. 查询优化
5.1 使用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';
分析 type 列：
ALL：全表扫描INDEX：索引扫描RANGE：范围扫描REF：索引查找CONST：常量查找
5.2 使用 FORCE INDEX 强制使用索引
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';
5.3 适当使用 HASH 索引（仅适用于 MEMORY 引擎）
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(100),INDEX idx_name USING HASH (name)
) ENGINE=MEMORY;
