MySql 表数据优化 date_free过大碎片整理

-- 获取表数据量排行
SELECT
table_schema ,
table_name ,
data_free,
table_rows ,
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)',
TRUNCATE ( ( data_length + index_length ) / 1024 / 1024, 2 ) AS '总大小(MB)'
FROM information_schema.TABLES a
WHERE TABLE_SCHEMA = 'dfs_chen'
ORDER BY ( data_length + index_length ) DESC
LIMIT 20;

 

 

 date_free过大可以碎片整理，

-- 碎片整理 使用不要太频繁
OPTIMIZE table dfs_chen.dfs_operation_log

-- Table does not support optimize, doing recreate + analyze instead用:

ALTER TABLE dfs_chen.dfs_operation_log ENGINE='InnoDB';