mysql 索引下推_mysql 的索引下推（IndexCondition）简单理解

前置版本mysql> select version();

+------------+

| version() |

+------------+

| 5.7.26-log |

+------------+

先来建个表加个索引DROP TABLE IF EXISTS `indexconditionpushdown`;

CREATE TABLE `indexconditionpushdown` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`age` int(11) NOT NULL,

`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

PRIMARY KEY (`id`),

KEY `multi_name_age` (`name`,`age`)

) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------

-- Records of indexconditionpushdown

-- ----------------------------

INSERT INTO `indexconditionpushdown` VALUES ('1', '20', '陈某');

INSERT INTO `indexconditionpushdown` VALUES ('2', '30', '陈某某');

INSERT INTO `indexconditionpushdown` VALUES ('3', '30', '李某');

alter table indexConditionPushDown add index multi_name_age(name,age);

ICP版本mysql> explain select * from indexConditionPushDown where name like "陈%" and age =20;

+----+-------------+------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+

| 1 | SIMPLE | indexConditionPushDown | NULL | range | multi_name_age | multi_name_age | 771 | NULL | 1 | 33.33 | Using index condition |

+----+-------------+------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+

1 row in set

禁用ICPmysql> set optimizer_switch='index_condition_pushdown=off';

Query OK, 0 rows affected

mysql> explain select * from indexConditionPushDown where name like "陈%" and age =20;

+----+-------------+------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+

| 1 | SIMPLE | indexConditionPushDown | NULL | range | multi_name_age | multi_name_age | 771 | NULL | 1 | 33.33 | Using where |

+----+-------------+------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+

1 row in set

结论 【Extra】ICP版本为 Using index condition

禁用后未 Using where