關于Mysql索引那點事_MySQL

關系數據庫的世界是一個表與集合、表與集合上的運算占統治地位的世界。數據庫是一個表的集合，而表又是行和列的集合。在發布一條SELECT 查詢從表中進行檢索行時，得到另一個行和列的集合。這些都是一些抽象的概念，對于數據庫系統用來操縱表中數據的基本表示沒有多少參考價值。另一個抽象概念是，表上的運算都同時進行；查詢是一種概念性的集合運算，并且集合論中沒有時間概念。當然，現實世界是相當不同的。數據庫管理系統實現了抽象的概念，但是在實際的硬件 范圍內要受到實際的物理約束。結果是，查詢要花時間，有時要花很長的時間。而人類很容易不耐煩，不喜歡等待，因此我們丟下了集合上的那些瞬間的數學運算的抽象世界去尋求加速查詢的方法。幸運的是，有幾種加速運算的技術，可對表進行索引使數據庫服務器查找行更快。可考慮怎樣充分利用這些索引來編寫查詢。可編寫影響服務器調度機制的查詢，使來自多個客戶機的查詢協作得更好。我們思考基本硬件怎樣運行，以便想出怎樣克服其物理約束對性能進行改善的方法。

這些正是本文所要討論的問題，其目標是優化數據庫系統的性能，使其盡可能快地處理各種查詢。MySQL已經相當快了，但即使是最快的數據庫，在人的設計下還能運行得更快。

1 使用索引

我們首先討論索引，因為它是加快查詢的最重要的工具。還有其他加快查詢的技術，但是最有效的莫過于恰當地使用索引了。在MySQL的郵件清單上，人們通常詢問關于使查詢更快的問題。在大量的案例中，都是因為表上沒有索引，一般只要加上索引就可以立即解決問題。但這樣也并非總是有效，因為優化并非總是那樣簡單。然而，如果不使用索引，在許多情形下，用其他手段改善性能只會是浪費時間。應該首先考慮使用索引取得最大的性能改善，然后再尋求其他可能有幫助的技術。

本文介紹索引是什么、它怎樣改善查詢性能、索引在什么情況下可能會降低性能，以及怎樣為表選擇索引。下一節，我們將討論MySQL的查詢優化程序。除了知道怎樣創建索引外，了解一些優化程序的知識也是有好處的，因為這樣可以更好地利用所創建的索引。某些編寫查詢的方法實際上會妨礙索引的效果，應該避免這種情況出現。（雖然并非總會這樣。有時也會希望忽略優化程序的作用。我們也將介紹這些情況。）

1.1 索引的益處

讓我們從一個無索引的表著手來考察索引是怎樣起作用的。無索引的表就是一個無序的行集。例如，圖4 - 1給出了我們在第1章“MySQL與SQL 介紹” 中首先看到的ad 表。這個表上沒有索引，因此如果我們查找某個特定公司的行時，必須查看表中的每一行，看它是否與所需的值匹配。這是一個全表掃描，很慢，如果表中只有少數幾個記錄與搜索條件相匹配，則其效率是相當低的。

圖4 - 2給出了相同的表，但在表的company_num 列上增加了一個索引。此索引包含表中每行的一項，但此索引是在company_num 上排序的。現在，不需要逐行搜索全表查找匹配的條款，而是可以利用索引進行查找。假如我們要查找公司13的所有行，那么可以掃描索引，結果得出3行。然后到達公司14的行，這是一個比我們正在查找的要大的號碼。索引值是排序的，因此在讀到包含14的記錄時，我們知道不會再有匹配的記錄，可以退出了。如果查找一個值，它在索引表中某個中間點以前不會出現，那么也有找到其第一個匹配索引項的定位算法，而不用進行表的順序掃描（如二分查找法）。這樣，可以快速定位到第一個匹配的值，以節省大量搜索時間。數據庫利用了各種各樣的快速定位索引值的技術，這些技術是什么并不重要，重要的是它們工作正常，索引技術是個好東西。

有人會問，為什么不只對數據文件進行排序，省掉索引文件？這樣不也在搜索時產生相同的效果嗎？問得好，如果只有單個索引時，是這樣的。不過有可能會用到第二個索引，但同時以兩種不同的方法對同一個數據文件進行排序是不可能的。（如，想要一個顧客名的索引，同時又要一個顧客ID 號或電話號碼的索引。）將索引文件作為一個與數據文件獨立的實體就解決了這個問題，而且允許創建多個索引。此外，索引中的行一般要比數據文件中的行短。在插入或刪除值時，為保持排序順序而移動較短的索引值與移動較長的數據行相比更為容易。

這個例子與MySQL索引表的方法相符。表的數據行保存在數據文件中，而索引值保存在索引文件中。一個表上可有不止一個索引；如果確實有不止一個索引，它們都保存在同一個索引文件中。索引文件中的每個索引由排過序的用來快速訪問數據文件的鍵記錄數組構成。

前面的討論描述了單表查詢中索引的好處，其中使用索引消除了全表掃描，極大地加快了搜索的速度。在執行涉及多個表的連接查詢時，索引甚至會更有價值。在單個表的查詢中，每列需要查看的值的數目就是表中行的數目。而在多個表的查詢中，可能的組合數目極大，因為這個數目為各表中行數之積。

假如有三個未索引的表t 1、t 2、t 3，分別只包含列c 1、c 2、c 3，每個表分別由含有數值1到1000 的1000 行組成。查找對應值相等的表行組合的查詢如下所示：
SELECT c1,c2,c3
FROM t1,t2,t3
WHERE c1=c2 AND c1=c3

此查詢的結果應該為1000 行，每個組合包含3 個相等的值。如果我們在無索引的情況下處理此查詢，則不可能知道哪些行包含那些值。因此，必須尋找出所有組合以便得出與WHERE 子句相配的那些組合。可能的組合數目為10 0 0×10 0 0×10 0 0（十億），比匹配數目多一百萬倍。很多工作都浪費了，并且這個查詢將會非常慢，即使在如像MySQL這樣快的數據庫中執行也會很慢。而這還是每個表中只有1000 行的情形。如果每個表中有一百萬行時，將會怎樣？很顯然，這樣將會產生性能極為低下的結果。如果對每個表進行索引，就能極大地加速查詢進程，因為利用索引的查詢處理如下：

1) 如下從表t1中選擇第一行，查看此行所包含的值。
2) 使用表t2 上的索引，直接跳到t2 中與來自t1的值匹配的行。類似，利用表t3 上的索引，直接跳到t3 中與來自t1的值匹配的行。
3) 進到表t1的下一行并重復前面的過程直到t1中所有的行已經查過。在此情形下，我們仍然對表t1執行了一個完全掃描，但能夠在表t2 和t3 上進行索引查找直接取出這些表中的行。從道理上說，這時的查詢比未用索引時要快一百萬倍。如上所述，MySQL利用索引加速了WHERE 子句中與條件相配的行的搜索，或者說在執行連接時加快了與其他表中的行匹配的行的搜索。它也利用索引來改進其他操作的性能：

■ 在使用MIN( ) 和MAX( ) 函數時，能夠快速找到索引列的最小或最大值。
■ MySQL常常能夠利用索引來完成ORDER BY 子句的排序操作。
■ 有時，MySQL可避免對整個數據文件的讀取。假如從一個索引數值列中選擇值，而且不選擇表中其他列。這時，通過對索引值的讀取，就已經得到了讀取數據文件所要得到的值。沒有對相同的值進行兩次讀取的必要，因此，甚至無需涉及數據文件。 bitsCN.com

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com