Mysql数据库索引数据结构学习篇

文章部分摘于https://www.cnblogs.com/yufeng218/p/12465694.html

1、先了解一下mysql数据库：

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。MySQL是开放源代码的。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

  MySQL结构图：

2、影响数据库性能的因素：

 1.服务器硬件
 2.服务系统
 3.数据库存储引擎的选择(重点)
 4.数据库参数配置
 5.数据库表结构设计和sql语句(重点)

3、索引：

索引是帮助MySQL高效获取数据的排好序的数据结构。

索引的数据结构：

有一张表，表名为 t ，表中有7条数据；使用 select * from t where t.clo2 = 89 进行查询

如果没有创建索引

若表中没有创建索引，则会全表扫描，一条一条的遍历查询，需要遍历 6 次，查询一行数据至少和磁盘做一次I/O操作（I/O是很耗性能的），至少要做 6 次 I/O 操作；

大家可以在这个网站上学习各种数据结构：https://www.cs.usfca.edu/~galles/visualization/BST.html

如果创建了索引，索引的底层是二叉树，这样查询 4 次就找到数据了
二叉树特点：左边的子元素小于父元素，右边的子元素大于父元素。每个结点最多有两颗子树，结点的度最大为2，也就是只有两个分叉的树。度的意思是当前结点有几个分叉就是几度。左子树和右子树是有顺序的，次序不能颠倒。即使某结点只有一个子树，也要区分左右子树。

有大小顺序的二叉树就变成了单项链表的数据结构。

如果创建了索引，索引的底层是红黑树，这样查询 2 次就找到数据了
红黑树特点：是一种自平衡二叉查找树，每个节点是黑色，或者是红色。根节点是黑色。如果一个节点是红色的，则它的子节点必须是黑色的。当数据越大树的高度越大，查询越慢 ，高度不可控（数据多io重、性能差）。

当单边的节点大于3时候，就会自动调整，这样可以解决二叉树的弊端。

 

如果创建了索引，索引的底层是B-Tree，这样查询 2 次就找到数据了
B-Tree特点：叶节点据有相同的深度，叶节点的指针为空，所有的索引元素不能重复，节点中的数据索引从左到右递增排序。

若 Max. Degree = 4，则如下图所示：

 

 

当然 B-Tree 也是有弊端的；以下是 B-Tree 的存储，数字为key，data为对应的数据；

若一个节点我们申请的空间为16KB，若data中的数据过大，则一个节点能放的数据量越小，这样就会造成树的高度比较大了（比红黑树高度小点）；空白大小为6个字节  存放的是下一个节点内存中的地址。

如果创建了索引，索引的底层是B+Tree，这样查询 2 次就找到数据了

B+Tree特点：非叶子节点不存储data，只存储索引（冗余），可以放更多索引；叶子节点包含所有索引字段，即所有的data元素存储在叶子节点上；叶子节点使用指针连接，提高区间访问的性能；树节点从左到右依次递增，叶子节点从左到右依次递增，双向指针。

B+Tree 查询某一数据具体过程：

以上图中查找 49 的数据：

1.先将根节点的数据(15, 56, 77) 做一次磁盘 I/O 操作取出加载到内存中，然后再在内存中做比对，找到对应的指针，查找到其对应的节点；

2. 将指针指向节点的数据(15, 20, 49) 做一次磁盘 I/O 操作取出加载到内存中，然后再在内存中做比对，找到对应的指针，接着去叶子节点获取数据；

B+Tree 相对于 B-Tree的优化点：

  优化点1： B-Tree的所有节点都存储了 data 元素， B+Tree的非叶子节点不存储 data元素，则 B+Tree 的一个非叶子节点可以存储更多的索引；

  优化点2： B+Tree在叶子节点之间增加了指针连接；对 select * from t where col2 > 20 的范围查找有很好的支持；MySQL 对 B+Tree 做了优化，叶子节点使用的是双向指针；

MySQL页默认能够存储多少数据：

<1> 查看MySQL文件页大小（一个节点的大小）：

SHOW GLOBAL STATUS like 'Innodb_page_size'；

<2> MySQL页文件默认为16KB，树的高度为3，能够存储多少数据？

　　我们先看非叶子节点，假设主键ID为 bigint 类型，那么长度为8B，指针大小在Innodb源码中6B，一共14B，那么一页（即一个节点）可以存储  16KB/14B=1170 个索引元素和 1170个指针；根节点有1170个索引和1170个指针，树高度为2的节点就有1170个，那么叶子节点的数量为 1170x1170；每个叶子节点可以存储16KB，若每条数据比较大为1KB，那么每个叶子节点可以存储16条数据；那么，高度为3的 B+Tree 的叶子节点可以存储的数据量为 1170x1170x16=2000W；

 

如果创建了索引，索引的底层是Hash表

先介绍一下Hash算法

Hash本身是一种函数，又被称为散列函数。将key放在数组里，用一个hash算法把不同的key转换成一个确定的value，然后放在这个数组的指定位置，相同的输入永远可以得到相同的输出。具体的算法有MD5、SHA1、SHA2、SHA3。

Hash冲突：不同的key得到了相同的value，当出现Hash冲突，可以在冲突发生的位置跟一个链表。

若索引使用的 Hash 存储的，存储的时候先做一次hash运算，根据 hash 的值就可以快速的定位数据的磁盘指针，这样就不管表里面有多少数据，我们的查询效率都非常的快；

在实际中为什么使用 B-Tree 或 B+Tree 来存储索引的方式更多，而不太使用 hash 呢？

原因1：若使用 select * from t where clo2 > 6，这种查找范围的SQL，那Hash就不能搞定了，就不会走索引了；而且对排序hash也没有办法；

原因2：hash会产生 hash 碰撞，MySQL的底层对hash做了处理，很少会发生hash碰撞的；