Mysql查询库、表、索引、碎片大小

查看表和索引和大小
SELECT
TABLE_NAME,
DATA_LENGTH,
INDEX_LENGTH,
(DATA_LENGTH + INDEX_LENGTH) AS LENGTH,
TABLE_ROWS,
CONCAT (ROUND ((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024/1024, 3), ‘GB’) AS total_size
FROM
information_schema.TABLES
WHERE TABLE_SCHEMA = ‘database_name’
– AND table_name = ‘table_name’
ORDER BY LENGTH DESC;

查看表的分区信息
SELECT partition_name part,partition_expression expr,partition_description descr,table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=‘table_name’ and table_rows >0;

查看碎片大小
SELECT TABLE_SCHEMA
,TABLE_NAME
,ENGINE
,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024/1024), 2) AS SIZE_GB
,ROUND(DATA_FREE/1024/1024/1024,2) AS FREE_SIZ_GB
,ROUND((DATA_LENGTH + INDEX_LENGTH+DATA_FREE)/1024/1024/1024,2) AS TOTLE_GB
FROM information_schema.TABLES
ORDER BY FREE_SIZ_MB DESC;

查看库大小
select
table_schema as ‘数据库’,
sum(table_rows) as ‘记录数’,
sum(truncate(data_length/1024/1024/1024, 2)) as ‘数据容量(GB)’,
sum(truncate(index_length/1024/1024/1024, 2)) as ‘索引容量(GB)’
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;