mysql:容量查询

一、容量查询

1、使用下面的SQL语句查询表的数据和索引大小

SELECT
SUM(DATA_LENGTH) + SUM(INDEX_LENGTH) AS TOTAL_SIZE
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'pcs' AND TABLE_NAME = 't_pcs_transaction_event';

2、将查询结果转换为M单位

SELECT CONCAT(ROUND(TOTAL_SIZE/1024/1024, 2), 'M') AS TOTAL_SIZE
FROM (
SELECT
SUM(DATA_LENGTH) + SUM(INDEX_LENGTH) AS TOTAL_SIZE
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'pcs' AND TABLE_NAME = 't_pcs_transaction_event'
) AS T;

3、要查询整个数据库的容量--查询结果分为数据大小（DATA_SIZE）、索引大小（INDEX_SIZE）和总大小（TOTAL_SIZE），单位为M。

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024), 2), 'M') AS DATA_SIZE,
CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024), 2), 'M') AS INDEX_SIZE,
CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2), 'M') AS TOTAL_SIZE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'pcs';

3、要查询整个数据库的容量--查询结果分为数据大小（DATA_SIZE）、索引大小（INDEX_SIZE）和总大小（TOTAL_SIZE），单位为G。
 

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024/1024), 2), 'G') AS DATA_SIZE,
CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024/1024), 2), 'G') AS INDEX_SIZE,
CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024/1024, 2), 'G') AS TOTAL_SIZE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'aws';

二、表空间整理

SELECT * from t_pcs_transaction_event;

DELETE from t_pcs_transaction_event where id = '708692980550078464'

optimize table t_pcs_transaction_event