MySQL索引深入解析
目录
一、索引数据结构（二叉树，红黑树，B树，B+树，Hash）
1.二叉树：如果用二叉树去存储索引，那么 key 记录的是索引字段的值，value 记录的是索引所在数据行的磁盘文件的地址。假如我们不建立索引，直接去查询，如果要查询 Col2 字段值为 89 的数据，那么正常需要进行磁盘交互（I/O）次数 6 次才能查询到；如果使用二叉树作为索引结构（二叉树的特点是右边的元素大于其父元素，左边的元素小于其父元素），那么仅仅需要 2 次查询就可以查询到
优点：相对于没有索引的字段查询效率会高一些
缺点：如果是在递增的字段比如 Col1 上去建立二叉树结构的索引，也会带来一些问题：虽然递增的字段建立的索引是二叉树结构，但更像是一条链表，我们去查询 Col1 为 6 的值仍然需要去查询 6 次。所以在递增字段上使用二叉树结构作为索引实质上没有作用
2. 红黑树（Red Black Tree）：也叫二叉平衡树，在一侧数据比较大的时候，红黑树会自己做一次平衡调整
优点：解决了普通二叉树建立自增字段索引引发的链表问题
缺点：当数据量很多的时候，几百万甚至上千万的时候，红黑树结构的索引高度会非常高，那么如果需要的查询的数据在叶子节点，那么需要查询的次数就会偏大
3. B 树：相对于红黑树，B 树横向拉长了索引存储的空间（相当于一个大的节点里面有很多小的节点，这些小的节点就相当于key-value键值对，key是索引元素，value是data元素，data元素就是索引所在行的磁盘文件地址），从而缩短树的高度。
特点：
1）叶节点具有相同的深度，叶节点的指针为空
2）所有索引元素不重复
3）节点中的数据索引从左到右递增排列
4. B+树（B 树的变种）：mysql 采用的索引数据结构，我们可以看到相对于 B 树，B+树将非叶子节点的 data（索引所在行的磁盘文件的地址或者索引所在行的所有其他列）全部放到了叶子节点上，也就是说 B+树的叶子节点存储了这个字段的所有索引元素和这个表的所有字段；而非叶子节点就是存储这个节点的子节点（磁盘页，相当于一块比较大的放索引的空间）中的第一个索引元素和每个子节点所在磁盘文件的地址。
特点：
1）非叶子节点不存储data，只存储冗余索引，可以放更多的索引
2）叶子节点包含所有索引字段
3）叶节点相邻之间用指针连接，便于范围查询和排序，提高区间访问的性能。B+树的叶子节点之间存在指针关系且是排好顺序依次递增的，在进行范围查询的时候通过指针很容易定位下一个磁盘页的磁盘位置
5.Hash：存储索引的时候会对这一索引列的值进行一次 Hash 算法，得到的结果定位到一个 Hash 桶里，确定了数据存储的位置，比如 Alice 在进行 Hash 运算之后得到的散列值是 2，就会在 Hash 桶2 的位置存储这个列的值（可能会存在多个相同的散列值---- Hash 碰撞）以及 索引所在行的磁盘文件地址。 在根据字段进行查询的时候，会先对值进行依次 Hash 运算得到的散列值去 Hash 桶里找，找到匹配的散列值，之后如果有多个那就遍历这个链表一直到匹配上.
问题来了，我们很可能只需要 IO 一次就能定位到我们所需要的元素，而 B+树有时候需要 2-3 次，效率在某种情况下可能比 B+树高，但为什么不会采用 Hash 呢？
缺点：
1）仅能满足“=”，“IN”，不支持范围查询；
2）hash冲突问题
二、千万级数据表如何用 B+树索引快速查找
在实际的查询中，耗费性能主要是 I/O 操作（将磁盘页放入 内存（RAM）中），在内存中对磁盘页去进行比对数据的时间可以忽略不计。
那么问题来了，我们为什么不将索引数据放在一个磁盘页中？
如果数据量几千万，索引几千万个同时加载到内存中，会直接干爆内存，而且这样去查数据效率也不大。
mysql 建议的一个磁盘页的大小为：16384 字节，大约 16KB（SHOW GLOBAL STATUS like ‘Innodb_page_size';这条语句可以查看）。
为什么用 16kb， 假如我们某个索引字段是 bigint 类型（8 字节），在 C 语言底层，存储地址分配的空间是 6 字节，那么 16k 可以放 16KB/（6+8）B = 1170，也就是一个磁盘页能放 1170 个索引；叶子节点会特殊一点，data 如果存储的是索引所在行的所有其他列的值，顶多 1KB 左右（不存储大数据，字段数量不是成百上千个），所以一个叶子节点磁盘页可以放 16 个索引；假设存储高度为 3，那么算出来就是 1170*1170*16 = 2190 2400，足以存储2000 多万个索引，也就是2000 多万个数据。两千万的数据磁盘IO只需要 3 次（一般 mysql 会把根节点放进内存，作为常驻内存，比较高的MYSQL版本会把所有的非叶子节点都作为常驻内存，那么IO次数还会减少）
mysql 为什么选择 B+树？
B+树存储 2000 多万条数据只需要高度为 3 的树即可；
如果换成 B 树，B 树的非叶子节点也存储data 数据，也就是说一个磁盘页只能放 16 个 索引，那么需要16+16*16+16*16*16+16^n =2000w，n 就是树的高度，肯定远远超过 3。
树的高度取决于非叶子节点所储存的索引的数量，存储的越多，树的高度越小
如果数据量超过 2000 多万怎么办，那就分库分表或者就再加一层
三、MyISAM 和 InnDB 数据库存储引擎的区别
1. MyISAM： 如果表的存储引擎是 MyISAM， 那么这张表会产生三个文件，比如 A 库下一张表名为 test，那么会在 mysql 的安装目录下 data/A 下有三个文件（test.frm,test.MYD,test.MYI）；
frm： 存储的是数据表结构相关的信息
MYD：存储的数据
MYI：存储的索引
在查询的数据的时候，首先从 MYI 文件中定位索引元素，在匹配到索引元素之后获取到 data 数据行所在的磁盘地址，拿着磁盘地址去 MYD 文件中定位这一行数据。
2. InnDB：只有两个文件 frm文件 和 ibd文件，
frm： 存储的是数据表结构相关的信息
ibd 文件本身也是按 B+树组织的 一个索引结构文件，与 MyISAM 不同的是，叶子节点的 data 存储的不再仅仅是数据的地址也有可能其他列数据的值，也就是索引存储的区别
总结：MyISAM 的索引文件和数据文件是分离的（非聚集（簇）索引），InnDB 的叶子节点包含了完整的数据记录（聚集索引），聚集索引的查询效率就会略高于非聚集索引。
四、为什么推荐使用整型自增主键做索引
为什么要建主键？
因为有了主键，那么 mysql 会根据这个主键去维护整张表的索引数据形成上图中的 B+树，如果不建主键，那么 mysql 会在所有这张表的所有字段中查找一列数据不重复的字段作为索引去维护索引结构；那么如果没有符合情况的字段，mysql 会自增一个隐藏列，类似于 rownum 去维护这个索引结构。 这样可以减少 MySQL 的工作；
为什么要用整型？
因为我们在查询数据的时候，B+树索引结构中会经历很多次比较大小，那么整形比较大小会更快一些（相对于 UUID 字符串，字符串比较大小还需要按照每一位的 ASCII 码去比较）；整型比字符占用的空间也会小一些（节约磁盘空间）
为什么要自增？
mysql 在维护索引的时候 会自动帮我们排序，如果不是自增的，mysql 在维护索引树的时候会导致磁盘页的分裂和整体 B+树的自己平衡；如果是自增的，在磁盘页满了之后会继续开辟一个新的空间作为下一个磁盘页
五、聚集索引&非聚集索引&联合索引的数据结构
1. 聚集索引：也是聚簇索引，inndb的主键索引
2. 非聚集索引（非主键索引/辅助索引/二级索引/稀疏索引）：
叶子节点存储的是辅助索引的值和聚簇索引的值，不存列数据是为了节省存储空间和保证一致性（假如插入数据的时候，那么两颗树都得去维护整张表的数据，但凡没有一个没插成功就会出错，减少复杂度）。在利用它进行查询的时候，定位到叶子节点的主键之后还需要再去主键索引的树去查询一下（回表）
3. 联合索引（复合索引）：即多个字段共同存储成一个索引，按照最左边第一个字段依次进行比较大小去排列成树
六、MySQL 最左前缀优化原则的原理
索引最左前缀原理：用联合索引的条件就是查询的时候第一个字段必须要查询，否则用不了，以下三条查询语句只有第一条能使用到联合索引
为什么呢？因为联合索引的 B+树排序的时候就是按照字段从左到右的顺序去排列的
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
