MySQL如何實現表維護

這篇文章主要介紹MySQL如何實現表維護，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

表維護

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

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

執行表維護工具主要有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;

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

| Table | Op | Msg_type | Msg_text |

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

| world_innodb.City | check | status | OK |

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

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

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;

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

| Table | Op | Msg_type | Msg_text |

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

| world_innodb.Country | analyze| status | OK |

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

在對非常量對象執行聯接操作時，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 版本兼容?？赡軙驗槟撤N數據類型的存儲格式或排序順序發生變化而出現不兼容的情況。如果出現潛在的不兼容情況，則服務器將對表運行全面檢查。如果全面檢查成功，則服務器會使用當前的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;

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

| Table | Op | Msg_type | Msg_text |

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

| world_innodb.Country | check | status | OK |

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

如果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;

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

| Table | Op | Msg_type | Msg_text |

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

| mysql.help_relation | optimize | status | OK |

| mysql.help_topic | optimize | status | OK |

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

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;

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

| Table | Op | Msg_type | Msg_text |

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

| mysql.help_relation | repair | status | OK |

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

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如何實現表維護”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！