MySQL -- 空间回收
InnoDB的物理存储
- InnoDB表的组成:表结构(frm)+数据(ibd)
- MySQL 8.0开始,允许将表结构定义(占用空间很小)放在系统数据表中
- 控制参数
innodb_file_per_table
- ON:每个InnoDB表数据存储在一个以**.ibd为后缀的文件中,推荐**
- 更容易管理,
DROP TABLE
会直接删除这个文件
- 更容易管理,
- OFF:InnoDB表数据存储在共享表空间
DROP TABLE
,空间也是不会回收的
- ON:每个InnoDB表数据存储在一个以**.ibd为后缀的文件中,推荐**
1 | mysql> SHOW VARIABLES LIKE '%innodb_file_per_table%'; |
文件空洞
空洞:可以被复用但没有被使用的空间,经过大量增删改的表,都会存在空洞
删除
- 如果删掉
R4
,InnoDB只会将R4
标记为删除,如果再插入300~600的记录时,可能会复用这个位置,但磁盘文件不会缩小- 记录的复用,仅限于符合范围条件的数据
- 如果删除了一个数据页上的所有记录,那么整个数据页都可以被复用的
- 当整个页从B+树里摘除后,可以被复用到任何位置
- 如果将
page A
上的所有记录删除后,page A
会被标记为可复用- 当插入ID=50的记录时,需要申请新页时
page A
可以被复用
- 当插入ID=50的记录时,需要申请新页时
- 如果相邻的两个数据页利用率都很小
- 系统会把这两个数据页上的数据合并到其中一个页上,另一个数据页就会被标记为可复用
- 如果通过
DELETE
命令删除整个表,那么所有的数据页都会被标记为可复用,但磁盘上的文件同样不会变小 TRUNCATE
=DROP
+CREATE
插入
- 如果数据是随机插入的,就有可能造成索引的数据页分裂
page A
已满,如果再插入ID=550的数据,就必须申请一个新的页面page B
来保存数据,导致页分裂,留下了空洞
更新
更新索引上的值,等同于先逻辑删除旧值后再插入新值,同样也会造成空洞
重建表
逻辑过程
- 新建一个与表A结构相同的表B
- 按照主键递增的顺序,把表A中的数据一行一行读出,然后再插入表B
- 表B的主键索引更紧凑,数据页的利用率也更高
- 表B作为临时表,数据从表A导入到表B,然后用表B替换A
重建命令
1 | ALTER TABLE A ENGINE=InnoDB; |
ALTER TABLE
默认会提交前面的事务
Before MySQL 5.5
- 与上述的逻辑过程类似,MySQL自动完成转存数据,交换表名和删除旧表等操作
- 时间消耗最多的是往临时表(Server层)插入数据的过程,在这个过程中,如果新数据要写入表A,就会造成数据丢失
- 因此整个DDL过程中,表A是不能执行DML的,即不是Online的
- MySQL 5.6引入Online DDL
Since MySQL 5.6
- 建立一个临时文件(InnoDB内部),扫描表A主键的所有数据页
- 用数据页中表A的记录生成B+树,存储到临时文件
- state 2(日志):生成临时文件的过程中,将所有对A的操作记录在一个日志文件(row log)中
- state 3(重放):临时文件生成后,将日志文件的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件
- 用最新的临时文件替换表A的数据文件
MDL锁
ALTER
语句在启动时需要获取MDL写锁,但会在真正拷贝数据之前退化为MDL读锁- MDL读锁不会阻塞其他线程对这个表的DML,同时又能阻塞其他线程对这个表的DDL
- 对一个大表来说,
Online DDL
最耗时的过程是拷贝数据到临时表的过程,期间是可以接受DML- 相对于整个DDL过程来说,锁的时间非常短,对业务来说,可以认为是
Online
- 相对于整个DDL过程来说,锁的时间非常短,对业务来说,可以认为是
性能消耗
- 重建表会扫描原表数据和构建临时文件(或临时表)
- 对于大表来说,重建表会非常消耗IO和CPU资源
- 推荐工具:
gh-ost
Online + Inplace
tmp_table
是一个临时表,在Server层创建的tmp_file
是临时文件,在InnoDB内部创建的,整个DDL过程都在InnoDB内部完成- 对于Server层来说,并没有把数据挪动到临时表,是个原地操作(Inplace)
- DDL过程如果是Online的,那一定是Inplace的,反之不成立
ALTER TABLE t ADD FULLTEXT(field_name);
是Inplace的,但会阻塞DML(非Online)
1 | ALTER TABLE A ENGINE=InnoDB; |
与Inplace对应的是Copy,强制拷贝表到Server层
1 | mysql> ALTER TABLE t ENGINE=InnoDB, ALGORITHM=COPY; |
ALTER + ANALYZE + OPTIMIZE
ALTER TABLE t ENGINE=InnoDB
:重建表ANALYZE TABLE t
:触发表索引信息的重新采样统计OPTIMIZE TABLE t
:ALTER
+ANALYZE
参考资料
《MySQL实战45讲》
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.