如何编辑网站,网站建设的扩展阶段包括,php网站建设案例教程,什么是网络社交平台文章目录 1. SQL 语句为什么变“慢”了1.1 什么情况会引发数据库的 flush 过程呢#xff1f;1.2 四种情况性能分析1.3 InnoDB 刷脏页的控制策略 2. 数据库表的空间回收2.1 innodb_file_per_table参数2.2 数据删除流程2.3 重建表2.4 Online 和 inplace 3. count(*) 语句怎样实现… 文章目录 1. SQL 语句为什么变“慢”了1.1 什么情况会引发数据库的 flush 过程呢1.2 四种情况性能分析1.3 InnoDB 刷脏页的控制策略 2. 数据库表的空间回收2.1 innodb_file_per_table参数2.2 数据删除流程2.3 重建表2.4 Online 和 inplace 3. count(*) 语句怎样实现3.1 为何 InnoDB 不把数字存起来呢3.2 如何保存操作记录总数3.2.1 用缓存系统保存计数3.2.2 在数据库保存计数 3.3 不同的 count 用法(基于 InnoDB 引擎) 1. SQL 语句为什么变“慢”了
InnoDB 在处理更新语句的时候只做了写日志这一个磁盘操作。这个日志叫作 redo log重做日志在更新内存写完 redo log 后就返回给客户端本次更新成功。
当内存数据页跟磁盘数据页内容不一致的时候我们称这个内存页为“脏页”。内存数据写入到磁盘后内存和磁盘上的数据页的内容就一致了称为“干净页”。
这里可以通过一个孔乙己赊账的案例来分析一下 假设原来孔乙己欠账 10 文这次又要赊 9 文。
平时执行很快的更新操作其实就是在写内存和日志而 MySQL 偶尔“抖”一下的那个瞬间可能就是在刷脏页flush
1.1 什么情况会引发数据库的 flush 过程呢
继续用咸亨酒店掌柜的这个例子想一想掌柜在什么情况下会把粉板上的赊账记录改到账本上
第一种场景是粉板满了记不下了。这时候如果再有人来赊账掌柜就只得放下手里的活儿将粉板上的记录擦掉一些留出空位以便继续记账。当然在擦掉之前他必须先将正确的账目记录到账本中才行。 这个场景对应的就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作把 checkpoint 往前推进redo log 留出空间可以继续写 checkpoint 可不是随便往前修改一下位置就可以的。比如图 2 中把 checkpoint 位置从 CP 推进到 CP’就需要将两个点之间的日志浅绿色部分对应的所有脏页都 flush 到磁盘上。之后图中从 write pos 到 CP’之间就是可以再写入的 redo log 的区域。 第二种场景是要记住的事情太多掌柜发现自己快记不住了赶紧找出账本把孔乙己这笔账先加进去。 这种场景对应的就是系统内存不足。当需要新的内存页而内存不够用的时候就要淘汰一些数据页空出内存给别的数据页使用。如果淘汰的是“脏页”就要先将脏页写到磁盘。 第三种场景是生意不忙的时候或者打烊之后。这时候柜台没事掌柜闲着也是闲着不如更新账本。 这种场景对应的就是 MySQL 认为系统“空闲”的时候。当然MySQL“这家酒店”的生意好起来可是会很快就能把粉板记满的所以“掌柜”要合理地安排时间即使是“生意好”的时候也要见缝插针地找时间只要有机会就刷一点“脏页”。 第四种场景是年底了咸亨酒店要关门几天需要把账结清一下。这时候掌柜要把所有账都记到账本上这样过完年重新开张的时候就能就着账本明确账目情况了。 这种场景对应的就是 MySQL 正常关闭的情况。这时候MySQL 会把内存的脏页都 flush 到磁盘上这样下次 MySQL 启动的时候就可以直接从磁盘上读数据启动速度会很快。 1.2 四种情况性能分析
第三种情况是属于 MySQL 空闲时的操作这时系统没什么压力而第四种场景是数据库本来就要关闭了。这两种情况下不会太关注“性能”问题。所以这里主要来分析一下前两种场景下的性能问题。
第一种是“redo log 写满了要 flush 脏页”这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候整个系统就不能再接受更新了所有的更新都必须堵住。如果你从监控上看这时候更新数会跌为 0。第二种是“内存不够用了要先将脏页写到磁盘”这种情况其实是常态。 InnoDB 用缓冲池buffer pool管理内存缓冲池中的内存页有三种状态 还没有使用的使用了并且是干净页使用了并且是脏页。
InnoDB 的策略是尽量使用内存因此对于一个长时间运行的库来说未被使用的页面很少。
而当要读入的数据页没有在内存的时候就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉如果要淘汰的是一个干净页就直接释放出来复用但如果是脏页呢就必须将脏页先刷到磁盘变成干净页后才能复用。
刷脏页虽然是常态但是出现以下这两种情况都是会明显影响性能的
一个查询要淘汰的脏页个数太多会导致查询的响应时间明显变长日志写满更新全部堵住写性能跌为 0这种情况对敏感业务来说是不能接受的。
所以InnoDB 需要有控制脏页比例的机制来尽量避免上面的这两种情况。
1.3 InnoDB 刷脏页的控制策略
首先要正确地告诉 InnoDB 所在主机的 IO 能力这样 InnoDB 才能知道需要全力刷脏页的时候可以刷多快。
用到 innodb_io_capacity 这个参数了它会告诉 InnoDB 你的磁盘能力。建议设置成磁盘的 IOPS。 磁盘的 IOPS 可以通过 fio 这个工具来测试 测试磁盘随机读写的命令 fio -filename$filename -direct1 -iodepth 1 -thread -rwrandrw -ioenginepsync -bs16k -size500M -numjobs10 -runtime10 -group_reporting -namemytest InnoDB 怎么控制引擎按照“全力”的百分比来刷脏页 刷盘速度就是要参考这两个因素
一个是脏页比例一个是 redo log 写盘速度。
参数 innodb_max_dirty_pages_pct 是脏页比例上限默认值是 75%。 InnoDB 会根据当前的脏页比例假设为 M算出一个范围在 0 到 100 之间的数字,伪代码如下: F1(M) { if Minnodb_max_dirty_pages_pct then return 100; return 100*M/innodb_max_dirty_pages_pct; } InnoDB 每次写入的日志都有一个序号当前写入的序号跟 checkpoint 对应的序号之间的差值我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字这个计算公式可以记为 F2(N)。 F2(N) 算法比较复杂N 越大算出来的值越大 根据上述算得的 F1(M) 和 F2(N) 两个值取其中较大的值记为 R之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。
流程图如下: 要尽量避免这种查询慢的情况就要合理地设置 innodb_io_capacity 的值并且平时要多关注脏页比例不要让它经常接近 75%。
脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的具体的命令参考下面的代码
mysql select VARIABLE_VALUE into a from global_status where VARIABLE_NAME Innodb_buffer_pool_pages_dirty;
select VARIABLE_VALUE into b from global_status where VARIABLE_NAME Innodb_buffer_pool_pages_total;
select a/b;一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时这个查询就可能要比平时慢了。而 MySQL 中的一个机制可能让你的查询会更慢在准备刷一个脏页的时候如果这个数据页旁边的数据页刚好是脏页就会把这个“邻居”也带着一起刷掉而且这个把“邻居”拖下水的逻辑还可以继续蔓延也就是对于每个邻居数据页如果跟它相邻的数据页也还是脏页的话也会被放到一起刷。
在 InnoDB 中innodb_flush_neighbors 参数就是用来控制这个行为的值为 1 的时会有“连坐”机制值为 0 时表示不找邻居仅刷自己。
找“邻居”这个优化在机械硬盘时代是很有意义的可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。 如果使用的是 SSD 这类 IOPS 比较高的设备的话我就建议你把 innodb_flush_neighbors 的值设置成 0
在 MySQL 8.0 中innodb_flush_neighbors 参数的默认值已经是 0 了。 思考 一个内存配置为 128GB、innodb_io_capacity 设置为 20000 的大规格实例正常会建议你将 redo log 设置成 4 个 1GB 的文件。 但如果你在配置的时候不慎将 redo log 设置成了 1 个 100M 的文件会发生什么情况呢又为什么会出现这样的情况呢 每次事务提交都要写 redo log如果设置太小很快就会被写满也就是下面这个图的状态这个“环”将很快被写满write pos 一直追着 CP。这时候系统不得不停止所有更新去推进 checkpoint。这时看到的现象就是磁盘压力很小但是数据库出现间歇性的性能下跌。 2. 数据库表的空间回收
问题 数据库占用空间太大我把一个最大的表删掉了一半的数据怎么表文件的大小还是没变
一个 InnoDB 表包含两部分
表结构定义数据。 在 MySQL 8.0 版本以前表结构是存在以.frm 为后缀的文件里。 而 MySQL 8.0 版本则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小 2.1 innodb_file_per_table参数
参数 innodb_file_per_table控制表数据是存在共享表空间里还是单独的文件中。
这个参数设置为 OFF 表示的是表的数据放在系统共享表空间也就是跟数据字典放在一起这个参数设置为 ON 表示的是每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
从 MySQL 5.6.6 版本开始它的默认值就是 ON 了。 建议不论使用 MySQL 的哪个版本都将这个值设置为 ON。 因为一个表单独存储为一个文件更容易管理而且在你不需要这个表的时候通过 drop table 命令系统就会直接删除这个文件。而如果是放在共享表空间中即使表删掉了空间也是不会回收的 删除整个表的时候可以使用 drop table 命令回收表空间但是遇到的更多的删除数据的场景是删除某些行这就遇到了开头的问题表中的数据被删除了但是表空间却没有被回收。
2.2 数据删除流程
先来看看一个B 树索引示意图 假设要删掉 R4 这个记录InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时可能会复用这个位置。但是磁盘文件的大小并不会缩小。
现在已经知道了 InnoDB 的数据是按页存储的如果删掉了一个数据页上的所有记录整个数据页就可以被复用了。但是数据页的复用跟记录的复用是不同的。
记录的复用只限于符合范围条件的数据。如上面的例子R4 这条记录被删除后如果插入一个 ID 是 400 的行可以直接复用这个空间。但如果插入的是一个 ID 是 800 的行就不能复用这个位置了。而当整个页从 B 树里面摘掉以后可以复用到任何位置。
如果相邻的两个数据页利用率都很小系统就会把这两个页上的数据合到其中一个页上另外一个数据页就被标记为可复用。
如果用 delete 命令把整个表的数据删除所有的数据页都会被标记为可复用。但是磁盘上文件不会变小。
delete 命令只是把记录的位置或者数据页标记为了“可复用”但磁盘文件的大小是不会变通过 delete 命令是不能回收表空间的
不止是删除数据会造成空洞插入数据也会。 如果数据是按照索引递增顺序插入的那么索引是紧凑的。但如果数据是随机插入的就可能造成索引的数据页分裂。 可以看到由于 page A 满了再插入一个 ID 是 550 的数据时就不得不再申请一个新的页面 page B 来保存数据了。 页分裂完成后page A 的末尾就留下了空洞注意实际上可能不止 1 个记录的位置是空洞。 更新索引上的值可以理解为删除一个旧的值再插入一个新值。这也是会造成空洞的。
大量增删改的表都是可能是存在空洞的。所以如果能够把这些空洞去掉就能达到收缩表空间的目的。而重建表就可以达到这样的目的。
2.3 重建表
如果有一个表 A需要做空间收缩为了把表中存在的空洞去掉可以新建一个与表 A 结构相同的表 B然后按照主键 ID 递增的顺序把数据一行一行地从表 A 里读出来再插入到表 B 中。 由于表 B 是新建的表所以表 A 主键索引上的空洞在表 B 中就都不存在了。显然地表 B 的主键索引更紧凑数据页的利用率也更高。如果把表 B 作为临时表数据从表 A 导入表 B 的操作完成后用表 B 替换 A从效果上看就起到了收缩表 A 空间的作用。 可以使用 alter table A engineInnoDB 命令来重建表临时表 B (server层创建)不需要自己创建MySQL 会自动完成转存数据、交换表名、删除旧表的操作。
改锁表 DDL 花时间最多的步骤是往临时表插入数据的过程如果在这个过程中有新的数据要写入到表 A 的话就会造成数据丢失。因此在整个 DDL 过程中表 A 中不能有更新。也就是说这个 DDL 不是 Online 的MySQL 5.6 版本开始引入的 Online DDL对这个操作流程做了优化。 Online DDL 之后重建表的流程
建立一个临时文件(存在tmp_file中)扫描表 A 主键的所有数据页用数据页中表 A 的记录生成 B 树存储到临时文件中生成临时文件的过程中将所有对 A 的操作记录在一个日志文件row log中对应的是图中 state2 的状态临时文件生成后将日志文件中的操作应用到临时文件得到一个逻辑数据上与表 A 相同的数据文件对应的就是图中 state3 的状态用临时文件替换表 A 的数据文件。
Online DDL 上图流程中alter 语句在启动的时候需要获取 MDL 写锁但是这个写锁在真正拷贝数据之前就退化成读锁了。 为什么要退化呢为了实现 OnlineMDL 读锁不会阻塞增删改操作。 那为什么不干脆直接解锁呢为了保护自己禁止其他线程对这个表同时做 DDL。 对于一个大表来说Online DDL 最耗时的过程就是拷贝数据到临时表的过程这个步骤的执行期间可以接受增删改操作。所以相对于整个 DDL 过程来说锁的时间非常短。对业务来说就可以认为是 Online 的。
**重建方法都会扫描原表数据和构建临时文件。对于很大的表来说这个操作是很消耗 IO 和 CPU 资源的。**因此如果是线上服务要很小心地控制操作时间。如果想要比较安全的操作的话推荐使用 GitHub 开源的 gh-ost 来做。
2.4 Online 和 inplace
表 A 使用Online DDL重建出来的数据是放在“tmp_file”里的这个临时文件是 InnoDB 在内部创建出来的。 整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说没有把数据挪动到临时表是一个“原地”操作这就是“inplace”名称的来源。
如果有一个 1TB 的表现在磁盘间是 1.2TB能不能做一个 inplace 的 DDL 呢答案是不能。因为tmp_file 也是要占用临时空间的。
alter table t engineInnoDB其实隐含的意思是
alter table t engineinnodb,ALGORITHMinplace;inplace 对应的就是拷贝表的方式了用法是
alter table t engineinnodb,ALGORITHMcopy;当使用 ALGORITHMcopy 的时候表示的是强制拷贝表对应的流程就是图 《改锁表 DDL》 的操作过程。
Online 和 inplace这两个逻辑之间的关系
DDL 过程如果是 Online 的就一定是 inplace 的反过来未必也就是说 inplace 的 DDL有可能不是 Online 的。截止到 MySQL 8.0添加全文索引FULLTEXT index和空间索引 (SPATIAL index) 就属于这种情况。
从 MySQL 5.6 版本开始alter table t engine InnoDB也就是 recreate默认的就是上面图 4 的流程了analyze table t 其实不是重建表只是对表的索引信息做重新统计没有修改数据这个过程中加了 MDL 读锁optimize table t 等于 recreateanalyze。 思考 假设现在有人碰到了一个“想要收缩表空间结果适得其反”的情况看上去是这样的
一个表 t 文件大小为 1TB对这个表执行 alter table t engineInnoDB发现执行完成后空间不仅没变小还稍微大了一点儿比如变成了 1.01TB。
可能是什么原因呢
这个表本身就已经没有空洞的了比如说刚刚做过一次重建表操作。在 DDL 期间如果刚好有外部的 DML 在执行这期间可能会引入一些新的空洞。
在重建表的时候InnoDB 不会把整张表占满每个页留了 1/16 给后续的更新用。也就是说其实重建表之后不是“最”紧凑的。 3. count(*) 语句怎样实现
在不同的 MySQL 引擎中count(*) 有不同的实现方式。
MyISAM 引擎把一个表的总行数存在了磁盘上因此执行 count(*) 的时候会直接返回这个数效率很高InnoDB 引擎就麻烦了它执行 count(*) 的时候需要把数据一行一行地从引擎里面读出来然后累积计数。 需要注意的是在这里讨论的是没有过滤条件的 count(*)如果加了 where 条件的话MyISAM 表也是不能返回得这么快的。 3.1 为何 InnoDB 不把数字存起来呢 因为即使是在同一个时刻的多个查询由于多版本并发控制MVCC的原因InnoDB 表“应该返回多少行”也是不确定的 举例 假设表 t 中现在有 10000 条记录我们设计了三个用户并行的会话。
会话 A 先启动事务并查询一次表的总行数会话 B 启动事务插入一行后记录后查询表的总行数会话 C 先启动一个单独的语句插入一行记录后查询表的总行数。
假设从上到下是按照时间顺序执行的同一行语句是在同一时刻执行的。
图 1 会话 A、B、C 的执行流程 在最后一个时刻三个会话 A、B、C 会同时查询表 t 的总行数但拿到的结果却不同。 这与InnoDB 的事务设计有关系可重复读是它默认的隔离级别在代码上就是通过多版本并发控制也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见因此对于 count(*) 请求来说InnoDB 只好把数据一行一行地读出依次判断可见的行才能够用于计算“基于这个查询”的表的总行数。 count(*) 操作的优化 InnoDB 是索引组织表主键索引树的叶子节点是数据而普通索引树的叶子节点是主键值。所以普通索引树比主键索引树小很多。对于 count(*) 这样的操作遍历哪个索引树得到的结果逻辑上都是一样的。因此MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下尽量减少扫描的数据量是数据库系统设计的通用法则之一。
show table status 命令的输出结果里面也有一个 TABLE_ROWS索引统计的值是通过采样来估算的。实际上TABLE_ROWS 就是从这个采样估算得来的因此它也很不准。官方文档说误差可能达到 40% 到 50%。所以show table status 命令显示的行数也不能直接使用。
小结一下
MyISAM 表虽然 count(*) 很快但是不支持事务show table status 命令虽然返回很快但是不准确InnoDB 表直接 count(*) 会遍历全表虽然结果准确但会导致性能问题。
3.2 如何保存操作记录总数
3.2.1 用缓存系统保存计数
可以用一个 Redis 服务来保存这个表的总行数。这个表每被插入一行 Redis 计数就加 1每被删除一行 Redis 计数就减 1。这种方式下读和更新操作都很快但缓存系统可能会丢失更新。
将计数保存在缓存系统中的方式还不只是丢失更新的问题。即使 Redis 正常工作这个值还是逻辑上不精确的。
举例 设想一下有这么一个页面要显示操作记录的总数同时还要显示最近操作的 100 条记录。那么这个页面的逻辑就需要先到 Redis 里面取出计数再到数据表里面取数据记录。
会出现一下两种情况
查到的 100 行结果里面有最新插入记录而 Redis 的计数里还没加 1查到的 100 行结果里没有最新插入的记录而 Redis 的计数里已经加了 1。
时序图如下 在并发系统里面我们是无法精确控制不同线程的执行时刻的因为存在图中的这种操作序列所以说即使 Redis 正常工作这个计数值还是逻辑上不精确的。
3.2.2 在数据库保存计数 会话 B 的读操作仍然是在 T3 执行的但是因为这时候更新事务还没有提交所以计数值加 1 这个操作对会话 B 还不可见。因此会话 B 看到的结果里 查计数值和“最近 100 条记录”看到的结果逻辑上就是一致的。 3.3 不同的 count 用法(基于 InnoDB 引擎)
count() 的语义 count() 是一个聚合函数对于返回的结果集一行行地判断如果 count 函数的参数不是 NULL累计值就加 1否则不加。最后返回累计值。 count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数而 count(字段则表示返回满足条件的数据行里面参数“字段”不为 NULL 的总个数。
对于 count(主键 id) 来说InnoDB 引擎会遍历整张表把每一行的 id 值都取出来返回给 server 层。server 层拿到 id 后判断是不可能为空的就按行累加。
对于 count(1) 来说InnoDB 引擎遍历整张表但不取值。server 层对于返回的每一行放一个数字“1”进去判断是不可能为空的按行累加。 对比出来count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行以及拷贝字段值的操作。 对于 count(字段) 来说
如果这个“字段”是定义为 not null 的话一行行地从记录里面读出这个字段判断不能为 null按行累加如果这个“字段”定义允许为 null那么执行的时候判断到有可能是 null还要把值取出来再判断一下不是 null 才累加。
count(*) 是例外并不会把全部字段取出来而是专门做了优化不取值。count(*) 肯定不是 null按行累加。
结论 按照效率排序的话count(字段)count(主键 id)count(1)≈count( * )所以建议尽量使用 count(*)。 思考 由于事务可以保证中间结果不被别的事务读到因此修改计数值和插入新记录的顺序是不影响逻辑结果的。 但是从并发系统性能的角度考虑在这个事务序列里应该先插入操作记录还是应该先更新计数表呢
并发系统性能的角度考虑应该先插入操作记录再更新计数表。因为更新计数表涉及到行锁的竞争先插入再更新能最大程度地减少事务之间的锁等待提升并发度 来自《MySQL实战45讲》林晓斌
