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 t：ALTER + ANALYZE

参考资料

《MySQL实战45讲》