索引到底是什么
索引是帮助MySQL高效获取数据的排好序的数据结构
索引存储在文件里
索引结构
磁盘存取时间
- 寻道时间（速度慢，费时）
-
旋转时间（速度较快）
image.png
image.png
以下数据结构作为索引结构的问题
二叉树 数据向一方偏离
红黑树 虽然数据较二叉树树形能翻转保持平衡，数据大量的时候，数据深度会很大
HASH 能快速定位到某一行，却无法解决查询范围的问题
局部性原理与磁盘预读
为了提升效率，要尽量减少磁盘IO的次数。实际过程中，磁盘并不是每次严格按需读取，而是每次都会预读。磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中，这样做的理论依据是计算机科学中注明的局部性原理：
当一个数据被用到时，其附近的数据也通常会马上被使用
程序运行期间所需要的数据通常比较集中
（1）由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，
因此对于具有局部性的程序来说，预读可以提高I/O效率.预读的长度一般为页(page)的整倍数。
（2）MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K(这个值可以修改)。linux 默认页大小为4K。
一般来说有多少层高（数据深度）就有多少次IO耗时操作，减少层高非常有必要
BTree
- 度(Degree)一节点的数据存储个数
- 叶子节点具有相同的深度
- 叶子节点的指针为空
-
节点中的数据key从左到右递增排列
注意：由于计算机硬件限制，度(Degree)无限增大，并不能减少IO次数
image.png
B+Tree(B-Tree上优化)
- 非叶子节点不存储data，只储存key，
可以增大度(Degree) - 叶子节点不存储指针
-
顺序访问指针，提高区间访问的性能
image.png
B+Tree索引的性能分析
- 一般使用磁盘I/O次数评价索引结构的优劣
- 预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存
- 局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用
- B+Tree节点的大小设为等于一个页，每次新建节点直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，就实现了一个节点的载入只需一次I/O
- B+Tree的度d一般会超过100，因此h非常小(一般为3到5之间)
为什么mysql的索引使用B+树而不是B树呢？？
（1）B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
（2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。
MyISAM索引实现(非聚集)
MyISAM索引文件和数据文件是分离的
image.png
image.png
有些 MySQL 版本还缺乏完整的存储过程支持 — 意味着不支持事务，这是 MyISAM 系统的最大缺点。
InnoDB索引实现(聚集)
- 数据文件本身就是索引文件
- 表数据文件本身就是按B+Tree组织的一个索引结构文件
- 聚集索引-叶节点包含了完整的数据记录
为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
首先，为了满足MySQL的索引数据结构B+树的特性，必须要有索引作为主键，可以有效提高查询效率，因此InnoDB必须要有主键。如果不手动指定主键，InnoDB会从插入的数据中找出不重复的一列作为主键索引，如果没找到不重复的一列，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；
其次，索引的数据类型是整型，一方面整型占有的磁盘空间或内存空间相比字符串更少，另一方面整型比较比字符串比较更快速，字符串比较是先转换为ASCII码，然后再比较的。
最后，B+树本质是多路多叉树，如果主键索引不是自增的，那么后续插入的索引就会引起B+树的其他节点的分裂和重新平衡，影响数据插入的效率，如果是自增主键，只用在尾节点做增加就可以。
- 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
主键索引和非主键索引维护各自的B+树结构，当插入的数据的时候，由于数据只有一份，通过非主键索引获取到主键值，然后再去主键索引的B+树数据结构中找到对应的行数据，节省了内存空间；
如果非主键索引的叶子节点也存储一份数据，如果通过非主键索引插入数据，那么要向主键索引对应的行数据进行同步，那么会带来数据一致性问题。可以通过事务的方式解决，我们都知道使用事务后，就会对性能有所消耗。
image.png
image.png
联合索引结构
联合索引的底层存储结构长什么样？
定义联合索引(员工级别，员工姓名，员工出生年月)，将联合索引按照索引顺序放入节点中，新插入节点时，先按照联合索引中的员工级别比较，如果相同会按照是员工姓名比较，如果员工级别和员工姓名都相同 最后是员工的出生年月比较。可以从图中从上到下，从左到右看，第一个B+树的节点 是通过联合索引的员工级别比较的，第二个节点是 员工级别相同，会按照员工姓名比较，第三个节点是 员工级别和员工姓名都相同，会按照员工出生年月比较。
image.png
网友评论