mysql索引介绍

索引介绍

索引是什么

官方介绍：在关系数据库中，索引是一种单独的，物理的数对数据库表中一列或多列的值进行排序的存储结构，他是某个表中一列或若干列值的结合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

其实把索引理解为图书目录就非常好理解了。数据库索引就好比是一本书的目录，能加快数据库的查询速度。

一般来说索引本身也很大，不可能全部存放在内存中，因此索引往往是存储在磁盘上的文件中（可能存储在单独的索引文件中-myisam，也可能和数据一起存储在数据文件中-innodb）。

索引的优势和劣势

优势：

可以提高数据检索的效率，降低数据库的IO成本，类似书的目录。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

索引会占据磁盘空间

索引虽然提高了查询效率，但是会降低更新表的效率。例如每次对表进行增删改操作，MYSQL不仅要保存数据，还要保存或者更新对应的索引文件。

索引的数据结构

Hash索引

Hash索引的单条记录查询的效率很高，时间复杂度是O(1) ，但是不适合范围查找。因为Hash码之间是没有规律的，两个相近的数据，Hash值相差很远，因此会被分到不同的桶中。

二叉查找树

问题：如果按照大小顺序存储值，容易出现“树不分叉”的问题。

平衡二叉树

问题：1.因为平衡二叉树是个绝对平衡的二叉树，因此它在调整二叉树的形态上会消耗更多的性能。2.时间复杂度与树的高度有关。树有多高就需要检索多少次，每个结点的读取都对应着一次磁盘IO操作。

B树

问题：1.B树的一个节点里面存放了数据，所以不能存放很多个数据。2.不支持范围查询的快速查找，如查找10-35之间的数据，查找到15后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历。

B+树

特点：

1.所有记录节点都是按照键值的大小存放在同一层的叶子节点上，而非叶子节点只存储key的信息，这样可以大大减少每个结点的存储key数量，降低b+树的高度

2.b+树叶子结点的关键词从小到大有序排列，左边结尾数据都未保存右边节点开始数据的指针

3.b+树层级更少，相对于b树来说，b+树每个非叶子节点储存的关键字书更多，树的层级更少，所以查询速度更快。

4.b+树天然具备排序功能，b+树所有的叶子节点数据构成了一个有序链表，再查询数据的时候更方便，数据紧密性高，缓存的命中率也会比b树高。

6.b+树遍历全节点更快。b+树遍历整棵树只需要遍历所有的叶子节点即可，不需要像b树一样需要对每一层进行遍历，这样有利于数据库做全表扫描。

b树和b+树最主要的区别在于非叶子节点是否存储数据的问题。

b树：非叶子节点和叶子节点都会存放数据

b+树：只有叶子节点才会存放数据，非叶子节点存放主键和指针的值。

简单b+树：

数据页

Mysql在存储数据的时候是以数据页为最小单位的，且数据在数据也页中的存储是连续的，数据页中的数据是按照主键排序的（没有主键是由Mysql自己维护的ROW_ID来排序），数据页和数据页之间是通过双向链表来关联的，数据与数据之间是通过单向链表来关联的。

也就是说在每个数据页中，必然有一个最小的主键，然后每个数据页的页号和最小的主键会组成一个主键目录，假设现在要查找主键为2的数据，通过二分查找法最后确定下主键为2的记录在数据页1中，此时就会定位到数据页1接着再去定位主键为2的记录，这是大致流程。

索引页

如果现在有很多很多数据页，那是不是对应的主键目录会很大很大呢？

那假设有1000万条记录，5000万条记录呢？是不是就算是通过二分法查找，其效率依旧是很低的，所以为了解决这种问题Mysql又设计出了一种新的存储结构-索引页

假设上面的主键目录中记录是非常多，Mysql会将里面的记录拆分到不同的索引页中。

索引页中记录的是每页数据页的页号和该数据页中最小的主键记录，也就是说最小的主键和数据页不是单纯的维护在主键目录中，而是演变成了索引页，索引页和数据页类似，一张不够就分裂到下一张。

索引页的分层

如果索引页太多的话会往上一层扩散，那现在假设上一层的索引页记录也太多了，那怎么办？很简单，继续分裂，再往上一层继续。

举个例子。假设现在要查找37这条记录，首先从最顶层的索引页开始查找，因为id=37，因此定位到了索引页16，然后到索引页16继续查找，定位到数据在索引页3中，然后继续查找，最终能够定位到数据在数据页8中，假设数据页8如下所示

完整的图如下所示，这实际上就是一棵b+树结构

Mysql的Innodb引擎和Myisam引擎的实现

Myisam虽然查找性能极佳，但是不支持事务处理。Innodb最大的特色就是支持了ACID兼容的事务功能，而且支持行级锁。

执行指令之后，系统出现以下文件，说明这两个引擎数据和索引的组织方式是不一样的。

Innodb创建生成的文件：

1.frm:创建表的语句

2.idb:表里面的数据+索引文件

Myisam创建表后生成的文件有

1.frm:创建表的语句

2.MYD:表里面的数据文件

3.MYI:表里面的索引文件

1.Myisam引擎的底层实现（非聚集索引方式）

Myisam用的是非聚集索引的方式，技术聚合索引落在不同的两个文件上。Myisam在建表以主键作为key来建立索引B+树，树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到Myisam数据文件中直接定位到具体的数据记录。

当我们为其它的字段添加索引时，我们同样会生成对应字段的索引树，该字段的叶子节点同样是记录了对应数据的物理地址，然后也拿着这个物理地址取数据文件里定位到具体的数据记录。

2.Innodb引擎的底层实现（聚集索引方式）

Innodb是聚集索引方式，因此数据和索引都存放在同一个文件里。首先Innodb会根据主键Id作为Key建立索引b+树，b+树的叶子节点存放的是主键ID对应的数据。

这是建表的时候Innodb就会自动建立好主键ID索引树，这也就是为什么Mysql在建表时要求必须指定主键的原因。当为表的其他字段添加索引时，叶子节点存放的是主键的Key，拿到主键后，Innodb才会去主键索引树里根据刚才找到的主键key查找对应的数据(回表)。

其他字段添加索引之所以不在叶子节点存放数据是因为Innodb需要节省空间，一个表可能有很多个索引，Innodb都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余）。通过这看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是很值得的。

文章参考：一文搞懂MySQL索引所有知识点（建议收藏）_敖 丙的博客-CSDN博客

腾讯面试官问我：MySQL索引原理是什么？ - 知乎 (zhihu.com)