您当前的位置：首页 > 计算机 > 软件应用 > 数据库 > MySQL

MySQL从零到有24：构建高性能索引（策略篇）

时间：10-09来源：作者：点击数：

学习如果构建高性能的索引之前，我们先来了解下之前的知识，以下两篇是基础原理，了解之后，对面后续索引构建的原则和优化方法会有更清晰的理解：

MySQL从零到有22：索引的介绍和原理分析

MySQL从零到有23：MySQL索引实现和使用

我们编写索引的目的是什么？就是使我们的sql语句执行得更加高效，更快的获取或者处理数据，这个也是建设高性能Web的必要条件。

只有我们深刻理解了索引的原理和执行过程，才能知道怎么恰当地使用索引，以及怎么达到最优的查询。

知识回顾

innodb是MySQL默认的存储引擎，使用范围也较广，我们就以innodb存储引擎为例来进行下面方案的说明。

b+树存储结构

MySQL采用b+树的方式存储索引信息。b+树图例如下：

b+树结构特点

1、叶子节点存储关键字（索引字段的值）信息及对应的值（完整记录）。

2、其他非叶子节点只存储键值信息及子节点的链指针

3、每个叶子节点相当于MySQL中的一页，同层级的叶子节点以双向链表的形式相连

4、每个节点（页）中存储了多条记录，记录之间用单链表的形式连接组成了一条有序的链表，顺序是按照索引字段排序的

5、b+树中检索数据时：每次检索都是从根节点开始，一直需要搜索到叶子节点，如图中所示.

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读取一条记录的时候，并不是将这个记录本身从磁盘读取出来，而是以页为单位，将整个也加载到内存中，一个页中可能有很多记录，然后在内存中对页进行检索。

在innodb中，每个页的大小默认是16kb。

Mysql中索引包含

聚集索引（主键索引）

每个表一定会有一个聚集索引（即主键索引），而表中的数据以b+树的方式存储，b+树叶子节点中的key为主键值，data为完整记录的信息，非叶子节点存储的是主键的值。

通过聚集索引检索数据只需要按照b+树的搜索过程，即可以检索到对应的记录。

非聚集索引（辅助索引）

每个表可以有多个非聚集索引，b+树结构，叶子节点的key为索引字段字段的值，data为主键的值，非叶子节点只存储索引字段的值。

通过非聚集索引检索记录的时候，需要2次操作，先在非聚集索引中检索出主键，然后再到聚集索引中检索出主键对应的记录，这个过程叫做回表，比聚集索引多了一次操作。

保证索引有效使用

有效索引是指我们的某些字段上建立了索引，并且在对该字段进行检索过程中，能够快速定位到目标数据所在的页，有效的降低页的io操作，而不是去扫描所有的数据页，这样才算有效的利用索引，

保证了检索是有效使用索引的（俗称检索走了索引）。但如果检索过程不能够确定数据在某些页中，而进行了大量的数据页扫描，我们则认为这种情况下索引对查询是无效的。

索引的优点

1、索引大大减少了服务器需要扫描的数据量

2、索引可以帮助服务器避免排序和临时表

3、索引可以将随机I/O变为顺序I/O

b+树中数据执行过程

唯一记录定位

上图中所有的数据都是唯一的，查询306的记录，过程如下：

1、将Disk1页加载到内存

2、在内存中采用二分法查找，可以确定306位于[300,350)中间，所以我们需要去加载100关联Disk5

3、将Disk5加载到内存中，采用二分法找到306的记录

查询所有同值记录

上图中306有多条记录，我们查询306的所有记录步骤如下：

1、将Disk1页加载到内存

2、在内存中采用二分法查找，可以确定306位于[300,350)中间，所以我们需要去加载100关联Disk5

3、将Disk5加载到内存中，采用二分法找到第一个小于306的记录，即300，从300这个点按照链表向后访问，找到所有的306记录，遇到大于306的停止

范围查找

数据如上图，查询[255,355]所有记录，因为Page和Page之间是双向链表并且是ASC顺序结构，页内部的数据是单项ASC顺序链表结构，所以只用找到范围的起始值所在的位置，然后通过依靠链表访问两个位置之间所有的数据即可，

步骤如下：

1、将PDisk1页加载到内存

2、内存中采用二分法找到255位于200关联的Disk4中，355位于Disk6页中

3、将Disk4加载到内存中，采用二分法找到第一个255的记录，然后继续完后扫描，Disk4扫描完成之后，通过链表结构继续向后访问Disk5中的300、306，当Disk5访问完毕之后，通过Disk5的nextpage指针访问下一页Disk6中，继续扫Disk6中的记录，遇到大于355的值停止。

模糊匹配

数据如上图。

检索以c字符开头的所有记录

执行步骤如下：

1、将Disk1数据加载到内存中

2、在Disk1页的记录中采用二分法找到最后一个小于等于c的值，这个值是ce，以及第一个大于c的，这个值是d，ce指向叶节点Disc4，d指向叶节点Disk6，此时可以断定以f开头的记录可能存在于[Disk4,Disk6)这个范围的页内，即Disk4、Disk5这两个页中

3、加载Disk4这个页，在内部以二分法找到第一条c开头的记录，然后以链表方式继续向后访问Disk5中的记录，即可以找到所有已c开头的数据

检索包含c字符的记录

包含一般是采用%c%来计算，但是这种写法是破坏索引的有效使用，上面的数据中，c在每个页中都存在，

我们通过Disk1页中的记录是无法判断包含c的记录会分布在哪些页中，只能加载所有的页（一次次的IO操作），并且遍历所有叶节点上的记录信息进行筛选，才可以找到包含c字符的记录。

所以如果使用了%value%这种方式，索引对查询是无效的。

遵循最左匹配原则

如下图，b+树的数据项是复合的数据结构，比如(empname,depno,job)这种（即构建一个联合索引）时，b+树是按照从左到右的顺序来建立搜索树的。

示例，当以('brand',106,'SALEMAN')这样的数据来检索的时候，b+树会优先比较empname来确定下一步的所搜方向，如果empname相同再依次比较depno和job，最后得到检索的数据。

但如果是(106,'SALEMAN')这样，没有empname的数据来的时候，b+树就不知道下一步该查哪个节点，因为empname就是第一个比较因子，必须要先根据empname来搜索才能知道下一步去哪里查询。

比如当('brand','SALEMAN')这样的数据来检索时，b+树可以用empname来指定搜索方向，但下一个字段depno的缺失，所以只能把名字等于 'brand' 的数据都找到，然后再匹配职位是SALEMAN的数据了。

这个重要特征就是索引的最左匹配原则，按照这个原则执行索引效率特别高。

我们试试在b+树上分析和举例：下图中是3个字段(depno,empname,job)的联合索引，数据以depno asc,empname asc,job asc这种排序方式存储在节点中的，排序原则： 1、索引以depno字段升序 2、depno相同时，以empname字段升序， 3、empname相同的时候，以job字段升序

第一位置

检索depno=7的记录

由于页中的记录是以depno asc,empname asc,job asc这种排序方式存储的，所以depno字段是有序的，可以通过二分法快速检索到，步骤如下：

1、将Disk1加载到内存中

2、在内存中对Disk1中的记录采用二分法找，可以确定depno=7的记录位于{7,Brand,1}和{7,dyny,1}关联的范围内，这两个所指向的页分别是 Disk2 和 Disk4。

3、加载页Disk2，在Disk2中采用二分法快速找到第一条depno=7的记录，然后通过链表向下一条及下一页开始扫描，直到在Disk4中找到第一个不满足depno=7的记录为止。

第一+第二位置

检索depno=7 and empname like 'B%'的记录

步骤跟上面是一致的，可以确定depno=1 and empname like 'B%'的记录位于{7,Band,1}和{7,Bec,1}关联的范围内，查找过程和depno=7查找步骤类似。

第二位置

检索empname like 'C%'的记录

这种情况通过Disk1页中的记录，无法判断empname like 'C%' 的记录在哪些页中的，只能逐个加载索引树的页，对所有记录进行遍历，然后进行过滤，此时索引无效。

第三位置

检索job=8的记录

这种情况和查询 empname like 'C%' 也一样，也只能扫描所有叶子节点，索引也无效。

第二+第三位置

empname和job一起查

这种原理跟前面两个一致，无法使用索引，只能对所有数据进行扫描。

第一+第三位置

按照(depno,job)字段顺序检索

这种仅使用到索引中的depno字段了，通过depnon确定范围之后，加载所有depno下的数据，再对job条件进行过滤。如果的depno查出来的数据基数巨大，也会慢。

比如我们的测试数据中 depno=16 的数据有50W左右，也是比较多的。

停止匹配的条件

检索depno=1 and empname>'' and job=1的记录

根据上面的图，这种检索方式只能先确定depno=1 and empname>''所在页的范围，然后对这个范围的所有页进行遍历，job字段在这个查询的过程中，是无法确定数据在哪些页的，此时我们说job是不走索引的，只有depno、empname能够有效的确定索引页的范围。

类似这种的还有>、<、between and、like，多字段联合索引的情况下，mysql会一直向右匹配直到遇到范围查询(>、<、between and、like)就停止匹配。

通过上面的示例可以知道，遵循最左匹配原则才会真正有效利用索引。

索引区分度

衡量策略

假设我们有两个有序的数组，都包含10条记录

[a,b,c,d,e,f,g,h,i,j,k] 和 [a,a,a,a,a,b,b,b,b,b]

如果要检索值为b的所有记录，哪个效率会高一点？

使用二分法查找执行步骤如下：

1、使用二分法找到最后一个小于b（就是上面数组中标红色的a）的记录

2、沿着这条记录向后扫描，和b对比，直到遇到第一个大于b的值结束，或者直到扫描完所有数据。

采用上面的方法找到b的记录，第一个数组中更快的一些。因为第二个数组中含有b的基数更大，需要访问和比较的次数也更多一点。

这种区分是有一种计算公式来衡量的：

selecttivity = count(distinct c_name)/count(*)

当索引区分度越高，检索速度越快，索引区分度低，则说明重复的数据比较多，检索的时候需要访问更多的记录才能够找到所有目标数据。

当索引区分度小到无限趋近于0的时候，基本等同于全表扫描了，此时检索效率肯定是慢的。

第一个数组没有重复数据，索引区分度为1，第二个区分度为0.2，所以第一个检索效率更高。

我们创建索引的时候，尽量选择区分度高的列作为索引。

数据对比

我们来看看emp 表中的两个字段，empname 和 depno 字段，

empname基本不重复，所以每个empname只有一条数据；而 500W的数据大约分配了10个部门，所以每个的depno下有约50W的数据。

mysql> select count(distinct empname)/count(*),count(distinct depno)/count(*) from emp;
+----------------------------------+--------------------------------+
| count(distinct empname)/count(*) | count(distinct depno)/count(*) |
+----------------------------------+--------------------------------+
| 0.1713                           | 0.0000                         |
+----------------------------------+--------------------------------+
1 row in set

索引区分度规则在后面很多场合会用到，还可以协助判断前缀索引的最佳匹配长度。

高性能索引策略

千万级测试数据

请参考第21篇（MySQL从零到有21（番外）：一次深夜优化亿级数据分页的奇妙经历）中模拟的千万数据，我们以这个数据为测试数据。

emp表中有500W数据我们用emp来做测试

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set

无索引的效果

我们之前在emp表上做过索引，所以先看一下这个表目前所有的索引

可以看到，目前主键字段id和depno字段上都有建立索引

mysql> desc emp;
+----------+-----------------------+------+-----+---------+----------------+
| Field    | Type                  | Null | Key | Default | Extra          |
+----------+-----------------------+------+-----+---------+----------------+
| id       | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| empno    | mediumint(8) unsigned | NO   |     | 0       |                |
| empname  | varchar(20)           | NO   |     |         |                |
| job      | varchar(9)            | NO   |     |         |                |
| mgr      | mediumint(8) unsigned | NO   |     | 0       |                |
| hiredate | datetime              | NO   |     | NULL    |                |
| sal      | decimal(7,2)          | NO   |     | NULL    |                |
| comn     | decimal(7,2)          | NO   |     | NULL    |                |
| depno    | mediumint(8) unsigned | NO   | MUL | 0       |                |
+----------+-----------------------+------+-----+---------+----------------+
9 rows in set

mysql> show index from emp;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY       |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_emp_id    |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_emp_depno |            1 | depno       | A         |          18 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

我们在没有做索引的字段上做一下查询看看，在500W数据中查询一个名叫LsHfFJA的员工，消耗 2.239S ，获取到一条id为4582071的数据。

再看看他的执行过程，扫描了4952492 条数据才找到该行数据：

mysql> explain select * from emp where empname='LsHfFJA';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set

主键检索

我们按照主键id来检索

因为在id上有聚集索引，所以检索效率很高

mysql> explain select * from emp where  id=4582071;
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | emp   | const | PRIMARY,idx_emp_id | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
1 row in set

这个速度很快，这个走的是上面介绍的唯一记录检索。

前缀索引和索引长度计算

这个我们上一篇有讨论过，需要选择适当的索引长度。

有时候需要索引很长的字符列，这会让索引变得大且慢（每个页存储的内容是有限的，如果一个页中可以存储的索引记录越多，那么查询效率就会提高，所以我们可以指定索引的字段长度）。

通常可以索引开始的部分字符，这样可以大大节约索引空间（每个页），从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

MySQL官方：一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

前缀索引的长度的判断方法

select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;

SELECT
    count(DISTINCT LEFT(cname, 3)) / count(*) AS sel3,
    count(DISTINCT LEFT(cname, 4)) / count(*) AS sel4,
    count(DISTINCT LEFT(cname, 5)) / count(*) AS sel5,
    count(DISTINCT LEFT(cname, 6)) / count(*) AS sel6,
    count(DISTINCT LEFT(cname, 7)) / count(*) AS sel7
FROM
    tname

添加前缀索引

ALTER TABLE tname ADD KEY (cname[(lenth)]);

测试emp_name最合适的长度，因为empname的长度基本维持在6个字符左右，少数量超过6长度，所以指定empname索引长度时6是最建议的

mysql>  SELECT
     count(DISTINCT LEFT(empname, 3)) / count(*) AS sel3,
     count(DISTINCT LEFT(empname, 4)) / count(*) AS sel4,
     count(DISTINCT LEFT(empname, 5)) / count(*) AS sel5,
     count(DISTINCT LEFT(empname, 6)) / count(*) AS sel6,
     count(DISTINCT LEFT(empname, 7)) / count(*) AS sel7
 FROM
     emp;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+
1 row in set

我们可以使用不同的长度来测试检索效率

当长度为2的时候，匹配度低于 0.0012，检索效率自然比较慢

mysql> create index idx_emp_empname on emp(empname(2));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where empname='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (1.793 sec)

当长度为6的时候，检索效率就比较高

mysql> create index idx_emp_empname on emp(empname(6));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where empname='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  （0.003 sec）

联合索引的使用

当我们需要在多个字段上面做索引的时候，经常的做法是每个字段都建一个索引，这种策略一般是不建议的，优先的做法是优化索引列的顺序，或者创建一个全覆盖的索引。

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL的“索引合并”（index merge）策略一定程序上可以使用表上的多个单列索引来定位指定的行。索引合并策略能够同时使用多个单列索引进行扫描，并将结果进行合并。那么什么时候选择联合索引呢？

1、当出现服务器对多个索引做相交操作时（通常有多个and条件），更好的操作是创建一个包含所有相关列的多列索引，而不是多个独立的单列索引。

2、当服务器需要对多个索引做联合操作时（通常有多个or条件），通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

这两种情况下更好的操作是创建一个包含所有相关列的多列索引，而不是多个独立的单列索引。

联合索引列的顺序策略

在建设联合索引中，我们经常遇到的困惑就是索引的顺序问题。正确的索引应该依赖使用该索引的查询，并能同时考虑更好的满足排序和分组的需求。

联合索引意味着他按照我们上面描述的最左匹配原则来进行搜素。

当不考虑分组和排序的时候，我们经常是将选择性高的内容放在前面，以下面的查询为例：

select * from emp where  empname like 'LsHfFJ%' and depno=106;

是创建(empname,depno）顺序的索引还是(depno,empname）顺序的索引？我们应该是找一个选择性高（也就是匹配内容少）的字段放在前面。

mysql> select sum(empname like 'LsHfFJ%'),sum(depno=106) from emp;
+-----------------------------+----------------+
| sum(empname like 'LsHfFJ%') | sum(depno=106) |
+-----------------------------+----------------+
| 7                           | 500194         |
+-----------------------------+----------------+
1 row in set

可以看出 empname字段的选择性更高一点，对应条件下的数据值 empname like 'LsHfFJ%' 下的数量会少很多。所以答案是将他作为索引的第一列。

但是这样也有个问题，索引的设计是根据现有的数据执行情况进行处理的，可能对其他条件的查询不公平，也可能随着数据的膨胀或者收缩，字段的选择性发生了变化。

另外一种经验做法是考虑全局基数和选择性，而不是某个具体的查询：

mysql> select count(distinct empname)/count(*) as empname_selecttivity,count(distinct depno)/count(*) as depno_selecttivity,count(*) from emp;
+----------------------+--------------------+----------+
| empname_selecttivity | depno_selecttivity | count(*) |
+----------------------+--------------------+----------+
| 0.1713               | 0.0000             |  5000000 |
+----------------------+--------------------+----------+
1 row in set

empname字段的选择性更高一点，所以答案是将他作为索引的第一列。

between and范围检索

mysql> select count(*) from emp where id between 18 and 28;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set  (0.001 sec)

速度也很快，id上有主键索引，这个采用的上面介绍的范围查找可以快速定位目标数据。

但是如果范围太大，跨度的page也太多，速度也会比较慢，如下：

mysql> select count(*) from emp where id between 1 and 4990000;
+----------+
| count(*) |
+----------+
|  4990000 |
+----------+
1 row in set  (0.878 sec)

上面id的值跨度太大，1所在的页和499万所在页中间有很多页需要读取，所以比较慢。

所以使用between and的时候，区间跨度不要太大。

in的检索

in方式检索数据，我们还是经常用的。

平时我们做项目的时候，建议少用表连接，比如电商中需要查询订单的信息和订单中商品的名称，可以先查询查询订单表，然后订单表中取出商品的id列表，采用in的方式到商品表检索商品信息，由于商品id是商品表的主键，所以检索速度还是比较快的。

通过id在400万数据中检索100条数据，看看效果：

mysql>  select * from emp a where
a.id in (800000, 800001, 800002, 800003, 800004, 800005, 800006, 800007, 800008, 800009, 800010, 800011, 800012, 800013,
 800014, 800015, 800016, 800017, 800018, 800019, 800020, 800021, 800022, 800023, 800024, 800025, 800026, 800027, 800028,
800029, 800030, 800031, 800032, 800033, 800034, 800035, 800036, 800037, 800038, 800039, 800040, 800041, 800042, 800043, 800044,
800045, 800046, 800047, 800048, 800049, 800050, 800051, 800052, 800053, 800054, 800055, 800056, 800057, 800058, 800059, 800060,
800061, 800062, 800063, 800064, 800065, 800066, 800067, 800068, 800069, 800070, 800071, 800072, 800073, 800074, 800075, 800076,
800077, 800078, 800079, 800080, 800081, 800082, 800083, 800084, 800085, 800086, 800087, 800088, 800089, 800090, 800091, 800092,
800093, 800094, 800095, 800096, 800097, 800098, 800099);
+--------+--------+---------+---------+-----+---------------------+------+------+-------+
| id     | empno  | empname | job     | mgr | hiredate            | sal  | comn | depno |
+--------+--------+---------+---------+-----+---------------------+------+------+-------+
| 800000 | 800000 | qVFqPY  | SALEMAN |   1 | 2021-01-23 16:43:02 | 2000 | 400  |   105 |
| 800001 | 800001 | KVzJXL  | SALEMAN |   1 | 2021-01-23 16:43:02 | 2000 | 400  |   107 |
| 800002 | 800002 | vWvpkj  | SALEMAN |   1 | 2021-01-23 16:43:02 | 2000 | 400  |   102 |
............
| 800099 | 800099 | roxtAx  | SALEMAN |   1 | 2021-01-23 16:43:02 | 2000 | 400  |   107 |
+--------+--------+---------+---------+-----+---------------------+------+------+-------+
100 rows in set  (0.001 sec)

耗时1毫秒左右，还是相当快的。

这个相当于多个分解为多个唯一记录检索，然后将记录合并。所以这个其实也是快的，只要in里面的数据不是极端海量的即可。

多个独立索引的执行策略

根据之前我们做的索引，在empname上已经做了索引，那我们在另外一个字段depno上也做索引，看看他是怎么匹配索引的。

mysql> create index idx_emp_depno on emp(depno);
mysql> show index from emp;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY         |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_emp_id      |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_emp_depno   |            1 | depno       | A         |          18 | NULL     | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_emp_empname |            1 | empname     | A         |     1650830 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set

查询下试试：

mysql> select * from emp where empname='LsHfFJA' and depno='106';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (0.000 sec)

上面查询速度很快，empname 和 depno上各有一个索引，觉得上面走哪个索引？

大家可能觉得empname位于where第一个，所以走的是empname字段所在的索引，过程可以解释为这样：

1、走empname所在的索引找到用户名为 empname='LsHfFJA' 所对应的所有记录

2、遍历记录过滤出部门编号的值为 depno='106' 的数据。

我们看一下 empname='LsHfFJA' 检索速度，确实很快，如下：

mysql> select * from emp where empname='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (0.001 sec)

走empname索引，然后再过滤，确实可以，速度也很快，果真和where后字段顺序有关么？我们把 empname 和 depno 的顺序对调一下，如下：

mysql> select * from emp where depno='106' and empname='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (0.000 sec)

速度还是很快，这次是不是先走 depno 索引检索出数据，然后再过滤 empname 呢？我们先来看一下depno=106查询速度：

mysql> select count(*) from emp where depno='106';
+----------+
| count(*) |
+----------+
|   500194 |
+----------+
1 row in set  (0.121 sec)

看上面，查询耗时121毫秒，50万数据，如果走depno肯定是不行的。

我们使用explain来看一下：

mysql> explain select * from emp where depno='106' and empname='LsHfFJA';
+----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys                 | key             | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | emp   | ref  | idx_emp_depno,idx_emp_empname | idx_emp_empname | 22      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+------------------------------------+
1 row in set

possible_keys：列出了这个查询可能会走两个索引（idx_emp_depno、idx_emp_empname）

实际上走的却是idx_emp_empname（key列：实际走的索引）。

所以，当多个条件中有索引的时候，并且关系是and的时候，会自动匹配索引区分度高的，显然name字段重复度很低，走name查询会更快一些。

模糊查询

看两个查询，都采用了模糊查询，但是使用%开头会造成无法从页面确定扫描的位置，导致索引无效，全表扫描。

mysql> select * from emp where empname like 'LsHfFJA%';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (0.000 sec)

mysql> select * from emp where empname like '%LsHfFJA%';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (2.034 sec)

上面第一个查询可以利用到name字段上面的索引，下面的查询是无法确定需要查找的值所在的范围的，只能全表扫描，无法利用索引，所以速度比较慢，这个过程上面有说过。

关于回表

当需要查询的数据在索引树中不存在的时候，需要再次到聚集索引中去获取，这个过程叫做回表，如查询：

mysql>  select empname,job,hiredate,sal from emp where empname like 'LsHfFJA%';
+---------+---------+---------------------+------+
| empname | job     | hiredate            | sal  |
+---------+---------+---------------------+------+
| LsHfFJA | SALEMAN | 2021-01-23 16:46:03 | 2000 |
+---------+---------+---------------------+------+
1 row in set

上面查询 empname、job、hiredate、sal，由于empname列所在的索引中只有empname、id 两个列的值，不包含job、hiredate、sal，所以上面过程如下：

1、走 empname 索引检索 LsHfFJA 对应的记录，取出id为 4582071 的数据。

2、在主键索引中检索出 id=4582071 的记录，获取其他字段的值

索引覆盖

查询中采用的索引树中包含了查询所需要的所有字段的值，不需要再去聚集索引检索数据，这种叫索引覆盖。

我们来看一个查询：

mysql> select id,empname from emp where empname='LsHfFJA';
+---------+---------+
| id      | empname |
+---------+---------+
| 4582071 | LsHfFJA |
+---------+---------+
1 row in set  (0.000 sec)

name对应idx_emp_empname索引，id为主键，所以idx_emp_empname索引树叶子节点中包含了empname、id的值，这个查询只用走idx_emp_empname这一个索引就可以了，如果select后面使用*，

还需要一次回表获取其他的值，比如上面的 job、hiredate、sal 字段等。

所以，获取数据时应需而取，写sql的时候，尽量避免使用*，*可能会多一次回表操作，需要看一下是否可以使用索引覆盖来实现，效率更高一些。

ICP，避免回表

索引下推,简称ICP(Index Condition Pushdown) ,是MySQL 5.6中新特性，可以在存储引擎层使用索引过滤数据的一种优化方式。

ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

我们举个例子来看一看：

mysql> select count(id) from emp where empname ='LsHfFJA' and  sal=2000;
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set

执行步骤如下：

1、走empname所在的索引检索出以 empname ='LsHfFJ' 的记录，并得到记录id

2、利用id去主键索引中查询出这条记录Record1

3、判断Record1中的sal为2000的值，然后重复上面的操作，直到找到所有记录为止。

上面的过程中需要走empname所在的索引以及需要回表操作。

但是如果采用ICP的方式，可以创建一个(empname,sal)的联合索引，检索步骤如下：

1、走(empname,sal)索引检索出以 empname ='LsHfFJ' 的第一条记录，可以得到(empname,sal,id)，我们记为Record1。

2、判断 Record1.sal=2000 的值，并重复上面的操作，直到找到所有记录为止

这个执行步骤免去回表操作，通过索引的数据就可以完成整个操作，效率会好很多。

类型转换可能导致索引无效

mysql> select * from emp where empname ='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set

从这个数据中我们可以看出 empname为字符串类型的，depno为数组类型的，这两个上面都有独立的索引，我们来看两个语句：

mysql> select * from emp where empname =1;
+---------+---------+---------+----------+-----+---------------------+-------+------+-------+
| id      | empno   | empname | job      | mgr | hiredate            | sal   | comn | depno |
+---------+---------+---------+----------+-----+---------------------+-------+------+-------+
| 5000001 | 5000099 | 1       | engineer |   1 | 2021-03-05 19:09:28 | 22500 | 400  |   106 |
+---------+---------+---------+----------+-----+---------------------+-------+------+-------+
1 row in set  (2.645 sec)

mysql> select count(*) from emp where depno ='106';
+----------+
| count(*) |
+----------+
|   500195 |
+----------+
1 row in set  (0.000 sec)

mysql> select count(*) from emp where depno =106;
+----------+
| count(*) |
+----------+
|   500195 |
+----------+
1 row in set  (0.001 sec)

1、第一个查询，即便是在empname上建了索引，耗时依旧达到2s多。那是因为empname是字符串类型，字符串和数字比较的时候，会将字符串强制转换为数字，然后进行比较，所以整个查询变成了全表扫描，

一个个抽出每条数据，将empname转换为数字和1进行比较。

2、第二个和第三个查询，depno是int类型的，两个查询效率一致，都是正常利用索引快速检索。这是因为数值类型的字段，查询匹配的值无论是字符串还是数值都会走索引。

函数导致索引无效

当我们不恰当的使用索引所对应的字段的时候，可能会导致索引失效，比如查询的过程没有保证独立的列，

这个独立的列是指索引对应的列不能作用在函数中。如下：

mysql> select * from emp  where id = 4990000;
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (0.002 sec)

mysql> select * from emp  where ABS(id) = 4990001;
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4990001 | 4990001 | fXtdiH  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   107 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (2.007 sec)

耗时分别是 0.002、2.007，使用explain分析后发现作用在函数的时候没有走索引，变成全表扫描：

mysql> explain select * from emp  where id = 4990000;
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | emp   | const | PRIMARY,idx_emp_id | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain select * from emp  where ABS(id) = 4990001;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set

表达式导致索引无效

跟上面的类型类似，索引对应的列也不能作用于表达式中：

mysql> select * from emp  where id = 4990000;
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (0.002 sec)

mysql> select * from emp  where id+1 = 4990001;
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (1.762 sec)

耗时分别是 0.002、1.762，使用explain分析后发现作用在表达式的时候没有走索引，变成全表扫描：

mysql> explain select * from emp  where id = 4990000;
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | emp   | const | PRIMARY,idx_emp_id | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain select * from emp  where id+1 = 4990001;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set

索引覆盖排序字段

我们先来看emp表的一个查询，我们经常会这么查员工的信息 emp:

mysql>  select * from emp where depno = 106 limit 10;
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
| id  | empno | empname | job     | mgr | hiredate            | sal  | comn | depno |
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
|   8 |     8 | LsHfFJ  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  15 |    15 | WxxUeO  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  40 |    40 | dZPYxu  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  43 |    43 | yyWlyF  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  65 |    65 | xRSLaX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  70 |    70 | dgwNzl  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  91 |    91 | lpTzQG  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 101 |   101 | BeSZuX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 127 |   127 | hvHYVI  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 132 |   132 | IUyKci  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
10 rows in set  (0.017 sec)

mysql>  select * from emp where depno = 106 order by hiredate limit 10;
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
| id  | empno | empname | job     | mgr | hiredate            | sal  | comn | depno |
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
| 142 |   142 | EapywO  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 132 |   132 | IUyKci  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 127 |   127 | hvHYVI  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 101 |   101 | BeSZuX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  91 |    91 | lpTzQG  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  70 |    70 | dgwNzl  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  65 |    65 | xRSLaX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  43 |    43 | yyWlyF  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  40 |    40 | dZPYxu  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  15 |    15 | WxxUeO  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
10 rows in set  (6.411 sec)

获取部门号为106的数据根据入职时间进行排序。从上面的查询可以看出，多了一个hiredate的排序，效率差很多，我们来分析一下。

在depno上创建过索引了，所以数据检索的步骤如下：

1、走idx_emp_depno索引，找到记录的的id

2、通过id在主键索引中回表检索出整条数据（回表的过程）

3、重复上面的操作，获取所有目标记录

4、在内存中对已查出的记录按照hiredate进行排序（排序的过程）

但是当数据量非常大的时候，排序还是比较慢的，最好的办法是查询出来的数据刚好是排好序的。

回顾一下MySQL中b+树的数据结构，记录是按照索引的值排序组成的链表，如果将depno和hiredate放在一起组成联合索引(depno,hiredate)，这样通过depno检索出来的数据自然就是按照hiredate排好序的，

少了一步排序操作，效率更好。

mysql>  select * from emp where depno = 106 order by hiredate limit 10;
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
| id  | empno | empname | job     | mgr | hiredate            | sal  | comn | depno |
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
|   8 |     8 | LsHfFJ  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  15 |    15 | WxxUeO  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  40 |    40 | dZPYxu  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  43 |    43 | yyWlyF  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  65 |    65 | xRSLaX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  70 |    70 | dgwNzl  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
|  91 |    91 | lpTzQG  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 101 |   101 | BeSZuX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 127 |   127 | hvHYVI  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
| 132 |   132 | IUyKci  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
+-----+-------+---------+---------+-----+---------------------+------+------+-------+
10 rows in set  (0.001 sec)