了解MySQL一些内部的技术实现

MySQL索引原理

索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500页是目录，它当然效率低，目录是要占纸张的,从而索引是要占磁盘空间的。

磁盘预读原理

计算机中计算和处理数据都是靠CPU，也就是中央处理器，在计算机中一般分为数个层次：依次为

寄存器：比如64位(16个寄存器)，是CPU的工作台，物理存放在CPU内所以不需要IO
一级缓存L1 4×64KB
二级缓存L2 4×256KB
三级缓存L3 8MB
内存 4GB
磁盘 1TB

CPU工作时所用的数据或者地址先从一级缓存里面找，找不到就从二级缓存里面找，依次类推。假如CPU到磁盘才有，那么这个数据就会存入内存，再存入三级缓存、二级缓存、一级缓存，最后存入寄存器，CPU再进行计算，这些模块读写数据的速度在内存中是非常快的，主要的处理时间消耗在和磁盘的I/O

磁盘读取数据依靠机械操作，物理磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动(各个磁盘须同时转动)。磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不动，磁盘转动，但磁臂可以前后动，用于读取不同磁道上的数据。磁道就是以盘片为中心划分出来的一系列同心环。磁道又划分为一个个小段，叫扇区，是磁盘的最小存储单元

磁盘读取时，系统将数据逻辑地址传给磁盘，磁盘的控制电路会解析出物理地址（哪个磁道，哪个扇区），于是磁头需要前后移动到相应的磁道——寻道，消耗的时间为磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；磁盘旋转将对应的扇区转到磁头下（磁头找到对应磁道的对应扇区），消耗的时间也就是旋转延迟，就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计，那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，为了尽量减少I/O操作，计算机系统一般采取预读的方式，预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。

计算机系统是分页读取和存储的，一般一页为4KB（8个扇区），每次读取和存取的最小单元为一页，而**磁盘预读时通常会读取页的整倍数。**当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），所以即使只需要读取一个字节，磁盘也会读取一页的数据

IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段类型要尽量的小，比如int占4字节，要比bigint8字节少一半。

索引的数据结构：B+树

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），索引是基于数据表创建的，对创建索引的列的值记录对应的地址，并且把这些值存储在一个数据结构中用于提高查询效率，它们包含着对数据表里所有记录的引用指针

InnoDB的数据文件本身就是索引文件，MyISAM的索引文件仅仅保存数据记录的地址，MyISAM索引文件和数据文件是分离的；默认数据与索引文件位置: /var/lib/mysql

MyISAM引擎的文件：.myd存储data，表数据文件； .myi存储index索引数据

InnoDB系统表空间文件：ibdata1、ibdata2等存储InnoDB系统信息和用户数据库表数据和索引，所有表共用
InnoDB单表表空间文件：.ibd，每个表使用一个表空间文件，存放用户数据库表数据和索引

InnoDB一个page的默认大小是 16 k(4kb(磁盘一页的大小) + 12kb(预读三个页面) = 16kb)，可以通过innodb_page_size设置；由于是Btree组织，要求叶子节点上一个page至少包含两条记录（否则就退化链表了），所以一个记录最多不能超过 8 k，又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过 4 k；

在Innodb中一张表对应一个聚集索引，而聚集索引元数据中指定了root page的页号，因此Innodb引擎可以根据页号和页大小计算出索引B+树root page的准确地址，从而对整个表数据进行操作，每个页都有一个对应的磁盘地址，也可以叫页号，因为表空间的数据文件会被划分成大小相等的页，所以知道页号，再根据文件的初始位置，就可以计算出页在磁盘中的准确地址

索引IO的每一页都会存储key和指针，指针很显然会存储指向的page页号，如果是B树则key还会存储对应data，所以查询首先找到root页，从对应的指针去IO下一个page，依次找到最终的数据

hash索引：只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

全文索引：用于MyISAM和InnoDB引擎，使用普通索引只匹配文本前几个字符，但是要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引，5.7之后使用ngram插件可以支持中文

B-Tree：B-Tree不是B减树，类似1-1，B+Tree是B树的一个变种，B树是一个有序数组+平衡多叉树，关键字集合分布在整颗树中，任何一个关键字出现且只出现在一个结点中，搜索有可能在非叶子结点结束，其搜索性能等价于在关键字全集内做一次二分查找，性能不稳定

B树的每个节点可以存储多个关键字及对应的行数据，B树的查询相较于平衡二叉树的查询，因为预读的原因主要发生在内存中；红黑树就是一个平衡二叉树，通过对任何一条从根到叶子的简单路径上各个节点的颜色进行约束，确保没有一条路径会比其他路径长2倍，因而是近似平衡的；红黑树每个节点下只有两个子节点，而硬盘IO时是按簇读取的，两个节点中的值可能不够填满簇导致每次IO的浪费，此时红黑树的高度会大于B+树，导致IO次数增加

以InnoDB的B树为例：

查找过程(B-tree)：以查询15为例，首先从表元数据中得到磁盘page编号，IO后把获取的root page磁盘块1加载到内存，在内存中用二分查找(折半查找)确定15在17之前，锁定磁盘块1的P1指针，通过磁盘块1的P1指针的磁盘地址(page页号)把磁盘块2由磁盘加载到内存，发生第二次IO，15在12之后锁定磁盘块2的p3指针.通过指针加载磁盘块7到内存，发生第三次IO，同时内存中做二分查找找到15，结束查询，总计三次IO.

浅蓝色的块是一个磁盘块，每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块

B+树：是一个有序数组链表+平衡多叉树，所有关键字都在叶子结点出现，非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储，B+树的分支结点仅仅存储着关键字信息和子节点的指针（磁盘块的偏移量），也就是说内部结点仅仅包含着索引信息，每个节点不保存数据，数据都保存在叶子节点，查询性能稳定

聚簇索引：就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中

MyISAM和InnoDB都默认使用B+Tree，在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，而且叶节点的data域存放的是数据记录的地址

聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页；非聚集索引，叶节点存储的是主键索引，然后再通过主键索引来找到一个完整的行记录

主键索引也就是聚集索引，可以不用再次查询，因为主键索引与数据放在一起；普通索引需要再次查询主键索引之后再得到数据；从存储角度来说，根节点都只存储Key键值，Index索引值，非聚集索引叶子节点存储的是Index索引值和主键指针，而聚簇索引的叶子节点包括Key键值，Index索引值，Data数据；

如果建表时不指定主键，数据库会拒绝建表的语句执行，一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，整个表就变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

非聚簇索引：常规索引就是非聚集索引，又叫二级索引

索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引，因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到‘表’的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。

回表：在InnoDB中主键就是聚簇索引，其叶子节点保存着数据，而普通索引叶子节点存储的是所有索引的数据，这就是回表；如果没有定义主键，那么该表第一个非空唯一索引就是聚簇索引，如果也没有非空唯一索引，那么内部会生产一个隐藏的索引；但myisam引擎中，主键就不是聚簇索引，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复

覆盖索引：有一种例外可以不使用聚集索引就能查询出所需要的数据，当为字段建立索引以后， 字段中的内容会被同步到索引之中， 如果为一个索引指定多个字段(建立复合索引)， 那么这多个字段的内容都会被同步至索引之中，例如 select name from table where sex=‘男’，原先执行流程就是先通过非聚集索引找到sex='男’的所有数据的主键ID，然后通过主键id去执行聚集索引查找最终结果；

那么覆盖索引的方式就是创建name和sex的组合索引