mysql索引下推（ICP）例子

1、索引下推是mysql5.6（包括）之后的优化策略。

2、是否设置了索引下推，explain执行计划查看到了 rows 行数应该是一致的。因为索引下推只是减少了回表的次数。

打开索引下推。

set optimizer_switch='index_condition_pushdown=on';

关闭索引下推

set optimizer_switch='index_condition_pushdown=off';

查看索引下推的设置状态

show VARIABLES like '%optimizer_switch%'

例子1

创建表

    CREATE TABLE `tuser` (
      `id` int(11) NOT NULL,
      `id_card` varchar(32) DEFAULT NULL,
      `name` varchar(32) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `ismale` tinyint(1) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `id_card` (`id_card`),
      KEY `name_age` (`name`,`age`)
    ) ENGINE=InnoDB

INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (1, '1', '张三', 10, 1);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (2, '2', '张三', 10, 1);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (3, '3', '张六', 30, 1);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (4, '4', '张三', 20, 1);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (5, '5', '李四', 20, NULL);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (6, '6', '王五', 10, NULL);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (7, '7', '马六', 34, NULL);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (8, '8', '朱九', 22, NULL);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (9, '9', '祝马甸', 27, NULL);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (10, '10', '毛阿敏', 234, NULL);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (11, '11', '王仲强', 33, NULL);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (12, '12', '高高高', 26, NULL);
INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (13, '13', '李航', 11, NULL);

执行以下命令

EXPLAIN select * from tuser where name like '李%' and age=11 and ismale=1;

创建了联合索引的情况

Using index condition 表示使用了索引下推。

如果删除联合索引，增加一个name字段的索引，是否会使用索引下推呢？

可以看到还是使用了索引下推。说明是否使用索引下推跟是否是联合索引无关；

注意：是否使用索引下推，一个是看索引下推的开关（可以通过上文进行设置）。

第二个，看是否使用了索引，如果索引都没有使用，也就没有所谓的索引优化了。是否使用索引又跟数据量以及查询条件有关，mysql优化器会计算各种策略的cost成本，选择成本小的策略。

例子2

EXPLAIN SELECT * from test_icp where name = 'zhangsan' and age > 8 and age < 12

可以看到扫描行数为3；

创建表

CREATE TABLE `test`.`Untitled`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_age`(`age`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (1, 'zhangsan', 22);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (2, 'lisi', 33);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (3, 'wangwu', 44);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (4, 'sdf', 12);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (5, 'abc', 13);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (6, 'bcx', 14);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (7, 'qwe', 15);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (8, 'kj', 16);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (9, 'zhangsan', 9);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (10, 'zhangsan', 11);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (11, 'zhangsan', 10);
INSERT INTO `test_icp`(`id`, `name`, `age`) VALUES (12, 'zhangsan', 22);