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

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

web 2025/5/11 10:21:46

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

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')));

四、运维与监控要点

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

统计信息维护：

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

主键设计规范：采用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%的排序时间。