<返回更多

MySQL数据库中如何正确的理解与使用索引?

2019-11-18    
加入收藏
MySQL数据库中如何正确的理解与使用索引?

 

索引是存储引擎用于快速查找记录的一种数据结构,我们可以通过合理的使用数据库索引以提高数据库的访问效率。接下来主要介绍在MySQL 数据库中索引类型,以及如何创建出更加合理且高效的索引技巧。

MySQL数据库的内部索引是由不同的存储引擎实现的,本文主要介绍一下 InnoDB存储引擎中的索引,InnoDB引擎中的索引是使用 B+树 的结构来存储的。


InnoDB引擎中的B+树结构

接下来我们看一下B+ 树结构,如下图:

MySQL数据库中如何正确的理解与使用索引?

 

首先,说一下B+ 树的特点:

MySQL中页是InnoDB引擎中存储数据的基本单位(块是文件系统操作的最小单位,扇区是磁盘操作的最小单位),数据是按数据页为单位来读写的,和磁盘交互的时候都是以页来进行的,每个页的大小默认是16kb。也就是说,当需要读取一条记录的时候,并不是将这个记录本身从磁盘读取出来,而是以页为单位,将整个也加载到内存中,一个页中可能有很多记录,然后在内存中对页通过二分法进行查询。

整体上来说MySQL中的索引用到了B+树,链表,二分法查找,做到了快速定位目标数据,快速范围查找。


InnoDB引擎中的索引类型

InnoDB引擎中有2种索引类型:主键索引(聚集索引)辅助索引(非聚集索引)

如下Person 表,id 作为主键索引,name 作为辅助索引

MySQL数据库中如何正确的理解与使用索引?

 

结合如上图中 Person表,InnoDB引擎数据查询过程如下:

如果需要查询 id=1 的数据,只需要通过主键索引(聚集索引)中查询就可以了。

如果需要查询 name='Jacy' 的数据,需要使用非聚集索引与聚集索引,需要2步:

  1. 首先,通过辅助索引中查询 name='Jacy' 的数据,获取id的值为 12
  2. 然后,根据id再到主键索引中查询 id=12 的数据记录。

如上,这个查询过程在MySQL中叫做 回表,下面我们会具体介绍回表。


聚集索引(主键索引)

聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序(索引中的数据物理存放地址和索引的顺序是一致的),可以这么理解:只要是索引是连续的,那么数据在存储介质上的存储位置也是连续的。 比方说:想要到字典上查找一个字,我们可以根据字典前面的拼音找到该字,注意拼音的排列时有顺序的。

聚集索引就像我们根据拼音的顺序查字典类似,可以大大的提高效率。在经常搜索一定范围的值时,通过索引找到第一条数据,根据物理地址连续存储的特点,然后查询相邻的数据,直到到达条件截止项。

每个表一定会有一个聚集索引,整个表的数据存储以B+ 树的方式存在文件中,叶子节点中的key为主键值,data为完整的整行记录的信息,非叶子节点存储主键的值。

通过聚集索引查询数据只需要按照B+ 树的搜索过程,即可以查询到对应的记录。聚集索引按照如下规则创建:


非聚集索引

索引的逻辑顺序与磁盘上的物理存储顺序不同。非聚集索引的键值在逻辑上也是连续的,但是表中的数据在存储介质上的物理顺序是不一致的,即记录的逻辑顺序和实际存储的物理顺序没有任何联系。索引的记录节点有一个数据指针指向真正的数据存储位置。

非聚集索引就像根据偏旁部首查字典一样,字典前面的目录在逻辑上也是连续的,但是查两个偏旁在目录上挨着的字时,字典中的字却很不可能是挨着的。

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

通过非聚集索引查询记录的时候,可能需要2次操作,先在非聚集索引中查询出主键,然后再到聚集索引中查询出主键对应的行记录,也就是进行两次 B+树查询。


InnoDB引擎中B+树的数据查询过程

我们在查询过程中,当使用多个索引时,InnoDB引擎使用的哪个索引,为什么有时候虽然使用了索引,但看执行计划却显示没有使用索引,这弄清楚这些之前。我们先看一下B+ 树查询数据的过程。

主键或唯一索引查询

MySQL数据库中如何正确的理解与使用索引?

 

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


非唯一索引查询

MySQL数据库中如何正确的理解与使用索引?

 

如上图,数据为并不是唯一的,我们查询26 的所有记录,过程如下:


范围查询

MySQL数据库中如何正确的理解与使用索引?

 

如上图,查询 [25,45] 所有记录,由于数据页之间是双向链表升序结构,页内部的数据是单项升序链表结构,所以只用找到范围的起始值所在的位置,然后通过依靠链表访问两个位置之间所有的数据即可,过程如下:

  1. Data page1 页加载到内存,在内存中通过二分法查找。
  2. 确定25位于 20关联的Data page3 页中,45位于40关联的Data page4 页中。
  3. Data page3 页加载到内存中,通过二分法找到第一个25的记录,然后通过链表结构继续向后访问Data page3页中的 26,当Data page3页访问完毕之后,通过Data page3页的nextpage指针访问下一页Data page4 页,同样加载到内存中,通过二分法查找所有小于45的记录。

模糊匹配查询

MySQL数据库中如何正确的理解与使用索引?

 

如上图,我们查询以 b 字母开头的所有记录,过程如下:

当我们在SQL中使用LIKE %b%全模糊查询时,执行过程是什么样的呢?

如上图,b在每个页中都存在,我们通过Data page1 页中的记录是无法判断包含b的记录在哪些页的,只能加载所有叶子节点(页),然后遍历所有记录进行过滤,才可以找到包含b 的记录。所以如果使用了LIKE %b%全模糊查询,索引对查询是无效的。


复合索引的最左匹配原则

MySQL数据库中如何正确的理解与使用索引?

 

当B+ 树的数据项是复合的数据结构,比如(name,age,sex) 的时候,B+ 树是按照从左到右的顺序来建立搜索树的,比如当使用 (Tony, 20, 男) 查询时,B+ 树会优先比较 name 来确定下一步的查询方向,如果name 相同再依次比较 age 和sex ,最后得到查询的数据。

但使用(20, 男)这样的没有name的数据来查询的时候,B+ 树则不知道下一步该查哪个节点,因为建立搜索树的时候name 就是第一个索引字段,必须要先根据name 来搜索才能知道下一步去哪里查询。

比如当使用 (Tony, 男) 查询时,B+ 树可以用name 来指定搜索方向,但下一个字段age 的缺失,所以只能把名字等于Tony 的数据都找到,然后再匹配性别是男 的数据了, 即索引的最左匹配特性,如上图。

MySQL数据库中如何正确的理解与使用索引?

 

同时,在上图中,将 a, b, c 3个字段建立为复合索引(a,b,c),索引中数据的顺序是以a asc, b asc, c asc这种排序方式存储在节点中的,索引先以a字段升序,如果a相同的时候,再以b字段升序,b相同的时候,再以c字段升序。

我们分别看下,当使用以下字段进行查询时,查询过程是什么样子的。

由于页中的记录是以a asc,b asc,c asc这种排序方式存储的,所以a字段是有序的,可以通过二分法快速查询到,过程如下:

1.将Data page 1加载到内存中,通过二分法查找,可以确定a=1的记录位于{1,1,1}和{1,6,1}内,关联Data page2与Data page3 页。

2.加载Data page 2 页到内存中,通过二分法快速找到第一条a=1的记录,然后通过链表向下一条及下一页Data page4 页进行查询,直到找到第一个不满足a=1的记录为止。

首先可以确定a=1 and b=6的记录位于{1,6,1}内,关联Data page3 页并加载到内存中,后续查找过程和a=1 查找步骤类似。

这种情况通过Data page 1页中的记录,无法判断c=1的记录在哪些页中的,只能加载索引树所有叶子节点,对所有记录进行遍历,然后进行过滤,此时索引是无效的。

同上,这种也无法利用索引,只能进行全表扫描,此时索引无效。

这种只能利用到索引中的a字段,通过a 确定索引范围,然后加载a关联的所有记录,再对c的值进行判断过滤。

这种情况只能先确定a=1 and b>=0所在页范围,然后对这个范围的所有页进行遍历,c字段在这个查询的过程中,是无法确定c的数据在哪些页的,此时c的索引失效,只有a、b能够有效的确定索引页的范围。

总结,对于复合索引失效的可能原因有以下几点:

复合索引的生效原则是从前往后依次使用生效,如果中间某个索引没有使用,那么仅断点前面的索引部分起作用,断点后面的索引不起作用,造成断点的原因一般有:


索引区分度

MySQL数据库中如何正确的理解与使用索引?

 

如上图,上面是两个有序的数组,都是10条记录,如果我们需要查询值为6 的所有记录,查询这两个数组哪个更快一些?

我们使用二分法查找包含6 的所有记录过程如下:先使用二分法找到最后一个小于6的记录,然后从这条记录向后获取下一个记录,依次与6 比较,直到遇到第一个大于6 的数字结束,或者到达数组末尾结束,通过这种方法找到6 的记录,第一个数组的查询更快的一些。因为第二个数组中含有6的比例更多的,需要访问以及匹配的次数更多一些。

这里涉及数据的区分度问题:索引区分度 = count(distint 字段) / count(字段)。

当索引区分度高的时候,查询数据要更快一些。当索引区分度太低,说明重复的数据比较多,查询的时候基本上接近于全索引数据的扫描了,此时查询速度是比较慢的。

如上图中,第一个数组的索引区分度为 0.9,第二个数组的索引区分度为0.2,所以第一个有序数组的查询效率更快一些。


结合实例理解索引的正确使用方式

为了更好地理解上述内容,我们以如下测试数据 nickname_information 表为例,其包含编号、姓名、性别、昵称 4个字段,其中除性别字段存在重复值,其余各字段均不重复,共300万条测试数据。

MySQL数据库中如何正确的理解与使用索引?

 


包含多个索引时,查询如何选择?

我们在name、sex 两个字段上分别创建索引 idx_name,idx_sex,如下:

MySQL数据库中如何正确的理解与使用索引?

 

查询姓名为testops1000001 并且性别为的所有信息:

MySQL数据库中如何正确的理解与使用索引?

 

我么可以看到执行之间不到1ms,name与sex都是索引字段,那么实际执行时使用的是哪个索引?

我们或许会说是根据WHERE 子句后的索引字段顺序,name 位于WHERE 第一个,所以走的是name字段所在的索引?执行过程如下:

  1. 首先,根据name 所在的索引找到testops1000001 对应的主键索引。
  2. 然后,根据主键索引查询所有数据记录。
  3. 最后,遍历所有数据记录过滤出sex=1 的值。

我们看一下 name='testops1000001' 查询速度,如下:

MySQL数据库中如何正确的理解与使用索引?

 

那么索引的选择真的与WHERE子句的索引字段顺序有关么?我们把name 和sex 的顺序对调一下,如下:

MySQL数据库中如何正确的理解与使用索引?

 

查询速度仍然很快,这次是不是先通过sex 索引查询出数据,然后再过滤name 呢?我们再来看一下sex=1查询速度:

MySQL数据库中如何正确的理解与使用索引?

 

看上面,查询耗时220毫秒,150万数据,因此,肯定不是使用的sex 的索引。我们使用 explain来看一下SQL执行计划。

MySQL数据库中如何正确的理解与使用索引?

 

我们通过执行计划,可以看到该SQL中可能使用的索引(possible_keys)包含idx_name,idx_sex ,但实际使用的索引(key)是idx_name

因此,当WHERE子句中包含多个索引字段并且关系是 AND 时,会使用索引区分度高的索引字段,如上例子中,显然name 字段不存在重复度,并且age 字段的重复度很高,因此会使用name 查询会更快一些。


模糊查询

MySQL数据库中如何正确的理解与使用索引?

 

如上两个SQL查询语句,第一个使用后模糊查询('testops1000%'),第二个使用全模糊查询(%testops1000%')。

第一个SQL语句可以利用到name 字段上面的索引,第二个SQL语句因无法确定需要查找的值所在的范围的,只能全表扫描,无法利用索引,所以速度比较慢。

WHERE 子句中使用 LIKE 进行模糊查询时,在关键词前加通配符或者前后都加通配号都无法使用索引,从而引发全表扫描。解决LIKE '%abc%' 时索引不被使用的方法就是添加覆盖索引(只访问索引的查询,索引和查询列一致,只需扫描索引而无须回表)。


回表

当需要查询的字段在索引树中不存在时(非索引字段),需要再次到聚集索引中去获取,这个过程叫做回表,如查询:

MySQL数据库中如何正确的理解与使用索引?

 

如上SQL查询语句中查询是所有字段,由于name 列所在的索引中只有name、id(主键索引) 两个列的值,不包含sex、nickname,所以上面过程如下:

  1. 首先,通过name 索引查询 testops1000001 对应的记录,取出id为1000001。
  2. 然后,在主键索引中查询 id=1000001 的记录,获取整行中所有字段的值。

索引覆盖

当查询中采用的索引树中包含了要查询的所有字段,不需要再去聚集索引查询数据,这种叫索引覆盖。或者说查询语句的执行只需要从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录。

MySQL数据库中如何正确的理解与使用索引?

 

如上SQL查询语句中,name 对应idx_name 索引,id为主键索引,所以idx_name 索引树叶子节点中包含了id、name的值,这个查询只用走 idx_name这一个索引就可以了。若改为查询全部字段,还需要一次回表获取sex、nickname的值,则不是索引覆盖。

所以设计SQL的时候,尽量避免使用'*',可能会多一次回表操作,同时需要关注是否可以使用索引覆盖来实现,效率更高一些。


索引下推

Index Condition Pushdown(ICP) 是MySQL 5.6中新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式,ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

举个例子,我们需要查询name以 testops1000 开头的并且性别为男 的记录数,SQL语句如下:

MySQL数据库中如何正确的理解与使用索引?

 

如上SQL查询语句,存在2种可能执行的过程:

第一种执行方式,如下:

  1. 首先,通过name 索引查询出以testops1000 的第一条记录,得到记录的 id。
  2. 然后,利用id 去主键索引中查询出这条记录T1。
  3. 最后,判断 T1.sex 是否为1,然后重复上面的操作,直到找到所有记录为止。

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

第二种执行方式,通过ICP的方式,我们可以这么做,创建一个(name, sex) 的组合索引,查询过程如下:

  1. 首先,通过(name,sex) 索引查询出以 testops1000 的第一条记录,可以得到(name, sex, id),记做T1。
  2. 然后,判断 T1.sex 是否为1,然后重复上面的操作,知道找到所有记录为止。

这个过程中不需要回表操作了,通过索引的数据就可以完成整个条件的过滤,速度比上面的更快一些。若当我们select全部字段时,索引下推可以减少回表查询的范围次数。

需要注意的是:索引下推一般可用于所求查询字段(SELECT列)不是或者不全是联合索引的字段,查询条件为多条件查询且查询条件子句(WHERE、ORDER BY)字段全是联合索引。

假设表table1 有联合索引(a,b),下面语句可以使用索引下推提高效率 SELECT * FROM table1 WHERE a > 2 AND b > 10;


类型错误使索引失效

MySQL数据库中如何正确的理解与使用索引?

 

如上SQL查询语句中,第2条查询很快,第三条用name 和 10086 比较,name为索引字段且字段类型为字符串类型,当字符串和数字比较的时候,会将字符串强制转换为数字,然后进行比较,所以第3个查询变成了全表扫描。

那么如果字段类型是数字类型,查询时使用字符串类型,会怎样?如下:

MySQL数据库中如何正确的理解与使用索引?

 

id上面有主键索引,id 是int 类型的,可以看到,上面两个查询都非常快,都可以正常利用索引快速查询,所以如果字段是数字类型,查询的值无论是字符串还是数组都会走索引。


函数使索引失效

MySQL数据库中如何正确的理解与使用索引?

 

name字段上建立索引,如上SQL查询语句中,第一个查询SQL使用索引,第二个查询SQL不使用索引。

第二个SQL对name 字段使用了concat 函数之后,name所在的索引树无法快速定位需要查找的数据所在的页,只能将所有页的记录加载到内存中,然后对每条数据使用concat 函数进行计算之后再进行条件判断,此时索引失效,进行全表数据扫描。

应尽量避免在 WHERE 子句中对 “=” 左边的字段进行函数表达式运算,可以将表达式运算移至“=”右边,否则将导致引擎放弃使用索引而进行全表扫描。


运算符使索引失效

MySQL数据库中如何正确的理解与使用索引?

 

id字段上建立主键索引,如上SQL查询语句中,第一个查询SQL使用索引,第二个查询SQL不使用索引。

第二个SQL中对id 使用运算符,id所在的索引树是无法快速定位需要查找的数据所在的页,只能将所有页的记录加载到内存中,然后对每条数据的id 进行计算之后再判断是否等于 2,此时索引失效,进行全表数据扫描。

应尽量避免在 WHERE 子句中对 “=” 左边的字段进行算术运算,可以将表达式运算移至“=”右边,否则将导致引擎放弃使用索引而进行全表扫描。


索引的一些建议

声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多资讯 >>>