凡是涉及到数据库,数据表名字段都需要修改。
-
data_length
、index_length
字段的存储单位是字节,所以我们要除以 2 个 1024 把字节转换为MB。 -
务必将代码中
kalacloud_test_data
改为要查询的数据库名。
文章目录
统计【单个表】数据条目
use '<Database>';
select
count(*)
from
< TableName >;
# 统计具有GET字段值的记录条数
select
count(*)
from
< TableName >
where
operation = "GET";
查看 MySQL【所有库】的容量大小
SELECT
table_schema as 'Database',
sum(table_rows) as 'Number',
sum(
truncate (data_length / 1024 / 1024, 2)
) as 'Data Capacity(MB)',
sum(
truncate (index_length / 1024 / 1024, 2)
) as 'Index Capacity(MB)',
sum(
truncate (DATA_FREE / 1024 / 1024, 2)
) as 'Debris Occupancy(MB)'
from
information_schema.tables
group by
table_schema
order by
sum(data_length) desc,
sum(index_length) desc;
查看 MySQL【指定库】的容量大小
SELECT
table_schema as 'Database',
sum(table_rows) as 'Number',
sum(
truncate (data_length / 1024 / 1024, 2)
) as 'Data Capacity(MB)',
sum(
truncate (index_length / 1024 / 1024, 2)
) as 'Index Capacity(MB)',
sum(
truncate (DATA_FREE / 1024 / 1024, 2)
) as 'Debris Occupancy(MB)'
from
information_schema.tables
where
table_schema = '<Database Name>'
order by
data_length desc,
index_length desc;
查看 MySQL【指定库】中【所有表】的容量大小
SELECT
table_schema as 'Database',
table_name as 'table name',
table_rows as 'Number',
truncate (data_length / 1024 / 1024, 2) as 'DataCapacity(MB)',
truncate (index_length / 1024 / 1024, 2) as 'IndexCapacity(MB)',
truncate (DATA_FREE / 1024 / 1024, 2) as 'DebrisCapacity(MB)'
from
information_schema.tables
where
table_schema = '<DatabaseName>'
order by
data_length desc,
index_length desc;
查看 MySQL【指定库】中【指定表】的容量大小
SELECT
table_name AS "Table Name",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size (MB)"
FROM
information_schema.TABLES
WHERE
table_schema = 'invoice_test'
AND table_name = 'info';
查看 MySQL 数据库中,容量Top10 的表
USE information_schema;
SELECT
TABLE_SCHEMA as 'Database',
table_name as 'TableName',
table_rows as 'Number',
ENGINE as 'StorageEngine',
truncate (data_length / 1024 / 1024, 2) as 'DataCapacity(MB)',
truncate (index_length / 1024 / 1024, 2) as 'IndexCapacity(MB)',
truncate (DATA_FREE / 1024 / 1024, 2) as 'DebrisCapacity(MB)'
from
tables
order by
table_rows desc
limit
10;
查看 MySQL【指定库】中,容量Top10 的表
USE information_schema;
SELECT
TABLE_SCHEMA as 'Database',
table_name as 'TableName',
table_rows as 'Number',
ENGINE as 'StorageEngine',
truncate (data_length / 1024 / 1024, 2) as 'DataCapacity(MB)',
truncate (index_length / 1024 / 1024, 2) as 'IndexCapacity(MB)',
truncate (DATA_FREE / 1024 / 1024, 2) as 'DebrisCapacity(MB)'
from
tables
where
table_schema = '<DatabaseName>'
order by
table_rows desc
limit
10;
最新评论
密码:blog.sirliu.com
本内容密码:blog.sirliu.com 最新整理的文章在这里喔:https://blog.sirliu.com/2018/11/shell_lian_xi_ti.html