mysql查詢優(yōu)化技術(shù)系列講座之使用索引--

《mysql查詢優(yōu)化技術(shù)系列講座之使用索引--》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在應(yīng)用文檔-天天文庫(kù)。

1、MySQL查詢優(yōu)化技術(shù)系列講座之使用索引>>　　索引是提高查詢速度的最重要的工具。當(dāng)然還有其它的一些技術(shù)可供使用，但是一般來(lái)說(shuō)引起最大性能差異的都是索引的正確使用。在MySQL郵件列表中，人們經(jīng)常詢問(wèn)那些讓查詢運(yùn)行得更快的方法。在大多數(shù)情況下，我們應(yīng)該懷疑數(shù)據(jù)表上有沒(méi)有索引，并且通常在添加索引之后立即解決了問(wèn)題。當(dāng)然，并不總是這樣簡(jiǎn)單就可以解決問(wèn)題的，因?yàn)閮?yōu)化技術(shù)本來(lái)就并非總是簡(jiǎn)單的。然而，如果沒(méi)有使用索引，在很多情況下，你試圖使用其它的方法來(lái)提高性能都是在浪費(fèi)時(shí)間。首先使用索引來(lái)獲取最大的性能提高，接著再看其

2、它的技術(shù)是否有用。　　這一部分講述了索引是什么以及索引是怎么樣提高查詢性能的。它還討論了在某些環(huán)境中索引可能降低性能，并為你明智地選擇數(shù)據(jù)表的索引提供了一些指導(dǎo)方針。在下一部分中我們將討論MySQL查詢優(yōu)化器，它試圖找到執(zhí)行查詢的效率最高的方法。了解一些優(yōu)化器的知識(shí)，作為對(duì)如何建立索引的補(bǔ)充，對(duì)我們是有好處的，因?yàn)檫@樣你才能更好地利用自己所建立的索引。某些編寫查詢的方法實(shí)際上讓索引不起作用，在一般情況下你應(yīng)該避免這種情形的發(fā)生。　　索引的優(yōu)點(diǎn)　　讓我們開(kāi)始了解索引是如何工作的，首先有一個(gè)不帶索引的數(shù)據(jù)表。不帶索

3、引的表僅僅是一個(gè)無(wú)序的數(shù)據(jù)行集合。例如，圖1顯示的ad表就是不帶索引的表，因此如果需要查找某個(gè)特定的公司，就必須檢查表中的每個(gè)數(shù)據(jù)行看它是否與目標(biāo)值相匹配。這會(huì)導(dǎo)致一次完全的數(shù)據(jù)表掃描，這個(gè)過(guò)程會(huì)很慢，如果這個(gè)表很大，但是只包含少量的符合條件的記錄，那么效率會(huì)非常低?！　D1：無(wú)索引的ad表　　圖2是同樣的一張數(shù)據(jù)表，但是增加了對(duì)ad表的pany_num數(shù)據(jù)列的索引。這個(gè)索引包含了ad表中的每個(gè)數(shù)據(jù)行的條目，但是索引的條目是按照pany_num值排序的?，F(xiàn)在，我們不是逐行查看以搜尋匹配的數(shù)據(jù)項(xiàng)，而是使用索引。假

4、設(shè)我們查找公司13的所有數(shù)據(jù)行。我們開(kāi)始掃描索引并找到了該公司的三個(gè)值。接著我們碰到了公司14的索引值，它比我們正在搜尋的值大。索引值是排過(guò)序的，因此當(dāng)我們讀取了包含14的索引記錄的時(shí)候，我們就知道再也不會(huì)有更多的匹配記錄，可以結(jié)束查詢操作了。因此使用索引獲得的功效是：我們找到了匹配的數(shù)據(jù)行在哪兒終止，并能夠忽略其它的數(shù)據(jù)行。另一個(gè)功效來(lái)自使用定位算法查找第一條匹配的條目，而不需要從索引頭開(kāi)始執(zhí)行線性掃描（例如，二分搜索就比線性掃描要快一些）。通過(guò)使用這種方法，我們可以快速地定位第一個(gè)匹配的值，節(jié)省了大量的搜索

5、時(shí)間。數(shù)據(jù)庫(kù)使用了多種技術(shù)來(lái)快速地定位索引值，但是在本文中我們不關(guān)心這些技術(shù)。重點(diǎn)是它們能夠?qū)崿F(xiàn)，并且索引是個(gè)好東西?！　D2：索引后的ad表　　你可能要問(wèn)，我們?yōu)槭裁床粚?duì)數(shù)據(jù)行進(jìn)行排序從而省掉索引？這樣不是也能實(shí)現(xiàn)同樣的搜索速度的改善嗎？是的，如果表只有一個(gè)索引，這樣做也可能達(dá)到相同的效果。但是你可能添加第二個(gè)索引，那么就無(wú)法一次使用兩種不同方法對(duì)數(shù)據(jù)行進(jìn)行排序了（例如，你可能希望在顧客名稱上建立一個(gè)索引，在顧客ID號(hào)或號(hào)碼上建立另外一個(gè)索引）。把與數(shù)據(jù)行相分離的條目作為索引解決了這個(gè)問(wèn)題，允許我們創(chuàng)建多個(gè)索

6、引。此外，索引中的行一般也比數(shù)據(jù)行短一些。當(dāng)你插入或刪除新的值的時(shí)候，移動(dòng)較短的索引值比移動(dòng)較長(zhǎng)數(shù)據(jù)行的排序次序更加容易?！　〔煌腗ySQL存儲(chǔ)引擎的索引實(shí)現(xiàn)的具體細(xì)節(jié)信息是不同的。例如，對(duì)于MyISAM數(shù)據(jù)表，該表的數(shù)據(jù)行保存在一個(gè)數(shù)據(jù)文件中，索引值保存在索引文件中。一個(gè)數(shù)據(jù)表上可能有多個(gè)索引，但是它們都被存儲(chǔ)在同一個(gè)索引文件中。索引文件中的每個(gè)索引都包含一個(gè)排序的鍵記錄（它用于快速地訪問(wèn)數(shù)據(jù)文件）數(shù)組?！　∨c此形成對(duì)照的是，BDB和InnoDB存儲(chǔ)引擎沒(méi)有使用這種方法來(lái)分離數(shù)據(jù)行和索引值，盡管它們也把索引

7、作為排序后的值集合進(jìn)行操作。在默認(rèn)情況下，BDB引擎使用單個(gè)文件存儲(chǔ)數(shù)據(jù)和索引值。InnoDB使用單個(gè)數(shù)據(jù)表空間（tablespace），在表空間中管理所有InnoDB表的數(shù)據(jù)和索引存儲(chǔ)。我們可以把InnoDB配置為每個(gè)表都在自己的表空間中創(chuàng)建，但是即使是這樣，數(shù)據(jù)表的數(shù)據(jù)和索引也存儲(chǔ)在同一個(gè)表空間文件中?！　∏懊娴挠懻撁枋隽藛蝹€(gè)表查詢環(huán)境下的索引的優(yōu)點(diǎn)，在這種情況下，通過(guò)減少對(duì)整個(gè)表的掃描，使用索引明顯地提高了搜索的速度。當(dāng)你運(yùn)行涉及多表聯(lián)結(jié)（jion）查詢的時(shí)候，索引的價(jià)值就更高了。在單表查詢中，你需要在每

8、個(gè)數(shù)據(jù)列上檢查的值的數(shù)量是表中數(shù)據(jù)行的數(shù)量。在多表查詢中，這個(gè)數(shù)量可能大幅度上升，因?yàn)檫@個(gè)數(shù)量是這些表中數(shù)據(jù)行的數(shù)量所產(chǎn)生的?！　〖僭O(shè)你擁有三個(gè)未索引的表t1、t2和t3，每個(gè)表都分別包含數(shù)據(jù)列i1、i2和i3，并且每個(gè)表都包含了1000條數(shù)據(jù)行，其序號(hào)從1到1000。查找某些值匹配的數(shù)據(jù)行組合的查詢可能如下所示：　　SELECTt1.i1,t2.i2,t3.i3FROMt1,t2,t