按在地上摩擦面试官之mysql特性(精讲)

原文地址

面试点：那你深入讲讲InnoDB存储引擎的关键特性？

➡插入缓冲 

 

要想深刻的理解InnoDB插入缓冲特性，我们需要对mysql的索引有比较深刻的理解。

  面试点：讲讲InnoDB聚集索引和非聚集索引的区别？

聚集索引和非聚集索引

 

聚集索引：聚集索引是指数据库表行中数据的物理顺序（磁盘上）与键值的逻辑（索引）顺序相同(目前先这样理解，其实说法是错误的，后面会解答)。换句话说就是索引的顺序和数据库里存的顺序一致。

在InnoDB中，表中的数据都是按照主键顺序存放。而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。同B+树结构一样，每个数据页都通过一个双向链表进行链接。

由于实际的数据结构只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引

优点：对于主键的排序查找以及范围查找速度都非常快

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，也称为辅助索引。

 

对于辅助索引，叶子节点并不包含行记录的全部数据，叶子节点除了包含键值（辅助索引的键值）以外，每个叶子节点中的索引行中还包含了一个书签，该书签用来高速在InnoDB存储引擎那里可以找到与索引相对应的行数据（其实就是主键索引），通过主键索引再去找到具体的行数据。

插入性能

 

当设计一个数据表的时候，一般都有主键而且主键是自增（AUTO_INCREMENT）的，通常插入的索引都是连续的，也就是我们说的聚集索引，聚集索引的好处就是一般数据都是顺序存储的，如果你的sql读的是某一块连续的数据块，这样因为聚集索引的连续性，你不需要访问多个不同的数据页来访问数据，大大减少了IO，提升了查询速度。一般情况下，主键索引的插入也非常快，因为不需要离散的读取数据页。

（特殊情况）

🔹当插入时数据时候，但是主键是指定的值，不是NULL，可能会导致出现非连续的情况。那么极有可能和第一次插入的主键索引不在一个数据页上，这时需要额外的一次IO。

 

🔹如果主键类型为uuid的时候，也是非连续的。那么问题来了如果主键不连续了怎么办，或者一张表可能存在辅助索引。

 

🔹上面两种特殊情况，插入时跟辅助索引一样，效率也会很低。

插入缓冲Insert Buffer

 

插入缓冲Insert buffer是mysql InnoDB存储引擎中一个比较令人兴奋的功能，乍一看名字buffer？难道是mysql缓冲池的一个组成部分？其实insert buffer和数据页一样也是物理页的一个组成部分。

 

对于非聚集索引（辅助索引）来说，在进行插入的时候可能不再是顺序的了，这时就需要离散地访问非聚集索引页，由于随机读取的存在导致了插入操作性能会下降。

 

注：并不是所有的辅助索引都是无序的（时间序列的索引）

 

对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若存在则直接插入，如果不在，则先放入到Insert Buffer对象中，然后以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge操作，这样减少了多次插入，提高对于非聚集索引的性能。

Insert buffer的同时满足两个条件

 

索引必须是辅助索引。如果是聚集索引，那就没什么要合并的必要了

 

不能是唯一索引因为插入缓冲的时候，mysql不会去判断数据的唯一性，如果要判断唯一性，就要离散读，失去了Insert Buffer的意义

 

总结：针对非聚集索引，合并插入操作，提高插入性能

面试点：innoDB存储引擎是怎样提高可用性的？

➡两次写

 

前面提到了提高插入的性能，这时会想到另一个问题就是插入时候宕机了怎么处理？

那么doulewrite提供了可靠性。

 

当InnoDB数据写入到磁盘是以Page为单位进行操作的，InnoDB的Page Size默认是16KB。16K的数据，写入4K时，发生了宕机，这意味着如果服务器宕机了，可能只做了部分写入，只有一部分写是成功的，这种情况下就是partial page write（部分页写失效）问题。

有些人可能会想到，如果发生写失效，MySQL可以根据redo log进行恢复。这是一个办法，但是必须清楚地认识到，redo log中记录的是对页的物理修改。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的。MySQL在恢复的过程中检查page的checksum，checksum就是检查page的最后事务号，发生partial page write问题时，page已经损坏，找不到该page中的事务号。在InnoDB看来，这样的数据页是无法通过checksum验证的，就无法恢复。

为了解决这个问题，InnoDB实现了double write buffer，简单来说，就是在写数据页之前，先把这个数据页写到一块独立的物理文件位置（ibdata），然后再写到数据页。这样在宕机重启时，如果出现数据页损坏，那么在应用redo log之前，需要通过该页的副本来还原该页，然后再进行redo log重做，这就是double write。double write技术带给innodb存储引擎的是数据页的可靠性，下面对doublewrite技术进行解析，让大家充分理解double write是如何做到保障数据页的可靠性。

double write体系结构:

 

double write由两部分组成，一部分是InnoDB内存中的double write buffer，大小为2M，另一部分是物理磁盘上ibdata系统表空间中大小为2MB，共128个连续的Page。

double write工作流程如下：

 

当一系列机制（main函数触发、checkpoint等）触发数据缓冲池中的脏页进行刷新到data file的时候，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的double write buffer，之后通过double write buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上。然后马上调用fsync函数，同步脏页进磁盘上。由于在这个过程中，double write页的存储时连续的，因此写入磁盘为顺序写，性能很高；完成double write后，再将脏页写入实际的各个表空间文件，这时写入就是离散的了。各模块协作情况如下图（第一步应为脏页产生的redo记录log buffer，然后log buffer写入redo log file，为简化次要步骤直接连线表示）：

double write的缺点?

 

dblwr位于共享表空间上的double write buffer实际上也是一个文件，引入了一次额外写的开销，每个数据页都被要求写两次。由于需要大量的fsync操作，所以它会降低MySQL的整体性能，但是并不会降低到原来的50%。这主要是因为：

 

🔹double write是一个连接的存储空间，所以硬盘在写数据的时候是顺序写，而不是随机写，这样性能更高。

 

🔹 将数据从double write buffer写到真正的segment中的时候，系统会自动合并连接空间刷新的方式，每次可以刷新多个pages。

double write默认开启，参数skip_innodb_doublewrite虽然可以禁止使用double write功能，但还是强烈建议大家使用double write。避免部分写失效问题，当然，如果你的数据表空间放在本身就提供了部分写失效防范机制的文件系统上，如ZFS/FusionIO/DirectFS文件系统，在这种情况下，就可以不开启doublewrite了

double write在恢复的时候是如何工作的？

 

如果是写double write buffer本身失败，那么这些数据不会被写到磁盘，InnoDB此时会从磁盘载入原始的数据，然后通过InnoDB的事务日志来计算出正确的数据，重新写入到double write buffer。

 

如果double write buffer写成功的话，但是写磁盘失败，InnoDB就不用通过事务日志来计算了，而是直接用buffer的数据再写一遍。如上图中显示，在恢复的时候，InnoDB直接比较页面的checksum，如果不对的话，Innodb存储引擎可以从共享表空间的double write中找到该页的一个最近的副本，将其复制到表空间文件，再应用redo log，就完成了恢复过程。因为有副本所以也不担心表空间中数据页是否损坏，但InnoDB的恢复通常需要较长的时间。

 

总结：当插入操作遇到问题时候，提高数据库可用性

➡自适应哈希

 

为什么要出现自适应哈希索引？

 

🔹 哈希是一种非常快的查找方法，一般情况下查找的时间复杂度是O（1）

 

🔹而MySql数据库中的索引B+树的一般高度为3~4层，所以如果用索引B+树查找的话一般要进行3-4次的查找

 

🔹 由于某些应用中可能会频繁的进行某一类的SQL操作，这些SQL操作甚至会访问相同的索引页，所以为了更快的对这些热点页进行查询，InnoDB存储引擎会为这些热点页建立哈希索引

哈希查找为什么是O（1）？

 

🔹哈希表是一种线性表，也就是我们常说的数组，也就是说存储的物理地址是连续的，可以直接通过index存取，并且数据存储的形式是<key，value>

🔹每次存取之前调用hash函数，hash函数会返回一个index，就可以直接定位到我们需要访问的数据，取出value

🔹hash函数的时间复杂度是O（1），这里给一个hashmap中的hash函数为例：
下面是hashmap中的hash函数，我们可以看到，其实操作就是进行一次计算

🔹 InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，就会建立哈希索引，所以称为自适应哈希索引（AHI）

🔹 AHI是通过缓冲池的B+树页来构造的，因此建立的速度很快，不需要对整张表构建哈希索引

🔹 InnoDB会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引

建立自适应哈希索引的要求

 

要求页的连续访问模式一定是一样的
1）WHRER A = XXX
2）WHRER A = XXX AND B=xxx

访问模式一样指的是查询的条件一样，若交替进行上述两种查询，则不会建立哈希索引

🔹要求以该模式访问了100次

🔹页通过该模式访问了N次，其中N = 页中记录 * 1/16

自适应哈希索引的过程

 

🔹在查找非聚集索引的时候，要遍历非聚集索引B+树，然后找到相应主键，再根据主键去聚集索引树查找

🔹当发现某一棵非聚集索引树中的某页已经成为热点页了，就可以对其建立哈希表，下次访问就可以直接通过哈希表查找

注意的地方

 

🔹哈希索引只能用来搜索等值的查询，不可以进行范围查找

🔹用户可以通过innodb_adaptive_hash_index来考虑是否禁用此特性

➡异步io

 

linux上，innodb使用异步IO子系统(native AIO)来对数据文件页进行预读和写请求。行为受到参数innodb_use_native_aio控制。
默认是开启的，且只是适用于linux平台，需要libaio库。在其他的类unix平台上，innodb使用的是同步I/O。

 

由于历史的原因，在windows平台上innodb只使用异步I/O。

在同步I/O情况下，查询线程将I/O请求放入队列，innodb后台线程会便利请求队列，每次处理一个请求。并行处理的请求个数受到后台线程的数量控制(参数innodb_read_io_threads)。

 

native AIO情况下，查询线程直接将I/O请求分发给操作系统，从而避免的后台线程数量对并发数的控制。innodb后台线程只需要等待操作系统对IO请求的处理反馈信息。

 

native AIO优点是可以扩展，对于I/O高的系统，通过show engine innodb status可以看到很多挂起的读写线程。磁盘控制器影响I/O性能。
native AIO的另一个优点就是可以进行I/O merge操作。

 

native AIO潜在的不足是，对于高I/O系统缺少对I/O写请求分发的控制。有些场景下，太多的I/O写请求分发给操作系统，可能会导致I/O读饥荒，这取决于系统可以同时处理I/O活动的能力。

➡刷新比邻页

 

刷新邻接页原理

InnoDB存储引擎还提供了Flush Neighbor Page（刷新邻接页）的特性。其工作原理为：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。

 

刷新邻接页优点

通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势。

 

刷新邻接页开启与关闭

开启与关闭需要考虑到下面两个问题：

🔹 是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变成脏页？

 

🔹 固态硬盘有着较高的IOPS，是否还需要这个特性？

为此，InnoDB存储引擎从1.2.x版本开始提供了参数innodb_flush_neighbors，用来控制是否启用该特性。对于传统机械硬盘建议启用该特性，而对于固态硬盘有着超高IOPS性能的磁盘，则建议将该参数设置为0，即关闭此特性.

 

大家好，我是山虎，喜欢数学，编码，算法，股票，AI。经历过一次失败的创业。东西不要死记硬背，要做到自己真正的理解。年轻人就要折腾，年轻人就要折腾，年轻人就要折腾。原创不易，帮忙转发。

下一篇:   按在地上摩擦面试官之mysql索引(精讲)