MySql查看数据库及表容量大小并排序

MySql查看数据库及表容量大小并排序

带刀医生关注IP属地: 江苏

2022.04.11 20:05:34字数 85阅读 1,219

MySql查看数据库及表容量⼤⼩并排序查看所有数据库容量⼤⼩

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 表名;

参考文章