mysql数据库索引使用总结和对比

左手的ㄟ右手 2021-07-21 02:27 332阅读 0赞

# 数据库存储特点 #

数据库和nosql数据库、ES等软件相比更加重量级，同时更加安全，支持的查询方式也是其他软件不能比拟的，数据和索引文件均放到硬盘中，在数据量大时，一般都将大量数据先存取到第三方软件中，然后再异步批量插入到数据库中，数据优先从第三方软件中查询，一些特殊操作，如数据统计，历史数据查询，数据分析再从数据库中查询。

# 数据库查询过程 #

数据库查询过程中可以理解为从硬盘上读取数据到内存，然后进行条件判断，最后将符合条件的数据用一个中间表装起来，返回给客户端的过程。

# 普通查询的问题 #

数据库查询过程中花费时间主要在于IO操作和大量中间表操作，前者因为硬盘性能原因和数据量导致读取耗时，后者主要体现为条件判断、列数过多导致中间表很大。

# 问题对应方法 #

使用索引实际上大量减少IO了操作，若普通查询需要读取1000次，使用索引后可能1次性就能读取出来。

中间表可以通过减少无关列数，优化查询条件

# 索引到底是什么 #

索引是帮助Mysql高效获取数据的一种数据结构，随着数据库版本更新，索引底层的数据结构也一步步进化。

# 索引解决了什么 #

若表不建立索引，会导致查询变为全表扫描，explain 结果为all，这是一种非常不推荐的做法，尤其是数据突破十万条以后查询将变得很慢。

# 索引储存在哪里 #

和数据一样，索引以文件形式储存在硬盘上，在MyISAM储存引擎中，数据和索引文件试试分开储存的。

![format_png][]

MyISAM文件储存示意图

在InnoDB中，数据和索引文件是合起来储存的，注意下图中没有了I（index）结尾的文件。

![format_png 1][]

InnoDB文件储存示意图

后面会进一步分析为什么会这样

# 索引的各种存储结构及其优缺点 #

在开始讲这一小节之前，我们先来看一下在数据库没有加索引的情况下，SQL中的where字句是如何查找目标记录的。

我们先看下左边表格第二列Col2列的数据时如何查找的，如果我们希望查找`where Col2 = 22`的记录，我们在没加索引的情况下是按顺序从第一条记录查找，由此可知需要查找5次才能找到；

如果对Col2字段加上索引后，我们假设使用最简单的二叉树作为索引存储方式，再次查找`where Col2 = 22`的记录这次只需要查找2次就能找到目标记录，效率提高十分明显。  
[![35.png][]][35.png 1]

# 二叉树 #

1.  优点

二叉树是一种比顺序结构更加高效地查找目标元素的结构，它可以从第一个父节点开始跟目标元素值比较，如果相等则返回当前节点，如果目标元素值**小于**当前节点，则移动到**左侧子节点**进行比较，**大于**的情况则移动到**右侧子节点**进行比较，反复进行操作最终移动到目标元素节点位置。  
[![36.gif][]][36.gif 1]

1.  缺点

在大部分情况下，我们设计索引时都会在表中提供一个自增整形字段作为建立索引的列，在这种场景下使用二叉树的结构会导致我们的索引总是添加到右侧，在查找记录时跟没加索引的情况是一样的，如下图所示:  
[![37.gif][]][37.gif 1]

# 红黑树 #

1.  优点

红黑树也叫平衡二叉树，它不仅继承了二叉树的优点，而且解决了上面二叉树遇到的自增整形索引的问题，从下面的动态图中可以看出红黑树会走动对结构进行调整，始终保证**左子节点数 < 父节点数 < 右子节点数**的规则。  
[![38.gif][]][38.gif 1]

1.  缺点

在数据量大的时候，深度也很大。从图中可以看出每个父节点只能存在两个子节点，如果我们有很多数据，那么树的深度依然会很大，可能就会超过十几二十层以上，对我们的磁盘寻址不利，依然会花费很多时间查找。

# Hash #

1.  优点

对数据进行Hash（散列）运算，主流的Hash算法有MD5、SHA256等等，然后将哈希结果作为文件指针可以从索引文件中获得数据的文件指针，再到数据文件中获取到数据，按照这样的设计，我们在查找`where Col2 = 22`的记录时只需要对22做哈希运算得到该索引所对应那行数据的文件指针，从而在MySQL的数据文件中定位到目标记录，查询效率非常高。

1.  缺点

无法解决**范围查询**（Range）的场景，比如`select count(id) from sus_user where id >10;`因此Hash这种索引结构只能针对**字段名=目标值**的场景使用。

不适合模糊查询（like）的场景。

# B-Tree #

既然红黑树存在缺点，那么我们可以在红黑树的基础上构思一种新的储存结构。解决的思路也很简单，既然觉得树的深度太长，就只需要适当地增加每个树节点能存储的数据个数即可，但是数据个数也必须要设定一个合理的阈值，不然一个节点数据个数过多会产生多余的消耗。

按照这样的思路，我们先来了解下关于B-Tree的一些知识点：

*  度(Degree)-节点的数据存储个数，每个树节点中数据个数大于 **15/16\*Degree**（未验证） 时会自动分裂，调整结构
 *  叶节点具有相同的深度，左子树跟右子树的深度一致
 *  叶节点的指针为空
 *  节点中的数据key从左到右递增排列

1.  树节点结构

在这里需要说明下的是，BTree的结构里每个节点包含了索引值和表记录的信息，我们可以按照Map集合这样理解：key=索引，value=表记录，如下图所示:  
[![39.png][]][39.png 1]

1.  优点

BTree的结构可以弥补红黑树的缺点，解决数据量过大时整棵树的深度过长的问题。相同数量的数据只需要更少的层，相同深度的树可以存储更多的数据，查找的效率自然会更高。  
[![40.gif][]][40.gif 1]

1.  缺点

从上面得知，在查询单条数据是非常快的。但如果范围查的话，BTree结构每次都要从根节点查询一遍，效率会有所降低，因此在实际应用中采用的是另一种BTree的变种B+Tree（B+树）。

# B+Tree（MySQL索引的真正存储结构） #

在介绍B+Tree之前，我们先来看下面两个问题：

1.  为什么要对BTree继续做优化？
    
    要解答这个疑问需要先了解BTree每个节点结构（上面已经说明）和MySQL数据库它是如何读取索引数据的，索引和表数据在不使用的时候是存储在文件中的，也就是磁盘，当我们执行查询操作时会DBMS（数据库管理系统）首先会先从内存中查找，如果找到直接使用，如果找不到则从磁盘文件中读取；操作系统储存数据的最小单位是**页（page）**，一页假设是**4K**大小（由操作系统决定），对内存和磁盘读取数据是按**一页的整数倍**读取的。  
    [![41.png][]][41.png 1]
    
    这里我们假设数据库一次IO操作就读取1页4K的数据，再假设图中圈起来的元素就是一个大节点，内含多个小节点的索引和数据，其大小是10MB，那么我们要从磁盘中读取完整个大节点需要进行 10M / 4K = 2500次IO操作，这样就可以看出如果大节点数据总量越大，需要执行的IO操作越多，花费的时间也越长，因此为了提高性能，数据库会建议我们一个大节点只存储一页4K大小的数据，这里的数据包含了索引和表记录，另外我们还能计算出树的度Degree应该设置成多大才合理：
    
    Degree=内存页大小（4K）÷单个索引值字节大小Degree=内存页大小（4K）÷单个索引值字节大小
    
    进一步分析，索引值的大小相对于整条记录的大小是很小的，如果我们需要查找的数据刚好是在最后，那么前面遍历过的节点中存储的记录数据是不是对我们来说是没用的，它会占用比索引大得多的空间，导致我们一个大节点里能遍历的索引数量大大减少，需要向下继续遍历的几率就更大，花费更多时间查找，那么有没有办法可以优化呢？看下一个问题。
2.  相对于BTree，B+Tree做了哪些优化？
    
     *  B+Tree存储结构，只有叶子节点存储数据  
        新的B+树结构没有在所有的节点里存储记录数据，而是只在最下层的叶子节点存储，上层的所有非叶子节点只存放索引信息，这样的结构可以让单个节点存放下更多索引值，增大度Degree的值，提高命中目标记录的几率。  
        这种结构会在上层非叶子节点存储一部分冗余数据，但是这样的缺点都是可以容忍的，因为冗余的都是索引数据，不会对内存造成大的负担。  
        [![42.png][]][42.png 1]
     *  每个叶子节点都指向下一个叶子节点  
        这点优化有什么用呢？我们直接看下面的B+Tree结构，如果我们进行范围查找where id > 4的记录，我们只需要先找到id = 4的记录后自然就能通过叶子节点间的双向指针方便地查询出大于4的所有记录。  
        [![43.png][]][43.png 1]  
        [![44.gif][]][44.gif 1]

# #

## MyISAM索引实现（非聚集索引） ##

MyISAM中索引和数据是分开储存的，并且主键索引和辅助索引（二级索引）的储存方式是一样的。

![format_png 2][]

主键索引

![format_png 3][]

辅助索引

## InnoDB索引实现（聚集索引） ##

InnoDB中索引文件和数据文件是同一个文件。并且主键索引和二级索引储存方式有所不同，如图所示，二级索引的叶子节点不储存数据，仅储存主键ID。

![format_png 4][]

主键索引

![format_png 5][]

辅助索引

这里思考两个问题

1.  InnoDB表中必然有主键，为什么最好一定是有序自增id？
2.  为什么二级索引叶子节点储存的是主键值？

问题一：如果id是无序的，那么很有可能新插入的值会导致当前节点分裂，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。  
反之，如果每次插入有序，那就会在当前页后面连续写入，写不下就会重新分配一个节点，内存都是连续的，这样效率自然也就最高了。

问题二：如果二级索引储存的也是数据，那么每次插入mysql都不得不更新每棵索引树，这样就加剧了新增编辑时的性能损耗，并且这样一来空间利用率也不高，产生了大量冗余数据。

## 联合索引 ##

联合索引底层数据结构长什么样？

![format_png 6][]

联合索引示意

比较相等时，先比较第一列的值，如果相等，再继续比较第二列，以此类推。

## 最左前缀原理 ##

使用联合索引时，索引列的定义顺序将会影响到最终查询时索引的使用情况。例如联合索引（a,b,c），mysql会从最左边的列优先匹配，如果最左边的带头大哥没有使用到，在未使用覆盖索引的情况下，就只能全表扫描。  
联合底层数据结构思考，mysql会优先以联合索引第一列匹配，此后才会匹配下一列，如果不指定第一列匹配的值，也就无法得知下一步查询哪个节点。  
另外还有一种情况，如果遇到 > < between等这样的范围查询，那B+树中也就无法对下一列进行等值匹配了。

[format_png]: /images/20210720/5671acdba74b42aca522263300964299.png
[format_png 1]: /images/20210720/bd3a2b22542b4e8098cd693e4bc873ff.png
[35.png]: /images/20210720/7ef5ff287cae46fd8bbe78aa48a444b9.png
[35.png 1]: https://kwilove.gitee.io/styles/images/mysql/35.png
[36.gif]: /images/20210720/aab94543f4b442ceb8f2fecc5a69aee6.png
[36.gif 1]: https://kwilove.gitee.io/styles/images/mysql/36.gif
[37.gif]: /images/20210720/b56ecd8eced74c43a3646f35169c9f72.png
[37.gif 1]: https://kwilove.gitee.io/styles/images/mysql/37.gif
[38.gif]: /images/20210720/08e87da4f8a04f34b72dea4b0e5fba17.png
[38.gif 1]: https://kwilove.gitee.io/styles/images/mysql/38.gif
[39.png]: /images/20210720/180892aa60534369acf48e27b6cbc36f.png
[39.png 1]: https://kwilove.gitee.io/styles/images/mysql/39.png
[40.gif]: /images/20210720/abb89bd840214e01bb6d63048e95a21c.png
[40.gif 1]: https://kwilove.gitee.io/styles/images/mysql/40.gif
[41.png]: /images/20210720/2c94314c34c041c18913bad1d602a1a8.png
[41.png 1]: https://kwilove.gitee.io/styles/images/mysql/41.png
[42.png]: /images/20210720/a823d416ad4342f492e4d5b1ae97244d.png
[42.png 1]: https://kwilove.gitee.io/styles/images/mysql/42.png
[43.png]: /images/20210720/717ffe58bd08410385c9ede4a9c0fd69.png
[43.png 1]: https://kwilove.gitee.io/styles/images/mysql/43.png
[44.gif]: /images/20210720/16476fdbd6624c888ced2393cbaa5f01.png
[44.gif 1]: https://kwilove.gitee.io/styles/images/mysql/44.gif
[format_png 2]: /images/20210720/2b45c756684e4f5f8b226b38e0a4a33a.png
[format_png 3]: /images/20210720/12de1366cefe49d8884ae4709aabf922.png
[format_png 4]: /images/20210720/f6ed474e76b8469ebbc259d29c3e5119.png
[format_png 5]: /images/20210720/93705868f9774553aefbcb92b8b8302c.png
[format_png 6]: /images/20210720/d03547ddbb29480cb5e6838e77f2f4da.png