mysql:索引使用_mysql索引使用-CSDN博客

本文链接：https://blog.csdn.net/wangdamingll/article/details/107661221

一、mysql索引分类

InnoDB存储引擎支持以下几种常见的索引：

B+树索引
就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。分为聚集索引和辅助索引(非聚集索引)
全文索引
即fulltext,是提高全文搜素速度的一种索引结构
哈希索引
InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

二、mysql索引使用

这里主要介绍聚集索引、辅助索引、联合索引和覆盖索引(非聚集索引)的使用.

1. 聚集索引

(1) 定义

聚集索引表记录的排列顺序和索引的排列顺序一致（以InnoDB聚集索引的主键索引来说，叶子节点中存储的就是行数据，行数据在物理储器中的真实地址就是按照主键索引树形成的顺序进行排列的）

(2) 特点

每张表只能拥有一个聚集索引。
聚集索引的叶子节点称为数据页，因此在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

(3) 结构示意图

//创建表
CREATE TABLE t (
a INT NOT NULL,
b VARCHAR(8000),
c INT NOT NULL,
PRIMARY KEY(a),
KEY idx_c(c)
) ENGINE=INNODB;

//插入测试数据
INSERT INTO t SELECT 1,REPEAT('a',7000),-1;
INSERT INTO t SELECT 2,REPEAT('a',7000),-2;
INSERT INTO t SELECT 3,REPEAT('a',7000),-3;
INSERT INTO t SELECT 4,REPEAT('a',7000),-4;

数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。因此这棵聚集索引树的构造大致如图5-14所示。
在这里插入图片描述
注意:许多数据库的文档会这样告诉读者：聚集索引按照顺序物理地存储数据。如果看图5-14，可能也会有这样的感觉。但是试想一下，如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

(4) 聚集索引使用

对于上面的测试表t：

//表结构信息
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | a           | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | idx_c    |            1 | c           | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show create table t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(8000) DEFAULT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

//对主键(聚集索引)进行order by排序,但是在实际过程中并没有进行所谓的filesort操作.
mysql> explain select * from t order by a limit 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+

//对主键a进行范围查找
mysql> explain select * from t where a>1 and a<3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
mysql> explain select count(*) from t where a>1 and a<3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

2. 辅助索引(非聚集索引)

(1) 定义

对于辅助索引（Secondary Index，也称非聚集索引），非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致（在逻辑上数据是按顺序排存放的，但是物理上在真实的存储器中是散列存放的）

在这里插入图片描述

(2) 特点

叶子节点并不包含行记录的全部数据,叶子节点除了包含键值以外,还包括相应行数据的聚集索引键。
可以建立多个辅助索引
可能引起离散读的问题,但是一般的数据库都通过实现预读（read ahead）技术来避免多次的离散读操作.

(3) 结构示意图

//表结构
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | a           | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | idx_c    |            1 | c           | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show create table t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(8000) DEFAULT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

//插入测试数据
INSERT INTO t SELECT 1,REPEAT('a',7000),-1;
INSERT INTO t SELECT 2,REPEAT('a',7000),-2;
INSERT INTO t SELECT 3,REPEAT('a',7000),-3;
INSERT INTO t SELECT 4,REPEAT('a',7000),-4;

UPDATE t SET c=0-a;

mysql> select a,c from t;
+---+----+
| a | c  |
+---+----+
| 4 | -4 |
| 3 | -3 |
| 2 | -2 |
| 1 | -1 |
+---+----+

在这里插入图片描述

(4) 使用

mysql>  explain select * from t where c=-3 ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | idx_c         | idx_c | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

mysql>  explain select * from t order by c limit 2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_c | 4       | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------+

mysql>  explain select * from t where c>-3 and c<-1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_c         | idx_c | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+

mysql>  explain select count(*) from t where c>-3 and c<-1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_c         | idx_c | 4       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+

3. 联合索引

(1) 定义

联合索引是指对表上的多个列进行索引。

(2) 特点

可以对多个列进行建立联合索引,加快搜索速度
已经对第二个键值进行了排序处理。例如，在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了。

(2) 使用

对形如(a,b)的联合索引，使用类似以下查询是免排序的:
SELECT…FROM TABLE WHERE a=xxx ORDER BY b

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | idx_a_b  |            1 | a           | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | idx_a_b  |            2 | b           | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> explain select * from t1 where a=3 order by b;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a_b       | idx_a_b | 4       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+

对形如(a,b,c)的联合索引:(a,b) (a,b,c)免排序 (a,c) (b,c)均不能:
可以:
SELECT…FROM TABLE WHERE a=xxx ORDER BY b
SELECT…FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c
不可以:
SELECT…FROM TABLE WHERE a=xxx ORDER BY c
SELECT…FROM TABLE WHERE b=xxx ORDER BY c

//1 表信息
mysql> desc buy_log;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| userid   | int(10) unsigned | NO   | MUL | NULL    |       |
| nameid   | int(11)          | YES  |     | NULL    |       |
| buy_date | date             | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+

mysql> show create table buy_log\G;
*************************** 1. row ***************************
       Table: buy_log
Create Table: CREATE TABLE `buy_log` (
  `userid` int(10) unsigned NOT NULL,
  `nameid` int(11) DEFAULT NULL,
  `buy_date` date DEFAULT NULL,
  KEY `userid` (`userid`),
  KEY `idx_u_n_b` (`userid`,`nameid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

//2 测试数据
INSERT INTO buy_log VALUES(1,1,'2009-01-01');
INSERT INTO buy_log VALUES(2,2,'2009-01-01');
INSERT INTO buy_log VALUES(3,3,'2009-01-01');
INSERT INTO buy_log VALUES(1,1,'2009-02-01');
INSERT INTO buy_log VALUES(3,3,'2009-02-01');
INSERT INTO buy_log VALUES(1,1,'2009-03-01');
INSERT INTO buy_log VALUES(1,1,'2009-04-01');

//测试sql
mysql> explain SELECT * FROM buy_log  WHERE userid=1 ORDER BY nameid DESC LIMIT 3;
+----+-------------+---------+------------+------+------------------+-----------+---------+-------+------+----------+--------------------------+
| id | select_type | table   | partitions | type | possible_keys    | key       | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+---------+------------+------+------------------+-----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | buy_log | NULL       | ref  | userid,idx_u_n_b | idx_u_n_b | 4       | const |    4 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+------+------------------+-----------+---------+-------+------+----------+--------------------------+

mysql> explain SELECT * FROM buy_log  WHERE userid=1 and  nameid=1 ORDER BY buy_date DESC LIMIT 3;
+----+-------------+---------+------------+------+------------------+-----------+---------+-------------+------+----------+--------------------------+
| id | select_type | table   | partitions | type | possible_keys    | key       | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+---------+------------+------+------------------+-----------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | buy_log | NULL       | ref  | userid,idx_u_n_b | idx_u_n_b | 9       | const,const |    4 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+------+------------------+-----------+---------+-------------+------+----------+--------------------------+

mysql> explain SELECT * FROM buy_log  WHERE userid=1 ORDER BY buy_date DESC LIMIT 3;
+----+-------------+---------+------------+------+------------------+-----------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys    | key       | key_len | ref   | rows | filtered | Extra                                    |
+----+-------------+---------+------------+------+------------------+-----------+---------+-------+------+----------+------------------------------------------+
|  1 | SIMPLE      | buy_log | NULL       | ref  | userid,idx_u_n_b | idx_u_n_b | 4       | const |    4 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+---------+------------+------+------------------+-----------+---------+-------+------+----------+------------------------------------------+

mysql> explain SELECT * FROM buy_log  WHERE nameid=1 ORDER BY buy_date DESC LIMIT 3;
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | buy_log | NULL       | index | NULL          | idx_u_n_b | 13      | NULL |    7 |    14.29 | Using where; Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+

4. 覆盖索引

(1) 定义

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

(2) 特点

辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
对某些统计问题而言的,比如:SELECT COUNT(*) FROM tablename;InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作。

(3) 使用

//1 表信息
mysql> desc buy_log;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| userid   | int(10) unsigned | NO   | MUL | NULL    |       |
| nameid   | int(11)          | YES  |     | NULL    |       |
| buy_date | date             | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+

mysql> show create table buy_log\G;
*************************** 1. row ***************************
       Table: buy_log
Create Table: CREATE TABLE `buy_log` (
  `userid` int(10) unsigned NOT NULL,
  `nameid` int(11) DEFAULT NULL,
  `buy_date` date DEFAULT NULL,
  KEY `userid` (`userid`),
  KEY `idx_u_n_b` (`userid`,`nameid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

//2 测试数据
INSERT INTO buy_log VALUES(1,1,'2009-01-01');
INSERT INTO buy_log VALUES(2,2,'2009-01-01');
INSERT INTO buy_log VALUES(3,3,'2009-01-01');
INSERT INTO buy_log VALUES(1,1,'2009-02-01');
INSERT INTO buy_log VALUES(3,3,'2009-02-01');
INSERT INTO buy_log VALUES(1,1,'2009-03-01');
INSERT INTO buy_log VALUES(1,1,'2009-04-01');

//仅使用一次辅助联合索引来完成查询
mysql> explain SELECT userid FROM buy_log  WHERE buy_date>='2009-01-01'AND buy_date<='2009-02-01';
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | buy_log | NULL       | index | NULL          | idx_u_n_b | 13      | NULL |    7 |    14.29 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+


//InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择
mysql> explain SELECT COUNT(*) FROM buy_log;
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | buy_log | NULL       | index | NULL          | userid | 4       | NULL |    7 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-------------+

mysql> explain SELECT COUNT(*)  FROM buy_log  WHEREbuy_date>='2009-01-01'AND buy_date<='2009-02-01';
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | buy_log | NULL       | index | NULL          | idx_u_n_b | 13      | NULL |    7 |    14.29 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

//测试覆盖索引没有的字段
mysql> alter table buy_log drop index idx_u_n_b;
mysql> alter table buy_log add index idx_n_b(nameid,buy_date);
mysql> show index from buy_log;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buy_log |          1 | userid   |            1 | userid      | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| buy_log |          1 | idx_n_b  |            1 | nameid      | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| buy_log |          1 | idx_n_b  |            2 | buy_date    | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> show create table buy_log\G;
*************************** 1. row ***************************
       Table: buy_log
Create Table: CREATE TABLE `buy_log` (
  `userid` int(10) unsigned NOT NULL,
  `nameid` int(11) DEFAULT NULL,
  `buy_date` date DEFAULT NULL,
  KEY `userid` (`userid`),
  KEY `idx_n_b` (`nameid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

//使用覆盖索引
mysql> explain SELECT buy_date  FROM buy_log;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | buy_log | NULL       | index | NULL          | idx_n_b | 9       | NULL |    7 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

//全表扫描
mysql> explain SELECT *  FROM buy_log;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | buy_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

注意:在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。