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

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

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的基数更大，需要访问和比较的次数也更多一点。 
这种区分是有一种计算公式来衡量的：
1 selecttivity = count(distinct c_name)/count(*) 
当索引区分度越高，检索速度越快，索引区分度低，则说明重复的数据比较多，检索的时候需要访问更多的记录才能够找到所有目标数据。
当索引区分度小到无限趋近于0的时候，基本等同于全表扫描了，此时检索效率肯定是慢的。
第一个数组没有重复数据，索引区分度为1，第二个区分度为0.2，所以第一个检索效率更高。
我们创建索引的时候，尽量选择区分度高的列作为索引。

数据对比 

我们来看看emp 表中的两个字段，empname 和 depno 字段，
empname基本不重复，所以每个empname只有一条数据；而 500W的数据大约分配了10个部门，所以每个的depno下有约50W的数据。
1 mysql> select count(distinct empname)/count(*),count(distinct depno)/count(*) from emp; 
2 +----------------------------------+--------------------------------+
3 | count(distinct empname)/count(*) | count(distinct depno)/count(*) |
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 set
16 
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上有聚集索引，所以检索效率很高

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

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

前缀索引和索引长度计算

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

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

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

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

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

前缀索引的长度的判断方法 
1 select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;

 

1 SELECT
2     count(DISTINCT LEFT(cname, 3)) / count(*) AS sel3,
3     count(DISTINCT LEFT(cname, 4)) / count(*) AS sel4,
4     count(DISTINCT LEFT(cname, 5)) / count(*) AS sel5,
5     count(DISTINCT LEFT(cname, 6)) / count(*) AS sel6,
6     count(DISTINCT LEFT(cname, 7)) / count(*) AS sel7
7 FROM
8     tname
添加前缀索引 
1 ALTER TABLE tname ADD KEY (cname[(lenth)]); 

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

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

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

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

 1 mysql> create index idx_emp_empname on emp(empname(2));
 2 Query OK, 0 rows affected
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> select * from emp where empname='LsHfFJA';
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 8 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 9 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 1 row in set  (1.793 sec) 

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

 1 mysql> create index idx_emp_empname on emp(empname(6));
 2 Query OK, 0 rows affected
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> select * from emp where empname='LsHfFJA';
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 8 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 9 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 1 row in set  （0.003 sec）

联合索引的使用

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

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL的“索引合并”（index merge）策略一定程序上可以使用表上的多个单列索引来定位指定的行。索引合并策略能够同时使用多个单列索引进行扫描，并将结果进行合并。那么什么时候选择联合索引呢？ 
1、当出现服务器对多个索引做相交操作时（通常有多个and条件），更好的操作是创建一个包含所有相关列的多列索引，而不是多个独立的单列索引。
2、当服务器需要对多个索引做联合操作时（通常有多个or条件），通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
 
这两种情况下更好的操作是创建一个包含所有相关列的多列索引，而不是多个独立的单列索引。

联合索引列的顺序策略

在建设联合索引中，我们经常遇到的困惑就是索引的顺序问题。正确的索引应该依赖使用该索引的查询，并能同时考虑更好的满足排序和分组的需求。
联合索引意味着他按照我们上面描述的最左匹配原则来进行搜素。
当不考虑分组和排序的时候，我们经常是将选择性高的内容放在前面，以下面的查询为例：
1 select * from emp where  empname like 'LsHfFJ%' and depno=106;

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

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

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

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

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

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

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

between and范围检索

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

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

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

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

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

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

in的检索

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

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

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

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

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

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

多个独立索引的执行策略

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

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

查询下试试：

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

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

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

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

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

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

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

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

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

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

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

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

我们使用explain来看一下：

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

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

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

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

模糊查询

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

 1 mysql> select * from emp where empname like 'LsHfFJA%';
 2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 5 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 1 row in set  (0.000 sec)
 8 
 9 mysql> select * from emp where empname like '%LsHfFJA%';
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
12 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
13 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
14 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
15 1 row in set  (2.034 sec) 

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

关于回表

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

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

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

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

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

索引覆盖

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

我们来看一个查询：

1 mysql> select id,empname from emp where empname='LsHfFJA';
2 +---------+---------+
3 | id      | empname |
4 +---------+---------+
5 | 4582071 | LsHfFJA |
6 +---------+---------+
7 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服务器访问存储引擎的次数。

我们举个例子来看一看：

1 mysql> select count(id) from emp where empname ='LsHfFJA' and  sal=2000;
2 +-----------+
3 | count(id) |
4 +-----------+
5 |         1 |
6 +-----------+
7 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 的值，并重复上面的操作，直到找到所有记录为止

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

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

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

 

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

 1 mysql> select * from emp where empname =1;
 2 +---------+---------+---------+----------+-----+---------------------+-------+------+-------+
 3 | id      | empno   | empname | job      | mgr | hiredate            | sal   | comn | depno |
 4 +---------+---------+---------+----------+-----+---------------------+-------+------+-------+
 5 | 5000001 | 5000099 | 1       | engineer |   1 | 2021-03-05 19:09:28 | 22500 | 400  |   106 |
 6 +---------+---------+---------+----------+-----+---------------------+-------+------+-------+
 7 1 row in set  (2.645 sec)
 8 
 9 mysql> select count(*) from emp where depno ='106';
10 +----------+
11 | count(*) |
12 +----------+
13 |   500195 |
14 +----------+
15 1 row in set  (0.000 sec)
16 
17 mysql> select count(*) from emp where depno =106;
18 +----------+
19 | count(*) |
20 +----------+
21 |   500195 |
22 +----------+
23 1 row in set  (0.001 sec)

 

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

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

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

函数导致索引无效

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

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

 1 mysql> select * from emp  where id = 4990000;
 2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 5 | 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 1 row in set  (0.002 sec)
 8 
 9 mysql> select * from emp  where ABS(id) = 4990001;
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
12 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
13 | 4990001 | 4990001 | fXtdiH  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   107 |
14 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
15 1 row in set  (2.007 sec)

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

 1 mysql> explain select * from emp  where id = 4990000;
 2 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 3 | id | select_type | table | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
 4 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 5 |  1 | SIMPLE      | emp   | const | PRIMARY,idx_emp_id | PRIMARY | 4       | const |    1 | NULL  |
 6 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 7 1 row in set
 8 
 9 mysql> explain select * from emp  where ABS(id) = 4990001;
10 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
11 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
12 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
13 |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
14 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
15 1 row in set

表达式导致索引无效

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

 1 mysql> select * from emp  where id = 4990000;
 2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 5 | 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 1 row in set  (0.002 sec)
 8 
 9 mysql> select * from emp  where id+1 = 4990001;
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
12 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
13 | 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
14 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
15 1 row in set  (1.762 sec) 

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

 1 mysql> explain select * from emp  where id = 4990000;
 2 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 3 | id | select_type | table | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
 4 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 5 |  1 | SIMPLE      | emp   | const | PRIMARY,idx_emp_id | PRIMARY | 4       | const |    1 | NULL  |
 6 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 7 1 row in set
 8 
 9 mysql> explain select * from emp  where id+1 = 4990001;
10 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
11 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
12 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
13 |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
14 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
15 1 row in set 

索引覆盖排序字段

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

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

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

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

索引使用总结

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

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

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

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

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

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

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

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

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

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

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

posted @ 2021-03-08 14:54  Hello-Brand  阅读(1711)  评论(1)  编辑  收藏  举报