【MySQL】私有云查询表数据、清理MySQL大表

2023-09-26 本文已影响0人 Bogon

一、表查询

1.查询所有库表

$ mysql   -h xx.xx.xx.xx   -P  3306  -u  root    -p'XXX'

> show databases;

> select
        table_schema as '数据库',
        table_name as '表名',
        table_rows as '记录数',
        truncate(data_length/1024/1024, 2) as '数据容量(MB)',
        truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

这段 MySQL SQL 查询语句是用来检索数据库中所有表的相关信息。它从 information_schema.tables 表中选择了以下列进行展示：

table_schema：数据库名称
table_name：表名
table_rows：记录数，即表中的行数
truncate(data_length/1024/1024, 2)：数据容量，以兆字节（MB）为单位进行截断显示，计算方式是将 data_length（数据长度）除以 1024 再除以 1024，并保留两位小数
truncate(index_length/1024/1024, 2)：索引容量，以兆字节（MB）为单位进行截断显示，计算方式与数据容量类似

查询结果按照数据容量和索引容量的降序排列，意味着结果集中的表会按照数据容量和索引容量从大到小的顺序排列。

通过执行这个查询语句，你可以获取对数据库中每个表的记录数、数据容量和索引容量等信息，以便更好地了解和优化数据库的性能。

2.查询指定库表

要查询特定的数据库（例如test库）中的所有表，你可以对查询语句进行修改。

在from information_schema.tables的部分添加一个where子句来筛选指定的数据库名称。

以下是修改后的查询语句：

$ mysql   -h xx.xx.xx.xx   -P  3306  -u  root    -p'XXX'

> show databases;

> use test;

> select
    table_schema as '数据库',
    table_name 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 data_length desc, index_length desc;

修改后的查询语句将仅返回test库中的表信息。请确保将'opensys'替换为实际想要查询的数据库名称。

执行修改后的查询语句后，将会获取到test库中每个表的记录数、数据容量和索引容量等信息。

二、清理大表

一位网友发现测试服务器上的一个 log 表达到了 100G。他想这个表太大了，而且测试环境的 log 数据，没啥用，就想着给 delete 掉。
一开始，他就直接执行 delete，发现，执行了半天，没反应。于是，他就从 Navicat 上，强制结束了执行，选择使用 drop 进行执行。执行 drop 命令后，发现花了更久的时间，命令还是没返回执行成功。

他就慌了，更要命的是，这个时候，各个业务线的人，都在反馈，测试环境很卡。最后，他只好坦白从宽，去和领导和DBA 反馈问题了。

我们假设testDB中的testTable有100G ，需要对其数据做清理，但是还需保留该表。

$ mysql   -h xx.xx.xx.xx   -P  3306  -u  root    -p'XXX'
> use testDB;

// new_table克隆old_table的表结构和索引

> create table new_table  like  testTable ;

// old_table改名为backup_table，new_table改名为old_table

> RENAME TABLE  testTable  TO backup_table, new_table TO  testTable;

//  删除旧表及数据

> DROP TABLE backup_table;

对上述操作的解读：

使用create table new_table like testTable;语句创建一个新表new_table，其结构与已存在的表testTable完全相同。
使用RENAME TABLE testTable TO backup_table, new_table TO testTable;语句将原有的表名testTable改为backup_table，并将新建的表new_table改为testTable，实现了旧表重命名和新表替换旧表的操作。
使用DROP TABLE backup_table;语句删除名为backup_table的旧表及其数据。

请注意，在执行这些操作之前，请确保对数据库进行了备份，并仔细检查代码以确保正确性。

此外，如果其他程序或查询使用了旧表的名称，请确保在更改表名后更新相关引用，以避免出现任何错误或不一致性。

上述方法可以避免删除大表时MySQL卡死的原因是，它采用了一种表重命名的方式，而不是直接删除旧表。

这种方法具有以下优点：

表重命名是一个原子操作：MySQL的RENAME TABLE语句是一个原子操作，它会在瞬间完成，所以在重命名期间不会对其他数据库操作造成影响。
重命名操作只需要修改元数据：通过将原表改名为备份表，然后将新表改名为原表，实际上只是修改了数据库的元数据，而不涉及真正的数据移动或复制。这样可以节省大量时间和资源。
删除操作变得简单快速：由于原表已经被重命名为备份表，在删除旧表时只需执行一条简单的DROP TABLE语句，不需要移动或复制任何数据，因此删除过程非常迅速。

总之，采用表重命名的方式可以避免删除大表时MySQL卡死的问题，提高了删除操作的效率和安全性。

MySQL 上 drop 一张上百G 的表需要多长时间？

在MySQL上删除一张上百GB大小的表所需的时间取决于多个因素，包括以下几点：

硬件性能：删除操作涉及到磁盘I/O和数据处理，较高的硬件性能通常能够加快删除速度。
MySQL配置：MySQL的配置参数也会对删除操作的速度产生影响。例如，innodb_buffer_pool_size参数会影响到InnoDB存储引擎的性能，而innodb_file_per_table参数决定了每个表是否有单独的数据文件，可能会影响删除操作。
数据库负载：如果数据库当前正在进行大量的读写操作，删除操作可能会受到其他活动的干扰，导致执行时间延长。
删除方式：如果表使用了外键约束或触发器等特殊功能，删除操作可能会引发相关的级联删除操作或其他复杂逻辑，进而增加删除时间。

基于以上因素的影响，很难给出具体的时间估计。通常情况下，删除一张上百GB大小的表可能需要数分钟到数小时不等的时间。建议在进行大型表的删除操作前做好备份，并在低负载时段进行操作以避免对正常业务造成影响。