Mysql索引介绍

目录

1. 什么是索引

1.1 为什么要创建索引？

1.2 选择索引的数据结构B+tree

1.3 B+tree与B-tree的区别或优势

2. 索引类型

2.1 聚集索引和非聚集索引

 2.2 主键索引和二级索引...

2.3 联合索引 - 索引最左前缀原则      

---

1. 什么是索引

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

---

1.1 为什么要创建索引？

        首先我们执行一条sql语句：select * from test where test.col2 = 98，从上图可见存储是无序的，要从col2中拿到一条值为98的数据，需要遍历col2中的所有数据，而数据是存储在磁盘上的，同时又要对磁盘进行IO交互 ，而IO交互的效率是很低的，每次从磁盘中拿到数据再进行比对，是非常耗费时间和资源的，这就需要缩短我们的查询次数。 

        基于这些原因，我们的索引就诞生了，索引其实就是一种特定的数据结构。

---

1.2 选择索引的数据结构B+tree

       

         如我们常用的红黑树（左），二叉树（右），虽然都可以降低查询的次数，但是随着数据的增长，树的高度也会随之增高（会造成树的高度不可控），所以这两种存储结构是不合理的。

        我们希望树的高度是可控的，即尽可能的降低树的高度，相对于二叉树和红黑树，B-tree就有着明显的优势。

        B-tree可以有效的降低树的高度，每一个小节点相当于一个key-value（16-data），叶节点具有相同的深度，所有所以元素不重复，节点中的数据索引从左到右依次递增排列，而我们Mysql底层实际的数据结构使用的是B+tree数据结构（B+tree可以说是B-tree的变种），B+tree其实是对  B-tree进行了一些改造。

---

1.3 B+tree与B-tree的区别或优势

        1. 相对于B-tree的数据结构来说，B+tree将data数据都存储在叶子节点，非叶子节点不存储data，只存储索引（存储的索引为冗余索引），对同样的内存空间来说，B+tree的数据结构可以存储更多的索引（降低树的高度）。

        2. 在B+tree的数据结构中，节点和节点之间从左到右依次递增，在叶子节点中，相邻节点用指针连接（指针可以理解为存储相邻节点在磁盘的存储位置），提高了区间访问的性能， 而B-tree的数据结构在叶子节点之间没有连接，也无法定位到相邻的下一个叶子节点的地址，这无疑是降低了区间（范围）查找的效率。

2. 索引类型

2.1 聚集索引和非聚集索引

        MylSAM索引文件和数据文件是分离的（非聚集索引）。                                                          

         MylSAM以B+tree的数据结构存储索引，索引下的data为该索引对应的数据在磁盘的存储地址。索引会存储在.MYI文件中，数据会存储在.MYD文件中，在进行查找时，会先到MYI文件中根据索引查到对应的磁盘地址，再到MYD文件中查到需要的数据。

---

        InnoDB索引（聚集索引）

1.  表数据文件本身就是按照B+tree组织的一个索引结构文件
2.  聚集索引：叶子节点包含了完整的数据记录

        我的理解是：索引与数据文件是分开存储的即非聚集索引， 聚集索引中的索引和数据存储在同一个叶子节点，即存储在一起，不需要二次查找（回表操作）。

---

 2.2 主键索引和二级索引...

        

1.  主键索引中叶子节点存储的是数据行的所有数据，也就是我们理解的聚集索引。
2.  二级索引中叶子节点存储的是主键索引，需要回到主键索引（即回表）查询出数据行的所有数据，即非聚集索引。这样设计的目的是为了“数据的一致性和节省存储空间”。
3. 二级索引的查询效率在大多数场景下是高于主键索引的查询效率的。（本文不作阐述）

---

2.3 联合索引 - 索引最左前缀原则      

        KEY `id_name_age_position` (`name`, `age`,`position`) USING BTREE

     联合索引会根据索引的先后顺序（name>age>position）进行排序维护在B+tree的数据结构。