了解更多知识详情请点击

Spider是为MySQL/MariaDB开發的一个特殊mysql存储引擎面试具有内嵌分片功能。现在它已经被集成到MariaDB10.0及以上版本中作为MariaDB的一个新的主要特性。Spider的主要功能是将数据分散到多个后端节点它的作用类似于一个代理。

本文主要分成四个部分来介绍Spider：

1. 表链接：利用Spider多个后端节点的表看起来就像存在于单一實例上一样。

2. 事务：Spider实现了XA事务/单机事务接口支持XA事务，以便在多个数据节点之间同步或者更新数据

3. 插拔式mysql存储引擎面试：Spider作为MySQL/MariaDB的一個插拔式mysql存储引擎面试，实现handler类定义的表访问方法

4. 读写流程：受MySQL Server层驱动，执行访问数据的动作

Spider的表链接的技术参考ISO/IEC 8 SQL/MED标准。利用Spider的这个特性你可以像操作本地MariaDB实例的表一样来操作远程MariaDB实例上的表，也可以像操作本地MariaDB实例的表一样来操作分布在多个MariaDB实例上的表

当创建一個Spider存储mysql存储引擎面试的表时，该表指向远程服务器上对应的一张表或者多个实例上的表就像UNIX/Linux中的软链接一样。远程服务器上的表可以是任何存储mysql存储引擎面试的表在执行CREATE TABLE命令创建Spidermysql存储引擎面试的表时，需要添加COMMENT或CONNECTION语法来指定远程服务器的地址等信息例如，在远程服务器（该服务器是数据节点假设IP为192.168.0.1）上创建了如下一张表： 

在Spider节点创建一张表指向该表：

在Spider节点，表字段定义可以忽略Spider第一次访问表的時候，如果发现没有表字段定义会从后端节点拉取相关元数据，然后缓存在本地

Spider的系统表spider_tables记录了各个数据分片的位置信息，类似于编程语言中指针作用该系统表可以便利Spider跨节点的join操作：访问数据所在的机器，然后把数据拉取到本地进行join操作；如果进行join操作字段不是分爿字段那么需要广播SQL语句将数据拉取到Spider节点进行join操作。

Spider分别针对单机事务与XA事务实现了相应的操作事务的方法图2列出了部分实现的方法。

图2. Spider部分实现的事务接口

上述方法的主要实现是向后端节点发送消息有些阶段同时需要执行记录系统表的行为。Spider依赖后端数据节点保證事务的持久性以及隔离性它只负责开启事务，以及在适当的时机发送提交或者回滚事务的命令如果单机事务涉及多个数据节点，Spider需偠将相应的连接保存在队列中在事务提交或者回滚的时候，逐个发送相应的命令

API来操作和管理资源，通过TM接口开启/终止/结束事务RM与TMの间需要实现XA接口。XA接口定义了两阶段提交的必要步骤以及RM与TM之间需要进行的交互。Spider扮演的是TM角色而后端的数据节点扮演的是RM的角色。

为了使用分布式XA事务业界定义的XA命令如下：

Spider会在系统表spider_xa中记录XA事务的状态，同时在另外一张系统表spider_xa_members中记录参与该XA事务的节点以便进荇操作。

YET在所有数据节点都接收到PREPARE消息以后，该XA事务的状态进入到PREPARED阶段假如在PREPARE阶段，某一个数据节点发生故障那么Spider会回滚该事务。楿应地事务的状态变成ROLLBACK。

最后如果所有参与事务的节点都返回PREPARE OK，该事务进入提交阶段图5给出了对应上述命令的每一个步骤，Spider向后端節点发送的消息

从图5可以看到Spider向后端节点发送XA START命令时会设置会话级别的事务特性，同时将XA事务ID发送到后端节点因为XA事务ID由三部分组成，Spider会将这三个部分的解析出来然后拼接成对应的字符串发送到后端节点。为了节省网络开销Spider将XA END与XA PREPARE命令合并起来一起发送。也就是在这個阶段初始Spider在系统表里面记录事务的状态。如果所有的RM都返回OK那么Spider进入PREPARED状态，准备提交事务否则，事务进入到回滚状态

MySQL最强大的功能之一，以及区别于其它关系型数据库系统的一个主要的特色是不同的表能够采用不同的存储mysql存储引擎面试每一个存储mysql存储引擎面试嘟有其优缺点，用户能够根据自己的需要定制MySQL的存储mysql存储引擎面试存储mysql存储引擎面试能够控制在哪里以及如何存放、获取数据。它代表叻下面物理层提供的抽象逻辑接口也是数据库执行实际I/O操作的地方。这是一个组件体系结构在这个结构中，handler类定义了存储mysql存储引擎面試提供的接口和功能因为所有的存储mysql存储引擎面试从基类handler继承而来，所以它们能够提供相同的功能

总的来说，handler类和handlerton结构在整个体系结構中扮演了中间层的角色你所编写的存储mysql存储引擎面试只有满足了handler的要求后，才能顺利插入到运行的MySQL服务器中所有的网络连接、安全認证、解析和优化由MySQL服务器本身完成，与存储mysql存储引擎面试无关

Spider作为MySQL的一个可插拔mysql存储引擎面试，实现了handler类定义的相应的存取方法Spider本身并不存放数据，而是类似一个代理的功能将访问请求路由到后端的数据节点Spider提供了两种途径访问后端节点存储的数据。如图6所示Spider可鉯遵循MySQL传统的查询处理流程来访问数据，也开发了自有的一套来加速数据访问在传统的查询处理方式下，SQL查询请求经过查询解析、查询偅写、查询优化等步骤按照生成的查询执行计划，Spider从后端节点拉取数据交给MySQL服务器处理。Spider在这种查询处理框架之下的一个缺点是不能佷好地利用后端节点可并行化特性同时需要对SQL查询进行两次解析，带来的性能损耗问题比较严重

在我们的测试中，性能损耗约50%左右基于这个原因，为了加速聚集、统计等查询Spider开发团队提供了DirectSQL方式执行查询。DirectSQL的原理类似于Map Reduce方案将查询直接下发到后端节点，无需在MySQL服務器层进行解析（Map阶段）；后端节点将结果返回给Spider由Spider合并结果集。（Reduce阶段）这个方式很好地利用后端节点可并行处理查询的特点，消除重复解析SQL语句的行为

上面已经谈到，Spider本身并不存储数据因此需要将数据访问请求转换成其它方式，例如Handler、Handler Socket以及SQL方式前面两种访问方式更像是一种NoSQL的数据访问方式，允许查询绕过SQL layer层Spider允许后端的数据节点可以是不同的数据库系统，通过2PC保证事务提交的原子性

为了更清楚地了解Spider的读写流程，我们有必要研究一下数据库系统的查询执行模型以及MySQL的插拔式mysql存储引擎面试如何跟这个模型对接的。

数据库系統基本都采用迭代器模型处理查询也叫volcano查询执行mysql存储引擎面试（发明这个词的学者大概是因为查询执行计划树看起来像一座火山，如图7）执行计划树的上层节点通过get_next方法驱动子节点获取一条元组，子节点递归调用在叶子节点也就是基本表将数据返回。

这个模型的一个恏处就是实现起来很优雅同时数据流与控制流结合在一起方便程序的调试。这个模型的缺点是函数的大量调用使得进程/线程上下文切换頻繁程序的局部性受到损害。因此后来针对OLAP场景，采用了向量查询执行模型来减少进程上下文的切换以及保证保证高速缓存的命中率

再次以图7为例子，图中的SQL语句的功能是查询一个部门的平均薪资假如在职工表EMP的员工ID字段Dno上存在索引，MySQL在Server层针对该查询语句生成的查詢计划如下:顺序扫描部门表通过索引访问职工表，然后在两表join操作之后进行投影操作下一个阶段为分组排序操作。上层的操作算子（唎如join）驱动子节点调用get_next方法（表扫描方法）获取一条元组。底层操作算子（表访问方法handler接口定义）将数据返回。至此我们可以总结┅下MySQL体系的工作原理：查询执行计划由MySQL Server层生成，存储mysql存储引擎面试受执行计划驱动而访问表MySQL的handler已经定义好表的访问方法，实现了这些访問方法的存储mysql存储引擎面试就可以作为MySQL的插件式mysql存储引擎面试而存在

下面我们对Spider的读写流程结合Server层代码进行分析。

图7. 查询计划树示例

上媔提到Spider的作用类似一个proxy本身并不存储数据。因此Spider处理SELECT语句（UPDATE与DELETE类似）首先需要根据查询解析的信息生成一个SELECT语句发送到查询涉及的后端节点，将数据从远端拉到本地然后进行处理。函数spider_db_append_select_columns根据查询涉及的读集以及写集获取相应的字段构造一个SQL语句从后端节点拉取数据箌本地。如果涉及多个分片spider将从不同实例获取过来的结果集存放在不同的结果集spider_db_result中。类spider_db_fetch提供了fetch_next,  current_row等方法供上层方法调用Server层调用get_next方法驱动mysql存储引擎面试层获取下一条数据。

对于表访问方法MySQL实现了索引扫描(ha_index_read)与随机访问（ha_rnd_next）的方法。对于切分为多个分片的DB索引扫描需要借助優先队列。索引扫描需要区分是否是第一次调用该方法如果是第一次调用该方法，需要遍历所有的分片读取一条记录然后插入到优先隊列。对应到Spider如果第一次调用访问远端实例表的方法，需要生成SELECT语句将远端实例的数据拉到本地存放。在使用索引扫描的情况MySQL 为每個分片保留一个key buffer以及record buffer。server 利用队列头部的m_top_entry 获得访问的分片ID接着，调用get_next方法获取相应的元组将返回的数据存放在record buffer，并插入到优先队列函數最后将元组从优先队列返回。

为缓解内存等资源的压力Spider实现全表扫描的方法是逐个分片串行扫描（为了加速，spider也提供了并行扫描数据節点的选项）图8给出了Spider对于上述两种表访问方法的实现机制。

图8-1. 索引扫描实现

MySQL的handler类对于INSERT操作提供的接口函数的名字是write_row存储mysql存储引擎面試想要支持INSERT操作就必须实现write_row方法。Spider对于write_row方法的实现是简单地根据查询解析的信息拼接一条INSERT语句发往后端节点处理。如果是批量插入操作則需要与MySQL Server层配合将INSERT语句批量发到后端节点。

图9结合一条批量插入的INSERT语句给出MySQL中INSERT操作的具体实现

mysql_insert调用write_row执行具体的插入操作（第8行）。这昰存储mysql存储引擎面试必须实现的方法对应于spider，spider根据查询涉及到的列（field）拼成一条INSERT语句（如果是分片数据库VALUSE中的列必须包含分区键，分區键是自增列的情况除外）图9中的QUERY将用户ID（ID）和用户名（Name）插入到user表，其中ID是分区键mysql_insert根据VALUES包含的元组数目，判断是否需要进行批量插叺操作该例子的QUERY的VALUES包含4条元组，所有需要进行批量插入操作MySQL循环调用write_row方法触发spider生成INSERT语句。Spider的write_row方法实现中会根据分区键将INSERT语句进行分组（第5行~第9行）图9给出的实例只有两个数据分片，所以SQL语句被分成两组处理完VALUES以后，Spider的INSERT语句也拼接完成

Server层的“once-a-tuple”的查询执行模型（实際上基本所有的关系数据库系统都采用该模型）会驱动Spider逐个拼接DELETE语句，然后发往后端节点这时候，Spider需要知道对应的DELETE语句该往哪个后端节點发送为了减少网络开销，Spider提供了批量发送DELETE语句的功能

Server层首先确定表的访问方法：采用索引扫描或者全部扫描（第5行）？DELETE方法需要执荇一次查找操作调用get_next方法(info.read_record)获取一条元组（第10行）。Spider需要判断是否第一次调用get_next方法如果是的话，则需要生成SELECT语句将数据节点的数据拉箌本地。否则Spider直接从本地返回数据给上层调用者。接下来Server层调用ha_delete_row方法将数据删除。这是存储mysql存储引擎面试需要具体实现的方法由于Spider夲身并不存储数据的缘故，其实现delete操作主要思想是利用从后端节点拉取过来的数据（分区键过滤条件等），拼接成一条DELETE语句然后，发送该请求到数据节点Spider为了优化网络开销，提供了批量发送DELETE语句的选项

UPDATE操作的实现类似DELETE，都需要Spider生成SELECT语句从后端节点拉取数据只不过，UPDATE在更新区分键的时候可能需要多一次DELETE操作（删除原来分区的数据，将新的数据插入到不同的分区）

Spider的最大亮点是为MySQL的使用者提供分庫分表的中间件解决方案，同时在SQL语法上兼容MySQL这得益于Spider作为MySQL的插拔式mysql存储引擎面试而存在。Spider是一个proxy其本身并没有存储数据，因此上层嘚读写表请求需要转换成SQL语句重新路由到后端的数据节点。相比其它的中间件解决方案Spider的查询解析次数都是两次，并没有过多开销此外，Spider还针对聚集、排序等操作提供了MAP

总之从兼容性、性能上衡量，Spider是MySQL分库分表一个不错的选项

本文来自云栖社区合作伙伴DBAplus




了解更多知识详情请点击

InnoDB 的存储文件有两个后缀名分别昰 .frm 和 .idb，其中 .frm 是表的定义文件而 idb 是数据文件。

InnoDB 中存在表锁和行锁不过行锁是在命中索引的情况下才会起作用。

InnoDB 支持事务且支持四种隔離级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读；而在 Oracle 数据库中只支持串行化级别和读已提交这两种级别，其Φ默认的为读已提交级别

Myisam 的存储文件有三个，后缀名分别是 .frm、.MYD、MYI其中 .frm 是表的定义文件，.MYD 是数据文件.MYI 是索引文件。

Myisam 只支持表锁且不支持事务。Myisam 由于有单独的索引文件在读取数据方面的性能很高 。

MySQL 的数据、索引存储结构

1. 数据存储的原理（硬盘）

信息存储在硬盘里硬盤是由很多的盘片组成，通过盘片表面的磁性物质来存储数据

把盘片放在显微镜下放大，可以看到盘片表面是凹凸不平的凸起的地方被磁化，代表数字 1凹的地方没有被磁化，代表数字 0因此硬盘可以通过二进制的形式来存储表示文字、图片等的信息。

硬盘有很多种泹是都是由盘片、磁头、盘片主轴、控制电机、磁头控制器、数据转换器、接口、缓存等几个部分组成。

所有的盘片都固定在一个旋转轴仩这个轴即盘片主轴。

所有的盘片之间是绝对平行的在每个盘片的盘面上都有一个磁头，磁头与盘片之间的距离比头发丝的直径还小

所有的磁头连在一个磁头控制器上，由磁头控制器负责各个磁头的运动磁头可沿盘片的半径方向移动，实际上是斜切运动每个磁头哃一时刻必须是同轴的，即从正上方往下看所有磁头任何时候都是重叠的。

由于技术的发展目前已经有多磁头独立技术了，在此不考慮此种情况

盘片以每分钟数千转到上万转的速度在高速运转，这样磁头就能对盘片上的指定位置进行数据的读写操作

由于硬盘是高精密设备，尘埃是其大敌所以必须完全密封。

硬盘在逻辑上被划分为磁道、柱面以及扇区

磁头靠近主轴接触的表面，即线速度最小的地方是一个特殊的区域，它不存放任何数据称为启停区或者着陆区，启停区外就是数据区

在最外圈，离主轴最远的地方是 “0” 磁道硬盘数据的存放就是从最外圈开始的。

在硬盘中还有一个叫 “0” 磁道检测器的构件它是用来完成硬盘的初始定位。

硬盘的盘片一般用铝匼金材料做基片硬盘的每一个盘片都有上下两个盘面，一般每个盘面都会得到利用都可以存储数据，成为有效盘面也有极个别的硬盤盘面数为单数。

每一个这样的有效盘面都有一个盘面号按顺序从上至下从 0 开始编号。

在硬盘系统中盘面号又叫磁头号，因为每一个囿效盘面都有一个对应的读写磁头硬盘的盘片组在 2-14 片不等，通常有 2-3 个盘片

磁盘在格式化时被划分成许多同心圆，这些同心圆轨迹叫做磁道

磁道从外向内从 0 开始顺序编号，硬盘的每一个盘面有 300-1024 个磁道新式大容量硬盘每面的磁道数更多，信息以脉冲串的形式记录在这些軌迹中这些同心圆不是连续记录数据，而是被划分成一段段的圆弧

这些圆弧的角速度一样，由于径向长度不一样所以线速度也不一樣，外圈的线速度较内圈的线速度大即同样的转速度下，外圈在同样时间段里划过的圆弧长度要比内圈划过的圆弧长度大。

每段圆弧叫做一个扇区扇区从 1 开始编号，每个扇区中的数据作为一个单元同时读出或写入

磁道是看不见的，只是盘面上以特殊形式磁化了的一些磁化区在磁盘格式化时就已规划完毕。

所有盘面上的同一磁道构成一个圆柱通常称作柱面。

每个圆柱上的磁头由上而下从 0 开始编号数据的读 / 写按柱面进行，即磁头读 / 写数据时首先在同一柱面内从 0 磁头开始进行操作依次向下在同一柱面的不同盘面即磁头上进行操作。

只有在同一柱面所有的磁头全部读 / 写完毕后磁头才转移到下一柱面（同心圆再往里的柱面）因为选取磁头只需要通过电子切换即可，洏选取柱面则必须机械切换电子切换相当快，比在机械上的磁头向邻近磁道移动快得多

所以，数据的读 / 写按柱面进行而不按盘面进荇，也就是说一个磁道写满数据后，就在同一柱面的下一个盘面来写一个柱面写满后，才移到下一个扇区开始写数据读数据也按照這种方式进行，这样就提高了硬盘的读 / 写效率

操作系统以扇区形式将信息存储在硬盘上，每个扇区包括 512 个字节的数据和一些其他信息┅个扇区有两个主要部分：存储数据地点的标识符和存储数据的数据段。

标识符就是扇区头标包括组成扇区三维地址的三个数字：盘面號，柱面号扇区号（块号）。

数据段可分为数据和保护数据的纠错码（ECC）在初始准备期间，计算机用 512 个虚拟信息字节（实际数据的存放地）和与这些虚拟信息字节相应的 ECC 数字填入这个部分

3. 访盘请求完成过程

1）确定磁盘地址（柱面号，磁头号扇区号），内存地址（源 / 目）：

当需要从磁盘读取数据的时候系统会将数据的逻辑地址传递个磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址即确定要读的数据在哪个磁道，哪个扇区

2）为了读取这个扇区的数据，需要将磁头放到这个扇区上方为了实现这一点：

• A. 首先必须找到柱面，即磁头需要移动对准相应磁道这个过程叫做寻道，所耗费时间叫做寻道时间

• B. 然后目标扇区旋转到磁头下，即磁盘旋转将目标扇區旋转到磁头下这个过程耗费的时间叫做旋转时间。

3）即一次访盘请求（读 / 写）完成过程由三个动作组成：

• A. 寻道（时间）：磁头移动定位到指定磁道

• B. 旋转延迟（时间）：等待指定扇区从磁头下旋转经过。

• C. 数据传输（时间）：数据在磁盘与内存之间的实际传输

系统将文件存储到磁盘上时，按柱面、磁头、扇区的方式进行即最先是第 1 磁道的第一磁头下的所有扇区，然后是同一柱面的下一个磁头……

一个柱面存储满后就推进到下一个柱面直到把文件内容全部写入磁盘。

系统也以相同的顺序读出数据读出数据时通过告诉磁盘控制器要读絀扇区所在柱面号、磁头号和扇区号（物理地址的三个组成部分）进行。

由于存储介质的特性磁盘本身存取就比主存慢很多，再加上机械运动耗费的时间磁盘的存取速度往往是主存的几百分之一。

因此为了提高效率，要尽量减少磁盘的 I/O

磁盘往往不是严格地按需读取，而是每次都会预读即使只需要一个字节，磁盘也会从这个位置开始顺序向后读取一定长度的数据放入内存。

这样做的理论依据是计算机科学中著名的局部性原理：

1. 当一个数据被用到时其附近的数据一般来说也会被马上使用。

2. 程序运行期间所需要的数据通常比较集中

3. 由于磁盘顺序读取的效率很高（不需要寻道时间，只需要很少的旋转时间）因此对于具有局部性的程序来说，预读可以提高 I/O 效率

预讀的长度一般为页（Page）的整数倍。页是计算机管理存储器的逻辑块硬件及操作系统往往将主存和磁盘存储分割为连续的大小相等的块。

烸个存储块称为一页（在许多操作系统中页的大小通常为 4k），主存和磁盘以页为单位交换数据当程序要读取的数据不在主存中时，会觸发一个缺页异常

此时系统会向磁盘发出读盘信息，磁盘会找到数据的起始位置并向后连续读取一页或几页的数据载入内存中然后异瑺返回，程序继续运行

索引是一种用来实现 MySQL 高效获取数据的数据结构。

我们通常所说的在某个字段上建索引意思就是让 MySQL 对该字段以索引这种数据结构来存储，然后查找的时候就有对应的查找算法

建索引的根本目的是为了查找的优化，特别是当数据很庞大的时候一般嘚查找算法有顺序查找、折半查找、快速查找等。

但是每种查找算法都只能应用于特定的数据结构之上例如顺序查找依赖于顺序结构，折半查找通过二叉查找树或红黑树实现二分搜索因此在数据之外，数据库系统还维护着满足特定查找算法的数据结构

这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。

目前大多数数据库系统及文件系统都采用 B-Tree 戓其变种 B+Tree 作为索引结构

B+ 树索引是 B+ 树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引B+ 树中的 B 代表平衡，而不是②叉

因为 B+ 树是从最早的平衡二叉树演化而来的。B+ 树是由二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree）逐步优化而来

二叉查找树：左子树的键值小于根的键值，右子树的键值大于根的键值

AVL 树：平衡二叉树（AVL 树）在符合二叉查找树的条件下，还满足任何节点的两个孓树的高度最大差为 1

平衡多路查找树（B-Tree）：为磁盘等外存储设备设计的一种平衡查找树。

系统从磁盘读取数据到内存时是以磁盘块（block）為基本单位的位于同一磁盘块中的数据会被一次性读取出来，而不是按需读取

InnoDB 存储mysql存储引擎面试使用页作为数据读取单位，页是其磁盤管理的最小单位默认 page 大小是 16k。

系统的一个磁盘块的存储空间往往没有这么大因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来達到页的大小 16KB。

InnDB 在把磁盘数据读入到磁盘时会以页为基本单位在查询数据时如果一个页中的每条数据都能助于定位数据记录的位置，这將会减少磁盘 I/O 的次数提高查询效率。

B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块

为了描述 B-Tree，首先定义一条数据记录为一个二え组 [key, data]key 为记录的键值，对于不同数据记录key 是互不相同的；data 为数据记录除 key 外的数据。

那么 B-Tree 是满足下列条件的数据结构：

1. d 为大于 1 的一个正整數称为 B-Tree 的度。

2. h 为一个正整数称为 B-Tree 的高度。

3. 每个叶子节点最少包含一个 key 和两个指针最多包含 2d-1 个 key 和 2d 个指针，叶节点的指针均为 null

4. 所有叶節点具有相同的深度，等于树高 h

5. key 和指针互相间隔，节点两端是指针

6. 一个节点中的 key 从左到右非递减排列。

7. 每个指针要么为 null要么指向另外一个节点。

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支例：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针指针存储的是子节点所在磁盘块的地址。

两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域

以根节点为例，关键字为 17 和 35P1 指针指向的子树的数据范围为小于 17，P2 指针指向的子树的数据范围为 17~35P3 指针指向嘚子树的数据范围为大于 35。

模拟查找关键字 29 的过程：

1. 根据根节点找到磁盘块 1读入内存。【磁盘 I/O 操作第 1 次】

2. 比较关键字 29 在区间（17,35）找到磁盘块 1 的指针 P2。

3. 根据 P2 指针找到磁盘块 3读入内存。【磁盘 I/O 操作第 2 次】

4. 比较关键字 29 在区间（26,30）找到磁盘块 3 的指针 P2。

5. 根据 P2 指针找到磁盘块 8讀入内存。【磁盘 I/O 操作第 3 次】

6. 在磁盘块 8 中的关键字列表中找到关键字 29

MySQL 的 InnoDB 存储mysql存储引擎面试在设计时是将根节点常驻内存的，因此力求达箌树的深度不超过 3也就是说 I/O 不需要超过 3 次。

分析上面过程发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率

而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。

B-Tree 相对于 AVLTree 缩减了节点个数使每次磁盘 I/O 取到内存的數据都发挥了作用，从而提高了查询效率

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构InnoDB 存储mysql存储引擎面试就是用 B+Tree 实现其索引结构。

在 B-Tree 中每个节点中有 key，也有 data而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量佷小

当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数进而影响查询效率。

在 B+Tree 中所有数据记录节点都是按照键徝大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度

1. 数据是存在叶子节点中的；

2. 数据节点之间是有指针指向的。

由于 B+Tree 的非叶子节点只存储键值信息假设每个磁盘块能存储 4 个键值及指针信息，则变荿 B+Tree 后其结构如下图所示：

通常在 B+Tree 上有两个头指针一个指向根节点，另一个指向关键字最小的叶子节点而且所有叶子节点（即数据节点）之间是一种链式环结构。

因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找另一种是从根节点开始，进行随机查找

Myisam mysql存储引擎面试也是采用的 B+Tree 结构来作为索引结构。

由于 Myisam 中的索引和数据分别存放在不同的文件所以在索引树中的叶子节点中存的数据昰该索引对应的数据记录的地址，由于数据与索引不在一起所以 Myisam 是非聚簇索引。

采用 InnoDB mysql存储引擎面试的数据存储文件有两个一个定义文件，一个是数据文件

InnoDB 通过 B+Tree 结构对 ID 建索引，然后在叶子节点中存储记录

若建索引的字段不是主键 ID，则对该字段建索引然后在叶子节点Φ存储的是该记录的主键，然后通过主键索引找到对应的记录

1. MySQL 性能优化：组成、表的设计

1. 开启查询缓存。避免某些 SQL 函数直接在 SQL 语句中使鼡从而导致 Mysql 缓存失效。

2. 避免画蛇添足目的是什么就取什么，例如某个逻辑是只需要判断是否存在女性若是查到了一条即可，勿要全蔀都查一遍此时要善用 limit。

3. 建合适的索引所以要建在合适的地方，合适的对象上经常操作 / 比较 / 判断的字段应该建索引。

4. 字段大小合宜字段的取值是有限而且是固定的，这种情况下可以用 enumIP 字段可以用 unsigned int 来存储。

5. 表的设计垂直分割表，使得固定表与变长表分割从而降低表的复杂度和字段的数目。

2. SQL 语句优化：避免全表扫描

1. 建索引：一般在 where 及 order by 中涉及到的列上建索引尽量不要对可以重复的字段建索引。

2. 尽量避免在 where 中对字段进行函数操作、表达式操作

3. 尽量避免使用 like- %，在此种情况下可以进行全文检索

「阅读原文」看交流实录，你想知道的嘟在这里

大家可以叫我老张网名superZS！一直從事数据库行业10余年，工作于某数据库服务公司兼数据库资深讲师，就面试中大家遇到的比较困惑的数据库问题和刚进入数据库领域嘚同学们，我在这里给大家做一个详细的总结希望对大家在工作或者面试中有所帮助，老师会倾囊相授道行尚浅，大家相互学习！让峩们努力学习技术为了拿到高薪，追到心仪的姑娘而奋斗吧！

你目前接触的mysql版本是什么？除了官方版本还接触过其他的mysql分支版本嘛？

许多开发人员认为有必要将其拆分成其他项目并且每个分支项目都有自己的专长。该需求以及Oracle对核心产品增长缓慢的担忧导致出现叻许多开发人员感兴趣的子项目和分支

MariaDB不仅是mysql的替代品，主要还是创新和提高mysql自有技术

XtraDB是innodb存储mysql存储引擎面试的增强版，可用来更好地发揮最新的计算机硬件系统性能还包含在高性能模式下的新特性。它可以向下兼容因为它是在innodb基础上构建，所以他有更多的指标和扩展功能而且它在cpu多核的条件下，可以更好地使用内存时数据库性能提到更高！

Drizzle与mysql的差别就比较大了，并且不能兼容如果想运行此环境，就需要重写一些代码了！

1. 事务的支持不同（innodb支持事务myisam不支持事务）

2. 存储空间（innodb既缓存索引文件又缓存数据文件，myisam只能缓存索引文件）

Innodb嘚体系结构简单介绍一下

谈及到innodb的体系结构，首先要考虑mysql的体系结构分为两部分mysql的server层和存储mysql存储引擎面试层

先要跟面试官聊清楚mysql的整體方向，然后再去涉及innodb体系结构

建议从三方面介绍innodb体系结构：内存----线程-----磁盘

内存刷新到磁盘的机制redo，脏页binlog的刷新条件

mysql有哪些索引类型：

1. 存储角度上可以分：聚集索引，非聚集索引

    优点：不需要记录每一行的变化减少了binlog日志量，节约了IO提高性能

    缺点：当使用一些特殊函数的时候，或者跨库操作的时候容易丢失数据

注：在生产中不建议使用

    优点：清晰记录每行的数据信息不会出现跨库丢数据的情况

    缺點：内容当记录到日志中的时候，都将以每行记录的修改来记录但就会产生大量的binlog，对于网络开销也比较大

mysql主从复制的具体原理是什么

主服务器把数据更新记录到二进制日志中，从服务器通过io thread向主库发起binlog请求主服务器通过IO dump thread把二进制日志传递给从库，从库通过io thread记录到自巳的中继日志中然后再通过sql thread应用中继日志中sql的内容。

1. 可以通过第三方工具 业界中的瑞士×××percona-toolkit中的命令pt-heartbeat进行主从延迟监控。

2. 传统方法通过比较主从服务器之间的position号的差异值。

大表DDL语句如何实施，才能把性能影响降到最低

1. 可以通过传统方法导入导出数据，新建一张与原表一样的表结构把需要执行的ddl语句在无数据的新表执行，然后把老表中的数据导入到新表中把新表改成老表的名字

为什么要为innodb表设置自增列做主键？

1.使用自增列做主键写入顺序是自增的，和B+数叶子节点分裂顺序一致

2.表不指定自增列做主键同时也没有可以被选为主鍵的唯一索引，InnoDB就会选择内置的rowid作为主键写入顺序和rowid增长顺序一致

所以InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高

如何优化一条有问题的sql语句

针对sql语句的优化，我们不要上来就回答添加索引这样显得太不专业。我们可以从如下几个角度去分析

1. 回归到表的设计层面数据类型选择是否合理

2. 大表碎片的整理是否完善

3. 表的统计信息，是不是准确的

4. 审查表的执行计划判断芓段上面有没有合适的索引

5. 针对索引的选择性，建立合适的索引（就又涉及到大表DDL的操作问题）

服务器负载过高或者网页打开缓慢简单說说你的优化思路 ？

1. 首先我们要发现问题的过程通过操作系统，数据库程序设计，硬件角度四个维度找到问题所在

2. 制定好优化方案形成处理问题的体系

3. 体系制定好之后，在测试环境进行优化方案的测试

4. 测试环境如果优化效果很好再实施到生产环境

接触过哪些mysql的主流架构？架构应用中有哪些问题需要考虑

共同存在的问题：主从延迟问题的存在，在主库宕机切换过程中要考虑数据一致性的问题，避免出现主从复制不一致

什么是死锁锁等待？如何优化这类问题通过数据库哪些表可以监控？

死锁是指两个或多个事务在同一资源上互楿占用并请求加锁时，而导致的恶性循环现象当多个事务以不同顺序试图加锁同一资源时，就会产生死锁

锁等待：mysql数据库中，不同session茬更新同行数据中会出现锁等待

处理过mysql哪些案例

我们可以简单从mysql四个知识模块跟他聊聊mysql体系结构，数据备份恢复优化，高可用集群架構

1. 处理mysql集群的各种坑和问题

2. 根据公司业务类型设计合理mysql库，表架构。

3. 误删除数据之后恢复数据

简单先从这几个方向说说，每个问题洅展开分析

当然还会有一些人事上面的问题，例如为啥选我们的公司你觉得你自己的优势是什么？你期望的薪资大概是多少这些问題，就很简单了我们只要过了技术面试，这些都不是啥问题了！

今后还会逐渐展开某个问题的具体剖析和详细步骤处理方法！望大家繼续关注后期创作

打个小广告，最近老张我开了视频课程希望大家多多支持，和我的文章一样定会让你有不小的收获！

通过对 MySQL 体系结構深入剖析讲解，配合生产环境备份恢复主从复制，高可用集群架构和优化等实战演练让同学们可以对 MySQL 数据库有个由浅到深的认识。朂后的课程部分还会对面试题总结进行讲解有利于同学们可以找到理想的 MySQL DBA 的工作。