手机网站建设的目的,常宁网页设计,wordpress静态,先做网站还是先收集样品从表中删除数据 从表中删除数据#xff0c;也即是delete过程。
什么是表空间
表空间可以看做是InnoDB存储引擎逻辑结构的最高层#xff0c;所有的数据都存放在表空间中。默认情况下#xff0c;InnoDB存储引擎有一个共享表空间idbdata1#xff0c;即所有数据都存放在这个表…从表中删除数据 从表中删除数据也即是delete过程。
什么是表空间
表空间可以看做是InnoDB存储引擎逻辑结构的最高层所有的数据都存放在表空间中。默认情况下InnoDB存储引擎有一个共享表空间idbdata1即所有数据都存放在这个表空间内。如果用户启用了参数 innodb_file_per_table则每张表内的数据可以单独放到一个表空间内。
如果启用了 innodb_file_per_table 参数需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页其他类的数据如回滚undo信息插入缓冲索引页、系统事务信息二次写缓冲Double write buffer等还是存放在原来的共享表空间内。这同时也说明了另一个问题即使在启用了参数 innodb_file_per_table 之后共享表空间还是会不断地增加其大小。
表数据存放
一个InnoDB表包含两部分 即 表结构定义和数据。
表结构定义
在MySQL 8.0版本以前 表结构是存在以.frm为后缀的文件里。
从MySQL 8.0版本开始 则已经允许把表结构定义放在系统数据表中了。 表结构定义占用的空间很小。
表数据
表数据既可以存在共享表空间里 也可以是单独的文件。 这个行为是由参数innodb_file_per_table控制的
这个参数设置为OFF表示的是 表的数据放在系统共享表空间 也就是跟数据字典放在一起。这个参数设置为ON表示的是 每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。
从MySQL 5.6.6版本开始 它的默认值就是ON了。
规范建议你不论使用MySQL的哪个版本 都将这个值设置为ON。 因为 一个表单独存储为一个文件更容易管理 而且在你不需要这个表的时候 通过drop table命令 系统就会直接删除这个文件。 而如果是放在共享表空间中 即使表删掉了 空间也是不会回收的。
清理表数据的方式有以下几种
1drop table删除整个表回收表空间若表数据独立存放则直接删除对应.ibd文件。
2delete from table只是把记录/数据页标记为“可复用”但磁盘文件的大小是不会变。
3truncate table相当于dropcreate。
数据删除流程
问1使用delete删除表中的记录时表空间没有被回收为什么
数据页存放在文件中假设数据页中的一些记录被使用delete删除了这时数据页并不会真正的删除这些记录而是把这些记录标记为删除即被标记删除的位置可以被复用。如果数据页上的所有记录都被删除了则可以复用整个数据页。但是磁盘上文件不会变小。 问2记录的复用和数据页的复用有什么区别
假设要删掉记录R4InnoDB引擎只会把R4记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时可能会复用这个位置。但如果插入的是一个ID是800的行 就不能复用这个位置了。
而当整个数据页中的记录都被删除时整个页从B树里面摘掉可以复用到任何位置。 以下图为例 如果将数据页page A上的所有记录删除以后 page A会被标记为可复用。 这时候如果要插入一条ID50的记录需要使用新页的时候 page A是可以被复用的。 如果相邻的两个数据页利用率都很小 系统就会把这两个页上的数据合到其中一个页上 另外一个数据页就被标记为可复用。
执行delete命令后这些可以复用而没有被使用的空间看起来就像是”空洞“。
实际上 不止是删除数据会造成空洞 插入数据、更新数据也会。
1插入数据造成空洞
如果数据是按照索引递增顺序插入的 那么索引是紧凑的。 但如果数据是随机插入的 就可能造成索引的数据页分裂。假设下图中page A已经满了 这时我要再插入一行数据 会怎样呢 可以看到 由于page A满了 再插入一个ID是550的数据时 就不得不再申请一个新的页面page B来保存数据了。 页分裂完成后 page A的末尾就留下了空洞注意 实际上 可能不止1个记录的位置是空洞 。
2更新数据造成空洞
更新索引上的值 可以理解为删除一个旧的值 再插入一个新值。 不难理解 这也是会造成空洞的。 问如何去掉这些空洞
也就是说 经过大量增删改的表 都是可能存在空洞的。 所以 如果能够把这些空洞去掉 就能达到收缩表空间的目的。
而重建表就可以达到这样的目的。
重建表
重建表的意义在于回收表空间使得表的存储结构更加紧凑
那么表又是如何重建的呢
假设有一个表A需要做空间收缩把表中存在的空洞去掉具体过程如下
1新建一个与表A结构相同的表B
2按照主键ID递增的顺序把数据一行一行地从表A里读出来再插入到表B中
3把表B作为临时表 数据从表A导入表B的操作完成后 用表B替换A 从效果上看 就起到了收缩表A空间的作用。
MySQL可以使用如下命令重建表
alter table A engineInnoDB;
MySQL5.6之前版本重建表示意图 显然 花时间最多的步骤是往临时表插入数据的过程 如果在这个过程中 有新的数据要写入到表A的话 就会造成数据丢失。 因此 在整个DDL过程中 表A中不能有更新。 也就是说 这个DDL不是Online的。MySQL5.6之前版本
在MySQL 5.6版本开始引入的Online DDL 对这个操作流程做了优化。引入了Online DDL之后 重建表的流程
1建立一个临时文件扫描表A主键的所有数据页
2用数据页中表A的记录生成B树存储到临时文件中
3生成临时文件的过程中将所有对A的操作记录在一个日志文件row log中对应的是图中state2的状态
4临时文件生成后将日志文件中的操作应用到临时文件得到一个逻辑数据上与表A相同的数据文件对应的就是图中state3的状态
5用临时文件替换表A的数据文件
MySQL5.6之后版本重建表示意图 由于日志文件的存在允许对表A做增删改操作。 这也就是Online DDL名字的来源所谓Online即对表进行DDL操作的同时允许对该表进行增删改操作DML。
Online DDL过程需要加MDL锁元数据锁过程如下
1拿MDL写锁
2降级成MDL读锁
3真正做DDL
4升级成MDL写锁
5释放MDL锁
之所以加写锁是为了等待加读锁的DDL或DML完成退化为读锁是为了不阻塞DML语句的执行
注1
mysqlmysql 5.6改进重建表过程允许Online DDL。
注2重建表的过程最耗时的是拷贝数据因而该过程中允许DML将会获得比较多的收益这也是Online DDL的意义
注3对于大表进行重建表的操作消耗较大的CPU和IO因而需要考虑在业务不繁忙的情况下进行为保证安全可以使用gh-ost工具
Copy和Inplace
copy和inplace是重建表的两种策略重建表默认使用的是inplace。
可以通过如下命令显示指定重建表的策略
alter table t engineinnodb, ALGORITHM inplace;
alter table t engineinnodb, ALGORITHM copy;
问1copy和inplace有什么区别
1copy的含义当使用ALGORITHMcopy的时候 表示的是强制拷贝表 对应的流程就是上一小节第一张图的操作过程。
2inlace的含义在上一小节第二张图中把表A重建出来的数据存放在“tmp_file”里的 这个临时文件是InnoDB在内部创建出来的。 整个过程都是在InnoDB内部完成。对于server层来说 没有把数据挪动到临时表 是一个“原地”操作 这就是“inplace”名称的来源。所谓inplace是索引创建在原表上直接进行而表数据依然需要数据拷贝区别在于
拷贝过程不记录undo log和redo log二级索引是有序的可以按顺序加载无需Change Buffer因为对于二级索引没有随机写操作 问2如果你有一个1TB的表 现在磁盘间是1.2TB 能不能做一个inplace的DDL呢
答不能。
因为 tmp_file也是要占用临时空间的。我们重建表的这个语句alter table t engineInnoDB 其实隐含的意思是
-- 重建表
alter table t engineinnodb, ALGORITHMinplace; 问3inplace跟Online是不是就是一个意思
答不是。只是在重建表这个逻辑中刚好是这样而已。
比如 如果我要给InnoDB表的一个字段加全文索引 写法是
alter table t add FULLTEXT(field_name);
这个过程是inplace的 但会阻塞增删改操作 是非Online的。
如果说这两个逻辑之间的关系是什么的话 可以概括为
DDL过程如果是Online的 就一定是inplace的反过来未必 也就是说inplace的DDL 有可能不是Online的。 截止到MySQL 8.0 添加全文索引FULLTEXTindex 和空间索引(SPATIAL index)就属于这种情况。 问4使用optimize table、 analyze table和alter table这三种方式重建表有什么区别
1从MySQL 5.6版本开始 alter table t engine InnoDB也就是recreate 默认的就是上一小节第二张图的流程了。
2analyze table t 其实不是重建表 只是对表的索引信息做重新统计 没有修改数据 这个过程中加了MDL读锁。
3optimize table t 等于recreateanalyze。 问5假设现在有人碰到了一个“想要收缩表空间结果适得其反”的情况看上去是这样的
一个表t文件大小为1TB
对这个表执行 alter table t engineInnoDB
发现执行完成后空间不仅没变小还稍微大了一点儿比如变成了1.01TB。
你觉得可能是什么原因呢
答
1这个表本身就已经没有空洞了比如刚做过一次重建表操作。且在重建表的时候InnoDB不会把整张表占满每个页留了1/16给后续的更新用所以重建之后文件就反而变大了。
2重建过程中存在DML执行因而在row log重新应用时又产生了空洞。
