搜尋
首頁資料庫mysql教程MySQL回表查詢與索引覆寫的差異是什麼

回表查詢

InnoDB索引分為兩大類,一類是聚集索引(Clustered Index),一類是非聚集索引(Secondary Index)

聚集索引(叢集索引):葉子節點中存的是整行數據,找到索引也就找到了數據,索引即數據,表中行的物理順序與鍵值的邏輯(索引)順序相同,一個表只能包含一個聚集索引。因為索引(目錄)只能按照一種方法進行排序。

非聚集索引(普通索引、非聚集索引、二級索引):非聚集索引的btree葉子節點中儲存的是當行資料的PK(主鍵)。例如MYISAM透過key_buffer把索引先快取到記憶體中,當需要存取資料時(透過索引存取資料),在記憶體中直接搜尋索引,然後透過索引找到磁碟對應數據,這也就是為什麼索引不在key buffer命中時,速度慢的原因。

為什麼非主鍵索引結構葉子結點儲存的是主鍵值?

減少了出現行移動或資料頁分割時二級索引的維護工作(當資料需要更新的時候,二級索引不需要修改,只需要修改叢集索引,一個表只能有一個叢集索引,其他的都是二級索引,這樣只需要修改叢集索引就可以了,不需要重新建構二級索引)

當使用非聚集索引時,為了取得具體數據,我們需要透過主鍵返回到聚集索引並查詢數據。著就叫回表查詢。掃描了2次索引樹。所以效率相對較低。

索引覆寫

索引覆寫就是解決回表查詢的一種方案。見名知意,就是查詢的所有列都被所使用的索引列覆蓋(可以是單列索引也可以是聯合索引,通常是聯合索引,單列索引很難覆蓋查詢的所有列)。

因為索引中已經包含了要查詢的欄位的值,因此查詢的時候直接傳回索引中的欄位值就可以了,不需要再到表中查詢,避免了對主鍵索引的二次查詢,也就提高了查詢的效率。

id為聚集索引,name為非聚集索引:

select name, age from t where name = 'lcc';

就需要回表查詢

索引覆寫:

在SQL中只查詢name字段。這樣name的索引就覆蓋到了所有的查詢欄位。

select name  from t where name = 'lcc';

將name的索引修改為聯合索引(name, age ),之後還是執行select name, age from t where name = 'lcc'。這樣也覆蓋到了所有的查詢列。
因為覆寫索引必須要儲存索引的資料列值,而雜湊索引、空間索引和全文索引等都不儲存索引列值,因此只有使用B-Tree索引的資料可以做覆蓋索引。

進行索引覆寫查詢時,在explain(執行計畫)的Extra欄位可以看到【Using Index】的資訊。

索引覆寫的優點

  • 索引條目通常遠小於資料行的大小,因為覆寫索引只需要讀取索引,大幅減少了資料的存取量。

  • 索引是按照列值順序儲存的,對於IO密集的範圍查找會比隨機從磁碟讀取每一行資料的IO小得多。

  • 有些儲存引擎例如MyISAM在記憶體中只快取索引,資料則依賴作業系統來緩存,因此要存取資料的話需要一次系統調用,使用覆蓋索引則避免了這一點。

  • 針對InnoDB引擎下的資料庫表,覆寫索引因為InnoDB的叢集索引而非常實用。因為InnoDB的二級索引在葉子節點中保存了行的主鍵值,如果二級索引能夠覆蓋查詢,就避免了對主鍵索引的二次查詢。

哪些場景適合使用索引覆寫來最佳化SQL

  • #當不需要查詢整行記錄時;

#全表count查詢優化;
  • Limit分頁查詢;
  • 哪些情況下不要建立索引
  • 表格記錄太少

經常增刪改的表格或欄位(如使用者餘額)

#Where條件裡用不到的字段不創建索引

過濾性不好的不適合建索引(如性別)

索引下推
  • 索引下推優化是MySQL 5.6 引入的, 可以在索引遍歷過程中,對索引中包含的字段先做判斷,直接過濾掉不滿足條件的記錄,減少回表次數

    建立聯合索引:
  • KEY `username` (`name`,`age`) )
  • 執行:

    select * from user2 where name like 'j%' and age=99;

    上面的查詢sql符合索引的最左前綴原則,所以將會用到username 索引####### 5.5中上面這個SQL 的執行流程是這樣的:############首先MySQL 的server 層呼叫儲存引擎取得第一個以j 開頭的username。 ############儲存引擎找到username=‘j’ 的第一筆記錄後,在B Tree 的葉子結點中保存主鍵id,此時透過回表操作,去主鍵索引中找到該筆記錄的完整數據,並傳回給server 層。 ###
  • server 層拿到資料之後,判斷該筆記錄的age 是否為99,如果age=99,就把該筆記錄回傳給客戶端,如果age!=99,那就丟棄該記錄。

 5.6中上面這個SQL 的執行流程是這樣的:

  • MySQL 的server 層首先呼叫儲存引擎定位到第一個以j 開頭的username。

  • 找到記錄後,儲存引擎並不急著回表,而是繼續判斷這條記錄的age 是否等於99,如果age=99,再去回表,如果age不等於99,就不去回表了,直接繼續讀取下一筆記錄。

#

以上是MySQL回表查詢與索引覆寫的差異是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述
本文轉載於:亿速云。如有侵權,請聯絡admin@php.cn刪除
MySQL和其他SQL方言之間的語法有什麼區別?MySQL和其他SQL方言之間的語法有什麼區別?Apr 27, 2025 am 12:26 AM

mysqldiffersfromothersqldialectsinsyntaxforlimit,自動啟動,弦樂範圍,子征服和表面上分析。 1)MySqluessLipslimit,whilesqlserverusestopopandoraclesrontersrontsrontsrontsronnum.2)

什麼是mysql分區?什麼是mysql分區?Apr 27, 2025 am 12:23 AM

MySQL分區能提升性能和簡化維護。 1)通過按特定標準(如日期範圍)將大表分成小塊,2)物理上將數據分成獨立文件,3)查詢時MySQL可專注於相關分區,4)查詢優化器可跳過不相關分區,5)選擇合適的分區策略並定期維護是關鍵。

您如何在MySQL中授予和撤銷特權?您如何在MySQL中授予和撤銷特權?Apr 27, 2025 am 12:21 AM

在MySQL中,如何授予和撤銷權限? 1.使用GRANT語句授予權限,如GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host';2.使用REVOKE語句撤銷權限,如REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host',確保及時溝通權限變更。

說明InnoDB和Myisam存儲引擎之間的差異。說明InnoDB和Myisam存儲引擎之間的差異。Apr 27, 2025 am 12:20 AM

InnoDB適合需要事務支持和高並發性的應用,MyISAM適合讀多寫少的應用。 1.InnoDB支持事務和行級鎖,適用於電商和銀行系統。 2.MyISAM提供快速讀取和索引,適合博客和內容管理系統。

MySQL中有哪些不同類型的連接?MySQL中有哪些不同類型的連接?Apr 27, 2025 am 12:13 AM

MySQL中有四種主要的JOIN類型:INNERJOIN、LEFTJOIN、RIGHTJOIN和FULLOUTERJOIN。 1.INNERJOIN返回兩個表中符合JOIN條件的所有行。 2.LEFTJOIN返回左表中的所有行,即使右表中沒有匹配的行。 3.RIGHTJOIN與LEFTJOIN相反,返回右表中的所有行。 4.FULLOUTERJOIN返回兩個表中所有符合或不符合JOIN條件的行。

MySQL中有哪些不同的存儲引擎?MySQL中有哪些不同的存儲引擎?Apr 26, 2025 am 12:27 AM

mysqloffersvariousStorageengines,每個suitedfordferentusecases:1)InnodBisidealForapplicationsNeedingingAcidComplianCeanDhighConcurncurnency,supportingtransactionsancions and foreignkeys.2)myisamisbestforread-Heavy-Heavywyworks,lackingtransactionsactionsacupport.3)記憶

MySQL中有哪些常見的安全漏洞?MySQL中有哪些常見的安全漏洞?Apr 26, 2025 am 12:27 AM

MySQL中常見的安全漏洞包括SQL注入、弱密碼、權限配置不當和未更新的軟件。 1.SQL注入可以通過使用預處理語句防止。 2.弱密碼可以通過強制使用強密碼策略避免。 3.權限配置不當可以通過定期審查和調整用戶權限解決。 4.未更新的軟件可以通過定期檢查和更新MySQL版本來修補。

您如何確定MySQL中的慢速查詢?您如何確定MySQL中的慢速查詢?Apr 26, 2025 am 12:15 AM

在MySQL中識別慢查詢可以通過啟用慢查詢日誌並設置閾值來實現。 1.啟用慢查詢日誌並設置閾值。 2.查看和分析慢查詢日誌文件,使用工具如mysqldumpslow或pt-query-digest進行深入分析。 3.優化慢查詢可以通過索引優化、查詢重寫和避免使用SELECT*來實現。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser是一個安全的瀏覽器環境,安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問,並防止學生使用未經授權的資源。

VSCode Windows 64位元 下載

VSCode Windows 64位元 下載

微軟推出的免費、功能強大的一款IDE編輯器

WebStorm Mac版

WebStorm Mac版

好用的JavaScript開發工具

PhpStorm Mac 版本

PhpStorm Mac 版本

最新(2018.2.1 )專業的PHP整合開發工具