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

53421222

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下的表现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

[iyunv@HE1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> show create table helei_stu\G\ *************************** 1. row ***************************        Table: helei_stu Create 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=utf8 1 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中，执行结果如下图所示：

Part2:ICP在MySQL5.7.15下的表现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

[iyunv@HE1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 22 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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_stu Create 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=utf8 1 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下的表现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

[iyunv@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 26 Server version: 10.1.16-MariaDB MariaDB Server Copyright (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 names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [helei]> show create table helei_stu\G\ *************************** 1. row ***************************        Table: helei_stu Create 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=utf8 1 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。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。