Mysql性能调优（一） Mysql索引数据结构详解

首先第一点我们要知道为什么要用索引？

因为表数据都是保存在磁盘中的，如果没有索引，我们查询时需要逐行的从磁盘中拿数据去与条件匹配，每次匹配都要进行一次磁盘I/O，性能比较差；而索引是帮助MySQL高效获取数据的排好序的数据结构，使用索引可以大大提升我们对表数据的过滤效率。

目录

索引数据结构：

二叉树

红黑树（二叉平衡树）

B-Tree

B+Tree(B-Tree变种)

Hash表

MyISAM存储引擎索引实现

InnoDB存储引擎索引实现

---

 

索引数据结构：

二叉树

1. 特点：
   1. 右边的支树大于父节点；
   2. 左边的支树小于父节点；

如下图：

1.  缺点：
   1. 如果索引列是递增的，根据二叉树的特点，二叉树结构将变成链表结构，所以二叉树不适用于递增的字段；

如下图：

红黑树（二叉平衡树）

1. 特点
   1. 包含二叉树的特点、；
   2. 在二叉树特点基础上，当单边树的高度大于3的时候会自旋做自动平衡；
2. 缺点
   1. 红黑树存储大量数据时，树的高度不可控（磁盘I/O次数不可控）

递增列结构如下图：

可以看到同样的递增列，如果结构为二叉树，查找6这个元素时要经过6次磁盘I/O；而红黑树只用3次，所以红黑树效率比二叉树效率要高

B-Tree

1. 特点：
   1. 叶节点具有相同的深度，叶节点的指针为空
   2. 所有索引元素不重复
   3. 节点中的数据索引从左到右递增排列
   4. 每个小节点下以KEY:VALUE形式存储，KEY为索引，VALUE分为以下两种情况
      1. InnoDB存储引擎下存储索引所在行的其他所有字段
      2. MyISAM存储引擎下存储磁盘文件指针
   5. 叶子节点没有指针连接
2. 缺点：
   1. mysql对一个大节点推荐给的总容量大概为16kb，如果在大节点总容量固定的情况下，索引表列多的情况下，会导致一个大节点下横向能存的索引个数比较少；表数据量过大的情况下，树的高度也将不可控；

递增列结构如图：

详细结构：

B+Tree(B-Tree变种)

1. 特点
   1. 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
   2. 叶子节点包含所有索引字段
   3. 叶子节点用指针连接，提高区间访问的性能（mysql中对B+Tree的指针做了优化，优化为双向指针，且末尾与开始节点也是双向连接的；该优化使Mysql在范围查询时不用每次回到根节点进行定位，提升了效率）

递增列结构如图：

详细结构：

上面有说过mysql对一个大节点推荐给的总容量大概为16kb，那么我们根据上图给出的数据计算一下高度为3的B+Tree的树能存储多少数据，非业节点存储索引 个数=16*1024/（6+8）=1170，3层数存储数据量=1170*1170*16=21902400；这说明千万级的数据只需要经过3次磁盘I/O即可匹配到（一般根节点保存在内存中，可能只要经过2次磁盘I/O即可），效率非常高。

Hash表

1. 特点
   1. 等值查询时，对索引值进行hash计算得到结果值，根据结果值在hash表中快速定位到索引所对应数据在磁盘文件中的磁盘指针（无关表数据大小）
   2. 不支持乏味查询

MyISAM存储引擎索引实现

1. 特点
   1. MyISAM主键索引文件和数据文件是分离的(非聚集)(MYD存储数据行、MYI储存索引)
   2. MyISAM会单独为表的主键创建一个B+树索引，只不过在B+树的叶子节点中存储的是索引值 + 磁盘指针（数据文件中索引所在行的磁盘指针）的组合

大概结构如下图

InnoDB存储引擎索引实现

1. 特点
   1. InnoDB主键索引和数据是储存在同一个文件中的(聚集)
   2. 表数据文件本身就是按B+Tree组织的一个索引结构文件
   3. 聚集索引-叶节点包含了完整的数据记录

主键索引结构：

辅助索引结构：

非主键索引中，为了保证数据的一致性和节省存储空间。叶子节点存储是主键值（每次查找都要遍历两棵树，先在辅助索引树中找到主键索引，然后再到主键索引树中找到完整的数据）。

问题：

1. 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
   1. 因为InnoDB表数据文件本身就是按B+Tree组织的，必须要有一个主键索引存储在B+Tree里组织data元素（数据行）；如果InnoDB表没有设置主键的情况下，Mysql会在表中找一行唯一索引作为主键索引，如果没有唯一索引，那么Mysql会在后台生成一个隐藏的唯一索引列rowid；
   2. 整型数据在作比较时性能好，非自增情况下，如果将一个值插入一个已满的节点中，将会导致节点分裂、整个树的平衡，如果是递增则是直接往后面加入避免节点分裂、平衡。
2. 联合索引的底层存储结构长什么样？
   1. 索引最左前缀原理（按索引字段顺序逐个比较）
   2. 结构如下图