個(gè)體戶 做網(wǎng)站個(gè)人網(wǎng)頁(yè)生成器
??博主介紹??：
?又是一天沒白過(guò)，我是奈斯，DBA一名?
???擅長(zhǎng)Oracle、MySQL、SQLserver、Linux，也在積極的擴(kuò)展IT方向的其他知識(shí)面???
??????大佬們都喜歡靜靜的看文章，并且也會(huì)默默的點(diǎn)贊收藏加關(guān)注??????
? ? SQL語(yǔ)句的執(zhí)行計(jì)劃復(fù)雜多變，每一次的查詢都可能帶來(lái)截然不同的執(zhí)行策略。為了真正掌握其內(nèi)在的工作機(jī)制，我們必須深入生產(chǎn)環(huán)境，通過(guò)反復(fù)的實(shí)踐與觀察來(lái)積累經(jīng)驗(yàn)。今天將聚焦于explain Extra輸出列中的兩個(gè)關(guān)鍵要素：“Using index”和“Using where”，帶大家一起看一下Using index和Using where對(duì)查詢計(jì)劃的影響，當(dāng)然今天的介紹只是眾多Extra輸出列中其中的兩個(gè)。希望通過(guò)這篇文章，能夠幫助大家更好地理解SQL查詢的執(zhí)行過(guò)程，以及如何更有效地優(yōu)化查詢性能。
? ? 今天作為統(tǒng)計(jì)信息和執(zhí)行計(jì)劃的最后一篇，讓我們回顧一下前兩篇的介紹內(nèi)容：
第一篇：持久化和非持久化統(tǒng)計(jì)信息介紹
第二篇：執(zhí)行計(jì)劃介紹
第三篇：執(zhí)行計(jì)劃之覆蓋索引Using index和條件過(guò)濾Using where詳細(xì)介紹（當(dāng)前篇）
? ? ? ? ? ??
目錄
一、Using index：使用覆蓋索引。屬于積極現(xiàn)象，一般不需要過(guò)多再去干預(yù)。
案例：不使用覆蓋索引的情況和使用覆蓋索引的情況
不使用覆蓋索引的情況：
?使用覆蓋索引的情況：
二、Using where：條件中的字段沒有使用索引，或者部分字段沒有使用索引。屬于消極現(xiàn)象，進(jìn)行分析干預(yù)。
案例：使用到Using where的情況和不使用到Using where的情況
使用到Using where的情況：
不使用到Using where的情況：
? ? ? ? ??
一、Using index：使用覆蓋索引。屬于積極現(xiàn)象，一般不需要過(guò)多再去干預(yù)。
注意：是使用了覆蓋索引，和使用索引不是一個(gè)概念，有沒有使用索引通過(guò)key輸出列確定。
? ? ? ? ??
官方文檔介紹：MySQL :: MySQL 8.0 Reference Manual :: 10.8.2 EXPLAIN Output Format
? ? 使用索引（JSON屬性：Using_index）
? ? 只使用索引樹中的信息從表中檢索列信息，而不必進(jìn)行額外的查找來(lái)讀取實(shí)際行。當(dāng)查詢僅使用作為單個(gè)索引一部分的列時(shí)，可以使用此策略。
? ? 對(duì)于具有用戶定義的聚集索引的InnoDB表，即使Extra列中沒有Using索引，也可以使用該索引。如果類型為索引，鍵為PRIMARY，則會(huì)出現(xiàn)這種情況。
? ? 顯示了EXPLAIN FORMAT=TRADINAL和EXPLAIN ORMAT=JSON所使用的任何覆蓋索引的信息。從MySQL 8.0.27開始，它也顯示為EXPLAIN FORMAT=TREE。
? ? ? ? ? ?
覆蓋索引（Covering Index）：
? ? 如果一個(gè)索引包含了查詢所需的所有字段，則稱該索引為覆蓋索引。當(dāng)MySQL查詢使用這個(gè)索引時(shí)，它可以直接從索引中獲取所有需要的數(shù)據(jù)，而無(wú)需再回表（即返回原表）查找。
? ? 在查詢執(zhí)行計(jì)劃中，當(dāng)出現(xiàn)“Using index”時(shí)，意味著MySQL已經(jīng)使用了覆蓋索引（Covering Index）來(lái)檢索數(shù)據(jù)。這是一個(gè)優(yōu)化提示，表明查詢可以僅通過(guò)索引來(lái)滿足，而無(wú)需訪問(wèn)實(shí)際的數(shù)據(jù)表。
? ? 出現(xiàn)覆蓋索引的好處：1、減少I/O操作：因?yàn)镸ySQL可以僅通過(guò)索引獲取數(shù)據(jù)，所以它不需要讀取整個(gè)表的數(shù)據(jù)，這通常意味著更少的磁盤I/O操作。
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2、提高查詢速度：由于減少了I/O操作和可能的表查找，查詢通常會(huì)更快。
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 3、減少內(nèi)存使用：由于不需要從表中讀取額外的數(shù)據(jù)，所以內(nèi)存使用可能會(huì)更低。
? ? 需要注意的是，雖然“Using index”是一個(gè)優(yōu)化提示，但在某些情況下，它可能不是最優(yōu)的。例如，如果查詢中的某些條件不能有效地使用索引，或者索引本身非常大，那么全表掃描可能更快。
? ? ? ? ? ? ?
回表（Back to Table）：
? ? "回表"是指當(dāng)查詢語(yǔ)句需要獲取的數(shù)據(jù)不僅僅在索引中，還需要回到主表中進(jìn)行二次查詢獲取的過(guò)程。
1、回表的影響：回表操作會(huì)增加額外的I/O操作和訪問(wèn)時(shí)間，影響查詢的性能。因?yàn)榛乇硇枰~外的查找步驟，所以在高并發(fā)的情況下，回表操作會(huì)導(dǎo)致數(shù)據(jù)庫(kù)的負(fù)載增加，可能會(huì)成為性能瓶頸。
2、如何避免回表：為了避免回表操作，可以使用覆蓋索引（Covering Index）的方式。覆蓋索引是指創(chuàng)建一個(gè)包含了查詢所需的所有列的索引，這樣就可以直接從索引中獲取所需的數(shù)據(jù)，而無(wú)需回到表中查找。使用覆蓋索引可以減少I/O操作和提高查詢性能。
3、如何優(yōu)化回表：如果無(wú)法避免回表操作，可以通過(guò)以下方法進(jìn)行優(yōu)化：
? ? ? ? ? ? ? ? ? ?一、優(yōu)化查詢語(yǔ)句：盡量減少回表操作的次數(shù)，可以通過(guò)合理的查詢條件、索引設(shè)計(jì)和查詢優(yōu)化等方式來(lái)減少回表操作。
? ? ? ? ? ? ? ? ? ?二、調(diào)整表結(jié)構(gòu)：如果回表操作非常頻繁，可以考慮調(diào)整表結(jié)構(gòu)，將需要查詢的列放在索引中，或者使用聚簇索引來(lái)減少回表操作。
? ? ? ? ? ? ? ? ? ?三、使用緩存：如果查詢的數(shù)據(jù)具有一定的重復(fù)性，可以考慮使用緩存來(lái)減少回表操作。
? ? ? ? ?
覆蓋索引觸發(fā)的條件：
1、沒有where條件，select查詢的返回列包含在索引列中：這意味著查詢的結(jié)果可以直接從索引中獲取，而無(wú)需訪問(wèn)實(shí)際的數(shù)據(jù)表。
2、有where條件，where和select都要包含索引列或復(fù)合索引：這有助于數(shù)據(jù)庫(kù)系統(tǒng)更有效地利用索引進(jìn)行查找和過(guò)濾。
3、查詢結(jié)果的總字段長(zhǎng)度可以接受：如果查詢結(jié)果的總字段長(zhǎng)度過(guò)大，可能不適合使用覆蓋索引，因?yàn)檫@可能會(huì)增加系統(tǒng)的開銷。
? ? 當(dāng)滿足上述條件時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)可能會(huì)選擇使用覆蓋索引來(lái)執(zhí)行查詢，從而提高查詢的效率。不過(guò)，請(qǐng)注意，索引的使用和優(yōu)化是一個(gè)復(fù)雜的過(guò)程，具體是否使用覆蓋索引還取決于數(shù)據(jù)庫(kù)管理系統(tǒng)的具體實(shí)現(xiàn)和查詢的具體情況。
? ? ? ? ??
案例：不使用覆蓋索引的情況和使用覆蓋索引的情況
mysql> show index from tb_200w; ---表現(xiàn)在無(wú)索引
mysql> create index idx_tb_200w_ina on tb_200w(id,name,age); ---創(chuàng)建索引? ? ? ? ??
不使用覆蓋索引的情況：
1）沒有where條件，select查詢的返回列沒有在索引列中：
mysql> explain select * from tb_200w; mysql> explain select sex from tb_200w; mysql> explain select age,sex from tb_200w; ---select中包含了索引列和沒有索引的列，這種情況肯定是不會(huì)使用到覆蓋索引的，因?yàn)椴樵冋Z(yǔ)句需要獲取的數(shù)據(jù)不僅僅在索引中，還需要回到主表中進(jìn)行二次查詢獲取的過(guò)程。
? ? ? ? ? ??
2）有where條件，where或select沒有包含索引列或復(fù)合索引：
mysql> explain select * from tb_200w where id=1; mysql> explain select sex from tb_200w where id=1; mysql> explain select age,sex from tb_200w where id=1; ---where包含了索引列，但是select中包含了索引列和沒有索引的列，這種情況肯定是不會(huì)使用到覆蓋索引的，因?yàn)椴樵冋Z(yǔ)句需要獲取的數(shù)據(jù)不僅僅在索引中，還需要回到主表中進(jìn)行二次查詢獲取的過(guò)程。
? ??
? ? ? ? ??
?使用覆蓋索引的情況：
1）沒有where條件，select查詢的返回列包含在索引列中：?
mysql> explain select id from tb_200w; mysql> explain select name from tb_200w; mysql> explain select age from tb_200w; mysql> explain select name,age from tb_200w; mysql> explain select age,id from tb_200w; ---都會(huì)使用到覆蓋索引，因?yàn)閟elect查詢的返回列包含在索引列中了
? ? ? ? ?
2）有where條件，where和select都要包含索引列或復(fù)合索引：
mysql> explain select id from tb_200w where id=1; mysql> explain select name from tb_200w where id=1; mysql> explain select age from tb_200w where id=1; mysql> explain select name,age from tb_200w where id=1; mysql> explain select age,id from tb_200w where id=1; ---都會(huì)使用到覆蓋索引，因?yàn)閣here和select都包含索引列或復(fù)合索引了
? ? ? ? ?
? ??
二、Using where：條件中的字段沒有使用索引，或者部分字段沒有使用索引。屬于消極現(xiàn)象，進(jìn)行分析干預(yù)。
? ? ? ? ?
官方文檔介紹：MySQL :: MySQL 8.0 Reference Manual :: 10.8.2 EXPLAIN Output Format
? ? 使用where（JSON屬性：attached_condition）
? ? WHERE子句用于限制哪些行與下一個(gè)表匹配或發(fā)送到客戶端。除非您特別打算從表中提取或檢查所有行，否則如果Extra值不是Using where，并且表聯(lián)接類型是all或index，則查詢中可能會(huì)出現(xiàn)錯(cuò)誤。
? ? ? ? ? ??
?Using where介紹：
? ? Using where表示查詢?cè)跈z索到行之后，需要使用WHERE子句中的條件對(duì)這些行進(jìn)行過(guò)濾。這并不一定意味著查詢沒有使用索引，而是意味著即使有索引被使用，索引本身并不能完全滿足WHERE子句中的所有條件，因此MySQL需要讀取實(shí)際的行數(shù)據(jù)，并在服務(wù)器層面對(duì)這些行進(jìn)行額外的過(guò)濾。
? ? ? ? ? ??
Using where觸發(fā)的條件：
1、復(fù)合索引與部分條件匹配：如果你有一個(gè)復(fù)合索引（例如 (a, b, c)），但你的WHERE子句只涉及索引的前兩個(gè)字段（例如 WHERE a = 1 AND b = 2），那么MySQL可以使用這個(gè)復(fù)合索引來(lái)快速找到匹配的行。但是，如果WHERE子句還包含第三個(gè)字段的條件（例如 c > 3），則即使索引被使用，Using where 也會(huì)出現(xiàn)在執(zhí)行計(jì)劃中，因?yàn)樾枰獙?duì)找到的行進(jìn)行額外的過(guò)濾。
2、沒有合適的索引：如果查詢的WHERE子句條件沒有對(duì)應(yīng)的索引，或者索引的選擇性不高（即索引中的不同值不多），MySQL可能會(huì)選擇全表掃描而不是使用索引。在這種情況下，Using where 會(huì)出現(xiàn)在執(zhí)行計(jì)劃中，因?yàn)樗袡z索到的行都需要進(jìn)行過(guò)濾。
? ? ? ? ? ?
避免Using where的觸發(fā)：
? ? Using where 的出現(xiàn)并不一定意味著查詢性能不佳。實(shí)際上，在很多情況下，即使需要額外的過(guò)濾，使用索引仍然比全表掃描更快。然而，如果發(fā)現(xiàn)Using where經(jīng)常出現(xiàn)并且查詢性能不佳，需要考慮：
1、添加或優(yōu)化索引：確保你的查詢條件有合適的索引支持。
2、調(diào)整查詢：嘗試重寫查詢或分解復(fù)雜查詢?yōu)槎鄠€(gè)簡(jiǎn)單查詢，以便更好地利用索引。
3、分析數(shù)據(jù)和索引統(tǒng)計(jì)信息：使用ANALYZE TABLE命令更新表的統(tǒng)計(jì)信息，以便MySQL優(yōu)化器能夠做出更好的決策。
? ? ? ? ? ? ? ?
案例：使用到Using where的情況和不使用到Using where的情況
mysql> show index from tb_200w; ---表現(xiàn)在無(wú)索引
mysql> create index idx_tb_200w_id on tb_200w(id); ---創(chuàng)建單列索引 mysql> create index idx_tb_200w_ina on tb_200w(name,age,cardid); ---創(chuàng)建復(fù)合索引? ? ? ??
使用到Using where的情況：
mysql> explain select * from tb_200w where tel=13604981449; ---直接查詢普通列，而不引用索引列 mysql> explain select * from tb_200w where age=38; ---查詢的列在復(fù)合索引中，但是并沒有引用前置列(name列)，所以不會(huì)使用到索引 mysql> explain select * from tb_200w where cardid=1000110123; ---查詢的列在復(fù)合索引中，但是并沒有引用前置列(name列)，所以不會(huì)使用到索引
? ? ? ? ? ? ? ?
不使用到Using where的情況：
mysql> explain select * from tb_200w where id=1000; ---使用單列索引 mysql> explain select * from tb_200w where name='n1000'; ---使用復(fù)合索引，引用了前置列(name列) mysql> explain select * from tb_200w where name='n1000' and age=38 and cardid='1000110123'; ---使用復(fù)合索引，引用了前置列(name列)
? ? 關(guān)于統(tǒng)計(jì)信息和執(zhí)行計(jì)劃的內(nèi)容全部就到這里，執(zhí)行計(jì)劃所涉及的內(nèi)容太過(guò)廣泛，我所介紹的內(nèi)容也只是冰山一角，以后就靠大家自己去學(xué)習(xí)和實(shí)踐。當(dāng)然我也會(huì)繼續(xù)在CSDN上分享關(guān)于執(zhí)行計(jì)劃的內(nèi)容。