資料庫優化器什麼情況下放棄索引

1. Oracle資料庫強制索引

當where子句對某一列使用函數時 除非利用這個簡單的技術強制索引 否則Oracle優化器不能在查詢中使用索引

通常情況下 如果在WHERE子句中不使用諸如UPPER REPLACE 或SUBSTRD等函數 就不能對指定列建立特定的條件 但如果使用了這些函數 則會出現一粗鬧個問題 這些函數會阻礙Oracle優化器對列使用索引 因而與採用索引的情況相比較 查詢會花費更多的時間

慶幸的是 如果在使用函數的這些列中包含了字元型數據 可以用這樣一種方法修改查詢語句 以達到強制性使用索引 更有效地運行查詢 這篇文章介紹了涉及的技術 並說明了在兩種典型情況下怎樣實現

大小寫混合情況

在討論由於函數修改了列的內容 如何強制使用索引前 讓我們首先看看為什麼Oracle優化器在這種情況下不能使用索引 假定我們要搜尋包含了大小寫混合的數據 如在表 中ADDRESS表的NAME列 因為數據是用戶輸入的 我們無法使用已經統一改為大寫的數據 為了找到每一個名為john的地址 我們使用包含了UPPER子句的查詢語句 如下所示

SQL> select address from address where upper(name) like JOHN ;

在運行這個查詢語句前 如果我們運行了命令 set autotrace on 將會得到下列結果 其中包含了執行過程

ADDRESS cleveland row selected Execution Plan SELECT STATEMENT TABLE ACCESS FULL ADDRESS

可以看到 在這種情況下 Oracle優化器對ADDRESS 表作了一次完整的掃描 而沒有使用NAME 列的索引 這是因為索引是根據列中數據的實際值建立的 而UPPER 函數已經將字元轉換成大寫 即修改了這些值 因此該查詢不能使用這列的索引 優化器不能與索引項比較 JOHN 沒有索引項對應於 JOHN 只有 john

值得慶幸的是 如果在這種情況下想要強制使用索引 有一種簡便的方法 只要在WHERE 子句中增加一個或多個特定友凳扮的條件 用於測試索引值 並減少需要掃描的行 但這並沒有修改原來SOL 編碼中的條件 以下列查詢語句為例

SQL> select address from address where upper(name) like JO% AND (name like J% or name like j% );

使用這種查詢語句（已設置AUTOTRACE） 可得到下列結果

ADDRESS cleveland row selected Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I

現在 優化器為WHERE 子句中AND 聯結的兩個語句中每一個語句確定的范圍進行掃描 第二個語句沒有引用函數 因而使用了索引 在兩個范圍掃描後 將運行結果合並

在這個例子中 如果資料庫有成百上千行 可以用下列方法擴充WHERE 子句 進一步縮小掃描范圍

select address from address where upper(name) like JOHN AND (name like JO% or name like jo% or name like Jo or name like jO );

得到的結果與以前相同 但是 其執行過程如好灶下所示 表明有 個掃描范圍

Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I

如果試圖進一步提高查詢速度 我們可以在特定的 name like 條件中指明 個或更多的字元 然而 這樣做會使得WHERE子句十分笨重 因為需要大小寫字元所有可能的組合 joh Joh jOh joH等等 除此之外 指定一個或兩個字元已足以加快查詢的運行速度了

現在讓我們看看 當我們引用不同的函數時 怎樣運用這個基本技術

使用REPLACE的情況

正如名字不總是以大寫輸入一樣 電話號碼也會以許多格式出現 如 （ ） 等等

如果在列名為 PHONE_NUMBER中搜尋上述號碼時 可能需要使用函數REPLACE以保證統一的格式 如果在PHONE_NUMBER列中只包含空格 連字元和數字 where 子句可以如下所示

WHERE replace(replace(phone_number ) ) =

WHERE子句兩次使用REPLACE 函數去掉了連字元和空格 保證了電話號碼是簡單的數字串 然而 該函數阻止了優化器在該列使用索引 因此 我們按如下方法修改WHERE子句 以強制執行索引

WHERE replace(replace(phone_number ) ) = AND phone_number like %

如果我們知道數據中可能包含圓括弧 WHERE 子句會稍微復雜一點 我們可以再增加REPLACE 函數（去掉圓括弧 連字元和空格） 按如下所示擴充增加的條件

WHERE replace(replace(replace(replace(phone_number ) ) ( ) ) ) = AND (phone number like % or phone_number like ( % )

該例強調了巧妙地選用WHERE 子句條件的重要性 而且 這些條件不會改變查詢結果 你的選擇應基於完全了解該列中存在的信息類型 在該例中 我們需要知道 PHONE_NUMBER 數據中存在幾種不同的格式 這樣 我們能夠修改WHERE 子句而不會影響查詢結果

正確的條件 lishixin/Article/program/Oracle/201311/18519

2. 分析SQL執行過程中，哪些SQL條件會走索引

這樣回答你，以下幾種情況sql中索引不會被用到
1、查詢謂詞沒有使用索引的主要邊界,換句話說就是select *，可能會導致不走索引。
比如，你查詢的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一個包含Y值的組合索引，但是優化器會認為需要一行行的掃描會更有效，這個時候，優化器可能會選擇TABLE ACCESS FULL，但是如果換成了SELECT Y FROM T WHERE Y = XXX，優化器會直接去索引中找到Y的值，因為從B樹中就可以找到相應的值。

2、單鍵值的b樹索引列上存在null值，導致COUNT(*)不能走索引。
如果在B樹索引中有一個空值，那麼查詢諸如SELECT COUNT(*) FROM T 的時候，因為HASHSET中不能存儲空值的，所以優化器不會走索引，有兩種方式可以讓索引有效，一種是SELECT COUNT(*) FROM T WHERE XXX IS NOT NULL或者把這個列的屬性改為not null (不能為空)。

3、索引列上有函數運算，導致不走索引
如果在T表上有一個索引Y，但是你的查詢語句是這樣子SELECT * FROM T WHERE FUN(Y) = XXX。這個時候索引也不會被用到，因為你要查詢的列中所有的行都需要被計算一遍，因此，如果要讓這種sql語句的效率提高的話，在這個表上建立一個基於函數的索引，比如CREATE INDEX IDX FUNT ON T(FUN(Y));這種方式，等於Oracle會建立一個存儲所有函數計算結果的值，再進行查詢的時候就不需要進行計算了，因為很多函數存在不同返回值，因此必須標明這個函數是有固定返知皮禪回值的。

4、隱式轉換導致不走索引。
索引不適用於隱式轉換的情況，比如你的SELECT * FROM T WHERE Y = 5 在Y上面有一個索引，但是Y列是VARCHAR2的，那麼Oracle會將上面的5進行一個隱式的轉換，SELECT * FROM T WHERE TO_NUMBER(Y) = 5,這個時候也是有可能用不到索引的。

5、表的資料庫小或者需要選擇大部分數據，不走索引
在Oracle的初始化參數中，有一個參數是一次讀取的數據塊的數目，比如你的表只有幾個數據塊大小，而且可以被Oracle一次性抓取，那麼就沒有使用索引的必要了，因為抓取索引還需要去根據rowid從數據塊中獲取相應的元素值，因此在表特別小的情況下，索引沒有用到是情理當中的事搭塵情。
6、cbo優化器下統計信息不準確，導致不走索引
很長時間沒有做表分析，或者重新收集表狀態信息了，在數據字典中，表的統計信息是不準確的，這個情況下，可能會使用錯誤的索引，這個效率可能也是比較低的。
7、！=或者<>(不等於），可能導致不走索引，也可能走 INDEX FAST FULL SCAN
例如select id from test where id<>100
8、表欄位的屬性導致不走索引，字元型的索引列會導致優化器認為需要掃描索引大握數部分數據且聚簇因子很大，最終導致棄用索引掃描而改用全表掃描方式，
由於字元型和數值型的在insert的時候排序不同，字元類型導致了聚簇因子很大，原因是插入順序與排序順序不同。詳細點說，就是按照數字類型插入（1..3200000），按字元類型（'1'...'32000000'）t排序，在對字元類型使用大於運算符時，會導致優化器認為需要掃描索引大部分數據且聚簇因子很大，最終導致棄用索引掃描而改用全表掃描方式。

3. oracle索引為什麼會選不是最合適的索引

Oracle選擇索引的過程是由查詢優化器來完成的，其目標鄭拍是盡可能地優化查詢性能，最終選擇最合適的索引。但是有時候，優化器也會選擇不是最合適的索引，這可能是由以下原因造成的：

1. 統計信息不準確：查詢優化器在選擇索引時需要依賴數據表的統計信息，包括行數、列的基數和直方圖等。如果這些統計信息不準確或者過時，就會導致查詢優化器選擇不合適的索引。

2. 索引失效：如果查詢中含有不等於（<>）或者非（NOT）操作符，那麼索引將無法使用。此時，查詢優化器可能會選擇其他索引或全表掃描。

3. 索引使用成本高：有時候，一個索引雖然能夠幫助查詢優化器加速查詢速度，但是索引本身的使用成本可能很高。例如，在某些情況下，對索引進行大量的隨機訪問可能比執行全表掃描更為低效。

4. 數據分布均勻：當表的某個列的值分布比較均勻時，使用索引可能不如全表掃描更有效率。因為查詢優化器前大需要在索引和表之間來回切換，消耗了額外的資源。

在實際應用中，優化查詢性能是一個復慧叢豎雜的過程，需要仔細考慮多個因素。因此，在設計索引時，需要仔細評估不同的因素，以確定最佳的索引方案。

4. mysql索引問題

1.首選資料庫都會有自動優化查詢計劃的能力，在語句一中，明顯對seq進行了排序，而is_need_udate用in進行毀告范圍查詢，使用index2，開銷就會小很多，但是語句二中is_need_update沒有這個了，所以才會使用index1.
2.所以建立的原則
2.1根據對應表查詢頻率最高的屬顫余鬧性建立索引
2.2為經常需要排序，分組的欄位茄罩建立索引
2.3盡量使用數據量少的索引
建議詳細的使用方法看看書吧，資料庫的優化是一門大學問，值得好好研究的

5. 資料庫的多表大數據查詢應如何優化

1.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：x0dx0aselect id from t where num is nullx0dx0a可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：x0dx0aselect id from t where num=0x0dx0a2.應盡量避免在 where 子句顫洞中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。優化器將無法通過索引來確定將要命中的行數,因此需要搜索該表的所有行。x0dx0a3.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：x0dx0aselect id from t where num=10 or num=20x0dx0a可以這樣查詢：x0dx0aselect id from t where num=10x0dx0aunion allx0dx0aselect id from t where num=20x0dx0a4.in 和 not in 也要慎用，因為IN會使系統無法使用索引,而只能直接搜索表中的數據。如：x0dx0aselect id from t where num in(1,2,3)x0dx0a對於連續的數值，能用 between 就不要用 in 了：x0dx0aselect id from t where num between 1 and 3x0dx0a5.盡量避免在索引過的字元數據中，使用非打頭字母搜索。這也使得引擎無法利用索引。 x0dx0a見如下例子： x0dx0aSELECT * FROM T1 WHERE NAME LIKE 『%L%』 x0dx0aSELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=』L』 x0dx0aSELECT * FROM T1 WHERE NAME LIKE 『L%』 x0dx0a即使NAME欄位建有索引，前兩個查詢依然無法利用春嘩索引完成加快操作，引擎不得不對全表所有數據逐條操作來完成任務。而第三個查詢能夠使用索引來加快操作。x0dx0a6.必要時強制查詢優化器使用某個索引，如在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：x0dx0aselect id from t where num=@numx0dx0a可以改為強制查詢使用索引：x0dx0aselect id from t with(index(索引名)) where num=@numx0dx0a7.應盡量避免在 where 子句中對欄位進行表達式操扒洞行作，這將導致引擎放棄使用索引而進行全表掃描。如：x0dx0aSELECT * FROM T1 WHERE F1/2=100 x0dx0a應改為: x0dx0aSELECT * FROM T1 WHERE F1=100*2x0dx0aSELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=』5378』 x0dx0a應改為: x0dx0aSELECT * FROM RECORD WHERE CARD_NO LIKE 『5378%』x0dx0aSELECT member_number, first_name, last_name FROM members x0dx0aWHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21 x0dx0a應改為: x0dx0aSELECT member_number, first_name, last_name FROM members x0dx0aWHERE dateofbirth < DATEADD(yy,-21,GETDATE()) x0dx0a即：任何對列的操作都將導致表掃描，它包括資料庫函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊。x0dx0a8.應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：x0dx0aselect id from t where substring(name,1,3)='abc'--name以abc開頭的idx0dx0aselect id from t where datediff(day,createdate,񟭅-11-30')=0--『2005-11-30』生成的idx0dx0a應改為:x0dx0aselect id from t where name like 'abc%'x0dx0aselect id from t where createdate>=񟭅-11-30' and createdate<񟭅-12-1'x0dx0a9.不要在 where 子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。x0dx0a10.在使用索引欄位作為條件時，如果該索引是復合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應盡可能的讓欄位順序與索引順序相一致。x0dx0a11.很多時候用 exists是一個好的選擇：x0dx0aelect num from a where num in(select num from b)x0dx0a用下面的語句替換：x0dx0aselect num from a where exists(select 1 from b where num=a.num)x0dx0aSELECT SUM(T1.C1)FROM T1 WHERE( x0dx0a(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0) x0dx0aSELECT SUM(T1.C1) FROM T1WHERE EXISTS( x0dx0aSELECT * FROM T2 WHERE T2.C2=T1.C2) x0dx0a兩者產生相同的結果，但是後者的效率顯然要高於前者。因為後者不會產生大量鎖定的表掃描或是索引掃描。

6. 在SQL語句的where子句中對存在索引的列使用函數時，為什麼Oracle優化器會忽略掉這些索引

where子畢絕句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。
因為SQL只有在運行時才會解析局部變數，但優化程序不能將塵芹訪問計劃的選擇推遲到運行時；它必須在編手兄姿譯時進行選擇。然而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。

7. SQL優化萬能公式：5 大步驟 + 10 個案例

在應用開發的早期，數據量兆尺鍵少，開發人員開發功能時更重視功能上的實現，隨著生產數據的增長，很多SQL語句開始暴露出性能問題，對生產的影響也越來越大，困鏈有時可能這些有問題的SQL就是整個系統性能的瓶頸。

1、通過慢查日誌等定位那些執行效率較低的SQL語句

2、explain 分析SQL的執行計劃

type由上至下，效率越來越高

Extra

3、show profile 分析

了解SQL執行的線程的狀態及消耗的時間。默認是關閉的，開啟語句「set profiling = 1;」

4、trace

trace分析優化器如何選擇執行計劃，通過trace文件能夠進一步了解為什麼優惠券選擇A執行計劃而不選擇B執行計劃。

5、確定問題並採用相應的措施

案例1、最左匹配

索引

SQL語句

查詢匹配從左往右匹配，要使用order_no走索引，必須查詢條件攜帶shop_id或者索引( shop_id , order_no )調換前後順序

案例2、隱式轉換

索引

SQL語句

隱式轉換相當於在索引上做運算，會讓索引失效。mobile是字元類型，使用了數字，應該使用字元串匹配，否則MySQL會用到隱式替換，導致索引失效。

案例3、大分頁

索引

SQL語句

對於大分頁的場景，可以優先讓產品優化需求，如果沒有優化的，有如下兩種優化方式， 一種是把上一次的最後一條數據，也即上面的c傳過來，然後做「c < xxx」處理，但是這種一般需要改介面協議，並不一定可行。另一種是採用延遲關聯的方式進行處理，減少SQL回表，但是要記得索引需要完全覆蓋才有效果，SQL改動如下

案例4、in + order by

索引

SQL語句

in查詢在MySQL底層是族巧通過n*m的方式去搜索，類似union，但是效率比union高。in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過將in包含的數值，一條條去查詢獲取元組數的，因此這個計算過程會比較的慢，所以MySQL設置了個臨界值(eq_range_index_pe_limit)，5.6之後超過這個臨界值後該列的cost就不參與計算了。因此會導致執行計劃選擇不準確。默認是200，即in條件超過了200個數據，會導致in的代價計算存在問題，可能會導致Mysql選擇的索引不準確。

處理方式，可以( order_status , created_at )互換前後順序，並且調整SQL為延遲關聯。

案例5、范圍查詢阻斷，後續欄位不能走索引

索引

SQL語句

范圍查詢還有「IN、between」

案例6、不等於、不包含不能用到索引的快速搜索。（可以用到ICP）

在索引上，避免使用NOT、!=、>、!、NOT EXISTS、NOT IN、NOT LIKE等

案例7、優化器選擇不使用索引的情況

如果要求訪問的數據量很小，則優化器還是會選擇輔助索引，但是當訪問的數據占整個表中數據的蠻大一部分時（一般是20%左右），優化器會選擇通過聚集索引來查找數據。

查詢出所有未支付的訂單，一般這種訂單是很少的，即使建了索引，也沒法使用索引。

案例8、復雜查詢

如果是統計某些數據，可能改用數倉進行解決；如果是業務上就有那麼復雜的查詢，可能就不建議繼續走SQL了，而是採用其他的方式進行解決，比如使用ES等進行解決。

案例9、asc和desc混用

desc 和asc混用時會導致索引失效

案例10、大數據

對於推送業務的數據存儲，可能數據量會很大，如果在方案的選擇上，最終選擇存儲在MySQL上，並且做7天等有效期的保存。那麼需要注意，頻繁的清理數據，會照成數據碎片，需要聯系DBA進行數據碎片處理。

8. 技術分享 | 為什麼 SELECT 查詢選擇全表掃描，而不走索引

SQL的執行成本（cost）是 MySQL 優化器選擇 SQL 執行計劃時一個重要考量因素。當優化器認為使用索引的成本高於全表掃描的時候，優化鬧雹器將會選擇全表掃描，而不是使用索引。

下面通過一個實驗來說明。

如下結構的一張表，表中約有104w行數據：

查詢1，並未用到ct_index(create_time)索引:

而查詢2，則用到了ct_index(create_time)索引:

這里使用optimizer trace工具，觀察MySQL對SQL的優化處理過程：

獲得關於此SQL的詳細優化器處理信息：

通過逐行閱讀，發現優化器在join_optimization（SQL優化階段）部分的rows_estimation內容里：

通過觀察優化器的信息，不難發現，使用索引掃描行數約52w行，而全表掃描約為104w行。為什麼優化器反寬彎猛而認為使用索引慎橋的成本比全表掃描還高呢？

因為當ct_index(create_time)這個普通索引並不包括查詢的所有列，因此需要通過ct_index的索引樹找到對應的主鍵id，然後再到id的索引樹進行數據查詢，即回表（通過索引查出主鍵，再去查數據行），這樣成本必然上升。尤其是當回表的數據量比較大的時候，經常會出現MySQL優化器認為回表查詢代價過高而不選擇索引的情況。

這里可以回頭看查詢1 和 查詢2的數據量佔比：

另外，在MySQL的官方文檔中對此也有簡要的描述：

https://dev.mysql.com/doc/refman/5.7/en/where-optimization.html

參考文檔：

https://opensource.actionsky.com/20201127-mysql/

https://blog.csdn.net/CSDNcircular/article/details/107253747

9. 索引的用途,在什麼情況下索引會降低資料庫操作性能

索引可以加快查詢速度，但是會降低增加、刪除老友、修改的速度
如果你的數據表，增加刪除修改的時候比查詢的時候侍液槐多，那麼用索引就會降低性能埋彎

10. MySQL 索引優化器選擇索引的規則是什麼

在鍵租謹開始演示之前，我們先介紹下兩個概念。

概念一，數據的可選擇性基數，也就是常說的cardinality值。

查詢優化器在生成各種執行計劃之前，得先從統計信息中取得相關數據，這樣才能估算每步操作所涉及到的記錄數，而這個相關數據就是cardinality。簡單來說，就是每個值在每個欄位中的唯一值分布狀態。

比如表t1有100行記錄，其中一列為f1。f1中唯一值的個數可以是100個，也可以是1個，當然也可以是1到100之間的任何一個數字。這里唯一值越的多少，就是這個列的可選擇基數。

那看到這里我們就明白了，為什麼要在基數高的欄位上建立索引，而基數低的的欄位建立索引反而沒有全表掃描來的快。當然這個只是一方面，至於更深入的探討就不在我這篇探討的范圍了。

概念二，關於HINT的使用。

這里我來說下HINT是什麼，在什麼時候用。

HINT簡單來說就是在某些特定的場景下人工協助MySQL優化器的工作，使她生成最優的執行計劃。一般來說，優化器的執行計劃都是最優化的，不過在某些特定場景下，執行計劃可能不是最優化。

比如：表t1經過大稿基量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經很不準確了，這時候剛好執行了一條SQL，那麼有可能這條SQL的執行計劃就不是最優的。為什麼說有可能呢？

來看下具體演示

譬如，以下兩條SQL，

• A：

• select * from t1 where f1 = 20;



• B：

• select * from t1 where f1 = 30;



如果f1的值剛好頻繁更新的值為30，並且沒有達到MySQL自動更新cardinality值的臨界值或者說用戶設置了手動更新又或者用戶減少了sample page等等，那麼對這兩條語句來說，可能不準確的就是B了。

這里順帶說下，MySQL提供了自動更新和手動更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊。

那回到正題上，MySQL 8.0 帶來了幾個HINT，我今天就舉個index_merge的例子。

示例表結構：

• mysql> desc t1;+------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || rank1 | int(11) | YES | MUL | NULL | || rank2 | int(11) | YES | MUL | NULL | || log_time | datetime | YES | MUL | NULL | || prefix_uid | varchar(100) | YES | | NULL | || desc1 | text | YES | | NULL | || rank3 | int(11) | YES | MUL | NULL | 型啟 |+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)



表記錄數：


• mysql> select count(*) from t1;+----------+| count(*) |+----------+| 32768 |+----------+1 row in set (0.01 sec)



這里我們兩條經典的SQL：

• SQL C：

• select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;



• SQL D：

• select * from t1 where rank1 =100 and rank2 =100 and rank3 =100;



表t1實際上在rank1,rank2,rank3三列上分別有一個二級索引。

那我們來看SQL C的查詢計劃。

顯然，沒有用到任何索引，掃描的行數為32034，cost為3243.65。

• mysql> explain format=json select * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3243.65" }, "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "rows_examined_per_scan": 32034, "rows_proced_per_join": 115, "filtered": "0.36", "cost_info": { "read_cost": "3232.07", "eval_cost": "11.58", "prefix_cost": "3243.65", "data_read_per_join": "49K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)



我們加上hint給相同的查詢，再次看看查詢計劃。

這個時候用到了index_merge,union了三個列。掃描的行數為1103，cost為441.09，明顯比之前的快了好幾倍。

• mysql> explain format=json select /*+ index_merge(t1) */ * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "441.09" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "union(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1103, "rows_proced_per_join": 1103, "filtered": "100.00", "cost_info": { "read_cost": "330.79", "eval_cost": "110.30", "prefix_cost": "441.09", "data_read_per_join": "473K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)



我們再看下SQL D的計劃：

• 不加HINT，

• mysql> explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "534.34" }, "table": { "table_name": "t1", "access_type": "ref", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "idx_rank1", "used_key_parts": [ "rank1" ], "key_length": "5", "ref": [ "const" ], "rows_examined_per_scan": 555, "rows_proced_per_join": 0, "filtered": "0.07", "cost_info": { "read_cost": "478.84", "eval_cost": "0.04", "prefix_cost": "534.34", "data_read_per_join": "176" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" } }}1 row in set, 1 warning (0.00 sec)



• 加了HINT，

• mysql> explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.23" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "intersect(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1, "rows_proced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "5.13", "eval_cost": "0.10", "prefix_cost": "5.23", "data_read_per_join": "440" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" } }}1 row in set, 1 warning (0.00 sec)



對比下以上兩個，加了HINT的比不加HINT的cost小了100倍。

總結下，就是說表的cardinality值影響這張的查詢計劃，如果這個值沒有正常更新的話，就需要手工加HINT了。相信MySQL未來的版本會帶來更多的HINT。