MySQL索引开始篇——与回表

整篇开局就需要先引入题外话，一个是操作系统中的局部性原理和磁盘预读
*

局部性原理

编写良好的计算机程序，它们倾向于引用邻近于其他最近引用过的数据项的数据项，或者最近引用过的数据项本身。局部性又分为时间局部性和空间局部性。
		--时间局部性：
		时间局部性是指如果程序中的某条指令一旦执行，则不久之后该指令可能再次被执行；如果某数据被访问，则不久之后该数据可能再次被访问。强调数据的重复访问。

（直白点就是，你最近执行过的程序或者访问的数据，再次执行和访问的概率比其他未执行过的程序高的多。<行为学？>）
　　 --空间局部性：
　　 空间局部性是指一旦程序访问了某个存储单元，则不久之后。其附近的存储单元也将被访问。强调连续空间数据的访问，一般顺序访问每个元素（步长为1）时具有最好的空间局部性，步长越大，空间局部性越差。
　　 （你访问了这条数据，旁边的数据最近你也会访问）

磁盘预读

	由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。 （这样做的理论依据就是局部性原理：当一个数据被使用到时，其附近的数据也通常会马上被使用）
	磁盘在顺序读取的时候效率比较高（不需要寻道时间，时间很少的旋转即可）
	预读的长度是为页的整数倍（通常也就是4k大小）,‘页’是计算机管理存储器的逻辑块，硬件和操作系统会将主存和磁盘存储区分为等大连续的小块块，每一个小块就是称一页。主存和磁盘就是已页作为单位进行交互数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

----------------------------------------------既然了解了上面这俩个，那么就可以引出MySQL的索引了
嘛是索引，这是我们需要先明确的
索引可以看做是一个目录，可以加快数据库中指定数据的一个定位。
也就是索引对于MySQL的高效运行很重要，索引是可以提高检索速度的

存储引擎

从sql语句的发送到响应返回：

在存储引擎中，不同的存储引擎，它们的数据文件和索引文件存放的位置是不同的，因此有了下面的分类
聚簇索引：存储引擎为innodb的时候，索引和数据是在一起的。
注意：在innodb存储引擎中默认会把所有数据文件放到表空间内，不会为每一个单独的表保存数据文件，需要将表单独用文件保存的话，需要设置属性： set global innodb_file_pre_table = on;
.frm：存放的是表结构
.ibd：存放了数据文件和索引文件
非聚簇索引：索引与数据都是独立的文件，在MyISAM存储引擎中
.frm： 存放了表及结构
.MYI：存放了索引数据
.MYD：存放了表中实际存储的数据

MySQL数据结构的选择

-------------------------------------下面就开始说说为什么索引采用了B+树
这可有得聊了、您且倒杯茶，听我慢慢道来

存放数据就定然离不开数据结构。
哈希表（散列表）
我们可以肯定，索引定然是唯一的不可重复的。自然而然想到会用哈希表来存放这些数据
哈希表呢，是可以完成索引的存储，每次在添加索引的时候需要计算索引列的哈希值，取模运算找到下标，将元素也就是data插入到下标位置即可。
哈希表是很适合等值查询的一种数据结构，但是表中的数据是无序数据，那么会导致范围查找的时候比较浪费时间，需要进行遍历操作。
hash表在使用的时候，就需要将全部的数据都加载到内存中，比较消耗内存的空间

树

二叉树（Binary Search Trees）
二叉树的存储结构呢，虽然也不错，但是顺序存储索引的话，就会导致不平衡，一边的树深（deep）会过深，那么不就又变回了链表的形式，查询的时候还是会遍历所有的数据进行匹配。不合适
AVL树（平衡二叉树）
AVL树是一颗严格意义上的平衡树，因为左右子树的高度差不能超过1，所以在插入元素的时候就会进行 1次或者N次的自旋操作，会严重的影响插入的性能。还是不合适
红黑树（Red-Black Trees）
红黑树是基于AVL树的一个升级，损失了部分查询的性能，因为红黑树允许最高子树和最低子树之差小于2倍就行。在插入的时候不需要再进行N多次的旋转操作，并且加入了变色的特性，满足插入和查询的性能平衡。但是呢，如果进行了更新操作，就会变得很麻烦
而且树深过深会造成io次数的变多，影响数据库的去读效率。

二叉树以及其变种都不能支持索引，原因是树的深度无法控制或者插入数据的性能比较低。
那就回到最原本的多叉树（B树）这种结构试试咯

B树
1、所有的键值都分布在整颗树中
2、搜索有可能会再非叶子结点结束，在关键字全集内做一次查找的性能逼近二分查找的效率
3、每个节点可以有m个子树
4,、根节点至少有2个子树
5、分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
6、所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列

说明：一个磁盘块中包含了三部分，指针指向的是范围内下一个磁盘块的位置。键值：表里面的主键值，数据就是行里剩下的数据。
比如查找关键字75：
1、从根节点找到磁盘块1，读入内存。 {磁盘I/O一次}
2、根据比较发现75是在34右侧，那么找到磁盘块1中的指针p3
3、根据p3指针找到磁盘块4，读入内存、看看磁盘块是否有需要的数据，找到就返回，没有就继续往下找{磁盘I/O二次}
4、比较关键字75是在区间（64/88）之间，找到磁盘块4的指针p2
5、根据p2指针找到磁盘块10，读取到内存{磁盘I/O三次}
6、在磁盘块10中找到关键字75，根据键值找到对应的data数据返回
只进行了三次的i/o读取，每个磁盘块只读取了4k的数据，效率上很快了，但是每个非叶子节点的磁盘块中包含了data数据，而每个页的存储空间是有限的，data数据的大小又无法确定大小，如果data数据较大会导致每个节点存储的key数量变小。导致存储的深度变大，增大了查询时磁盘I/O次数，影响了查询性能。

在B树（B-树）的结构基础上进行改变，引出了现在MySQL现在使用的索引数据结构----B+树

B+树（重点来了）

在B树的基础上进行改变，有一个思路就是既然是因为每个数据块中存放data数据大小无法确定，那么就解决掉！

mysql索引数据结构–B+Tree
B+Tree是在BTree的基础上做的一种优化
1、B+Tree每个数据块上可以存放更多的节点了
–这么做降低了树的高度
–将数据范围也变为了多个区间，区间越多，数据检索越快
2、实现1的做法，就出现了再非叶子节点只存储key，叶子节点上存储key和数据
3、叶子节点中会两两指针相互连接，这么做也符合了磁盘 预读特性，顺序查询的性能也变得更高

注意：在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

–查找数据依旧是三次I/O操作，但是从数据量上可不是一个级别了。这种方式的查询可以支撑了百万级别（看到这里我是跪了，佩服这思路）

回表
在索引的创建上，一般是在唯一键上，普遍是主键创建索引。当然了，哪怕没有设置主键，也会生成一个看不到的rowId这个列。也是为了方便数据的查询
正常的从主键这种唯一数据查询，只需要从头结点找到范围，再找到值得的叶子节点中的数据返回即可。
注意：是innoDB的存储引擎中叶子节点存放的key和data数据

一些情况下会在一些普通信息列中创建索引。这样就会出现回表的现象。
比如我再name列中创建了索引，就会生成对应的索引数据，这个索引数据就变的很奇妙了，那么在叶子节点中存储的就是该记录的主键，然后再通过主键的索引找到对应的记录。
以上就是回表的现象…

但是回表这个现象，值存在于Innodb索引引擎中，在MyISAM中没有。因为MyISAM中B+树的叶子节点存放的是地址值，会直接根据地址值到MYD中找到对应的数据返回。也就没有回表了