摘要：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要访问表中大部分行时，按顺序读取比通过索引读取更快，顺序读取可以使磁盘查找最小化。
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。