MySQL 索引和數(shù)據(jù)表該如何維護
查找和修復(fù)數(shù)據(jù)表沖突
數(shù)據(jù)表最糟糕的事情就是發(fā)生沖突。使用MyISAM存儲引擎時，通常因為崩潰導(dǎo)致沖突。然而，當存在硬件故障、MySQL內(nèi)部Bug或操作系統(tǒng)Bug時，所有的存儲引擎都可能遭受索引沖突。
沖突的索引可能導(dǎo)致查詢返回錯誤的結(jié)果，在沒有重復(fù)值時的重復(fù)索引錯誤增加，甚至可能導(dǎo)致全表掃描或崩潰。如果你遇到過偶發(fā)的事件，例如一個你認為不會發(fā)生的錯誤，這個時候運行CHECK TABLE命令去檢測數(shù)據(jù)表是否有沖突（注意有些數(shù)據(jù)庫引擎不支持這個命令，有些則支持多種選項參數(shù)去指定如何檢測表）。通常，CHECK TABLE命令會捕獲大部分的數(shù)據(jù)表和索引錯誤。
你可以通過REPAIR TABLE命令修復(fù)數(shù)據(jù)表錯誤，但是也不是全部存儲引擎都支持這個命令。這個時候你需要執(zhí)行一個“沒有操作”的ALTER語句，例如將一個數(shù)據(jù)表的引擎修改為和當前的引擎一樣，例如可以對InnoDB的數(shù)據(jù)表執(zhí)行下面的語句：
ALTER TABLE innodb_tb1 ENGINE=INNODB;
相應(yīng)地，你也可以使用一個存儲引擎指定的離線修復(fù)工具，例如myisamchk，或者導(dǎo)出數(shù)據(jù)再重新導(dǎo)入。然而，如果沖突發(fā)生在系統(tǒng)區(qū)，或者在數(shù)據(jù)表的數(shù)據(jù)行區(qū)域，而不是索引的話，你可能無法使用這些辦法。這種情況下，你可能需要從你的備份中恢復(fù)數(shù)據(jù)或從沖突的文件中恢復(fù)數(shù)據(jù)。
如果你在InnoDB中也遇到了沖突，這會是極其嚴重的錯誤，你需要使用正確的方法去分析問題。InnoDB通常不會發(fā)生沖突。它的設(shè)計對沖突處理很健壯。沖突會是硬件故障（如內(nèi)存區(qū)錯誤或磁盤錯誤），DBA的操作錯誤（如在MySQL環(huán)境外操作了數(shù)據(jù)庫文件）或InnoDB自身的Bug (這種概率很低）的表現(xiàn)。通常的一個原因類似視圖使用rsync工具創(chuàng)建備份的錯誤。這時沒有可執(zhí)行的查詢——由于這會引起InnoDB的數(shù)據(jù)沖突，而你認為這會避免。如果你通過一個有問題的查詢引起了InnoDB的數(shù)據(jù)沖突，那這并不是你的錯誤，這是InnoDB的Bug。
如果真的遇到了數(shù)據(jù)沖突，最重要的事情是搞清楚引起沖突的原因，在這之前不要簡單地修復(fù)數(shù)據(jù)，也許這個沖突會自動消失。你可以通過innodb_force_recovery參數(shù)將InnoDB修改為強制恢復(fù)模式來修復(fù)數(shù)據(jù)（可以查閱MySQL的操作手冊）。你也可以使用開源的Percona InnoDB數(shù)據(jù)恢復(fù)工具（www.percona.com/software/my…）從損壞的數(shù)據(jù)文件中提取數(shù)據(jù)。
更新索引統(tǒng)計
MySQL查詢優(yōu)化器在決定如何使用索引前，會調(diào)用兩個API獲取索引值的分布。第一個是records_in_range方法，該方法接收一個范圍參數(shù)，然后返回該范圍的結(jié)果數(shù)量。對于MyISAM引擎來說返回結(jié)果是準確的，但是對于InnoDB來說是估計值。
第二個API是info方法，該方法返回多種類型的數(shù)據(jù)，包括索引候選者（即每個索引對應(yīng)的記錄數(shù)量估計值）。
當存儲引擎給查詢優(yōu)化器提供不太準確的數(shù)據(jù)行數(shù)信息，或查詢計劃過于復(fù)雜而無法估計準確的行數(shù)時，優(yōu)化器使用索引統(tǒng)計去估計數(shù)據(jù)行數(shù)。MySQL優(yōu)化器是基于查詢代價做出決策的，最主要的代價準則就是這次查詢會查找的數(shù)據(jù)量。如果索引統(tǒng)計從來沒有生成，或者是過期了，優(yōu)化器可能會做出錯誤的決定。解決的方案是運行ANALYZE TABLE命令，該命令會重建索引統(tǒng)計。
每個存儲引擎實現(xiàn)索引統(tǒng)計的方式不同，因此你運行ANALUZE TABLE命令的頻率也會不同，運行該命令的代價也不同，典型的存儲引擎對索引統(tǒng)計處理方式如下：
- Memory引擎不存儲索引統(tǒng)計。
- MyISAM在磁盤存儲索引統(tǒng)計，并且ANALYZE TABLE在計算候選數(shù)據(jù)行的時候使用全索引掃描。整個表在這個過程中會被鎖定。
- InnoDB在MySQL 5.5版本中不在磁盤存儲索引統(tǒng)計，而是通過隨機的索引采樣實現(xiàn)并且將結(jié)果存在內(nèi)存中。
可以通過SHOW INDEX FROM命令檢查索引的候選者。例如：
這個命令給了很多索引相關(guān)的信息，可以查閱MySQL的手冊了解具體細節(jié)。這里需要特別關(guān)注的是Cardinality列。該列展示了存儲引擎估計的索引對應(yīng)了多少個不同的值。在MySQL 5.0及更新的版本中，也可以通過INFORMATION_SCHEMA.STATISTICS表中獲取這些信息，這十分方便。例如，你可以根據(jù)INFORMATION_SCHEMA查詢?nèi)フ业侥切┑秃Y選性的索引。但是注意，對于數(shù)據(jù)量龐大的服務(wù)器，這些中間表可能會導(dǎo)致服務(wù)器的負荷大量增加。
InnoDB的統(tǒng)計值得深入研究。統(tǒng)計的結(jié)果是通過索引數(shù)據(jù)頁的隨機采樣計算得到的，這是假設(shè)剩余未被采樣到的數(shù)據(jù)也是類似的分布。在舊的InnoDB版本中，這個采樣的頁數(shù)是8，但最新版本的可以通過innodb_stats_sample_pages變量調(diào)整。將這個值設(shè)置為大于8有助于生成更具代表性的索引統(tǒng)計，尤其是對于大的數(shù)據(jù)表，但所需要花的代價也會不同。
InnoDB在數(shù)據(jù)表第一次打開，運行ANALUZE TABLE和數(shù)據(jù)表存儲大小顯著改變時（1/16的變化量或20億行的插入）會計算索引統(tǒng)計。
INFORMATION_SCHEMA表的某些查詢，運行SHOW TABLE STATUS，執(zhí)行SHOW INDEX查詢或MySQL命令行客戶端啟用了自動完成設(shè)置，InnoDB也會計算索引統(tǒng)計。這實際會對大數(shù)據(jù)量，或I/O速度很慢的服務(wù)器造成嚴重的問題?？蛻舳顺绦蚧虮O(jiān)控工具導(dǎo)致發(fā)生重新采樣會導(dǎo)致很多鎖和加重服務(wù)器負擔，也會影響終端用戶的啟動時間。由于SHOW INDEX命令會更新索引統(tǒng)計，而如果你不更改的話你無法觀測到索引統(tǒng)計。你可以通過禁用innodb_stats_on_metadata（默認是關(guān)閉的）選項去避免這些問題。下面的命令可以查出InnoDB索引統(tǒng)計相關(guān)的系統(tǒng)變量。
SHOW GLOBAL VARIABLES WHERE Variable_name like 'innodb_stats%'
如果使用的是包含了替換InnoDB的Percona XtraDB存儲引擎的Percona服務(wù)器，你可以做進一步的配置。innodb_stats_auto_update選項可以讓你禁止自動采樣，可以有效凍結(jié)自動統(tǒng)計計算，除非你手動運行ANALYZE TABLE。這可以讓你擺脫不穩(wěn)定的查詢。這個特性是基于那些大型部署系統(tǒng)客戶的要求添加的。
為追求更高的查詢計劃穩(wěn)定性和更快的系統(tǒng)啟動速度，你可以使用系統(tǒng)級的數(shù)據(jù)表存儲索引統(tǒng)計。這種方式在系統(tǒng)重啟或InnoDB第一次啟動打開數(shù)據(jù)表時不需要重新計算索引統(tǒng)計。這個特性在Percona 5.1版本已經(jīng)得到支持，并且在標準的MySQL 5.6版本已經(jīng)得到支持。Percona服務(wù)器這個特性是通過innodb_use_sys_stats_table選項啟用的。在MySQL 5.6版本后，是通過innodb_stats_persistent選項控制的，默認是ON。同時，還有一個變量控制單表的，innodb_stats_auto_recalc變量默認為ON，會在數(shù)據(jù)表變化量超過10%時重新統(tǒng)計該表的索引統(tǒng)計（手冊可以參考：dev.mysql.com/doc/refman/…）。
如果你沒有配置自動更新索引統(tǒng)計，你需要定期使用ANALYZE TABLE命令來更新索引統(tǒng)計，除非你知道不更新不會導(dǎo)致糟糕的查詢計劃。
以上就是MySQL 索引和數(shù)據(jù)表該如何維護的詳細內(nèi)容，更多關(guān)于MySQL 索引和數(shù)據(jù)表維護的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
mysql?自增長約束(auto_increment)的使用
MySQL中的自增長約束(auto_increment)用于自動為表的主鍵字段生成唯一的遞增值,通過設(shè)置該屬性,可以簡化主鍵的管理,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-11-11
mysql分區(qū)后每個分區(qū)成了獨立的文件,雖然從邏輯上還是一張表其實已經(jīng)分成了多張獨立的表,下面這篇文章主要給大家介紹了關(guān)于MySQL分區(qū)之指定各分區(qū)路徑的相關(guān)資料,需要的朋友可以參考下2022-04-04
這篇文章主要介紹了mysql5.7 設(shè)置遠程訪問的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-02-02
Web程序慢，首先的一個檢測對象就是數(shù)據(jù)庫，寫過幾年程序的都知道。現(xiàn)在想追蹤MySQL中那些拖累服務(wù)器性能的SQL語句，怎么辦2012-06-06
rpm -ivh方式安裝mysql并修改數(shù)據(jù)存儲位置的實現(xiàn)
在Linux環(huán)境下進行MySQL的安裝可以使用不同的方式,但在本文中我們將關(guān)注一種特定的方式,即通過RPM包的方式進行安裝,本文主要介紹了rpm -ivh方式安裝mysql并修改數(shù)據(jù)存儲位置的實現(xiàn),感興趣的可以了解一下2023-09-09
詳解MySQL中Order By排序和filesort排序的原理及實現(xiàn)
這篇文章主要為大家詳細介紹了MySQL的Order By排序的底層原理與filesort排序，以及排序優(yōu)化手段，文中的示例代碼講解詳細，感興趣的小編可以跟隨小編一起學習一下2022-08-08
當需要停止或關(guān)閉MySQL實例時,很多人并不清楚如何做到正確、安全地關(guān)閉MySQL,會對數(shù)據(jù)的安全性和穩(wěn)定性造成很大的威脅,本文將從多個方面詳細闡述如何正確關(guān)閉MySQL,包括關(guān)閉MySQL的基本方法、關(guān)閉MySQL前的準備工作、優(yōu)雅地關(guān)閉MySQL以及遇到MySQL無法正常關(guān)閉的情況2023-09-09
這篇文章主要介紹了mysql查詢連續(xù)記錄方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-05-05
MySQL的INSERT INTO SELECT FROM語句允許用戶通過一條SQL語句實現(xiàn)從一個或多個表中查詢數(shù)據(jù)并將結(jié)果插入到另一個表中,這種方式特別適用于需要將數(shù)據(jù)從一張表遷移到另一張表,或者基于多表查詢結(jié)果創(chuàng)建新表的場景2024-10-10
MySQL兩種表存儲結(jié)構(gòu)MyISAM和InnoDB的性能比較測試
MySQL兩種表存儲結(jié)構(gòu)MyISAM和InnoDB的性能比較測試...2006-12-12