Mysql覆蓋索引的示例分析-創(chuàng)新互聯(lián)

小編給大家分享一下Mysql覆蓋索引的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

成都創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供策勒網(wǎng)站建設、策勒做網(wǎng)站、策勒網(wǎng)站設計、策勒網(wǎng)站制作等企業(yè)網(wǎng)站建設、網(wǎng)頁設計與制作、策勒企業(yè)網(wǎng)站模板建站服務，10多年策勒做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡服務。

概念

如果索引包含所有滿足查詢需要的數(shù)據(jù)的索引成為覆蓋索引(Covering Index)，也就是平時所說的不需要回表操作

判斷標準

使用explain，可以通過輸出的extra列來判斷，對于一個索引覆蓋查詢，顯示為using index,MySQL查詢優(yōu)化器在執(zhí)行查詢前會決定是否有索引覆蓋查詢

注意

1、覆蓋索引也并不適用于任意的索引類型，索引必須存儲列的值

2、Hash 和full-text索引不存儲值，因此MySQL只能使用B-TREE

3、并且不同的存儲引擎實現(xiàn)覆蓋索引都是不同的

4、并不是所有的存儲引擎都支持它們

5、如果要使用覆蓋索引，一定要注意SELECT 列表值取出需要的列，不可以是SELECT *，因為如果將所有字段一起做索引會導致索引文件過大，查詢性能下降，不能為了利用覆蓋索引而這么做

如果一個索引包含(或覆蓋)所有需要查詢的字段的值，稱為‘覆蓋索引'。即只需掃描索引而無須回表。

只掃描索引而無需回表的優(yōu)點：

1.索引條目通常遠小于數(shù)據(jù)行大小，只需要讀取索引，則mysql會極大地減少數(shù)據(jù)訪問量。

2.因為索引是按照列值順序存儲的，所以對于IO密集的范圍查找會比隨機從磁盤讀取每一行數(shù)據(jù)的IO少很多。

3.一些存儲引擎如myisam在內(nèi)存中只緩存索引，數(shù)據(jù)則依賴于操作系統(tǒng)來緩存，因此要訪問數(shù)據(jù)需要一次系統(tǒng)調(diào)用

4.innodb的聚簇索引，覆蓋索引對innodb表特別有用。(innodb的二級索引在葉子節(jié)點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢)

覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引不存儲索引列的值，所以mysql只能用B-tree索引做覆蓋索引。

當發(fā)起一個索引覆蓋查詢時，在explain的extra列可以看到using index的信息

覆蓋索引的坑：mysql查詢優(yōu)化器會在執(zhí)行查詢前判斷是否有一個索引能進行覆蓋，假設索引覆蓋了where條件中的字段，但不是整個查詢涉及的字段，mysql5.5和之前的版本也會回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會被過濾掉。

如上圖則無法使用覆蓋查詢，原因：

1.沒有任何索引能夠覆蓋這個索引。因為查詢從表中選擇了所有的列，而沒有任何索引覆蓋了所有的列。

2.mysql不能在索引中執(zhí)行LIke操作。mysql能在索引中做最左前綴匹配的like比較，但是如果是通配符開頭的like查詢，存儲引擎就無法做比較匹配。這種情況下mysql只能提取數(shù)據(jù)行的值而不是索引值來做比較

優(yōu)化后SQL：添加索引（artist,title,prod_id），使用了延遲關(guān)聯(lián)(延遲了對列的訪問)

說明：在查詢的第一階段可以使用覆蓋索引，在from子句中的子查詢找到匹配的prod_id，然后根據(jù)prod_id值在外層查詢匹配獲取需要的所有值。

5.5時API設計不允許mysql將過濾條件傳到存儲引擎層（是把數(shù)據(jù)從存儲引擎拉到服務器層，在根據(jù)條件過濾），5.6之后由于ICP這個特性改善了查詢執(zhí)行方式

當MySQL不能使用索引進行排序時，就會利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對數(shù)據(jù)進行排序，如果內(nèi)存裝載不下，它會將磁盤上的數(shù)據(jù)進行分塊，再對各個數(shù)據(jù)塊進行排序，然后將各個塊合并成有序的結(jié)果集（實際上就是外排序）

對于filesort，MySQL有兩種排序算法

1、兩遍掃描算法(Two passes)

實現(xiàn)方式是先將須要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出，然后在設定的內(nèi)存（通過參數(shù)sort_buffer_size設定）中進行排序，完成排序之后再次通過行指針信息取出所需的Columns

注：該算法是4.1之前采用的算法，它需要兩次訪問數(shù)據(jù)，尤其是第二次讀取操作會導致大量的隨機I/O操作。另一方面，內(nèi)存開銷較小

2、 一次掃描算法(single pass)

該算法一次性將所需的Columns全部取出，在內(nèi)存中排序后直接將結(jié)果輸出
注： 從 MySQL 4.1 版本開始使用該算法。它減少了I/O的次數(shù)，效率較高，但是內(nèi)存開銷也較大。如果我們將并不需要的Columns也取出來，就會極大地浪費排序過程所需要 的內(nèi)存。在 MySQL 4.1 之后的版本中，可以通過設置 max_length_for_sort_data 參數(shù)來控制 MySQL 選擇第一種排序算法還是第二種。當取出的所有大字段總大小大于 max_length_for_sort_data 的設置時，MySQL 就會選擇使用第一種排序算法，反之，則會選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。

當對連接操作進行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進行filesort操作，然后進行連接處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結(jié)果集生成一個臨時表，在連接完成之后進行filesort操作，此時，EXPLAIN輸出 “Using temporary;Using filesort”

以上是“Mysql覆蓋索引的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)成都網(wǎng)站設計公司行業(yè)資訊頻道！

另外有需要云服務器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

網(wǎng)頁題目：Mysql覆蓋索引的示例分析-創(chuàng)新互聯(lián)
本文URL：http://muchs.cn/article48/dsppep.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供App開發(fā)、網(wǎng)站維護、手機網(wǎng)站建設、品牌網(wǎng)站制作、電子商務、外貿(mào)網(wǎng)站建設

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)