MySQL 索引下推用两张 B 树图讲清楚查询条件中明明写了“城市是杭州”为什么数据库还会读取北京、上海用户的完整记录这正是理解索引下推的切入点。索引下推Index Condition Pushdown简称 ICP是一种查询优化扫描索引时先用索引中已有的字段判断条件只有满足条件的记录才继续回表读取完整数据。它的主要作用是减少不必要的回表。本文以 MySQL 的 InnoDB 存储引擎为例用同一条查询、同一批数据分别画出没有索引下推和有索引下推的执行过程。1. 先准备一个容易理解的例子假设有一张用户表CREATETABLE用户(编号INTPRIMARYKEY,姓名VARCHAR(20)NOTNULL,年龄INTNOTNULL,城市VARCHAR(20)NOTNULL,INDEX年龄城市索引(年龄,城市))ENGINEInnoDB;其中编号是每条记录的唯一编号也是主键。为方便讲解表名和字段名统一使用中文SQL 中用反引号包住这些名称。表中有以下数据编号姓名年龄城市1小林20杭州2小陈30上海3小王31北京4小赵32杭州5小李35上海6小周40杭州现在我们要找出年龄大于 30 岁并且居住在杭州的用户SELECT*FROM用户WHERE年龄30AND城市杭州;最终应该返回小赵和小周也就是编号为4和6的两条记录。下面假设 MySQL 选择了年龄城市索引联合索引进行范围扫描。示例数据很少真实执行时优化器也可能选择全表扫描这里重点看使用该索引时的执行机制。2. 看图之前先认识两棵 B 树这个例子涉及两棵不同的 B 树。第一棵用于筛选的二级索引树我们创建的索引是(年龄, 城市)。在 InnoDB 中这个二级索引的叶子节点还会保存主键编号。因此一条索引记录可以理解为年龄 32城市 杭州记录编号 4这里没有姓名。联合索引先按年龄排序相同年龄再按城市排序相同索引键的记录再由主键区分。不能因为索引里有城市就认为所有杭州用户都会排在一起。第二棵保存完整记录的主键索引树主键索引也叫聚簇索引。它的叶子节点保存完整记录例如记录编号 4姓名 小赵年龄 32城市 杭州这次查询使用SELECT *需要返回姓名等全部字段。二级索引里没有姓名因此还要拿着记录编号到主键索引树中查找完整记录。这个“先从二级索引取得主键再到主键索引读取完整记录”的过程就叫回表。图中的“叶子页”可以理解为 B 树最下面、实际存放记录的页面上方的“导航节点”负责指路。比如“编号小于 4 走左边否则走右边”只是树的导航规则不是新的查询条件。两张图都保留相同的结构上面是联合索引 B 树下面是主键 B 树。树的分支表示导航关系叶子页之间的横向连接表示它们按顺序相连。图为教学示意简化了内部节点并把记录分到少量叶子页中不代表这些示例数据的真实页面布局。3. 没有索引下推先回表再检查城市先看没有开启索引下推的情况。第一步按年龄找到候选记录。MySQL 利用二级索引扫描年龄 30的范围得到 4 条索引记录编号 331 岁北京。编号 432 岁杭州。编号 535 岁上海。编号 640 岁杭州。第二步这 4 条候选记录全部回表。虽然二级索引里已经存有城市但在这条未使用 ICP 的执行路径中存储引擎不会先用城市条件淘汰候选记录而是根据它们的编号读取完整数据。所以小王、小赵、小李、小周的完整记录都会被读取。第三步读取完整记录后再检查城市。MySQL 中负责执行查询的上层模块Server 层收到记录后判断城市是不是杭州去掉北京的小王和上海的小李返回小赵和小周。可以先把存储引擎理解为“负责扫描索引、读取数据的部分”把 Server 层理解为“负责 SQL 执行等工作的上层”。这里的关键是城市条件在回表之后才被判断。读图时从上往下看上面的联合索引树找到 4 条年龄符合条件的记录。两棵树之间的说明表示这 4 条记录全部需要回表。下面的主键索引树用蓝色标出本次读取的完整记录。最后才判断城市去掉北京和上海的用户。图把多次回表合并展示实际执行可以逐条扫描、逐条判断和读取。问题就在这里北京的小王和上海的小李最终根本不会出现在结果中却已经各做了一次回表。4. 有索引下推先检查城市再决定是否回表开启索引下推后按年龄扫描索引的过程没有改变。MySQL 仍然会扫描到编号3、4、5、6这 4 条索引记录。改变的是存储引擎会在二级索引中提前检查城市是不是杭州。因为城市已经在二级索引里这一步不需要先读取完整记录编号 3 的城市是北京不符合条件跳过不回表。编号 4 的城市是杭州符合条件回表读取小赵的完整记录。编号 5 的城市是上海不符合条件跳过不回表。编号 6 的城市是杭州符合条件回表读取小周的完整记录。注意“跳过”只是本次查询不再读取它的完整记录不会删除或修改任何数据。再从上往下看第二张图上面的联合索引树仍然扫描相同的 4 条索引记录。索引里已经有城市所以北京、上海的两条记录可以提前排除图中用浅橙色标出。只拿杭州用户的编号 4 和 6去下面的主键索引树读取完整记录。最终仍然返回小赵和小周但少做了两次回表。注意编号 5 与编号 4、6 恰好在同一个主键叶子页里。图中的“不回表”指不再为编号 5 单独查找完整记录并不表示包含它的数据页一定不会被读取。5. 索引下推到底省了什么对比两种执行方式对比项没有索引下推有索引下推按年龄扫描的二级索引记录4 条4 条在哪里检查城市回表后由上层执行模块检查回表前由存储引擎检查索引中的城市回表读取完整记录4 次2 次最终返回结果小赵、小周小赵、小周本例没有减少二级索引扫描的记录数减少的是不必要的回表和完整记录读取。如果按年龄找到 10 万条候选记录而其中只有 1000 条的城市是杭州那么在同样的执行条件下回表次数就可能从 10 万次降到 1000 次。候选记录越多、能提前排除的记录越多收益通常越明显。如果候选记录本来就几乎全部满足城市条件能省下的回表就很少。这里的“回表次数”不等于“实际磁盘读取次数”。数据可能已经在内存中多条记录也可能在同一个数据页上。因此回表从 4 次变成 2 次并不意味着磁盘 I/O 必然减少一半。6. 为什么叫“下推”没有索引下推时城市条件由上层执行模块Server 层在拿到完整记录之后判断。有索引下推时MySQL 把这个条件交给更靠近数据的存储引擎让它读取索引时就判断。看图中蓝色虚线连接的两个判断框左边在上层右边在下层。它表示两种方案中“城市是不是杭州”这个条件的判断位置发生了变化两侧向上的实线箭头表示完整记录从存储引擎交给上层执行模块。“下推”说的是把条件判断从上层执行模块Server 层交给存储引擎而不是把条件从 B 树的根节点推到叶子节点。7. 两个容易混淆的问题范围查询后面的索引字段是否就没用了仍然看本例的联合索引(年龄, 城市)。年龄和城市这两个字段在查询中发挥的作用不同字段及条件在本例中做什么对应的概念年龄 30找到扫描的起点扫描年龄大于 30 的索引记录即编号 3、4、5、6用索引定位扫描范围城市 杭州开启索引下推后存储引擎直接检查扫描到的索引记录中的城市只让编号 4、6 回表索引下推在回表前利用索引字段过滤为什么还要扫描北京、上海的索引记录因为联合索引先按年龄排序相同年龄再按城市排序不同年龄的杭州用户并没有集中在一个连续区间里。因此本例仍要扫描年龄大于 30 的这个范围。年龄条件用于确定扫描范围开启索引下推后城市条件用于判断扫描到的记录是否需要回表。北京、上海的索引记录仍然会被扫描到但存储引擎看见城市不符合条件就可以跳过后续的回表。这样扫描的索引记录仍是 4 条需要回表读取的完整记录只有 2 条。所以本例中范围条件后面的“城市”字段仍然有用它可以通过索引下推减少回表。索引下推本身也是利用索引的一种优化重点是把过滤提前到回表之前。索引下推和覆盖索引有什么区别本文使用SELECT *需要读取索引中没有的姓名因此符合条件的记录仍然需要回表。如果改成SELECT编号,年龄,城市FROM用户WHERE年龄30AND城市杭州;查询所需的字段都可以从二级索引中取得这就属于覆盖索引查询通常可以直接在索引中完成不需要为了取得其他字段再回表。索引下推仍需回表但尽量少回表。覆盖索引所需字段都在索引里通常无须回表。另外能下推的条件必须能利用索引里的字段判断。本例可以提前检查城市如果条件涉及索引里没有的姓名就不能直接在这棵二级索引中完成该条件的判断。是否实际采用 ICP还取决于访问方式及优化器选择等因素。8. 如何确认查询是否使用了索引下推可以查看执行计划EXPLAINSELECT*FROM用户WHERE年龄30AND城市杭州;传统EXPLAIN输出的Extra列中如果出现Using index condition说明这条执行计划使用了索引条件下推。注意它和Using index不是同一个含义后者通常表示查询可以通过覆盖索引取得所需数据。回到本文的查询最值得记住的执行顺序是没有索引下推 按年龄扫描索引 → 候选记录全部回表 → 检查城市 → 返回结果 有索引下推 按年龄扫描索引 → 先在索引中检查城市 → 符合条件的记录回表 → 返回结果能在索引里提前排除的记录就不必再读取完整数据。这就是索引下推减少查询开销的原因。明这条执行计划使用了索引条件下推。注意它和Using index不是同一个含义后者通常表示查询可以通过覆盖索引取得所需数据。回到本文的查询最值得记住的执行顺序是没有索引下推 按年龄扫描索引 → 候选记录全部回表 → 检查城市 → 返回结果 有索引下推 按年龄扫描索引 → 先在索引中检查城市 → 符合条件的记录回表 → 返回结果能在索引里提前排除的记录就不必再读取完整数据。这就是索引下推减少查询开销的原因。