欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL 覆蓋索引的優(yōu)點(diǎn)

 更新時間:2021年05月19日 11:15:53   作者:島上碼農(nóng)  
當(dāng)索引包含了所有查詢的數(shù)據(jù)時,這個索引就稱之為覆蓋索引。覆蓋索引能夠成為一個非常有力的工具并且能夠顯著改善性能,本文將具體講述覆蓋索引的優(yōu)點(diǎn)

一個通常的建議是為WHERE條件創(chuàng)建索引,但這其實(shí)是片面的。索引應(yīng)當(dāng)為全部查詢設(shè)計(jì),而不僅僅是WHERE條件。索引確實(shí)能有效地查找數(shù)據(jù)行,但MySQL也能夠使用索引獲取列數(shù)據(jù),這樣根本不需要去讀取一行數(shù)據(jù)。畢竟,索引的葉子節(jié)點(diǎn)包含了索引對應(yīng)的值。當(dāng)年能夠讀取索引就能夠拿到想要的數(shù)據(jù)時為什么還去讀數(shù)據(jù)行呢?當(dāng)索引包含了所有查詢的數(shù)據(jù)時,這個索引就稱之為覆蓋索引。

覆蓋索引能夠成為一個非常有力的工具并且能夠顯著改善性能??紤]一下不讀數(shù)據(jù)只需要讀取索引的情況:

  • 索引值通常會比整個行存儲空間小很多,因此MySQL只讀取索引值時可以只讀取很少的數(shù)據(jù)。這對于緩存負(fù)荷來說十分重要——響應(yīng)時間大部分消耗在復(fù)制數(shù)據(jù)。對于磁盤I/O而言也是一樣,因?yàn)樗饕龜?shù)據(jù)相比行數(shù)據(jù)存儲空間小很多,因此更節(jié)省I/O負(fù)載和內(nèi)存占用(對于MyISAM引擎更顯著,因?yàn)镸yISAM可以將索引打包使得存儲空間更小)。
  • 索引是按索引值的順序存儲的,因此I/O訪問跨度相比隨機(jī)磁盤位置獲取行數(shù)據(jù)而言消耗的I/O頻次更少。對于某些存儲引擎,例如MyISAM和Percona XtraDB,你甚至能夠使用OPTIMIZE優(yōu)化表獲得完全有序的索引,這會使得簡單范圍的查詢完全使用順序訪問。
  • 有些存儲引擎,例如MyISAM,MySQL內(nèi)存中只緩存索引。由于操作系統(tǒng)為MyISAM緩存了數(shù)據(jù),訪問時通常需要一個系統(tǒng)調(diào)用。這可能導(dǎo)致巨大的性能影響,尤其是對于緩存負(fù)荷場景來說,系統(tǒng)調(diào)用對于數(shù)據(jù)訪問來說是最昂貴的代價。
  • 由于InnoDB的聚集索引,覆蓋索引對于InnoDB來說十分有幫助。InnoDB的輔助索引在其葉子節(jié)點(diǎn)中保存了行的主鍵值。因此,輔助索引覆蓋查詢后可以避免進(jìn)行主鍵查詢。

在所有的場景中,最典型的就是相比查找數(shù)據(jù)行,只包含索引列的查詢的代價相當(dāng)?shù)?。需要注意的是,聚集索引并不是任意類型的索引。聚集索引必須存儲索引?shù)據(jù)列對應(yīng)的值。哈希,空間和全文索引并沒有存儲這些值,因此MySQL只能使用二叉樹去覆蓋查詢。而且,不同的存儲引擎實(shí)現(xiàn)覆蓋索引的方式不同,并且并不是全部的存儲引擎都支持覆蓋索引(例如Memory存儲引擎當(dāng)前就不支持)。

當(dāng)你驗(yàn)證查詢中索引使用了覆蓋索引時,使用Explain語句時,會在Extra列中看到“Using index”。例如,在store_goods表有一個(shop_id, goods_category_id1)的多列索引。MySQL可以在查詢返回?cái)?shù)據(jù)只有這兩列時使用索引:

EXPLAIN SELECT `goods_category_id1`,`shop_id` FROM `store_goods` WHERE 1

覆蓋索引查詢在某些情況會讓這樣的優(yōu)化失效。MySQL查詢優(yōu)化器在執(zhí)行查詢時會判斷索引是否覆蓋到。假設(shè)索引覆蓋了WHERE條件,但沒有覆蓋整個查詢。如果評估結(jié)果決定不走覆蓋索引,那么MySQL 5.5及以前的版本會直接獲取數(shù)據(jù)行,即便是不需要這些數(shù)據(jù),然后才會過濾掉。

讓我們看一下為什么這種情況會發(fā)生,然后如何重寫查詢以便解決這個問題。首先查詢是這樣的:

EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%'

這個時候的結(jié)果是不會走覆蓋索引,而是普通的索引,這是因?yàn)椋?/p>

  • 沒有索引覆蓋了查詢數(shù)據(jù)列,因?yàn)槲覀儚臄?shù)據(jù)表讀取了全部列并且沒有索引列覆蓋了全部列。理論上,MySQL還有一個快捷方式可以使用,那就是WHERE條件中使用了索引覆蓋的列,因此MySQL可以先使用這個索引找到對應(yīng)的actor,然后在檢查他們的title是否匹配,然后在讀取滿足條件的全部的數(shù)據(jù)行。
  • 對于早期的低版本的存儲引擎API(MySQL 5.5以前的版本)來說,MySQL無法在索引中使用LIKE操作,而只支持簡單的比較操作(=,IN,>=)。MySQL可以在索引中使用前綴匹配的LIKE查詢,這是因?yàn)樗梢詫⑺鼈冝D(zhuǎn)換為比較操作。但是前導(dǎo)通配符(也就是LIKE中前置的%)導(dǎo)致存儲引擎無法評估匹配條件。因此,MySQL會獲取行數(shù)據(jù)再比較,而不是索引的值。

有一種方式可以使用巧妙的組合索引和重寫查詢條件。我們可以將索引擴(kuò)展到(artist, title, prod_id),然后像下面那樣重寫查詢語句:

EXPLAIN SELECT * 
FROM products
	JOIN (
    SELECT prod_id
    FROM products
    WHERE actor='SEAN CARREY' AND TITLE LIKE '%APOLLO%'
 ) AS t1 ON (t1.prod_id=products.prod_id)

我們稱之為“遞延JOIN(deferred join)”,因?yàn)樗舆t了列的訪問。在第一階段的查詢中,當(dāng)它在子查詢中找到了匹配的行的過程中,MySQL使用了覆蓋索引。雖然在整個查詢中沒有覆蓋到,但總比沒有的好。

這種優(yōu)化的效果好壞取決于WHERE條件查找到了多少行數(shù)據(jù)。假設(shè)products表包含了上百萬行的數(shù)據(jù)??梢詫Ρ纫幌逻@兩種查詢的性能對比,總的數(shù)據(jù)為100萬行。

  • 第一種情況:有30000個products的actor是“SEAN CARREY”,其中20000個的title包含“APOLLO”;
  • 第二種情況:有30000個products的actor是“SEAN CARREY”,其中40個的title包含“APOLLO”;
  • 第三種情況:有50個products的actor是“SEAN CARREY”,其中10個的title包含“APOLLO”。

對比結(jié)果如下表。

數(shù)據(jù)集 原始查詢 優(yōu)化后查詢
第一種情況 5qps 5qps
第二種情況 7qps 35qps
第三種情況 2400qps 2000qps

結(jié)果的解釋如下:

  • 在第一種情況中,查詢返回了很大的結(jié)果集,因此看不到優(yōu)化效果。大部分時間花在了讀取和發(fā)送數(shù)據(jù)。
  • 在第二種情況中,使用覆蓋索引后子查詢過濾得到了一個小的結(jié)果集,這樣優(yōu)化的效果是性能提升了5倍。產(chǎn)生這種效果的原因是相比查出30000行的數(shù)據(jù)集,這里只需要讀取40行。
  • 第三種情況顯示了子查詢失效了。覆蓋索引過濾返回的結(jié)果集太少了,導(dǎo)致子查詢的代價比直接從數(shù)據(jù)表讀取數(shù)據(jù)還要高。

在大多數(shù)存儲引擎中,一個索引只能夠覆蓋訪問列是索引的一部分。然而,InnoDB實(shí)際上會做進(jìn)一步的優(yōu)化。想想InnoDB的的輔助索引在葉子節(jié)點(diǎn)中存儲了主鍵的值。這意味著InnoDB的輔助索引實(shí)際上有了額外的列幫助InnoDB使用覆蓋索引。 例如,sakila.actor表使用了InnoDB,然后在last_name有一個索引,因此這個索引能夠覆蓋或者主鍵actor_id的查詢——即便這個列并不是索引的一部分。

EXPLAIN SELECT actor_id, last_name
FROM sakila.actor WHERE last_name = 'HOPPER'

以上就是MySQL 覆蓋索引的優(yōu)點(diǎn)的詳細(xì)內(nèi)容,更多關(guān)于MySQL 覆蓋索引的資料請關(guān)注腳本之家其它相關(guān)文章!

相關(guān)文章

  • MySQL臨時表滿了/臨時表空間耗盡的解決方法

    MySQL臨時表滿了/臨時表空間耗盡的解決方法

    當(dāng)你收到“臨時表滿了”的警報時,通常意味著 MySQL 在處理查詢時創(chuàng)建的臨時表空間已經(jīng)耗盡,本文主要介紹了MySQL臨時表滿了/臨時表空間耗盡的解決方法,感興趣的可以了解一下
    2024-08-08
  • MySQL遠(yuǎn)程無法連接的一些常見原因總結(jié)

    MySQL遠(yuǎn)程無法連接的一些常見原因總結(jié)

    有的小伙伴發(fā)現(xiàn)自己的mysql無法正常連接遠(yuǎn)程服務(wù)器,下面這篇文章主要給大家介紹了關(guān)于MySQL遠(yuǎn)程無法連接的一些常見原因,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2022-09-09
  • php mysql insert into 結(jié)合詳解及實(shí)例代碼

    php mysql insert into 結(jié)合詳解及實(shí)例代碼

    在mysql中如果要保存數(shù)據(jù)到數(shù)據(jù)庫我們就會用到insert into命令了,下面我來給大家介紹mysql insert into幾種常用的使用方法,需要的朋友可以參考下
    2016-11-11
  • Mysql?安裝及my.ini的創(chuàng)建過程

    Mysql?安裝及my.ini的創(chuàng)建過程

    這篇文章主要介紹了Mysql?安裝及my.ini的創(chuàng)建過程,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2022-07-07
  • 安裝MySQL后include目錄下沒有找到libmysql.lib

    安裝MySQL后include目錄下沒有找到libmysql.lib

    安裝了MySQL后,在其安裝目錄下的include文件夾并沒有找到libmysql.lib,主要原因是在安裝MySQL的時候,沒有勾選develop component這一選項(xiàng)造成的
    2014-08-08
  • Linux環(huán)境下mysql5.7.13安裝教程

    Linux環(huán)境下mysql5.7.13安裝教程

    這篇文章主要為大家詳細(xì)介紹了Linux環(huán)境下mysql5.7.13安裝教程,感興趣的小伙伴們可以參考一下
    2016-07-07
  • 細(xì)說MySQL死鎖與日志二三事

    細(xì)說MySQL死鎖與日志二三事

    這篇文章主要和大家一起聊一聊MySQL死鎖與日志二三事,實(shí)際業(yè)務(wù)當(dāng)中如何快速的定位線上MySQL問題,修復(fù)異常?本文根據(jù)兩個實(shí)際case,分享下相關(guān)的經(jīng)驗(yàn)與方法,感興趣的小伙伴們可以參考一下
    2017-08-08
  • MySQL?Flink實(shí)時流處理的核心技術(shù)之窗口機(jī)制

    MySQL?Flink實(shí)時流處理的核心技術(shù)之窗口機(jī)制

    Flink是一款流處理框架,窗口機(jī)制是其核心技術(shù)之一。Flink的窗口機(jī)制可以將無限的數(shù)據(jù)流劃分為有限的窗口,并對窗口內(nèi)的數(shù)據(jù)進(jìn)行處理。Flink的窗口機(jī)制支持時間、計(jì)數(shù)、會話等多種窗口類型,并且可以在不同的窗口之間進(jìn)行流轉(zhuǎn)換和數(shù)據(jù)聚合,是實(shí)時流處理中非常重要的技術(shù)
    2023-05-05
  • 阿里云ESC 安裝 MYSQL8.0的教程

    阿里云ESC 安裝 MYSQL8.0的教程

    這篇文章主要介紹了阿里云ESC 安裝 MYSQL8.0的教程,本文給大家介紹的非常詳細(xì),具有一定的參考借鑒價值,需要的朋友可以參考下
    2019-12-12
  • MySQL之表碎片化的問題解決

    MySQL之表碎片化的問題解決

    MySQL數(shù)據(jù)庫的碎片是由于頻繁的增刪改查操作導(dǎo)致的數(shù)據(jù)塊不連續(xù)或不規(guī)則分布,本文主要介紹了MySQL之表碎片化的問題解決,具有一定的參考價值,感興趣的可以了解一下
    2024-08-08

最新評論