MySQL 之索引的使用规则与注意事项

一、索引基础

1. 索引是什么

索引（Index）是 MySQL 中一种帮助快速查询数据的数据结构，类似于书籍的目录 —— 通过目录可以快速定位到目标章节，而无需逐页翻阅。在 MySQL 中，索引默认使用B+树结构（InnoDB 引擎），其核心目的是减少磁盘 I/O 次数，从而提升查询效率。

工作原理：

• 当没有索引时，MySQL 必须执行全表扫描（Full Table Scan），逐行检查数据
• 使用索引后，MySQL 可以通过索引快速定位到目标数据所在的数据页
• B+树结构使得查询时间复杂度从 O(n) 降低到 O(log n)

示例： 假设有一个包含100万条用户记录的表：

• 无索引时，查询 WHERE username='张三' 需要扫描100万行
• 有索引时，可能只需3-4次磁盘I/O就能找到目标数据

2. 索引的优缺点

使用索引并非 "百利而无一害"，了解其优缺点是合理使用的前提：

优点

1. 提升查询效率（尤其是大数据量场景）

   • 减少磁盘I/O次数
   • 避免全表扫描
   • 在百万级以上数据表中效果尤为显著

2. 加速ORDER BY/GROUP BY等排序分组操作

   • 索引本身是有序的
   • 可以避免临时表的创建和排序操作
   • 例如：SELECT * FROM users ORDER BY create_time DESC

3. 避免全表扫描（减少 CPU 资源消耗）

   • 减少不必要的数据读取和处理
   • 降低服务器负载

缺点

1. 占用额外磁盘空间（索引文件独立存储）

   • 索引通常占用表数据的10%-30%空间
   • 对于大型表，索引文件可能达到GB级别

2. 降低写入性能（新增/修改/删除数据时需同步维护索引）

   • 每次数据变更都需要更新索引
   • 在高并发写入场景下会成为瓶颈
   • 例如：每秒数千次的订单创建操作

3. 过多索引会增加 MySQL 优化器的选择成本

   • 优化器需要评估使用哪个索引最有效
   • 可能出现索引选择错误的情况
   • 建议单表索引数不超过5-6个

3. 常见索引类型

不同场景需要选择不同类型的索引，常见类型及适用场景如下：

主键索引（PRIMARY KEY）

• 默认自动创建，唯一且非空
• 用于唯一标识表中记录（如用户ID）
• 每个表只能有一个主键索引
• 示例：CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(20))

唯一索引（UNIQUE）

• 确保索引列的值唯一（允许NULL，且多个NULL不冲突）
• 常用于业务上需要唯一约束的字段
• 示例：手机号、邮箱、身份证号等字段
• 创建方式：ALTER TABLE users ADD UNIQUE (phone)

普通索引（INDEX）

• 最基础的索引，无唯一性约束
• 用于频繁查询的字段，如用户名
• 创建方式：CREATE INDEX idx_name ON users(name)
• 适合范围查询：WHERE age BETWEEN 20 AND 30

联合索引（复合索引）

• 将多个字段组合成一个索引（如(a,b,c)）
• 需遵循"最左前缀原则"
• 适用于多字段联合查询场景
• 示例：CREATE INDEX idx_name_phone ON users(name, phone)
• 有效查询：
  • WHERE name='张三'
  • WHERE name='张三' AND phone='13800138000'
• 无效查询：
  • WHERE phone='13800138000'（不满足最左前缀）

联合索引优化技巧：

1. 将选择性高的字段放在前面
2. 避免冗余索引，如已有(a,b)索引，再创建(a)索引就是冗余的
3. 考虑查询频率和排序需求来设计索引顺序

二、索引使用核心规则

1. 联合索引：严格遵循"最左前缀原则"

联合索引的生效依赖于"从左到右"的字段匹配，缺少最左侧字段或跳过中间字段，都会导致索引失效。这种设计源于B+树索引结构的特性，索引字段的排序是按照定义顺序从左到右构建的。

以联合索引idx_name_age_sex (name, age, sex)为例，不同查询条件的索引生效情况如下：

查询 SQL	索引生效字段	是否生效	原因
SELECT * FROM user WHERE name='张三'	name	是	匹配最左前缀字段
SELECT * FROM user WHERE name='张三' AND age=20	name, age	是	从左到右连续匹配
SELECT * FROM user WHERE name='张三' AND age=20 AND sex='男'	name, age, sex	是	完全匹配联合索引
SELECT * FROM user WHERE age=20 AND sex='男'	无	否	缺少最左前缀name
SELECT * FROM user WHERE name='张三' AND sex='男'	name	部分生效	跳过中间字段age，仅name生效

重要注意事项：如果查询条件中包含"范围查询"(如>, <, BETWEEN, IN)，则范围字段右侧的索引字段会失效。

示例：SELECT * FROM user WHERE name='张三' AND age>20 AND sex='男'

此时索引仅name和age生效，sex字段因在范围字段age右侧而失效。这是因为范围查询后的条件无法保证有序性，MySQL优化器无法有效使用后续索引字段。

2. 避免对索引字段进行"函数/运算操作"

如果对索引字段使用函数(如SUBSTR(), DATE(), COUNT())或数学运算(如+, -, *)，会导致MySQL无法直接使用索引，只能进行全表扫描。这是因为索引存储的是原始值，而非计算后的值。

反例(索引失效)：

-- 对索引字段name使用函数
SELECT * FROM user WHERE SUBSTR(name, 1, 2)='张';

-- 对索引字段age进行数学运算
SELECT * FROM user WHERE age+1=21;

正例(索引生效)：

-- 避免函数操作，直接匹配字段
SELECT * FROM user WHERE name LIKE '张%';

-- 将运算转移到右侧常量
SELECT * FROM user WHERE age=21-1;

优化建议：对于必须使用函数的情况，可以考虑创建函数索引(MySQL 8.0+支持)或添加计算列并为其创建索引。

3. 避免索引字段使用"不等于"或"否定判断"

当查询条件中包含!=, <>(不等于)、NOT IN、IS NOT NULL时，MySQL优化器可能会放弃使用索引，转而进行全表扫描。这是因为这些条件通常需要检查大量数据，使用索引可能不如全表扫描高效。

反例(索引失效风险)：

SELECT * FROM user WHERE name!='张三';
SELECT * FROM user WHERE age NOT IN (20,25);
SELECT * FROM user WHERE phone IS NOT NULL;

替代方案：

1. 若业务允许，尽量用"等于"查询替代；
2. 若必须使用否定判断，可通过LIMIT限制结果集；
3. 考虑是否适合分表查询；
4. 对于IS NOT NULL，可以设置默认值并查询=默认值；
5. 对于NOT IN，可以尝试改写为LEFT JOIN ... WHERE ... IS NULL形式。

4. 模糊查询：避免"%"开头

使用LIKE进行模糊查询时，若匹配规则以%开头(如%张三)，会导致索引失效；而以%结尾(如张三%)或中间包含%(如张%三`)，则索引仍生效。这是因为索引是按照字段值的前缀排序存储的。

示例(索引字段为name)：

查询 SQL	索引是否生效	原因
SELECT * FROM user WHERE name LIKE '张三%'	是	%在右侧，可通过索引前缀匹配
SELECT * FROM user WHERE name LIKE '%张三'	否	%在左侧，无法使用索引
SELECT * FROM user WHERE name LIKE '张%三'	是	%在中间，左侧前缀"张"可匹配索引

优化建议：

1. 避免使用%开头的模糊查询
2. 对于必须使用%开头的场景，可以考虑使用全文索引(FULLTEXT)或搜索引擎(如Elasticsearch)
3. 对于固定长度的前缀查询，可以使用SUBSTRING()函数创建计算列并建立索引

5. 隐式类型转换会导致索引失效

若索引字段的类型与查询条件中的值类型不匹配，MySQL会进行"隐式类型转换"，这会破坏索引的结构，导致索引失效。这是因为类型转换相当于对索引字段使用了函数操作。

最常见的场景：

• 字符串类型字段用数字查询(如varchar类型的phone字段，用数字13800138000查询，而非字符串'13800138000')
• 日期类型字段用字符串查询

反例(索引失效)：

-- phone是varchar类型(索引字段)，查询时用数字，触发隐式转换
SELECT * FROM user WHERE phone=13800138000;

正例(索引生效)：

-- 字符串类型匹配，无隐式转换
SELECT * FROM user WHERE phone='13800138000';

最佳实践：

1. 在应用层确保查询条件的类型与字段类型一致
2. 使用ORM框架时注意类型映射
3. 对于混合类型的字段，考虑使用CAST()或CONVERT()函数显式转换

6. 优先使用"覆盖索引"，减少"回表"

"覆盖索引"指查询的字段完全被索引覆盖(即查询的列都在索引中)，此时MySQL无需从数据表中获取数据，仅通过索引即可返回结果，避免了"回表"操作(从索引找到主键后，再去主键索引查完整数据)，大幅提升效率。

示例(联合索引idx_name_age (name, age))：

查询 SQL	是否覆盖索引	是否回表	效率
SELECT name, age FROM user WHERE name='张三'	是	否	仅需扫描索引，效率高
SELECT name, age, sex FROM user WHERE name='张三'	否	是	sex不在索引中，需回表查数据

最佳实践：

1. 在创建索引时，可将频繁查询的"非索引字段"加入联合索引(如idx_name_age_sex)，实现覆盖索引
2. 避免使用SELECT *，只查询需要的字段
3. 对于大表查询，优先设计覆盖索引
4. 使用EXPLAIN查看执行计划中的Using index确认是否使用了覆盖索引

三、索引使用注意事项

1. 不要给 "低区分度" 字段建索引

区分度概念：指字段中不同值的比例（区分度 = 不同值数量 / 总记录数）。计算区分度可使用以下SQL：

SELECT COUNT(DISTINCT 字段名)/COUNT(*) FROM 表名;

低区分度字段：

• sex字段（通常只有"男/女"两个值）
• status字段（常见0/1两个状态值）
• boolean类型字段（true/false）
• 枚举类型字段（少数几个固定值）

为什么避免建索引： 当字段区分度<0.05时，MySQL优化器通常会选择全表扫描而非索引扫描，因为：

1. 索引过滤效果差（可能返回大量数据）
2. 回表查询成本高
3. 索引维护成本大于收益

反例：

-- 不应为低区分度字段建索引
CREATE INDEX idx_sex ON users(sex);  -- 区分度通常<0.01
CREATE INDEX idx_status ON orders(status); -- 通常只有0/1两种状态

正例：

-- 应为高区分度字段建索引
CREATE INDEX idx_phone ON users(phone);  -- 区分度=1
CREATE INDEX idx_email ON users(email);  -- 区分度通常>0.9

2. 避免 "重复索引" 和 "冗余索引"

重复索引： 对同一字段创建多个相同类型的索引，如：

CREATE INDEX idx_name1 ON users(name);
CREATE INDEX idx_name2 ON users(name); -- 完全重复，无意义

冗余索引： 当联合索引已包含单独索引的功能时，如：

CREATE INDEX idx_name_age ON users(name, age);
CREATE INDEX idx_name ON users(name); -- 冗余，可删除

检查方法：

SHOW INDEX FROM users;

查看结果中的Seq_in_index和Column_name字段，判断是否重复或冗余。

优化建议：

1. 定期检查并删除重复索引
2. 使用联合索引替代多个单列索引
3. 调整索引顺序使最左前缀原则发挥最大作用

3. 小表无需建索引

适用场景：

• 数据量<1000条
• 主要用于配置型数据
• 查询频率低

原因：

1. 小表全表扫描通常在0.1-1ms内完成
2. 索引维护成本（插入/更新时的B+树调整）可能高于查询收益
3. 索引会占用额外存储空间

建议：

• 监控表数据量增长情况
• 当表数据超过1万条时再考虑添加索引
• 对于配置表，可考虑使用内存数据库（如Redis）

4. 频繁更新的字段慎用索引

问题场景：

• 计数器字段（如login_count）
• 状态字段（频繁变更）
• 实时排名字段

影响：

1. 每次更新都需要同步更新索引B+树
2. 可能导致页分裂，影响写入性能
3. 增大死锁概率

解决方案：

-- 不推荐
CREATE INDEX idx_login_count ON users(login_count);

-- 推荐方案
-- 1. 使用缓存
UPDATE user_cache SET login_count = login_count+1 WHERE user_id=123;

-- 2. 定期批量更新
UPDATE users SET login_count = ? WHERE user_id = ?;

5. 避免 "过度索引"

合理数量：

• 一般表：3-5个索引
• 复杂业务表：不超过10个

过度索引的危害：

1. 写入性能下降（每次INSERT/UPDATE需维护所有索引）
2. 存储空间浪费
3. 优化器选择困难（需评估多个索引计划）

优化方法：

1. 合并索引（使用联合索引）
2. 删除不使用的索引（通过监控确定）
3. 优先保证高频查询的性能

6. 主键索引尽量用 "自增 ID"

聚簇索引特点：

• InnoDB使用主键作为聚簇索引
• 数据按主键顺序物理存储

自增ID优势：

1. 顺序插入，避免页分裂
2. 插入性能高（总是在B+树末尾追加）
3. 存储空间小（比UUID节省空间）

UUID问题：

1. 随机插入导致页分裂
2. 占用空间大（36字节）
3. 索引碎片率高

解决方案：

-- 推荐
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ...
);

-- 不推荐
CREATE TABLE users (
  id VARCHAR(36) PRIMARY KEY DEFAULT UUID(),
  ...
);

7. 注意 "NULL 值" 对索引的影响

NULL值处理：

• 唯一索引：允许多个NULL值（因为NULL≠NULL）
• 普通索引：会记录NULL值，但查询优化可能受影响

问题场景：

SELECT * FROM users WHERE name IS NULL;  -- 可能不会走索引

优化建议：

1. 设置默认值替代NULL

ALTER TABLE users MODIFY name VARCHAR(100) NOT NULL DEFAULT '';

1. 必须使用NULL时，考虑使用覆盖索引
2. 在查询中明确处理NULL情况

8. 分页查询注意 "深分页" 问题

深分页问题：

-- 扫描10010行，丢弃前10000行
SELECT * FROM users LIMIT 10000, 10;

优化方案：

1. 主键过滤法（最优）

SELECT * FROM users WHERE id > 10000 LIMIT 10;

1. 延迟关联法

SELECT * FROM users 
JOIN (SELECT id FROM users LIMIT 10000, 10) AS tmp
USING(id);

1. 业务上限制最大分页深度

适用场景：

• 用户浏览历史
• 商品列表
• 日志查询

9. 定期分析索引使用情况

监控方法：

1. 使用performance_schema

SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

1. 使用sys库（MySQL 5.7+）

SELECT * FROM sys.schema_unused_indexes;

1. 慢查询日志分析

清理策略：

1. 先备份再删除
2. 在低峰期操作
3. 监控删除后对查询性能的影响

10. 索引操作时机选择

操作风险：

1. 创建大表索引可能锁表
2. 导致写入阻塞
3. 可能引发复制延迟

最佳实践：

1. 使用ONLINE DDL（MySQL 8.0+）

ALTER TABLE users ADD INDEX idx_name (name), ALGORITHM=INPLACE, LOCK=NONE;

1. 分批创建大表索引
2. 使用pt-online-schema-change工具

操作时间窗口：

• 业务低峰期（如凌晨2-4点）
• 维护窗口期
• 业务流量低谷时段

四、如何验证索引是否生效

1. EXPLAIN：查看执行计划

EXPLAIN 是 MySQL 提供的查询执行计划分析工具，通过在查询语句前加上 EXPLAIN 关键字，可以获取 MySQL 优化器对该查询的执行策略。这是优化 SQL 查询性能的首要工具。

关键字段解析

type 字段（按性能从优到劣排序）：

• system：系统表，只有一行数据（性能最佳）
• const：通过主键或唯一索引查询单条记录
• eq_ref：多表 JOIN 时使用主键或唯一索引关联
• ref：使用非唯一索引查询
• range：索引范围查询
• index：全索引扫描（比全表扫描稍好）
• ALL：全表扫描（性能最差，索引未生效）

key 字段：

• 显示实际使用的索引名称
• 若显示 NULL，则表示未使用任何索引

rows 字段：

• MySQL 预估需要扫描的行数
• 数值越小查询效率越高

实际应用示例

-- 创建测试表
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    gender TINYINT,
    INDEX idx_name_age (name, age)
);

-- 分析查询
EXPLAIN SELECT * FROM user WHERE name='张三' AND age=20;

结果分析：

• 若 type 为 ref，key 为 idx_name_age：说明联合索引生效
• 若 type 为 ALL，key 为 NULL：说明索引未生效，需要检查查询条件或重建索引

2. SHOW PROFILE：查看 SQL 执行细节

SHOW PROFILE 提供更详细的查询执行过程分析，可以精确到每个执行阶段的耗时，特别适合分析复杂查询的性能瓶颈。

使用步骤

-- 1. 开启性能分析功能
SET profiling = 1;

-- 2. 执行需要分析的查询
SELECT * FROM user WHERE name='张三' 
ORDER BY age DESC LIMIT 100;

-- 3. 查看所有已分析的查询列表
SHOW PROFILES;

-- 4. 查看特定查询的详细执行过程
-- 其中1为查询ID，可通过SHOW PROFILES获取
SHOW PROFILE FOR QUERY 1;

-- 5. 关闭性能分析（可选）
SET profiling = 0;

关键性能指标

需要特别关注的 Status 值：

• Creating sort index：表示正在创建排序索引
• Sending data：表示正在从存储引擎读取数据
• Copying to tmp table：表示需要创建临时表
• Using filesort：表示使用外部排序（性能瓶颈）
• Using temporary：表示创建了临时表（性能瓶颈）

优化建议

当发现以下情况时，应考虑优化查询或索引：

1. Using filesort：添加适当的索引避免文件排序
2. Using temporary：优化 GROUP BY 或 ORDER BY 子句
3. 某个阶段耗时过长：针对该阶段进行特定优化

例如，对于包含排序的查询可以添加复合索引：

-- 原始查询
SELECT * FROM articles WHERE category_id=5 ORDER BY created_at DESC;

-- 优化方案
ALTER TABLE articles ADD INDEX idx_category_created (category_id, created_at);