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 = 'sys';

— 查看指定数据库各表容量大小

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 = 'sys' -- 查看mysql库各表容量大小
ORDER BY
data_length DESC,
index_length DESC;

–查询某库中所有表碎片空间大小

SELECT
  CONCAT( table_schema, '.', table_name ) AS TABLE_NAME,
  ENGINE AS TABLE_ENGINE,
  table_type AS TABLE_TYPE,
  table_rows AS TABLE_ROWS,
  CONCAT( ROUND( data_length / ( 1024 * 1024 ), 2 ), 'M' ) AS TB_DATA_SIZE,
  CONCAT( ROUND( index_length / ( 1024 * 1024 ), 2 ), 'M' ) AS TB_IDX_SIZE,
  CONCAT( ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2 ), 'M' ) AS TOTAL_SIZE,
CASE
    
    WHEN data_length = 0 THEN
    0 ELSE ROUND( index_length / data_length, 2 ) 
  END AS TB_INDX_RATE,
  CONCAT( ROUND( data_free / 1024 / 1024, 2 ), 'MB' ) AS TB_DATA_FREE,
CASE
    
    WHEN ( data_length + index_length ) = 0 THEN
    0 ELSE ROUND( data_free /( data_length + index_length ), 2 ) 
  END AS TB_FRAG_RATE 
FROM
  information_schema.TABLES 
WHERE
  table_schema = '库名' 
  AND ROUND( DATA_FREE / 1024 / 1024, 2 ) >= 50 
ORDER BY
  data_free DESC;

— 清理碎片空间 相关链接

mysql> OPTIMIZE TABLE 表名;

对于 InnoDB 存储引擎，OPTIMIZE TABLE 实际上被映射为 ALTER TABLE … FORCE 命令，该命令会重建表以更新索引统计信息并释放聚簇索引中的未使用空间。当您在一个表上运行 OPTIMIZE TABLE 命令时，它在输出中显示为以下形式：

mysql> OPTIMIZE TABLE foo;

+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

这表示 OPTIMIZE TABLE 命令正在处理 InnoDB 表，并执行相应的操作来优化表的性能和空间利用。

看着别扭的话 直接跳官网吧（

）

打赏

微信扫一扫，打赏作者吧～