MySQL命令统计的库大小和物理文件大小差异

一、MySQL数据库容量查询方法

1.1 查看所有数据库容量大小

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

1.2 看所有数据库各表容量大小

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;

1.3 查看指定数据库容量大小（如mysql库）

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='mysql'
order by data_length desc, index_length desc;

二、命令统计表/库空间大小与表物理文件大小

2.1 MySQL表物理文件介绍

存储引擎是myisam, 在data目录下会看到3类文件：.frm、.myi、.myd
（1）*.frm--表定义，是描述表结构的文件。
（2）*.MYD--"D"数据信息文件，是表的数据文件。
（3）*.MYI--"I"索引信息文件，是表数据文件中任何索引的数据树

存储引擎是InnoDB, 在data目录下会看到2类文件：.frm、.ibd
（1）*.frm--表结构的文件。
（2）*.ibd--表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

2.2 对比MySQL命令统计数据大小与真实物理文件差异

（1）命令统计confluence库表大小

mysql> 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='confluence'
    -> order by data_length desc, index_length desc;
+------------+--------------------------------+-----------+------------------+------------------+
| 数据库      | 表名                           | 记录数    | 数据容量(MB)     | 索引容量(MB)       |
+------------+--------------------------------+-----------+------------------+------------------+
| confluence | BODYCONTENT                    |      3836 |            72.46 |             0.18 |
| confluence | scheduler_run_details          |    606729 |            51.57 |            90.40 |
| confluence | EVENTS                         |     14428 |            10.09 |             9.09 |

（2）查看confluence库中表物理文件大小

[root@blogs-v2 confluence]# ll -h -S
total 404M
-rw-r----- 1 polkitd input 164M May 18 18:26 scheduler_run_details.ibd
-rw-r----- 1 polkitd input  84M May 18 17:35 BODYCONTENT.ibd
-rw-r----- 1 polkitd input  29M May 18 17:35 EVENTS.ibd

通过（1）（2）对比不难发现，以BODYCONTENT、scheduler_run_details、EVENTS  表为例，命令统计其数据文件大小分别是72.46MB、51.57MB、10.09MB，而查看对应表的物理文件，发现大小分别是164MB、84MB、29MB。三张表的物理文件大小是命令行统计大小的2-3倍左右，那么为什么会出现这种情况呢？命令统计大小不应该和实际物理文件一样大嘛？当然不是！！！！

在现实生产环境中，这种差异有时会达到数十倍关系，导致数据库磁盘空间暴涨，为了避免业务影响，通常需要扩容更大的磁盘空间应对，但这也间接增加了业务成本。那么我们就来讲讲导致这种现象的第一种可能：数据碎片。详情参考：MySQL删除数据空间没有释放-碎片 

由上述我们知道了第一种可能是数据碎片，下面我们就来探讨一下第二种可能：随着数据的增长物理文件本身会占用较大磁盘空间。这就好比为了让房子住更多的人，要加盖或者扩建一样，自然房子也越来越重。

+------------+--------------------------------+-----------+------------------+------------------+
| 数据库      | 表名                           | 记录数    | 数据容量(MB)     | 索引容量(MB)       |
+------------+--------------------------------+-----------+------------------+------------------+
| confluence | EVENTS                         |     14428 |            10.09 |             9.09 |
+------------+--------------------------------+-----------+------------------+------------------+

#（1）导出EVENTS表中的数据备用
[root@blogs-v2 ~]# mysqldump -uroot -P33306 -h$IP -p$Password confluence EVENTS > events.sql
[root@blogs-v2 ~]# ll -h
total 5.9M
-rw-r--r-- 1 root root 5.9M May 19 10:10 events.sql # 可以看出导出前events数据文件+索引是19.18MB导出后是5.9MB肯定是被压缩了

#（2）新建测试库test，新建表events
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE `EVENTS` (
    ->   `rev` varchar(255) COLLATE utf8mb4_bin NOT NULL,
    ->   `history` varchar(255) COLLATE utf8mb4_bin NOT NULL,
    ->   `partition` int(11) NOT NULL,
    ->   `sequence` int(11) NOT NULL,
    ->   `event` mediumblob,
    ->   `contentid` bigint(20) NOT NULL,
    ->   `inserted` datetime(6) NOT NULL,
    ->   PRIMARY KEY (`rev`,`history`),
    ->   UNIQUE KEY `e_h_r_idx` (`history`,`rev`),
    ->   UNIQUE KEY `e_h_p_s_idx` (`history`,`partition`,`sequence`),
    ->   KEY `e_c_i_idx` (`contentid`,`inserted`),
    ->   KEY `e_i_c_idx` (`inserted`,`contentid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

#（3）此时查看空表物理文件大小
[root@blogs-v2 test]# ll -h -S
total 176K
-rw-r----- 1 polkitd input 160K May 19 10:21 EVENTS.ibd
-rw-r----- 1 polkitd input 8.6K May 19 10:21 EVENTS.frm
-rw-r----- 1 polkitd input   60 May 19 10:20 db.opt

#（4）向test库中还原数据
mysql> source /root/events.sql

#（5）命令查看表数据大小和查看物理文件大小
+--------------------+-----------------+-----------+------------------+------------------+
| 数据库              | 表名            | 记录数    | 数据容量(MB)      | 索引容量(MB)      |
+--------------------+-----------------+-----------+------------------+------------------+
| test               | EVENTS          |     14065 |             6.57 |            13.15 |
+--------------------+-----------------+-----------+------------------+------------------+

[root@blogs-v2 test]# ll -h -S
total 28M
-rw-r----- 1 polkitd input  27M May 19 10:26 EVENTS.ibd
-rw-r----- 1 polkitd input 8.6K May 19 10:26 EVENTS.frm
-rw-r----- 1 polkitd input   60 May 19 10:20 db.opt

通过上述实验不难发现空表EVENTS占用磁盘空间160k，随着导入5.9MB events.sql的数据备份，命令查看EVENTS表数据文件大小是6.57MB，EVENTS物理文件大小是27MB，接近5倍的关系。当然这里出现了命令行统计的索引文件大小为13.15MB和物理统计的索引文件大小为8.6k，有较大差异的，我暂时没有搞明白为什么，命令统计偏差这么大。待后续完善~