MySQL全面瓦解24：构建高性能索引（策略篇）

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

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

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字段升序  

@H_@R_419_6940@_199@

第一位置

检索depno=7的记录

由于页中的记录是以depno 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,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,1 selecttivity = count(distinct c_name)/count(*) 当索引区分度越高，检索速度越快，索引区分度低，则说明重复的数据比较多，检索的时候需要访问更多的记录才能够找到所有目标数据。当索引区分度小到无限趋近于0的时候，基本等同于全表扫描了，此时检索效率肯定是慢的。第一个数组没有重复数据，索引区分度为1，第二个区分度为0.2，所以第一个检索效率更高。我们创建索引的时候，尽量选择区分度高的列作为索引。数据对比 我们来看看emp 表中的两个字段，empname 和 depno 字段，empname基本不重复，所以每个empname只有一条数据；而 500W的数据大约分配了10个部门，所以每个的depno下有约50W的数据。

1 MysqL> select distinct empname)count(*),1)">distinct depno)*) from emp; 2 +----------------------------------+--------------------------------+3 | *) |4 5 | 0.1713                           0.0000                         6 7 1 row in set

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

高性能索引策略千万级测试数据

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

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

1 MysqL> select count(*) from emp;2 +----------+3 | count(*) |4 +----------+5 |  5000000 |6 +----------+7 1 row in set 

无索引的效果

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

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

 1 MysqL> desc emp; 2 +----------+-----------------------+------+-----+---------+----------------+ 3 | FIEld    | Type                  | Null | Key | Default | Extra          | 4 +----------+-----------------------+------+-----+---------+----------------+ 5 | ID       | int(10) unsigned      | NO   | PRI | NulL    | auto_increment | 6 | empno    | mediumint(8) unsigned | NO   |     | 0       |                | 7 | empname  | varchar(20)           | NO   |     |         |                | 8 | job      | varchar(9)            | NO   |     |         |                | 9 | mgr      | mediumint(8) unsigned | NO   |     | 0       |                |10 | hiredate | datetime              | NO   |     | NulL    |                |11 | sal      | decimal(7,2)          | NO   |     | NulL    |                |12 | comn     | decimal(7,2)          | NO   |     | NulL    |                |13 | depno    | mediumint(8) unsigned | NO   | Mul | 0       |                |14 +----------+-----------------------+------+-----+---------+----------------+15 9 rows in set16 17 MysqL> show index from emp;18 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+19 | table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |20 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+21 | emp   |          0 | PRIMARY       |            1 | ID          | A         |     4952492 | NulL     | NulL   |      | BTREE      |         |               |22 | emp   |          1 | IDx_emp_ID    |            1 | ID          | A         |     4952492 | NulL     | NulL   |      | BTREE      |         |               |23 | emp   |          1 | IDx_emp_depno |            1 | depno       | A         |          18 | NulL     | NulL   |      | BTREE      |         |               |24 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+25 3 rows in set

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

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

1 MysqL> explain select * from emp where empname='LsHfFJA';2 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+3 | ID | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |4 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+5 |  1 | SIMPLE      | emp   | ALL  | NulL          | NulL | NulL    | NulL | 4952492 | Using where |6 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+7 1 row in set 

主键检索

我们按照主键ID来检索 

 

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

> 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       |    NulL  set

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

前缀索引和索引长度计算

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

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

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

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

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

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

1 distinct left(`c_name`,calcul_len))from t_name;

 

SELECT2     disTINCT left(cname,3)) / AS sel3,3     4))  sel4,1)">4     5))  sel5,1)">5     6))  sel6,1)">6     7))  sel77 FROM8     tname

添加前缀索引 

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

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

 1 MysqL>   2      left(empname,1)"> 3       4       5       6       7   8      emp; 9 ------+--------+--------+--------+--------+10 | sel3   | sel4   | sel5   | sel6   | sel7   11 12 0.0012 0.0076 0.0400 0.1713 13 14 set

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

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

create index IDx_emp_empname on emp(empname(2)); 2 query OK,1); Font-weight: bold">0 rows affected 3 Records: 0  Duplicates: 0  Warnings: 0 4  5 MysqLwhere empname='LsHfFJA' 6 -------+---------+---------+---------+-----+---------------------+------+------+-------+ 7 | ID      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno  8 4582071 | LsHfFJA | SALEMAN |   2021-0123 16:46:03 2000 400  106 set  (1.793 sec) 

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

6set  （0.003 sec）

联合索引的使用

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

在多个列上建立独立的单列索引大部分情况下并不能提高MysqL的查询性能。MysqL的“索引合并”（index merge）策略一定程序上可以使用表上的多个单列索引来定位指定的行。索引合并策略能够同时使用多个单列索引进行扫描，并将结果进行合并。那么什么时候选择联合索引呢？ 1、当出现服务器对多个索引做相交 *** 作时（通常有多个and条件），更好的 *** 作是创建一个包含所有相关列的多列索引，而不是多个独立的单列索引。2、当服务器需要对多个索引做联合 *** 作时（通常有多个or条件），通常需要消耗大量cpu和内存资源在算法的缓存、排序和合并 *** 作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。 这两种情况下更好的 *** 作是创建一个包含所有相关列的多列索引，而不是多个独立的单列索引。联合索引列的顺序策略在建设联合索引中，我们经常遇到的困惑就是索引的顺序问题。正确的索引应该依赖使用该索引的查询，并能同时考虑更好的满足排序和分组的需求。联合索引意味着他按照我们上面描述的最左匹配原则来进行搜素。当不考虑分组和排序的时候，我们经常是将选择性高的内容放在前面，以下面的查询为例：

where  empname like LsHfFJ%' and depno106;

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

sum(empname '),1)">sum(depno106)  emp;---------------------------+----------------+') 106) 7                           500194         set

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

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

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

as empname_selecttivity,1)">as depno_selecttivity,1)">--------------------+--------------------+----------+| empname_selecttivity | depno_selecttivity 0.1713               0.0000             5000000 set 

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

between and范围检索

where ID between 18 and 28--------+|       11 0.001 sec) 

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

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

49900004990000 0.878 sec)

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

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

in的检索

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

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

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

from emp a where  2 a.ID in (800000,1); Font-weight: bold">800001,1); Font-weight: bold">800002,1); Font-weight: bold">800003,1); Font-weight: bold">800004,1); Font-weight: bold">800005,1); Font-weight: bold">800006,1); Font-weight: bold">800007,1); Font-weight: bold">800008,1); Font-weight: bold">800009,1); Font-weight: bold">800010,1); Font-weight: bold">800011,1); Font-weight: bold">800012,1); Font-weight: bold">800013,1)"> 3  800014,1); Font-weight: bold">800015,1); Font-weight: bold">800016,1); Font-weight: bold">800017,1); Font-weight: bold">800018,1); Font-weight: bold">800019,1); Font-weight: bold">800020,1); Font-weight: bold">800021,1); Font-weight: bold">800022,1); Font-weight: bold">800023,1); Font-weight: bold">800024,1); Font-weight: bold">800025,1); Font-weight: bold">800026,1); Font-weight: bold">800027,1); Font-weight: bold">800028 4 800029,1); Font-weight: bold">800030,1); Font-weight: bold">800031,1); Font-weight: bold">800032,1); Font-weight: bold">800033,1); Font-weight: bold">800034,1); Font-weight: bold">800035,1); Font-weight: bold">800036,1); Font-weight: bold">800037,1); Font-weight: bold">800038,1); Font-weight: bold">800039,1); Font-weight: bold">800040,1); Font-weight: bold">800041,1); Font-weight: bold">800042,1); Font-weight: bold">800043,1); Font-weight: bold">800044 5 800045,1); Font-weight: bold">800046,1); Font-weight: bold">800047,1); Font-weight: bold">800048,1); Font-weight: bold">800049,1); Font-weight: bold">800050,1); Font-weight: bold">800051,1); Font-weight: bold">800052,1); Font-weight: bold">800053,1); Font-weight: bold">800054,1); Font-weight: bold">800055,1); Font-weight: bold">800056,1); Font-weight: bold">800057,1); Font-weight: bold">800058,1); Font-weight: bold">800059,1); Font-weight: bold">800060800061,1); Font-weight: bold">800062,1); Font-weight: bold">800063,1); Font-weight: bold">800064,1); Font-weight: bold">800065,1); Font-weight: bold">800066,1); Font-weight: bold">800067,1); Font-weight: bold">800068,1); Font-weight: bold">800069,1); Font-weight: bold">800070,1); Font-weight: bold">800071,1); Font-weight: bold">800072,1); Font-weight: bold">800073,1); Font-weight: bold">800074,1); Font-weight: bold">800075,1); Font-weight: bold">800076800077,1); Font-weight: bold">800078,1); Font-weight: bold">800079,1); Font-weight: bold">800080,1); Font-weight: bold">800081,1); Font-weight: bold">800082,1); Font-weight: bold">800083,1); Font-weight: bold">800084,1); Font-weight: bold">800085,1); Font-weight: bold">800086,1); Font-weight: bold">800087,1); Font-weight: bold">800088,1); Font-weight: bold">800089,1); Font-weight: bold">800090,1); Font-weight: bold">800091,1); Font-weight: bold">800092800093,1); Font-weight: bold">800094,1); Font-weight: bold">800095,1); Font-weight: bold">800096,1); Font-weight: bold">800097,1); Font-weight: bold">800098,1); Font-weight: bold">800099);------+--------+---------+---------+-----+---------------------+------+------+-------+| ID     | empno  800000 | qVFqPY  43:02 105 800001 | KVzJXL  107 800002 | vWvpkj  102 15 ............16 800099 | roxtAx  17 18 100 rows 0.001 sec) 

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

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

多个独立索引的执行策略

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

index IDx_emp_depno on emp(depno); 2 MysqL> show index  3 -----+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed Null | Index_type | Comment | Index_comment |          0 PRIMARY         |            | ID          | A         |     4952492 NulL     NulL   |      | BTREE      |         |               | IDx_emp_ID      | IDx_emp_depno   | depno       | IDx_emp_empname | empname     1650830 4 rows set 

查询下试试：

1060.000 sec) 

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

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

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

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

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

0.001 sec)

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

where depnoand empname0.000 sec)

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

500194 0.121 sec)

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

我们使用explain来看一下：

--+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+------------------------------------+| type | possible_keys                 key             | Extra                              | ref  | IDx_emp_depno,IDx_emp_empname 22      | Using index condition; Using where set

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

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

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

模糊查询

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

where empname LsHfFJA% 2 0.000 sec) 8  9 MysqL%LsHfFJA%15 2.034 sec) 

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

关于回表

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

select empname,job,hiredate,sal -------+---------+---------------------+------+set 

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

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

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

索引覆盖

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

我们来看一个查询：

select ID,empname -------+---------+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服务器访问存储引擎的次数。

我们举个例子来看一看：

count(ID) and  sal2000---------+count(ID) set 

执行步骤如下：

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

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

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

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

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

1、走(empname,sal)索引检索出以 empname '  的第一条记录，可以得到(empname,sal,ID)，我们记为Record1。
2、判断 Record1.sal2000 的值，并重复上面的 *** 作，直到找到所有记录为止

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

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

set

 

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

1-------+---------+---------+----------+-----+---------------------+-------+------+-------+| job      | sal   5000001 5000099 1       | engineer 0305 19:09:28 22500 2.645where depno 500195 16 17 MysqL10619 20 21 22 23 0.001 sec)

 

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

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

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

函数导致索引无效

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

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

from emp  = | PWmulY  24 set  (0.002 sec)where ABS(ID) 49900014990001 | fXtdiH  set  (2.007 sec)

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

set--+-------------+-------+------+---------------+------+---------+------+---------+-------------+| possible_keys key  | rows    | Extra       ALL  NulL          NulL NulL    NulL set

表达式导致索引无效

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

(0.002 sec)where ID+(1.762 sec) 

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

set 

索引覆盖排序字段

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

 1 MysqL>  select * where depno = 106 limit 10 2 +-----+-------+---------+---------+-----+---------------------+------+------+-------+ 3 | ID  | empno | empname | job     | mgr | hiredate            | sal  | comn | depno | 4 +-----+-------+---------+---------+-----+---------------------+------+------+-------+ 5 |   8 |     8 | LsHfFJ  | SALEMAN |   1 | 2021-01-16:42:03 | 2000 | 400  |   106 | 6 |  15 |    15 | WxxUeO  | SALEMAN |    7 |  40 |    40 | dZPYxu  | SALEMAN |    8 |  43 |    43 | yyWlyF  | SALEMAN |    9 |  65 |    65 | xRSLaX  | SALEMAN |   10 |  70 |    70 | dgwNzl  | SALEMAN |   11 |  91 |    91 | lpTzQG  | SALEMAN |   12 | 101 |   101 | BeSZuX  | SALEMAN |   13 | 127 |   127 | hvHYVI  | SALEMAN |   14 | 132 |   132 | IUyKci  | SALEMAN |   15 +-----+-------+---------+---------+-----+---------------------+------+------+-------+10 rows 0.01717 18 MysqL>  106 order by hiredate limit 19 +-----+-------+---------+---------+-----+---------------------+------+------+-------+20 | ID  | empno | empname | job     | mgr | hiredate            | sal  | comn | depno |21 +-----+-------+---------+---------+-----+---------------------+------+------+-------+22 | 142 |   142 | EapywO  | SALEMAN |   23 | 24 | 25 | 26 |  27 |  28 |  29 |  30 |  31 |  32 +-----+-------+---------+---------+-----+---------------------+------+------+-------+33 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排好序的，

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

0.001 sec) 

索引使用总结

1、正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

2、正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。

3、联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，MysqL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。

如   depno=1 and empname>'' and job=1  如果建立(depno,job)顺序的索引，job是用不到索引的。

4、应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表 *** 作，提升效率。 

5、正确判断是否使用联合索引（上面联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表 *** 作，提升效率。

6、避免索引失效的原则：禁止对索引字段使用函数、运算符 *** 作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

7、避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

8、模糊查询'%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是'value%'是可以有效利用索引。

9、索引覆盖排序字段，这样可以减少排序步骤，提升查询效率 

10、尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

总结

以上是内存溢出为你收集整理的MySQL全面瓦解24：构建高性能索引（策略篇）全部内容，希望文章能够帮你解决MySQL全面瓦解24：构建高性能索引（策略篇）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。