MySQL 的常用维护语句

無糖收录于类别 MySQL

2020-11-05 2020-11-05 约 1462 字预计阅读 3 分钟

在日常开发中遇到的一些问题。

一、查看数据库的各个表占用的文件大小

以查看 test 数据库为例：

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    engine AS '存储引擎',
    table_comment AS '备注',
    table_rows AS '记录数',
    TRUNCATE (data_length / 1024 / 1024, 2) AS '数据大小(MB)',
    TRUNCATE (index_length / 1024 / 1024, 2) AS '索引大小(MB)'
FROM
    information_schema.TABLES
WHERE
    table_schema = 'test'
ORDER BY
    table_rows DESC;

二、查找持续时间超过 60s 的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

三、查看当前线程处理情况

配套 kill 语句可以处理突发事件

show full processlist;
kill Id;

四、优化表

optimize table `table_name_A`;

重新组织表数据和相关索引数据的物理存储，以减少存储空间，提高访问表时的 I/O 效率。

但此操作会锁表，需要避开业务高峰期。

五、重建表

alter table A engine=InnoDB;

试想一下，如果你现在有一个表 A，需要做空间收缩，为了把表中存在的空洞去掉，你可以怎么做呢？你可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。这里，你可以使用 alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。

而在 MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。

我给你简单描述一下引入了 Online DDL 之后，重建表的流程：

建立一个临时文件，扫描表 A 主键的所有数据页；
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
用临时文件替换表 A 的数据文件。

可以看到，不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。

DDL 之前是要拿 MDL 写锁的，这样还能叫 Online DDL 吗？

alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。而对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。

需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用 GitHub 开源的 gh-ost 来做。

另外，使用 alter table t engine=InnoDB 有可能会让一个表占用的空间反而变大：

1、就是这个表本身就已经没有空洞，比如说刚刚做过一次重建表操作；

2、在 DDL 期间，如果刚好有外部的 DML 在执行，这期间可能会引入一些新的空洞；

3、在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是 “最” 紧凑的。

目录