【MySQL】 索引(上)

1. 索引的概念

索引：提高数据库的性能 只需执行 正确的 create index ，查询速度就可以提高 百上千倍
但查询速度的提高，是以 插入 、更新、删除 的速度为代价的，增加了大量的IO

索引的价值 ，在于提高一个海量数据的检索速度

索引是 在内存中 以特定的数据结构 组织的 一种结构

2. MySQL与磁盘 的交互基本单位

mysql 是一个应用层软件，在系统角度 是一个应用进程，在网络角度 属于一个应用层的服务
磁盘与操作系统之间 ，是按照4KB为单位进行IO交互

为了提高IO效率，所以MySQL的IO基本单位 是16KB
但按照常识来说，MySQL是应用层服务 是不可能访问硬件的
所以16KB是站在MySQL角度，向操作系统提出来的，操作系统内部存在文件缓冲区的
由MySQL向文件缓冲区发送16KB数据，再由文件缓冲区将数据以4*4KB的方式发送给磁盘

3. 建立共识

1. mysql中的数据文件，都是以page为单位(16KB)保存在磁盘中
   (msyql上创建数据库基本上都是 linux 上的文件，而linux上文件需要保存在磁盘中)

2. mysql的增删查改操作，都是需要进行计算的，找到对应的插入位置
   而涉及到计算，就必须有CPU参与，为了便于CPU参与，一定要将数据移动到内存中
   需要先由磁盘把数据 搬到操作系统内部，在搬到mysql自己的缓冲区 buffer pool内部，用mysql自己的代码对缓冲区进行增删查改
   所以数据一定是磁盘中有，内存中也有，当操作完内存数据后，以特定的刷新策略，刷新到磁盘
   (在buffer pool 缓冲区中 将16KB的数据进行修改，写入 文件缓冲区中，再把对应的数据刷到磁盘中)

3. mysql服务器在内存中运行时，在服务器内部，就申请了被称为 buffer pool 的大内存空间
   来进行各种缓存，即很大的内存空间

4. 为了更高的效率，一定要尽可能减少 系统和磁盘的IO次数
   (一次IO数据量越大，IO次数越少，效率越高)

4. 现象与结论

如何理解mysql中page概念
1 page 大小只有16KB ，而硬件大小为512字节，所以mysql内部一定需要会存在大量的page,就决定了mysql 必须要将多个同时存在的page 进行 管理

要管理所有的mysql内的page，就需要先描述，在组织.page 不仅仅是 一个内存块，其内部还有写入 管理信息
就可以将所有的page 用链表 的形式管理起来

为什么 要采用page的方案 进行交互 而不是用多少加载多少？
多加出来的东西 ，本质是一种预加载 (提前将数据放入内存中)
预加载机制 可以有效 利用 局部性原理 ，有效减少IO次数
(page大小为16KB，传过来的数据只有10KB，剩余6KB的空间就会被提前把即将用的数据占用)

局部性原理：
把一部分数据预先加载到缓冲区里，提高整机的效率
如CPU正在访问第100行代码，未来有很大概率访问101行，
所以一旦访问到第100行就把100行附近的数据全部load到内存中

5. 页目录

为什么要引入 页目录概念

创建一张表 user，内部包含 约束为主键的id、不为空的age、不为空的name

插入时，是按照 3 4 2 5 1 进行插入的

但在查找是发现是有序的

向一个具有主键的表中 乱序插入数据 发现数据会自动排序
是谁做的，为什么这么做？

不同的page，在MySQL中，都是16KB，使用prev和next 构成双向链表
因为有主键，所以MySQL会按照主键 对数据进行排序
若没有主键，则插入的顺序 就是 查询的顺序

若表很多，则可能存在多个page
而当查询某条记录时，会将整个page 加载到内存中，就可以有效的减少IO次数

page与page之间用链式结构连接起来，而每个page内部的数据 也是基于链表的
若查找一条记录，则为线性查找，效率就会很低
所以就引入了 页目录 的概念

假设有一本C语言基础的书，想要找到指针章节
可以有两者寻找方法：
1.从头逐页向后翻，直到找到目标内容
2.通过该书的目录，从而直接找到对应的目标内容
查找目录的方案，是可以顺序找的，因为目录肯定比书的页数少，所以可以提高定位

单页情况

针对单页page 引入目录
牺牲一部分保存数据的空间，用来存储目录

该目录只有两个字段
第一个字段为 指向起始位置的key值
第二个 指针字段 为 指向记录的起始位置

假设查找 id为4的记录，在之前需要 线性 遍历 4次
而现在 则可以通过 目录2[3] ，直接定位新的起始位置，提高了效率

所以是为了方便引入目录，才使其有序的

多页情况

单个page大小是固定的，随着数据不断增大，16KB不可能存下所有的数据，必定会有多个页来存储数据

使用B+树 构建索引

虽然可以通过多个page 进行遍历， 再通过目录快速定位数据
但是也是有问题的，page内部的效率提升了，但page间依旧需要遍历

该数据结构为 B+树
自顶向下查找，只需要加载部分目录到内存，即可完整整个算法过程，减少了IO次数

在MySQL内 对事务 统一使用 B+ 树的方式 进行管理
整个B+树 称为 MySQL innode db下的索引结构
当建表时，就是在该结构下，进行增删查改

而整个B+树 是被构建在mysql的buffer pool缓冲区中

1.叶子节点会保存数据，而路上节点没有，非叶子节点 ，不存数据，只要目录项
非叶子节点不存数据，就意味着可以存储更多的目录项，目录页可以更多的管理更多的叶子page
这棵树 一定是 一颗 矮胖型的树，就意味着 途径上的节点 减少，找到目标所需更少的page ，IO次数也变少了

2.叶子节点全部用链表联起来
叶子节点全部用链表联起来，而非叶子节点不会用链表连接，这是B+树的特点
比较希望进行范围查找
(如：查找 20 -30 区间内的数据，只需找到20 和30 ，以20为起点 ，30作为终点，因为是有序的，所以只需遍历即可)

为什么不用其他数据结构

1. 链表是 线性遍历的，效率上是不满足条件的

2. 二叉搜索树 是 瘦高 型的 一颗树，相比于 B+树的矮胖型
   从叶子节点向下遍历时，会遇到很多节点 进行多次IO，就导致效率低下
   而且还存在退化问题，可能退化成线性结构

3. AVL和红黑树 虽然近似平衡 ，但相比于 B+ ，树的整体同样过高，都是自顶向下寻找，层数越高 ，IO次数越多
   所以还是选择 层数相对较低的 B+ 树 提高效率

4. 理论上hash 的搜索时间复杂度为O(1)，理论上是可以充当索引的
   但是hash 是没办法进行区间式的查找，会增加访问的成本

为什么采用B+树，而不采用B树？

B树 每一个节点 都对应一个配置，其配置内部 既包含 目录项 又包含数据
所以B树除了叶子节点包含数据外，路上节点也会包含数据，而且所有的叶子节点没有用链式结构连接起来

B+树 的 非叶子节点不存储 数据，数据全部都在叶子节点，并且所有的叶子节点全部都用链式结构连接起来

MySQL 认为 若在磁盘块中添加数据，就认为单个配置中 能够保存的目录项就会变少
(一个节点 既保存数据，又保存目录项， 其目录项肯定会比 只保存目录项的节点 少)

意味着 一个目录 所管理的子目录 变少了，所以 B树 相比较 B+树 会更高一些,IO的效率就会变低
( 拥有的目录项的数量是相同的，每个路上节点保存的目录项变少了， 经过的节点就会变多，整颗树就会变高)

B+树的叶子节点是链式连接的，所以只需找到对应范围的临界点位置 即可查询整个范围
而B树的叶子节点不是链式连接的，就意味进行范围查找时 每次都需要 重新遍历 B 树
所以对于范围查找，B树效率低下

6. 聚簇索引 和 非聚簇索引

将所有的数据全部放入 叶子节点 的存储引擎 为 innodb

除了 innodb的存储引擎 还有 MyISAM 的存储引擎

MyISAM 底层 采用 B+树 作为索引结果

叶子节点 存储的是 所对应的主键 记录的地址

通过B+树搜索 找到对应的数据，再根据数据对应的叶子节点 找到记录的地址

把 B+树 和 数据本身 分离的存储 方案 称为 非聚簇索引

MyISAM 特点为 将 索引 page 和 数据 page 分离 即叶子节点没有数据存在，只有对应的数据地址

在终端1中，输入 create database index_db 创建 index_db 库

创建 终端2， 使用 cd /var/lib/mysql ，进入该路径

当前路径下，存在 index_db目录

在终端1中的index_db库中，创建test1表，其中包含 约束为主键的 id、不为空的name，存储引擎为 innodb

在终端2中 发现index_db中 共有两个文件
test1.frm 为 表结构
test1.idb 为索引和数据结构