MySQL何时使用索引探究

大家已经知道，索引的核心价值是快速找到特定数据，那MySQL具体在哪些操作中会用到索引呢？我们一起来看看。

• 查找与WHERE条件匹配的行时

root@database-one 14:31:  [gftest]> select count(*) from emp_copy;
+----------+
| count(*) |
+----------+
|    20480 |
+----------+
1 row in set (0.01 sec)

root@database-one 14:32:  [gftest]> show index from emp_copy \G
*************************** 1. row ***************************
        Table: emp_copy
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: eno
    Collation: A
  Cardinality: 20864
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: emp_copy
   Non_unique: 1
     Key_name: idx_empcopy_ename
 Seq_in_index: 1
  Column_name: ename
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.01 sec)

root@database-one 14:32:  [gftest]> explain select * from emp_copy where ename='刘杰';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp_copy | NULL       | ref  | idx_empcopy_ename | idx_empcopy_ename | 33      | const | 4096 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

emp_copy表有2万多行数据，eno、ename列上有索引，当查找“刘杰”的信息时，MySQL就通过ename列上的索引去快速匹配。

• 排除某些行时

root@database-one 14:35:  [gftest]> explain select * from emp_copy where ename='刘杰' and sal>8000;
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | ref  | idx_empcopy_ename | idx_empcopy_ename | 33      | const | 4096 |    33.33 | Using where |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 14:35:  [gftest]> explain select * from emp_copy where ename<>'刘杰' and sal>8000;
+----+-------------+----------+------------+------+-------------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys     | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | idx_empcopy_ename | NULL | NULL    | NULL | 20864 |    16.67 | Using where |
+----+-------------+----------+------------+------+-------------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 14:36:  [gftest]> explain select * from emp_copy where eno<>12  and sal>8000;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 10441 |    33.33 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 14:37:  [gftest]> explain select * from emp_copy where eno<>12 and ename<>'刘杰' and sal>8000;
+----+-------------+----------+------------+-------+---------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys             | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | range | PRIMARY,idx_empcopy_ename | PRIMARY | 4       | NULL | 10441 |    16.67 | Using where |
+----+-------------+----------+------------+-------+---------------------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 14:39:  [gftest]> explain select * from emp_copy where eno<>12 and ename='刘杰' and sal>8000;
+----+-------------+----------+------------+------+---------------------------+-------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table    | partitions | type | possible_keys             | key               | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+----------+------------+------+---------------------------+-------------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | emp_copy | NULL       | ref  | PRIMARY,idx_empcopy_ename | idx_empcopy_ename | 33      | const | 4172 |    16.68 | Using index condition; Using where |
+----+-------------+----------+------------+------+---------------------------+-------------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

如果可以使用的索引有多个，MySQL通常使用查找行数最小的索引。

• 针对多列索引，即组合索引，使用最左边的前缀列查询时均会使用索引

例如，在表上有基于（col1、col2、col3）三列的索引，则以(col1)、(col1,col2)和(col1,col2,col3)作为条件搜索时都能使用上索引。

root@database-one 14:54:  [gftest]> create index idx_empcopy_3col on emp_copy(age,sal,hiredate);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@database-one 14:56:  [gftest]> explain select * from emp_copy where age>25;
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys    | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | idx_empcopy_3col | NULL | NULL    | NULL | 20864 |    50.00 | Using where |
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

root@database-one 14:56:  [gftest]> explain select * from emp_copy where age>25 and sal>8000;
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys    | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | idx_empcopy_3col | NULL | NULL    | NULL | 20864 |    16.66 | Using where |
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 14:56:  [gftest]> explain select * from emp_copy where age>25 and sal>8000 and hiredate>date_add(now(), interval 1 year);
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys    | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | idx_empcopy_3col | NULL | NULL    | NULL | 20864 |     5.55 | Using where |
+----+-------------+----------+------------+------+------------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果where条件中不包含多列索引最左边的列，则无法使用索引。

root@database-one 15:00:  [gftest]> explain select * from emp_copy where sal>8000;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20864 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 15:01:  [gftest]> explain select * from emp_copy where hiredate>date_add(now(), interval 1 year);
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20864 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 15:01:  [gftest]> explain select * from emp_copy where sal>8000 and hiredate>date_add(now(), interval 1 year);
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20864 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 关联其它表时

root@database-one 15:17:  [gftest]> create table dept(deptno int(2) primary key,deptname varchar(20),adress varchar(30)) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

root@database-one 15:17:  [gftest]> insert into dept values(10,'财务','北京'),(20,'客服','西安'),(30,'技术','深圳');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@database-one 15:17:  [gftest]> explain select * from emp_copy e join dept d on e.deptno=d.deptno where sal>8000;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |     3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20864 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

root@database-one 15:18:  [gftest]> create index idx_empcopy_deptno on emp_copy(deptno);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@database-one 15:18:  [gftest]> explain select * from emp_copy e join dept d on e.deptno=d.deptno where sal>8000;
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys      | key                | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY            | NULL               | NULL    | NULL            |    3 |   100.00 | NULL        |
|  1 | SIMPLE      | e     | NULL       | ref  | idx_empcopy_deptno | idx_empcopy_deptno | 5       | gftest.d.deptno | 6954 |    33.33 | Using where |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-----------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

• group by或order by时

root@database-one 15:38:  [gftest]> explain select deptno,sum(sal),avg(sal) from emp_copy group by deptno;
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+-------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys      | key                | key_len | ref  | rows  | filtered | Extra |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | emp_copy | NULL       | index | idx_empcopy_deptno | idx_empcopy_deptno | 5       | NULL | 20864 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@database-one 15:43:  [gftest]> explain select * from emp_copy order by age,sal,hiredate;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20864 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 15:45:  [gftest]> explain select age,sal,hiredate from emp_copy order by age,sal,hiredate;
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp_copy | NULL       | index | NULL          | idx_empcopy_3col | 15      | NULL | 20864 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@database-one 15:47:  [gftest]> explain select ename,age,sal,hiredate from emp_copy order by age,sal,hiredate;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | emp_copy | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20864 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

可以看到group by或order by时，MySQL并不是列上有索引就必定使用，还跟select要返回的列有关系。当select要返回的列都包含在索引中时，MySQL会优先使用索引，这样可以不必回表，获得更好的效能。

最后，还要注意，索引对小表价值不大，因为直接读取整个表可能比通过索引检索更快。索引对要读取表中大部分甚至全部数据的SQL价值不大，因为当SQL要访问表中大部分行时，按顺序读取比通过索引读取更快，顺序读取可以使磁盘查找最小化。