哥妞 海淀山后有哥妞

mysql索引下推(ICP)例子

2020-12-21

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%'

image

例子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;

创建了联合索引的情况

image

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

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

image

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

==注意==:是否使用索引下推,一个是看索引下推的开关(可以通过上文进行设置)。

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

例子2

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

image

可以看到扫描行数为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);

1分也是爱


微信扫描二维码,关注公众号 (转载本站文章请注明作者和出处 哥妞-geniu)

Similar Posts

评论