作者:陈晨_软件五千言 链接:https://www.jianshu.com/p/f0bd138bd4fe 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字节) 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)等 高性能MySQL分析 image 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) 高性能MySQL分析 image 表空间 如果启用了 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值。 高性能MySQL分析 image 行溢出数据 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:锁定一个范围,不包括记录本身 微信搜索公众号:Java后端编程,回复:java 领取资料 。 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来执行,执行之后的空间不会回收,只会用于重用。 |