change buffer
- InooDB 会将这些更新操作缓存在 change buffer 中,就不需要从磁盘中读入这个数据页
- change buffer 在内存中有拷贝,也会被写入到磁盘上
- 将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge
- 触发 merge 的场景
- 访问对应数据页
- 后台线程定期 merge
- 数据库正常关闭
- 触发 merge 的场景
- 优势:减少读磁盘,执行速度明显提升,避免数据页读入占用额外内存,提高内存利用率
- 数据从磁盘读入内存涉及随机 IO 的访问,是数据库里面成本最高的操作之一
- 唯一索引的更新就不能使用 change buffer,普通索引可以使用
- 唯一索引所有的更新操作都要先判断这个操作是否违反唯一性约束
- change buffer 用的是 buffer pool 里的内存,不能无限增大
- innodb_change_buffer_max_size(设置成 50 ,表示大小最多只能占用 buffer pool 的 50%
- 使用场景
- change buffer 的主要目的就是将记录的变更动作缓存下来,merge 前 change buffer 记录越多,收益越大
- 对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,使用效果最好
- 业务模型常见的就是账单类、日志类的系统
- 对于更新模式是写入之后马上会做查询的业务模式,change buffer 反而起到了副作用
- 更新先记录在 change buffer,随后马上访问此数据页,立即触发 merge 过程
- 随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价
- 普通索引和 change buffer 的配合使用,对于数据量大的表的更新优化很明显
- 在使用机械硬盘时,change buffer 这个机制的收效是非常显著的
- redo log 与 change buffer 在提升更新性能上的收益
- redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写)
- change buffer 主要节省的则是随机读磁盘的 IO 消耗
- merge 执行流程
- 从磁盘读入数据页到内存(老版本的数据页)
- 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个)并依次应用
- 写 redo log(数据的变更和 change buffer 的变更
- 脏页刷新(异步
优化器逻辑
- 目的:找到一个最优的执行方案,并用最小的代价去执行语句
- 结合扫描行数与是否使用临时表、是否排序等因素进行综合判断
- 基数:一个索引上不同的值的个数(基数越大,索引的区分度越好;采样统计而来;
- 对于由于索引统计信息不准确导致的问题,用 analyze table 来解决(重新统计索引信息
- 对于其他优化器误判的情况,解决的方法
- force index 来强行指定索引
- 修改语句来引导优化器
- 增加或者删除索引来绕过这个问题
索引创建
- 直接创建完整索引,这样可能比较占用空间
- 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引
- 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题
- 创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,都不支持范围扫描
- 索引选取的越长,占用的磁盘空间就越大,相同的数据页能放下的索引值就越少,搜索效率越低
- hash 后可能存在冲突,where 部分要判断 原值 是否精确匹配
脏页
- 当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为 “脏页”
- 内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为 “干净页”
- 触发数据库 flush 的场景
- redo log 写满
- 系统内存不足(淘汰的数据页刚好是脏页,则需先写到磁盘
- 系统空闲时
- MySQL 正常关闭时
- 刷脏页是常态,但出现以下两种情况时会明显影响性能
- 一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长
- 日志写满,更新全部堵住,写性能跌为 0,这种情况对敏感业务来说,是不能接受的
- 刷脏页的控制策略
- innodb_io_capacity :告诉 InnoDB 你的磁盘能力(建议设置为磁盘的 IOPS
- InnoDB 的刷盘速度就是要参考这两个因素:一个是脏页比例,一个是 redo log 写盘速度
-
innodb_max_dirty_pages_pct 是脏页比例上限,默认值是 75%
- 按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度
-
- 刷新邻接页
- nnodb_flush_neighbors 参数就是用来控制这个行为的(8.0以前默认为 开启 1
- 高 IOPS 设备建议设置成0(只刷自己),减少 SQL 语句响应时间
表空间
-
InnoDB 表包含两部分,即:表结构定义和数据
- 表结构在 MySQL 8.0 以前存在以.frm 为后缀的文件里,MySQL 8.0 允许把表结构定义放在系统数据表中
-
innodb_file_per_table 控制表数据是存放在共享表空间里还单独文件中
- OFF:表的数据放在系统共享表空间,也就是跟数据字典放在一起
- ON:每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中(MySQL 5.6.6 版本开始 默认
-
数据删除流程
- delete 命令其实只是把记录的位置,或者数据页标记为了 “可复用”,但磁盘文件的大小是不会变
- 数据页的复用跟记录的复用是不同的
- 记录的复用,只限于符合范围条件的数据
- 数据页的复用可以复用到任何位置(如果相邻两个页利用率都比较小,会合并成一个页
- 删除数据会造成空洞,插入数据也可能会
- 非递增顺序插入(随机插入),造成索引的数据页分裂
- 更新索引上的值,可以理解为删除一个旧的值,再插入一个新值
-
重建表
-
alter table A engine=InnoDB 命令来重建
- 通常情况,数据页的利用率会更高,有收缩表空间作用
- 重建表时,InnoDB 会给每个页留了 1/16 给后续的更新用,即重建表后不是 “最” 紧凑的
-
改锁表 DDL
- MySQL 会自动完成转存数据、交换表名、删除旧表的操作,但这个 DDL 不是 Online 的
-
Online DDL
- 重建表的过程中,允许对表 A 做增删改操作
- 锁流程
- 拿 MDL 写锁
- 降级成 MDL 读锁
- 真正做 DDL(占绝大部分时间,但此期间可以正常读写数据
- 升级成 MDL 写锁
- 释放 MDL 锁
- 重建方法都会扫描原表数据和构建临时文件。对于很大表来说,会很消耗 IO 和 CPU 资源的
- 推荐使用 GitHub 开源的 gh-ost 来做
-
Online 和 inplace
- tmp_table(临时表)是在 server 层创建的,tmp_file(临时文件)是在内部创建出来的
- inplace(原地):整个 DDL 过程都在 InnoDB 内部完成
- 对于 server 层来说,没有把数据挪动到临时表,是一个 “原地” 操作
- alter table t engine=InnoDB —> alter table t engine=innodb,ALGORITHM=inplace;
- 两者关系
- DDL 过程如果是 Online 的,就一定是 inplace 的
- 反过来未必,也就是说 inplace 的 DDL,有可能不是 Online 的
- 添加全文索引(FULLTEXT index)和空间索引 (SPATIAL index) 就属于这种情况
-
三种方式重建表的区别
- alter table t engine = InnoDB(也就是 recreate)默认的就是上面图的流程
- analyze table t 其实不是重建表,只是对表的索引信息做重新统计,这个过程中加了 MDL 读锁
- optimize table t 等于 recreate+analyze
-
count (*)
- 为什么 InnoDB 不跟 MyISAM 一样,也把总数存起来呢
- 由于多版本并发控制(MVCC)的原因,InnoDB 表 “应该返回多少行” 也是不确定的
- 计算总数时,MySQL 优化器会找到最小的那棵树来遍历(普通索引树比主键索引树小很多
- 在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一
- 获取总数的各种方法
- MyISAM 表虽然 count (*) 很快,但是不支持事务
- show table status 命令虽然返回很快,但是不准确
- InnoDB 表直接 count (*) 会遍历全表,虽然结果准确,但会导致性能问题
- 不同的 count 用法
- count ( id) :遍历整张表,把每一行的 id 值取出来返回给server层进行是否为空,并累加结果
- count (1):InnoDB 引擎遍历整张表,但不取值,server 层对每一行放入“1”,并
- count (字段):取出字段判断定义是否为 not null,若是,按行累加,若否,判断为非null才累加
- count (*):并不取出全部字段,而是专门做优化,不取值,直接按行累加
- 按照效率排序:count (字段)<count (主键 id)<count (1)≈count (*)
order by
-
MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer
-
sort_buffer_size,就是 MySQL 为排序开辟的内存(sort_buffer)的大小
- 如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成
- 如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序
-
max_length_for_sort_data,是 MySQL 中专门控制用于排序的行数据的长度的一个参数
- 如果单行的长度超过这个值,MySQL 就认为单行太大,要换一个算法(全排序 -> rowid
-
全字段排序
-
如何查看 OPTIMIZER_TRACE
-
/* 打开optimizer_trace,只对本线程有效 */ SET optimizer_trace='enabled=on'; /* @a保存Innodb_rows_read的初始值 */ select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read'; /* 执行语句 */ select city, name,age from t where city='杭州' order by name limit 1000; /* 查看 OPTIMIZER_TRACE 输出 */ SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G /* @b保存Innodb_rows_read的当前值 */ select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read'; /* 计算Innodb_rows_read差值 */ select @b-@a;
-
全排序的 OPTIMIZER_TRACE 部分结果
-
number_of_tmp_files 表示的是,排序过程中使用的临时文件数
- sort_buffer_size 超过了需要排序的数据量的大小,就是 0,表示排序可以在内存中完成
- sort_buffer_size 越小,需要分成的份数越多,number_of_tmp_files 的值就越大
- 内存放不下时,就需要使用外部排序,外部排序一般使用归并排序算法
- MySQL 将需要排序的数据分成 12 份,每一份单独排序后存在这些临时文件中
- 然后把这 12 个有序文件再合并成一个有序的大文件
- sort_mode 中的 packed_additional_fields 的意思排序过程对字符串做了 “紧凑” 处理
- 即使字段的定义是 varchar (16),在排序过程中还是要按照实际长度来分配空间的
-
-
rowid 排序
-
对比全字段排序流程,rowid排序多访问了一次表 t 的主键索引,即步骤7
-
rowid 排序的 OPTIMIZER_TRACE 部分输出
- sort_mode 变成了 <sort_key, rowid>,表示参与排序的只有 name 和 id 这两个字段
- number_of_tmp_files 变成 10
- 虽然参与排序的行数仍然是4000行,但每一行都变小,需要排序的总数据量也就变小
-
-
全字段排序 VS rowid 排序
- MySQL 的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问
- 如果 MySQL 实在是担心排序内存太小,会影响排序效率,才会采用 rowid 排序算法
- 需要再回到原表去取数据
- 如果 MySQL 认为内存足够大,会优先选择全字段排序
- 排序后就会直接从内存里面返回查询结果,不用再回到原表去取数据
- 对于 InnoDB 表来说,rowid 排序会要求回表多造成磁盘读,因此不会被优先选择
-
通过设计好的联合索引字段列可以避免文件排序(利用联合索引本身有序的特性
临时表
- 先通过原理分析算出扫描行数,然后再通过查看慢查询日志,来验证自己的结论
- 内存临时表
-
回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘
- memory 引擎创建内存临时表
- order by rand () 使用了内存临时表,内存临时表排序的时候使用了 rowid 排序方法
-
随机排序完整流程图
- pos:对于索引组织表就是主键ID,对于堆表就是行记录物理地址指针
-
- 磁盘临时表
-
tmp_table_size 这个配置限制了内存临时表的大小,默认值是 16M
- 临时表大小超过了 tmp_table_size,那么内存临时表就会转成磁盘临时表
-
磁盘临时表使用的引擎默认是 InnoDB,是由参数 internal_tmp_disk_storage_engine 控制的
- 没有显式索引的 InnoDB 表
-
采用是 MySQL 5.6 版本引入的一个新的排序算法,即:优先队列排序算法
-
优先队列排序算法示例图
-
filesort_priority_queue_optimization chosen=true,表示使用了优先队列排序算法
-
当 limit 数过大,超过我们设置的 sort_buffer_size 大小,就只能使用归并排序算法
-
-