建索引要素
下面将阿里规范中零散的MySQL索引要求，提炼为5 大核心建索引要素，并配以可直接落地的 SQL 示例和验证方法。
核心建索引要素清单
| 要素 | 阿里规范原文映射 | 核心原则 | 适用场景 |
|---|---|---|---|
| ① 唯一性保障 | 业务唯一字段必须建唯一索引 | 用UNIQUE代替应用层校验，防并发重复 | 手机号、订单号、(user_id, biz_date)组合 |
| ② JOIN 强约束 | JOIN字段同类型+必建索引 | 隐式类型转换会让索引失效，关联键必须精确匹配 | 多表ON条件字段 |
| ③ 字符串截断 | VARCHAR必须指定索引长度 | 索引越短 B+树越矮，区分度≥0.9 即可截断 | 邮箱、URL、长文本描述 |
| ④ 最左前缀法则 | (隐含) 复合索引优化 | 索引列顺序决定命中范围，等值在前，范围在后 | WHERE + ORDER BY混合查询 |
| ⑤ 覆盖索引 & 数量 | (隐含) 避免回表 & 控制索引数 | 索引包含SELECT字段可免回表；单表索引≤5 个 | 高频查询接口优化 |
逐条详解 + 实战示例
① 业务唯一字段 → 唯一索引（单列/复合）
sql
-- 单列唯一 ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone); -- 复合唯一（如：同一用户每天只能签到一次） ALTER TABLE user_signin ADD UNIQUE INDEX uk_user_date (user_id, signin_date);为什么必须建？
应用层if (exists)在高并发下仍会插入重复数据。数据库层UNIQUE是最终一致性保障，且查询时走唯一索引效率极高。
② JOIN 关联字段 → 绝对同类型 + 必建索引
sql
-- ✅ 正确：类型完全一致 users.id BIGINT UNSIGNED PRIMARY KEY orders.user_id BIGINT UNSIGNED ALTER TABLE orders ADD INDEX idx_user_id (user_id); -- ❌ 错误：VARCHAR 与 INT 关联 users.id VARCHAR(20) -- 历史遗留 orders.user_id BIGINT -- 执行 JOIN 时 MySQL 会做 CONVERT(users.id, INT)，索引直接失效，全表扫描！修复步骤：
- 统一字段类型：
ALTER TABLE users MODIFY id BIGINT UNSIGNED; - 关联字段必建索引：
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
③ VARCHAR 字段 → 前缀索引（区分度法）
不要对VARCHAR(255)直接建全量索引！用以下公式测算最佳长度：
sql
-- 测算区分度（目标 > 0.9） SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS len5, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS len8, COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS len12 FROM users;假设len8 = 0.92，则创建前缀索引：
sql
ALTER TABLE users ADD INDEX idx_email_prefix (email(8));经验值参考：
- 邮箱/手机号：通常前
8~12位区分度已 >0.95 - 中文姓名：前
2~3个汉字即可 - 订单号/UUID：区分度高，可建全量或前缀
16位
④ 复合索引 → 最左前缀法则（核心中的核心）
假设查询语句：
sql
SELECT * FROM orders WHERE status = 1 AND pay_type = 'wechat' AND create_time > '2024-01-01' ORDER BY create_time DESC;🎯正确索引顺序：
sql
-- 等值条件在前，范围/排序在后 CREATE INDEX idx_status_pay_create ON orders(status, pay_type, create_time);命中规则：
| 查询条件 | 是否走索引 | 原因 |
|---|---|---|
status=1 | ✅ | 命中第1列 |
status=1 AND pay_type='wx' | ✅ | 命中1,2列 |
pay_type='wx' | ❌ | 跳过第1列，全表扫描 |
status=1 AND create_time>... | ⚠️ | 命中1列，第3列仅用于排序过滤 |
⑤ 覆盖索引 & 索引数量控制
- 覆盖索引：索引包含查询所需所有字段，避免“回表”（二级索引 → 主键索引 → 数据行）
sql
-- 查询只拿 id, status, user_id CREATE INDEX idx_cover ON orders(status, user_id, id); -- EXPLAIN 的 Extra 列会显示 Using index（性能最优） - 数量限制：单表索引建议
≤5个。索引越多，INSERT/UPDATE/DELETE越慢（每次改数据都要同步更新索引树）。
建完索引怎么验证？（必做）
永远不要凭感觉，用EXPLAIN说话：
sql
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1;看这三列：
| 字段 | 期望值 | 说明 |
|---|---|---|
type | ref/range/eq_ref(consts) | ALL=全表扫描，必须优化 |
key | 你的索引名 | 显示实际使用的索引 |
Extra | Using where/Using index | Using filesort=排序未走索引，需调整 |
高频避坑清单
| 错误做法 | 正确做法 | 后果 |
|---|---|---|
给gender、is_deleted建单列索引 | 放入复合索引末尾，或建联合索引 | 区分度太低，优化器直接忽略索引 |
WHERE name LIKE '%abc'建普通索引 | 改用全文索引FULLTEXT或 ES | 左模糊%开头必全表扫描 |
函数/表达式包裹索引列WHERE YEAR(create_time)=2024 | 改为范围查询WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31' | 函数破坏索引有序性 |
索引字段允许NULL | 定义NOT NULL DEFAULT 0/'' | NULL值统计复杂，影响索引选择性 |
总结口诀（建议收藏）
唯一业务建 Unique，关联字段必对齐。 VARCHAR 算区分度，截断长度省空间。 等值在前范围后，最左前缀莫跳过。 覆盖索引免回表，单表不过五个键。 EXPLAIN 验证再上线，性能提升看得见。续接。。。
一、复合索引 & 最左前缀法则
底层原理（B+ 树排序规则）
假设创建复合索引：INDEX idx_abc (a, b, c)InnoDB 底层 B+ 树的数据排序顺序是：
- 先按
a排序 a相同的记录，再按b排序b相同的记录，最后按c排序
就像电话簿：先按“姓”排，姓相同按“名”排，名相同按“字”排。
最左前缀法则：查询条件必须从索引最左边开始连续匹配。一旦中间断开或遇到范围查询，后面的列就无法利用索引的有序性。
正例 vs 反例（表结构：INDEX idx_name_age_city (name, age, city)）
| 查询条件 | 索引使用情况 | 原因剖析 |
|---|---|---|
✅WHERE name = '张三' | 走索引(name) | 命中最左列 |
✅WHERE name = '张三' AND age = 25 | 走索引(name, age) | 连续命中前两列 |
❌WHERE age = 25 AND city = '北京' | 全表扫描 | 跳过最左列name，B+树无法定位起点 |
❌WHERE name = '张三' AND city = '北京' | 仅走(name)，city不走索引 | name匹配后，age未限定 →age无序 → 对应的city也无序，无法用索引快速定位 |
❌WHERE name = '张三' AND age > 20 AND city = '北京' | 走(name, age)，city失效 | age > 20是范围查询，匹配到的是age的一段连续区间，区间内的city是无序的，索引断裂 |
❌WHERE LEFT(name,3)='张'或WHERE name=123 | 全表扫描 | 函数/隐式类型转换破坏了索引列的原始值，B+树有序性被打破 |
关键结论：
- 等值查询
=、IN可以继续往后匹配 - 范围查询
>,<,BETWEEN,LIKE 'abc%'会终止后续列的索引使用 - 设计复合索引口诀：等值条件放左边，范围/排序放右边
二、覆盖索引（Covering Index）
底层原理（什么是“回表”？）
InnoDB 的表结构分两种索引：
- 聚簇索引（主键索引）：叶子节点存整行数据
- 二级索引（普通索引）：叶子节点只存
(索引列值, 主键ID)
当查询需要的字段不在二级索引中时，MySQL 必须：二级索引找到主键ID→拿着ID去聚簇索引找整行数据→ 这个动作叫回表（Bookmark Lookup）。
覆盖索引：查询的SELECT、WHERE、ORDER BY、GROUP BY所需的所有字段，全部包含在某个索引中。此时 MySQL直接读索引树就够，无需回表，性能提升 10~100 倍。
正例 vs 反例
表结构同上，主键id，索引INDEX idx_name_age (name, age)
(注：InnoDB 二级索引默认包含主键id，所以实际索引数据为(name, age, id))
| 查询语句 | 是否覆盖索引 | 原因 & EXPLAIN 表现 |
|---|---|---|
✅SELECT id, name, age FROM t WHERE name = '张三' | 是 | 索引里已有id, name, age，直接返回。Extra: Using index |
❌SELECT id, name, age, phone FROM t WHERE name = '张三' | 否（需回表） | 索引里没有phone，必须拿id去主键索引找完整行。Extra: Using where |
❌SELECT * FROM t WHERE name = '张三' | 否（需回表） | SELECT *包含所有列，索引根本装不下。阿里规范严禁生产用SELECT * |
❌SELECT name, age FROM t WHERE city = '北京' | 否（全表扫描） | WHERE条件city无索引，直接全表扫描，更谈不上覆盖 |
关键结论：
- 覆盖索引的核心是
SELECT的字段 ≤ 索引包含的字段 - InnoDB 中，任何二级索引都隐含包含主键，所以
SELECT id, 索引列...极易形成覆盖索引 - 阿里规范强调：
禁止 SELECT *不仅是为了网络带宽，更是为了逼出覆盖索引，杜绝无脑回表
三、实战验证：EXPLAIN怎么看？
永远不要猜，用执行计划说话：
sql
EXPLAIN SELECT id, name, age FROM t_user WHERE name = '张三' AND age = 25;重点关注这三列：
| 列名 | 期望值 | 说明 |
|---|---|---|
type | ref或range | ALL=全表扫描（必须优化），index=全索引扫描（比全表快） |
key | idx_name_age | 实际使用的索引名（为NULL说明没走索引） |
Extra | Using index |
|
四、阿里规范落地检查清单
| 场景 | 规范建议 | 错误做法 | 正确做法 |
|---|---|---|---|
| WHERE 多条件 | 按等值= > 范围> > 排序顺序建复合索引 | INDEX(age, name)但查询是WHERE name=? AND age> | INDEX(name, age) |
| ORDER BY 优化 | 排序字段尽量放索引末尾，且排序方向与查询一致 | ORDER BY age DESC, name ASC（混合方向） | 统一ASC或DESC，或拆成两个索引 |
| 高频查询接口 | 尽量让SELECT字段被索引覆盖 | SELECT *或SELECT col1, col2, col3... | SELECT id, name, status（仅查必要字段） |
| 索引数量 | 单表 ≤5 个复合索引 | 为每个查询单独建单列索引 | 合并相似查询的 WHERE 条件到同一个复合索引 |
终极口诀（建议贴显示器旁）
最左前缀不能跳，范围之后全失效。 等值打头范围后，复合索引顺序对。 SELECT 字段索引有，拒绝回表性能牛。 EXPLAIN 看 Extra，Using index 是高手。