mysql索引
本文转载自https://blog.csdn.net/weixin_43874301/article/details/128606128
https://blog.csdn.net/weixin_43874301/article/details/128606128
索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。
索引在mysql数据库中分三类：
B+树索引、Hash索引、全文索引
B+树索引从二叉查找树，平衡二叉树和B树这三种数据结构演化来的。
二叉查找树
从图中可以看到，为user表（用户信息表）建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点，节点中存储了键(key)和数据(data)。
键对应user表中的id，数据对应user表中的行数据。二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。 顶端的节点称为根节点，没有子节点的节点称之为叶节点。
如果需要查找id=12的用户信息，利用创建的二叉查找树索引，查找流程如下：
- 将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来把当前节点>的右子节点作为当前节点。
- 继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。
- 把12和当前节点的键值12对比，12等于12，满足条件，从当前节点中取出data，即id=12,name=xm。
- 利用二叉查找树只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，需要6次才能找到。
平衡二叉树
上面讲解了利用二叉查找树可以快速的找到数据。但是，如果上面的二叉查找树是这样的构造：
这个时候可以看到的二叉查找树变成了一个链表。
如果需要查找id=17的用户信息，需要查找7次，也就相当于全表扫描了。
导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。
为了解决这个问题，需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。
平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。
平衡二叉树保证了树的构造是平衡的，当插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。
平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。
B树
因为内存的易失性。一般情况下，都会选择将user表中的数据和索引存储在磁盘这种外围设备中。
但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，应当尽量减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。
如果能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那查找数据的时间也会大幅度降低。如果用树这种数据结构作为索引的数据结构，那每查找一次数据就需要从磁盘中读取一个节点，也就是说的一个磁盘块，都知道平衡二叉树可是每个节点只存储一个键值和数据的。如果要存储海量的数据，可以想象到二叉树的节点将会非常多，高度也会及其高，查找数据时也会进行很多次磁盘IO，查找数据的效率将会极低！
为了解决平衡二叉树的这个弊端，应该寻找一种单个节点可以存储多个键值和数据的平衡树。
B树（Balance Tree）即为平衡树的意思
图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。- 图中的每个节点称为页，页就是上面说的磁盘块，在MySQL中数据读取的基本单位都是页，所以这里叫做页更符合MySQL中索引的底层数据结构。
从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。
基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。
假如要查找id=28的用户信息，那么在上图B树中查找的流程如下：
- 先找到根节点也就是页1，判断28在键值17和35之间，那么根据页1中的指针p2找到页3。
- 将28和页3中的键值相比较，28在26和30之间，根据页3中的指针p2找到页8。
- 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。
B+树
B+树是对B树的进一步优化。让先来看下B+树的结构图：
B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般查找10亿数据，只需要2次磁盘IO。
因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
在innodb中，通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。
聚集索引 VS 非聚集索引
在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。
聚集索引（聚簇索引）
以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，称之为聚集索引。
特点：
根据主键的大小进行记录和数据页的排序（ 1.一个数据页内的多个记录是根据主键的大小排成一个单链表2.各个数据页也是根据主键的大小排成一个双链表3.每一层的数据页中也是根据主键的大小进行排序）
叶子节点中存储的是完整的数据记录（完整记录指一条记录中包括了所有字段的值，包括隐藏字段/隐藏列的值）
优点：
- 数据访问更快，因为聚簇索引将数据和索引保存在同一个树中，因此获取速度比非聚簇索引要快
- 对于主键的排序查找和范围查找更快（本身就是有序的）
- 数据成块出现，节省大量IO操作
缺点：
- 插入速度严重依赖插入顺序.按照主键顺序插入是最快的，如果不按主键插入，就会发生页分裂，重新调整顺序
- 更新主键的代价高。主键更改，该记录就会移动，其他记录也会被迫移动，变动太大，主键通常定义不可更新
- 二级索引访问需要两次索引查找。第一次找到主键值，第二次根据主键值找到行数据.
限制：
MySQL数据库中只有Innodb支持聚簇索引，MyISAM不支持
每个表只能有一个聚簇索引，一般为该表的主键，因为数据物理存储排序方式唯一。
没有定义主键时，Innodb会选择非空的唯一索引代替， 如果没有这样的索引，Innodb会隐式的定义一个主键作为聚集索引
选择主键时，尽量用有序的顺序id，用无序id像字符串等可能无法保证数据的顺序增长
非聚集索引（非聚簇索引）
以主键以外的列值作为键值构建的B+树索引，称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，称为回表。
