恢復後不同的 MySQL 數據文件大小
我是數據庫管理的新手。我現在公司的數據庫架構是主從複製。我們使用的是 MySQL 5.0.86 版本。
幾週前，我們升級了公司的主要應用程序。此升級為其數據庫的所有表添加了一個列。在升級之前，我們的 ibdata 大小約為 3.1GiB。升級後約為 5.2GiB。
今天，我用 mysqldump 做了一個完整的備份恢復測試。恢復後，ibdata 大小約為 3.6 GiB。標準選擇在恢復中顯示與原始數據庫中相同的數據。
我通過對 information_schema 的選擇檢查了數據大小（CUSTODIA 是我們主應用程序的名稱）：
select SUM(DATA_LENGTH+INDEX_LENGTH) from TABLES where TABLE_SCHEMA='CUSTODIA';這是主數據庫中的結果：
+-------------------------------+ | SUM(DATA_LENGTH+INDEX_LENGTH) | +-------------------------------+ | 5683345068 | +-------------------------------+這是恢復數據庫的結果：
+-------------------------------+ | SUM(DATA_LENGTH+INDEX_LENGTH) | +-------------------------------+ | 3735748608 | +-------------------------------+我的問題：
- 為什麼原始和恢復的數據庫大小之間存在這種差異？
- 假設恢復的數據庫沒問題，儘管大小上有差異，是否安全？
- MySQL如何計算data_length？是估計嗎？
- 我可以安全地將生產的 ibdata 文件大小減少到 3.6GiB 而無需停機嗎？
對此的任何想法將不勝感激。謝謝你。
這對我來說是世界上所有的意義。
InnoDB 創建每個 16K 的數據頁和索引頁。如果正在插入、更新、刪除、送出和回滾數據行，您將有 FRAGMENTATION !!!
有兩種情況可以產生內部碎片：
- 單行可以寫入多個數據頁，因為某些列值會使行太大而無法放入數據頁。
- 有一個包含 32K 數據的 TEXT 列。
在這兩種情況下，跨越多個數據頁的單行必須像鍊錶一樣連結起來。讀取行時，必須始終導航內部生成的數據頁列表。
PostgreSQL 實現了一個非常出色的機制，稱為TOAST（超大屬性儲存技術），將超大數據保存在表之外，以阻止這種內部碎片的趨勢。
已經使用 mysqldump 創建了一個包含 CREATE TABLE 語句的文件，然後是大量的 INSERT，當將 mysqldump 載入到新伺服器時，您會得到一個沒有未使用空間的新表以及連續的數據和索引頁。
對於我的解釋，假設您在 CUSTODIA 數據庫中有一個名為 userinfo 的 InnoDB 表
如果您想壓縮表格，您有三 (3) 個選項
選項1
OPTIMIZE TABLE CUSTODIA.userinfo;選項 2
ALTER TABLE CUSTODIA.userinfo ENGINE=InnoDB;選項 3
CREATE TABLE CUSTODIA.userinfo2 LIKE CUSTODIA.userinfo; INSERT INTO CUSTODIA.userinfo2 SELECT * FROM CUSTODIA.userinfo; DROP TABLE CUSTODIA.userinfo; ALTER TABLE CUSTODIA.userinfo2 RENAME CUSTODIA.userinfo;CAVEAT ：選項 3 不適用於有約束的表。選項 3 非常適合 MyISAM。
現在為您的問題：
問題 1. 為什麼原始和恢復的數據庫大小之間存在這種差異？
如上所述
問題 2. 假設恢復的數據庫沒問題，儘管大小有所不同，是否安全？
如果您想絕對確保兩台伺服器上的數據相同，只需在兩台數據庫伺服器上執行以下命令：
CHECKSUM TABLE CUSTODIA.userinfo;希望兩台伺服器上同一張表的校驗和值相同。如果您有數十個甚至數百個表，則可能需要編寫腳本。
問題3：MySQL如何計算data_length？是估計嗎？
您在總結 data_length 和 index_length 時使用了正確的方法。根據我對碎片的解釋，這是一個估計。
問題 4. 我可以安全地將生產的 ibdata 文件大小減少到 3.6GiB 而不會停機嗎？
好消息 ！！！你絕對可以壓縮它！事實上，怎麼想把它壓縮到那個數字的一小部分？？？關注這兩個連結，因為我在StackOverflow和ServerFault中解決了這個問題。
https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400
壞消息 ！！！抱歉，您將有 3-5 分鐘的停機時間來重建 ib_logfile0 和 ib_logfile1 以及一勞永逸地縮小 ibdata1。這是非常值得的，因為它將是一次性操作。
Q3 – 你問的是磁碟佔用嗎？還是數據大小？這些大小與使用了多少磁碟空間有關。高估了數據和索引的大小——因為
- 16KB 塊很少滿
- 大於 767B 的欄位被分流到其他區域，以 1MB 為單位分配（或類似的東西）
- 一旦表大於普通大小，就會添加 8MB 的範圍，其中一些保留為“空閒”。
- 以 PRIMARY KEY 順序插入將使數據塊合理密集地打包。否則，在任何載入過程中，數據和索引塊都會隨心所欲地進行隨機插入和塊拆分。隨機插入會導致大約 69% 的塊被填滿，這僅僅是因為塊拆分的隨機性。
- InnoDB 試圖延遲索引更新，從而減少我剛才描述的混亂。但是如果 table 比 innodb_buffer_pool_size 大得多，那麼這種優化會發生多少是有限制的。
由於上述所有因素，重新載入可能會導致更大或更小的磁碟佔用空間。
有沒有註意到 1 行表的 avg_row_length 為 16KB？