MySQL索引使用规则详解：从设计到优化的完整指南

一、索引设计三大黄金原则

1. 选择性优先原则

选择区分度高的字段建立索引（区分度=不同值数量/总行数），例如用户ID、手机号等唯一性字段的区分度接近1，而性别字段仅有0.5的区分度则不适合建索引。 计算公式：

SELECT COUNT(DISTINCT column)/COUNT(*) FROM table;

当结果>0.2时适合建索引。对于长字符串字段建议使用前缀索引（如ALTER TABLE users ADD INDEX idx_email(email(10))）。

2. 精简存储原则

• 字段精简：整型字段优先于字符串，数值比较比字符串更快。 
• 长度优化：字符串索引建议使用前20%字符即可覆盖80%查询需求。 
• 联合索引顺序：高频字段靠左，短字段优先。（例如索引(status,create_time)比(create_time,status)更高效）

3. 场景关联原则

• 高频查询字段：WHERE、JOIN、ORDER BY、GROUP BY子句中的字段必须建索引 
• 关联查询字段：多表JOIN的关联键必须索引化 
• 排序字段组合：WHERE a=1 ORDER BY b场景需要建立(a,b)联合索引 

二、核心使用规则解析

1. 最左前缀法则（联合索引铁律） 

联合索引(a,b,c)的使用规则：

• ✅ 有效场景： WHERE a=1 WHERE a>5 AND b=2 WHERE a=1 ORDER BY b
• ❌ 失效场景： WHERE b=2（未命中左列） WHERE a=1 AND c=3（中间列断裂） WHERE a=1 AND b LIKE '%abc'（范围查询阻断后续列）

2. 覆盖索引与回表机制 

• 覆盖索引：查询字段完全包含在索引中

  -- 索引(name,age)
  SELECT name,age FROM users WHERE name='张三'; -- 无需回表

• 回表查询：需二次查找主键索引

  SELECT * FROM users WHERE name='张三'; -- 需回表获取其他字段

3. 索引失效六大陷阱 

失效场景	示例	解决方案
前导模糊查询	WHERE name LIKE '%张'	改为后缀模糊'张%'
隐式类型转换	WHERE phone=13800138000（phone为varchar）	显式转换phone='13800138000'
索引列参与运算	WHERE YEAR(create_time)=2025	改写为范围查询BETWEEN '2025-01-01' AND '2025-12-31'
OR条件未优化	WHERE a=1 OR b=2	改为UNION合并查询
联合索引顺序错误	索引(a,b)但查询WHERE b=2 AND a=1	调整条件顺序
非单调主键导致页分裂	UUID作为主键	改用自增ID

三、高级优化策略

1. 联合索引设计技巧 

• 范围查询右置原则：将范围查询字段放在索引最右侧

  -- 查询WHERE a>10 AND b=20，索引应设计为(b,a)

• 排序字段前置原则：ORDER BY create_time DESC时，将排序字段加入索引左列

2. 索引合并与重构 

• 避免冗余索引：已有(a,b)时，单独a索引冗余
• 索引下推优化：5.6+版本支持将WHERE条件推送到存储引擎层过滤 
• 索引合并策略：通过UNION替代OR查询，或使用WHERE (a=1 AND b=2) OR (a=3 AND b=4)触发索引合并

3. 特殊场景处理

• 深度分页优化：

  -- 原始查询：SELECT * FROM logs ORDER BY id LIMIT 100000,10; 
  -- 优化方案：SELECT * FROM logs WHERE id > 100000 ORDER BY id LIMIT 10;

• 函数索引应用：

  -- 按月统计场景建立函数索引 ALTER TABLE orders ADD INDEX idx_month((DATE_FORMAT(create_time,'%Y%m')));

四、运维与监控要点

1. 索引数量控制：单表索引不超过5个 ，冗余索引定期清理
2. 写性能平衡：索引导致写操作下降30%-50% ，高频更新字段谨慎建索引
3. 统计信息维护：

   ANALYZE TABLE users; -- 更新统计信息 
   SHOW INDEX_STATISTICS; -- 查看索引使用率

4. 主键设计规范：采用BIGINT AUTO_INCREMENT避免页分裂 

五、最佳实践案例

用户中心表优化示例：

-- 原始表 
CREATE TABLE users ( 
id VARCHAR(32) PRIMARY KEY, 
mobile CHAR(11), 
name VARCHAR(50), 
created_at DATETIME ); 
-- 优化后 
CREATE TABLE users ( 
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 自增主键 
mobile CHAR(11) NOT NULL,
name VARCHAR(50) NOT NULL, 
created_at DATETIME NOT NULL, 
INDEX idx_mobile(mobile), 
INDEX idx_created_name(created_at, name(10)) -- 联合索引+前缀 
);

通过将主键改为自增整型，查询性能提升3倍；联合索引(created_at,name(10))使SELECT * FROM users WHERE created_at>'2025-01-01' ORDER BY name LIMIT 100减少80%的排序时间。

参考资料

1. CSDN: B+树索引原理
2. 腾讯云: 索引使用原则
3. 阿里云索引规范
4. MySQL索引优化全攻略