1. 前言
声明:本文总结笔记参考 https://www.toutiao.com/i6935264754059477542?wid=1627461136093
最近有个全量同步的功能开发,操作过程是先删除在插入,并通过delete
删除表记录。这样会有一个问题,如下:
已经执行了delete
,表文件的大小为何没减小?
2. 分析问题
delete
后发生了什么?比如想要删除 R4
这条记录:
过程描述:
InnoDB
直接将R4
这条记录标记为删除,称为可复用的位置。- 如果之后要插入
ID
在300
到700
间的记录时,就会复用该位置。 - 由此可见,磁盘文件的大小并不会减少。
原因:MySQL InnoDB
中采用了 B+ 树
作为存储数据的结构,也就是常说的索引组织表,并且数据时按照页来存储的。具体可以参考之前写过的博客《数据库系列课程(17)-索引实现原理(小结)》
3. 如何解决?
3.1 optimize
可以使用OPTIMIZE TABLE
来回收未使用的空间,并整理数据文件的碎片,语法如下:
OPTIMIZE TABLE 表名;
注意:
OPTIMIZE TABLE
只对MyISAM
,BDB
和InnoDB
表起作用。
另外,也可以执行通过ALTER TABLE
重建表
ALTER TABLE 表名 ENGINE=INNODB
3.1 optimize 与alter的区别
alter table t engine = InnoDB
等于recreate
)optimize table t
等于recreate + analyze
4. Online DDL
最后,再说一下Online DDL
,dba
的日常工作肯定有一项是ddl
变更,ddl
变更会锁表,这个可以说是dba
心中永远的痛,特别是执行ddl
变更,导致库上大量线程处于“Waiting for meta data lock
”状态的时候。因此在 5.6
版本后引入了 Online DDL
。
Online DDL
推出以前,执行ddl
主要有两种方式copy
方式和inplace
方式。inplace
方式又称为(fast index creation
)。相对于copy
方式,inplace
方式不拷贝数据,因此较快。但是这种方式仅支持添加、删除索引两种方式,而且与copy
方式一样需要全程锁表,实用性不是很强。Online
方式与前两种方式相比,不仅可以读,还可以支持写操作。
执行online DDL
语句的时候,使用ALGORITHM
和LOCK
关键字,这两个关键字在我们的DDL
语句的最后面,用逗号隔开即可。示例如下:
ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;
ALGORITHM选项:
INPLACE
:替换:直接在原表上面执行DDL的操作。COPY
:复制:使用一种临时表的方式,克隆出一个临时表,在临时表上执
DDL,然后再把数据导入到临时表中,在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间,表不允许DML的操作。DEFAULT
:默认方式,有MySQL自己选择,优先使用INPLACE的方式。
LOCK选项:
SHARE
:共享锁,执行DDL的表可以读,但是不可以写。NONE
:没有任何限制,执行DDL的表可读可写。EXCLUSIVE
:排它锁,执行DDL的表不可以读,也不可以写。DEFAULT
:默认值,也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT,那就是交给MySQL子句去觉得锁还是不锁表。不建议使用,如果你确定你的DDL语句不会锁表,你可以不指定lock或者指定它的值为default,否则建议指定它的锁类型。
执行DDL
操作时,ALGORITHM
选项可以不指定,这时候MySQL
按照INSTANT
、INPLACE
、COPY
的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT
,也是同样的效果。如果指定了ALGORITHM
选项,但不支持的话,会直接报错。
OPTIMIZE TABLE
和ALTER TABLE 表名 ENGINE=INNODB
都支持Oline DDL
,但依旧建议在业务访问量低的时候使用。