当前位置:网站首页>MySQL实战45讲 学习笔记(六)

MySQL实战45讲 学习笔记(六)

2022-01-15 02:19:38 ordinaryBlog

MySQL性能瞬间下降分析

什么时候会引发flush

  1. redo log 写满。这时候系统会停止所有更新操作,把 checkpoint 往前推进,redo log 留出空间可以继续写
  2. 系统内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰一些数据页,空出内存给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘
  3. MySQL 认为系统“空闲”的时候
  4. MySQL 正常关闭的情况。

显然:以下两种情况会严重影响性能:

一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;
日志写满,更新全部堵住,写性能跌为 0

flush速度控制

影响InnoDB的刷盘速度的因素:

  1. 脏页比例,
  2. redo log 写盘速度

相关参数

  1. innodb_io_capacity 动态调整刷新脏页的数量
  2. innodb_max_dirty_pages_pct 脏页比例上限,默认值是 75%
  3. 脏页比例:Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total (脏页数/总页数)
  4. innodb_flush_neighbors (值为1则邻居连坐 0则关闭)

表内数据的删除

一个InnoDB表包含两个部分:表结构定义和数据

表结构

Mysql 8.0以前表结构存在.frm为后缀的文件里。8.0以后表结构可以被放入系统数据表中

表数据

innodb_file_per_table

为OFF时,数据存放在系统共享表空间中
为ON时,InnoDB 表数据存储在一个以 .ibd 为后缀的文件

注意:一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过 drop table 命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。

表数据删除与复用

  1. 记录的删除与复用:
    InnoDB引擎只会把该记录标记为删除
    记录的复用:
    记录的复用,只限于符合范围条件的数据。
  2. 页的删除与复用
    当整个页从 B+ 树里面摘掉以后,可以复用到任何位置
  3. 当用delete命令把整个表的数据删除后
    所有的数据页都会 被标记为可复用。但是磁盘上,文件不会变小

删除数据

delete 命令其实只是把记录的位置,或者数据页标记为了“可复用”,但磁盘文件的大小是不会变的。也就是说,通过 delete 命令是不能回收表空间的,会有空间被浪费

插入数据

插入数据也会造成空间的浪费:
如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。

更新数据

更新索引上的值,可以理解为删除一个旧的值,再插入一个新值。这也会造成空间上的浪费

如何解决这种空洞

重建表
新建一个与表 A 结构相同的表 B,然后按照主键 ID 递增的顺序,把数据一行一行地从表 A 里读出来再插入到表 B 中
可以使用 alter table A engine=InnoDB 命令来重建表

重建表

MySQL5.5 之前 重建表如下图 会造成数据的丢失
在这里插入图片描述
(图片来自极客时间)
MySQL 5.6之后 开始引入的 Online DDL

  1. 建立一个临时文件,扫描表 A 主键的所有数据页;
  2. 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;
  3. 生成临时文件的过程中,**将所有对 A 的操作记录在一个日志文件(row log)**中,对应的是图中 state2 的状态;
  4. 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件,对应的就是图中 state3 的状态;
  5. 用临时文件替换表 A 的数据文件。

在这里插入图片描述
(图片来自极客时间)
在之前 我们把表 A 中的数据导出来的存放位置叫作 tmp_table。这是一个临时表,是在 server 层创建的,现在根据表 A 重建出来的数据是放在“tmp_file”里的,这个临时文件是 InnoDB在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成

optimize table、analyze table 和 alter table 这三种方式重建表的区别

analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁;
optimize table t 等于 alter table+analyze

版权声明
本文为[ordinaryBlog]所创,转载请带上原文链接,感谢
https://blog.csdn.net/kang2411212/article/details/120809975

随机推荐