《《數(shù)據(jù)倉庫SQL優(yōu)化》PPT課件.ppt》由會員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)倉庫SQL優(yōu)化》PPT課件.ppt（22頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

數(shù)據(jù)倉庫系統(tǒng)調(diào)優(yōu)課題,DB2數(shù)據(jù)庫分區(qū),數(shù)據(jù)庫分區(qū)(database partitioning)設(shè)計(jì),基本思路：字典表壓縮 Oracle：塊級壓縮 塊：大小介于4KB~32KB之間的存儲單元 當(dāng)確定某個(gè)表要被壓縮后，Oracle在每個(gè)數(shù)據(jù)塊中保留空間，以便儲存在該數(shù)據(jù)塊中的多個(gè)位置上出現(xiàn)的數(shù)據(jù)的單一拷貝 不能夠跨塊的邊界尋找更大的重復(fù)模式 只支持在批量加載操作期間進(jìn)行數(shù)據(jù)壓縮 DB2：表級壓縮 需要事先提供樣本數(shù)據(jù) 可在INSERT 操作過程中同時(shí)進(jìn)行壓縮,DB2行壓縮技術(shù)（一）,基于壓縮字典的壓縮方式 通過使用較少的數(shù)據(jù)庫頁來表示相同數(shù)據(jù),從而達(dá)到節(jié)省磁盤存儲空間的目的,DB2行壓縮技術(shù)（二）,行壓縮的不會明顯降低UPDATE的效率 由于數(shù)據(jù)占用的存儲空間少了，所以訪問數(shù)據(jù)所需要的IO也少了，而IO正是數(shù)據(jù)倉庫系統(tǒng)常見的瓶頸 與行壓縮關(guān)聯(lián)的成本取決于壓縮和解壓縮數(shù)據(jù)所需的額外 CPU 周期 可使用 REORG TABLE 命令來創(chuàng)建壓縮字典?在處理 REORG 命令時(shí),現(xiàn)有的所有表行都要被壓縮? Reorg完成之后，后續(xù)INSERT的數(shù)據(jù)會自動按照現(xiàn)有的壓縮字典來壓縮數(shù)據(jù)，如果數(shù)據(jù)業(yè)務(wù)邏輯發(fā)生了大的變更，壓縮效果不理想，可以重新執(zhí)行reorg操作 分區(qū)表是一個(gè)分區(qū)一個(gè)壓縮字典，所以分區(qū)表新分區(qū)的數(shù)據(jù)在沒有reorg過之前是不會自動壓縮的,DB2 VS Oracle,實(shí)驗(yàn)環(huán)境 OS: Windows Server 2003 64-bit Memory: 2G CPU: AMD opteron 865 processor 1.80G（8核） Oracle企業(yè)版 10g VS DB2版本：DB2 企業(yè)版 9.7 數(shù)據(jù)集: 中國移動GSM語音通話記錄（gsm_voic_cdr.dat），替換分隔符后總大小為4.247G,,實(shí)驗(yàn)效果,7,7,查詢語句 select count(*) from GSM where EXCHANGECODE = 8613900121‘ 更新語句 UPDATE GSM SET LACCODE = ‘GSM’ WHERE DURATION = 100 刪除語句 DELETE FROM GSM WHERE DURATION = 100,壓縮后的數(shù)據(jù)僅占原來的38.76%（Oracle）或24.55%（DB2） VARCHAR的結(jié)構(gòu)信息在未壓縮的DB2上占用較多空間 壓縮后數(shù)據(jù)增、刪、改、查的效率更高 移動數(shù)據(jù)上非常適合壓縮,索引優(yōu)化(一),索引的優(yōu)點(diǎn),1. 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。 2. 可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。 3. 可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。 4. 在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。 5. 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。,索引的缺點(diǎn),1. 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。 2. 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間。 3.當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。,索引優(yōu)化(二),DB2在用戶指定數(shù)據(jù)表主鍵時(shí)， 自動生成以主鍵為關(guān)鍵字的聚簇索引。建立其他索引時(shí)有以下策略： ( 1 )避免在小表上建立索引 因?yàn)樗饕木S護(hù)需要一定的代價(jià)，在表上進(jìn)行增刪改操作時(shí) ，索引需要重組，這就增加了數(shù)據(jù)庫的負(fù)擔(dān)， 如果對索引的插入或者更新?lián)p失的時(shí)間大于在查詢中節(jié)省下來的時(shí)間，那么使用索引就是一個(gè)不明智的決定。 ( 2 )在經(jīng)常進(jìn)行鏈接的列上建立索引， 并且字段類型保持一致多表鏈接查詢是數(shù)據(jù)庫中最復(fù)雜、 最耗時(shí)的操作之一。改進(jìn)多表鏈接查詢性能對系統(tǒng)性能的改進(jìn)起很大的作用。在鏈接屬性上存在索引時(shí)， D B 2中采用索引嵌套循環(huán)鏈接 ，否則 D B 2使用哈希鏈接。 ( 3 )在頻繁進(jìn)行 g r o u p b y ／o r d e r b y的列上建立索引 ( 4 )建立索引字段的列的長度盡量小，避免在 B l o b ／ C l o b類型上建立索引。,索引優(yōu)化(三),( 5 )在 S QL語句中頻繁進(jìn)行比較運(yùn)算的列上建立索引。 ( 6 )避免在選擇性太低的字段上建立索引，基數(shù)較大的列很適合用來做索引。選擇性太低指的是索引中同一索引值的對應(yīng)記錄太多， 在這種列上建立索引意義不大。DB 2最優(yōu)化處理器不會使用該列作為執(zhí)行計(jì)劃的一部分 。 ( 7 )建立組合索引需要注意索引列順序如果在 A， B兩列上順序建立組合索引以后， 那么在w h e r e 子句中帶有下列搜 索條件會使用此索引 ： 條件為 A； 條件為 A。 B 。如果條件僅為 B則不會使用此索引。對于多列索引，將查詢中引用最多的列放在定義的前面。 ( 8 )由少數(shù)窄列組成，列寬度較大的列不適合作索引。考慮到管理上的開銷，應(yīng)避免在索引中使用多于 5 個(gè)的列。 ( 9 )避免添加與已有的索引相似的索引。因?yàn)檫@樣會給優(yōu)化器帶來更多的工作，并且會降慢更新操作的速度。相反，我們應(yīng)該修改已有的索引，使其包含附加的列。,多維群集（ MDC ）索引(一),基于塊的索引，比常規(guī)索引小得多，掃描的時(shí)候更快 MDC 對性能的貢獻(xiàn)在于提高檢索數(shù)據(jù)的效率 MDC 塊索引意味著需要的 RID 索引更少 由于新行是插在表中具有近似值的行附近的位置，因此數(shù)據(jù)仍然是聚合的，而不需要運(yùn)行 REORG 實(shí)用程序 由于新行是插在表中具有近似值的行附近的位置，所以MDC表無法創(chuàng)建群集索引(cluster index)，索引的群集率也比較低，類似serv_id = 1100000000000000 and serv_id 1200000000000000 的索引訪問效率比較低。按照目前倉庫的應(yīng)用現(xiàn)狀，清單類的表不適合建MDC 要避免mdc字段被頻繁的更新，因?yàn)橐鸨桓碌男袕脑瓉淼膲K遷移到新的塊,多維群集（ MDC ）索引(二),MDC維的定義原則 用于范圍、等于或 IN 列表謂詞 用于轉(zhuǎn)入、轉(zhuǎn)出或其他大規(guī)模的行刪除 粗粒度，也就是說不同的值很少的列 MDC維最多可以使用16列的組合 典型的應(yīng)用設(shè)計(jì)是用一個(gè)表示日期的列作為一個(gè) MDC 維，再加上 0 到 3 個(gè)其他列作為其他維，例如 地域(area) 和 產(chǎn)品類型(product_type),多維群集（ MDC ）索引(三),CREATE TABLE “XJDSS“.“LINGUO_MD_CU_COLLIGATE_ATTRI_DAY_01“ (“STAT_DATE“ DECIMAL(8, 0), “SERV_ID“ DECIMAL(18, 0), ) ORGANIZE BY DIMENSIONS (STAT_DATE,USER_STATUS) DATA CAPTURE NONE IN “BAS_WH_MINE“ INDEX IN “BAS_WH_MINE“ PARTITIONING KEY (SERV_ID ) USING HASHING COMPRESS YES VALUE COMPRESSION; 系統(tǒng)會自動創(chuàng)建MDC索引,分區(qū)表（一）,與 MDC 類似，它也可以將具有近似值的行存儲在一起 分區(qū)表支持按照一個(gè)維將一個(gè)表分區(qū)成多個(gè)數(shù)據(jù)分區(qū) 通過分區(qū)表特性，用戶可以手動地定義每個(gè)數(shù)據(jù)分區(qū)，包括將被包括到那個(gè)分區(qū)的值的范圍 每個(gè)分區(qū)表分區(qū)是一個(gè)單獨(dú)的數(shù)據(jù)庫對象 因此，分區(qū)表支持為分區(qū)表附加和卸除數(shù)據(jù)分區(qū)。卸除的分區(qū)成為一個(gè)常規(guī)表。而且，必要時(shí)可以將每個(gè)數(shù)據(jù)分區(qū)放在它自己的表空間中。 在DROP分區(qū)表的時(shí)候是一個(gè)分區(qū)一個(gè)分區(qū)地卸載，分區(qū)多的情況下比常規(guī)表慢很多 分區(qū)表通過分區(qū)排除提高數(shù)據(jù)檢索性能 分區(qū)字段同樣不允許UPDATE操作,分區(qū)表（二）,分區(qū)表的每個(gè)表分區(qū)進(jìn)行reorg操作，但是要把該分區(qū)的數(shù)據(jù)卸載(detach)到小表，然后再安裝(attach)上去 RANGE分區(qū)，未指定的分區(qū)值不能INSERT到數(shù)據(jù)庫中 添加分區(qū)操作ALTER TABLE xjdss.linguo_md_cu_user_day_03 ADD PARTITION STAT_DATE20080601 STARTING FROM 20080601 INCLUSIVE ENDING AT 20080602; 添加帶數(shù)據(jù)分區(qū)的操作 ALTER TABLE xjdss.linguo_md_cu_user_day_03 ATTACH PARTITION STAT_DATE20080601 STARTING FROM 20080601 INCLUSIVE ENDING AT 20080602 EXCLUSIVE from XJDSS.MD_CU_USER_DAY20080601; 卸載分區(qū)(可以用戶清除數(shù)據(jù)) ALTER TABLE XJDSS.LINGUO_MD_CU_USER_DAY_03 DETACH PARTITION STAT_DATE20080601 INTO XJDSS.MD_CU_USER_DAY20080601 ;,分區(qū)表（三）,卸載分區(qū)(可以用戶清除數(shù)據(jù)) ALTER TABLE XJDSS.LINGUO_MD_CU_USER_DAY_03 DETACH PARTITION STAT_DATE20080601 INTO XJDSS.MD_CU_USER_DAY20080601 ; 分區(qū)轉(zhuǎn)入后該分區(qū)的狀態(tài)不正常 可以同構(gòu)完整性檢查來回復(fù) SET INTEGRITY FOR xjdss.linguo_md_cu_user_day_03 ALLOW WRITE ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN xjdss.linguo_md_cu_user_day_03 USE XJDSS.MD_CU_USER_DAY_tmp SET INTEGRITY FOR xjdss.linguo_md_cu_user_day_03 ALLOW WRITE ACCESS IMMEDIATE CHECKED; 分區(qū)表結(jié)構(gòu)在DDL中看不出來，可以從系統(tǒng)字典表中看出來 select * from syscat.datapartitions with ur,分區(qū)表（四）,建表語句(推薦) CREATE TABLE XJDSS.LINGUO_MD_CU_COLLIGATE_ATTRI_DAY_01 (“STAT_DATE“ DECIMAL(8, 0), “SERV_ID“ DECIMAL(18, 0), “MSISDN“ VARCHAR(15) ) DATA CAPTURE NONE IN “BAS_WH_MID01“ INDEX IN “BAS_WH_MID01_IDX“ PARTITIONING KEY (SERV_ID ) USING HASHING partition by range (stat_date) ( PARTITION STAT_DATE20081001 STARTING FROM 20081001 INCLUSIVE ENDING AT 20081002 EXCLUSIVE ,PARTITION STAT_DATE20081002 STARTING FROM 20081002 INCLUSIVE ENDING AT 20081003 EXCLUSIVE ) COMPRESS YES VALUE COMPRESSION;,SQL優(yōu)化(一),18,針對5月6日運(yùn)行時(shí)間最長的100個(gè)腳本：共198個(gè)文件夾，每個(gè)文件夾中包含1~6個(gè)Perl程序，SQL語句嵌入到Perl程序中對數(shù)據(jù)庫進(jìn)行操作,現(xiàn)狀1：插入語句嵌入到循環(huán)中，每次循環(huán)插入一條語句，然后斷開與數(shù)據(jù)庫的聯(lián)系，進(jìn)行非數(shù)據(jù)庫操作,改進(jìn)建議1：在每次與數(shù)據(jù)庫的連接中，盡可能多地執(zhí)行插入操作，減少與數(shù)據(jù)庫連接的次數(shù),,現(xiàn)狀2：不少插入語句和文件操作，或者插入語句和字符串操作交替運(yùn)行 INSERT INTO $PDATADB.$table_target($TRG_COL_LIST) SELECT $TRG_COL_LIST FROM $table_today ; 交替著：$UNIT_DATTM = substr($DATA_FNAME,8,8);,改進(jìn)建議2：在插入數(shù)據(jù)的過程中，盡可能在下一次插入語句之前不要進(jìn)行其它文件I/O 或字符處理，全部集中最后一同處理,,SQL優(yōu)化(二),19,現(xiàn)狀3：DELETE FROM $PDATADB.$table_target WHERE $FILTER INSERT INTO $PDATADB.$table_target($TRG_COL_LIST),改進(jìn)建議3：使用UPDATE代替DELETE和INSERT組合 ，減少數(shù)據(jù)庫I/O,,現(xiàn)狀4：WHERE子句中，直接在列名上使用函數(shù)，無法利用索引 where function(colName) operator Value ；,改進(jìn)建議4：函數(shù)轉(zhuǎn)移到值上 where colName operator function(Value),,現(xiàn)狀5：查詢語句的WHERE子句中，直接在沒有索引的列名上使用函數(shù) char(trim(tablename))=char(trim(tb_10400_04002_s_20041007…)),改進(jìn)建議5：進(jìn)行語句的等價(jià)轉(zhuǎn)換，提高效率： tablename like ’% tb_10400_04002_s_20041007…% ’,,現(xiàn)狀6：統(tǒng)計(jì)表中記錄的個(gè)數(shù)，大部分情況使用了 select count(*),改進(jìn)建議6：進(jìn)行語句的等價(jià)轉(zhuǎn)換，盡量利用索引進(jìn)行統(tǒng)計(jì)：SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table_name) AND indid 2,,SQL語句索引的利用,采用函數(shù)處理的字段不能利用索引，如： substr(hbs_bh,1,4)=’5400’，優(yōu)化處理：hbs_bh like ‘5400%’ 進(jìn)行了顯式或隱式的運(yùn)算的字段不能進(jìn)行索引，如： ss_df+2050，優(yōu)化處理：ss_df30 ‘X’||hbs_bh’X5400021452’，優(yōu)化處理：hbs_bh’5400021542’ 條件內(nèi)包括了多個(gè)本表的字段運(yùn)算時(shí)不能進(jìn)行索引，如： ys_dfcx_df，無法進(jìn)行優(yōu)化 qc_bh||kh_bh=’5400250000’，優(yōu)化處理：qc_bh=’5400’ and kh_bh=’250000’,表的三種Join方法(NLJOIN HSJOIN MSJOIN ）,對于被連接的數(shù)據(jù)子集較小的情況，nested loop連接是個(gè)較好的選擇。nested loop就是掃描一個(gè)表，每讀到一條記錄，就根據(jù)索引去另一個(gè)表里面查找，沒有索引一般就不會是 nested loops。 Hash join是大數(shù)據(jù)集連接時(shí)的常用方式。優(yōu)化器掃描小表（或數(shù)據(jù)源），利用連接鍵（也就是根據(jù)連接字段計(jì)算hash 值）在內(nèi)存中建立hash表，然后掃描大表，每讀到一條記錄就來探測hash表一次，找出與hash表匹配的行。 對連接的每個(gè)表做table access full；對table access full的結(jié)果按照連接鍵進(jìn)行排序；進(jìn)行msjoin對排序結(jié)果進(jìn)行合并。 因?yàn)槠渑判虺杀靖?，大多為hash join替代。,SQL優(yōu)化效果,實(shí)驗(yàn)環(huán)境：處理器AMD865，主頻1.8GHz，內(nèi)存31.8GB 實(shí)驗(yàn)數(shù)據(jù)：每表包含10萬條記錄,