【文章內容簡介】 里并不適合使用 a的索引。? =， 通過表 table作為驅動表與表 tab做表連接，連接條件是 table表的 b列 =tab表的 a列，這里主要看表 table用于表連接的結果集其每條記錄的 b列值對應在 tab表的 a列能夠選取到的記錄數(shù)的總和占 tab表記錄數(shù)的百分比 (這里用 tab表 a列的索引指的是使用 nested loop表連接方式的情況下，使用 hash join或其他的表連接方式，這個比例的計算并不適用，關于表連接的方式，我們在后面討論 )，如果表 table用于作為驅動表的結果集比較小、且結果集中 b列的列值對應 tab表中 a列的列值能夠選取的到的記錄數(shù)比較低，則適合使用 tab表上 a列的索引。 (這里指使用 nested loop的情況，涉及到表連接索引的使用要跟表連接的方式一起考慮，在表連接的部分我們再做討論 ) 2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題? 缺少合適的索引可用 (選擇更加優(yōu)化的字段或者合理的復合索引首列 ) 案例 1：index 在這個案例中：語句 (1)存在一個日期范圍查詢可以使用在日期字段上的索引，但是如果時間范圍跨度過大，這個索引的效率也就不高了；語句 (2)能夠有查詢條件的字段當中只有一個區(qū)分度很低的字段建了索引 (千萬條數(shù)據(jù)只有幾十個獨立列值 )， 這個字段是不適合建立單列索引的，查詢使用這個索引的效率也非常低；這兩個語句我們通過分析語句，都發(fā)現(xiàn)了有區(qū)分度比較高且適用的查詢條件字段，只要在這些字段建立索引，就能優(yōu)化語句的執(zhí)行效率。這里我們看到，過大的范圍查詢會影響索引的效率；而過低的區(qū)分度的列則并不適合建立單列索引。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題? 缺少合適的索引可用 (選擇更加優(yōu)化的字段或者合理的復合索引首列 ) 案例 2：index 在這個案例中：表 cjk上原來有一個復合索引 (FZJZH, FBMDM, FSCDM, FGDDM, FZQDM, FHTXH, FMMLB,FCJSJ)， 這個索引的區(qū)分度很高，本來是很好用的。但問題就出來這兩個查詢語句中，前面幾列使用的都是模糊查詢，而根據(jù)實際情況，往往傳入的都是百分號，導致索引掃描的時候無法根據(jù)關鍵字的范圍快速的定位到需要的索引結點，在這里反而使用這個索引效率比全表掃描還要低得多 (實際情況是幾個小時 )。分析這條兩條語句的查詢條件，發(fā)現(xiàn) FCJSJ這個查詢條件，實際操作中基本上都是查詢一天的數(shù)據(jù)，這里只需要建立一個以 FCJSJ作為首列的復合索引(fbdsj,fbmdm,fzjzh,fgddm,fscdm,fzqdm)， 就可以優(yōu)化語句的效率。由于 B Tree索引是關鍵字排序，如果復合索引的首列不能根據(jù)查詢條件有效的篩選，就需要掃描大量冗余的索引結點；在這個案例中由于前面幾列都出現(xiàn)了 %號的情況，導致幾乎是將整個索引結點掃描了一遍才得到結果，效率非常低。所以復合索引要特別注意首列的選擇。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題? 不均勻分布的列 值 在 bind peeking和 histogram的影響下 ，影響 索引 的使用首先解釋下相關名詞：bind peeking： sql語句中使用到綁定變量，在第一次執(zhí)行時會 peeking其綁定變量的值，就相當于常量語句一樣，并根據(jù)這個具體值解析計算成本，解釋出執(zhí)行計劃。這一特性是在 oracle9i以后引入的。histogram： 直方圖，對于不同列值更加準確的數(shù)據(jù)量的統(tǒng)計。對于列值分布不均勻的列來說，通過直方圖，就能準確計算出不同列值的數(shù)據(jù)量，而不僅僅簡單的根據(jù) (總記錄數(shù) /獨立列值數(shù) )來計算其數(shù)據(jù)量 (平均情況 )。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題? 不均勻分布的列 值 在 bind peeking和 histogram的影響下 ，影響 索引 的使用以下引用《 ORACLE數(shù)據(jù)庫優(yōu)化案例簡報 (第一期 )》的相關內容：由于 8i 還沒有 bind peeking 技術，使用綁定變量以后無法使用 histogram， 所以最好在編程時對具有 skew 值的列不使用 bind 變量 ,這樣 , 生成計劃時 , 其可根據(jù)histogram 的值來估算返回的數(shù)據(jù)量 , 并生成合適的計劃。9i引入了 bind peeking技術，使用綁定變量以后可以用到 histogram， 但是如果第一次執(zhí)行帶入的變量值失誤，很可能產生的執(zhí)行計劃對以后的多次查詢不適合而帶來性能問題。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題? 不均勻分布的列 值 在 bind peeking和 histogram的影響下 ，影響 索引 的使用我們看看《 ORACLE數(shù)據(jù)庫優(yōu)化案例簡報 (第一期 )》所舉的這個案例：語句：select policy_cert_no, apply_personnel_numfrom acc_policy_certwhere policy_no = :1and cert_type = 39。139。select policy_cert_nofrom acc_policy_certwhere insurance_card_no = :1and policy_no = :22023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題? 不均勻分布的列 值 在 bind peeking和 histogram的影響下 ，影響 索引 的使用這兩條語句都是對表 acc_policy_cert進行查詢，在 policy_no字段上有主鍵索引 (復合索引的首列 )， 正常的情況下，應該走這個索引而不是全表掃描。但在 policy_no的列值分布并不均勻，比如 policy_no列共有 100個不同的值，其中為70的占了 99％，為其他值的數(shù)據(jù)行僅占 1％（即選擇性很高），則如果不使用綁定變量，借助 histogram， oracle能夠知道，查詢 policy_no=70的時候應該走全表掃描效率更高，查詢 policy_no為其他值的語句應該走索引效率更高。問題就出現(xiàn)了，假設第一次執(zhí)行時帶入的是 70的這個值，顯然執(zhí)行計劃會走全表掃描，但是由于使用了綁定變量，以后即使是帶入其他值，執(zhí)行計劃依然會走全表掃描。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題? 不均勻分布的列 值 在 bind peeking和 histogram的影響下 ，影響 索引 的使用解決辦法有 3個：)不使用綁定變量，但是這樣就會導致大量的 hard parse， 對 shared pool也會產生大量的消耗。)修改程序，針對不同情況使用不同執(zhí)行計劃 (不同的語句 ))不收集直方圖 (這樣就會按照平均情況來計算數(shù)據(jù)量 )或者使用 hint綁定執(zhí)行計劃，使其總能使用到索引，這樣就可以使大部分情況得到較好的效率，但對于比如 70這樣的值就會效率低下。《 ORACLE數(shù)據(jù)庫優(yōu)化案例簡報 (第一期 )》中相關內容。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于表連接? 三種主要的表連接方式? 何時使用哪種表連接方式2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯三種主要的表連接方式? nested loop join循環(huán)嵌套連接：行源 1的每一條記錄，依次去匹配行源 2的每條記錄，將符合連接條件的記錄放在結果集中，直到行源 1的所有記錄都完成這個操作。循環(huán)嵌套連接是最基本也是最古老的表連接方式。? sort merge join排序合并連接：行源 1和行源 2的數(shù)據(jù)分別排序，然后將兩個排序的源表合并，符合連接條件的記錄放到結果集中。由于排序需要內存空間， sort merge join對內存有比較大的消耗，如果內存空間 (8i為 sort_area_size， 9i及以上使用 PGA)不足，則會使用臨時表空間，這樣會降低排序合并連接的效率。排序合并連接是最古老的表連接方式之一。? hash join哈希連接：將行源 1計算成一張基于連接鍵的 hash表，行源 2的每條記錄依次掃描這張hash表，找到匹配的記錄放到結果集。計算 hash表需要內存空間， hash join同樣對于內存有比較大的消耗，如果內存空間 (8i為 hash_area_size， 9i及以上使用 PGA)不足，則會使用臨時表空間，這樣會降低哈希連接的效率。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯三種主要的表連接方式? nested loop join2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯三種主要的表連接方式? sort merge join2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯三種主要的表連接方式? hash join2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯何時使用哪種表連接方式? nested loop join表連接方式的適用情況nested loop join適合于：作為表連接的驅動表 (也就是之前的行源 1，也稱為外部表)記錄數(shù)比較少或者通過直接的查詢條件能篩選出比較少的記錄數(shù)，被連接表 (也就是之前的行源 2，也稱為內部表 )在連接條件上有區(qū)分度很高的索引；驅動表上的每條記錄通過被連接表在連接條件上的索引能快速的匹配到少量的記錄；整體的結果集比較小，這樣就比較適合使用 nested loop join。nested loop join選擇驅動表時應優(yōu)先選擇記錄數(shù)比較少的、通過直接查詢條件能夠篩選出比較少記錄的表作為驅動表，這樣能夠有效的減少匹配次數(shù)。例如這樣的查詢語句： select * from a,b where =:1 and =； 這里 a表有 100條記錄，通過 col1=:1的條件能夠篩選出 50條記錄， b表只有 10條記錄；這里如果以 a表作驅動表的話，則匹配次數(shù)是 50*10；如果用 b表作為驅動表的話，則匹配次數(shù)是10*100；顯然應該使用 a表作為驅動表。如果連接條件沒有很好的索引、或者作為表連接的兩張表結果集都相當大，則并不適合使用 nested loop join。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯何時使用哪種表連接方式? sort merge join表連接方式的適用情況sort merge join適用于：當表連接的兩張表的結果集都比較大，或沒有很好的條件可以篩選，連接條件缺少很好的索引時，可以選擇使用 sort merge join。 由于 sort merge join需要對作連接的兩張表都作排序，實際上如果語句中沒有排序需求，oracle更加傾向于選擇 hash join。 但如果語句中本身就有排序的需求， sort merge join則有可能省去單獨的排序。sort merge join對內存消耗比較大，如果內存空間不足以完成排序，則需要用到臨時表空間，效率會有較大的降低。sort merge join只能用于等價連接。2023/2/27 星期六 169。中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯何時使用哪種表連接方式? hash join表連接方式的適用情況hash join適用于：當表連接的兩張表的結果集都比較大，或沒有很好的條件可以篩選，連接條件缺少很好的索引時，使用 hash join能夠取得比較好的效率 。 hash join雖然也需要將一張表的所有記錄依次和 hash表中的記錄進行匹配，但掃描 hash表的速度要比掃描 B Tree索引快的多，所以在大結果集和缺少良好索引的情況下，使用 hash join能得到比較好的效率 。 與 sort merge join相比， hash join只需要進行一次排序，因此大部分情況下， oracle會更傾向于選擇 hash join。hash join需要計算一張 hash table， 與 sort merge join一樣，需要消耗大量的內存空