MySQL/MariaDB新特性之索引下推优化

索引下推优化

Part1:index_condition_pushdown

index_condition_pushdown（ICP）默认开启，可以通过命令：

show variables like 'optimizer_switch'\G来查看，如下图所示：

Part2:原理简述这一特性从MariaDB5.3/MySQL5.6起，开始生效，我们在执行查询计划的时候，看到的Using index condition特性，简称为ICP。它可以提高检索速度，提高从server层到存储引擎层的调用速度，并减少了存储引擎访问表的次数，从而提高了数据库的整体性能。总之一句话，当你看到执行计划中出现了using index condition的时候，说明效率好！很好！非常好！就对了~

实战

Part1:ICP在MySQL5.6.25下的表现

[root@HE1 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.25-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();+------------+| version()  |+------------+| 5.6.25-log |+------------+1 row in set (0.00 sec)mysql> use helei;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show create table helei_stu\G\*************************** 1. row ***************************       Table: helei_stuCreate Table: CREATE TABLE `helei_stu` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(10) NOT NULL DEFAULT '',  `class` tinyint(3) unsigned NOT NULL DEFAULT '0',  `score` tinyint(3) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`id`),  KEY `idx_class_score` (`class`,`score`),  KEY `idx_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> explain select * from helei_stu where class=2 and score >60;+----+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+| id | select_type | table     | type  | possible_keys   | key             | key_len | ref  | rows | Extra                 |+----+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+|  1 | SIMPLE      | helei_stu | range | idx_class_score | idx_class_score | 2       | NULL |    8 | Using index condition |+----+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+1 row in set (0.04 sec)

可以看出，在MySQL5.6.25中，执行结果如下图所示：

[root@HE1 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 22Server version: 5.7.15-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();+------------+| version()  |+------------+| 5.7.15-log |+------------+1 row in set (0.00 sec)mysql> show create table helei_stu\G*************************** 1. row ***************************       Table: helei_stuCreate Table: CREATE TABLE `helei_stu` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(10) NOT NULL DEFAULT '',  `class` tinyint(3) unsigned NOT NULL DEFAULT '0',  `score` tinyint(3) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`id`),  KEY `idx_class_score` (`class`,`score`),  KEY `idx_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> explain select * from helei_stu where class=2 and score >60;                                                                                          +----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+| id | select_type | table     | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | helei_stu | NULL       | range | idx_class_score | idx_class_score | 2       | NULL |    8 |   100.00 | Using index condition |+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)

可以看出，在MySQL5.7.15中，执行结果如下图所示：

Part3:ICP在MariaDB10.1.16下的表现

[root@HE3 ~]# /usr/local/mariadb/bin/mysql -uroot -S /tmp/mariadb.sock Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 26Server version: 10.1.16-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> select version();+-----------------+| version()       |+-----------------+| 10.1.16-MariaDB |+-----------------+1 row in set (0.00 sec)MariaDB [(none)]> use helei;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [helei]> show create table helei_stu\G\*************************** 1. row ***************************       Table: helei_stuCreate Table: CREATE TABLE `helei_stu` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(10) NOT NULL DEFAULT '',  `class` tinyint(3) unsigned NOT NULL DEFAULT '0',  `score` tinyint(3) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`id`),  KEY `idx_class_score` (`class`,`score`),  KEY `idx_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf81 row in set (0.00 sec)MariaDB [helei]> explain select * from helei_stu where class=2 and score >60;+------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+| id   | select_type | table     | type  | possible_keys   | key             | key_len | ref  | rows | Extra                 |+------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+|    1 | SIMPLE      | helei_stu | range | idx_class_score | idx_class_score | 2       | NULL |    8 | Using index condition |+------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+1 row in set (0.00 sec)MariaDB [helei]> select version();+-----------------+| version()       |+-----------------+| 10.1.16-MariaDB |+-----------------+1 row in set (0.00 sec)

可以看出，在MariaDB10.1.16中，执行结果如下图所示：

——总结——

在MySQL5.5中，无这一特性，所以会先根据class=1来查找记录，最后根据score>60过滤。而MySQL5.6/MySQL5.7和MariaDB 5.3/5.5/10.0/10.1 版本中，在查找class=1的同时，会根据score>60进行过滤，所以这里看到的是using index condition。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。