快捷導(dǎo)航

MySQL 數(shù)據(jù)庫空間使用大小查詢的方法實(shí)現(xiàn)

更新時(shí)間：2025年07月18日 08:28:49 作者：csdn_aspnet

本文主要介紹了MySQL 數(shù)據(jù)庫空間使用大小查詢的方法實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、查詢數(shù)據(jù)庫空間大小

1. 查看所有數(shù)據(jù)庫空間

SELECT
  table_schema AS '數(shù)據(jù)庫',
  SUM(table_rows) AS '記錄數(shù)',
  SUM(TRUNCATE(data_length/1024/1024,2)) AS '數(shù)據(jù)容量(MB)',
  SUM(TRUNCATE(index_length/1024/1024,2)) AS '索引容量(MB)',
  SUM(TRUNCATE((data_length+index_length)/1024/1024,2)) AS '總大小(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC;

此語句統(tǒng)計(jì)所有數(shù)據(jù)庫的總數(shù)據(jù)量、索引量及碎片空間，結(jié)果按數(shù)據(jù)容量降序排列。

使用

SELECT
  table_schema AS '數(shù)據(jù)庫',
  SUM(table_rows) AS '記錄數(shù)',
  SUM(TRUNCATE(data_length/1024/1024,2)) AS '數(shù)據(jù)容量(MB)',
  SUM(TRUNCATE(index_length/1024/1024,2)) AS '索引容量(MB)',
  SUM(TRUNCATE((data_length+index_length)/1024/1024,2)) AS '總大小(MB)'
FROM information_schema.tables
WHERE table_schema='sftzhzx_jy-241220'
GROUP BY table_schema
ORDER BY SUM(data_length) DESC;

SELECT
  table_schema AS '數(shù)據(jù)庫',
  SUM(table_rows) AS '記錄數(shù)',
  SUM(TRUNCATE(data_length/1024/1024,2)) AS '數(shù)據(jù)容量(MB)',
  SUM(TRUNCATE(index_length/1024/1024,2)) AS '索引容量(MB)',
  SUM(TRUNCATE((data_length+index_length)/1024/1024,2)) AS '總大小(MB)'
FROM information_schema.tables
WHERE table_schema='sftzhzx_jd'
GROUP BY table_schema
ORDER BY SUM(data_length) DESC;

2. 查看指定數(shù)據(jù)庫空間

SELECT
  table_schema AS '數(shù)據(jù)庫',
  SUM(data_length + index_length)/1024/1024 AS '總大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
GROUP BY table_schema;

替換 your_database_name 后，可獲取特定數(shù)據(jù)庫的總空間占用。

使用

SELECT
  table_schema AS '數(shù)據(jù)庫',
  SUM(data_length + index_length)/1024/1024 AS '總大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'sftzhzx_jy-241220'
GROUP BY table_schema;

SELECT
  table_schema AS '數(shù)據(jù)庫',
  SUM(data_length + index_length)/1024/1024 AS '總大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'sftzhzx_jd'
GROUP BY table_schema;

二、查詢表級(jí)空間占用

1. 查看數(shù)據(jù)庫中所有表空間

SELECT
  table_name AS '表名',
  TRUNCATE(data_length/1024/1024,2) AS '數(shù)據(jù)容量(MB)',
  TRUNCATE(index_length/1024/1024,2) AS '索引容量(MB)',
  TRUNCATE((data_length+index_length)/1024/1024,2) AS '總大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC;

用于分析指定數(shù)據(jù)庫內(nèi)各表的空間分布，識(shí)別大表。

2. 精確查詢單表空間

SELECT
  table_name AS '表名',
  TRUNCATE((data_length + index_length)/1024/1024,2) AS '總大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
  AND table_name = 'your_table_name';

適用于定位具體表的空間占用情況。

三、空間管理建議

1、自動(dòng)擴(kuò)展配置

在 my.cnf 配置文件中設(shè)置自動(dòng)擴(kuò)展參數(shù)，避免空間不足：

[mysqld]
innodb_data_file_path = ibdata1:10M:autoextend

2、獨(dú)立表空間優(yōu)化

啟用獨(dú)立表空間可提升管理靈活性：

SET GLOBAL innodb_file_per_table = 1;

3、定期清理碎片

對(duì)頻繁更新的表執(zhí)行優(yōu)化命令：

OPTIMIZE TABLE your_table_name;

以上方法結(jié)合系統(tǒng)表查詢與配置優(yōu)化，可有效管理和監(jiān)控?cái)?shù)據(jù)庫空間。

到此這篇關(guān)于MySQL 數(shù)據(jù)庫空間使用大小查詢的方法實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL 空間使用大小查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: