【【mysql知识点整理】知识点整理】 — mysql索引底层数据结构索引底层数据结构
文章目录文章目录1 为什么要用索引2 什么是索引3 简单说说HASH索引4 非HASH索引为什么选用的数据结构为B+树?4.1 为什么不是其他数据结构4.2为什么是B+树而不是B树呢?4.2.1 B树
数据结构4.2.2 B+树数据结构,以及为什么选择B+树4.2.3 一个错误的观点:B树和B+树的区别之一为B树的非叶子节点存储数据4.3 简单猜想:为什么索引中每个节点在内存中的地
址是随机的5 MySQL索引的体现形式5.1 MyISAM存储引擎5.2 InnoDB存储引擎5.2.1 索引的体现形式5.2.2 为什么InnoDB的非主键索引绑定的是该索引对应的主键值★★★5.2.3
InnoDB表创建主键应该注意什么5.2.3.1 首先应该明确,无论怎样InnoDB对应的表都有主键5.2.3.2 InnoDB对应的表建议使用整型自增主键5.2.4 InnoDB主键索引再思考 ★★★
1 为什么要用索引为什么要用索引
在实际业务中,当一张数据库表建好后,里面的数据往往是在不同时间点进行插入的,因此每张表里一行行的数据在磁盘中并不是按照顺序依次排列的。也就是说对于一张数据库表
而言,它每一行的内存地址都是随机的,如下图所示:
内存地址是随机的会带来什么问题呢???
相信每个人都知道,cpu在读取磁盘的数据时,都是按照N*页(64位系统,一页为4K)来读取的 — 即预读(之所以有预读是因为在磁盘上的数据通常遵循“集中读写”的原则,使用一些
数据,大概率会使用附近的数据,这就是所谓的“局部性原理”,它表明提前加载是有效的,确实能够减少磁盘IO)。但是这个N肯定不能很大,因为太大肯定就会影响读取效
率,mysql的默认大小为4,即每次读取16K的数据。
而由于表中每一行的内存地址都是随机的,那即使cpu读取数据时有预读,也会大概率无法一次读取到表中的多条数据。因此假如我们要查询上图中col2 = 23的那一行数据时,极端情况下
就要经历7次磁盘I/O:
(1)取出第一行的地址,经历一次I/O,拿着第一行的数据,判断col2是否为23
(2)再取出第二行的地址,经历一次I/O,拿着第二行的数据,判断col2是否为23
…
直到读取到最后一个地址,并查找的满足条件的数据。
也就是说在没有索引的情况下想查询某张数据库表里满足条件的数据,需要遍历整张表,并进行多次I/O。而众所周知的是I/O是非常消耗时间的,因此想要快速查询表中的数据索引
是必不可少的。
2 什么是索引什么是索引
人们一般会将索引比作书的目录。但是由于数据库表的行数往往会达到十万、百万的量级。因此索引假如仅仅只如书的目录一样,要想从十万、百万量级的索引中找到目标索引,再
根据目标索引找到真实的数据,也需要很多次I/O。
那到底什么是索引呢?mysql官方给出的定义如下:
索引是帮助MySQL高效的获取数据的数据结构。
这句话可以这样来理解:
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
下图就是一种可能的索引方式示例:
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址。
为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应数
据,从而快速的检索出符合条件的记录。
这里要注意的是:一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。 — 在介绍mysql存储引擎的时候还会再提及。
3 简单说说简单说说HASH索引索引
众所周知,mysql有两种索引:HASH索引和BTREE索引(BTREE索引的底层数据结构为B+Tree)。
HASH索引本文不过多去说,仅仅指出如下几点:
(1)如果键值唯一,哈希索引明显有绝对优势
(2)无法完成范围查询检索
(3)无法利用索引完成排序,以及like ‘xxx%’ 这样的部门模糊查询
(4)不支持多列联合索引
(5)因为会有哈希碰撞问题,所以当发生哈希碰撞时,查询效率会降低
4 非非HASH索引为什么选用的数据结构为索引为什么选用的数据结构为B+树树?
4.1 为什么不是其他数据结构为什么不是其他数据结构
mysql底层构建索引并没有采用第2小结示例中所说的二叉树,也没有采用对大家来说可能相对比较熟悉的平衡二叉树如:AVL树、红黑树。这是为什么呢?通过下面几张图相信大家
肯定会有所感悟:
数据库学习网站推荐:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html