查找和修復(fù)數(shù)據(jù)表沖突

數(shù)據(jù)表最糟糕的事情就是發(fā)生沖突。使用myisam存儲(chǔ)引擎時(shí)，通常因?yàn)楸罎?dǎo)致沖突。然而，當(dāng)存在硬件故障、mysql內(nèi)部bug或操作系統(tǒng)bug時(shí)，所有的存儲(chǔ)引擎都可能遭受索引沖突。

沖突的索引可能導(dǎo)致查詢返回錯(cuò)誤的結(jié)果，在沒有重復(fù)值時(shí)的重復(fù)索引錯(cuò)誤增加，甚至可能導(dǎo)致全表掃描或崩潰。如果你遇到過偶發(fā)的事件，例如一個(gè)你認(rèn)為不會(huì)發(fā)生的錯(cuò)誤，這個(gè)時(shí)候運(yùn)行check table命令去檢測(cè)數(shù)據(jù)表是否有沖突（注意有些數(shù)據(jù)庫(kù)引擎不支持這個(gè)命令，有些則支持多種選項(xiàng)參數(shù)去指定如何檢測(cè)表）。通常，check table命令會(huì)捕獲大部分的數(shù)據(jù)表和索引錯(cuò)誤。

你可以通過repair table命令修復(fù)數(shù)據(jù)表錯(cuò)誤，但是也不是全部存儲(chǔ)引擎都支持這個(gè)命令。這個(gè)時(shí)候你需要執(zhí)行一個(gè)“沒有操作”的alter語(yǔ)句，例如將一個(gè)數(shù)據(jù)表的引擎修改為和當(dāng)前的引擎一樣，例如可以對(duì)innodb的數(shù)據(jù)表執(zhí)行下面的語(yǔ)句：

相應(yīng)地，你也可以使用一個(gè)存儲(chǔ)引擎指定的離線修復(fù)工具，例如myisamchk，或者導(dǎo)出數(shù)據(jù)再重新導(dǎo)入。然而，如果沖突發(fā)生在系統(tǒng)區(qū)，或者在數(shù)據(jù)表的數(shù)據(jù)行區(qū)域，而不是索引的話，你可能無法使用這些辦法。這種情況下，你可能需要從你的備份中恢復(fù)數(shù)據(jù)或從沖突的文件中恢復(fù)數(shù)據(jù)。

如果你在innodb中也遇到了沖突，這會(huì)是極其嚴(yán)重的錯(cuò)誤，你需要使用正確的方法去分析問題。innodb通常不會(huì)發(fā)生沖突。它的設(shè)計(jì)對(duì)沖突處理很健壯。沖突會(huì)是硬件故障（如內(nèi)存區(qū)錯(cuò)誤或磁盤錯(cuò)誤），dba的操作錯(cuò)誤（如在mysql環(huán)境外操作了數(shù)據(jù)庫(kù)文件）或innodb自身的bug (這種概率很低）的表現(xiàn)。通常的一個(gè)原因類似視圖使用rsync工具創(chuàng)建備份的錯(cuò)誤。這時(shí)沒有可執(zhí)行的查詢——由于這會(huì)引起innodb的數(shù)據(jù)沖突，而你認(rèn)為這會(huì)避免。如果你通過一個(gè)有問題的查詢引起了innodb的數(shù)據(jù)沖突，那這并不是你的錯(cuò)誤，這是innodb的bug。

如果真的遇到了數(shù)據(jù)沖突，最重要的事情是搞清楚引起沖突的原因，在這之前不要簡(jiǎn)單地修復(fù)數(shù)據(jù)，也許這個(gè)沖突會(huì)自動(dòng)消失。你可以通過innodb_force_recovery參數(shù)將innodb修改為強(qiáng)制恢復(fù)模式來修復(fù)數(shù)據(jù)（可以查閱mysql的操作手冊(cè)）。你也可以使用開源的percona innodb數(shù)據(jù)恢復(fù)工具（www.percona.com/software/my…）從損壞的數(shù)據(jù)文件中提取數(shù)據(jù)。

更新索引統(tǒng)計(jì)

mysql查詢優(yōu)化器在決定如何使用索引前，會(huì)調(diào)用兩個(gè)api獲取索引值的分布。第一個(gè)是records_in_range方法，該方法接收一個(gè)范圍參數(shù)，然后返回該范圍的結(jié)果數(shù)量。對(duì)于myisam引擎來說返回結(jié)果是準(zhǔn)確的，但是對(duì)于innodb來說是估計(jì)值。

第二個(gè)api是info方法，該方法返回多種類型的數(shù)據(jù)，包括索引候選者（即每個(gè)索引對(duì)應(yīng)的記錄數(shù)量估計(jì)值）。

當(dāng)存儲(chǔ)引擎給查詢優(yōu)化器提供不太準(zhǔn)確的數(shù)據(jù)行數(shù)信息，或查詢計(jì)劃過于復(fù)雜而無法估計(jì)準(zhǔn)確的行數(shù)時(shí)，優(yōu)化器使用索引統(tǒng)計(jì)去估計(jì)數(shù)據(jù)行數(shù)。mysql優(yōu)化器是基于查詢代價(jià)做出決策的，最主要的代價(jià)準(zhǔn)則就是這次查詢會(huì)查找的數(shù)據(jù)量。如果索引統(tǒng)計(jì)從來沒有生成，或者是過期了，優(yōu)化器可能會(huì)做出錯(cuò)誤的決定。解決的方案是運(yùn)行analyze table命令，該命令會(huì)重建索引統(tǒng)計(jì)。

每個(gè)存儲(chǔ)引擎實(shí)現(xiàn)索引統(tǒng)計(jì)的方式不同，因此你運(yùn)行analuze table命令的頻率也會(huì)不同，運(yùn)行該命令的代價(jià)也不同，典型的存儲(chǔ)引擎對(duì)索引統(tǒng)計(jì)處理方式如下：

• memory引擎不存儲(chǔ)索引統(tǒng)計(jì)。
• myisam在磁盤存儲(chǔ)索引統(tǒng)計(jì)，并且analyze table在計(jì)算候選數(shù)據(jù)行的時(shí)候使用全索引掃描。整個(gè)表在這個(gè)過程中會(huì)被鎖定。
• innodb在mysql 5.5版本中不在磁盤存儲(chǔ)索引統(tǒng)計(jì)，而是通過隨機(jī)的索引采樣實(shí)現(xiàn)并且將結(jié)果存在內(nèi)存中。

可以通過show index from命令檢查索引的候選者。例如：

這個(gè)命令給了很多索引相關(guān)的信息，可以查閱mysql的手冊(cè)了解具體細(xì)節(jié)。這里需要特別關(guān)注的是cardinality列。該列展示了存儲(chǔ)引擎估計(jì)的索引對(duì)應(yīng)了多少個(gè)不同的值。在mysql 5.0及更新的版本中，也可以通過information_schema.statistics表中獲取這些信息，這十分方便。例如，你可以根據(jù)information_schema查詢?nèi)フ业侥切┑秃Y選性的索引。但是注意，對(duì)于數(shù)據(jù)量龐大的服務(wù)器，這些中間表可能會(huì)導(dǎo)致服務(wù)器的負(fù)荷大量增加。

innodb的統(tǒng)計(jì)值得深入研究。統(tǒng)計(jì)的結(jié)果是通過索引數(shù)據(jù)頁(yè)的隨機(jī)采樣計(jì)算得到的，這是假設(shè)剩余未被采樣到的數(shù)據(jù)也是類似的分布。在舊的innodb版本中，這個(gè)采樣的頁(yè)數(shù)是8，但最新版本的可以通過innodb_stats_sample_pages變量調(diào)整。將這個(gè)值設(shè)置為大于8有助于生成更具代表性的索引統(tǒng)計(jì)，尤其是對(duì)于大的數(shù)據(jù)表，但所需要花的代價(jià)也會(huì)不同。

innodb在數(shù)據(jù)表第一次打開，運(yùn)行analuze table和數(shù)據(jù)表存儲(chǔ)大小顯著改變時(shí)（1/16的變化量或20億行的插入）會(huì)計(jì)算索引統(tǒng)計(jì)。

information_schema表的某些查詢，運(yùn)行show table status，執(zhí)行show index查詢或mysql命令行客戶端啟用了自動(dòng)完成設(shè)置，innodb也會(huì)計(jì)算索引統(tǒng)計(jì)。這實(shí)際會(huì)對(duì)大數(shù)據(jù)量，或i/o速度很慢的服務(wù)器造成嚴(yán)重的問題。客戶端程序或監(jiān)控工具導(dǎo)致發(fā)生重新采樣會(huì)導(dǎo)致很多鎖和加重服務(wù)器負(fù)擔(dān)，也會(huì)影響終端用戶的啟動(dòng)時(shí)間。由于show index命令會(huì)更新索引統(tǒng)計(jì)，而如果你不更改的話你無法觀測(cè)到索引統(tǒng)計(jì)。你可以通過禁用innodb_stats_on_metadata（默認(rèn)是關(guān)閉的）選項(xiàng)去避免這些問題。下面的命令可以查出innodb索引統(tǒng)計(jì)相關(guān)的系統(tǒng)變量。

如果使用的是包含了替換innodb的percona xtradb存儲(chǔ)引擎的percona服務(wù)器，你可以做進(jìn)一步的配置。innodb_stats_auto_update選項(xiàng)可以讓你禁止自動(dòng)采樣，可以有效凍結(jié)自動(dòng)統(tǒng)計(jì)計(jì)算，除非你手動(dòng)運(yùn)行analyze table。這可以讓你擺脫不穩(wěn)定的查詢。這個(gè)特性是基于那些大型部署系統(tǒng)客戶的要求添加的。

為追求更高的查詢計(jì)劃穩(wěn)定性和更快的系統(tǒng)啟動(dòng)速度，你可以使用系統(tǒng)級(jí)的數(shù)據(jù)表存儲(chǔ)索引統(tǒng)計(jì)。這種方式在系統(tǒng)重啟或innodb第一次啟動(dòng)打開數(shù)據(jù)表時(shí)不需要重新計(jì)算索引統(tǒng)計(jì)。這個(gè)特性在percona 5.1版本已經(jīng)得到支持，并且在標(biāo)準(zhǔn)的mysql 5.6版本已經(jīng)得到支持。percona服務(wù)器這個(gè)特性是通過innodb_use_sys_stats_table選項(xiàng)啟用的。在mysql 5.6版本后，是通過innodb_stats_persistent選項(xiàng)控制的，默認(rèn)是on。同時(shí)，還有一個(gè)變量控制單表的，innodb_stats_auto_recalc變量默認(rèn)為on，會(huì)在數(shù)據(jù)表變化量超過10%時(shí)重新統(tǒng)計(jì)該表的索引統(tǒng)計(jì)（手冊(cè)可以參考：dev.mysql.com/doc/refman/）。

如果你沒有配置自動(dòng)更新索引統(tǒng)計(jì)，你需要定期使用analyze table命令來更新索引統(tǒng)計(jì)，除非你知道不更新不會(huì)導(dǎo)致糟糕的查詢計(jì)劃。

以上就是mysql 索引和數(shù)據(jù)表該如何維護(hù)的詳細(xì)內(nèi)容，更多關(guān)于mysql 索引和數(shù)據(jù)表維護(hù)的資料請(qǐng)關(guān)注服務(wù)器之家其它相關(guān)文章！