MySql查看数据库及表容量大小并排序
带刀医生IP属地: 江苏 2022.04.11 20:05:34字数 85阅读 1,219MySql查看数据库及表容量⼤⼩并排序查看所有数据库容量⼤⼩
SELECT
table_schema AS '数据库',
sum(table_rows) AS '记录数',
sum(
TRUNCATE (data_length / 1024 / 1024, 2)
) AS '数据容量(MB)',
sum(
TRUNCATE (index_length / 1024 / 1024, 2)
) AS '索引容量(MB)'
FROM
information_schema. TABLES
GROUP BY
table_schema
ORDER BY
sum(data_length) DESC,
sum(index_length) DESC;
查看所有数据库各表容量⼤⼩
SELECT
table_schema AS '数据库',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE (data_length / 1024 / 1024, 2) AS '数据容量(MB)',
TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROM
information_schema. TABLES
ORDER BY
data_length DESC,
index_length DESC;
查看指定数据库容量⼤⼩
SELECT
table_schema AS '数据库',
sum(table_rows) AS '记录数',
sum(
TRUNCATE (data_length / 1024 / 1024, 2)
) AS '数据容量(MB)',
sum(
TRUNCATE (index_length / 1024 / 1024, 2)
) AS '索引容量(MB)'
FROM
information_schema.tables where table_schema = 'your_table_name';
查看指定数据库各表容量⼤⼩
SELECT
table_schema AS '数据库',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
information_schema.TABLES
WHERE
table_schema = '指定的库名'
ORDER BY
data_length DESC,
index_length DESC;
查看mysql各表数据存储碎片大小
SELECT
table_schema AS '库名',
table_name AS '表名',
ENGINE AS '存储引擎',
table_rows AS '行数',
trim(
concat(
round(DATA_LENGTH / 1024 / 1024, 1)
)
) AS '数据大小MB',
trim(
round(index_length / 1024 / 1024, 1)
) AS '索引大小MB',
trim(
round(DATA_FREE / 1024 / 1024, 1)
) AS '碎片大小MB'
FROM
information_schema. TABLES
WHERE
table_schema NOT IN (
'information_schema',
'phpmyadmin',
'scripts',
'test',
'performance_schema',
'mysql'
)
AND DATA_FREE / 1024 / 1024 > 1000
ORDER BY
DATA_FREE DESC;
mysql更新数据碎片刷新
OPTIMIZE TABLE 表名;
标签:1024,容量,MB,length,及表,MySql,table,排序,schema
From: https://www.cnblogs.com/yaoyangding/p/17053032.html