開發(fā)部數(shù)據(jù)庫及數(shù)據(jù)操作培訓資料.ppt

數(shù)據(jù)庫及數(shù)據(jù)操作培訓資料 演示者 施運淵 培訓大綱 第1課 關系型數(shù)據(jù)庫簡單介紹關系型數(shù)據(jù)庫及常用關系型數(shù)據(jù)庫的介紹 第2課 表表和字段的命名規(guī)范 字段數(shù)據(jù)類型 建表原則等 第3課 視圖視圖命名規(guī)范 索引視圖 可更新視圖 第4課 索引索引創(chuàng)建原則 哪些情況可能無法使用到索引 第5課 觸發(fā)器觸發(fā)器簡單說明 使用情況 培訓大綱 第6課 存儲過程存儲過程說明 使用情況 第7課 常用函數(shù)一些常用的函數(shù) 第8課 性能優(yōu)化如何著手優(yōu)化性能 第8課 常用技巧開發(fā)時使用到的一些技巧 第1課 關系型數(shù)據(jù)庫 關系型數(shù)據(jù)庫 關系數(shù)據(jù)庫是建立在集合代數(shù)基礎上 應用數(shù)學方法來處理數(shù)據(jù)庫中的數(shù)據(jù) 現(xiàn)實世界中的各種實體以及實體之間的各種聯(lián)系均用關系模型來表示 關系模型由關系數(shù)據(jù)結構 關系操作集合 關系完整性約束三部分組成 關系型數(shù)據(jù)庫以行和列的形式存儲數(shù)據(jù) 以便于用戶理解 儲存與管理數(shù)據(jù)的基本形式是二維表 使用結構化查詢語言 SQL 作為查詢工具 第1課 關系型數(shù)據(jù)庫 常用關系型數(shù)據(jù)庫 OracleSQLServerSybaseDB2MySQL 第2課 數(shù)據(jù)表 表的定義表 TABLE 是數(shù)據(jù)庫中用來存儲數(shù)據(jù)的對象 是有結構的數(shù)據(jù)的集合 是整個數(shù)據(jù)庫系統(tǒng)的基礎 表命名規(guī)則表名用T 開頭 表名長度不能超過30個字符 表名中含有單詞全部采用單數(shù)形式 單詞要大寫 多個單詞間用下劃線 進行連接 若庫中有多個系統(tǒng) 表名采用系統(tǒng)名稱 單詞或多個單詞 系統(tǒng)名是開發(fā)系統(tǒng)的縮寫 如VNET 表中含有的單詞建議用完整的單詞 如果導致表名長度超過30個字符 則從最后一個單詞開始 依次向前采用該單詞的縮寫 第2課 數(shù)據(jù)表 字段命名規(guī)則字段名長度不能超過30個字符 字段名中含有單詞全部采用單數(shù)形式 單詞要大寫 多個單詞間用下劃線 進行連接 字段名中含有的單詞建議用完整的單詞 如果導致長度超過30個字符 則從最后一個單詞開始 依次向前采用該單詞的縮寫字段類型設置原則按實際業(yè)務需要來選擇數(shù)據(jù)類型 例如需要參與計算的就用數(shù)字型 與時間有關的就用日期型 切勿所有字段全設置成一種類型 數(shù)據(jù)表的關聯(lián)字段必須是同種類型 字段長度也需與業(yè)務聯(lián)系 禁止盲目設置為最大值 第2課 數(shù)據(jù)表 常用字段類型字符型 Varchar 用來存放可變長度的字符串 一般最大長度在2000 4000左右 絕大部分字符串均可使用此類型 推薦使用 Char 定長的字符串 長度同Varchar Text Clob 用來存放可變長度的字符串 幾乎無上限 LONG Oracle專有 超長字符串 但不支持Like 并且每張表只能有1個LONG型字段 準備廢棄的字段類型 不推薦 總結 在大部分情況下優(yōu)先使用Varchar類型 因為其適應性最好 Char可使用在長度范圍變化較小的情況下 因為其效率較高 但是由于是定長的字段 所以不足部分會以空格填充 編程時要特別注意 并且占用空間也較大 Nvarchar和Nchar與環(huán)境變量NLS指定的語言集密切相關 所以不推薦 第2課 數(shù)據(jù)表 常用字段類型數(shù)字型 Integer 整數(shù)型 4字節(jié) 值為 2 31 2 31 1Decimal p s 數(shù)字數(shù)據(jù) 固定精度為P 寬度為S 優(yōu)先使用FLOAT 浮點數(shù)類型 NUMBER 38 雙精度 運算可能會產(chǎn)生問題 REAL 實數(shù)類型 NUMBER 63 精度更高總結 在大部分情況下優(yōu)先使用Decimal p s 類型 因為其適應性最好 Integer用于確認數(shù)據(jù)為整數(shù)情況下 第2課 數(shù)據(jù)表 常用字段類型日期型 Datetime 描述某天的日期和時刻 二進制型 Image Blob 存儲二進制數(shù)據(jù) LongRAW Oracle獨有 準備廢棄的字段類型 不推薦 第2課 數(shù)據(jù)表 主鍵 外鍵 非空字段 默認數(shù)據(jù)及備注主鍵 所有的表必須要有主鍵 如果沒有業(yè)務邏輯上的主鍵 也需要創(chuàng)建一個無意義列作為主鍵 建議使用一個字段作為主鍵 可提高索引的大小及效率 外鍵 沒有必要的話不推薦設置外鍵 校驗回滾等操作由程序來控制 過多使用外鍵只會增加系統(tǒng)的復雜度 非空字段 非空字段可以在數(shù)據(jù)層面對業(yè)務數(shù)據(jù)進行校驗 校驗工作主要還是在程序里完成 非空字段只是作為最后的檢驗手段 默認數(shù)據(jù) 數(shù)字型 日期型建議盡可能設置默認值 可以減少因為數(shù)據(jù)原因而引起的程序錯誤 備注 所有字段都必須填寫備注 建表原則哈哈 第2課 數(shù)據(jù)表 建表原則與實體相關 也就是說一張表能對應現(xiàn)實世界的一種事物在某一方面的屬性或一種事務在某一方面的屬性 簡單來說一個員工 事物 其基本信息就應該是獨立的一張表 一次員工的薪資調整 事務 也是獨立的一張表 正確理解一對一 一對多 多對多的關系 一對一 用戶的通訊方式 包括手機 居住地址等等 與用戶的基本信息 姓名 性別 等就是一對一的關系 建議將一對一的數(shù)據(jù)存儲在一張表內 增加表的長度而減少表的數(shù)量 一對多 用戶基本信息與用戶履歷就是一對多關系 可使用2張表 用戶基本信息表 用戶履歷表 來存儲 多對多 人員與崗位之間就是多對多關系 一般使用3張表來存儲這種結構 人員表 崗位表 人員崗位關系表 第2課 數(shù)據(jù)表 建表原則通俗地理解三個范式 通俗地理解是夠用的理解 并不是最科學最準確的理解 第一范式 1NF是對屬性的原子性約束 要求屬性具有原子性 不可再分解 第二范式 2NF是對記錄的惟一性約束 要求記錄有惟一標識 即實體的惟一性 第三范式 3NF是對字段冗余性的約束 即任何字段不能由其他字段派生出來 它要求字段沒有冗余 沒有冗余的數(shù)據(jù)庫設計可以做到 但是 沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫 有時為了提高運行效率 就必須降低范式標準 適當保留冗余數(shù)據(jù) 第2課 數(shù)據(jù)表 建表原則計算列 列有兩種類型 數(shù)據(jù)列和計算列 數(shù)據(jù)列指的是原始采樣而得的數(shù)據(jù) 計算列是指對數(shù)據(jù)列通過一定的公式計算加工而得出的數(shù)據(jù)列 例如 單價 數(shù)量屬于數(shù)據(jù)列 總價 單價 數(shù)量就是計算列 根據(jù)業(yè)務需求 對于常用的計算數(shù)據(jù)我們可以通過增加冗余的計算列來減少查詢的時間 切記計算列需要使用觸發(fā)器或者程序來保持其正確性 正確認識數(shù)據(jù)冗余 增加數(shù)據(jù)冗余的目的是為了減少表關聯(lián)從而提高檢索速度 切記冗余列需要使用觸發(fā)器或者程序來保持其正確性 在以下情況下可以考慮使用數(shù)據(jù)冗余 當查詢的主表或從表中有一張或多張表數(shù)據(jù)量巨大 參與關聯(lián)的表太多 主要業(yè)務數(shù)據(jù) 也就是用戶經(jīng)常查詢的數(shù)據(jù) 第2課 數(shù)據(jù)表 建表原則三少原則 一個數(shù)據(jù)庫中表的個數(shù)越少越好 只有表的個數(shù)少了 才能說明系統(tǒng)的E R圖少而精 去掉了重復的多余的實體 形成了對客觀世界的高度抽象 進行了系統(tǒng)的數(shù)據(jù)集成 防止了打補丁式的設計 一個表中組合主鍵的字段個數(shù)越少越好 因為主鍵的作用 一是建主鍵索引 二是做為子表的外鍵 所以組合主鍵的字段個數(shù)少了 不僅節(jié)省了運行時間 而且節(jié)省了索引存儲空間 一個表中的字段個數(shù)越少越好 只有字段的個數(shù)少了 才能說明在系統(tǒng)中不存在數(shù)據(jù)重復 且很少有數(shù)據(jù)冗余 更重要的是督促讀者學會 列變行 這樣就防止了將子表中的字段拉入到主表中去 在主表中留下許多空余的字段 所謂 列變行 就是將主表中的一部分內容拉出去 另外單獨建一個子表 第2課 數(shù)據(jù)表 建表原則三少原則 一個數(shù)據(jù)庫中表的個數(shù)越少越好 只有表的個數(shù)少了 才能說明系統(tǒng)的E R圖少而精 去掉了重復的多余的實體 形成了對客觀世界的高度抽象 進行了系統(tǒng)的數(shù)據(jù)集成 防止了打補丁式的設計 一個表中組合主鍵的字段個數(shù)越少越好 因為主鍵的作用 一是建主鍵索引 二是做為子表的外鍵 所以組合主鍵的字段個數(shù)少了 不僅節(jié)省了運行時間 而且節(jié)省了索引存儲空間 一個表中的字段個數(shù)越少越好 只有字段的個數(shù)少了 才能說明在系統(tǒng)中不存在數(shù)據(jù)重復 且很少有數(shù)據(jù)冗余 更重要的是督促讀者學會 列變行 這樣就防止了將子表中的字段拉入到主表中去 在主表中留下許多空余的字段 所謂 列變行 就是將主表中的一部分內容拉出去 另外單獨建一個子表 第3課 視圖 視圖的作用視圖能簡化用戶的操作 主要功能 視圖機制可以使用戶以不同的方式查詢同一數(shù)據(jù) 視圖對數(shù)據(jù)庫重構提供了一定程度的邏輯獨立性 視圖可以對機密的數(shù)據(jù)提供安全保護 命名規(guī)則名稱用VW 開頭 長度不能超過30個字符 名稱中含有單詞全部采用單數(shù)形式 單詞要大寫 多個單詞間用下劃線 進行連接 名稱中含有的單詞建議用完整的單詞 如果導致長度超過30個字符 則從最后一個單詞開始 依次向前采用該單詞的縮寫 第3課 視圖 索引視圖對于標準視圖而言 為每個引用視圖的查詢動態(tài)生成結果集的開銷很大 特別是對于那些涉及對大量行進行復雜處理 如聚合大量數(shù)據(jù)或聯(lián)接許多行 的視圖 如果在查詢中頻繁地引用這類視圖 可通過對視圖創(chuàng)建唯一聚集索引來提高性能 對視圖創(chuàng)建唯一聚集索引后 結果集將存儲在數(shù)據(jù)庫中 就像帶有聚集索引的表一樣 如果很少更新基礎數(shù)據(jù) 則索引視圖的效果最佳 維護索引視圖的成本可能高于維護表索引的成本 索引視圖可以提高下列查詢類型的性能 處理大量行的聯(lián)接和聚合 許多查詢經(jīng)常執(zhí)行的聯(lián)接和聚合操作 決策支持工作負荷 第3課 視圖 索引視圖索引視圖通常不會提高下列查詢類型的性能 具有大量寫操作的OLTP系統(tǒng) 具有大量更新的數(shù)據(jù)庫 不涉及聚合或聯(lián)接的查詢 GROUPBY鍵具有高基數(shù)度的數(shù)據(jù)聚合 GROUPBYID 語法 CREATETABLEwide tbl aintPRIMARYKEY bint zint CREATEVIEWv abcWITHSCHEMABINDINGASSELECTa b cFROMdbo wide tblWHEREaBETWEEN0AND1000CREATEUNIQUECLUSTEREDINDEXi abcONv abc a 第3課 視圖 可更新視圖利用視圖進行數(shù)據(jù)增 刪 改操作 會受到一定的限制 由兩個以上的基本表導出的視圖 視圖的字段來自字段表達式函數(shù) 視圖定義中有嵌套查詢 在一個不允許更新的視圖上定義的視圖 第4課 索引 定義說明使用索引可快速訪問數(shù)據(jù)庫表中的特定信息 索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結構 例如employee表的姓 lname 列 如果要按姓查找特定職員 與必須搜索表中的所有行相比 索引會幫助您更快地獲得該信息 索引是一個單獨的 物理的數(shù)據(jù)庫結構 它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單 索引提供指向存儲在表的指定列中的數(shù)據(jù)值的指針 然后根據(jù)您指定的排序順序對這些指針排序 數(shù)據(jù)庫使用索引的方式與您使用書籍中的索引的方式很相似 它搜索索引以找到特定值 然后順指針找到包含該值的行 第4課 索引 索引的優(yōu)點大大加快數(shù)據(jù)的檢索速度 創(chuàng)建唯一性索引 保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性 加速表和表之間的連接 在使用分組和排序子句進行數(shù)據(jù)檢索時 可以顯著減少查詢中分組和排序的時間 索引的缺點索引需要占物理空間 當對表中的數(shù)據(jù)進行增加 刪除和修改的時候 索引也要動態(tài)的維護 降低了數(shù)據(jù)的維護速度 錯誤的索引可能會減慢查詢速度 第4課 索引 索引類型唯一索引 唯一索引是不允許其中任何兩行具有相同索引值的索引 主鍵索引 在數(shù)據(jù)庫關系圖中為表定義主鍵將自動創(chuàng)建主鍵索引 主鍵索引是唯一索引的特定類型 該索引要求主鍵中的每個值都唯一 當在查詢中使用主鍵索引時 它還允許對數(shù)據(jù)的快速訪問 聚集索引 在聚集索引中 表中行的物理順序與鍵值的邏輯 索引 順序相同 一個表只能包含一個聚集索引 非簇集索引 普通索引 第4課 索引 建索引原則經(jīng)常做為條件查詢的字段加上索引 如果幾個字段同時做為查詢時就建復合索引 不要索引常用的小型表 數(shù)據(jù)量小的表不需要索引 500行 不要在大字段上建立索引如文本型之類的 對于查詢中很少涉及的列或者重復值比較多的列 不要建立索引 對于按范圍查詢的列 最好建立索引 如交易日期等 表中若有主鍵或者外鍵 一定要為其建立索引 在SQL語句中經(jīng)常進行GROUPBY ORDERBY的字段上建立索引 不要過度索引 修改表時數(shù)據(jù)庫需要更新索引 過度的索引不但耗費額外的空間 而且降低了修改更新操作的效率 第4課 索引 無法使用到索引的一些情況當WHERE條件中的字段是類似column1isnull或者column1isnotnull是 即便column1上面本來有索引也不會用到 對列進行數(shù)據(jù)加工后再做比較 例如 column1 column2 aaabbb Substr column1 1 aaa 等Like的字符串中第一個字符如果是 則用不到索引 例如 Column1like aaa 是可以的Column1like aaa 用不到也用不到索引有時可以采取適當措施改寫后可以用到索引 例 column1有3個值 A B C 三個值的分布為 A 10 B 80 C 10 則column1 B 可以改寫為column1in A C 第4課 索引 無法使用到索引的一些情況如果能不用到排序 則盡量避免排序 用到排序的情況有UNION 可用UNIONALL操作符替代UNION 因為UNIONALL操作只是簡單的將兩個結果合并后就返回 OrderbyGroupbyDistinctIn有時候也會用到排序確實要排序的時候也盡量要排序小數(shù)據(jù)量 盡量讓排序在內存中執(zhí)行 第5課 觸發(fā)器 定義說明觸發(fā)器 trigger 是個特殊的存儲過程 它的執(zhí)行不是由程序調用 也不是手工啟動 而是由事件來觸發(fā) 比如當對一個表進行操作 insert delete update 時就會激活它執(zhí)行 觸發(fā)器經(jīng)常用于加強數(shù)據(jù)的完整性約束和業(yè)務規(guī)則等 財務 人事等系統(tǒng)較多使用 優(yōu)點觸發(fā)器可通過數(shù)據(jù)庫中的相關表實現(xiàn)級聯(lián)更改 預編譯 已優(yōu)化 效率較高 避免了SQL語句在網(wǎng)絡傳輸然后再解釋的低效率 安全 不會有SQL語句注入問題存在 不需要修改程序重新編譯 第5課 觸發(fā)器 缺點可移植性差 觸發(fā)器排錯困難 而且數(shù)據(jù)容易造成不一致 后期維護不方便 影響數(shù)據(jù)庫的結構 同時增加了維護的復雜程序 語法create orreplace trigger觸發(fā)器名觸發(fā)時間觸發(fā)事件on表名 foreachrow 第5課 觸發(fā)器 觸發(fā)時間 指明觸發(fā)器何時執(zhí)行before 表示在數(shù)據(jù)庫動作之前觸發(fā)器執(zhí)行 after 表示在數(shù)據(jù)庫動作之后出發(fā)器執(zhí)行 觸發(fā)事件 指明哪些數(shù)據(jù)庫動作會觸發(fā)此觸發(fā)器insert 數(shù)據(jù)庫插入會觸發(fā)此觸發(fā)器 update 數(shù)據(jù)庫修改會觸發(fā)此觸發(fā)器 delete 數(shù)據(jù)庫刪除會觸發(fā)此觸發(fā)器 表名 數(shù)據(jù)庫觸發(fā)器所在的表 foreachrow 對表的每一行觸發(fā)器執(zhí)行一次 如果沒有這一選項 則只對整個表執(zhí)行一次 第5課 觸發(fā)器 觸發(fā)器無法使用的語句CREATE語句 如 CREATETABLE CREATEINDEX等 ALTER語句 如 ALTERTABLE ALTERINDEX等 DROP語句 如 DROPTABLE DROPINDEX等 第6課 存儲過程 定義說明存儲過程 StoredProcedure 是一組為了完成特定功能的SQL語句集 經(jīng)編譯后存儲在數(shù)據(jù)庫中 用戶通過指定存儲過程的名字并給出參數(shù) 如果該存儲過程帶有參數(shù) 來執(zhí)行它 存儲過程是數(shù)據(jù)庫中的一個重要對象 任何一個設計良好的數(shù)據(jù)庫應用程序都應該用到存儲過程 優(yōu)點預編譯 已優(yōu)化 效率較高 避免了SQL語句在網(wǎng)絡傳輸然后再解釋的低效率 安全 不會有SQL語句注入問題存在 不需要修改程序重新編譯 第6課 存儲過程 缺點可移植性差 調試困難 影響數(shù)據(jù)庫的結構 同時增加了維護的復雜程序 語法CREATEPROCEDURE 擁有者 存儲過程名 程序編號 參數(shù) 1 參數(shù) 1024 WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION AS程序行 第7課 常用函數(shù) 數(shù)字函數(shù)沒有特別特殊的 大家可以參考手冊 字符串函數(shù)字符串連接符Oracle Sybase SQLServer MySQL 無字符串連接函數(shù) CONCAT str1 str2 str3 INSTR C1 C2 I J 在一個字符串中搜索指定的字符 返回發(fā)現(xiàn)指定的字符的位置 C1被搜索的字符串C2希望搜索的字符串 第7課 常用函數(shù) 字符串函數(shù)LENGTH 返回字符串的長度LOWER 返回字符串 并將所有的字符小寫UPPER 返回字符串 并將所有的字符大寫LTRIM和RTRIM LTRIM刪除左邊出現(xiàn)的字符串 RTRIM刪除右邊出現(xiàn)的字符串SUBSTR string start count 取子字符串 從start開始 取count個REPLACE string s1 s2 string希望被替換的字符或變量 s1被替換的字符串 s2要替換的字符串 第7課 常用函數(shù) 日期函數(shù)當前日期時間 Oracle SysdateSQLServer Sybase GetDate MySql Now curedate 等等 不同的是這些都不能作為字段默認值 默認值必須使用current timestamp 但是current timestamp只用在timestamp的列 對datetime列無效 日期轉換函數(shù) Oracle TO CHAR 日期字段 YYYY MM DDHH24 MI SS TO DATE 日期格式字符串 該字符串的格式 SQLServer Sybase 字符串可以直接當做日期來插入 格式化獲取日期用Convert Char 長度 日期字段 參數(shù) Char代表獲取的字符長度 參數(shù)有很多 常用20 108 120 111等 第7課 常用函數(shù) 日期函數(shù)兩個日期之差 Oracle 日期1 日期2 返回帶小數(shù)點的天數(shù)SQLServer Sybase DATEDIFF datepart startdate enddate datepart 日期差的單位 MySql DATEDIFF startdate enddate 返回帶小數(shù)點的天數(shù)日期添加時間間隔 Oracle 時間函數(shù) 天數(shù) 可以帶小數(shù) SQLServer Sybase DATEADD datepart Number 日期字段 datepart 日期單位 Number 增加的數(shù)字 可以為負 MySQL DATE ADD date INTERVALexprtype 第8課 性能優(yōu)化 在給定的系統(tǒng)硬件和系統(tǒng)軟件條件下 提高數(shù)據(jù)庫系統(tǒng)的運行效率的辦法是 1 在數(shù)據(jù)庫物理設計時 降低范式 增加冗余 少用觸發(fā)器 多用存儲過程 2 當計算非常復雜 而且記錄條數(shù)非常巨大時 例如一千萬條 復雜計算要先在數(shù)據(jù)庫外面 以文件系統(tǒng)方式用C 語言計算處理完成之后 最后才入庫追加到表中去 3 發(fā)現(xiàn)某個表的記錄太多 例如超過一千萬條 則要對該表進行水平分割 水平分割的做法是 以該表主鍵PK的某個值為界線 將該表的記錄水平分割為兩個表 若發(fā)現(xiàn)某個表的字段太多 例如超過八十個 則垂直分割該表 將原來的一個表分解為兩個表 第9課 常用技巧 SQL語句性能一個SQL語句不要關聯(lián)太多的表 如果其中有大數(shù)據(jù)量的表時 盡量減少該表與其他表的關聯(lián) Where條件禁用或少用函數(shù)及字段數(shù)據(jù)操作 因為這樣無法使用到索引 例如SELECT FROMAwhereprice num 1000 Where子句順序對性能沒有影響 可能一些老版本的DBMS會有影響 為了統(tǒng)一 一般來說表之間的連接必須寫在其他WHERE條件之前 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾 SELECT子句中避免使用 ORACLE在解析的過程中 會將 依次轉換成所有的列名 這個工作是通過查詢數(shù)據(jù)字典完成的 這意味著將耗費更多的時間 第9課 常用技巧 SQL語句性能SELECT子句禁用或少用Distinct Distinct會引起全表掃描一般可以考慮用EXIST替換 EXISTS使查詢更為迅速 因為RDBMS核心模塊將在子查詢的條件一旦滿足后 馬上返回結果 例子 低效 SELECTDISTINCTDEPT NO DEPT NAMEFROMDEPTD EMPEWHERED DEPT NO E DEPT NO 高效 SELECTDEPT NO DEPT NAMEFROMDEPTDWHEREEXISTS SELECT X FROMEMPEWHEREE DEPT NO D DEPT NO sql語句用大寫的 因為oracle總是先解析sql語句 把小寫的字母轉換成大寫的再執(zhí)行 第9課 常用技巧 SQL語句性能用EXISTS替代IN 用NOTEXISTS替代NOTIN 在許多基于基礎表的查詢中 為了滿足一個條件 往往需要對另一個表進行聯(lián)接 在這種情況下 使用EXISTS 或NOTEXISTS 通常將提高查詢的效率 在子查詢中 NOTIN子句將執(zhí)行一個內部的排序和合并 無論在哪種情況下 NOTIN都是最低效的 因為它對子查詢中的表執(zhí)行了一個全表遍歷 為了避免使用NOTIN 我們可以把它改寫成外連接 OuterJoins 或NOTEXISTS 例子 高效 SELECT FROMEMP 基礎表 WHEREEMPNO 0ANDEXISTS SELECT X FROMDEPTWHEREDEPT DEPTNO EMP DEPTNOANDLOC MELB 低效 SELECT FROMEMP 基礎表 WHEREEMPNO 0ANDDEPTNOIN SELECTDEPTNOFROMDEPTWHERELOC MELB 第9課 常用技巧 SQL語句性能使用表的別名 Alias 當在SQL語句中連接多個表時 請使用表的別名并把別名前綴于每個Column上 這樣一來 就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤 用 替代 高效 SELECT FROMEMPWHEREDEPTNO 4低效 SELECT FROMEMPWHEREDEPTNO 3兩者的區(qū)別在于 前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO 3的記錄并且向前掃描到第一個DEPT大于3的記錄 避免在索引列上使用ISNULL和ISNOTNULL 第9課 常用技巧 SQL語句性能用UNION替換OR 適用于索引列 通常情況下 用UNION替換WHERE子句中的OR將會起到較好的效果 對索引列使用OR將造成全表掃描 注重 以上規(guī)則只針對多個索引列有效 假如有column沒有被索引 查詢效率可能會因為你沒有選擇OR而降低 在下面的例子中 LOC ID和REGION上都建有索引 高效 SELECTLOC ID LOC DESC REGIONFROMLOCATIONWHERELOC ID 10UNIONSELECTLOC ID LOC DESC REGIONFROMLOCATIONWHEREREGION MELBOURNE 低效 SELECTLOC ID LOC DESC REGIONFROMLOCATIONWHERELOC ID 10ORREGION MELBOURNE 假如你堅持要用OR 那就需要返回記錄最少的索引列寫在最前面 第9課 常用技巧 SQL語句性能總是使用索引的第一個列 假如索引是建立在多個列上 只有在它的第一個列 leadingcolumn 被where子句引用時 優(yōu)化器才會選擇使用該索引 這也是一條簡單而重要的規(guī)則 當僅引用索引的第二個列時 優(yōu)化器使用了全表掃描而忽略了索引用UNION ALL替換UNION 假如有可能的話 UNION ALL不會去除重復的行 慎用 索引只能告訴你什么存在于表中 而不能告訴你什么不存在于表中 使用count 或count 主鍵 而不要使用count column name 避免使用count distinctcolumn name 第9課 常用技巧 SQL語句性能等號右邊盡量不要使用字段名 如 select fromtbwherefield1 field3盡量少用子查詢 特別是相關子查詢 因為這樣會導致效率下降 使用count 或count 主鍵 而不要使用count column name 避免使用count distinctcolumn name 第9課 常用技巧 SQL語句調優(yōu)