innodb存储引擎

• 页
  • 数据库读写磁盘的基本单位是页（Page），无论是读一行，还是读取多行，都是加载这些行所在的页；如果以行为单位，一次I/O操作只能处理一行数据，效率会很低
  • lnnoDB将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，页的大小默认为16KB，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB的内容刷新到磁盘中。
  • 为了减少磁盘i/o，MySQL使用B+树索引，检索一条数据的快慢，主要受树的高度影响的，树越矮，i/o次数越少，而通过b+树检索到的不是目标行数据，而是目标行数所在的页
• 区(Extent)：是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64 个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB= 1MB
• 段(Segment)：由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页)，不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。
• 表空间（Tablespace）：是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

随机i/o：

• 以机械盘为例，数据最终是保存在磁盘一个个扇区上的，一个扇区写满了，就要换下一个扇区，这时就需要找到目标扇区
• 如果下一个扇区是紧连着的，就是顺序i/o，如果不是就是随机i/o了，这需要更长的物理移动时间，所以减少随机i/o是很重要的

页

InnoDB中页被划分为了七个部分：

• 文件头

• 文件尾

• 空闲空间，指的是页中还没有使用的空间

  • 数据会按照指定的行格式存储到用户记录中，但是一开始生成页的时候，并没有用户记录这个部分
  • 每插入一条记录，都会从空闲空间中申请空间给用户记录，当空闲空间全部被用户记录代替时，就以为着这个页使用完了，如果还有新的记录插入，就需要申请新的页

• 用户记录，存储行记录的内容，叶子节点就是真正的数据，用户记录中的数据按照指定的行格式排列，相互之间形成单链表

• 最大最小记录，是两个虚拟的行记录，mysql会自动给每个页里增加最大和最小记录

• 页头，记录了很多页的状态信息，例如槽的数量、记录的数量、第一条记录的地址等

• 页目录，大小不定，存储用户记录的相对位置

文件头和文件尾：

• 文件头，描述了页的信息
  • 页号（FLL_PAGE_OFFSET）：每一个页都有一个单独的页号，innodb可以通过页号唯一定位一个页
  • 页的类型（FLL_PAGE_TYPE）：代表当前页的类型，例如数据页、Undo日志页和事务数据页等
  • 上一页（FLL_PAGE_PREV）和下一页（FLL_PAGE_NEXT），页与页之间是双向链表连接
  • 日志序列号（FLL_PAGE_LSN） ，页面被最后修改时日志序列位置
  • 校验和 （FLL_PAGE_SPACE_OR_CHKSUM）
    • 对于一个很长的字符串，会通过某些算法来计算一个比较短的值来代表这个字符串（类比hash值），这个值就是校验和，再比较两个很长的字符串之前，会先比较校验和，校验和不一样，字符串肯定不一样，节约了比较两个字符串的时间损耗，
    • 对于innodb来说，校验和用于比较两个页是否相同
    • innodb以页为单位把数据加载到内存中处理，如果页的数据被修改了，就需要同步到磁盘，但如果再同步过程中出现意外，例如停电，就会造成页传输的不完整
    • 可以通过文件头和文件尾检验和的比较来判断一个页是否完整，如果两个值不相等，说明传输有问题，需要重新传输
• 文件尾，8字节，检验页是否完整

页目录：

• 为什么需要页目录
  • 在页中，记录是以单向链表的形式存储的，插入和删除很方便，但是检索效率不高，为了提高检索效率，设计了页目录
  • 首先将所有记录分成几个组，其中包括最大最小记录，当然不包括已经删除的记录
  • 第一组也就是最小记录所在的组，只有一条记录，最后一组也就是最大记录所在的组，会有1到8条记录，其他的组记录数量在4-8条之间，这样做的好处是除了第一组数据外，其他组的数据会尽量平分
  • 然后每个组的最后一条记录的头信息会存储该组中有多少条记录（n_owned）
  • 页目录就是用来按照顺序存储每一组的最后一条记录（最大的记录）的地址偏移量，每组的地址偏移量也成为槽（slot）
  • 查找时可以通过二分法快速确定记录所在的槽，并找到槽所在分组中主键值最小的那条记录，然后通过next_record（当前记录到下一条记录的地址偏移量）属性遍历槽中的记录
• 如何确定页目录的分组个数
  • 初始情况下，一个数据页里只有最小和最大记录，分属于两个组
  • 之后每插一条记录，都会从页目录中找到主键值比记录的主键值大并且差值最小的槽，然后把该槽的n_owned值加1，直到组中的记录数等于8
  • 如果一个组的记录数达到8，再插入新的记录，就会新增一个组，然后把原组中放入记录拆分一部分到新的组，一个组中4条记录，一个组中5条记录

从页的角度看b+树的检索：

• 从b+树的根开始逐层检索，直到找到叶子节点，也就是对应的数据页
• 然后将页加载到内存中，通过页目录中的槽采用二分查找到对应的组
• 然后遍历组中的链表找到记录

普通索引和唯一索引在效率上的差距：

• 唯一索引有唯一性约束，只有找到唯一那条的数据，就不需要再继续查询了
• 而普通索引在找到符合条件的记录后，还需要继续查找，然而因为数据页的大小是16K，只要符合条件的数据都在这个页上，多查找几条数据的时间可以忽略不记，如果符合条件的数据在多个数据页上就会比较耗费时间

行格式

行格式可分为四种：

• compact（紧凑的）：
• dynamic (动态的)：从5.1开始默认的行格式
• compressed (压缩的)
• redundant (冗余)
• 在创建表的时候，可以指定表的行格式（row_format = 行格式名称），也可以后续修改

compact行格式：

• 除了包含真实记录的数据
  • 真实数据除了真实的列，还会有三个隐藏列：
    • row_id : 行id，可以唯一标识一条记录
    • transaction_id：事务id
    • roll_pointer：回滚指针
• 还包括一些额外信息：
  • 变长字段长度列表
    • mysql支持一些可变长的数据类型，例如：varchar、text等，字段中存储了多少数据不是固定的，所以在存储真实数据的时候，需要把这些数据占用了多少个字节数也存储起来
    • compact把所有变长字段的真实数据占用的字节长度都存放在记录的开头部分，形成一个变长字段长度列表
  • null值列表
    • compact会把null值的列统一管理起来，存在一个null值列表中，如果表中没有允许null值的列，就没有null值列表
    • 之所以需要存储null值，是因为数据是需要对齐的，如果没有标出null的位置，就有可能在查询数据的时候出现混乱但是如果使用特定值来表示null值，就又会很浪费空间，
    • 所以在行数据头部开辟出一块空间专门来存储该行数据哪些是非空数据，哪些是空数据
    • 用于一个二进制位来表示一个列值是否为空，二进制位的值为1时，代表该列的值位null，为0代表不是null
  • 记录头的信息
    • 两个预留位，都是1bit，没有使用
    • delete_mask，用来标记当前记录是否被删除，0表示没有被删除，1表示删除
      • 之所以采用逻辑删除，不是直接物理删除，移除某条记录后，其他的数据需要再磁盘上重新排列，会导致性能消耗
      • 所有被删除的记录都会存放在垃圾链表，垃圾链表中记录占用的空间被称为可重用空间，如果后面有新的记录插入到表中，有可能会直接复用这些空间
    • min_rec_mask ：值为1就代表这条记录是b+树该层非叶子节点的最小记录，其他的记录都是0
    • recode_type：当前的记录类型
      • 0是普通记录
      • 1表示b+树非叶节点记录
      • 2表示最小记录
      • 3表示最大记录
    • heap_no：表示当前记录再本页中的位置
      • mysql会自动给每个页里增加两个记录，由于不是用户插入的所以也叫伪记录，0代表最小记录、1代表最大记录，从值可以知道它们的位置是最靠前的
    • n_owned：每个组中最后一条记录的头信息中会存储该组一共多少条记录，作为n_owned字段
    • next_record：表示当前记录到下一条记录的地址偏移量

行溢出问题：

• varchar字段，最大的存储范围是65535个字节，默认情况下能存储21,845个字符，但是因为需要用2两个字节记录变长字段的长度，和1个字节来记录null值的标识，所以其实最大可以存放65532个字节
• 然而65532，已经超过了16k（16384），这样一个页就连一条数据都无法存放，也就是行溢出
• 所以在compressed和redundant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储一部分数据，把剩余的几个数据分散在其他的页中进行分页存储，然后再真实数据处记录下这些页的地址(占用20个字节)，从而找到剩余数据所在的页，也被称为页的扩展

dynamic和compressed行格式：

• 这两个行格式和compact很像，区别就是处理行溢出时有分歧：
• compressed和dynamic对于存放在BLOB中的数据采用了完全行溢出的方式，真实数据处只存放20字节的地址，实际的数据都存放在其他位置
• compact和redundant 两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）
• compressed的另一个功能就是，存储在其中的行数据会以zlib算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度的数据能够进行非常有效的存储

redundant ：

• 是5.0版本以前默认的行格式
• 记录的额外信息包括：
  • 字段长度偏移列表
    • redundant 会把该条记录所有的列，包括隐藏列的长度信息都记录下来
    • 采用的是相邻两个数值的差值来计算各个列的长度，计算列值的长度不如compact直观
  • 记录头信息
    • 相比于compact多了两个记录：记录中的列数量、记录字段长度偏移列表中每个列对应的偏移量
    • 少了recode_type（当前的记录类型）

区、段、表

区(Extent)

• b+树的每一层中的页都会形成一个双向链表，如果以页为单位来分配存储空间，双向链表两个相邻的页的位置可能会非常远，
• 如果是范围查询，需要定位最左和最右记录，而如果相邻的页离得非常远，也就是随机i/o了，这样的效率很差，所以应该尽可能的让链表中相邻的页的物理位置也相邻，这样在范围查找的时候才能使用顺序i/o
• 所以引入了区的概念，一个区就是物理位置上连续的64个页，也就是 64*16 为1M的空间
• 所以在数据量比较大的时候，为某个索引分配空间的时候就不在以页为单位，而是以区为单位，甚至在数据量特别大的时候，会一次性分配多个区。虽然可能会造成空间的浪费，但是能消除很多随机i/o，功大于过

段(Segment)：

• 叶子节点和非叶子节点都是页，如果一个区同时存在叶子节点和非叶子节点，范围查找时，会需要扫描更多的区
• 所以innodb把叶子节点和非叶子节点分开进行存储，都有各自的区，存放叶子节点的区的集合就是一个段，存放非叶子节点的区的集合页是一个段，
• 所以一个innodb索引会产生两个段，一个叶子节点段（数据段），一个非叶子节点段（索引段）
• 此外还会为存储一些特殊数据而定义段，例如回滚段
• 段并不是一个连续的物理上的空间，而是逻辑上的概念，由存储引擎自身来管理，包含若干零星的页面，和一些完整的区

碎片区：

• innodb表一定有一个聚簇索引，一个索引有两个段，段是以区为单位来申请的，所以一个段最小就是一个区的大小，也就是1M，即使数据量非常小，一个索引也要占用2M的空间，如果在接着加索引，仍然需要2M，就会造成很大的浪费
• 所以在数据量非常小的情况下 ，以完整的区为单位来分配空间，是很大的浪费，所以提出了碎片区的概念
• 在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是可以用于不同的目的
• 所以，碎片区不属于任何一个段，直属于表空间
• 最终为某个段分配空间的策略是:
  • 在刚开始向表中插入数据时，段是从某个碎片区以单个页面为单位来分配存储空间的
  • 当段已近占用了32个碎片区页面后，就会申请完整的区为单位来分配存储空间

区的分类：

• 空闲区：还没有用到区中的任何页面
• 有剩余空间的碎片区
• 没有剩余空间的碎片区
• 附属于某个段的区，这个区属于段，其他的区都是碎片区，只属于表空间

表空间

• 表是innodb存储引擎luoji架构最高层，所有的数据都存放在表空间中
• 表空间可以分为：
  • 系统表空间
    • mysql进程只有一个系统表空间，有一些系统表，会额外记录整个系统的信息
    • 用户是不能直接访问这些表的
  • 独立表空间
    • 每个表都有一个独立的表空间，数据和索引信息都会保存在表空间中
    • 内部结构就是：段、区、页
    • 在8.0以后，frm文件合并到ibd文件，所以8.0初始的ibd文件比5.7大16K
  • 撤销表空间
  • 临时表空间

缓冲池和写缓冲

• 在MySQL中数据分为内存和磁盘两个部分，可以通过在buffer pool缓存数据页和索引页，来减少磁盘读
• 通过change buffer缓解磁盘写。

MySQL 的大表查询为什么不会爆内存

• 由于 MySQL 是边读变发，因此对于数据量很大的查询结果来说，不会再 server 端保存完整的结果集，所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。
• InnoDB 引擎内部，由于有淘汰策略，InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。对冷数据的全扫描，影响也能做到可控制。

Buffer_Pool

• 在应用系统中，为了加速数据访问，可以把高频的数据放在缓存中。例如：Redis、MongoDB里，来减轻数据库的压力，在操作系统中，为了减少磁盘IO，引入了缓冲池(buffer pool)机制，MySQL作为一个存储系统，为提高性能，减少磁盘IO，同样具有缓冲池机制

• Buffer Pool也就是【缓冲池，简称BP】，是一块内存区域，用于缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用，Buffer Pool默认大小 128M，用于缓存数据页（16KB）

• 所有数据页的读写操作都需要通过buffer pool进行，

  • innodb 读操作，先从buffer_pool中查看数据的数据页是否存在，如果不存在，则将page从磁盘读取到buffer pool中。
  • innodb 写操作，先把数据和日志写入 buffer pool 和 log buffer，再由后台线程以一定频率将 buffer 中的内容刷到磁盘

• 当我们在写入数据的时候，先更新Buffer Pool中的数据页，然后将更新操作顺序写Redo log，写操作的事务持久性由redo log 落盘保证，buffer pool只是为了提高读写效率。

• Buffer Pool里有三个链表，LRU链表，free链表，flush链表，InnoDB通过这三个链表的使用来控制数据页的更新与淘汰的

  • 控制块
    • 为了更好管理的缓存页，InnoDB 为每一个缓存的数据页都创建了一个单独的区域，大概占缓存页大小的5%，用于描述数据也就是控制块，包括了数据页所属表空间、数据页编号、缓存页在Buffer Pool中的地址，链表节点信息、一些锁信息以及 LSN 信息等
    • 控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中
  • Free链表
    • 当启动 Mysql 服务器的时候，需要完成对 Buffer Pool 的初始化过程，把它划分为若干对控制块和缓存页
    • 划分空间后Buffer Pool的缓存页是都是空的，当要对数据执行增删改查的操作的时候，才会把数据对应的页从磁盘文件里读取出来，放入Buffer Pool中的缓存页中，当BufferPool中间有的页数据持久化到硬盘后，这些数据页又会被空闲出来。
    • 所以为了知道哪些数据页是空的，那些是有数据的，innoDB维护了一个双向链表Free链表，也就是空闲链表，由一个基础节点和若干个子节点组成，记录了空闲的数据页对应的控制块信息
    • Free链表存在的意义就是描述Buffer Pool中的数据页，所以Free链表跟数据页的是一一对应的关系
  • LRU链表
    • Buffer Pool 的大小是有限的，所以对于一些频繁访问的数据是希望能够一直留在 Buffer Pool 中，而一些访问比较少的数据，我们希望能将它够释放掉，空出数据页缓存其他数据
    • 所以，InnoBD采用了LRU（Least recently used）算法，将频繁访问的数据放在链表头部，而不怎么访问的数据链表末尾，空间不够的时候就从尾部开始淘汰，从而腾出空间
    • 当数据库从磁盘加载一个数据页到Buffer Pool中的时候，会将一些变动信息也写到控制块中，并且将控制块从Free链表中脱离加入到LRU链表中
  • Flush链表
    • 对数据的读写都是先对Buffer Pool中的缓存页进行操作，此时缓存页跟磁盘页的数据就会不一致，也就是产生了脏页，然后在通过后台线程将脏页写入到磁盘，持久化到磁盘中
    • Flush链表的作用就是帮助定位脏页，结构与Free链表的结构很类似，也由基节点与子节点组成
    • 是一个双向链表，链表结点是被修改过的缓存页对应的控制块（更新过的缓存页）

buffer pool的工作流程，大致可以分为三步：

• 先查询buffer pool是否存在对应的数据页，有的话则直接返回
• 不存在对应的数据页，就去磁盘中查找，并把结果复制一份到buffer pool中，然后返回给客户端
• 下次有同样的查询，就可以直接查找buffer pool返回数据

MySQL对LRU算法的改进

• LRU 算法存在的问题：预读失效和Buffer Pool 污染

• 预读失效

  • 预读也就是为了减少磁盘IO，innoDB会把数据从磁盘读取到内存中使用，磁盘数据读取到内存，并不是按需读取，而是按页读取，一次至少读一页数据（16K），如果未来要读取的数据就在页中，直接读取内存即可，不需要磁盘IO，提高效率

  • 预读失效，也就是那些被提前加载进来的数据页并一直没有被访问，预读的数据会被放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，需要把末尾的页淘汰掉。如果这些预读的数据一直没有被使用，就会被挤到链表的尾部，进而被淘汰，那缓存的命中率就会大大降低，相当于预读是白费功夫

  • 但是并不能因为预读失效，而将预读机制去掉，所以需要提高缓存的命中率。

    • 所以MySQL将 LRU 划分为：ld和young两个区域
      • 预读的页会被加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部
      • 如果预读的页一直没有被访问，会一直存在old 区域，直到被移除，不会影响 young 区域中的热点数据
      • old 区域，在LRU 链表的后半部分；young 区域，在 LRU 链表的前半部分
      • old 区域占整个 LRU 链表长度的比例，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37，（可以通过 innodb_old_blocks_pc 参数来设置）

• Buffer Pool 污染

  • 也就是当Sql执行的时候，会数据加载到Buffer Pool ，而Buffer Pool的大小是有限的，如果加载大量数据（例如对大表进行全表扫描），就会将Buffer Pool 里的所有页都替换出去，导致原本的热数据被淘汰下次访问的时候，又要重新去磁盘读取，导致数据库性能下降
  • Buffer Pool污染跟预读失效都是一样的会导致LRU的热点数据被替换和淘汰
  • 全表扫描之所以会替换淘汰原有的LRU链表young 区域数据，主要是因为我们将原本只会访问一次的数据页加载到young 区。 这些数据实际上刚刚从磁盘被加载到Buffer Pool，然后就被访问，之后就不会用，所以可以将数据放young 区的门槛提高有点，从而把这种访问一次就不会用的数据过滤掉，把它挡在Old区，这样就不会污染young 区的热点数据了
  • MySQL 解决方式就是提高了数据从Old区域进入到 young 区域门槛
    • 先设定一个间隔时间innodb_old_blocks_time，默认为1秒，也就是数据页必须在 old 区域停留的时间，然后将Old区域数据页的第一次访问时间在其对应的控制块中记录下来
    • 如果后续的访问时间与第一次访问的时间大于innodb_old_blocks_time，才会将该缓存页移动到 young 区域的头部。
    • 当同时满足数据页被访问与数据页在 old 区域停留时间超过 1 秒两个条件，才会被插入到 young 区域头部
  • young 区域优化
    • MySQL为了防止 young 区域节点频繁移动到头部，对 young 区域也做了一个优化：young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会

脏页的刷盘时机

• 当我们对数据进行修改时，其实修改的是Buffer Pool 中数据所在缓存页，修改后将其设置为脏页，并将脏页的控制块同时存在于 LRU 链表和 Flush 链表，然后通过刷脏将修改同步至磁盘
• 刷脏的目的是将修改的数据同步磁盘，释放Buffer Pool内存空间，因此我们肯定是需要将访问的最少的数据页刷会磁盘，释放其数据页内存，只需要根据LRU链表，将其Old区域尾部节点输盘即可
• 刷脏不是每次修改都进行的，那样性能会很差，因此刷脏是通过一定的时机触发进行批量刷盘的。
• 脏页的刷盘时机总的来说就分为以下种：
  • redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
  • MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；
  • MySQL 空闲时，后台线程会定期脏页刷盘
    • 为了避免缓冲池内存不够，MySQL在后台有一个定时任务，通过单独的后台线程，不断从LRU链表Old区尾部的缓存页刷回至磁盘中并同时释放缓存页。
  • Buffer Pool 空间不足时，会淘汰一部分数据页，如果淘汰的是脏页，需要先将其同步到磁盘。
    • Buffer Pool内存不足脏页刷盘分为两种情况：
    • 若缓存页同时在flush链表和LRU链表中，说明数据被修改过，则需要刷脏，释放掉缓存页的内存，将控制块重新添加到free链表中
    • 若缓存页只是存在于LRU链表中，说明数据没有被修改过，则不需要刷脏，直接释放掉缓存页的内存，将控制块重新添加到free链表中

innodb缓冲池

• 就性能而言，innodb缓冲池大小通常是最重要的变量，因为它不仅缓存索引，还缓存行数据，自适应哈希索引 ，锁和其他内部结构
• 缓冲池实现了延时写，把多个写操作合并在一起
• 大型的缓冲池需要更长的关闭时间和预设时间

change buffer（写缓冲）：

• change buffer就是在非唯一普通索引页不在buffer pool中时，对页进行了写操作的情况下，先将记录变更缓冲，等未来数据被读取时，再将 change buffer 中的操作merge到原数据页的技术。

• 在MySQL5.5之前，叫插入缓冲(insert buffer)，只针对insert做了优化；现在对delete和update也有效，叫做写缓冲(change buffer)。

• 原理

  • 当需要更新一个数据页时，如果数据页在内存中就直接更新。如果数据页不在内存中。在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
  • 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
  • 虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上（ibdata）。
  • 将 change buffer 中的操作合并到原数据页，得到最新结果的过程称为 merge。以下情况会触发merge：
    • 访问这个数据页；
    • 后台master线程会定期 merge；
    • 数据库缓冲池不够用时；
    • 数据库正常关闭时；
    • redo log写满时；

• change buffer为什么针对非唯一普通索引页

  • 唯一索引所有的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。
  • 因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用
  • 普通索引不需要判断唯一性，正常使用 change buffer 更新。

MySQL 是如何保证数据不丢失的

• 只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据

• 在恢复数据时，redolog 状态为commit 则说明 binlog 也成功，直接恢复数据；如果redolog 是prepare(准备)，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

• redlog：

  • redlog是重做日志，是Innodb存储引擎独有的，使MySQL在崩溃的时候具有了恢复数据的能力
  • redlog日志会备份的是事务执行过程中的修改数据
  • redolog存储了数据被修改的值，当提交了一个事务的时候，InnoDB会先把要修复的数据写入到日志中，然后再去修改缓冲池里面的真正数据页。
  • redolog本身也是由两个部分组成，分别是在内存中的日志缓存（redolog buffer）和在磁盘中的重做日志文件（redologfile）；前者是存在内存中的，容易丢失，后者是存在硬盘中的，丢失概率较低。

• binlog

  • binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，默认情况下，binlog是二进制格式的，不能使用文本工具的命令进行查看，而是使用mysqlbinlog解析查看。
  • 当数据写入到数据库的时候，会同时把更新的SQL语句写入到相应的binlog文件里面，同时在使用mysqldump（导出数据）进行备份的时候，只是对一段时间的数据进行了全局备份，但是如果备份后发现数据库服务器产生故障，这个时候就要用到binlog日志了。

• binlog和redolog的区别：

  • redolog是在InnoDB存储引擎层记录的，而binlog是mysql数据库服务器层记录的

  • 两种日志记录的内容形式不同，MySQL的binlog是逻辑日志，而InnoDB存储引擎层面的重做日志是物理日志。

  • 两种日志与记录写入磁盘的时间点不同，二进制日志只在事物提交完成后进行一次写入，而redolog的重做日志在事物的进行过程中不断地被写入。

  • binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，但是redolog是循环使用的。

如何理解 MySQL 的边读边发

• 服务端并不须要保存一个完整的结果集。取数据和发数据的流程是：
  • 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
  • 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
  • 若是发送成功，就清空 net_buffer，而后继续取下一行，并写入 net_buffer。
  • 若是发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socketsend buffer）写满了，进入等待。直到网络栈从新可写，再继续发送
  • 所以一个查询在发送过程当中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大
• 这就意味着，若是客户端接收得慢，会致使 MySQL 服务端因为结果发不出去，这个事务的执行时间变长

mysql_use_result的使用

• 对于每个可以产生一个结果集的命令（比如select、show、describe, explain, check_table等等），都需要调用mysql_store_result或者mysql_use_result语句，处理完结果集后需要使用mysql_free_result释放。
• Mysql_use_result初始化一个取回结果集但是它并不像mysql_store_result那样实际的读取结果放到客户端。相反，每一个列结果都是通过调用mysql_fecth_row独立取回的。它直接从服务器读取一个查询的结果而不是存储它到一个临时表或者一个客户端的缓存里面。因此对于mysql_use_result而言它比mysql_store_result快一些并且使用更少的内存。客户端只有在当前的列或者通信的缓存即将超过max_allowed_packet才申请内存。
• 有些情况不能使用mysql_use_result接口，如果每一列在客户端要做很多的的处理，或者输出发生到屏幕用户可能通过ctrl-s退出，这样会挂起服务器，而阻止其他线程去更新客户端正在获取数据的这些表。
• 当使用mysql_use_result时，必须执行mysql_fetch_row直到NULL值返回。否则那些没有被获取的列将作为你下个请求的一部分返回。
• 一旦你处理完所有的结果集，必须调用mysql_free_result去释放。