我遇到了一個非常慢的數據庫問題。phpMyAdmin 中的分析器建議我OPTIMIZE TABLE在我的表上執行。

但在這樣做之前，我（當然）想知道表中的數據是否會發生任何事情，或者這個操作是否完全無害。

使用時我應該考慮哪些優點和缺點OPTIMIZE TABLE？索引和主鍵是否仍然保持不變？數據庫中是否存在優化後會變慢的區域？

OPTIMIZE TABLE基本上做了三（3）件事

1. 縮小數據頁
2. 縮小索引頁
3. 計算新的索引統計

從概念上講，OPTIMIZE TABLE在mydb.mytable

USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp RENAME mytable;
DROP TABLE mytablezap;
ANALYZE TABLE mytable;

然而，基於

• 桌子mydb.mytable
• 數據目錄是/var/lib/mysql

讓我們看看儲存引擎細節

MyISAM

MyISAM 表mydb.mytable物理儲存在三個文件中

• /var/lib/mysql/mydb/mytable.frm（表結構）
• /var/lib/mysql/mydb/mytable.MYD（數據）
• /var/lib/mysql/mydb/mytable.MYI（索引）

執行 OPTIMIZE TABLE 的概念描述會將數據頁和索引頁複製到一個新的.MYD和.MYI. 這將消除任一文件中的碎片頁面。

InnoDB

有兩個觀點需要考慮

觀點 #1：innodb_file_per_table 已禁用

在禁用innodb_file_per_table的情況下，每個 InnoDB 表的所有數據頁和索引頁都儲存在系統表空間內（更好地稱為文件 ibdata1）。

當您OPTIMIZE TABLE在儲存在 ibdata1 中的 InnoDB 表上執行時，所有數據和索引頁面都是連續寫入的，因此該表的所有頁面都在一起。壞消息是它使 ibdata1 快速增長。

觀點 #2：innodb_file_per_table 已啟用

啟用innodb_file_per_table後，每個 InnoDB 表的所有數據頁和索引頁都儲存在 ibdata1 之外。這是 的物理儲存mydb.mytable：

• /var/lib/mysql/mydb/mytable.frm（表結構）
• /var/lib/mysql/mydb/mytable.ibd（數據和索引）

當您OPTIMIZE TABLE在儲存在 ibdata1（系統表空間）之外的 InnoDB 表上執行時，這將執行導致.ibd壓縮文件的概念步驟。

我以前寫過這個

• Oct 29, 2010: Howto: 清理 mysql InnoDB 儲存引擎？（堆棧溢出）
• Mar 25, 2012:為什麼 InnoDB 將所有數據庫儲存在一個文件中？
• Apr 11, 2012:你如何從 InnoDB 表中刪除碎片？
• Dec 21, 2012:是否有 OPTIMIZE TABLE 進度的進度指示器？
• Jan 07, 2013:數據庫空間與 ibdata1 大小不匹配

現在，對於您最初的問題…

但在這樣做之前，我（當然）想知道表中的數據是否會發生任何事情，或者這個操作是否完全無害。

我剛剛解釋了 OPTIMIZE TABLE 如何適用於兩個儲存引擎。這可能會很耗時，具體取決於數據和索引的大小。在地震或停電之外，執行 OPTIMIZE TABLE 不會損害數據並重建索引。

使用 OPTIMIZE TABLE 時我應該考慮哪些優點和缺點？

同上

索引和主鍵是否仍然保持不變？

是的

數據庫中是否存在優化後會變慢的區域？

沒門。查詢沒有碎片的表只會更快

引用自：https://dba.stackexchange.com/questions/40769