Banner
首頁 > 最新資訊 > 內容
湖南java培訓課程:MySQL——高性能索引策略
- 2019-02-21-

1.隔離列

如果在查詢中沒有隔離索引的列,MySQL通常不會使用索引?!案綦x”意味著它不是表達式的一部分,也沒有位于函數中。硅谷教育湖南java培訓課程

2.前綴索引和索引的選擇性

有時需要索引很長的字符列,它會使索引變大并且變慢。一個策略就是模擬哈希索引。但是有時這也不夠好,那么該怎么辦?

通??梢运饕_始的幾個字符,而不是全部值,以節約空間并得到好的性能。這使索引需要的空間變小,但是也會降低選擇性。索引選擇性是不重復的索引值和表中所有行的比值。高選擇性的索引有好處,因為它使MySQL在查找匹配的時候可以過濾更多的行。唯一索引的選擇率為1,為最佳值 。

矛盾在于選擇足夠長的前綴會得到好的選擇性,但是短的前綴會節約空間。前綴應該足夠長,使它的選擇性能夠接近于索引整個列。換句話說,前綴的基數性應該接近于全列的基數性。

前綴索引能很好地減少索引的大小及提高速度,但是它也有壞處:MySQL不能在ORDER BY或GROUP BY查詢中使用前綴索引,也不能把它們用作覆蓋索引。

3. 聚集索引

聚集索引不是一種單獨的索引類型,而是一種存儲數據的方式。其具體細節依賴于實現方式,但是InnoDB的聚集索引實際上在同樣的結構中保存了B-Tree索引和數據行。

當表有聚集索引的時候,它的數據行實際保存在索引的葉子頁中。術語“聚集”指實際的數據行和相關的鍵值都保存在一起。每個表只能有一個聚集索引,因為不能一次把行保存在兩個地方。

由于是存儲引擎負責實現索引,因此不是所有的存儲引擎都支持聚集索引。當前,SolidDB和InnoDB是唯一支持聚集索引的存儲引擎。硅谷教育

一些數據庫服務器可以選擇聚集的列,但是目前沒有任何MySQL的存儲引擎能做到這點。InnoDB按照主鍵進行聚集。

如果沒有定義主鍵,InnoDB會試著使用唯一的非空索引來代替。如果沒有這種索引,InnoDB就會定義隱藏的主鍵然后再上面進行聚集。InnoDB只能聚集在同一頁面中的記錄。包含相鄰鍵值的頁面也許會相距甚遠。

聚集的數據有以下優點:

可以把相關數據保存在一起。

數據訪問快。聚集索引把索引和數據都保存在了同一棵B-Tree樹中。

使用覆蓋索引的查詢可以使用包含在葉子節點中的主鍵值。

缺點:

聚集能最大限度地提升I/O密集負載的性能。如果數據能裝入內存,那么其順序也就無所謂了,這樣聚集就沒什么用處。

插入速度嚴重依賴于插入順序。按照主鍵的順序插入行是把數據裝入InnoDB表最快的方法。如果沒有按照主鍵順序插入數據,那么在插入之后最好使用OPTIMIZE TABLE重新組織一下表。

更新聚集索引列是昂貴的,因為它強制InnoDB把每個更新的行移動到新的位置。硅谷教育

建立在聚集索引上的表在插入新行,或者在行的主鍵被更新,該行必須被移動的時候會進行分頁。分頁發生在行的鍵值要求行必須被放在一個已經放滿了數據的也得時候,此時存儲引擎必須分頁才能容納該行。分頁會導致表占用更多的磁盤空間。

聚集表可能會比全表掃描慢,尤其在表存儲得比較稀疏或因為分頁而沒有順序存儲的時候。

第二(非聚集)索引可能會比預期的大,因為它們的葉子節點包含了被引用行的主鍵列。

第二索引訪問需要兩次索引查找,而不是一次。

最后一點有點迷惑人。答案在于第二索引保存的“行指針”的本質。葉子節點不會保存引用的行的物理位置,而是保持了行的主鍵值。硅谷教育

這意味著為了從第二索引查找行,存儲引擎首先要找到葉子節點,然后使用保存在那里的主鍵值找到主鍵,最終找到行。這需要兩次動作,兩次B-Tree導航,而不是一次。(在InnoDB中,自適應哈希索引能減少這種損失。)

比較InnoDB和MyISAM的數據布局: InnoDB支持聚集索引,因此它以不同于MyISAM的方式保存數據。

4. 覆蓋索引

包含所有滿足查詢需要的數據的索引叫覆蓋索引。

索引記錄通常遠小于全行大小,因此,如果只讀索引,MySQL就能極大地減少數據訪問量。因為索引比數據小很多,能更好地被裝入內存。(這對于MyISAM尤其正確,它能壓縮索引,使它們變得更小。)硅谷教育

索引時按照索引值進行排序的,因此I/O密集型范圍訪問將會比隨機地從磁盤上提取每一行數據要快的多。

大部分存儲引擎緩存索引比緩存數據更好。一些存儲引擎,例如MyISAM,只在MySQL內存中緩存了索引。因為操作系統緩存了給MyISAM的數據,訪問它通常需要系統調用。這也許會導致巨大的性能影響,尤其對于那些系統調用占了數據訪問中最大開銷的負載。硅谷教育

覆蓋索引對于InnoDB表特別有用,因為InnoDB的聚集緩存。InnoDB的第二索引在葉子節點中保存了行的主鍵值。因此,覆蓋了查詢的第二索引在主鍵中避免了另外一次索引查找。

覆蓋索引和任何索引都不一樣。覆蓋索引必須保存它包含的列的數據。哈希、空間和全文索引不會保存這些值,因此MySQL只能使用B-Tree索引來覆蓋查詢。并且,不同的存儲引擎實現覆蓋索引的方式不一樣,不是所有的存儲引擎都支持覆蓋索引。

5 為排序使用索引掃描

MySQL有兩種產生排序結果的方式:使用文件排序,或者掃描有序的索引。MySQL能為排序和查找行使用同樣的索引。

按照索引對結果進行排序,只有當索引的順序和ORDER BY子句中的順序完全一致,并且所有列排序的方向(升序或降序)一樣才可以。硅谷教育,如果查詢連接了多個表,只有在ORDER BY子句的所有列引用的是第一個表才可以。查找查詢中的ORDER BY子句也有同樣的局限:它要使用索引的最左前綴。在其他所有情況下,MySQL使用文件排序。

按照索引進行排序的一耳光最重要的用途是有ORDER BY和LIMIT子句的查詢。

6. 壓縮(前綴壓縮)索引

MyISAM使用前綴壓縮以減少索引大小,運行更多索引被裝入內存,以及在某些情況下極大地提高性能。它在默認情況下會壓縮字符串,但是可以讓它壓縮整數。

壓縮后的塊占用的空間較小,但是某些操作變慢了。因為每個值的壓縮前綴依賴于前面的值,MyISAM不能再索引塊中中庸二分查找找到想要的值,必須從頭開始。順序向前的操作性能尚可,但是反向掃描——例如ORDER BY DESC———不會很好地工作。任何須查找數據塊中部的行的操作要對塊進行掃描,平均說來,要掃描半個塊。測試表明壓縮后的鍵使MyISAM表上的索引查找對CPU密集的負載會慢幾倍,因為掃描須隨機查找。

權衡壓縮的標準時CPU內存資源和磁盤資源的折中。

7. 多余和重復索引

選擇擴展索引作。

8.索引和鎖定

索引對InnoDB有很重要的作用,因為它會讓查詢鎖定更少的行。

這里有一個很少人知道的關于InnoDB、索引和鎖定的細節:InnoDB能在第二索引上放置共享(讀取)鎖,但是獨占(寫入)鎖要求訪問主鍵。這消除了使用覆蓋索引的可能性,并且能導致SELECT FOR UPDATE比LOCK IN SHARE MODE或非鎖定查詢慢得多。

硅谷教育

地 址:湖南省長沙市岳麓區高新開發區延農創業基地5樓

聯系方式:0731-83755989

公司官網:http://www.369interactive.com/

關注我們

版權所有:硅谷教育手機版營業執照信息公示

亚洲国产在线精品国