1.我们用这个sql检查mysql的大对象信息,
SELECT TABLE_SCHEMA,
TABLE_NAME,
CONCAT(ROUND(DATA_LENGTH/1024/1024),'MB') AS DATA_LENGTH,
CONCAT(ROUND(INDEX_LENGTH/1024/1024),'MB') AS INDEX_LENGTH,
CONCAT(ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024),'MB') AS TOTDB_SIZE,
TABLE_ROWS
FROM information_schema.TABLES
where TABLE_SCHEMA not in ('information_schema','performance_schema','sys')
GROUP BY TABLE_NAME
order by 6 desc limit 50
sql的结果是:
TABLE_SCHEMA TABLE_NAME DATA_LENGTH INDEX_LENGTH TOTDB_SIZE TABLE_ROWS
report report_opr_detl 1078MB 0MB 1078MB 1778206
报告表report_opr_detl的大小为1078MB。行数为1778206,
2.但是当我们检查os文件系统中的表大小时。
报告大小为1.5G。与步骤1的结果不同(1078MB)
[root@pommodb02 report]# du -sh report_opr_detl*
46K report_opr_detl.frm
1.5G report_opr_detl.ibd
它报告表的count(*)为2162772行。它与步骤1的结果不同(1778206行)
mysql> select count(*) from report.report_opr_detl;
+----------+
| count(*) |
+----------+
| 2162772 |
+----------+
1 row in set (0.65 sec)
3.所以我的问题是为什么步骤1和步骤2不同,