索引下推优化

Index Condition Pushdown（ICP）是对 MySQL 使用索引从表中检索行的情况的优化。
如果没有 ICP，存储引擎会遍历索引以查找基表中的行，并将它们返回给 MySQL 服务层，该服务层会过滤 WHERE 行的条件。
启用 ICP 后，如果 WHERE 只使用索引中的列来过滤部分条件，MySQL 服务层会推送这部分内容。WHERE 条件下推到存储引擎。然后，存储引擎使用索引条目过滤推送的索引条件，并且仅当满足该条件时才从表中读取行。
ICP 可以减少存储引擎必须访问基表的次数以及 MySQL 服务层必须访问存储引擎的次数。
索引下推 - 图1
索引下推 - 图2

条件限制

  • ICP 用于 range， ref， eq_ref，和 ref_or_null 访问方法时，有必要访问完整的表行。
  • ICP 可用于表 InnoDB 和 MyISAM 表，包括分区 InnoDB 和 MyISAM 表。
  • 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取的数量，从而减少 I/O操作。对于 InnoDB 聚簇索引，已将完整记录读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会降低 I/O。
  • 在虚拟生成列上创建的二级索引不支持 ICP。InnoDB 支持虚拟生成列上的二级索引。
  • 引用子查询的条件无法下推。
  • 无法下推涉及存储函数的条件。存储引擎无法调用存储的函数。
  • 触发条件无法下推。

    没有使用索引下推的工作原理

  1. 获取下一行，首先读取索引元组，然后使用索引元组找到并读取整个表行。

  2. 测试 WHERE 适用于此表的条件部分。根据测试结果接受或拒绝该行。

    使用索引下推的工作原理

  3. 获取下一行的索引元组（但不是完整的表行）。

  4. 测试 WHERE 适用于此表的条件部分，并且只能使用索引列进行检查。如果不满足条件，则继续下一行的索引元组。
  5. 如果满足条件，请使用索引元组来查找并读取整个表行。
  6. 测试 WHERE 适用于此表的条件的剩余部分。根据测试结果接受或拒绝该行。

    测试

    建表语句：
    1. DROP TABLE IF EXISTS `person`;
    2. CREATE TABLE `person`  (
    3. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
    4. `zipcode` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    5. `lastname` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
    6. `address` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
    7. `firstname` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
    8. PRIMARY KEY (`id`) USING BTREE,
    9. INDEX `idx_zipcode`(`zipcode`, `lastname`, `firstname`) USING BTREE
    10. ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    上表创建表的时候生成了一个联合索引 idx_zipcode。
    开启索引下推：
    set optimizer_switch='index_condition_pushdown=on';
    生成执行计划：
    EXPLAIN SELECT * FROM person
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
    执行计划：
    索引下推 - 图3
    关闭索引下推：
    set optimizer_switch='index_condition_pushdown=off';
    执行计划：
    索引下推 - 图4

    参考资料

    Index Condition Pushdown Optimization