InnoDB的物理存储

  1. InnoDB表的组成:表结构(frm)+数据(ibd)
    • MySQL 8.0开始,允许将表结构定义占用空间很小)放在系统数据表
  2. 控制参数innodb_file_per_table
    • ON:每个InnoDB表数据存储在一个以**.ibd为后缀的文件中,推荐**
      • 更容易管理,DROP TABLE会直接删除这个文件
    • OFF:InnoDB表数据存储在共享表空间
      • DROP TABLE,空间也是不会回收的
1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+

文件空洞

空洞:可以被复用但没有被使用的空间,经过大量增删改的表,都会存在空洞

删除

  1. 如果删掉R4,InnoDB只会将R4标记为删除,如果再插入300~600的记录时,可能会复用这个位置,但磁盘文件不会缩小
    • 记录的复用,仅限于符合范围条件的数据
  2. 如果删除了一个数据页上的所有记录,那么整个数据页都可以被复用
    • 整个页从B+树里摘除后,可以被复用到任何位置
    • 如果将page A上的所有记录删除后,page A会被标记为可复用
      • 当插入ID=50的记录时,需要申请新页page A可以被复用
  3. 如果相邻的两个数据页利用率都很小
    • 系统会把这两个数据页上的数据合并到其中一个页上,另一个数据页就会被标记为可复用
  4. 如果通过DELETE命令删除整个表,那么所有的数据页都会被标记为可复用,但磁盘上的文件同样不会变小
  5. TRUNCATE = DROP + CREATE

插入

  1. 如果数据是随机插入的,就有可能造成索引的数据页分裂
  2. page A已满,如果再插入ID=550的数据,就必须申请一个新的页面page B来保存数据,导致页分裂,留下了空洞

更新

更新索引上的值,等同于先逻辑删除旧值后再插入新值,同样也会造成空洞

重建表

逻辑过程

  1. 新建一个与表A结构相同的表B
  2. 按照主键递增的顺序,把表A中的数据一行一行读出,然后再插入表B
    • 表B的主键索引更紧凑,数据页的利用率也更高
  3. 表B作为临时表,数据从表A导入到表B,然后用表B替换A

重建命令

1
ALTER TABLE A ENGINE=InnoDB;

ALTER TABLE默认会提交前面的事务

Before MySQL 5.5

  1. 与上述的逻辑过程类似,MySQL自动完成转存数据,交换表名和删除旧表等操作
  2. 时间消耗最多的是往临时表Server层)插入数据的过程,在这个过程中,如果新数据要写入表A,就会造成数据丢失
  3. 因此整个DDL过程中,表A是不能执行DML的,即不是Online
  4. MySQL 5.6引入Online DDL

Since MySQL 5.6

  1. 建立一个临时文件InnoDB内部),扫描表A主键的所有数据页
  2. 用数据页中表A的记录生成B+树,存储到临时文件
  3. state 2(日志):生成临时文件的过程中,将所有对A的操作记录在一个日志文件row log)中
  4. state 3(重放):临时文件生成后,将日志文件的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件
  5. 用最新的临时文件替换表A的数据文件
MDL锁
  1. ALTER语句在启动时需要获取MDL写锁,但会在真正拷贝数据之前退化为MDL读锁
    • MDL读锁不会阻塞其他线程对这个表的DML,同时又能阻塞其他线程对这个表的DDL
  2. 对一个大表来说,Online DDL最耗时的过程是拷贝数据到临时表的过程,期间是可以接受DML
    • 相对于整个DDL过程来说,锁的时间非常短,对业务来说,可以认为是Online

性能消耗

  1. 重建表会扫描原表数据构建临时文件(或临时表)
  2. 对于大表来说,重建表会非常消耗IO和CPU资源
  3. 推荐工具:gh-ost

Online + Inplace

  1. tmp_table是一个临时表,在Server层创建的
  2. tmp_file临时文件,在InnoDB内部创建的,整个DDL过程都在InnoDB内部完成
    • 对于Server层来说,并没有把数据挪动到临时表,是个原地操作(Inplace
  3. DDL过程如果是Online的,那一定是Inplace的,反之不成立
    • ALTER TABLE t ADD FULLTEXT(field_name);Inplace的,但会阻塞DML(非Online
1
2
3
4
5
ALTER TABLE A ENGINE=InnoDB;
等同于
mysql> ALTER TABLE t ENGINE=InnoDB, ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0

Inplace对应的是Copy,强制拷贝表到Server

1
2
3
mysql> ALTER TABLE t ENGINE=InnoDB, ALGORITHM=COPY;
Query OK, 100000 rows affected (1.46 sec)
Records: 100000 Duplicates: 0 Warnings: 0

ALTER + ANALYZE + OPTIMIZE

  1. ALTER TABLE t ENGINE=InnoDB重建表
  2. ANALYZE TABLE t:触发表索引信息的重新采样统计
  3. OPTIMIZE TABLE tALTER + ANALYZE

参考资料

《MySQL实战45讲》