–查看数据量较大和碎片较多的表
（1）查看数据表量较大的前10张表。
mysql> SELECT TABLE_SCHEMA AS database_name,
TABLE_NAME AS table_name,
TABLE_ROWS AS table_rows,
ENGINE AS table_engine,
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA NOT IN
(‘performance_schema’,‘mysql’,‘information_schema’)
ORDER BY T1.TABLE_ROWS DESC
LIMIT 10;
（2）查看数据表空间较大的前10张表。
mysql> SELECT TABLE_SCHEMA AS database_name,
TABLE_NAME AS table_name,
TABLE_ROWS AS table_rows,
ENGINE AS table_engine,
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA
NOT IN(‘performance_schema’,‘mysql’,‘information_schema’)
ORDER BY
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)
DESC LIMIT 10;
（3）查看碎片较多的前10张表。
mysql> SELECT
TABLE_SCHEMA AS database_name,
TABLE_NAME AS table_name,
TABLE_ROWS AS table_rows,
ENGINE AS table_engine,
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB,
ROUND(ROUND((DATA_FREE)/1024.0/1024, 2)/
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2)
AS Free_Percent
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA NOT IN
(‘performance_schema’,‘mysql’,‘information_schema’)
AND ROUND(ROUND((DATA_FREE)/1024.0/1024, 2)/
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2)
>10
AND ROUND((DATA_FREE)/1024.0/1024, 2)>100
ORDER BY Free_Percent DESC
LIMIT 10;
