如何在MySQL中維護索引和數據表

如何在MySQL中維護索引和數據表？很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

查找和修復數據表沖突

數據表最糟糕的事情就是發生沖突。使用MyISAM存儲引擎時，通常因為崩潰導致沖突。然而，當存在硬件故障、MySQL內部Bug或操作系統Bug時，所有的存儲引擎都可能遭受索引沖突。

沖突的索引可能導致查詢返回錯誤的結果，在沒有重復值時的重復索引錯誤增加，甚至可能導致全表掃描或崩潰。如果你遇到過偶發的事件，例如一個你認為不會發生的錯誤，這個時候運行CHECK TABLE命令去檢測數據表是否有沖突（注意有些數據庫引擎不支持這個命令，有些則支持多種選項參數去指定如何檢測表）。通常，CHECK TABLE命令會捕獲大部分的數據表和索引錯誤。

你可以通過REPAIR TABLE命令修復數據表錯誤，但是也不是全部存儲引擎都支持這個命令。這個時候你需要執行一個“沒有操作”的ALTER語句，例如將一個數據表的引擎修改為和當前的引擎一樣，例如可以對InnoDB的數據表執行下面的語句：

ALTER TABLE innodb_tb1 ENGINE=INNODB;

相應地，你也可以使用一個存儲引擎指定的離線修復工具，例如myisamchk，或者導出數據再重新導入。然而，如果沖突發生在系統區，或者在數據表的數據行區域，而不是索引的話，你可能無法使用這些辦法。這種情況下，你可能需要從你的備份中恢復數據或從沖突的文件中恢復數據。

如果你在InnoDB中也遇到了沖突，這會是極其嚴重的錯誤，你需要使用正確的方法去分析問題。InnoDB通常不會發生沖突。它的設計對沖突處理很健壯。沖突會是硬件故障（如內存區錯誤或磁盤錯誤），DBA的操作錯誤（如在MySQL環境外操作了數據庫文件）或InnoDB自身的Bug (這種概率很低）的表現。通常的一個原因類似視圖使用rsync工具創建備份的錯誤。這時沒有可執行的查詢——由于這會引起InnoDB的數據沖突，而你認為這會避免。如果你通過一個有問題的查詢引起了InnoDB的數據沖突，那這并不是你的錯誤，這是InnoDB的Bug。

如果真的遇到了數據沖突，最重要的事情是搞清楚引起沖突的原因，在這之前不要簡單地修復數據，也許這個沖突會自動消失。你可以通過innodb_force_recovery參數將InnoDB修改為強制恢復模式來修復數據（可以查閱MySQL的操作手冊）。你也可以使用開源的Percona InnoDB數據恢復工具（www.percona.com/software/my…）從損壞的數據文件中提取數據。

更新索引統計

MySQL查詢優化器在決定如何使用索引前，會調用兩個API獲取索引值的分布。第一個是records_in_range方法，該方法接收一個范圍參數，然后返回該范圍的結果數量。對于MyISAM引擎來說返回結果是準確的，但是對于InnoDB來說是估計值。

第二個API是info方法，該方法返回多種類型的數據，包括索引候選者（即每個索引對應的記錄數量估計值）。

當存儲引擎給查詢優化器提供不太準確的數據行數信息，或查詢計劃過于復雜而無法估計準確的行數時，優化器使用索引統計去估計數據行數。MySQL優化器是基于查詢代價做出決策的，最主要的代價準則就是這次查詢會查找的數據量。如果索引統計從來沒有生成，或者是過期了，優化器可能會做出錯誤的決定。解決的方案是運行ANALYZE TABLE命令，該命令會重建索引統計。

每個存儲引擎實現索引統計的方式不同，因此你運行ANALUZE TABLE命令的頻率也會不同，運行該命令的代價也不同，典型的存儲引擎對索引統計處理方式如下：

• Memory引擎不存儲索引統計。

• MyISAM在磁盤存儲索引統計，并且ANALYZE TABLE在計算候選數據行的時候使用全索引掃描。整個表在這個過程中會被鎖定。

• InnoDB在MySQL 5.5版本中不在磁盤存儲索引統計，而是通過隨機的索引采樣實現并且將結果存在內存中。
  

可以通過SHOW INDEX FROM命令檢查索引的候選者。例如：

這個命令給了很多索引相關的信息，可以查閱MySQL的手冊了解具體細節。這里需要特別關注的是Cardinality列。該列展示了存儲引擎估計的索引對應了多少個不同的值。在MySQL 5.0及更新的版本中，也可以通過INFORMATION_SCHEMA.STATISTICS表中獲取這些信息，這十分方便。例如，你可以根據INFORMATION_SCHEMA查詢去找到那些低篩選性的索引。但是注意，對于數據量龐大的服務器，這些中間表可能會導致服務器的負荷大量增加。

InnoDB的統計值得深入研究。統計的結果是通過索引數據頁的隨機采樣計算得到的，這是假設剩余未被采樣到的數據也是類似的分布。在舊的InnoDB版本中，這個采樣的頁數是8，但最新版本的可以通過innodb_stats_sample_pages變量調整。將這個值設置為大于8有助于生成更具代表性的索引統計，尤其是對于大的數據表，但所需要花的代價也會不同。

InnoDB在數據表第一次打開，運行ANALUZE TABLE和數據表存儲大小顯著改變時（1/16的變化量或20億行的插入）會計算索引統計。

INFORMATION_SCHEMA表的某些查詢，運行SHOW TABLE STATUS，執行SHOW INDEX查詢或MySQL命令行客戶端啟用了自動完成設置，InnoDB也會計算索引統計。這實際會對大數據量，或I/O速度很慢的服務器造成嚴重的問題。客戶端程序或監控工具導致發生重新采樣會導致很多鎖和加重服務器負擔，也會影響終端用戶的啟動時間。由于SHOW INDEX命令會更新索引統計，而如果你不更改的話你無法觀測到索引統計。你可以通過禁用innodb_stats_on_metadata（默認是關閉的）選項去避免這些問題。下面的命令可以查出InnoDB索引統計相關的系統變量。

SHOW GLOBAL VARIABLES WHERE Variable_name like 'innodb_stats%'

如果使用的是包含了替換InnoDB的Percona XtraDB存儲引擎的Percona服務器，你可以做進一步的配置。innodb_stats_auto_update選項可以讓你禁止自動采樣，可以有效凍結自動統計計算，除非你手動運行ANALYZE TABLE。這可以讓你擺脫不穩定的查詢。這個特性是基于那些大型部署系統客戶的要求添加的。

為追求更高的查詢計劃穩定性和更快的系統啟動速度，你可以使用系統級的數據表存儲索引統計。這種方式在系統重啟或InnoDB第一次啟動打開數據表時不需要重新計算索引統計。這個特性在Percona 5.1版本已經得到支持，并且在標準的MySQL 5.6版本已經得到支持。Percona服務器這個特性是通過innodb_use_sys_stats_table選項啟用的。在MySQL 5.6版本后，是通過innodb_stats_persistent選項控制的，默認是ON。同時，還有一個變量控制單表的，innodb_stats_auto_recalc變量默認為ON，會在數據表變化量超過10%時重新統計該表的索引統計（手冊可以參考：dev.mysql.com/doc/refman/…）。

如果你沒有配置自動更新索引統計，你需要定期使用ANALYZE TABLE命令來更新索引統計，除非你知道不更新不會導致糟糕的查詢計劃。

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。