高性能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,若不是加在最后可能会有影响
  • 变相的枚举

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

  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逻辑存储结构

表空间

如果启用了 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来执行,执行之后的空间不会回收,只会用于重用。

版权声明:
作者:Mr李
链接:https://www.techfm.club/p/37627.html
来源:TechFM
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
< <上一篇
下一篇>>