建站
咨詢
售后
建站咨詢
新聞中心
“為什么索引沒有被使用”是一個(gè)涉及面較廣的問題。有很多種原因會(huì)導(dǎo)致索引沒有被使用。下面是一些非常有用的檢查列表。請(qǐng)點(diǎn)擊下面鏈接來查看文章的具體內(nèi)容：
成都創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括浦江網(wǎng)站建設(shè)、浦江網(wǎng)站制作、浦江網(wǎng)頁(yè)制作以及浦江網(wǎng)絡(luò)營(yíng)銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，浦江網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到浦江省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
快速檢查
表上是否存在索引?
檢查您認(rèn)為應(yīng)該通過索引訪問的表上是否真的有定義索引。那些索引可能已經(jīng)被刪掉或者在創(chuàng)建的時(shí)候就失敗了 – 比如一種可能的場(chǎng)景是，在對(duì)表做導(dǎo)入或 load 操作后，由于軟件或人為錯(cuò)誤造成索引沒有被創(chuàng)建。下面的語(yǔ)句可以用來檢查索引是否存在。
- SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;
索引是否應(yīng)該被使用?
Oracle 不會(huì)僅僅因?yàn)橛兴饕嬖诰鸵欢ㄒ褂盟饕?。如果一個(gè)查詢需要檢索出這個(gè)表里所有的記錄(比如說表之間做連接操作)，那為什么還要既訪問索引的所有數(shù)據(jù)又訪問表的所有數(shù)據(jù)呢?在這種情況下只訪問表的數(shù)據(jù)會(huì)更快。對(duì)所有的查詢 Oracle Optimizer 會(huì)基于統(tǒng)計(jì)信息來計(jì)算各種訪問路徑，包括索引，從而選出***的一個(gè)。
索引本身的問題
索引列或者索引的前置列是否在單表(non-join)查詢的 Where 條件中(predicate list)?
如果不是，至少需要索引前置列在查詢謂詞列表中，查詢才能使用索引。(例外：請(qǐng)見下面的 Skip Scan)。
示例:
在列 EMP.EMPNO 上定義了單列索引 EMPNO_I1，同時(shí)在列 EMP.EMPNO 和 EMP.DEPT 上定義了聯(lián)合索引 EMPNO_DEPT_I2(EMP.EMPNO為索引前置列)。那么必須在查詢謂詞列表中(where從句)使用列 EMP.EMPNO，優(yōu)化器才能使用這兩個(gè)索引中的某一個(gè)。
- SELECT ename, sal, deptno FROM emp WHERE empno<100;
例外:
- 只要索引中包含查詢所需的所有列, 而且至少有一個(gè)索引列中含有非空約束，CBO 就能夠使用索引快速全掃描(INDEX_FFS)。執(zhí)行 INDEX_FFS 不需要索引前置列。需要注意的是 INDEX_FFS 不能保證返回的行是排序的。結(jié)果的順序是與讀取索引塊的順序一致的，只有當(dāng)使用了 'order by' 子句時(shí)才能保證結(jié)果是排序的。
- CBO 能使用 Index Skip Scan (INDEX_SS). 執(zhí)行 INDEX_SS 不需要索引前置列。
- CBO 能夠選用一個(gè)索引來避免排序，但是索引列必須存于在 order by 子句中才可以。
索引列是否用在連接謂詞中(join predicates)?
例如，下面這個(gè)連接謂詞定義了如何在表 emp 和 dept 的 deptno 列上做連接：
- emp.deptno = dept.deptno
如果索引列是連接謂詞的一部分，那么查詢?cè)趫?zhí)行時(shí)使用了哪種類型的連接?
- 哈希/排序合并連接(Hash / Sort Merge Join): 對(duì)于哈希連接和排序合并，在連接執(zhí)行的時(shí)候，外部表的信息還沒有獲得，因此無法進(jìn)行對(duì)內(nèi)部表的行檢索。它的處理方式是將外部表和內(nèi)部表分別查詢后將結(jié)果合并。哈希連接和排序合并的內(nèi)部表不能通過連接的索引列單獨(dú)被訪問。這是連接類型的執(zhí)行機(jī)制的限制。嵌套循環(huán)連接有所不同，它們?cè)试S通過索引查詢內(nèi)部表的連接列。
- 嵌套循環(huán)連接(Nested Loops Join):嵌套循環(huán)連接讀取外部表，然后利用所收集的信息訪問內(nèi)部表。該算法允許對(duì)內(nèi)部表基于索引進(jìn)行查詢。
只有嵌套循環(huán)連接(Nested loops join)允許索引在內(nèi)部表中僅基于連接列進(jìn)行查找。
另外，連接的順序(join order)是否允許使用索引?
一個(gè)嵌套循環(huán)連接的外部表必須已經(jīng)訪問過，才可以在內(nèi)部表中使用索引。查看 explain plan，以確定哪些訪問路徑已經(jīng)使用。由于這個(gè)限制，表的連接順序是很重要的。
例如：
如果我們通過"emp.deptno = dept.deptno"來對(duì) EMP 和 DEPT 做連接，并且在 EMP.DEPTNO 有一個(gè)索引，并假設(shè)查詢中沒有與 EMP.DEPTNO 相關(guān)的其他謂詞，EMP 是在 DEPT 前被訪問，然后沒有值可用于在 EMP.DEPTNO 索引中查詢。在這種連接順序下，要想使用這個(gè)索引我們只能使用全索引掃描或索引快速全掃描。在這種情況下，全表掃描(FTS)的成本可能更小。
索引列在 IN 或者多個(gè) OR 語(yǔ)句中?
比如：
- emp.deptno IN (10,23,34,....)
或
- emp.deptno = 10
- OR emp.deptno = 23
- OR emp.deptno = 34
- ....
這種情況下查詢可能已經(jīng)被轉(zhuǎn)化為不能使用索引的語(yǔ)句。
索引列是否被函數(shù)修改?
索引不能用于被函數(shù)修改的列。函數(shù)索引(function based indexes)可以用來解決這個(gè)問題。
隱式類型轉(zhuǎn)換(implicit type conversion)是什么?
如果進(jìn)行比較的兩個(gè)值的數(shù)據(jù)類型不同，則 Oracle 必須將其中一個(gè)值進(jìn)行類型轉(zhuǎn)換使其能夠比較。這就是所謂的隱式類型轉(zhuǎn)換。通常當(dāng)開發(fā)人員將數(shù)字存儲(chǔ)在字符列時(shí)會(huì)導(dǎo)致這種問題的產(chǎn)生。Oracle 在運(yùn)行時(shí)會(huì)強(qiáng)制轉(zhuǎn)化其中一個(gè)值，(由于固定的規(guī)則)在索引字符列使用 to_number。由于添加函數(shù)到索引列所以導(dǎo)致索引不被使用。實(shí)際上，Oracle 也只能這么做，類型轉(zhuǎn)換是一個(gè)應(yīng)用程序設(shè)計(jì)因素。由于轉(zhuǎn)換是在每行都進(jìn)行的，這會(huì)導(dǎo)致性能問題。
是否在語(yǔ)義(semantically)上無法使用索引?
出于對(duì)查詢整體成本的考慮，一個(gè)成本較低的執(zhí)行計(jì)劃中可能是無法使用索引的。某索引可能已經(jīng)被考慮在某種連接排序及方法中，但是成本***的那個(gè)執(zhí)行計(jì)劃中卻無法從“語(yǔ)義”角度使用該索引。
錯(cuò)誤類型的索引掃描?
例如：快速全索引掃描而不是索引范圍掃描
這可能是優(yōu)化器選擇了所需的索引，但卻使用了客戶不希望的掃描方法。在這種情況下，利用 INDEX_FFS，INDEX_ASC 和 INDEX_DESC 提示來強(qiáng)制使用需要的掃描類型
我們還可以定義索引的排序順序?yàn)檫f增或遞減。Oracle 對(duì)待降序索引就好像它是基于函數(shù)的索引，因此與缺省使用的升序的執(zhí)行計(jì)劃不同。通過查看執(zhí)行計(jì)劃，您看不到使用升序或降序，需要額外檢查視圖 DBA_IND_COLUMNS 的'DESCEND'列。
是否索引列為可空?
索引不存儲(chǔ) NULL 值，除非該索引為聯(lián)合索引(即多列索引)，或者它是一個(gè)位圖索引。
只有至少有一個(gè)索引列有值，聯(lián)合索引才存儲(chǔ)空值。聯(lián)合索引中尾部的空值也會(huì)被存放在索引中。如果所有列的值都為空，這行將不會(huì)存儲(chǔ)在索引中。由于索引中缺乏 NULL 值，那么一些結(jié)果中可能會(huì)返回 NULL 值(如count)的操作可能會(huì)被禁用索引。這是因?yàn)閮?yōu)化器不能保證在單獨(dú)使用索引時(shí)可以獲得準(zhǔn)確的信息。關(guān)于使用 NOT IN 和 NULL 的其他一些考慮。
位圖索引允許存儲(chǔ)空值。因此優(yōu)化器會(huì)使用這些索引,無論它們的結(jié)果可信與否。索引上的空值有時(shí)很有用，特別對(duì)于某些類型的 SQL 語(yǔ)句，如與聚合函數(shù) COUNT 查詢。示例:
- SELECT count(*) FROM emp;
NLS_SORT是否設(shè)置為二進(jìn)制(BINARY)?
如果 NLS_SORT 未設(shè)置為二進(jìn)制，索引將不會(huì)被使用。這是因?yàn)樗饕腔?Key 值的二進(jìn)制順序來建立的(pre-sorted使用二進(jìn)制值)。無論優(yōu)化器設(shè)置為何種方法，NLS_SORT 不是二進(jìn)制時(shí)，將使用全表掃描，。更多關(guān)于NLS_SORT和索引的使用
是否使用的是不可見索引(invisible indexes)?
從 Oracle Database 11g Release 1開始，您可以創(chuàng)建不可見索引或?qū)⒁粋€(gè)已經(jīng)存在的索引標(biāo)記為不可見。Optimizer 不會(huì)考慮不可見索引，除非在 session 或 system 級(jí)將參數(shù) OPTIMIZER_USE_INVISIBLE_INDEXES 設(shè)置為 TRUE。DML 操作還是會(huì)維護(hù)這些不可見索引的。
優(yōu)化器和成本計(jì)算相關(guān)問題
是否存在準(zhǔn)確且合適的統(tǒng)計(jì)信息(Statistics)?
CBO 依賴于準(zhǔn)確的、***的和完整的統(tǒng)計(jì)信息來確定一個(gè)特定查詢的***執(zhí)行計(jì)劃。如果使用 CBO，請(qǐng)確保統(tǒng)計(jì)信息已經(jīng)收集。如果沒有統(tǒng)計(jì)信息， CBO 將使用預(yù)定義的統(tǒng)計(jì)信息，這樣是很可能不會(huì)產(chǎn)生良好的計(jì)劃或讓應(yīng)用程序使用索引。
請(qǐng)注意，CBO 會(huì)根據(jù)開銷(COST)來決定使用不同的索引。除了基本的表和索引的信息之外，如果說在某些列上數(shù)據(jù)分布是不均勻的，那么還需要收集這些列的數(shù)據(jù)的分布。
在一般情況下，對(duì)象的數(shù)據(jù)或結(jié)構(gòu)的改變會(huì)使以前的統(tǒng)計(jì)信息不準(zhǔn)確，因此應(yīng)該重新收集新的統(tǒng)計(jì)信息。例如，對(duì)表裝載了大量的數(shù)據(jù)后，需要收集新的統(tǒng)計(jì)信息。安裝新補(bǔ)丁集(Patchset)后,也建議重新收集統(tǒng)計(jì)信息。表訪問***效果是統(tǒng)計(jì)信息是在相同版本的數(shù)據(jù)庫(kù)中生成的。
一個(gè)索引是否與其它的索引有相同的等級(jí)或者成本(cost)?
對(duì)于相同開銷(COST)的索引，CBO 會(huì)使用多種辦法將不同的索引區(qū)分開，如將索引名稱按字母順序排序，完全匹配的索引掃描會(huì)選擇更大的NDK(不同鍵值的個(gè)數(shù))的索引(不適用于快速全掃描)或選擇葉塊數(shù)量較少的索引。請(qǐng)注意一般很少發(fā)生這種情況。
索引的選擇度不高?
索引的選擇度不高
使用它可能不是一個(gè)好的選擇...
列數(shù)據(jù)不是平均分布的。
- CBO 假定列數(shù)據(jù)不會(huì)傾斜，并均勻分布。如果不是這樣，那么統(tǒng)計(jì)信息可能沒有反映真實(shí)情況，那么即使某些值的選擇度高，索引也會(huì)因?yàn)檎麄€(gè)列的選擇度不高而不適用索引。 如果是這種情況，那么應(yīng)考慮采用直方圖記錄更準(zhǔn)確的列的數(shù)據(jù)分布或者采用提示(hint)。
- 統(tǒng)計(jì)信息不準(zhǔn)確導(dǎo)致索引看起來選擇性不高而不被選擇?？赡艿囊?guī)避方法:
- 收集更精確的統(tǒng)計(jì)值。
- 對(duì)于數(shù)據(jù)分布不均勻的列考慮收集列的統(tǒng)計(jì)信息
在總體成本中，表掃描的成本占大部分
通常來說，當(dāng)使用索引的時(shí)候，我們需要再次檢索表本身來找到索引中不存在的字段的值，這個(gè)操作比檢索索引本身的開銷要大很多。由于 optimizer 是基于總體的成本來計(jì)算執(zhí)行計(jì)劃，如果通過索引檢索表的成本很大，并且超過了某個(gè)閥值，optimizer 就會(huì)考慮其他的訪問路徑。
比如：
- SELECT empno FROM emp WHERE empno=5
這條語(yǔ)句可能會(huì)使用基于列 empno的索引，因?yàn)樗行枰臄?shù)據(jù)都存放在索引中所以不需要再對(duì)表做而外的訪問。反之：
- SELECT ename FROM emp WHERE empno=5
這條語(yǔ)句會(huì)需要對(duì)表做而外的訪問，因?yàn)?ename 字段沒有存放在索引中。檢索 ename 的開銷會(huì)隨著查詢返回記錄條數(shù)的增加而變得昂貴。
Optimizer 使用"Clustering Factor"來判斷如果使用 index 的話需要而外對(duì)表做多少次訪問
訪問空索引并不意味著比訪問有值的索引高效。
Reorganization, Truncation 或刪除操作不一定會(huì)影響 SQL 語(yǔ)句執(zhí)行的成本。需要注意的是刪除操作并不會(huì)從對(duì)象中真正釋放空間。也就是說，刪除操作不會(huì)重置對(duì)象的高水位線。Truncate 操作會(huì)重置高水位線。空塊的存在會(huì)使索引/表掃描的成本比實(shí)際應(yīng)該的成本高。刪掉并重建會(huì)重組對(duì)象的結(jié)構(gòu)從而有可能會(huì)有幫助(也有可能變壞)。這類問題通常在比較兩個(gè)有相同數(shù)據(jù)的不同系統(tǒng)查詢性能時(shí)更容易看到。
參數(shù)設(shè)置
某些參數(shù)的設(shè)置可能會(huì)影響索引的使用。比如在大多數(shù)情況下都建議使用 DB_FILE_MULTIBLOCK_READ_COUNT 和 OPTIMIZER_INDEX_COST_ADJ 的默認(rèn)值。除非某些特定的操作有特定的建議，使用其它值會(huì)使索引的成本不現(xiàn)實(shí)的減少或變大從而極大的降低查詢的性能。
其它問題：
是否使用了視圖/子查詢?
查詢涉及到視圖或者子查詢時(shí)可能會(huì)被改寫，導(dǎo)致不使用索引(盡管該改寫的目標(biāo)之一是擴(kuò)展更多的訪問路徑)。這些改寫(rewrite)一般來說都是合并(merging)操作。
是否存在遠(yuǎn)程表(remote table)?
通常遠(yuǎn)程表不會(huì)使用索引。索引在分布式查詢中的使用依賴于被發(fā)送到遠(yuǎn)程的查詢。CBO 將評(píng)估遠(yuǎn)程訪問的成本，并評(píng)估比較發(fā)送或者不發(fā)送索引的謂詞到遠(yuǎn)程站點(diǎn)的成本。因此，CBO 可以做出有關(guān)遠(yuǎn)程表上使用索引的更加明智的決定。一個(gè)非常有效的方法就是,在遠(yuǎn)程建立包含相關(guān)謂詞的視圖并強(qiáng)制使用索引，之后在本地查詢中使用這個(gè)視圖。
是否使用并行執(zhí)行(PX)?
在并行執(zhí)行時(shí)索引的采用比在串行執(zhí)行((serial execution))時(shí)更加嚴(yán)格。一個(gè)快速檢測(cè)的方法就是禁用并行，然后查看該索引是否被使用。
是否是包含了子查詢的Update語(yǔ)句?
在一些情況下，基于成本的考慮，索引沒有被選使用是因?yàn)樗蕾囉谝粋€(gè)子查詢返回的值。這種情況下，可以使用提示(hint)來強(qiáng)制使用索引。
查詢是否使用了綁定變量?
CBO 對(duì) like 或范圍謂詞的綁定變量不能產(chǎn)生準(zhǔn)確的成本(cost)。這可能會(huì)導(dǎo)致索引不被選擇。
查詢是否引用了帶有延遲約束的列?
如果一個(gè)表中的某一列上含有延遲約束(比如 NOT NULL)并且這一列上有索引，那么不管這個(gè)約束當(dāng)前是延遲狀態(tài)或是被顯式地設(shè)置為立即使用，我們都不會(huì)考慮使用這一列上的索引。例如：
這個(gè)現(xiàn)象在以下 bug 中記錄，關(guān)閉為"not a bug":
索引提示(hint)不工作
請(qǐng)使用表的別名
有用的 hints:
標(biāo)題名稱：為何在查詢中索引未被使用
本文URL：http://www.dlmjj.cn/article/djpocjs.html
