如何在MySQL中找到數據庫的大小

要查看MySQL數據庫的大小，1. 可通過查詢information_schema數據庫獲取指定數據庫的總大小（含數據和索引），使用SUM(DATA_LENGTH INDEX_LENGTH)並轉換為MB；2. 查詢所有數據庫時，按總大小降序排列以識別佔用空間最多的數據庫；3. 查看單個數據庫內各表的大小分佈，可幫助發現需優化的大表，所有查詢均安全快速，因information_schema僅存儲元數據。

To find the size of a database in MySQL, you can use a simple SQL query that pulls information from the information_schema database, which stores metadata about all other databases.

Here's how to do it:

1. Check the Size of a Specific Database

Run this query, replacing 'your_database_name' with the actual name of your database:

 SELECT 
  TABLE_SCHEMA AS 'Database Name',
  ROUND(SUM(DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
GROUP BY TABLE_SCHEMA;

This gives you the total size of the database in megabytes, including both data and indexes.

2. Check the Size of All Databases

If you want to see the size of all databases on the server:

 SELECT 
  TABLE_SCHEMA AS 'Database Name',
  ROUND(SUM(DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY SUM(DATA_LENGTH INDEX_LENGTH) DESC;

This helps identify which databases are using the most space.

3. Breakdown by Table Within a Database

To see how much space each table takes in a specific database:

 SELECT 
  TABLE_NAME AS 'Table',
  ROUND((DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY (DATA_LENGTH INDEX_LENGTH) DESC;

This is useful for spotting large tables that might need optimization.

Notes:

• Sizes are calculated in bytes, so dividing by 1024 * 1024 converts to MB.
• DATA_LENGTH is the size of the actual data.
• INDEX_LENGTH is the size of the indexes (which also consume disk space).
• The information_schema only contains metadata and doesn't store the actual data, so this method is safe and fast.

Basically, just run the right query in your MySQL client (like MySQL Workbench or command line), and you'll get accurate size info.

以上是如何在MySQL中找到數據庫的大小的詳細內容。更多資訊請關注PHP中文網其他相關文章！