数据库端查询的表大小 和 实体物理文件的大小 相比较 差距较大##数据库查询的该表大小为1.9G
mysql> select
-> table_schema as 'db',
-> table_name as 'table',
-> table_rows as 'count',
-> truncate(data_length/1024/1024, 2) as 'data(MB)',
-> truncate(index_length/1024/1024, 2) as 'index(MB)'
-> from information_schema.tables
-> order by data_length desc, index_length desc;
+--------------------+----------------------------------------------------+---------+----------+-----------+
| db | table | count | data(MB) | index(MB) |
+--------------------+----------------------------------------------------+---------+----------+-----------+
| openapi | log_r_esb | 5312774 | 1957.00 | 0.00 |
###文件系统该目录的查询的 该表文件 大小为5G ,
[root@poipredis03 openapi]# du -s * |sort -nr
5971972 log_r_esb.ibd
请问,应该如何解释?
mysql> select
-> table_schema as 'db',
-> table_name as 'table',
-> table_rows as 'count',
-> truncate(data_length/1024/1024, 2) as 'data(MB)',
-> truncate(index_length/1024/1024, 2) as 'index(MB)'
-> from information_schema.tables
-> order by data_length desc, index_length desc;
+--------------------+----------------------------------------------------+---------+----------+-----------+
| db | table | count | data(MB) | index(MB) |
+--------------------+----------------------------------------------------+---------+----------+-----------+
| openapi | log_r_esb | 5312774 | 1957.00 | 0.00 |
###文件系统该目录的查询的 该表文件 大小为5G ,
[root@poipredis03 openapi]# du -s * |sort -nr
5971972 log_r_esb.ibd
请问,应该如何解释?
对于date_length,官方的解释:
• DATA_LENGTH
For MyISAM, DATA_LENGTH is the length of the data file, in bytes.
For InnoDB, DATA_LENGTH is the approximate amount of memory allocated for the clustered index,
in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.
mysql> select table_catalog
-> ,table_schema
-> ,table_name
-> ,engine
-> from information_schema.tables
-> where table_schema='openapi' and table_name='log_r_esb';
+---------------+--------------+------------+--------+
| table_catalog | table_schema | table_name | engine |
+---------------+--------------+------------+--------+
| def | openapi | log_r_esb | InnoDB |
+---------------+--------------+------------+--------+
可能是因为表碎片,删除了数据,OS文件并不会收缩,但是表的大小是会变小的。所以就造成了OS文件里有了碎片化空间。
-》谢谢