51精品国自产在线,入禽太深电影免费观看

99999久久久久久亚洲,欧美人与禽猛交狂配,高清日韩av在线影院,一个人在线高清免费观看,啦啦啦在线视频免费观看www

一條SQL引發(fā)的“血案”：與SQL優(yōu)化相關(guān)的4個案例

2020-08-14

作者：馬立和高振嬌韓鋒

來源：大數(shù)據(jù)DT（ID：hzdashuju）

內(nèi)容摘編自《數(shù)據(jù)庫高效優(yōu)化：架構(gòu)、規(guī)范與SQL技巧》

案例01 一條SQL引發(fā)的“血案”
1. 案例說明
某大型電商公司數(shù)據(jù)倉庫系統(tǒng)，正常情況下每天0～9點會執(zhí)行大量作業(yè)，生成前一天的業(yè)務(wù)報表，供管理層分析使用。但某天早晨6點開始，監(jiān)控人員就頻繁收到業(yè)務(wù)報警，大批業(yè)務(wù)報表突然出現(xiàn)大面積延遲。原本8點前就應(yīng)跑出的報表，一直持續(xù)到10點仍然沒有結(jié)果。公司領(lǐng)導(dǎo)非常重視，嚴(yán)令在11點前必須解決問題。
DBA緊急介入處理，通過TOP命令查看到某個進程占用了大量資源，殺掉后不久還會再次出現(xiàn)。經(jīng)與開發(fā)人員溝通，這是由于調(diào)度機制所致，非正常結(jié)束的作業(yè)會反復(fù)執(zhí)行。
暫時設(shè)置該作業(yè)無效，并從腳本中排查可疑SQL。同時對比從線上收集的ASH/AWR報告，最終定位到某條SQL比較可疑。
經(jīng)與開發(fā)人員確認(rèn)系一新增功能，因上線緊急，只做了簡單的功能測試。正是因為這一條SQL，導(dǎo)致整個系統(tǒng)運行緩慢，大量作業(yè)受到影響，修改SQL后系統(tǒng)恢復(fù)正常。

具體分析

這是一個很典型的兩表關(guān)聯(lián)語句，兩張表的數(shù)據(jù)量都較大。下面來看看執(zhí)行計劃，如圖1-1所示。
執(zhí)行計劃觸目驚心，優(yōu)化器評估返回的數(shù)據(jù)量為3505T條記錄，計劃返回量127P字節(jié)，總成本9890G，返回時間999:59:59。

分析結(jié)論

從執(zhí)行計劃中可見，兩表關(guān)聯(lián)使用了笛卡兒積的關(guān)聯(lián)方式。我們知道笛卡兒連接是指兩表沒有任何條件限制的連接查詢。一般情況下應(yīng)盡量避免笛卡兒積，除非某些特殊場合，否則再強大的數(shù)據(jù)庫也無法處理。
這是一個典型的多表關(guān)聯(lián)缺乏連接條件，導(dǎo)致笛卡兒積，引發(fā)性能問題的案例。
2. 給我們的啟示
從案例本身來講并沒有什么特別之處，不過是開發(fā)人員疏忽導(dǎo)致了一條質(zhì)量很差的SQL。但從更深層次來講，這個案例可以給我們帶來如下啟示。

開發(fā)人員的一個疏忽造成了嚴(yán)重的后果，原來數(shù)據(jù)庫竟是如此的脆弱。需要對數(shù)據(jù)庫保持“敬畏”之心。
電腦不是人腦，它不知道你的需求是什么，只能根據(jù)寫好的邏輯進行處理。
不要去責(zé)怪開發(fā)人員，誰都會犯錯誤，關(guān)鍵是如何從制度上保證不再發(fā)生類似的問題。

3. 解決之道
1）SQL開發(fā)規(guī)范
加強對數(shù)據(jù)庫開發(fā)人員的培訓(xùn)工作，提高其對數(shù)據(jù)庫的理解能力和SQL開發(fā)水平。將部分SQL運行檢查的職責(zé)前置，在開發(fā)階段就能規(guī)避很多問題。要向開發(fā)人員灌輸SQL優(yōu)化的思想，在工作中逐步積累，這樣才能提高公司整體開發(fā)質(zhì)量，也可以避免很多低級錯誤。
2）SQL Review制度
對于SQL Review，怎么強調(diào)都不過分。從業(yè)內(nèi)來看，很多公司也都在自己的開發(fā)流程中納入了這個環(huán)節(jié)，甚至列入考評范圍，對其重視程度可見一斑。其常見典型做法是利用SQL分析引擎（商用或自研）進行分析或采取半人工的方式進行審核。審核后的結(jié)果可作為持續(xù)改進的依據(jù)。
SQL Review的中間結(jié)果可以保留，作為系統(tǒng)上線后的對比分析依據(jù)，進而可將SQL的審核、優(yōu)化、管理等功能集成起來，完成對SQL整個生命周期的管理。
3）限流/資源控制
有些數(shù)據(jù)庫提供了豐富的資源限制功能，可以從多個維度限制會話對資源（CPU、MEMORY、IO）的使用，可避免發(fā)生單個會話影響整個數(shù)據(jù)庫的運行狀態(tài)。
對于一些開源數(shù)據(jù)庫，部分技術(shù)實力較強的公司還通過對內(nèi)核的修改實現(xiàn)了限流功能，控制資源消耗較多的SQL運行數(shù)量，從而避免拖慢數(shù)據(jù)庫的整體運行。

案例02 糟糕的結(jié)構(gòu)設(shè)計帶來的問題
1. 案例說明
這是某公司后臺的ERP系統(tǒng)，系統(tǒng)已經(jīng)上線運行了10多年。隨著時間的推移，累積的數(shù)據(jù)量越來越大。隨著公司業(yè)務(wù)量的不斷增加，數(shù)據(jù)庫系統(tǒng)運行緩慢的問題日益凸顯。
為提高運行效率，公司計劃有針對性地對部分大表進行數(shù)據(jù)清理。在DBA對某個大表進行清理時出現(xiàn)了問題。這個表本身有數(shù)百吉字節(jié)，按照指定的清理規(guī)則只需要根據(jù)主鍵字段范圍（運算符為>=）選擇出一定比例（不超過10%）的數(shù)據(jù)進行清理即可。
但在實際使用中發(fā)現(xiàn)，該SQL是全表掃描，執(zhí)行時間大大超出預(yù)期。DBA嘗試使用強制指定索引方式清理數(shù)據(jù)，依然無效，整個SQL語句的執(zhí)行效率達(dá)不到要求。為了避免影響正常業(yè)務(wù)運行，不得不將此次清理工作放在半夜進行，還需要協(xié)調(diào)庫房等諸多單位進行配合，嚴(yán)重影響正常業(yè)務(wù)運行。
為了盡量減少對業(yè)務(wù)的影響，DBA求助筆者幫助協(xié)同分析。這套ERP系統(tǒng)是由第三方公司開發(fā)的，歷史很久遠(yuǎn)，相關(guān)的數(shù)據(jù)字典等信息都已經(jīng)找不到了，只能從純數(shù)據(jù)庫的角度進行分析。這是一個普通表（非分區(qū)表），按照主鍵字段的范圍查詢一批記錄并進行清理。
按照正常理解，執(zhí)行索引范圍掃描應(yīng)該是效率較高的一種處理方式，但實際情況都是全表掃描。進一步分析發(fā)現(xiàn)，該表的主鍵是沒有業(yè)務(wù)含義的，僅僅是自增長的數(shù)據(jù)，其來源是一個序列。
但奇怪的是，這個主鍵字段的類型是變長文本類型，而不是通常的數(shù)字類型。當(dāng)初定義該字段類型的依據(jù)，現(xiàn)在已經(jīng)無從考證，但實驗表明正是這個字段的類型“異?！保瑢?dǎo)致了錯誤的執(zhí)行路徑。
下面通過一個實驗重現(xiàn)這個問題。
1）數(shù)據(jù)準(zhǔn)備
兩個表的數(shù)據(jù)類型相似（只是ID字段類型不同），各插入了320萬數(shù)據(jù)，ID字段范圍為1~3200000。

2）模擬場景
相關(guān)代碼如下：

對于普通的采用數(shù)值類型的字段，范圍查詢就是正常的索引范圍掃描，執(zhí)行效率很高。

對于文本類型字段的表，范圍查詢就是對應(yīng)的全表掃描，效率較低是顯而易見的。
3）分析結(jié)論

字符類型在索引中是“亂序”的，這是因為字符類型的排序方式與我們的預(yù)期不同。從“select * from t2 where id>= '3199990'”執(zhí)行返回755 565條記錄可見，不是直觀上的10條記錄。這也是當(dāng)初在做表設(shè)計時，開發(fā)人員沒有注意的問題。
字符類型還導(dǎo)致了聚簇因子很大，原因是插入順序與排序順序不同。詳細(xì)點說，就是按照數(shù)字類型插入（1..3200000），按字符類型（'1'...'32000000'）t排序。

select table_name,index_name,leaf_blocks,num_rows,clustering_factor from user_indexes where table_name in ('T1','T2');
TABLE_NAME         INDEX_NAME      LEAF_BLOCKS   NUM_ROWS    CLUSTERING_FACTOR -------------- -------------- ---------------- ---------- --------------------- T1               SYS_C0025294             6275    3200000                 31520
T2               SYS_C0025295            13271    3200000                632615

在對字符類型使用大于運算符時，會導(dǎo)致優(yōu)化器認(rèn)為需要掃描索引大部分?jǐn)?shù)據(jù)且聚簇因子很大，最終導(dǎo)致棄用索引掃描而改用全表掃描方式。

4）解決方法
具體的解決方法如下：

select * from t2 where id between '3199990' and '3200000'; -------------------------------------------------------------------------------- | Id  | Operation                 | Name         |Rows|Bytes |Cost(%CPU)| Time   | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT          |             |   6|  390 |   5 (0)|00:00:01|
|   1 |  TABLE ACCESS BY INDEX ROWID| T2           |   6|  390 |   5 (0)|00:00:01|
|*  2 |   INDEX RANGE SCAN        | SYS_C0025295 |   6|      |   3 (0)|00:00:01| -------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1  recursive calls 0  db block gets 13  consistent gets 0  physical reads

將SQL語句由開放區(qū)間掃描（>=），修改為封閉區(qū)間（between xxx and max_value）。使得數(shù)據(jù)在索引局部順序是“對的”。如果采用這種方式仍然走全表掃描，還可以進一步細(xì)化分段或者采用“逐條提取+批綁定”的方法。
2. 給我們的啟示
這是一個典型的由不好的數(shù)據(jù)類型帶來的執(zhí)行計劃異常的例子。它給我們帶來如下啟示：

糟糕的數(shù)據(jù)結(jié)構(gòu)設(shè)計往往是致命的，后期的優(yōu)化只是補救措施。只有從源頭上加以杜絕，才是優(yōu)化的根本。
在設(shè)計初期能引入數(shù)據(jù)庫審核，可以起到很好的作用。

案例03 規(guī)范SQL寫法好處多

1. 案例說明
某大型電商公司數(shù)據(jù)倉庫系統(tǒng)，開發(fā)人員反映作業(yè)運行緩慢。經(jīng)檢查是一個新增業(yè)務(wù)中某條SQL語句導(dǎo)致。經(jīng)分析是非標(biāo)準(zhǔn)的SQL引起優(yōu)化器判斷異常，將其修改成標(biāo)準(zhǔn)寫法后，SQL恢復(fù)正常。
1）具體分析
看下面的代碼：

select ... from ... where (
    ( 
　　　  order_creation_date>= to_date(20120208,'yyyy-mm-dd') and 
    order_creation_date<to_date(20120209,'yyyy-mm-dd')
    )  or     ( 
　　　  send_date>= to_date(20120208,'yyyy-mm-dd') and send_date<to_date(20120209,　'yyyy-mm-dd')
    )
)
andnvl(a.bd_id,0) = 1 -------------------------------------------------------------------------------- |  Id | Operation              | Name   |Cost (%CPU)| Time   |Pstart | Pstop | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |        | 2470K(100)|        |       |       |
|   1 |  SORT GROUP BY         |        |           |        |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID                                   |  XXXX  |     5 (0) | 00:00:01 | ROW L | ROW L |
|   3 |    NESTED LOOPS         |        | 2470K (1) | 08:14:11 |       |       |
|   4 |     VIEW               |VW_NSO_1| 2470K (1) | 08:14:10 |       |       |
|   5 |      FILTER            |        |           |          |       |       |
|   6 |       HASH GROUP BY    |        |  2470K (1)| 08:14:10 |       |       |
|   7 |        TABLE ACCESS BY GLOBAL INDEX ROWID 
                               |  XXXX  |      5 (0)| 00:00:01 | ROW L | ROW L |
|   8 |         NESTED LOOPS    |        |  2470K (1)| 08:14:10 |       |       |
|   9 |          SORT UNIQUE    |        |  2340K (2)| 07:48:11 |       |       |
|  10 |           PARTITION RANGE ALL  
                                |        |  2340K (2)| 07:48:11 |    1  |    92 |
|  11 |            TABLE ACCESS FULL                                 |  XXXX  |  2340K (2)| 07:48:11 |    1  |    92 |
|  12 |          INDEX RANGE SCAN 
                                |  XXXX  |      3 (0)| 00:00:01 |       |       |
|  13 |     INDEX RANGE SCAN    |  XXXX  |      3 (0)| 00:00:01 |       |       | --------------------------------------------------------------------------------

這個SQL中涉及的主要表是一個分區(qū)表，從執(zhí)行計劃（Pstart、Pstop）中可見，掃描了所有分區(qū)，分區(qū)裁剪特性沒有起效。
2）解決方法
見下面的代碼：

select ... from ... where 
    order_creation_date >= to_date(20120208,'yyyy-mm-dd') and 
    order_creation_date<to_date(20120209,'yyyy-mm-dd') union all select ... from ... where send_date>= to_date(20120208,'yyyy-mm-dd') and 
    send_date<to_date(20120209,'yyyy-mm-dd') and 
nvl(a.bd_id,0) = 5

嘗試通過引入union all來分解查詢，以便于優(yōu)化器做出更準(zhǔn)確的判斷。采用這個方法后，確實起效了，當(dāng)然不可避免會掃描兩遍表。

select ... from ... where (
    ( 
        order_creation_date>= to_date(20120208,'yyyymmdd') and 
        order_creation_date<to_date(20120209,'yyyymmdd')
    )  or     ( 
        send_date>= to_date(20120208,'yyyymmdd') and 
        send_date<to_date(20120209,'yyyymmdd')
    )
); -------------------------------------------------------------------------------- |  Id   | Operation           | Name | Cost(%CPU)|Time      | Pstart  | Pstop   | -------------------------------------------------------------------------------- |     0 | SELECT STATEMENT    |      |  42358 (1)| 00:08:29 |         |         |
|     1 |  SORT AGGREGATE     |      |           |          |         |         |
|     2 |   CONCATENATION     |      |           |          |         |         |
|     3 |    PARTITION RANGE SINGLE
                              |      |  17393 (1)| 00:03:29 |      57 |     57 |
|*    4 |     TABLE ACCESS FULL                               | XXXX |  17393 (1)| 00:03:29 |      57 |     57 |
|*    5 |    TABLE ACCESS BY GLOBAL INDEX ROWID 
                              | XXXX |  24966 (1)| 00:05:00 |   ROWID |  ROWID |
|*    6 |     INDEX RANGE SCAN  
                              | XXXX |    658 (1)| 00:00:08 |         |         | ---------------------------------------------------------------------------------

通過調(diào)整日期FORMAT格式，優(yōu)化器很精準(zhǔn)地判斷了分區(qū)（Pstart=57、Pstop=57），整體SQL性能得到了很大的提高，作業(yè)運行時間從8個多小時縮減到8分鐘。
3）分析結(jié)論
對于非標(biāo)準(zhǔn)的日期格式，Oracle在復(fù)雜邏輯判斷的情況下分區(qū)裁剪特性無法識別，不起作用。這種情況下，會走全表掃描，結(jié)果是正確的，但是執(zhí)行效率會很低。通過使用union all，簡化了條件判斷。使得Oracle在非保準(zhǔn)日期格式下也能使用分區(qū)裁剪特性，但最佳修改方式還是規(guī)范SQL的寫法。
2. 給我們的啟示

規(guī)范的SQL寫法，不但利于提高代碼可讀性，還有利于優(yōu)化器生成更優(yōu)的執(zhí)行計劃。
分區(qū)功能是Oracle應(yīng)對大數(shù)據(jù)的利器，但在使用中要注意是否真正會用到分區(qū)特性；否則，可能適得其反，使用分區(qū)會導(dǎo)致效率更差。

案例04 “月底難過”
1. 案例說明
某大型電商公司數(shù)據(jù)倉庫系統(tǒng)經(jīng)常出現(xiàn)在月底運行緩慢的情況，但在平時系統(tǒng)運行卻非常正常。這是因為月底往往有月報等大批量作業(yè)運行，而就在這個時間點上，常常會出現(xiàn)緩慢情況，所以業(yè)務(wù)人員一到月底就非常緊張。這也成了一個老大難問題，困擾了很長時間。
DBA介入處理，發(fā)現(xiàn)一個很奇怪的現(xiàn)象：某條主要SQL是造成執(zhí)行緩慢的主因，其執(zhí)行計劃是不確定的，也就是說因為執(zhí)行計劃的改變，導(dǎo)致其運行效率不同。而往往較差的執(zhí)行計劃發(fā)生在月底幾天，且由于月底大批作業(yè)的影響，整體性能比較飽和，更突顯了這個問題。
針對某個出現(xiàn)問題的時間段做了進一步分析，結(jié)果表明是由于統(tǒng)計信息的缺失導(dǎo)致了優(yōu)化器產(chǎn)生了較差的執(zhí)行計劃，并據(jù)此指定了人工策略，徹底解決了這個問題。
1）具體分析
先來看下面的代碼：

select... from xxx a join xxx b on a.order_id = b.lyywzdid left join xxx c on b.gysid = c.gysid
whereb.cdate>= to_date('2012-03-31', 'yyyy-mm-dd') – 3 and ...
a.send_date>= to_date('2012-03-31', 'yyyy-mm-dd') - 1 and 
　　　　a.send_date<to_date('2012-03-31', 'yyyy-mm-dd'); -------------------------------------------------------------------------------- |Id  | Operation          |Name  |  Rows  |  Bytes  | Cost (%CPU) |Pstart|Pstop| -------------------------------------------------------------------------------- |  0 | SELECT STATEMENT   |      |      1 |     104 |      9743(1)|      |     |
|  1 |  HASH JOIN OUTER   |      |      1 |     104 |      9743(1)|      |     |
|  2 |   TABLE ACCESS BY LOCAL INDEX ROWID                           | XXXX |      1 |      22 |         0(0)| 1189 | 1189|
|  3 |    NESTED LOOPS    |      |      1 |      94 |      9739(1)|      |     |
|  4 |     PARTITION RANGE ITERATOR    
                          |      |   1032 |   74304 |      9739(1)|  123 | 518 |
|  5 |      TABLE ACCESS FULL 
                          | XXXX |   1032 |   74304 |      9739(1)|  123 | 518 |
|  6 |     PARTITION RANGE SINGLE
                          |      |      1 |         |         0(0)| 1189 | 1189 |
|  7 |      INDEX RANGE SCAN 
                          | XXXX |      1 |         |         0(0)| 1189 | 1189 |
|  8 |   TABLE ACCESS FULL                           | XXXX |    183 |    1830 |         3(0)|      |     | --------------------------------------------------------------------------------

執(zhí)行計劃中，多表關(guān)聯(lián)使用了嵌套循環(huán)，這點對于OLAP系統(tǒng)來說是比較少見的。一般優(yōu)化器更傾向于使用SM和HJ。進一步檢查發(fā)現(xiàn)其成本竟然是0，怪不得優(yōu)化器使用了嵌套循環(huán)。
2）深入分析
檢查發(fā)現(xiàn)索引數(shù)據(jù)統(tǒng)計信息異常，這是分區(qū)索引，僅兩天的分區(qū)統(tǒng)計信息都是0。導(dǎo)致優(yōu)化器認(rèn)為嵌套循環(huán)的執(zhí)行效率更高，而不是使用哈希連接。結(jié)合業(yè)務(wù)發(fā)現(xiàn)，月底是業(yè)務(wù)高峰期，對于系統(tǒng)統(tǒng)計信息的作業(yè)收集，在指定的時間窗口內(nèi)無法完成。最后導(dǎo)致統(tǒng)計信息不完整，優(yōu)化器采用了錯誤的執(zhí)行計劃。
3）解決方法
解決的代碼如下：

exec dbms_stats.gather_index_stats(
  ownname=>'xxx', 
  indname=>'xxx',
  partname=>'PART_xxx', 
  estimate_percent => 10);

分析完對象的統(tǒng)計信息即恢復(fù)正常。
2. 給我們的啟示

統(tǒng)計信息是優(yōu)化器優(yōu)化的重要參考依據(jù)，一個完整、準(zhǔn)確的統(tǒng)計信息是必要條件。往往在優(yōu)化過程中，第一步就是查看相關(guān)對象的統(tǒng)計信息。
分區(qū)機制是Oracle針對大數(shù)據(jù)的重要解決手段，但也很容易造成所謂“放大效應(yīng)”。即對于普通表而言，統(tǒng)計信息更新不及時可能不會導(dǎo)致執(zhí)行計劃偏差過大；但對于分區(qū)表、索引來說，很容易出現(xiàn)因更新不及時出現(xiàn)0的情況，進而導(dǎo)致執(zhí)行計劃產(chǎn)生嚴(yán)重偏差。

CDA數(shù)據(jù)分析師考試相關(guān)入口一覽（建議收藏）：

? 想報名CDA認(rèn)證考試，點擊>>> “CDA報名” 了解CDA考試詳情；