MySQL索引是帮助数据库高效获取数据的数据结构，合理的索引设计能让查询性能提升数倍甚至数十倍，而不合理的索引不仅无法加速查询，还会增加写入和存储的负担。本文将从索引创建、使用和优化三个维度，讲解MySQL索引的最佳实践。
一、索引创建的最佳实践
1. 选择合适的索引列
优先为频繁出现在WHERE、ORDER BY、GROUP BY子句中的列创建索引，同时考虑列的区分度，区分度越高的列索引效果越好。比如用户表的user_id区分度远高于gender，更适合创建索引。
2. 遵循最左前缀原则创建联合索引
当需要为多列创建联合索引时，要把区分度最高的列放在最左侧，同时覆盖查询中常用的列组合。例如业务中有WHERE a = ? AND b = ?和WHERE a = ?两种查询场景，创建(a,b)联合索引就能同时覆盖两种场景，不需要单独为a创建索引。
3. 控制索引数量
每张表的索引数量建议不超过5个，过多的索引会增加插入、更新、删除操作的开销，同时占用更多存储空间。对于很少使用的查询场景，不需要为其创建专用索引。
4. 避免对长文本列直接创建索引
对于VARCHAR、TEXT这类长文本列，直接创建索引会占用大量空间且效率低，可以使用前缀索引，只索引列的前N个字符。比如对article_content列创建前缀索引：
-- 先查看不同前缀长度的区分度，选择合适的长度 SELECT COUNT(DISTINCT LEFT(article_content, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(article_content, 20)) / COUNT(*) AS sel20, COUNT(DISTINCT LEFT(article_content, 50)) / COUNT(*) AS sel50 FROM article; -- 创建前缀索引 CREATE INDEX idx_article_content_prefix ON article(article_content(50));
二、索引使用的最佳实践
1. 避免索引失效的常见场景
以下几种情况会导致索引失效，查询时会走全表扫描：
- 对索引列进行函数操作或者表达式计算，比如
WHERE YEAR(create_time) = 2023，可以改成WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01' - 使用
LIKE以通配符开头，比如WHERE name LIKE '%张三'，如果必须做后缀匹配可以考虑全文索引 - 索引列参与类型转换，比如索引列是
VARCHAR类型，查询时传入数字WHERE phone = 13800000000，需要改成WHERE phone = '13800000000' - 使用
OR连接条件时，只要有一个条件列没有索引，整个查询就不会走索引
2. 尽量使用覆盖索引
覆盖索引是指查询的字段全部包含在索引中，不需要回表查询主键索引获取数据，能大幅提升查询效率。比如有联合索引(user_id, username)，查询SELECT user_id, username FROM user WHERE user_id = 1就可以直接通过索引返回结果，不需要回表。
3. 合理使用索引排序
如果查询需要排序，尽量让排序的列和索引的列顺序一致，且排序方向相同，这样可以使用索引排序，避免额外的文件排序开销。比如索引是(a,b)，查询WHERE a = 1 ORDER BY b可以使用索引排序，而WHERE a = 1 ORDER BY b DESC如果索引没有指定降序则无法使用索引排序。
三、索引优化的常用方法
1. 定期清理冗余和无效索引
冗余索引是指已经被其他索引覆盖的索引，比如已经有(a,b)联合索引，再创建(a)索引就是冗余索引。可以通过sys.schema_redundant_indexes视图查看冗余索引，及时删除不需要的索引。同时对于长期没有使用的索引，也可以考虑删除。
2. 使用EXPLAIN分析查询计划
当查询性能不符合预期时，使用EXPLAIN命令分析查询的执行计划，查看是否使用了合适的索引，以及扫描的行数是否合理。常见的需要关注的字段：
| 字段名 | 说明 |
|---|---|
| type | 访问类型，从好到坏依次是system>const>eq_ref>ref>range>index>ALL，尽量让查询的type达到ref及以上 |
| key | 实际使用的索引，如果为NULL说明没有使用索引 |
| rows | 预估扫描的行数，数值越小越好 |
| Extra | 额外信息，出现Using filesort、Using temporary说明查询需要优化 |
示例分析查询计划：
EXPLAIN SELECT * FROM user WHERE user_id = 1;
3. 大表索引优化
对于数据量超过千万级的大表，普通的B+树索引可能性能下降，可以考虑以下优化方式：
- 如果查询场景主要是等值查询，可以使用哈希索引，MySQL的MEMORY引擎支持哈希索引，InnoDB引擎也有自适应哈希索引功能
- 如果查询场景是范围查询且数据有明显的时间特征，可以考虑按时间分区，每个分区单独维护索引
- 对于全文搜索场景，使用MySQL的全文索引替代
LIKE '%关键词%'查询
四、总结
MySQL索引的优化是一个结合业务场景持续调整的过程，没有通用的完美索引方案。创建索引时要考虑查询频率、列区分度、索引数量，使用时避免索引失效的场景，定期通过EXPLAIN分析查询计划，清理冗余索引。只有贴合业务实际的索引设计，才能最大化发挥MySQL的性能优势。
MySQLindex_optimizationindex_creationbest_practice修改时间：2026-06-16 19:06:50