Schema与数据类型优化

选择优化的数据类型

有几个简单的原则：

• 更小的通常更好
  一般情况下使用可以正确存储数据的最小数据类型。
• 简单的更好
  例如整型比字符操作代价更低。应当使用Mysql的日期类型而不是字符串，应当用整型存储IP地址
• 尽量避免NULL
  查询中如果包含NULL的列，对于Mysql来说更难优化，这样使得索引，索引统计，值都比较复杂。NULL的列会使用更多的存储空间，在Mysql里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节

整数类型
无符号的数字上限可以提高一倍
为整数类型指定宽度，如INT（11），不会限制值的合法范围，只是规定了Mysql的一些交互工具（命令行或客户端）用来显示的字符个数。对于存储和计算来讲，int(1) 和 int(20)是相同的。

实数类型
浮点类型在存储同样范围的值时，通常比Decimal使用更少的空间，Float使用4个字节，Double使用8个字节相比Float有更高的精度和更大的范围。这里能选择的是存储类型，Mysql内部使用Double作为内部浮点计算的类型。

字符串类型
CHAR和VARCHAR
VARCHAR节省了存储空间，如果行占用存储空间增长，并且在页内没有更多的空间存储，MyISAM拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。
下列情况使用Varchar是合适的：

• 字符串最大长度比平均长度大很多；
• 列的更新很少，所以碎片不是问题；
• 使用了UTF-8字符集，每个字符都使用不同的字节数进行存储。

InnoDB把过长的VARCHAR存储为BLOB
CHAR是定长的，会删除末尾的空格。CHAR(1)需要一个字节，VARCHAR(1)需要2个字节，因为还需要多一个字节存储长度。
类似的还有BINNARY和VARBINARY，填充使用的/0（0字节）

BLOB和TEXT
都是为了存储很大的数据设计的字符串数据类型，分别采用二进制和字符方式存储。不同在于BLOB存储的是二进制数据，没有排序规则或者字符集。
排序也只是对每个列的max_sort_length字节而不是整个字符串排序。
查询如果涉及BLOB，服务器不能在内存临时表中存储BLOB，必须要使用磁盘临时表，无论它多小。

日期和时间类型
DATETIME可以存储1001到9999年，精度为秒，与时区无关，使用8个字节的存储空间。TIMESTAMP保存了1970年1月1日以来的秒数。只使用4个字节的存储空间。从1970到2038年。

位数据类型
这些类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。
BIT
5.0之前BIT是TINYINT的同义词。之后则完全不同。MyISAM会打包所有的BIT列，InnoDB和Memory使用足够存储最小整数类型来存放BIT，所以不能节省存储空间。Mysql把BIT当作字符串类型而不是数字，会造成一些混乱。例如 a bit(8)，值为b'00111001'二进制等于57（ascii显示值等于9），a=9，a+0=57。应该谨慎使用，如果想存储true/false，可以使用CHAR(0)

选择标识符（identifier）
整数类型是最好的选择，很快并且可以使用AUTO_INCREMENT。避免使用字符串作为标识列，很耗空间，通常比数字类型慢，MyISAM默认对字符串使用压缩索引，会导致查询慢很多。

• 随机值如MD5，SHA1，UUID会导致INSERT和一些SELECT语句变慢，因为可能导致随机写入索引不同位置，导致页分裂，磁盘随机访问，对于聚簇存储引擎产生聚簇索引碎片。
• SELECT语句变慢因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
• 随机值导致缓存对所有类型的查询语句效果都很差。

Scheme设计中的陷阱

• 太多的列
  Mysql的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。非常宽的表可能会使得CPU占用非常高。
• 太多的关联
  “实体-属性-值”（EAV）设计模式在Mysql下不能靠谱的工作，限制了每个关联操作最多只能有61张表。单个查询最好在12个表内做关联。
• 全能的枚举
  枚举列表增加数据需要使用到ALTER TABLE，若不是加在最后可能会有影响
• 变相的枚举

范式和反范式
在范式化的数据库中，每个事实数据会出现并且只出现一次，相反，在反范式化的数据库中，信息是冗余的。

1. 第一范式
   确保数据表中每列（字段）的原子性。
   如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
   例如：user用户表，包含字段id,username,password

2. 第二范式
   在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
   如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。
   例如：一个用户只有一种角色，而一个角色对应多个用户。则可以按如下方式建立数据表关系，使其满足第二范式。
   user用户表，字段id,username,password,role_id
   role角色表，字段id,name
   用户表通过角色id（role_id）来关联角色表

3. 第三范式
   在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关。
   例如：一个用户可以对应多个角色，一个角色也可以对应多个用户。则可以按如下方式建立数据表关系，使其满足第三范式。
   user用户表，字段id,username,password
   role角色表，字段id,name
   user_role用户-角色中间表，id,user_id,role_id
   像这样，通过第三张表（中间表）来建立用户表和角色表之间的关系，同时又符合范式化的原则，就可以称为第三范式。

4. 反范式化
   反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。
   例如：在上例中的user_role用户-角色中间表增加字段role_name。
   反范式化可以减少关联查询时，join表的次数。

范式的优点

• 范式化的更新操作更快
• 更新需要变更的数据更少
• 表比较小，可以更好放在内存里

缺点是通常需要关联，代价相对昂贵，也可能使得一些索引策略无效。

反范式的优点
避免关联
查询相对高效（当索引合理）

创建高性能索引

索引可以包含一个或多个列，如果索引包含多个列，那列的顺序也十分重要，因为Mysql只能最高效的使用索引的最左前缀列。

B-Tree的索引列是顺序组织存储的，很适合查找范围数据。适用于全键值、键值范围或键前缀查找。

红黑树是一种含有红黑结点并能自平衡的二叉查找树。它必须满足下面性质：
性质1：每个节点要么是黑色，要么是红色。
性质2：根节点是黑色。
性质3：每个叶子节点（NIL）是黑色。
性质4：每个红色结点的两个子结点一定都是黑色。
性质5：任意一结点到每个叶子结点的路径都包含数量相同的黑结点。
从性质5又可以推出：
性质5.1：如果一个结点存在黑子结点，那么该结点肯定有两个子结点

哈希索引（hash index）只有精确匹配索引所有列的查询才有效。只包含哈希值和行指针，不存储字段值，所以不能避免读取行。
并不是按照索引值顺序存储，所以无法用于排序。
也不支持部分索引列匹配查找。只支持等值查询，不支持范围查询。

高性能的索引策略

独立的列才能使用到索引，列不能使用操作符或者表达式
多列索引，当使用到多个单列索引时，会进行多个索引的联合操作（索引合并）

选择合适的索引列顺序
正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。
在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。

聚簇索引
并非一种单独的索引类型，而是一种数据存储方式。InnoDB在同一个结构中保存了B-Tree索引和数据行。
InnoDB使用主键聚集数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义一个主键作为聚簇索引。InnoDB只聚集同一个页面的记录。
优点：

• 把相关数据保存再一起。
• 数据访问更快
• 使用覆盖索引扫描的查询可以直接使用节点中的主键值。

缺点：

• 插入速度依赖于插入顺序，如果不是按照主键加载数据，加载完成后最好使用OPTIMIZE TABLE重新组织表
• 更新聚簇索引的代价很高，因为会将被更新的行移动到新位置
• 插入新航或者主键更新需要移动行时，可能面临“页分裂（Page Split）”问题
• 可能导致全表扫描变慢，尤其是行比较稀疏
• 二级索引（非聚簇索引）可能比想象的要更大，因为叶子节点包含了引用行的主键列。
• 二级索引需要两次索引查找，而不是一次

覆盖索引
如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？所以一个索引包含（或者覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。

索引排序
只有索引的列顺序和orderby的顺序完全一致，并且列的正序，逆序都一样时，才能使用索引对结果进行排序。如果查询需要关联多张表，则只有当orderby的引用字段全部为第一个表时，才能使用索引进行排序。

索引和数据的碎片化
B-Tree索引可能会碎片化。
表的数据存储也可能碎片化：
行碎片
这种碎片指的时数据行被存储到多个地方的多个片段中。即使只查询一行记录，也会导致性能下降。
行间碎片
逻辑上顺序的页，或者行再磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大影响。
剩余空间碎片
指数据页中有大量的空余空间，会导致服务器读取大量不需要的数据造成浪费。

查询性能优化

查询的声明周期大致按照顺序：
从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。执行时最重要的阶段，包含了大量为检索数据到存储引擎的调用以及调用后的数据处理，包括排序，分组等。

慢查询基础：优化数据访问

是否请求了不需要的数据

• 查询不需要的记录
• 查询不需要的列 （多表关联 * ）
• 总是取出全部列（select * ）
• 重复查询相同的数据

是否在扫描额外的记录

衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

响应时间是 服务时间 和 排队时间 之和。
扫描的行数和返回的行数理想情况下应该是相同的，一般在1:1到10:1之间
扫描的行数和访问类型：在EXPAIN语句中的type列反应了访问类型。访问类型有很多中，包括全表扫描，索引扫描，范围扫描，唯一索引查询，常数引用等。这些速度是从慢到快，扫描行数也是从多到少。

重构查询的方式

一个复杂查询还是多个简单查询

Mysql支持多个简单查询，一个通用服务器上可以支持每秒10万的查询，一个千兆网卡满足每秒2000次的查询。Mysql内部每秒能扫描内存中上百万行数据，相比之下响应数据给客户端就慢得多了

切分查询

将一个大查询分而治之，例如一个删除大量数据的语句，拆分为多个小的删除。

分解关联查询

有很多好处：

• 让缓存的效率更高。无论是应用程序的缓存和Mysql的缓存，都会在单表的情况下更容易命中。
• 查询分解后减少了锁的竞争
• 应用层关联，更容易对数据库进行拆分，做到高性能和可扩展
• 减少冗余记录的查询
• 在应用中实现的哈希关联，而不是使用Mysql的嵌套查询。

执行查询的基础

执行查询的过程：

1. 客户端发送一条查询给服务器
2. 服务器先检查缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段。
3. 服务器进行SQL解析，预处理，再由优化器生成对应的执行计划。
4. Mysql根据优化器生成的执行计划，调用存储引擎的API执行查询。
5. 将结果返回给客户端

Mysql客户端/服务器通信协议

通信协议是“半双工”的，意味着任何一个时刻，要么是服务端向客户端发送数据，要么是客户端向服务端发送数据。这种协议让MySQL通信简单快速。但是也意味着没法进行流量控制，一旦一端开始发送消息，另一端要完整接收完整个消息才能响应它。客户端用一个单独的数据包将查询传给服务器，所以查询语句特别长的时候，参数max_allowed_packet特别重要。

查询状态

最简单使用SHOW FULL PROCESSLIST查看当前状态，状态值有如下几种：

• Sleep：线程正在等待客户端发送新的请求。
• Query：线程正在执行查询或者将查询结果返回客户端。
• Locked：服务器层线程等待表锁。在存储引擎基本实现的锁，例如InnoDB的行所，不会体现在线程状态中。
• Analyzing and statistics：线程收集存储引擎的统计信息，并生成查询的执行计划。
• Copying to tmp table [on disk]：线程执行查询，并将其结果集复制到一个临时表中，这种状态一般要么是做GROUP BY操作，或者文件排序操作，或者UNION操作。如果后面有“on disk”标记表示MySQL将内存临时表放到磁盘上。
• Sorting result：线程在对结果集排序。
• Sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

查询缓存

检查缓存是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同页不会匹配，如果命中在返回结果集之前MySQL会检查一次用户权限，这是无需解析SQL的，因为查询缓存中有保存当前查询需要的表信息。

查询优化处理

语法解析器和预处理

MySQL通过关键字将SQL语句解析，生成语法解析树，使用MySQL语法规则验证和解析查询。例如是否使用了错误的关键字，关键字顺序是否正确，引号前后是否正确匹配。
预处理根据MySQL规则进一步检查解析树是否合法。例如数据表、列是否存在，名字和别名是否有歧义。
下一步预处理器会验证权限。

查询优化器

语法树已经合法，优化器将其转为了执行计划。优化器作用就是找到最好的执行计划。
可以通过查询当前回话的Last_query_cost的值来得知MySQL计算当前查询成本。
根据一系列统计信息计算得来：每个表或者索引的页面个数，索引的基数（索引中不同值的数量），索引和数据行的长度，索引分布的情况。
优化器在评估成本的时候不考虑任何缓存，假设读取任何数据都需要一次磁盘IO

MySQL的查询优化器是一个复杂部件，使用了很多优化的执行策略。优化策略简单分为两种：静态优化和动态优化。
静态优化直接对解析树进行优化，静态优化在第一次万能充后就一直有效，使用不同参数执行查询页不会发生变化，可以认为是一种“编译时优化”。
动态优化和查询的上下文有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。可以认为时“运行时优化”。
MySQL能够处理的优化类型：

• 重新定义关联表的顺序：数据表的关联并不总是按照查询中指定的顺序执行
• 将外连接转为内连接：MySQL识别并重写查询，让其可以调整关联顺序。
• 使用等价变化规则：通过等价变换来简化并规范表达式。合并减少一些比较，一定一些恒等或者恒不等的判断。
• 优化Count() Max() Min()：min和max可以直接查询b-tree的最左或者最右端。
• 预估并转化位常数表达式：
• 覆盖索引扫描
• 子查询优化；某些情况下可以将子查询转换为效率更高的形式
• 提前终止查询：在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。
• 等值传播：两个列的值通过等值关联，MySQL能够传递where条件。
• 列表in()的比较：MySQL将in()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。这是一个O(log n)的操作。等价转换为Or的复杂度时O(n)。

MySQL执行关联查询

MySQL先从一个表中循环取出单条数据，在嵌套循环到下一个表中寻找匹配的行，依次直到找到所有表中匹配的行，然后根据各个表匹配的行返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果不行就返回上一层次关联表。
MySQL多表关联的指令树时一颗左侧深度优先的树。

关联查询优化器

MySQL的最优执行计划中的关联表的顺序，通过预估需要读取的数据页来选择，读取的数据页越少越好。
关联顺序的调整，可能会让查询进行更少的嵌套循环和回溯操作。
可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照查询顺序执行。

排序优化

排序时成本很高的操作，从性能角度考虑，应该尽量避免排序，或者避免对大量数据进行排序。
当不能用索引生成排序结果时，MySQL需要字节进行排序，如果数据量小使用内存，数据量大使用磁盘。不过统一都称为文件排序（filesort）。
MySQL有两种排序算法：

• 两次传输排序（旧版本）：读取指针和需要排序的字段，排序之后，再根据排序结果读取所需要的数据行。第二次读取数据的时候可能产生大量随机IOS，成本很高，不过在排序时加载的数据较少，所以在内存中就可以读取更多的行数进行排序。
• 单次传输排序（新版本）：查询所有需要列，根据给定列进行排序直接返回结果。在MySQL4.1之后引入。

查询执行引擎

查询执行阶段就根据执行计划，调用存储引擎的实现接口来完成。
查询结果返回时，即使不需要返回结果集给客户端，MySQL返回查询信息，例如影响到的行数。

查询优化的局限性

关联子查询（in+子查询）
使用join，或者使用函数GROUP_CONCAT()在in中构造一个由分好分隔的列表，有时候比关联更快，in加子查询性能糟糕，一般建议使用exists等效改写。

优化特定类型的查询

优化count查询

MyISAM的count函数非常快，只有在没有条件的前提下。
近似值：某些不需要精确值的情况下，可以使用EXPLAIN出来的优化器估算行数。

优化关联查询

• 确保on或者using子句中的列上有索引。
• 确保任何的group by和order by中的表达式只设计一个表中的列，这样MySQL才有可能使用索引来优化过程

优化子查询

在5.6之前尽量转换使用join，5.6之后没有太多差别

优化group by和distinct

groupby 使用主键列效率更高。

优化limit

“延迟关联”，首先使用索引覆盖来选取范围内的主键，接下来根据这些主键获取对应数据。

分区表

分区表限制：

• 一个表最多只能有1024个分区
• 5.1中分区表达式必须是整数，或者是返回整数的表达式。5.5中某些场景可以直接使用列进行分区。
• 如果分区字段中有主键或者唯一索引列，那么所有的主键列和唯一索引列都必须包含进来。
• 分区表中无法使用外键约束。

在数据量超大的时候B-Tree就无法起作用了，除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录。如果数据量巨大，这将产生大量随机IO，数据库的响应时间将大到不可接受的程度。

MySQL优化服务器配置

MySQL配置的工作原理

MySQL从 命令行参数和配置文件中获取配置信息。配置文件一般是在 /etc/my.cnf 或 /etc/mysql/my.cnf。
确认配置文件路径，可以使用下列命令

$ which mysql
/bin/mysql
$/bin/mysql --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

配置文件分为多个部分，每个部分的开头是用方括号括起来的分段名称。客户端会读取client部分，服务器通常读取mysqld部分。

配置项都使用小鞋，单次之间用下划线或者横线隔开。

常用变量及其效果

• key_buffer_size
  一次性为键缓冲区（key buffer）分配所有的指定空间。操作系统会在使用时才真正分配。
• table_cache_size
  这个变量会等到下次有线程打开表才有效果，会变更缓存中表的数量。
• thread_cache_size
  MySQL只有再关闭连接时才在缓存中增加线程，只在创建新连接时才从缓存中删除线程。
• query_cache_size
  修改这个变量会立刻删除所有缓存的查询，重新分配这片缓存到指定大小，并且重新初始化内存。
• read_buffer_size
  MySQL只会在查询需要使用时才会为该缓存分配内存，并且一次性分配该参数指定大小的全部内存。
• read_rnd_buffer_size
  MySQL只会在查询需要使用时才会为该缓存分配内存，并且只会分配该参数需要大小的内存。
• sort_buffer_size
  MySQL只会在查询排序需要使用时才会为该缓存分配内存，并且一次性分配该参数指定大小的全部内存，不管排序是否需要这想·么大的内存。

InnoDB事务日志

InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，无需在每个事务提交时把缓冲池的脏块刷新到磁盘中。
InnoDB用日志把随机IO变成顺序IO，一旦日志写入磁盘，事务就持久化了，即使变更还没有写到数据文件。
InnoDB最后是要把变更写入数据文件，日志有固定大小。InnoDB的日志是环形方式写的：当写到日志的尾部，会重新跳转到开头继续写，但不会覆盖到还没应用到数据文件的日志记录，因为这样会清掉已经提交事务的唯一持久化记录。
InnoDB使用一个后台线程只能地刷新这些变更到数据文件。这个线程可以批量组合写入，是的数据写入更顺序，以提高效率。事务日志把数据文件的随机IO转换为几乎顺序的日志文件和数据文件IO，把刷新操作转移到后台使得查询可以更快完成，并且缓和查询高峰时IO的压力。

InnoDB表空间

InnoDB把数据保存在表空间内，本质上是一个由一或多个磁盘文件组成的虚拟文件系统。InnoDB用表空间实现很多功能，不只是存储表和索引。它还保存了回滚日志（旧版本号），插入缓冲（Insert Buffer）、双写缓冲（Doublewrite Buffer），以及其他内部数据结构。
InnoDB使用双写缓冲来避免页没写完整锁导致的数据损坏。这是一个特殊的保留区域，再一些连续的块中足够保存100个页。本质上是一个最近写回的页面的备份拷贝。当InnoDB从缓冲池刷新页面到磁盘时，首先把他们写到双写缓冲，然后再把他们写到其所属的数据区域中，可以保证每个页面的写入都是原子并且持久化的。页面在末尾都有校验值（Checksum）来确认是否损坏。

InnoDB的多线程

• Master Thread
  非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERT BUFFER）、UNDO页的回收等。
• IO Thread
  InnoDB中大量使用了AIO（Async IO）来处理IO请求，可以极大提高数据库性能，IO Thread主要是负责这些IO请求的回调（call back）处理。InnoDB1.0之前工有4个IO Thread，分别是write、read、insert buffer、log IO thread。
• Purge Thread
  事务提交后，其使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。

InnoDB的内存

• 缓冲池
  InnoDB基于磁盘存储，记录按照页的方式进行管理。在数据库中进行读取页的操作，首先将磁盘读到的页存放在缓冲池中，下次读取先判断页是否在缓冲池则直接读取，否则读取磁盘上的页。对页的修改首先修改缓冲池，然后再以一定的频率刷新到磁盘（通过checkpoint机制）。缓冲池配置通过innodb_buffer_pool_size来设置。
  缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等

  

  内存数据对象

• LRU List、Free List和Flush List
  InnoDB的LRU添加了midpoint位置，新读取的页不是放到首部，而是放到midpoint位置。默认是放在LRU列表长度的5/8处。有些操作可能会全表扫描加载大量的页，如果直接加载到首部则可能刷出有效页。数据库开始时，LRU是空的，页都在FreeList中，查找时从Free列表中查找是否有可用空闲页，若有则从Free列表中删除放入LRU。当页从LRU的old部分假如到new时，称之为page made young，因为innodb_old_blocks_time设置导致页没有从old部分移动到new部分称为page not made young。

• 重做日志缓冲（redo log buffer）
  三种情况会讲redo log buffer中的内容刷新到日志文件

1. Master Thread每秒刷新一次
2. 每个事务提交时会刷新
3. redo log buffer剩余空间小于1/2时

• 额外的内存池
  在对数据库结构本身的内存进行分配的时候，需要从额外的内存池进行申请。

Checkpoint技术

InnoDB存储引擎内部有两种：

• Sharp Checkpoint
  数据库关闭时将所有脏页刷回磁盘，默认工作方式，参数innodb_fast_shuthown=1
• Fuzzy Checkpoint
  刷新一部分脏页。（Master Thread Checkpoint，FLUSH_LRU_LIST Checkpoint，Async/Sync Flush Checkpoint，Dirty Page too much Checkpoint）

InnoDB关键特性

插入缓冲

• Insert Buffer
  对于非聚集索引的插入或者更新操作，不是每一次直接插入到索引页，而是先判断插入的非聚集索引是否在缓冲池中，若在则插入，若不在则放入到一个Insert Buffer中。以一定的频率进行Insert Buffer和非聚集索引子节点的合并操作。需要满足两个条件：1.索引是辅助索引。2.索引不是唯一的。
• Change Buffer
  InnoDB 1.0.x开始可以对DML操作进行缓冲 （Insert，Delete，Update）分别是：Insert Buffer，Delete Buffer，Purge Buffer。

Insert Buffer是一颗B+树，全局唯一，负责对所有表的辅助索引进行Insert Buffer。
Merge Insert Buffer是合并到真正的辅助索引中的操作，在下面几种情况时发生：

• 辅助索引页被读取到缓冲池中
• Insert Buffer Bitmap 页追踪到该辅助索引页已经没有空间可用
• Master Thread 触发

自适应Hash索引（Adaptive Hash Index）

InnoDB 会监控各种索引列的查询，如果判断建立哈希索引可以提高访问速度，则会自动建立。AHI是通过缓冲池的B+树构建而来，不需要对整张表结构建立哈希索引。有如下要求：

• 以相同模式访问了100次
• 页通过该模式访问了N次：N=页中记录*1/16

异步IO

异步IO（Asychronous IO，AIO）

文件

• 参数文件：
  初始化参数文件
• 日志文件：
  例如错误日志文件（error log），二进制日志文件（binlog），慢查询日志文件（slow query log），查询日志文件（log）
• socket文件：
  UNIX域套接字方式进行连接是需要的文件。
• pid文件：
  MySQL实例的进程ID文件
• MySQL表结构文件：
  用来存放MySQL表结构定义的文件
• 存储引擎文件：

二进制日志（binlog）

记录了对MySQL数据库执行更改的所有操作，不包括SELECT和SHOW。

mysql> mysqlmaster status;

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
binlog.001663	5924141

mysql> show binlog events in 'binlog.001663' limit 5;

binlog文件名（Log_name）	日志开始位置（Pos）	事件类型（Event_type）	服务器编号（Server_id）	日志结束位置（End_log_pos）	信息
binlog.001663	5878887	Anonymous_Gtid	1	5878966	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663	5878966	Query	1	5879057	BEGIN
binlog.001663	5879057	Table_map	1	5879148	table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663	5879148	Update_rows	1	5879340	table_id: 8291 flags: STMT_END_F
binlog.001663	5879340	Xid	1	5879371	COMMIT /* xid=4800934 */
binlog.001663	5879371	Anonymous_Gtid	1	5879450	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663	5879450	Query	1	5879541	BEGIN
binlog.001663	5879541	Table_map	1	5879632	table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663	5879632	Update_rows	1	5879824	table_id: 8291 flags: STMT_END_F
binlog.001663	5879824	Xid	1	5879855	COMMIT /* xid=4800956 */
binlog.001663	5879855	Anonymous_Gtid	1	5879934	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663	5879934	Query	1	5880025	BEGIN
binlog.001663	5880025	Table_map	1	5880116	table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663	5880116	Update_rows	1	5880308	table_id: 8291 flags: STMT_END_F
binlog.001663	5880308	Xid	1	5880339	COMMIT /* xid=4800988 */

MySQL5.1引入了binlog_format参数，参数有STATEMENT、ROW、MIXED三种。

• STATEMENT
  和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句。
• ROW
  记录的是表的行更改情况。如果设置为ROW，可以将InnoDB事务隔离设置为READ COMMITTED获取更好的并发性。
• MIXED
  默认使用STATEMENT，某些情况下使用MIXED。

1. 表的存储引擎为NDB，对表的DML操作以ROW格式记录。
2. 使用了UUID() USER() CURRENT_USER() FOUND_ROWS() ROW_COUNT()
3. 使用了INSERT DELAY语句
4. 使用了用户自定义函数
5. 使用了临时表

要查看binlog日志文件的内容，必须使用MySQL提供的工具mysqlbinlog。

表结构定义文件

MySQL定义了frm为后缀名的文件，记录了表结构（视图）定义。

InnoDB存储引擎文件

表空间文件（tablespace file）

默认有一个初始大小为10MB，名为ibdata1的文件

重做日志文件（redo log file）

默认情况下会有 ib_logfile0和ib_logfile1作为 redo log file 。每个InnoDB至少有一个重做日志文件组（group），文件组下有两个重做日志文件，用户可以设置多个镜像日志组（mirrored log groups）

表

索引组织表（index organized table）

MySQL默认创建一个6字节大小的指针（_rowid）

InnoDB逻辑存储结构

所有的数据都被逻辑地存放在一个空间内，称之为表空间（tablespace），表空间又由段（segment），区（extent）、页（page）组成，页在某些文档中也成为块（block）

表空间

如果启用了 innodb_file_per_table的参数，每张表的数据可以单独放到一个表空间内 ，其中存放的是数据、索引、和插入缓冲Bitmap页，其他类的数据如回滚（undo）信息、插入缓冲索引页、系统事务信息、二次写缓冲还是放在原本的共享表空间。

段

表空间是由各个段组成的，包括数据段、索引段、回滚段等。数据段就是B+树的叶子节点（Leaf node segment），索引段即B+树的非索引节点（Non-leaf node segment）。

区

区是连续页组成的空间，任何情况下每个区的大小都为1MB，为了保证区中页的连续性，InnoDB一次从磁盘申请4-5个区，默认情况页大小为16KB，一个区中一共有64个连续的页。InnoDB1.0.x引入压缩页，每个页的大小可以通过key_block_size设置为2k、4k、8k。1.2.x版本新增了参数innodb_page_size，通过该参数可以将默认页的大小设置为4k、8k。

页

InnoDB中常见的页类型有：

• 数据页（B-tree Node）
• Undo页（Undo Log Page）
• 系统页（System Page）
• 事务数据页（Transaction system Page）
• 插入缓冲页位图（Insert Buffer Bitmap）
• 插入缓冲空闲列表页（Insert Buffer Free List）
• 未压缩的二进制大对象页（Uncompressed BLOB Page）

行

MySQL的存储是面向列的（row-oriented）,数据是按行存储的。页存放的记录有硬性定义最多存放16KB/2 - 200行，即7992行。

InnoDB数据页结构

数据页由下面7个部分组成：

• File Header（文件头）固定
• Page Header（页头）固定
• Infimun 和 Supremun Record 固定
  页中两个虚拟的行记录，Infimun是指比页中任何主键更小的值，Supremun指比任何值都大的值，这两个值在页创建的时候创建，在任何时候情况下都不会删除。
• User Record（用户记录，即行记录）
  存储实际记录，B+树索引组织。
• Free Space（空闲空间）
  空闲空间，链表数据结构。一条记录被删除后会放到空闲空间。
• Page Directory（页目录）
  存放了记录的相对位置，这些记录指针称之为槽（slots）或者目录槽（dictionary slots），稀疏目录，可能包含多条记录。
  B+树索引不能找到实际的记录，而是找到记录的页。

• File Trailer（文件结尾信息）
  检测页是否完整写入了磁盘，checksum值。

  

  InnoDB数据页结构

行溢出数据
InnoDB会将一条记录中的某些列存储在真正的数据列之外，BLOB，LOB字段可能不一定会将字段放在溢出页面，VARCHAR也有可能会放进溢出页面。
Oracle VarCHAR2最多存放4000字节，MSSQL最多8000字节，MySQL最多65535（存在其他开销，最长65532）。当发生行溢出时，数据存放在页类型Uncompress BLOB页面。数据页只保存数据的前768字节。

锁

lock与latch
latch一般称为闩锁，轻量级，要求锁定的时间非常短。在InnoDB中，分为mutex（互斥量）与rwlock（读写锁）。用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。
lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。在commit或者rollback之后释放，有死锁检测机制。

锁的类型

• 共享锁（S Lock）：允许事务读一行数据
• 排他锁（X Lock）：允许事务更新或删除一行数据

上述两种都是悲观锁，乐观锁就是CAS（Compare and Swap）

一致性非锁定读（consistent nonlocking read）
是指InnoDB通过MVCC（Multi Version Concurrency Control）读取数据库当前行的方式。如果读取的行正在进行update或者delete操作，则读取一个快照。在Read Committed和Repeatedable Read中使用。前者读取最新的快照，后者使用事务开始时的快照。
一致性锁定读（locking read）
也可以显式的对读取加锁，有两种操作：

• select ... for update（加一个X锁）
• select ... lock in share mode（加一个S锁）

行锁的3种算法

• Record Lock：单个行记录的锁
• Gap Lock：锁定一个范围，不包括记录本身
• Next-Key Lock：Gap+Record，锁定范围以及记录本身。用来解决幻影相关问题（Phantom）
  针对的是索引的区间，但是当查询条件指定唯一索引值（只针对主键索引/聚集索引）时，会降级为Record Lock，若是二级索引则不会。而且InnoDB还会对二级索引的下一个键值加上Gap Lock。
  例如，二级索引b列有1，3，6，9。当使用X锁锁定3时（where b<=3 for update），会NKL锁定了范围（1-3），同时会使用GL锁定下一个键值（3-6）。
  利用这个机制可以用一个事务，首先select id from t where col=xxx lock in share mode，接下来insert t (col) values (xxx)，能够保证一定插入不存在的值。

死锁

两个事务执行时，因争夺锁资源互相等待的场景。
解决死锁最简单的就是超时，通过innodb_lock_wait_timeout控制超时时间。
当前普遍使用的是wait-for graph（主动检测的方式），这要求数据库保存两种信息：

• 锁的信息链表
• 事务的等待列表

通过上述信息，可以在事务请求锁并发生等待时都进行判断，在上述两个信息构造的图中是否存在回路，如果存在就表示存在死锁。
采用深度优先算法实现，InnoDB1.2之前采用递归方式，之后采用非递归提高了性能。

事务的实现（ACID）

事务的隔离性由锁来实现，redo log（重做日志）保证事务的原子性和持久性，undo log（）保证事务的一致性。
redo恢复提交事务修改的页操作，是物理日志，记录的是页的物理修改操作。
undo回滚某个行记录到特定版本，是逻辑日志，记录的是行的修改记录。

redo

存在 redo log buffer和redo log file，buffer写入file时需要调用fsync操作，此操作取决于磁盘性能，决定了事务提交的性能也就是数据库的性能。

UNIX的写操作
一般情况下，对硬盘（或者其他持久存储设备）文件的write操作，更新的只是内存中的页缓存（page cache），而脏页面不会立即更新到硬盘中，而是由操作系统统一调度，如由专门的flusher内核线程在满足一定条件时（如一定时间间隔、内存中的脏页达到一定比例）内将脏页面同步到硬盘上（放入设备的IO请求队列）。
因为write调用不会等到硬盘IO完成之后才返回，因此如果OS在write调用之后、硬盘同步之前崩溃，则数据可能丢失。虽然这样的时间窗口很小，但是对于需要保证事务的持久化（durability）和一致性（consistency）的数据库程序来说，write()所提供的“松散的异步语义”是不够的，通常需要OS提供的同步IO（synchronized-IO）原语来保证
fsync的功能是确保文件fd所有已修改的内容已经正确同步到硬盘上，该调用会阻塞等待直到设备报告IO完成。除了同步文件的修改内容（脏页），fsync还会同步文件的描述信息（metadata，包括size、访问时间st_atime & st_mtime等等），因为文件的数据和metadata通常存在硬盘的不同地方，因此fsync至少需要两次IO写操作

undo

delete和update操作产生的删除语句并不是马上执行，而是将delete_flag标记为1，最后有purge操作来统一完成。用undo log来执行，执行之后的空间不会回收，只会用于重用。