主頁 > 知識庫 > MySQL 索引和數(shù)據(jù)表該如何維護(hù)

MySQL 索引和數(shù)據(jù)表該如何維護(hù)

熱門標(biāo)簽:400電話辦理服務(wù)價(jià)格最實(shí)惠 html地圖標(biāo)注并導(dǎo)航 呂梁外呼系統(tǒng) 催天下外呼系統(tǒng) 南太平洋地圖標(biāo)注 武漢電銷機(jī)器人電話 北京金倫外呼系統(tǒng) 大豐地圖標(biāo)注app 400電話變更申請

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

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

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

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

ALTER TABLE innodb_tb1 ENGINE=INNODB;

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

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

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

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

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

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

當(dāng)存儲引擎給查詢優(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)則就是這次查詢會查找的數(shù)據(jù)量。如果索引統(tǒng)計(jì)從來沒有生成,或者是過期了,優(yōu)化器可能會做出錯(cuò)誤的決定。解決的方案是運(yùn)行ANALYZE TABLE命令,該命令會重建索引統(tǒng)計(jì)。

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

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

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

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

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

InnoDB在數(shù)據(jù)表第一次打開,運(yùn)行ANALUZE TABLE和數(shù)據(jù)表存儲大小顯著改變時(shí)(1/16的變化量或20億行的插入)會計(jì)算索引統(tǒng)計(jì)。

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

SHOW GLOBAL VARIABLES WHERE Variable_name like 'innodb_stats%'

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

為追求更高的查詢計(jì)劃穩(wěn)定性和更快的系統(tǒng)啟動速度,你可以使用系統(tǒng)級的數(shù)據(jù)表存儲索引統(tǒng)計(jì)。這種方式在系統(tǒng)重啟或InnoDB第一次啟動打開數(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,會在數(shù)據(jù)表變化量超過10%時(shí)重新統(tǒng)計(jì)該表的索引統(tǒng)計(jì)(手冊可以參考:dev.mysql.com/doc/refman/…)。

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

以上就是MySQL 索引和數(shù)據(jù)表該如何維護(hù)的詳細(xì)內(nèi)容,更多關(guān)于MySQL 索引和數(shù)據(jù)表維護(hù)的資料請關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • MySQL 常見的數(shù)據(jù)表設(shè)計(jì)誤區(qū)匯總
  • MySQL數(shù)據(jù)表分區(qū)策略及優(yōu)缺點(diǎn)分析
  • MySQL高級特性——數(shù)據(jù)表分區(qū)的概念及機(jī)制詳解
  • MySQL如何構(gòu)建數(shù)據(jù)表索引
  • Mysql刪除數(shù)據(jù)以及數(shù)據(jù)表的方法實(shí)例
  • MySQL創(chuàng)建數(shù)據(jù)表時(shí)設(shè)定引擎MyISAM/InnoDB操作
  • 刪除mysql數(shù)據(jù)表如何操作
  • 關(guān)于MYSQL 你需要知道的數(shù)據(jù)類型和操作數(shù)據(jù)表
  • MySQL創(chuàng)建數(shù)據(jù)表并建立主外鍵關(guān)系詳解
  • MySQL數(shù)據(jù)表合并去重的簡單實(shí)現(xiàn)方法
  • MySQL 如何設(shè)計(jì)統(tǒng)計(jì)數(shù)據(jù)表

標(biāo)簽:徐州 自貢 麗水 西寧 迪慶 南充 無錫 龍巖

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL 索引和數(shù)據(jù)表該如何維護(hù)》,本文關(guān)鍵詞  MySQL,索引,和,數(shù)據(jù)表,該,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL 索引和數(shù)據(jù)表該如何維護(hù)》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySQL 索引和數(shù)據(jù)表該如何維護(hù)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章