表維護

為什麼要做表維護操作，解決什麼問題？

兩種情況下需要做表維護操作，一是由於伺服器崩潰而導致表損壞，二是對錶的查詢處理速度較慢的情況；

執行表維護工具主要有 MySQL Workbench 、 MySQL Enterprise Monitor 、 SQL (DML) 維護語句、 mysqlcheck 、 myisamchk ；下面就逐一介紹這些工具；

1.1. 表維護 SQL 語句

用於執行表維護的 SQL 語句有： ANALYZE TABLE （更新索引統計資訊）、 CHECK TABLE （徹底檢查完整性）、 CHECKSUM TABLE （徹底檢查完整性）、 REPAIR TABLE （修復）、 OPTIMIZE TABLE （最佳化），每個語句均包含一個或多個表名稱和可選的關鍵字。維護語句和輸出的示例：

mysql> CHECK TABLE world_innodb.City;

+-------------------+-------+----------+----------+

+-------------------+-------+----------+----------+

+-------------------+-------+----------+----------+

執行所請求的操作之後，伺服器將返回有關對客戶機執行操作的結果的資訊。該資訊以四列結果集形式顯示：

l Table ：指示對其執行操作的表

l Op ：指出操作（檢查、修復、分析或最佳化）

l Msg_type ：指示成功或失敗

l Msg_text ：提供其他資訊

1.1.1. ANALYZE TABLE 語句

ANALYZE TABLE 語句分析並儲存表的鍵分佈統計資訊，用於更好地進行查詢執行選擇，處理 InnoDB 、 NDB 和 MyISAM 表，支援分割槽表；

ANALYZE TABLE 選項： NO_WRITE_TO_BINLOG 或 LOCAL ：禁用二進位制日誌

ANALYZE TABLE 正常結果的示例：

mysql> ANALYZE LOCAL TABLE Country;

+----------------------+--------+----------+----------+

+----------------------+--------+----------+----------+

+----------------------+--------+----------+----------+

在對非常量物件執行聯接操作時， MySQL 使用所儲存的鍵分佈統計資訊來確定最佳化程式聯接表的順序。此外，鍵分佈確定了 MySQL 用於查詢中的特定表的索引。

您可以執行 ANALYZE TABLE 語句來分析並儲存統計資訊，或者配置 InnoDB ，以便在大量資料發生更改之後或者在查詢表或索引後設資料時自動收集統計資訊。

ANALYZE TABLE 特徵：

l 在分析過程中，對於 InnoDB 和 MyISAM ， MySQL 使用讀取鎖來鎖定表。

l 此語句等效於使用 mysqlcheck --analyze 。

l 需要對錶有 SELECT 和 INSERT 許可權。

l 支援分割槽表。還可以使用 ALTER TABLE...ANALYZE PARTITION 檢查一個或多個分割槽。

如果自從執行上一個 ANALYZE TABLE 語句後表未發生任何更改，則 MySQL 不會分析該表。預設情況下， MySQL 會將 ANALYZE TABLE 語句寫入二進位制日誌並將這些語句複製到複製從屬角色中。禁止使用可選的 NO_WRITE_TO_BINLOG 關鍵字或其別名 LOCAL 執行日誌記錄。

可以使用以下選項控制 MySQL 收集和儲存鍵分佈統計資訊的方式：

l innodb_stats_persistent ：此選項為 ON 時， MySQL 將對新建立的表啟用 STATS_PERSISTENT 設定。使用 CREATE TABLE 或 ALTER TABLE 語句時，還可以對錶設定 STATS_PERSISTENT 。預設情況下， MySQL 不會將鍵分佈統計資訊持久保留在磁碟上，因此有時必須生成這些資訊（如伺服器重新啟動後）。對於啟用了 STATS_PERSISTENT 的表， MySQL 會將其鍵分佈統計資訊儲存在磁碟上，從而不需要頻繁地為這些表生成統計資訊。隨著時間推移，透過此操作最佳化程式可以建立更一致的查詢計劃。

l innodb_stats_persistent_sample_pages ： MySQL 透過讀取 STATS_PERSISTENT 表的索引頁樣例（而並非整個表）重新計算統計資訊。預設情況下，將讀取 20 頁樣例。增大此數字可提高所生成的統計資訊和查詢計劃的質量。降低此數字可減少用於生成統計資訊的 I/O 成本。

l innodb_stats_transient_sample_pages ：此選項用於控制對沒有 STATS_PERSISTENT 設定的表的抽樣索引頁數量。

以下選項用於控制 MySQL 自動收集統計資訊的方式。

l innodb_stats_auto_recalc ：啟用此選項時，如果 STATS_PERSISTENT 表中 10% 的行自前一次重新計算後有所變化，則 MySQL 將自動為該表生成統計資訊。

l innodb_stats_on_metadata ：啟用此選項可在執行後設資料語句（如 SHOW TABLE STATUS ）或查詢 INFORMATION_SCHEMA.TABLES 時更新統計資訊。預設情況下，此選項處於禁用狀態。

1.1.2. CHECK TABLE 語句

ANALYZE TABLE 語句檢查表結構的完整性，並檢查內容中是否包含錯誤，驗證檢視定義，支援分割槽表，處理 InnoDB 、 CSV 、 MyISAM 和 ARCHIVE 表

CHECK TABLE 選項：

Ø FOR UPGRADE ：檢查表是否適用於當前伺服器。

Ø QUICK ：不掃描行來檢查錯誤連結。

如果 CHECK TABLE 發現 InnoDB 表出現問題，則伺服器將關閉，以防止錯誤擴散，同時 MySQL 會將錯誤寫入錯誤日誌；

CHECK TABLE 特徵：

Ø 對於 MyISAM 表，還將更新鍵統計資訊。

Ø 還可以檢查檢視是否出現問題，例如檢視定義中引用的表不再存在。

Ø 支援分割槽表。還可以使用 ALTER TABLE...CHECK PARTITION 檢查一個或多個分割槽。

使用 FOR UPGRADE 時，伺服器將檢查每個表以確定表結構是否與當前的 MySQL 版本相容。可能會因為某種資料型別的儲存格式或排序順序發生變化而出現不相容的情況。如果出現潛在的不相容情況，則伺服器將對錶執行全面檢查。如果全面檢查成功，則伺服器會使用當前的 MySQL 版本號標記表的 .frm 檔案。對 .frm 檔案進行標記可以確保以後對於與伺服器版本相同的表進行檢查的速度會加快。

建議對 InnoDB 、 MyISAM 和 ARCHIVE 儲存引擎使用 FOR UPGRADE 。對 InnoDB 和 MyISAM 表使用 QUICK 。 MyISAM 支援其他選項。請訪問

http://dev.mysql.com/doc/refman/5.6/en/check-table.htm

CHECK TABLE 語句

CHECK TABLE 正常結果的示例：

mysql> CHECK TABLE Country;

+----------------------+-------+----------+----------+

+----------------------+-------+----------+----------+

+----------------------+-------+----------+----------+

如果 CHECK TABLE 的輸出表明某個表出現問題，請修復該表。例如，您可以先使用 CHECK TABLE 語句檢測硬體問題（如記憶體故障或磁碟扇區損壞），然後再修復表。

Msg_text 輸出列通常為 OK 。如果輸出不是 OK 或 Table is already up to date ，請對該表執行修復。如果該表被標記為 corrupted 或 not closed properly ，但 CHECK TABLE 在表中未發現任何問題，則會將該表標記為 OK 。

1.1.3. CHECKSUM TABLE 語句

CHECKSUM TABLE 語句報告表 checksum ，用於驗證表的內容在備份、回滾或其他操作前後是否相同；

CHECKSUM TABLE 語句逐行讀取整個表以計算校驗和

Ø 預設的 EXTENDED 選項提供了此行為。

Ø QUICK 選項對 MyISAM 表可用。

Ø 當包含 MyISAM CHECKSUM=1 設定時，此為預設選項。

CHECKSUM TABLE 語句的示例：

mysql> CHECKSUM TABLE City;

+-------------------+-----------+

| Table | Checksum |

+-------------------+-----------+

| world_innodb.City | 531416258 |

+-------------------+-----------+

CHECKSUM TABLE 特徵：

Ø CHECKSUM TABLE 需要對錶有 SELECT 許可權。

Ø 對於不存在的表， CHECKSUM TABLE 將返回 NULL 並生成警告。

Ø 如果使用了 EXTENDED 選項，則將逐行讀取整個表，並計算 checksum 。

Ø 如果使用了 QUICK 選項：將報告實時表 checksum （如果可用）；否則將報告 NULL 。此操作非常快。透過在建立表時指定 CHECKSUM=1 表選項，對 MyISAM 表啟用了實時 checksum 。

Ø 如果既未指定 QUICK ，也未指定 EXTENDED ，則 MySQL 將假定為 EXTENDED （ CHECKSUM=1 的 MyISAM 表除外）。

checksum 值取決於表中的行格式。如果行格式發生了變化，則 checksum 也會更改。例如， VARCHAR 的儲存格式在 MySQL 4.1 之後的版本中有所變化，因此，在將 4.1 表升級到更高版本後，如果表中包含 VARCHAR 欄位，則 checksum 值將發生變化。

注：如果兩個表的 checksums 不同，則很可能這兩個表存在某方面的差異。不過，因為 CHECKSUM TABLE 使用的雜湊函式無法保證不衝突，所以存在兩個不同的表生成相同 checksum 的微弱可能性。

1.1.4. OPTIMIZE TABLE 語句

OPTIMIZE TABLE 語句透過對錶進行碎片整理來清理表，即透過重新構建表並釋放未使用的空間對錶進行碎片整理； OPTIMIZE TABLE 語句在最佳化過程中鎖定表，並更新索引統計資訊，最適用於完全填充的永久表，支援處理 InnoDB 、 MyISAM 和 ARCHIVE 表，支援分割槽表

OPTIMIZE TABLE 選項： NO_WRITE_TO_BINLOG 或 LOCAL ：禁用二進位制日誌。

OPTIMIZE TABLE 特徵：

Ø 碎片整理涉及回收透過刪除和更新產生的未使用空間，以及合併被分隔開的記錄和以非連續方式儲存的記錄。

Ø 需要對錶有 SELECT 和 INSERT 許可權

Ø 支援分割槽表。還可以使用 ALTER TABLE...OPTIMIZE PARTITION 檢查一個或多個分割槽。

例如，修改大量行之後，可以使用 OPTIMIZE TABLE 語句在 InnoDB 中重構一個 FULLTEXT 索引。

對於 InnoDB 表， OPTIMIZE TABLE 將對映到 ALTER TABLE ，後者將重構表以更新索引統計資訊並釋放群集索引中未使用的空間。 InnoDB 不會像其他儲存引擎一樣受碎片影響，因此不需要經常使用 OPTIMIZE TABLE 。

對使用 ARCHIVE 儲存引擎的表使用 OPTIMIZE TABLE 可以壓縮該表。由 SHOW TABLE STATUS 所報告的 ARCHIVE 表中的行數始終比較準確。最佳化操作過程中可能會出現一個 .ARN 檔案。

OPTIMIZE TABLE 語句

以下 OPTIMIZE TABLE 語句將最佳化 mysql 資料庫中兩個完全填充的表：

mysql> OPTIMIZE TABLE mysql.help_relation, mysql.help_topic;

+---------------------+----------+----------+----------+

+---------------------+----------+----------+----------+

+---------------------+----------+----------+----------+

2 rows in set (0.00 sec)

對於 MyISAM 表，在刪除表中大量內容或者對包含可變長度行的表（包含 VARCHAR 、 VARBINARY 、 BLOB 或 TEXT 列的表）進行多項更改之後，請使用 OPTIMIZE TABLE 語句。已刪除的行將保留在連結的列表中，而後續的 INSERT 操作將重用之前行的位置。

OPTIMIZE TABLE 對完全填充的表使用時效果最佳並且不會發生很大更改。如果資料更改較多並經常需要最佳化，則最佳化的優勢將會大大降低。

1.1.5. REPAIR TABLE 語句

REPAIR TABLE 語句修復可能已損壞的 MyISAM 或 ARCHIVE 表， 不支援 InnoDB ，但是支援分割槽表；

REPAIR TABLE 選項：

Ø QUICK ：僅修復索引樹，嘗試僅修復索引檔案，而不修複資料檔案。此型別的修復與 myisamchk --recover --quick 所執行的修復相似。

Ø EXTENDED ：逐行建立索引（而不是一次性建立有序索引）， MySQL 將逐行建立索引，而不是一次性建立有序索引。此型別的修復與 myisamchk --safe-recover 所執行的修復相似。

Ø USE_FRM ：使用 .FRM 檔案重新建立 .MYI 檔案，但是不能用於分割槽表。

Ø NO_WRITE_TO_BINLOG 或 LOCAL ：禁用二進位制日誌。

REPAIR TABLE 特徵：

Ø QUICK 選項：嘗試僅修復索引檔案，而不修複資料檔案。此型別的修復與 myisamchk --recover --quick 所執行的修復相似。

Ø EXTENDED 選項： MySQL 將逐行建立索引，而不是一次性建立有序索引。此型別的修復與 myisamchk --safe-recover 所執行的修復相似。

Ø USE_FRM 選項不能用於分割槽表。

Ø 需要對錶有 SELECT 和 INSERT 許可權

Ø 支援分割槽表。還可以使用 ALTER TABLE...REPAIR PARTITION 檢查一個或多個分割槽。

在執行表修復操作之前，最好對錶進行備份； 在某些情況下，該操作可能導致資料丟失。 可能的原因包括（但不僅限於）檔案系統錯誤。

如果伺服器在 REPAIR TABLE 操作過程中崩潰，則為避免進一步的損壞，重啟之後應立即執行另一 REPAIR TABLE ，然後再執行其他任何操作。

如果您經常需要使用 REPAIR TABLE 從損壞的表進行恢復，請嘗試找出根本原因，以防止相應損壞並避免使用 REPAIR TABLE 。

REPAIR TABLE 語句

REPAIR TABLE 語句的示例：

mysql> REPAIR TABLE mysql.help_relation;

+---------------------+--------+----------+----------+

+---------------------+--------+----------+----------+

+---------------------+--------+----------+----------+

1 row in set (0.00 sec)

1.2. mysqlcheck 客戶機程式

mysqlcheck 是用於檢查、修復、分析和最佳化表的命令列客戶機；它比發出 SQL 語句更加方便，可以處理 InnoDB 、 MyISAM 和 ARCHIVE 表，並且支援三種檢查級別：特定表、特定資料庫、所有資料庫

部分 mysqlcheck 維護選項：

Ø --analyze ：執行 ANALYZE TABLE 。

Ø --check ：執行 CHECK TABLE （預設）。

Ø --optimize ：執行 OPTIMIZE TABLE 。

Ø --repair ：執行 REPAIR TABLE 。

在某些情況下， mysqlcheck 比直接發出 SQL 語句更加方便。例如，如果提供資料庫名稱作為其引數，則 mysqlcheck 將確定該資料庫所包含的表，併發出語句處理所有這些表。您不需要提供明確的表名稱作為引數。此外，由於 mysqlcheck 是命令列程式，因此可以在執行計劃維護的作業系統作業中輕鬆使用該程式。

mysqlcheck 客戶機程式， Oracle 建議首先在不使用任何選項的情況下執行 mysqlcheck ，如果需要修復再重新執行。

部分 mysqlcheck 修改選項：

Ø --repair --quick ：嘗試快速修復。

Ø --repair ：正常修復（如果快速修復失敗）。

Ø --repair --force ：強制修復。

mysqlcheck 示例：

shell> mysqlcheck --login-path=admin world_innodb

shell> mysqlcheck -uroot -p mysql user --repair

shell> mysqlcheck -uroot -p --all-databases # 將檢查所有資料庫中的所有表

shell> mysqlcheck --login-path=admin --analyze --all-databases

預設情況下， mysqlcheck 將其第一個非選項引數解釋為資料庫名稱，並檢查該資料庫中的所有表。如果資料庫名稱後面有其他任何引數，則會將這些引數視為表名稱，從而只檢查這些表。

1.3. myisamchk 實用程式

myisamchk 是用於檢查 MyISAM 表的非客戶機實用程式，與 mysqlcheck 類似，其差異是 myisamchk 可以啟用或禁用索引，直接（而不是透過伺服器）訪問表檔案，這可以避免並發表訪問。

部分 myisamchk 選項：

Ø --recover ：修復表。

Ø --safe-recover ：修復 --recover 無法修復的表。

myisamchk 示例：

shell> myisamchk /var/lib/mysql/mysql/help_topic

shell> myisamchk help_category.MYI

shell> myisamchk --recover help_keyword

從理論上來看， myisamchk 與 mysqlcheck 具有相似的用途。但是， myisamchk 不與 MySQL 伺服器通訊，而是直接訪問表檔案。

如何在使用 myisamchk 執行表維護的同時避免並發表訪問？

A. 確保伺服器不會訪問正在進行處理的表。一種實現方法是鎖定表或停止伺服器。

B. 在命令提示符中，將位置更改為表所在的資料庫目錄。這是伺服器資料目錄的子目錄，該目錄的名稱與要檢查的表所在的資料庫名稱相同。（更改位置是為了更加便於引用表檔案。可以跳過此步驟，但 myisamchk 必須包含表所在的目錄。）

C. 呼叫 myisamchk ，使用選項指示要執行的操作，後跟引數以指定 myisamchk 應對其執行操作的表。這些引數可以是表名稱，也可以是表的索引檔案的檔名。索引檔名與表名稱相同，包含 .MYI 字尾。因此，可以透過 table_name 或 table_name.MYI 引用表。

D. 重新啟動伺服器。

注：請首先嚐試 --recover ，因為 --safe-recover 比較慢。

mysqlcheck 和 myisamchk 的用於控制所執行的維護型別的選項：

mysqlcheck 和 myisamchk 均使用多個選項來控制所執行的表維護操作的型別。上表彙總了一些最常用的選項，其中大多數選項同時適用於兩個程式。如果不是同時適用於兩個程式，會記錄在相關的選項說明中。

Ø --analyze ：分析表中鍵值的分佈。透過加快基於索引的查詢，這可以提高查詢的效能。

Ø --auto-repair ：如果檢查操作發現了問題，則自動修復出現問題的表。

Ø --check 或 -c ：檢查表中是否存在問題。如果未指定其他任何操作，則為預設操作。

Ø --check-only-changed 或 -C ：跳過表檢查（自上一次檢查後已更改的表或未正常關閉的表除外）。如果伺服器在表開啟時崩潰，則會出現後一種情況。

Ø --fast 或 -F ：跳過表檢查（未正常關閉的表除外）。

Ø --extended 、 --extend-check 或 -e ：執行擴充套件表檢查。對於 mysqlcheck ，將此選項與修復選項結合使用時，將執行比單獨使用修復選項時更徹底的修復。即， --repair --extended 執行的修復操作比 --repair 執行的操作更徹底。

Ø --medium-check 或 -m ：執行中等表檢查。

Ø --quick 或 -q ：對於 mysqlcheck ，不包含修復選項的 --quick 會導致只檢查索引檔案，而不檢查資料檔案。對於這兩個程式，將 --quick 與修復選項結合使用都會導致程式只修復索引檔案，而不修複資料檔案。

Ø --repair 、 --recover 或 -r ：執行表修復操作。

1.4. InnoDB 表維護

出現故障之後， InnoDB 將自動恢復。使用 CHECK TABLE 或客戶機程式可找出不一致、不相容和其他問題。也可透過使用 mysqldump 對錶進行轉儲來恢復該表：

shell> mysqldump <db_name> <table_name> > <dump_file>

然後，刪除該表並從轉儲檔案重新建立。

shell> mysql <db_name> < <dump_file>

要在崩潰後修復表，請使用 --innodb_force_recovery 選項重新啟動伺服器或者從備份中恢復表。使用 ALTER TABLE 進行最佳化時，將重構表並釋放群集索引中未使用的空間。

如果表檢查表明存在問題，請透過使用 mysqldump 轉儲該表、刪除該表並從轉儲檔案重新建立該表來將其恢復到一致狀態。

如果 MySQL 伺服器或其執行主機崩潰，則某些 InnoDB 表可能處於不一致狀態。在 InnoDB 的啟動序列中，會執行自動恢復。伺服器很少因為自動恢復故障而無法啟動。如果出現此情況，請使用以下過程：

A. 重新啟動伺服器，將 --innodb_force_recovery 選項的值設定為 1 到 6 之間的值。這些值表示增加警告級別以避免崩潰，以及針對已恢復的表中可能存在的不一致狀況增加容錯級別。最好從值 4 開始，該值可以阻止插入緩衝區合併操作。

B. 當在 --innodb_force_recovery 設定為非零值的情況下啟動伺服器時， InnoDB 將阻止 INSERT 、 UPDATE 或 DELETE 操作。因此，您應轉儲 InnoDB 表，然後在該選項生效時將這些表刪除。再在不使用 --innodb_force_recovery 選項的情況下重新啟動伺服器。伺服器啟動之後，將從轉儲檔案恢復 InnoDB 表。

C. 如果前述步驟失敗，則從前一個備份恢復表。

訪問 http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html 瞭解有關對損壞的資料庫啟動 InnoDB 的更多資訊。

1.5. MyISAM 表維護

MyISAM 表維護對於動態格式表和靜態格式表，預設的 CHECK TABLE 檢查型別均為 MEDIUM 。如果將靜態格式表型別設定為 CHANGED 或 FAST ，則預設選項為 QUICK 。對於 CHANGED 和 FAST ，將跳過行掃描，因為這些行很少損壞。如果表被標記為“已損壞”或“未正常關閉”，則 CHECK TABLE 將更改表。如果未在表中發現任何問題，則會將表的狀態標記為“最新”。如果表已損壞，則問題最有可能存在於索引而不是資料中。

shell> myisamchk --medium-check <table_name>

設定伺服器以執行檢查並自動修復表。使用 --myisam-recover 選項啟用自動修復。伺服器將在啟動之後第一次訪問每個 MyISAM 表時進行檢查，以確保這些表前一次正確關閉。

--myisam-recover 選項值可以包含以逗號分隔的值列表，由以下一個或多個值組成：

Ø DEFAULT ：預設檢查。

Ø BACKUP ：指示伺服器對必須進行更改的所有表進行備份。

Ø FORCE ：執行表恢復，即使可能導致多行資料丟失也是如此。

Ø QUICK ：執行快速恢復。恢復將跳過一些不包含因刪除或更新而產生的行間隔（也稱為“洞”）的表。

強制從 config 檔案恢復 MyISAM 表情況。例如，要指示伺服器對發現問題的 MyISAM 表執行強制恢復，但同時要備份其更改的所有表，請向選項檔案中新增以下內容：

[mysqld]

myisam-recover=FORCE,BACKUP

1.6. MEMORY 表維護

使用 DELETE...WHERE 語句刪除多個行時， MEMORY 表不會釋放記憶體。要釋放記憶體，必須執行空值 ALTER TABLE 操作。

1.7. ARCHIVE 表維護

ARCHIVE 表在插入錶行時將對其進行壓縮，檢索時，將根據需要對行進行解壓縮。一些 SELECT 語句可能會減弱壓縮功能。使用 OPTIMIZE TABLE 或 REPAIR TABLE 可以實現更好的壓縮，但只在未對錶進行訪問（讀或寫）時， OPTIMIZE TABLE 有效。

MySQL入門--表維護

表維護

相關文章