MySQL全面瓦解24:构建高性能索引(策略篇)

top_tony
发布于 2022-7-22 16:57
浏览
0收藏

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

MySQL全面瓦解22:索引的介绍和原理分析

MySQL全面瓦解23:MySQL索引实现和使用

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

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

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

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

MySQL全面瓦解24:构建高性能索引(策略篇)-鸿蒙开发者社区

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+树中数据执行过程
唯一记录定位
 MySQL全面瓦解24:构建高性能索引(策略篇)-鸿蒙开发者社区 

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

1、将Disk1页加载到内存

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

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

查询所有同值记录
  MySQL全面瓦解24:构建高性能索引(策略篇)-鸿蒙开发者社区

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

1、将Disk1页加载到内存

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

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

范围查找
 MySQL全面瓦解24:构建高性能索引(策略篇)-鸿蒙开发者社区

数据如上图,查询[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的值停止。

模糊匹配
 MySQL全面瓦解24:构建高性能索引(策略篇)-鸿蒙开发者社区

数据如上图。

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

MySQL全面瓦解24:构建高性能索引(策略篇)-鸿蒙开发者社区

第一位置
检索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的数据。

MySQL全面瓦解24:构建高性能索引(策略篇)-鸿蒙开发者社区

再看看他的执行过程,扫描了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来检索 

  MySQL全面瓦解24:构建高性能索引(策略篇)-鸿蒙开发者社区

 因为在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)的索引,那么只需要修改原来的索引即可。

 

文章转载自公众号:架构与思维

已于2022-7-22 16:57:37修改
收藏
回复
举报
回复
    相关推荐