MySQL实战45讲 笔记（4)

写在最前，本篇文章来源于对MySQL实战45讲的笔记，主要是为了帮助自己理解。如果同时还能对其他人有所裨益，那就更好不过了。如果有谬误的地方，还请不吝指出。

本文并非对文章的直接复制，并且肯定有理解不到位的情况，如果希望系统地学习，还是要去官网支持原作者。

注意：最好拥有一定的MySQL基础再来看本系列文章，可以去b站搜索动力节点的mysql基础教程，或者翻看我做的走进MySQL系列（笔记做的并不是特别详尽，仅作为参考）

索引的常见模型

一句简单的话来说，索引的出现就是为了提高查询的效率，像一本书的目录。

常见结构：哈希表、有序数组和搜索树。

哈希表
哈希表的优点是：查找和添加非常快。
缺点是：做区间查询很慢（由于并非有序），对于一个区间之内的数据必须一个个查找出来。

所以这种结果只适用于等值查询。

有序数组
有序数组在等值查询和范围查询场景下性能都不错

如果要查某一个值，用二分法就可以快速得到。
如果要查范围，先用二分法找到下界，再通过遍历找到右边界。

但在更新数据的时候比较麻烦，插入一个记录，需要挪动后面所有记录。
所以有序数组只适合静态存储引擎。

二叉搜索树
二叉搜索树的查询时间是O(log(N))
树可以有多叉，即每个节点有多个儿子。
二叉树搜索效率最高，然而实际上大多数数据库存储并不使用二叉树。

原因是，索引不止存在内存中，还要写到磁盘上。
一个平衡二叉树如果太高，比如20，一次查询可能要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10ms左右的寻址时间。对于一个100万行的表，如果用二叉树存储，单独访问一个行可能需要20个10ms的时间。

插入一些磁盘读取原理(内容来源于硬盘的读写原理详解)：
盘面：
磁盘的每一个盘片都有两个盘面，每个盘面都会被利用，每一个有效盘面都有有一个对应的读写磁头。
磁道：
磁盘在格式化时被划分为许多同心圆，这些轨迹叫做磁道，从外向内从0开始顺序编号。同心圆并非连续记录数据，而是划分为一段段圆弧。角速度一样，但线速度不一样。相同时间内，外圈划过的圆弧长度更大。每段圆弧称为一个扇区，每扇区的数据作为一个单元同时读取或写入。
柱面：
所有盘面上的同一磁道构成一个圆柱，称为柱面。磁头读写数据首先从同一柱面开始操作，只有同一柱面的所有磁头全部读写完成后，才转移到下一柱面。这是因为选取磁头只需要电子切换，而选取柱面则需要机械切换。一个柱面写满后，才移到下一个扇区写数据。
扇区：
信息以扇区的形式被存储在硬盘上，每个扇区包括512字节的数据。一个扇区包括标识符和数据段。

访问过程：

1. 找到柱面，即磁头移动到对应磁道，称为寻道。
2. 目标扇区旋转到磁头下，耗费时间称为旋转时间。

所以，一次访问过程由三个动作组成：
寻道、旋转、数据传输（数据在磁盘与内存间的实际传输）

预读：
由于磁盘存取速度很慢，为了提高效率，磁盘并非严格按需读取，每次都会预读，即使只需要1个字节，也会向后读取一定长度放入内存（局部性原理）。由于磁盘顺序读取效率很高，因此预读可以提高效率。
预读的长度一般为页的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

为了让一个查询尽可能少地读磁盘，就必须使得查询过程访问尽量少的数据块（即页），所以会尽可能地增加每个节点的子节点的数量。

数据库底层存储核心就是基于这些数据模型的，每遇到一个新数据库，要先关注它的数据模型，才能从理论上分析出数据库的适用场景。

在Mysql中，索引是存储引擎层实现的（包括事务也是），所以没有统一的标准。即使多个存储引擎支持同一类型索引，底层实现也可能不同。

InnoDB 索引模型

在InnoDB中， 表根据主键顺序以索引形式存放，称为索引组织表，使用了B+树索引模型。

Tips:

1. B+树与B树的差别是，B+树中的节点不存储数据，只是索引，而B树节点存储数据 。且B树的叶子节点并不需要链表来串联。
2. B+树的叶子节点存储的是值，包括了键值与指向数据行的地址。

每一个索引对应一棵B+树。索引类型分为主键索引和非主键索引。

主键索引的叶子节点存放整行数据，在InnoDB里，主键索引也被称为聚簇索引。

非主键索引的叶子节点内容是主键的值，在InnoDB里，非主键索引也被称为二级索引。

这两者查询的区别？

如果查询条件为主键，那么只需要搜索主键索引这个树
如果查询条件非主键，需要先搜索非主键的那颗树，得到主键的值，再到主键索引搜索，这个过程称为回表。

所以，应用中应该尽量使用主键查询。

索引维护

B+树为了维护索引有序性，插入新值时要做必要维护。

最糟糕的情况是，如果数据页已满，需要申请一个新的数据页，然后挪动部分数据过去，称为页分裂，影响性能。

除此之外，页分裂操作还影响利用率，原本放在一个页的数据，放在两个页中，空间利用率下降约50%。

当相邻页由于删除数据，利用率很低之后，会将数据页作合并，可以认为是分裂的逆过程。

为什么自增主键不会触发节点的分裂？
因为主键的值是顺序的，InnoDB把每一条记录都存储在上一条记录的后面，当达到最大填充因子时（默认页大小的15/16），下一条记录就会写入新的页中。

不使用自增主键的缺点？

1. 写入的目标页可能已经刷到磁盘上，并从缓存中移出（或者还未加载）。InnoDB必须先从磁盘读取目标页到内存中，导致大量随机I/O
2. 由于写入是乱序的，可能会进行频繁的页分裂操作（导致移动大量数据），并且
3. 由此带来稀疏页，并被不规则地填充，最终产生碎片

除了性能之外，还可以从存储空间的角度来看。
由于非主键索引的叶子节点上都是主键的值，如果主键的占用空间过大，会导致非主键索引空间占用过大。

有什么场景适合业务主键呢？

1. 只有一个索引
2. 该索引必须是唯一索引

有点像HashMap的K-V场景

由于没有其它索引，也就不需要考虑其它索引的叶子结点大小。

精选问题与评论

“N叉树”的N值在MySQL中是可以被人工调整的么？

1. 可以通过调整Key的大小来间接调整N值。N叉树中，非叶子节点存放的是索引信息，索引包含Key和Point指针，Point指针固定6个字节。通过页的大小和索引节点大小，可以计算出一个页可以有多少个节点。（当然还有页本身结构部分所占用的一些大小）
2. 改变页的大小，页越大，N就越大。

没有主键的表，仅有一个普通索引。怎么回表？
先找非空唯一索引，如果没有的话， innodb会给默认创建一个Rowid做主键