MySQL表大小计算方式、查询结果无增量原因及实际数据存储大小核查咨询

Great question—this is a common point of confusion when moving from MongoDB to MySQL, since their storage allocation models work a bit differently. Let's break this down step by step:

What Your Current Query Measures #

First, a quick correction: Your query labels the result as Size (MB) but since you didn't divide by 1024*1024, the 16384 value you're seeing is bytes (equal to 16KB, which matches InnoDB's default 16KB page size).

Your query sums DATA_LENGTH and INDEX_LENGTH from information_schema.TABLES:

SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH)) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "Books" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

These two values represent pre-allocated disk space, not actual used space:

• DATA_LENGTH: For InnoDB (MySQL's default storage engine), this is the total reserved space for the table's clustered index (which stores your actual row data). For MyISAM, it's the size of the raw data file.
• INDEX_LENGTH: Total reserved space for all secondary indexes on the table.

The sum is how much disk space MySQL has set aside for your table and indexes. This is why adding 20 rows didn't change the number: your new data still fits within the pre-allocated pages. MySQL only allocates new pages when the existing ones are completely filled.

How to View Actual Used Storage #

To track the real space your data and indexes are using, you need to account for DATA_FREE—a column that shows unused space within the pre-allocated pages.

Use this query to see both reserved and actual used space:

SELECT 
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `Reserved Size (MB)`,
  ROUND((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024, 2) AS `Actual Used Size (MB)`,
  ROUND(DATA_FREE / 1024 / 1024, 2) AS `Unused Reserved Space (MB)`
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = "Books" 
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

When you add your 20 rows, you'll notice Actual Used Size (MB) increases immediately (even if Reserved Size stays the same) until you fill up the pre-allocated pages.

Why This Feels Different From MongoDB #

MongoDB's document/collection size metrics typically show you the actual space used by documents plus metadata, which updates right away as you add data. MySQL's model is similar to MongoDB's extent pre-allocation (where MongoDB reserves chunks of space for collections), but your initial query only showed the total reserved space—not what's actually being used. With the Actual Used Size metric, you'll be able to track data size changes just like you did in MongoDB.

内容的提问来源于stack exchange，提问作者Nibedita Pattnaik