《Mysql實(shí)例mysql索引優(yōu)化實(shí)例解析》要點(diǎn)：
本文介紹了Mysql實(shí)例mysql索引優(yōu)化實(shí)例解析，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

一,有關(guān)mysql索引的使用辦法MYSQL必讀

　　所謂索引,因?yàn)樗羌涌觳樵兊淖钪匾墓ぞ?還有其他加快查詢的技術(shù),但是最有效的莫過(guò)于恰當(dāng)?shù)厥褂盟饕?在 mysql 的郵件清單上,人們通常詢問(wèn)關(guān)于使查詢更快的問(wèn)題.在大量的案例中,都是因?yàn)楸砩蠜](méi)有索引,一般只要加上索引就可以立即解決問(wèn)題.但這樣也并非總是有效,因?yàn)閮?yōu)化并非總是那樣簡(jiǎn)單.然而,如果不使用索引,在許多情形下,用其他手段改善性能只會(huì)是浪費(fèi)時(shí)間.應(yīng)該首先考慮使用索引取得最大的性能改善,然后再尋求其他可能有幫助的技術(shù).MYSQL必讀

　　本節(jié)介紹索引是什么、它怎樣改善查詢性能、索引在什么情況下可能會(huì)降低性能,以及怎樣為表選擇索引.下一節(jié),我們將討論 mysql 的查詢優(yōu)化程序.除了知道怎樣創(chuàng)建索引外,了解一些優(yōu)化程序的知識(shí)也是有好處的,因?yàn)檫@樣可以更好地利用所創(chuàng)建的索引.某些編寫查詢的辦法實(shí)際上會(huì)妨礙索引的效果,應(yīng)該避免這種情況出現(xiàn).(雖然并非總會(huì)這樣.有時(shí)也會(huì)希望忽略優(yōu)化程序的作用.我們也將介紹這些情況.)MYSQL必讀

二,索引對(duì)單個(gè)表查詢的影響MYSQL必讀

　　索引被用來(lái)快速找出在一個(gè)列上用一特定值的行.沒(méi)有索引,mysql不得不首先以第一條記錄開(kāi)始并然后讀完整個(gè)表直到它找出相關(guān)的行.表越大,花費(fèi)時(shí)間越多.如果表對(duì)于查詢的列有一個(gè)索引,mysql能快速到達(dá)一個(gè)位置去搜尋到數(shù)據(jù)文件的中間,沒(méi)有必要考慮所有數(shù)據(jù).如果一個(gè)表有1000行,這比順序讀取至少快100倍.注意你需要存取幾乎所有1000行,它較快的順序讀取,因?yàn)榇藭r(shí)我們避免磁盤尋道.MYSQL必讀

　　例如,student表：
　　MYSQL必讀

　　這樣,試圖對(duì)它進(jìn)行一個(gè)特定查詢時(shí),就不得不做一個(gè)全表的掃描,速度很慢.例如,我們查找出所有english成績(jī)不及格的學(xué)生：
　　MYSQL必讀

　　其中,where從句不得不匹配每個(gè)記錄,以檢查是否符合條件.對(duì)于這個(gè)較小的表也許感覺(jué)不到太多的影響.但是對(duì)于一個(gè)較大的表,例如一個(gè)非常大的學(xué)校,我們可能需要存儲(chǔ)成千上萬(wàn)的記錄,這樣一個(gè)檢索的所花的時(shí)間是十分可觀的.MYSQL必讀

　　如果,我們?yōu)閑nglish列創(chuàng)建一個(gè)索引：
　　MYSQL必讀

　　如上表,此索引存儲(chǔ)在索引文件中,包含表中每行的english列值,但此索引是在 english的基礎(chǔ)上排序的.現(xiàn)在,不需要逐行搜索全表查找匹配的條款,而是可以利用索引進(jìn)行查找.假如我們要查找分?jǐn)?shù)小于60的所有行,那么可以掃描索引,結(jié)果得出5行.然后到達(dá)分?jǐn)?shù)為66的行,及tom的記錄,這是一個(gè)比我們正在查找的要大的值.索引值是排序的,因此在讀到包含tom的記錄時(shí),我們知道不會(huì)再有匹配的記錄,可以退出了.如果查找一個(gè)值,它在索引表中某個(gè)中間點(diǎn)以前不會(huì)出現(xiàn),那么也有找到其第一個(gè)匹配索引項(xiàng)的定位算法,而不用進(jìn)行表的順序掃描(如二分查找法).這樣,可以快速定位到第一個(gè)匹配的值,以節(jié)省大量搜索時(shí)間.數(shù)據(jù)庫(kù)利用了各種各樣的快速定位索引值的技術(shù),這些技術(shù)是什么并不重要,重要的是它們工作正常,索引技術(shù)是個(gè)好東西.MYSQL必讀

　　因此在執(zhí)行下述查詢
　MYSQL必讀

　　你應(yīng)該可以發(fā)現(xiàn),這個(gè)結(jié)果與未索引english列之前的不同,它是排序的,原因正式如上所述.MYSQL必讀

三,索引對(duì)多個(gè)表查詢的影響MYSQL必讀

　　前面的討論描述了單表查詢中索引的好處,其中使用索引消除了全表掃描,極大地加快了搜索的速度.在執(zhí)行涉及多個(gè)表的連接查詢時(shí),索引甚至?xí)袃r(jià)值.在單個(gè)表的查詢中,每列需要查看的值的數(shù)目就是表中行的數(shù)目.而在多個(gè)表的查詢中,可能的組合數(shù)目極大,因?yàn)檫@個(gè)數(shù)目為各表中行數(shù)之積.MYSQL必讀

　　假如有三個(gè)未索引的表 t1、t2、t3,分別只包含列 c1、c2、c3,每個(gè)表分別由含有數(shù)值 1 到 1000 的 1000 行組成.查找對(duì)應(yīng)值相等的表行組合的查詢?nèi)缦滤荆?span id="7la94w5njlq9" class="showhide">MYSQL必讀

　　此查詢的結(jié)果應(yīng)該為 1000 行,每個(gè)組合包含 3 個(gè)相等的值.如果我們?cè)跓o(wú)索引的情況下處理此查詢,則不可能知道哪些行包含那些值.因此,必須尋找出所有組合以便得出與 where 子句相配的那些組合.可能的組合數(shù)目為 1000×1000×1000(十億),比匹配數(shù)目多一百萬(wàn)倍.很多工作都浪費(fèi)了,并且這個(gè)查詢將會(huì)非常慢,即使在如像 mysql 這樣快的數(shù)據(jù)庫(kù)中執(zhí)行也會(huì)很慢.而這還是每個(gè)表中只有 1000 行的情形.如果每個(gè)表中有一百萬(wàn)行時(shí),將會(huì)怎樣?很顯然,這樣將會(huì)產(chǎn)生性能極為低下的結(jié)果.如果對(duì)每個(gè)表進(jìn)行索引,就能極大地加速查詢進(jìn)程,因?yàn)槔盟饕牟樵兲幚砣缦拢?span id="7la94w5njlq9" class="showhide">MYSQL必讀

　　1) 如下從表 t1 中選擇第一行,查看此行所包含的值.MYSQL必讀

　　2) 使用表 t2 上的索引,直接跳到 t2 中與來(lái)自 t1 的值匹配的行.類似,利用表 t3 上的索引,直接跳到 t3 中與來(lái)自 t1 的值匹配的行.MYSQL必讀

　　3) 進(jìn)到表 t1 的下一行并重復(fù)前面的過(guò)程直到 t1 中所有的行已經(jīng)查過(guò).MYSQL必讀

　　在此情形下,我們?nèi)匀粚?duì)表 t1 執(zhí)行了一個(gè)完全掃描,但能夠在表 t2 和 t3 上進(jìn)行索引查找直接取出這些表中的行.從道理上說(shuō),這時(shí)的查詢比未用索引時(shí)要快一百萬(wàn)倍.MYSQL必讀

　　如上所述,mysql 利用索引加速了 where 子句中與條件相配的行的搜索,或者說(shuō)在執(zhí)行連接時(shí)加快了與其他表中的行匹配的行的搜索.MYSQL必讀

　　多列索引對(duì)查詢的影響MYSQL必讀

　　假定你發(fā)出下列select語(yǔ)句：
　　MYSQL必讀

　　如果一個(gè)多列索引存在于col1和col2上,適當(dāng)?shù)男锌梢灾苯颖蝗〕?如果分開(kāi)的單行列索引存在于col1和col2上,優(yōu)化器試圖通過(guò)決定哪個(gè)索引將找到更少的行并來(lái)找出更具限制性的索引并且使用該索引取行.MYSQL必讀

　　你可以這樣創(chuàng)建一個(gè)多列索引：
　　MYSQL必讀

　　而你應(yīng)該這樣創(chuàng)建分開(kāi)的單行列索引：
　　MYSQL必讀

　　如果表有一個(gè)多列索引,任何最左面的索引前綴能被優(yōu)化器使用以找出行.例如,如果你有一個(gè)3行列索引(col1,col2,col3),你已經(jīng)索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力.MYSQL必讀

　　如果列不構(gòu)成索引的最左面前綴,mysql不能使用一個(gè)部分的索引.假定你下面顯示的select語(yǔ)句：
　MYSQL必讀

　　如果一個(gè)索引存在于(col1、col2、col3)上,只有上面顯示的第一個(gè)查詢使用索引.第二個(gè)和第三個(gè)查詢確實(shí)包含索引的列,但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前綴.MYSQL必讀

　　如果like參數(shù)是一個(gè)不以一個(gè)通配符字符起始的一個(gè)常數(shù)字符串,mysql也為like比較使用索引.例如,下列select語(yǔ)句使用索引： mysql> select * from tbl_name where key_col like "patrick%";
　　MYSQL必讀

　　在第一條語(yǔ)句中,只考慮有"patrick" <= key_col < "patricl"的行.在第二條語(yǔ)句中,只考慮有"pat" <= key_col < "pau"的行.MYSQL必讀

　　下列select語(yǔ)句將不使用索引：MYSQL必讀

?　　在第一條語(yǔ)句中,like值以一個(gè)通配符字符開(kāi)始.在第二條語(yǔ)句中,like值不是一個(gè)常數(shù).MYSQL必讀

　　如果 column_name 是一個(gè)索引,使用column_name is null的搜索將使用索引.MYSQL必讀

　　mysql通常使用找出最少數(shù)量的行的索引.一個(gè)索引被用于你與下列操作符作比較的列：=、>、>=、<、<=、between和一個(gè)有一個(gè)非通配符前綴象'something%'的like的列.MYSQL必讀

　　對(duì)于一個(gè)多列索引,如果在where子句的所有and層次使用索引,將不使用來(lái)索引優(yōu)化查詢.為了能夠使用索引優(yōu)化查詢,必須把一個(gè)多列索引的前綴使用在一個(gè)and條件組中.MYSQL必讀

　　下列where子句使用索引：
　　MYSQL必讀

　　這些where子句不使用索引：
　　MYSQL必讀

　　mysql索引的作用
　　所有的mysql索引(primary、unique和index)在b樹(shù)中存儲(chǔ).字符串是自動(dòng)地壓縮前綴和結(jié)尾空間.create index句法.MYSQL必讀

　　索引用于：MYSQL必讀

　　快速找出匹配一個(gè)where子句的行.MYSQL必讀

　　在多個(gè)表的查詢時(shí),執(zhí)行連接時(shí)加快了與其他表中的行匹配的行的搜索.MYSQL必讀

　　對(duì)特定的索引列找出max()或min()值.MYSQL必讀

　　如果排序或分組在一個(gè)可用索引的最左面前綴上進(jìn)行(例如,order by key_part_1,key_part_2),排序或分組一個(gè)表.如果所有鍵值部分跟隨desc,鍵以倒序被讀取.MYSQL必讀

　　在一些情況中,一個(gè)查詢能被優(yōu)化來(lái)檢索值,不用咨詢數(shù)據(jù)文件.如果對(duì)某些表的所有使用的列是數(shù)字型的并且構(gòu)成某些鍵的最左面前綴,為了更快,值可以從索引樹(shù)被檢索出來(lái).MYSQL必讀

　　索引的弊端MYSQL必讀

　　一般情況下,如果 mysql 能夠知道怎樣用索引來(lái)更快地處理查詢,它就會(huì)這樣做.這表示,在大多數(shù)情況下,如果您不對(duì)表進(jìn)行索引,則損害的是您自己的利益.可以看出,作者描繪了索引的諸多好處.但有不利之處嗎?是的,有.實(shí)際上,這些缺點(diǎn)被優(yōu)點(diǎn)所掩蓋了,但應(yīng)該對(duì)它們有所了解.MYSQL必讀

　　首先,索引文件要占磁盤空間.如果有大量的索引,索引文件可能會(huì)比數(shù)據(jù)文件更快地達(dá)到最大的文件尺寸.其次,索引文件加快了檢索,但增加了插入和刪除,以及更新索引列中的值的時(shí)間(即,降低了大多數(shù)涉及寫入的操作的時(shí)間),因?yàn)閷懖僮鞑粌H涉及數(shù)據(jù)行,而且還常常涉及索引.一個(gè)表?yè)碛械乃饕蕉?則寫操作的平均性能下降就越大.在8.4.4節(jié)記錄裝載和修改的速度中,我們將更為詳細(xì)地介紹這些性能問(wèn)題,并討論怎樣解決.MYSQL必讀

　　選擇索引的準(zhǔn)則MYSQL必讀

　　創(chuàng)建索引的語(yǔ)法已經(jīng)在4.5索引屬性中進(jìn)行了介紹.這里,我們假定您已經(jīng)閱讀過(guò)該節(jié).但是知道語(yǔ)法并不能幫助確定表怎樣進(jìn)行索引.要決定表怎樣進(jìn)行索引需要考慮表的使用方式.本節(jié)介紹一些關(guān)于怎樣確定和挑選索引列的準(zhǔn)則：MYSQL必讀

　　1、搜索的索引列,不一定是所要選擇的列MYSQL必讀

　　換句話說(shuō),最適合索引的列是出現(xiàn)在 where 子句中的列,或連接子句中指定的列,而不是出現(xiàn)在 select 關(guān)鍵字后的選擇列表中的列,例如：MYSQL必讀

　　selectMYSQL必讀

　　col_a ←不適合作索引列MYSQL必讀

　　fromMYSQL必讀

　　tbl1 left join tbl2MYSQL必讀

　　on tbl1.col_b = tbl2.col_c ←適合作索引列MYSQL必讀

　　whereMYSQL必讀

　　col_d = expr ←適合作索引列MYSQL必讀

　　當(dāng)然,所選擇的列和用于 where 子句的列也可能是相同的.關(guān)鍵是,列出現(xiàn)在選擇列表中不是該列應(yīng)該索引的標(biāo)志.MYSQL必讀

　　出現(xiàn)在連接子句中的列或出現(xiàn)在形如 col1 = col2 的表達(dá)式中的列是很適合索引的列.查詢中的 col_b 和 col_c 就是這樣的例子.如果 mysql 能利用連接列來(lái)優(yōu)化一個(gè)查詢,表示它通過(guò)消除全表掃描相當(dāng)可觀地減少了表行的組合.MYSQL必讀

　　2、使用惟一索引MYSQL必讀

　　考慮某列中值的分布.對(duì)于惟一值的列,索引的效果最好,而具有多個(gè)重復(fù)值的列,其索引效果最差.例如,存放年齡的列具有不同值,很容易區(qū)分各行.而用來(lái)記錄性別的列,只含有“m”和“f”,則對(duì)此列進(jìn)行索引沒(méi)有多大用處(不管搜索哪個(gè)值,都會(huì)得出大約一半的行).MYSQL必讀

　　3、使用短索引MYSQL必讀

　　如果對(duì)串列進(jìn)行索引,應(yīng)該指定一個(gè)前綴長(zhǎng)度,只要有可能就應(yīng)該這樣做.例如,如果有一個(gè) char(200) 列,如果在前 10 個(gè)或 20 個(gè)字符內(nèi),多數(shù)值是惟一的,那么就不要對(duì)整個(gè)列進(jìn)行索引.對(duì)前 10 個(gè)或 20 個(gè)字符進(jìn)行索引能夠節(jié)省大量索引空間,也可能會(huì)使查詢更快.較小的索引涉及的磁盤 i/o 較少,較短的值比較起來(lái)更快.更為重要的是,對(duì)于較短的鍵值,索引高速緩存中的塊能容納更多的鍵值,因此,mysql 也可以在內(nèi)存中容納更多的值.這增加了找到行而不用讀取索引中較多塊的可能性.(當(dāng)然,應(yīng)該利用一些常識(shí).如僅用列值的第一個(gè)字符進(jìn)行索引是不可能有多大好處的,因?yàn)檫@個(gè)索引中不會(huì)有許多不同的值.)MYSQL必讀

　　4、利用最左前綴MYSQL必讀

　　在創(chuàng)建一個(gè) n 列的索引時(shí),實(shí)際是創(chuàng)建了 mysql 可利用的 n 個(gè)索引.多列索引可起幾個(gè)索引的作用,因?yàn)榭衫盟饕凶钭筮叺牧屑瘉?lái)匹配行.這樣的列集稱為最左前綴.(這與索引一個(gè)列的前綴不同,索引一個(gè)列的前綴是利用該的前 n 個(gè)字符作為索引值.)MYSQL必讀

　　假如一個(gè)表在分別名為 state、city 和 zip 的三個(gè)列上有一個(gè)索引.索引中的行是按 state/city/zip 的次序存放的,因此,索引中的行也會(huì)自動(dòng)按 state/city 的順序和 state 的順序存放.這表示,即使在查詢中只指定 state 值或只指定 state 和 city 的值,mysql 也可以利用索引.因此,此索引可用來(lái)搜索下列的列組合：MYSQL必讀

　　mysql 不能使用不涉及左前綴的搜索.例如,如果按 city 或 zip 進(jìn)行搜索,則不能使用該索引.如果要搜索某個(gè)州以及某個(gè) zip 代碼(索引中的列1和列3),則此索引不能用于相應(yīng)值的組合.但是,可利用索引來(lái)尋找與該州相符的行,以減少搜索范圍.MYSQL必讀

　　5、不要過(guò)度索引MYSQL必讀

　　不要以為索引“越多越好”,什么東西都用索引是錯(cuò)的.每個(gè)額外的索引都要占用額外的磁盤空間,并降低寫操作的性能,這一點(diǎn)我們前面已經(jīng)介紹過(guò).在修改表的內(nèi)容時(shí),索引必須進(jìn)行更新,有時(shí)可能需要重構(gòu),因此,索引越多,所花的時(shí)間越長(zhǎng).如果有一個(gè)索引很少利用或從不使用,那么會(huì)不必要地減緩表的修改速度.此外,mysql 在生成一個(gè)執(zhí)行計(jì)劃時(shí),要考慮各個(gè)索引,這也要費(fèi)時(shí)間.創(chuàng)建多余的索引給查詢優(yōu)化帶來(lái)了更多的工作.索引太多,也可能會(huì)使 mysql 選擇不到所要使用的最好索引.只保持所需的索引有利于查詢優(yōu)化.MYSQL必讀

　　如果想給已索引的表增加索引,應(yīng)該考慮所要增加的索引是否是現(xiàn)有多列索引的最左索引.如果是,則就不要費(fèi)力去增加這個(gè)索引了,因?yàn)橐呀?jīng)有了.MYSQL必讀

　　6、考慮在列上進(jìn)行的比較類型MYSQL必讀

　　索引可用于“<”、“<=”、“=”、“>=”、“>”和 between 運(yùn)算.在模式具有一個(gè)直接量前綴時(shí),索引也用于 like 運(yùn)算.如果只將某個(gè)列用于其他類型的運(yùn)算時(shí)(如 strcmp( )),對(duì)其進(jìn)行索引沒(méi)有價(jià)值.MYSQL必讀

　　總結(jié)MYSQL必讀

　　本節(jié)介紹了索引在優(yōu)化查詢中的作用,包括了索引優(yōu)化查詢的原理,索引在各種情況的檢索中的益處,也包括索引的的弊端：增加了存儲(chǔ)的空間,使裝載數(shù)據(jù)變慢.MYSQL必讀

　　索引是優(yōu)化查詢的最常用也是最有效的的辦法,一個(gè)數(shù)據(jù)表,尤其是容量很大的表,建立合適的索引,會(huì)使查詢的速度提高很大.

mysql索引優(yōu)化應(yīng)用實(shí)例
MySql索引優(yōu)化注意要點(diǎn)
mysql索引與mysql索引優(yōu)化查詢
Mysql索引優(yōu)化辦法解析
深入理解MySQL索引與優(yōu)化
mysql索引優(yōu)化實(shí)例分享
mysql索引使用與優(yōu)化
分享:Mysql索引優(yōu)化的技巧
mysql性能優(yōu)化之索引優(yōu)化MYSQL必讀

《Mysql實(shí)例mysql索引優(yōu)化實(shí)例解析》是否對(duì)您有啟發(fā)，歡迎查看更多與《Mysql實(shí)例mysql索引優(yōu)化實(shí)例解析》相關(guān)教程，學(xué)精學(xué)透。維易PHP學(xué)院為您提供精彩教程。

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/8906.html