mysql 索引下推_5分钟了解MySQL/MariaDB新特性之索引下推优化

[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 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_stuG

*************************** 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)