
Hello,我是小兔大白糖。 读完能用最左前缀与选择性解释复合索引是否命中,并避免把索引当银弹。
MySQL 索引选型先看最左前缀与选择性,再谈回表代价;索引不是银弹。
估计阅读时间:18 分钟
一句话结论是什么?
索引不是银弹,而是精确制导武器;深入理解 B+ Tree 物理结构、成本估算模型与最左前缀法则,才能在 Risk 场景下用最小写入代价换取最大查询性能。
1. 这是什么?概念怎么定义
1.1 B+ Tree(B+ 树)
InnoDB 存储引擎使用 B+ Tree 作为默认索引数据结构。与 B 树不同,B+ 树的所有数据记录都存储在叶子节点,非叶子节点仅存储键值和指针,形成一个稠密索引。叶子节点之间通过双向链表连接,支持高效的范围扫描和顺序访问。
类比:B+ 树就像一座图书馆的楼层导览系统。非叶子节点是每层楼的总览指示牌(只告诉你某类书在左边还是右边),叶子节点是具体的书架(存放着实体书),而书架之间有过道相连,你可以沿着过道连续浏览,不需要反复回电梯口。
1.2 Clustered Index(聚簇索引)
聚簇索引决定了数据在磁盘上的物理存储顺序。InnoDB 表必有且仅有一个聚簇索引,默认以主键构建;若未定义主键,则选择第一个非空唯一索引;否则隐式生成 6 字节的 row_id。聚簇索引的叶子节点存储的是完整的行记录(Row Data)。
1.3 Non-Clustered Index(非聚簇索引 / 二级索引)
非聚簇索引的叶子节点不存储完整行数据,而是存储索引列值 + 对应聚簇索引的键值(即主键)。查询时若需其他字段,必须执行"回表"(Lookup)操作,根据主键再到聚簇索引中查找完整记录。
1.4 Covering Index(覆盖索引)
当查询所需的所有列都能从非聚簇索引的叶子节点直接获取,无需回表,即称为覆盖索引。它是减少随机 I/O 的最有效手段之一。
1.5 Leftmost Prefix(最左前缀法则)
对于复合索引 (a, b, c),查询条件必须从索引的最左列开始连续匹配,才能利用该索引。WHERE a = 1 AND c = 3 只能用上 (a) 的部分;WHERE b = 2 AND c = 3 则完全用不上该索引。
1.6 Index Condition Pushdown(ICP,索引下推)
MySQL 5.6 引入的优化。在不使用 ICP 时,存储引擎根据索引前缀定位记录后回表,再由 Server 层过滤剩余条件。使用 ICP 后,存储引擎在索引遍历阶段就将 WHERE 中可下推的条件在引擎层过滤,减少回表次数。
1.7 为什么需要这些机制
- B+ 树:平衡树结构保证 O(logN) 的等值查询,叶子节点链表保证 O(N) 的顺序范围扫描,且节点大小与磁盘页(16KB)对齐,减少 I/O。
- 聚簇索引:将相关数据物理聚集,极大提升主键查询和范围查询性能。
- 覆盖索引:避免回表的随机 I/O,将查询成本从数十次 I/O 降至数次。
- ICP:减少 Server 层与引擎层的数据传输,降低回表率。
2. 核心原理是什么?
2.1 InnoDB 页结构与 B+ Tree 物理布局
InnoDB 以页(Page,默认 16KB)为最小 I/O 单位。一个 B+ 树节点对应一个页。
页头(File Header / Page Header)包含前后页指针、页类型、校验和等信息。用户记录(User Records)按主键顺序通过行格式的 next_record 指针形成单链表。页目录(Page Directory)存储稀疏槽位(Slots),加速页内二分查找。
当插入导致页空间不足时,触发页分裂(Page Split):
- 申请一个新的空白页;
- 将原页约 50% 的记录迁移到新页;
- 更新双向链表指针和父节点的目录项;
- 若父节点也满,则递归向上分裂,可能增加树高。
随机主键(如 UUID)会导致频繁的页分裂和碎片,而自增 ID 能保证顺序追加,减少分裂开销。
2.2 索引选择器的成本估算
MySQL 优化器使用基于成本的模型选择执行计划。核心代价公式:
Cost = IO Cost + CPU Cost
IO Cost = 预估读取页数 * IO_BLOCK_READ_COST
CPU Cost = 预估扫描行数 * CPU_TUPLE_COST + 比较次数 * CPU_COMPARE_COST
优化器会统计索引的 cardinality(基数,即不重复值数量)和表中总行数,通过 rows * (1 - (1 - 1/rows)^cardinality) 估算选择率。若统计信息过时(如大量数据变更后未 ANALYZE),优化器可能做出灾难性选择(比如对百万级表选错索引做 NLJ)。
2.3 B+ Tree 与二级索引查询路径
2.4 慢查询优化决策树
3. 怎么用?关键代码与配置
3.1 EXPLAIN 核心字段全解读
| 字段 | 含义与风险点 |
|---|---|
id | 执行顺序标识,数字大的先执行,相同的从上到下 |
select_type | SIMPLE/PRIMARY/SUBQUERY/DERIVED;DERIVED 表示物化子查询,需警惕临时表 |
type | 访问类型,性能从优到劣:system > const > eq_ref > ref > range > index > ALL。ALL 表示全表扫描,必须优化 |
possible_keys | 优化器考虑使用的索引,仅作参考 |
key | 实际选择的索引。若为空或选择错误,需强制索引或分析统计信息 |
ref | 与索引列比较的常量或列 |
rows | 估算需扫描的行数。数值与 Handler_read_% 结合判断准确性 |
filtered | 经过表条件过滤后剩余行百分比 |
Extra | 关键诊断信息:Using index(覆盖索引)、Using where(Server 层过滤)、Using filesort(额外排序,危险)、Using temporary(临时表,危险)、Using index condition(ICP 生效)、Range checked for each record(索引嵌套循环) |
3.2 风控订单表(risk_order)索引设计实战
业务背景:大型加密货币交易所 Risk 系统每天处理数千万级订单,需高频查询:
- 按 user_id + 时间范围查询用户近期订单;
- 按 status + amount 范围查询可疑大额订单;
- 按 order_id 精确查询单条记录;
- 按 risk_level + created_at 查询待审核队列。
-- 表结构: 风控订单表
CREATE TABLE `risk_order` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
`order_id` VARCHAR(64) NOT NULL COMMENT '业务订单号',
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
`symbol` VARCHAR(32) NOT NULL COMMENT '交易对',
`side` VARCHAR(8) NOT NULL COMMENT 'BUY/SELL',
`amount` DECIMAL(32, 16) NOT NULL COMMENT '订单金额',
`status` VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/REVIEW/APPROVED/REJECTED',
`risk_level` TINYINT NOT NULL DEFAULT 1 COMMENT '风险等级 1-5',
`risk_score` DECIMAL(5, 2) NOT NULL DEFAULT 0.00 COMMENT '风控评分',
`source` VARCHAR(32) NOT NULL COMMENT '来源渠道',
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
UNIQUE KEY `uk_order_id` (`order_id`),
KEY `idx_user_created` (`user_id`, `created_at`),
KEY `idx_status_amount` (`status`, `amount`),
KEY `idx_risk_level_created` (`risk_level`, `created_at`),
KEY `idx_covering_list` (`user_id`, `status`, `amount`, `risk_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='风控订单表';3.3 Java + MyBatis 业务级 Demo
// Entity
public class RiskOrder {
private Long id;
private String orderId;
private Long userId;
private String symbol;
private String side;
private BigDecimal amount;
private String status;
private Integer riskLevel;
private BigDecimal riskScore;
private String source;
private LocalDateTime createdAt;
private LocalDateTime updatedAt;
// getters and setters
}
// Mapper Interface
@Mapper
public interface RiskOrderMapper {
/**
* 查询用户最近 N 天的订单(利用 idx_user_created)
*/
@Select("SELECT * FROM risk_order " +
"WHERE user_id = #{userId} " +
" AND created_at >= #{startTime} " +
"ORDER BY created_at DESC " +
"LIMIT #{limit}")
@Options(maxRows = 1000)
List<RiskOrder> selectRecentByUser(@Param("userId") Long userId,
@Param("startTime") LocalDateTime startTime,
@Param("limit") int limit);
/**
* 覆盖索引查询:仅获取必要字段,避免回表(利用 idx_covering_list)
*/
@Select("SELECT user_id, status, amount, risk_level FROM risk_order " +
"WHERE user_id = #{userId} AND status = #{status} " +
"LIMIT #{limit}")
List<RiskOrderSummary> selectSummaryByUserAndStatus(@Param("userId") Long userId,
@Param("status") String status,
@Param("limit") int limit);
/**
* 范围查询可疑大额订单(利用 idx_status_amount)
*/
@Select("SELECT * FROM risk_order " +
"WHERE status = 'PENDING' " +
" AND amount >= #{minAmount} " +
"ORDER BY amount DESC " +
"LIMIT #{limit}")
List<RiskOrder> selectSuspiciousLargeOrders(@Param("minAmount") BigDecimal minAmount,
@Param("limit") int limit);
/**
* 批量插入(注意批量大小控制,避免 redo log 暴增)
*/
void batchInsert(@Param("list") List<RiskOrder> list);
}<!-- RiskOrderMapper.xml -->
<insert id="batchInsert">
INSERT INTO risk_order
(order_id, user_id, symbol, side, amount, status, risk_level, risk_score, source, created_at, updated_at)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.orderId}, #{item.userId}, #{item.symbol}, #{item.side}, #{item.amount},
#{item.status}, #{item.riskLevel}, #{item.riskScore}, #{item.source}, #{item.createdAt}, #{item.updatedAt})
</foreach>
</insert>3.4 慢查询分析与优化实例
假设生产环境出现以下慢查询:
SELECT * FROM risk_order
WHERE user_id = 888888
AND status IN ('PENDING', 'REVIEW')
AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;执行 EXPLAIN:
id: 1
select_type: SIMPLE
table: risk_order
type: ref
possible_keys: idx_user_created,idx_status_amount,idx_covering_list
key: idx_user_created
ref: const
rows: 150000
Extra: Using where; Using filesort
问题诊断:
type: ref仅命中idx_user_created的user_id前缀;status IN (...)导致created_at列无法继续走索引有序性;Extra: Using filesort表示对 15 万行做内存/磁盘排序,性能极差。
优化方案——调整索引为:
ALTER TABLE risk_order
DROP INDEX idx_user_created,
ADD INDEX idx_user_status_created (`user_id`, `status`, `created_at`);优化后 EXPLAIN:
type: range
key: idx_user_status_created
rows: 1200
Extra: Using index condition
filesort 消失,扫描行从 15 万降至 1200。
3.5 强制索引与索引提示
当优化器因统计信息偏差选择错误索引时:
SELECT * FROM risk_order FORCE INDEX (idx_user_status_created)
WHERE user_id = 888888 AND status = 'PENDING'
ORDER BY created_at DESC LIMIT 100;或使用 STRAIGHT_JOIN、USE INDEX 提示,但应优先通过 ANALYZE TABLE risk_order 修正统计信息。
4. 业界怎么做?
4.1 阿里巴巴:电商大促期间的索引降级与热点优化
阿里巴巴在双 11 期间面临极高的写入并发。订单表(类似 risk_order)的二级索引维护开销在峰值写入时成为瓶颈。其技术团队采取的策略包括:
- 前缀索引替代全列索引:对长字符串字段(如订单备注、地址)使用前缀索引
KEY idx_addr_prefix (address(10)),减少索引页体积,提升缓存命中率。 - 写入降维:大促前临时删除部分非核心查询索引,大促后通过
pt-online-schema-change重建,避免INSERT时的多棵 B+ 树维护竞争。 - 热点页监控:利用
information_schema.INNODB_BUFFER_PAGE观察哪些索引页被频繁访问,识别"右边界热点"(自增主键的最后一个页集中插入导致 latch 竞争),并通过表分区(Partitioning)打散写入。
踩坑教训:某年因未及时 ANALYZE TABLE,优化器对分区表选择了 index_merge,导致 type: index_merge 下的笛卡尔积式回表,CPU 打满。后通过 optimizer_switch='index_merge=off' 全局关闭该优化策略解决。
4.2 美团:配送系统地理位置索引与最左前缀陷阱
美团外卖的骑手派单系统依赖地理围栏查询。早期在 rider_location 表上创建了复合索引 (city_id, district_id, lng, lat),期望支持城市+区县+经纬度范围查询。
实际运行中发现,当查询条件为 WHERE city_id = 1 AND lng BETWEEN 116.3 AND 116.5 时,索引仅命中 city_id,lng 的范围条件因跳过 district_id 而无法使用索引有序性,导致大量 filesort。
解决方案:
- 索引拆分:将地理查询与城市行政查询解耦。行政查询使用
(city_id, district_id);地理范围查询改用 MySQL 8.0 的 Spatial Index(R-Tree)或应用层 GeoHash 前缀索引(geohash(8))。 - 业务维度冗余:对高频查询场景做适度反范式化,将
city_id与geohash_prefix合并为冗余列,创建单一索引避免最左前缀断裂。
踩坑教训:盲目追求宽索引试图"一索引解千愁",反而导致索引体积膨胀、缓冲池命中率下降,写入性能劣化 30%。
5. 业务场景怎么对照?
5.1 风控 / Risk 场景如何应用
在 大型加密货币交易所 Risk 系统中,索引设计的核心矛盾是高频写入(订单流入)与复杂风控查询(规则引擎扫描)之间的 I/O 竞争。
-
按用户轨迹查询(user_id + time range)
- 索引:
idx_user_created(user_id, created_at) - 场景:调查某用户近期交易行为,做关联分析。
- 要点:
user_id为等值查询放最左,created_at范围查询放右侧,保证索引有序性,消除filesort。
- 索引:
-
可疑订单扫描(status + amount range)
- 索引:
idx_status_amount(status, amount) - 场景:风控规则引擎定时扫描 PENDING 状态下超过阈值的大额订单。
- 要点:
status的基数低(仅几种状态),但它是等值过滤条件,必须放最左。若将amount放最左,则status的等值条件无法利用索引有序性。
- 索引:
-
风险评分队列(risk_level + created_at)
- 索引:
idx_risk_level_created(risk_level, created_at) - 场景:人工审核台按风险等级拉取待审核订单,按时间倒序。
- 要点:等级越高的订单越少,低等级订单量大。若扫描量过大,考虑在应用层做分片查询或引入 Redis 有序集合做索引加速。
- 索引:
-
覆盖索引减少回表
- 风控列表页通常只需展示订单号、金额、状态、风险等级等少数字段。
- 创建覆盖索引
idx_covering_query(user_id, status, amount, risk_level, order_id),让列表查询完全在索引内完成,降低 90% 的随机 I/O。
5.2 常见误区 vs 正确做法
| 误区 | 后果 | 正确做法 |
|---|---|---|
| 所有字段都加索引 | 写入性能暴跌,缓冲池被索引页占满 | 只加在 WHERE、JOIN、ORDER BY 的高频字段上 |
| 将高基数字段放复合索引最左 | 若该字段做范围查询,后续列失效 | 将等值查询字段放最左,范围/排序字段放右侧 |
| SELECT * 配合二级索引 | 必然回表,放大随机 I/O | 只取必要字段,尽量利用覆盖索引 |
| 忽视统计信息过期 | 优化器选错索引,执行计划灾难 | 定期 ANALYZE TABLE,监控 rows 估算偏差 |
| 用 UUID 做主键 | 页分裂频繁,磁盘碎片严重 | 使用自增 BIGINT 或雪花 ID(趋势递增) |
大偏移量分页 LIMIT 100000, 10 | 扫描 10 万行后丢弃 | 用覆盖索引 + 延迟关联,或基于游标/书签分页 |
索引列上套用函数 WHERE YEAR(created_at) = 2024 | 索引失效,触发全表扫描 | 改写为范围查询 WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' |
5.3 选型建议
- When to use B+ Tree Index:等值查询、范围查询、排序、分组、JOIN 条件。
- When NOT to use B+ Tree Index:低基数字段单独索引(如性别 status='Y/N');频繁更新的小表;写多读少且查询无明确模式的日志表(考虑 TokuDB/RocksDB 或归档策略)。
- When to use Covering Index:列表查询、高频 KV 查询、报表汇总查询。
- When to avoid wide composite index:索引列总长度超过 3072 字节(InnoDB 限制),或索引选择性增益边际递减时。
6. 高频问题怎么答?
Q1:为什么 InnoDB 用 B+ 树而不是 B 树或 Hash 索引?
B 树的非叶子节点也存储数据,导致每个节点能容纳的键值更少,树更高,I/O 更多;且 B 树叶子节点无链表,范围查询需中序遍历,效率低。Hash 索引仅支持等值查询,无法做范围扫描和排序,且不支持最左前缀。B+ 树通过"数据只在叶子、叶子成链表"的设计,同时优化了等值查询与范围查询,与磁盘页对齐后顺序 I/O 效率极高。
Q2:索引失效的 10 种常见情况有哪些?
- 对索引列做函数运算或隐式类型转换;
- 复合索引未遵循最左前缀法则;
- 索引列参与计算(如
WHERE amount + 1 > 100); - 索引列使用
NOT、!=、<>等非等值否定; LIKE以通配符开头(%abc);OR条件中某一列无索引;- 字符串与数字比较导致隐式转换;
IS NOT NULL在部分版本优化不佳;- 优化器判断全表扫描成本更低(如小表或索引选择性极低);
- 数据分布不均导致
rows估算偏差,优化器主动放弃索引。
Q3:Order By 如何避免 filesort?
让 ORDER BY 的列与索引列顺序完全一致,且方向一致(全部 ASC 或全部 DESC)。MySQL 8.0 支持混合排序的索引(DESC 索引),但 5.7 及之前版本若混用 ASC/DESC 会触发 filesort。另外,WHERE 中的等值条件 + ORDER BY 的排序列连续出现在索引中即可利用索引有序性,例如索引 (a, b),查询 WHERE a = 1 ORDER BY b 不会 filesort。
Q4:聚簇索引与非聚簇索引在写入性能上有什么差异?
聚簇索引的写入直接决定数据页布局,顺序主键是追加写,随机主键是随机写并可能触发页分裂。非聚簇索引的每次写入(INSERT/UPDATE/DELETE)都需要额外维护一棵或多棵 B+ 树,产生更多的 redo/undo log。因此,索引数量与写入吞吐量成反比。在高并发写入场景下,应严控二级索引数量,必要时采用"异步索引构建"或"写时合并"策略。
Q5:覆盖索引一定能避免回表吗?如果查询条件中包含不在索引中的列呢?
覆盖索引的定义是 SELECT 列表和 WHERE 条件中使用的列都在索引中。如果 WHERE 中引用了不在索引中的列(且无法下推),则仍然需要回表以获取该列进行过滤。但 ICP(Index Condition Pushdown)可以将 WHERE 中部分条件下推到存储引擎层,在二级索引遍历阶段就过滤,即使不是严格意义上的覆盖索引,也能减少回表次数。真正的零回表必须满足:所有需要的列都在同一个索引的叶子节点中。
Q6:EXPLAIN 中的 type=ref 和 type=range 有什么区别?
ref 表示使用非唯一索引的等值查询(= 或 <=>),返回匹配某个单独值的所有行;range 表示使用索引做范围扫描(BETWEEN、>、<、IN、LIKE 'prefix%' 等)。range 的性能通常低于 ref,因为需要扫描索引中的一个连续区间。若 range 的范围过大(如超过表总行数的 20%),优化器可能放弃索引转为 ALL。
Q7:什么是索引选择性(Selectivity),如何计算?
索引选择性 = 不重复索引值数量 / 表总行数,越接近 1 越好。选择性低的索引(如性别列,选择性约 0.5)对优化器帮助很小,因为扫描后仍要过滤大量行。可通过以下 SQL 计算:
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM risk_order;若选择性低于 0.1,单独建索引的意义不大,应考虑放入复合索引的最左或与其他高选择性列组合。
Q8:大表加索引如何做到 Online(不锁表)?
MySQL 5.6+ 的 ALGORITHM=INPLACE, LOCK=NONE 支持 Online DDL,但并非所有操作都完全无锁。最安全的生产实践是使用 pt-online-schema-change(Percona Toolkit):
- 创建新表并添加目标索引;
- 在原表上创建触发器,同步增量数据;
- 分批拷贝历史数据;
- 重命名表,完成切换。
MySQL 8.0 对 ALTER TABLE ... ADD INDEX 的 Online DDL 已大幅改善,但涉及 VARCHAR 扩容或 VIRTUAL 列时仍可能加 MDL 锁,建议大表变更一律在业务低峰期执行并配置 lock_wait_timeout。
Q9:风控场景下,面对千万级甚至亿级订单表,索引还够用吗?
单纯依赖 B+ 树索引在极端数据量下会面临缓冲池装不下、索引深度增加、维护成本飙升的问题。大型加密货币交易所 级别的 Risk 系统通常采用分层策略:
- 热数据索引:近 7 天订单保持完整二级索引,放在高性能 NVMe SSD;
- 温数据裁剪:30 天到 90 天数据保留最精简索引(仅主键 + order_id 唯一索引),历史查询走 OLAP(ClickHouse / Doris);
- 归档与分片:按时间分区(RANGE PARTITIONING)或分库分表(ShardingSphere / Vitess),将索引规模线性拆分;
- 异构索引:对复杂风控规则(如"某用户最近 30 天累计金额")预计算聚合结果存入 Redis 或 Flink 状态后端,避免实时扫描大表索引。
Q10:Count(DISTINCT col) 和索引有什么关系?如何优化?
COUNT(DISTINCT col) 若 col 上有索引,MySQL 可优化为索引扫描而非全表扫描+临时表,因为索引天然去重有序。若 col 无索引,通常会创建临时表做去重(Extra: Using temporary)。在风控报表中,若需频繁统计不同风险等级的用户数,可预先创建覆盖索引 (risk_level, user_id),甚至维护一张物化汇总表,通过 Flink 或定时任务增量更新,避免每次实时 COUNT(DISTINCT)。