高性能mysql发布时间_高性能MySQL分析

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，若不是加在最后可能会有影响

变相的枚举

范式和反范式

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

第一范式

确保数据表中每列(字段)的原子性。

如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。

例如：user用户表，包含字段id,username,password

第二范式

在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。

如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。

例如：一个用户只有一种角色，而一个角色对应多个用户。则可以按如下方式建立数据表关系，使其满足第二范式。

user用户表，字段id,username,password,role_id

role角色表，字段id,name

用户表通过角色id(role_id)来关联角色表

第三范式

在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关。

例如：一个用户可以对应多个角色，一个角色也可以对应多个用户。则可以按如下方式建立数据表关系，使其满足第三范式。

user用户表，字段id,username,password

role角色表，字段id,name

user_role用户-角色中间表，id,user_id,role_id

像这样，通过第三张表(中间表)来建立用户表和角色表之间的关系，同时又符合范式化的原则，就可以称为第三范式。

反范式化

反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。

例如：在上例中的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的嵌套查询。

执行查询的基础

执行查询的过程：

客户端发送一条查询给服务器

服务器先检查缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段。

服务器进行SQL解析，预处理，再由优化器生成对应的执行计划。

Mysql根据优化器生成的执行计划，调用存储引擎的API执行查询。

将结果返回给客户端

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中的内容刷新到日志文件

Master Thread每秒刷新一次

每个事务提交时会刷新

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。

表的存储引擎为NDB，对表的DML操作以ROW格式记录。

使用了UUID() USER() CURRENT_USER() FOUND_ROWS() ROW_COUNT()

使用了INSERT DELAY语句

使用了用户自定义函数

使用了临时表

要查看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会将一条记录中的某些列存储在真正的数据列之外，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来执行，执行之后的空间不会回收，只会用于重用。