MySQL 索引选型:B+ 树、最左前缀与回表代价

MySQL 索引选型:B+ 树、最左前缀与回表代价

Hello,我是小兔大白糖。 读完能用最左前缀与选择性解释复合索引是否命中,并避免把索引当银弹。

MySQL 索引选型先看最左前缀与选择性,再谈回表代价;索引不是银弹。

估计阅读时间:18 分钟

复合索引最左前缀匹配示意
复合索引最左前缀匹配示意 · 点击查看 SVG 原图

一句话结论是什么?

索引不是银弹,而是精确制导武器;深入理解 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):

  1. 申请一个新的空白页;
  2. 将原页约 50% 的记录迁移到新页;
  3. 更新双向链表指针和父节点的目录项;
  4. 若父节点也满,则递归向上分裂,可能增加树高。

随机主键(如 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_typeSIMPLE/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 系统每天处理数千万级订单,需高频查询:

  1. 按 user_id + 时间范围查询用户近期订单;
  2. 按 status + amount 范围查询可疑大额订单;
  3. 按 order_id 精确查询单条记录;
  4. 按 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

问题诊断:

  1. type: ref 仅命中 idx_user_created 的 user_id 前缀;
  2. status IN (...) 导致 created_at 列无法继续走索引有序性;
  3. 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)的二级索引维护开销在峰值写入时成为瓶颈。其技术团队采取的策略包括:

  1. 前缀索引替代全列索引:对长字符串字段(如订单备注、地址)使用前缀索引 KEY idx_addr_prefix (address(10)),减少索引页体积,提升缓存命中率。
  2. 写入降维:大促前临时删除部分非核心查询索引,大促后通过 pt-online-schema-change 重建,避免 INSERT 时的多棵 B+ 树维护竞争。
  3. 热点页监控:利用 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。

解决方案:

  1. 索引拆分:将地理查询与城市行政查询解耦。行政查询使用 (city_id, district_id);地理范围查询改用 MySQL 8.0 的 Spatial Index(R-Tree)或应用层 GeoHash 前缀索引 (geohash(8))。
  2. 业务维度冗余:对高频查询场景做适度反范式化,将 city_id 与 geohash_prefix 合并为冗余列,创建单一索引避免最左前缀断裂。

踩坑教训:盲目追求宽索引试图"一索引解千愁",反而导致索引体积膨胀、缓冲池命中率下降,写入性能劣化 30%。

5. 业务场景怎么对照?

5.1 风控 / Risk 场景如何应用

在 大型加密货币交易所 Risk 系统中,索引设计的核心矛盾是高频写入(订单流入)与复杂风控查询(规则引擎扫描)之间的 I/O 竞争。

  1. 按用户轨迹查询(user_id + time range)

    • 索引:idx_user_created(user_id, created_at)
    • 场景:调查某用户近期交易行为,做关联分析。
    • 要点:user_id 为等值查询放最左,created_at 范围查询放右侧,保证索引有序性,消除 filesort。
  2. 可疑订单扫描(status + amount range)

    • 索引:idx_status_amount(status, amount)
    • 场景:风控规则引擎定时扫描 PENDING 状态下超过阈值的大额订单。
    • 要点:status 的基数低(仅几种状态),但它是等值过滤条件,必须放最左。若将 amount 放最左,则 status 的等值条件无法利用索引有序性。
  3. 风险评分队列(risk_level + created_at)

    • 索引:idx_risk_level_created(risk_level, created_at)
    • 场景:人工审核台按风险等级拉取待审核订单,按时间倒序。
    • 要点:等级越高的订单越少,低等级订单量大。若扫描量过大,考虑在应用层做分片查询或引入 Redis 有序集合做索引加速。
  4. 覆盖索引减少回表

    • 风控列表页通常只需展示订单号、金额、状态、风险等级等少数字段。
    • 创建覆盖索引 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 种常见情况有哪些?

  1. 对索引列做函数运算或隐式类型转换;
  2. 复合索引未遵循最左前缀法则;
  3. 索引列参与计算(如 WHERE amount + 1 > 100);
  4. 索引列使用 NOT、!=、<> 等非等值否定;
  5. LIKE 以通配符开头(%abc);
  6. OR 条件中某一列无索引;
  7. 字符串与数字比较导致隐式转换;
  8. IS NOT NULL 在部分版本优化不佳;
  9. 优化器判断全表扫描成本更低(如小表或索引选择性极低);
  10. 数据分布不均导致 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):

  1. 创建新表并添加目标索引;
  2. 在原表上创建触发器,同步增量数据;
  3. 分批拷贝历史数据;
  4. 重命名表,完成切换。

MySQL 8.0 对 ALTER TABLE ... ADD INDEX 的 Online DDL 已大幅改善,但涉及 VARCHAR 扩容或 VIRTUAL 列时仍可能加 MDL 锁,建议大表变更一律在业务低峰期执行并配置 lock_wait_timeout。

Q9:风控场景下,面对千万级甚至亿级订单表,索引还够用吗?

单纯依赖 B+ 树索引在极端数据量下会面临缓冲池装不下、索引深度增加、维护成本飙升的问题。大型加密货币交易所 级别的 Risk 系统通常采用分层策略:

  1. 热数据索引:近 7 天订单保持完整二级索引,放在高性能 NVMe SSD;
  2. 温数据裁剪:30 天到 90 天数据保留最精简索引(仅主键 + order_id 唯一索引),历史查询走 OLAP(ClickHouse / Doris);
  3. 归档与分片:按时间分区(RANGE PARTITIONING)或分库分表(ShardingSphere / Vitess),将索引规模线性拆分;
  4. 异构索引:对复杂风控规则(如"某用户最近 30 天累计金额")预计算聚合结果存入 Redis 或 Flink 状态后端,避免实时扫描大表索引。

Q10:Count(DISTINCT col) 和索引有什么关系?如何优化?

COUNT(DISTINCT col) 若 col 上有索引,MySQL 可优化为索引扫描而非全表扫描+临时表,因为索引天然去重有序。若 col 无索引,通常会创建临时表做去重(Extra: Using temporary)。在风控报表中,若需频繁统计不同风险等级的用户数,可预先创建覆盖索引 (risk_level, user_id),甚至维护一张物化汇总表,通过 Flink 或定时任务增量更新,避免每次实时 COUNT(DISTINCT)。