資料庫端查詢的表大小 和 物體物理檔案的大小 相比較 差距較大
##資料庫查詢的該表大小為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
請問，應該如何解釋？
uj5u.com熱心網友回復：
可能是因為表碎片，洗掉了資料，OS檔案并不會收縮，但是表的大小是會變小的。所以就造成了OS檔案里有了碎片化空間。對于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.
uj5u.com熱心網友回復：
應該是innodb 引擎，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檔案里有了碎片化空間。
-》謝謝
uj5u.com熱心網友回復：
定期使用opotimize table 優化table，使用時會造成表鎖定轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/54253.html
標籤：MySQL
上一篇：MySQL Structured Query Language
下一篇：SQL mysql優化的要點