回表查詢
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分頁查詢;
- 哪些情況下不要建立索引
- 表格記錄太少
- 索引下推優化是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中文網其他相關文章!

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

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

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

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

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

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

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

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


熱AI工具

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

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

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

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

熱門文章

熱工具

記事本++7.3.1
好用且免費的程式碼編輯器

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

VSCode Windows 64位元 下載
微軟推出的免費、功能強大的一款IDE編輯器

WebStorm Mac版
好用的JavaScript開發工具

PhpStorm Mac 版本
最新(2018.2.1 )專業的PHP整合開發工具