sql實踐

① 用sql語句完成實踐課所要求的水費計算資料庫及其關聯表的創建

用inner join on
舉例說明一下吧：
－－－－－－－－－－－－－－
下面四種情況的一種。不是INNER JOIN的連接就是其他三種情況之一。
具體舉個例子
SELECT a.xh, a.yw, b.xx FROM a INNER JOIN b ON a.xh =b.xh
其中a.xh, a.yw, b.xx是顯示的結果中包含的欄位，FROM a INNER JOIN b 是結果中的欄位來自哪兩個怎麼關聯的表，ON a.xh =b.xh是兩個表關聯的關鍵欄位。這是最簡單的兩個表的連接查詢，必須要有這三個基本內容。至於排序、分組等功能的實現再另外加條件就行了。
_____________________________
兩表連接有四種情況：
INNER JOIN 只有在其他表中包含對應記錄(一個或多個)的記錄才出現在查詢結果中。即JOIN左右兩則所有的匹配記錄。
LEFT [OUTER] JOIN 在查詢結果中包含：JOIN 左側表中的所有記錄，以及JOIN 右側表中匹配的記錄。OUTER 關鍵字可被省略；包含 OUTER 強調這是一個外連接 (outer join)。
RIGHT [OUTER] JOIN 在查詢結果中包含：JOIN 右側表中的所有記錄，以及 JOIN 左側表中匹配的記錄。OUTER 關鍵字可被省略；包含 OUTER 強調這是一個外連接接 (outer join)。
FULL [OUTER] JOIN 在查詢結果中包含：JOIN 兩側所有的匹配記錄，和不匹配的記錄；包含 OUTER 強調這是一個外連接 (outer join)。
比如兩表A為語文成績，B為數學成績,通過學號XH連接，顯示A.XH,A.YW,B.XH,B.XX：
A： B：
xh yw xh xx
1 80 1 90
2 85 3 70
3 60 4 50
1)、inner join 的結果為
1 80 1 90
3 60 3 70

2)、right join的結果為
1 80 1 90
3 60 3 70
.null. .null. 4 50

3)、left joinr的結果為
xh yw xh xx
1 80 1 90
2 85 .null. .null
3 60 3 70

4)、full joinr的結果為
xh yw xh xx
1 80 1 90
2 85 .null. .null.
3 60 3 70
.null. .null. 4 50

② t-sql的實踐

創建一個表的時候，必須決定欄位定義所要使用的數據類型。數據類型定義了可以存儲在一個欄位中的數據種類。DBA可以使用數據類型來定義變數和存儲過程的輸入和輸出參數。你必須為每個欄位或變數選擇一個數據類型以適配於存儲在相應欄位或變數中的數據。另外，還需要考慮存儲需求並選擇高效率存儲的數據類型。舉個例子，想要存儲介於0到255的正數時通常要用tinyint替代smallint，int或bigint。這是因為tinyint是一個固定的1位元組欄位，而smallint為2位元組，int為4位元組還有bigint為一個8位元組的固定欄位。
選擇正確的數據類型還可以改善數據完整性。例如，如果為一個日期欄位使用datetime數據類型，那麼只有日期才能存儲在此欄位中。然而，如果為此欄位使用字元或數字數據類型，那麼最終的結果就可以在此欄位中存儲任何字元和數字類型的數據值，而它們並不代表一個日期。
最後，選擇正確的數據類型會帶來正確的執行計劃，從而改善資料庫性能。

③ 技術干貨：SQL on Hadoop在快手大數據平台的實踐與優化

快手大數據架構工程師鍾靚近日在 A2M 人工智慧與機器學習創新峰會分享了題為《SQL on Hadoop 在快手大數據平台的實踐與優化》的演講，主要從 SQL on Hadoop 介紹、快手 SQL on Hadoop 平台概述、SQL on Hadoop 在快手的使用經驗和改進分析、快手 SQL on Hadoop 的未來計劃四方面介紹了 SQL on Hadoop 架構。

SQL on Hadoop，顧名思義它是基於 Hadoop 生態的一個 SQL 引擎架構，我們其實常常聽到 Hive、SparkSQL、Presto、Impala 架構。接下來，我會簡單的描述一下常用的架構情況。

HIVE，一個數據倉庫系統。它將數據結構映射到存儲的數據中，通過 SQL 對大規模的分布式存儲數據進行讀、寫、管理。

根據定義的數據模式，以及輸出 Storage，它會對輸入的 SQL 經過編譯、優化，生成對應引擎的任務，然後調度執行生成的任務。

HIVE 當前支持的引擎類型有：MR、SPARK、TEZ。

基於 HIVE 本身的架構，還有一些額外的服務提供方式，比如 HiveServer2 與 MetaStoreServer 都是 Thrift 架構。

此外，HiveServer2 提供遠程客戶端提交 SQL 任務的功能，MetaStoreServer 則提供遠程客戶端操作元數據的功能。

Spark，一個快速、易用，以 DAG 作為執行模式的大規模數據處理的統一分析引擎，主要模塊分為 SQL 引擎、流式處理 、機器學習、圖處理。

SPARKSQL 基於 SPARK 的計算引擎，做到了統一數據訪問，集成 Hive，支持標准 JDBC 連接。SPARKSQL 常用於數據交互分析的場景。

SPARKSQL 的主要執行邏輯，首先是將 SQL 解析為語法樹，然後語義分析生成邏輯執行計劃，接著與元數據交互，進行邏輯執行計劃的優化，最後，將邏輯執行翻譯為物理執行計劃，即 RDD lineage，並執行任務。

PRESTO，一個互動式分析查詢的開源分布式 SQL 查詢引擎。

因為基於內存計算，PRESTO 的計算性能大於有大量 IO 操作的 MR 和 SPARK 引擎。它有易於彈性擴展，支持可插拔連接的特點。

業內的使用案例很多，包括 FaceBook、AirBnb、美團等都有大規模的使用。

我們看到這么多的 SQL on Hadoop 架構，它側面地說明了這種架構比較實用且成熟。利用 SQL on Hadoop 架構，我們可以實現支持海量數據處理的需求。

查詢平台每日 SQL 總量在 70 萬左右，DQL 的總量在 18 萬左右。AdHoc 集群主要用於交互分析及機器查詢，DQL 平均耗時為 300s；AdHoc 在內部有 Loacl 任務及加速引擎應用，所以查詢要求耗時較低。

ETL 集群主要用於 ETL 處理以及報表的生成。DQL 平均耗時為 1000s，DQL P50 耗時為 100s，DQL P90 耗時為 4000s，除上述兩大集群外，其它小的集群主要用於提供給單獨的業務來使用。

服務層是對上層進行應用的。在上層有四個模塊，這其中包括同步服務、ETL 平台、AdHoc 平台以及用戶程序。在調度上層，同樣也有四方面的數據，例如服務端日誌，對它進行處理後，它會直接接入到 HDFS 里，我們後續會再對它進行清洗處理；服務打點的數據以及資料庫信息，則會通過同步服務入到對應的數據源里，且我們會將元數據信息存在後端元數據系統中。

網頁爬取的數據會存入 hbase，後續也會進行清洗與處理。

HUE、NoteBook 主要提供的是互動式查詢的系統。報表系統、BI 系統主要是 ETL 處理以及常見的報表生成，額外的元數據系統是對外進行服務的。快手現在的引擎支持 MR、Presto 及 Spark。

管理系統主要用於管理我們當前的集群。HiveServer2 集群路由系統，主要用於引擎的選擇。監控系統以及運維系統，主要是對於 HiveServer2 引擎進行運維。

我們在使用 HiveServer2 過程中，遇到過很多問題。接下來，我會詳細的為大家闡述快手是如何進行優化及實踐的。

當前有多個 HiveServer2 集群，分別是 AdHoc 與 ETL 兩大集群，以及其他小集群。不同集群有對應的連接 ZK，客戶端可通過 ZK 連接 HiveServer2 集群。

為了保證核心任務的穩定性，將 ETL 集群進行了分級，分為核心集群和一般集群。在客戶端連接 HS2 的時候，我們會對任務優先順序判定，高優先順序的任務會被路由到核心集群，低優先順序的任務會被路由到一般集群。

BeaconServer 服務為後端 Hook Server 服務，配合 HS2 中的 Hook，在 HS2 服務之外實現了所需的功能。當前支持的模塊包括路由、審計、SQL 重寫、任務控制、錯誤分析、優化建議等。

•無狀態，BeaconServer 服務支持水平擴展。基於請求量的大小，可彈性調整服務的規模。

•配置動態載入，BeaconServer 服務支持動態配置載入。各個模塊支持開關，服務可動態載入配置實現上下線。比如路由模塊，可根據後端加速引擎集群資源情況，進行路由比率調整甚至熔斷。

•無縫升級，BeaconServer 服務的後端模塊可單獨進行下線升級操作，不會影響 Hook 端 HS2 服務。

•Hive 支持 SPARK 與 TEZ 引擎，但不適用於生產環境。

•SQL on Hadoop 的 SQL 引擎各有優缺點，用戶學習和使用的門檻較高。

•不同 SQL 引擎之間的語法和功能支持上存在差異，需要大量的測試和兼容工作，完全兼容的成本較高。

•不同 SQL 引擎各自提供服務會給數倉的血緣管理、許可權控制、運維管理、資源利用都帶來不便。

•在 Hive 中，自定義實現引擎。

•自動路由功能，不需要設置引擎，自動選擇適合的加速引擎。

•根絕規則匹配 SQL，只將兼容的 SQL 推給加速引擎。

•復用 HiveServer2 集群架構。

基於 HiveServer2，有兩種實現方式。JDBC 方式是通過 JDBC 介面，將 SQL 發送至後端加速引擎啟動的集群上。PROXY 方式是將 SQL 下推給本地的加速引擎啟動的 Client。

JDBC 方式啟動的後端集群，均是基於 YARN，可以實現資源的分時復用。比如 AdHoc 集群的資源在夜間會自動回收，作為報表系統的資源進行復用。

路由方案基於 HS2 的 Hook 架構，在 HS2 端實現對應 Hook，用於引擎切換；後端 BeaconServer 服務中實現路由 服務，用於 SQL 的路由規則的匹配處理。不同集群可配置不同的路由規則。

為了保證後算路由服務的穩定性，團隊還設計了 Rewrite Hook，用於重寫 AdHoc 集群中的 SQL，自動添加 LIMIT 上限，防止大數據量的 SCAN。

•易於集成，當前主流的 SQL 引擎都可以方便的實現 JDBC 與 PROXY 方式。再通過配置，能簡單的集成新的查詢引擎，比如 impala、drill 等。

•自動選擇引擎，減少了用戶的引擎使用成本，同時也讓遷移變得更簡單。並且在加速引擎過載 的情況下，可以動態調整比例，防止因過載 對加速性能的影響。

•自動降級，保證了運行的可靠性。SQL 路由支持 failback 模塊，可以根據配置選擇是否再路由引擎執行失敗後，回滾到 MR 運行。

•模塊復用，對於新增的引擎，都可以復用 HiveServer2 定製的血緣採集、許可權認證、並發鎖控制等方案，大大降低了使用成本。

•資源復用，對於 adhoc 查詢佔用資源可以分時動態調整，有效保證集群資源的利用率。

當查詢完成後，本地會輪詢結果文件，一直獲取到 LIMIT 大小，然後返回。這種情況下，當有大量的小文件存在，而大文件在後端的時候，會導致 Bad Case，不停與 HDFS 交互，獲取文件信息以及文件數據，大大拉長運行時間。

在 Fetch 之前，對結果文件的大小進行預排序，可以有數百倍的性能提升。

示例：當前有 200 個文件。199 個小文件一條記錄 a，1 個大文件混合記錄 a 與 test 共 200 條，大文件名 index 在小文件之後。

Hive 中有一個 SimpleFetchOptimizer 優化器，會直接生成 FetchTask，減小資源申請時間與調度時間。但這個優化會出現瓶頸。如果數據量小，但是文件數多，需要返回的條數多，存在能大量篩掉結果數據的 Filter 條件。這時候串列讀取輸入文件，導致查詢延遲大，反而沒起到加速效果。

在 SimpleFetchOptimizer 優化器中，新增文件數的判斷條件，最後將任務提交到集群環境，通過提高並發來實現加速。

示例：讀取當前 500 個文件的分區。優化後的文件數閾值為 100。

一個表有大量的子分區，它的 DESC 過程會與元數據交互，獲取所有的分區。但最後返回的結果，只有跟表相關的信息。

與元數據交互的時候，延遲了整個 DESC 的查詢，當元數據壓力大的時候甚至無法返回結果。

針對於 TABLE 的 DESC 過程，直接去掉了跟元數據交互獲取分區的過程，加速時間跟子分區數量成正比。

示例：desc 十萬分區的大表。

•復用 split 計算的數據，跳過 rece 估算重復統計輸入過程。輸入數據量大的任務，調度速率提升 50%。

•parquetSerde init 加速，跳過同一表的重復列剪枝優化，防止 map task op init 時間超時。

•新增 LazyOutputFormat，有 record 輸出再創建文件，避免空文件的產生，導致下游讀取大量空文件消耗時間。

•statsTask 支持多線程聚合統計信息，防止中間文件過多導致聚合過慢，增大運行時間。

•AdHoc 需要打開並行編譯，防止 SQL 串列編譯導致整體延遲時間增大的問題。

HS2 啟動時會對物化視圖功能進行初始化，輪詢整個元資料庫，導致 HS2 的啟動時間非常長，從下線狀態到重新上線間隔過大，可用性很差。

將物化視圖功能修改為延遲懶載入，單獨線程載入，不影響 HS2 的服務啟動。物化視圖支持載入中獲取已緩存信息，保證功能的可用性。

HS2 啟動時間從 5min+提升至<5s。

HS2 本身上下線成本較高，需要保證服務上的任務全部執行完成才能進行操作。配置的修改可作為較高頻率的操作，且需要做到熱載入。

在 HS2 的 ThriftServer 層我們增加了介面，與運維系統打通後，配置下推更新的時候自動調用，可實現配置的熱載入生效。

HiveServer2 的 scratchdir 主要用於運行過程中的臨時文件存儲。當 HS2 中的會話創建時，便會創建 scratchdir。在 HDFS 壓力大的時候，大量的會話會阻塞在創建 scratchdir 過程，導致連接數堆積至上限，最終 HS2 服務無法再連入新連接，影響服務可用性。

對此，我們先分離了一般查詢與 create temporay table 查詢的 scratch 目錄，並支持 create temporay table 查詢的 scratch 的懶創建。當 create temporay table 大量創建臨時文件，便會影響 HDFS NameNode 延遲時間的時候，一般查詢的 scratchdir HDFS NameNode 可以正常響應。

此外，HS2 還支持配置多 scratch，不同的 scratch 能設置載入比率，從而實現 HDFS 的均衡負載。

Hive 調度其中存在兩個問題。

一、子 Task 非執行狀態為完成情況的時候，若有多輪父 Task 包含子 Task，導致子 Task 被重復加入調度隊列。這種 Case，需要將非執行狀態修改成初始化狀態。

二、當判斷子 Task 是否可執行的過程中，會因為狀態檢測異常，無法正常加入需要調度的子 Task，從而致使查詢丟失 Stage。而這種 Case，我們的做法是在執行完成後，加入一輪 Stage 的執行結果狀態檢查，一旦發現有下游 Stage 沒有完成，直接拋出錯誤，實現查詢結果狀態的完備性檢查。

•HS2 實現了介面終止查詢 SQL。利用這個功能，可以及時終止異常 SQL。

•metastore JDOQuery 查詢優化，關鍵字異常跳過，防止元數據長時間卡頓或者部分異常查詢影響元數據。

•增加開關控制，強制覆蓋外表目錄，解決 insert overwrite 外表，文件 rename 報錯的問題。

•hive parquet 下推增加關閉配置，避免 parquet 異常地下推 OR 條件，導致結果不正確。

•executeForArray 函數 join 超大字元串導致 OOM，增加限制優化。

•增加根據 table 的 schema 讀取分區數據的功能，避免未級聯修改分區 schema 導致讀取數據異常。

•部分用戶並沒有開發經驗，無法處理處理引擎返回的報錯。

•有些錯誤的報錯信息不明確，用戶無法正確了解錯誤原因。

•失敗的任務排查成本高，需要對 Hadoop 整套系統非常熟悉。

•用戶的錯誤 SQL、以及需要優化的 SQL，大量具有共通性。人力維護成本高，但系統分析成本低。

SQL 專家系統基於 HS2 的 Hook 架構，在 BeaconServer 後端實現了三個主要的模塊，分別是 SQL 規則控制模塊、SQL 錯誤分析模塊，與 SQL 優化建議模塊。SQL 專家系統的知識庫，包含關鍵字、原因說明、處理方案等幾項主要信息，存於後端資料庫中，並一直積累。

通過 SQL 專家系統，後端可以進行查詢 SQL 的異常控制，避免異常 SQL 的資源浪費或者影響集群穩定。用戶在遇到問題時，能直接獲取問題的處理方案，減少了使用成本。

示例：空分區查詢控制。

SQL 專家系統能解決一部分 HS2 的任務執行的錯誤診斷需求，但是比如作業 健康 度、任務執行異常等問題原因的判斷，需要專門的系統來解決，為此我們設計了作業診斷系統。

作業診斷系統在 YARN 的層面，針對不同的執行引擎，對搜集的 Counter 和配置進行分析。在執行層面，提出相關的優化建議。

作業診斷系統的數據也能通過 API 提供給 SQL 專家系統，補充用於分析的問題原因。

作業診斷系統提供了查詢頁面來查詢運行的任務。以下是命中 map 輸入過多規則的任務查詢過程：

④ SQL如何快速處理海量數據

在以下的文章中，我將以「辦公自動化」系統為例，探討如何在有著1000萬條數據的MS SQL SERVER資料庫中實現快速的數據提取和數據分頁。以下代碼說明了我們實例中資料庫的「紅頭文件」一表的部分數據結構：

CREATE TABLE [dbo].[TGongwen] ( --TGongwen是紅頭文件表名

[Gid] [int] IDENTITY (1, 1) NOT NULL ,
--本表的id號，也是主鍵

[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
--紅頭文件的標題

[fariqi] [datetime] NULL ,
--發布日期

[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
--發布用戶

[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,

--需要瀏覽的用戶。每個用戶中間用分隔符「,」分開

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

下面，我們來往資料庫中添加1000萬條數據：

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通信科','通信科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,治安支隊,外事科','這是最先的25萬條記錄')

set @i=@i+1

end

GO

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','辦公室','辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,外事科','這是中間的25萬條記錄')

set @i=@i+1

end

GO

declare @h int

set @h=1

while @h<=100

begin

declare @i int

set @i=2002

while @i<=2003

begin

declare @j int

set @j=0

while @j<50

begin

declare @k int

set @k=0

while @k<50

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'通信科','辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,外事科','這是最後的50萬條記錄')

set @k=@k+1

end

set @j=@j+1

end

set @i=@i+1

end

set @h=@h+1

end

GO

declare @i int

set @i=1

while @i<=9000000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','通信科','通信科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,治安支隊,外事科','這是最後添加的900萬條記錄')

set @i=@i+1000000

end

GO

通過以上語句，我們創建了25萬條由通信科於2004年2月5日發布的記錄，25萬條由辦公室於2004年9月6日發布的記錄，2002年和2003年各100個2500條相同日期、不同分秒的由通信科發布的記錄（共50萬條），還有由通信科於2004年5月5日發布的900萬條記錄，合計1000萬條。

一、因情制宜，建立「適當」的索引

建立「適當」的索引是實現查詢優化的首要前提。

索引（index）是除表之外另一重要的、用戶定義的存儲在物理介質上的數據結構。當根據索引碼的值搜索數據時，索引提供了對數據的快速訪問。事實上，沒有索引,資料庫也能根據SELECT語句成功地檢索到結果，但隨著表變得越來越大，使用「適當」的索引的效果就越來越明顯。注意，在這句話中，我們用了「適當」這個詞，這是因為，如果使用索引時不認真考慮其實現過程，索引既可以提高也會破壞資料庫的工作性能。

（一）深入淺出理解索引結構

實際上，您可以把索引理解為一種特殊的目錄。微軟的SQL SERVER提供了兩種索引：聚集索引（clustered index，也稱聚類索引、簇集索引）和非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）。下面，我們舉例來說明一下聚集索引和非聚集索引的區別：

其實，我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查「安」字，就會很自然地翻開字典的前幾頁，因為「安」的拼音是「an」，而按照拼音排序漢字的字典是以英文字母「a」開頭並以「z」結尾的，那麼「安」字就自然地排在字典的前部。如果您翻完了所有以「a」開頭的部分仍然找不到這個字，那麼就說明您的字典中沒有這個字；同樣的，如果查「張」字，那您也會將您的字典翻到最後部分，因為「張」的拼音是「zhang」。也就是說，字典的正文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內容。

我們把這種正文內容本身就是一種按照一定規則排列的目錄稱為「聚集索引」。

如果您認識某個字，您可以快速地從自動中查到這個字。但您也可能會遇到您不認識的字，不知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據「偏旁部首」查到您要找的字，然後根據這個字後的頁碼直接翻到某頁來找到您要找的字。但您結合「部首目錄」和「檢字表」而查到的字的排序並不是真正的正文的排序方法，比如您查「張」字，我們可以看到在查部首之後的檢字表中「張」的頁碼是672頁，檢字表中「張」的上面是「馳」字，但頁碼卻是63頁，「張」的下面是「弩」字，頁面是390頁。很顯然，這些字並不是真正的分別位於「張」字的上下方，現在您看到的連續的「馳、張、弩」三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結果，然後再翻到您所需要的頁碼。

我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為「非聚集索引」。

通過以上例子，我們可以理解到什麼是「聚集索引」和「非聚集索引」。

進一步引申一下，我們可以很容易的理解：每個表只能有一個聚集索引，因為目錄只能按照一種方法進行排序。

（二）何時使用聚集索引或非聚集索引

下面的表總結了何時使用聚集索引或非聚集索引（很重要）。

動作描述

使用聚集索引

使用非聚集索引

列經常被分組排序

應

應

返回某范圍內的數據

應

不應

一個或極少不同值

不應

不應

小數目的不同值

應

不應

大數目的不同值

不應

應

頻繁更新的列

不應

應

外鍵列

應

應

主鍵列

應

應

頻繁修改索引列

不應

應

事實上，我們可以通過前面聚集索引和非聚集索引的定義的例子來理解上表。如：返回某范圍內的數據一項。比如您的某個表有一個時間列，恰好您把聚合索引建立在了該列，這時您查詢2004年1月1日至2004年10月1日之間的全部數據時，這個速度就將是很快的，因為您的這本字典正文是按日期進行排序的，聚類索引只需要找到要檢索的所有數據中的開頭和結尾數據即可；而不像非聚集索引，必須先查到目錄中查到每一項數據對應的頁碼，然後再根據頁碼查到具體內容。

（三）結合實際，談索引使用的誤區

理論的目的是應用。雖然我們剛才列出了何時應使用聚集索引或非聚集索引，但在實踐中以上規則卻很容易被忽視或不能根據實際情況進行綜合分析。下面我們將根據在實踐中遇到的實際問題來談一下索引使用的誤區，以便於大家掌握索引建立的方法。

1、主鍵就是聚集索引

這種想法筆者認為是極端錯誤的，是對聚集索引的一種浪費。雖然SQL SERVER默認是在主鍵上建立聚集索引的。

通常，我們會在每個表中都建立一個ID列，以區分每條數據，並且這個ID列是自動增大的，步長一般為1。我們的這個辦公自動化的實例中的列Gid就是如此。此時，如果我們將這個列設為主鍵，SQL SERVER會將此列默認為聚集索引。這樣做有好處，就是可以讓您的數據在資料庫中按照ID進行物理排序，但筆者認為這樣做意義不大。

顯而易見，聚集索引的優勢是很明顯的，而每個表中只能有一個聚集索引的規則，這使得聚集索引變得更加珍貴。

從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據查詢要求，迅速縮小查詢范圍，避免全表掃描。在實際應用中，因為ID號是自動生成的，我們並不知道每條記錄的ID號，所以我們很難在實踐中用ID號來進行查詢。這就使讓ID號這個主鍵作為聚集索引成為一種資源浪費。其次，讓每個ID號都不同的欄位作為聚集索引也不符合「大數目的不同值情況下不應建立聚合索引」規則；當然，這種情況只是針對用戶經常修改記錄內容，特別是索引項的時候會負作用，但對於查詢速度並沒有影響。

在辦公自動化系統中，無論是系統首頁顯示的需要用戶簽收的文件、會議還是用戶進行文件查詢等任何情況下進行數據查詢都離不開欄位的是「日期」還有用戶本身的「用戶名」。

通常，辦公自動化的首頁會顯示每個用戶尚未簽收的文件或會議。雖然我們的where語句可以僅僅限制當前用戶尚未簽收的情況，但如果您的系統已建立了很長時間，並且數據量很大，那麼，每次每個用戶打開首頁的時候都進行一次全表掃描，這樣做意義是不大的，絕大多數的用戶1個月前的文件都已經瀏覽過了，這樣做只能徒增資料庫的開銷而已。事實上，我們完全可以讓用戶打開系統首頁時，資料庫僅僅查詢這個用戶近3個月來未閱覽的文件，通過「日期」這個欄位來限製表掃描，提高查詢速度。如果您的辦公自動化系統已經建立的2年，那麼您的首頁顯示速度理論上將是原來速度8倍，甚至更快。

在這里之所以提到「理論上」三字，是因為如果您的聚集索引還是盲目地建在ID這個主鍵上時，您的查詢速度是沒有這么高的，即使您在「日期」這個欄位上建立的索引（非聚合索引）。下面我們就來看一下在1000萬條數據量的情況下各種查詢的速度表現（3個月內的數據為25萬條）：

（1）僅在主鍵上建立聚集索引，並且不劃分時間段：

Select gid,fariqi,neibuyonghu,title from tgongwen

用時：128470毫秒（即：128秒）

（2）在主鍵上建立聚集索引，在fariq上建立非聚集索引：

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用時：53763毫秒（54秒）

（3）將聚合索引建立在日期列（fariqi）上：

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用時：2423毫秒（2秒）

雖然每條語句提取出來的都是25萬條數據，各種情況的差異卻是巨大的，特別是將聚集索引建立在日期列時的差異。事實上，如果您的資料庫真的有1000萬容量的話，把主鍵建立在ID列上，就像以上的第1、2種情況，在網頁上的表現就是超時，根本就無法顯示。這也是我摒棄ID列作為聚集索引的一個最重要的因素。

得出以上速度的方法是：在各個select語句前加：declare @d datetime

set @d=getdate()

並在select語句後加：

select [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())

2、只要建立索引就能顯著提高查詢速度

事實上，我們可以發現上面的例子中，第2、3條語句完全相同，且建立索引的欄位也相同；不同的僅是前者在fariqi欄位上建立的是非聚合索引，後者在此欄位上建立的是聚合索引，但查詢速度卻有著天壤之別。所以，並非是在任何欄位上簡單地建立索引就能提高查詢速度。

從建表的語句中，我們可以看到這個有著1000萬數據的表中fariqi欄位有5003個不同記錄。在此欄位上建立聚合索引是再合適不過了。在現實中，我們每天都會發幾個文件，這幾個文件的發文日期就相同，這完全符合建立聚集索引要求的：「既不能絕大多數都相同，又不能只有極少數相同」的規則。由此看來，我們建立「適當」的聚合索引對於我們提高查詢速度是非常重要的。

3、把所有需要提高查詢速度的欄位都加進聚集索引，以提高查詢速度

上面已經談到：在進行數據查詢時都離不開欄位的是「日期」還有用戶本身的「用戶名」。既然這兩個欄位都是如此的重要，我們可以把他們合並起來，建立一個復合索引（compound index）。

很多人認為只要把任何欄位加進聚集索引，就能提高查詢速度，也有人感到迷惑：如果把復合的聚集索引欄位分開查詢，那麼查詢速度會減慢嗎？帶著這個問題，我們來看一下以下的查詢速度（結果集都是25萬條數據）：（日期列fariqi首先排在復合聚集索引的起始列，用戶名neibuyonghu排在後列）

（1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'

查詢速度：2513毫秒

（2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='辦公室'

查詢速度：2516毫秒

（3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='辦公室'

查詢速度：60280毫秒

從以上試驗中，我們可以看到如果僅用聚集索引的起始列作為查詢條件和同時用到復合聚集索引的全部列的查詢速度是幾乎一樣的，甚至比用上全部的復合索引列還要略快（在查詢結果集數目一樣的情況下）；而如果僅用復合聚集索引的非起始列作為查詢條件的話，這個索引是不起任何作用的。當然，語句1、2的查詢速度一樣是因為查詢的條目數一樣，如果復合索引的所有列都用上，而且查詢結果少的話，這樣就會形成「索引覆蓋」，因而性能可以達到最優。同時，請記住：無論您是否經常使用聚合索引的其他列，但其前導列一定要是使用最頻繁的列。

（四）其他書上沒有的索引使用經驗總結

1、用聚合索引比用不是聚合索引的主鍵速度快

下面是實例語句：（都是提取25萬條數據）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

使用時間：3326毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

使用時間：4470毫秒

這里，用聚合索引比用不是聚合索引的主鍵速度快了近1/4。

2、用聚合索引比用一般的主鍵作order by時速度快，特別是在小數據量情況下

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

用時：12936

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

用時：18843

這里，用聚合索引比用一般的主鍵作order by時，速度快了3/10。事實上，如果數據量很小的話，用聚集索引作為排序列要比使用非聚集索引速度快得明顯的多；而數據量如果很大的話，如10萬以上，則二者的速度差別不明顯。

3、使用聚合索引內的時間段，搜索時間會按數據占整個數據表的百分比成比例減少，而無論聚合索引使用了多少個

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

用時：6343毫秒（提取100萬條）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

用時：3170毫秒（提取50萬條）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

用時：3326毫秒（和上句的結果一模一樣。如果採集的數量一樣，那麼用大於號和等於號是一樣的）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'

用時：3280毫秒

4 、日期列不會因為有分秒的輸入而減慢查詢速度

下面的例子中，共有100萬條數據，2004年1月1日以後的數據有50萬條，但只有兩個不同的日期，日期精確到日；之前有數據50萬條，有5000個不同的日期，日期精確到秒。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi

用時：6390毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi

用時：6453毫秒

（五）其他注意事項

「水可載舟，亦可覆舟」，索引也一樣。索引有助於提高檢索性能，但過多或不當的索引也會導致系統低效。因為用戶在表中每加進一個索引，資料庫就要做更多的工作。過多的索引甚至會導致索引碎片。

所以說，我們要建立一個「適當」的索引體系，特別是對聚合索引的創建，更應精益求精，以使您的資料庫能得到高性能的發揮。

當然，在實踐中，作為一個盡職的資料庫管理員，您還要多測試一些方案，找出哪種方案效率最高、最為有效。

二、改善SQL語句

很多人不知道SQL語句在SQL SERVER中是如何執行的，他們擔心自己所寫的SQL語句會被SQL SERVER誤解。比如：

select * from table1 where name='zhangsan' and tID > 10000

和執行:

select * from table1 where tID > 10000 and name='zhangsan'

一些人不知道以上兩條語句的執行效率是否一樣，因為如果簡單的從語句先後上看，這兩個語句的確是不一樣，如果tID是一個聚合索引，那麼後一句僅僅從表的10000條以後的記錄中查找就行了；而前一句則要先從全表中查找看有幾個name='zhangsan'的，而後再根據限制條件條件tID>10000來提出查詢結果。

事實上，這樣的擔心是不必要的。SQL SERVER中有一個「查詢分析優化器」，它可以計算出where子句中的搜索條件並確定哪個索引能縮小表掃描的搜索空間，也就是說，它能實現自動優化。

雖然查詢優化器可以根據where子句自動的進行查詢優化，但大家仍然有必要了解一下「查詢優化器」的工作原理，如非這樣，有時查詢優化器就會不按照您的本意進行快速查詢。

在查詢分析階段，查詢優化器查看查詢的每個階段並決定限制需要掃描的數據量是否有用。如果一個階段可以被用作一個掃描參數（SARG），那麼就稱之為可優化的，並且可以利用索引快速獲得所需數據。

SARG的定義：用於限制搜索的一個操作，因為它通常是指一個特定的匹配，一個值得范圍內的匹配或者兩個以上條件的AND連接。形式如下：

列名 操作符 <常數 或 變數>

或

<常數 或 變數> 操作符列名

列名可以出現在操作符的一邊，而常數或變數出現在操作符的另一邊。如：

Name=』張三』

價格>5000

5000<價格

Name=』張三』 and 價格>5000

如果一個表達式不能滿足SARG的形式，那它就無法限制搜索的范圍了，也就是SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個索引對於不滿足SARG形式的表達式來說是無用的。

介紹完SARG後，我們來總結一下使用SARG以及在實踐中遇到的和某些資料上結論不同的經驗：

1、Like語句是否屬於SARG取決於所使用的通配符的類型

如：name like 『張%』 ，這就屬於SARG

而：name like 『%張』 ,就不屬於SARG。

原因是通配符%在字元串的開通使得索引無法使用。

2、or 會引起全表掃描

Name=』張三』 and 價格>5000 符號SARG，而：Name=』張三』 or 價格>5000 則不符合SARG。使用or會引起全表掃描。

3、非操作符、函數引起的不滿足SARG形式的語句

不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外還有函數。下面就是幾個不滿足SARG形式的例子：

ABS(價格)<5000

Name like 『%三』

有些表達式，如：

WHERE 價格*2>5000

SQL SERVER也會認為是SARG，SQL SERVER會將此式轉化為：

WHERE 價格>2500/2

但我們不推薦這樣使用，因為有時SQL SERVER不能保證這種轉化與原始表達式是完全等價的。

4、IN 的作用相當與OR

語句：

Select * from table1 where tid in (2,3)

和

Select * from table1 where tid=2 or tid=3

是一樣的，都會引起全表掃描，如果tid上有索引，其索引也會失效。

5、盡量少用NOT

6、exists 和 in 的執行效率是一樣的

很多資料上都顯示說，exists要比in的執行效率要高，同時應盡可能的用not exists來代替not in。但事實上，我試驗了一下，發現二者無論是前面帶不帶not，二者之間的執行效率都是一樣的。因為涉及子查詢，我們試驗這次用SQL SERVER自帶的pubs資料庫。運行前我們可以把SQL SERVER的statistics I/O狀態打開。

（1）select title,price from titles where title_id in (select title_id from sales where qty>30)

該句的執行結果為：

表 'sales'。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。

表 'titles'。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

（2）select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)

第二句的執行結果為：

表 'sales'。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。

表 'titles'。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

我們從此可以看到用exists和用in的執行效率是一樣的。

7、用函數charindex()和前面加通配符%的LIKE執行效率一樣

前面，我們談到，如果在LIKE前面加上通配符%，那麼將會引起全表掃描，所以其執行效率是低下的。但有的資料介紹說，用函數charindex()來代替LIKE速度會有大的提升，經我試驗，發現這種說明也是錯誤的：

select gid,title,fariqi,reader from tgongwen where charindex('刑偵支隊',reader)>0 and fariqi>'2004-5-5'

用時：7秒，另外：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

select gid,title,fariqi,reader from tgongwen where reader like '%' + '刑偵支隊' + '%' and fariqi>'2004-5-5'

用時：7秒，另外：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

8、union並不絕對比or的執行效率高

我們前面已經談到了在where子句中使用or會引起全表掃描，一般的，我所見過的資料都是推薦這里用union來代替or。事實證明，這種說法對於大部分都是適用的。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000

用時：68秒。掃描計數 1，邏輯讀 404008 次，物理讀 283 次，預讀 392163 次。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

union

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000

用時：9秒。掃描計數 8，邏輯讀 67489 次，物理讀 216 次，預讀 7499 次。

看來，用union在通常情況下比用or的效率要高的多。

但經過試驗，筆者發現如果or兩邊的查詢列是一樣的話，那麼用union則反倒和用or的執行速度差很多，雖然這里union掃描的是索引，而or掃描的是全表。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'

用時：6423毫秒。掃描計數 2，邏輯讀 14726 次，物理讀 1 次，預讀 7176 次。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

union

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-2-5'

用時：11640毫秒。掃描計數 8，邏輯讀 14806 次，物理讀 108 次，預讀 1144 次。

9、欄位提取要按照「需多少、提多少」的原則，避免「select *」

我們來做一個試驗：

select top 10000 gid,fariqi,reader,title from tgongwen ord

⑤ 資料庫基礎篇（二）—— SQL之數據查詢

接下來，我們將使用員工相關的四張樣本數據表，來學習SQL，建議你在學習過程中多動手練習，理解才會更深刻。表和欄位含義，如下圖：

如何利用SQL語句來操作以上數據呢？我們必須將樣本數據導入MySQL客戶端（如：Navicat）中。可以在客戶端操作數據，或者在終端窗口。工作中經常在客戶端操作，所以本文所有SQL語句將在Navicat中學習。
首先將sql腳本保存到桌面（獲取方式：關注"Python之每日一課"公眾號，後來回復"sql基礎數據"，即可。），導入SQL腳本的具體操作流程如下：

現在數據准備完成。這里是導入sql腳本；導出同理，選擇」轉儲SQL「文件。當然了，Navicat也支持將當前表或查詢結果導出Excel、CSV等文件類型。

下面可以寫SQL語句了（每個sql腳本可以保存，下次直接使用），如下:

類似於Python中 :print(要列印的東西)

①通過select查詢完的結果 ，是一個虛擬的表格，不是真實存在

② 要查詢的東西 可以是常量值、表達式、欄位、也可以是函數

補充：可以給欄位起別名，好處是提高可讀性，更方便理解；多表連接時，區分欄位。用AS 或 空格來實現。如下：

2、 條件查詢

條件查詢：根據條件過濾原始表的數據，查詢到想要的數據

1）語法

2）分類

①條件表達式

②邏輯表達式

③模糊查詢

⭐ 注意：where 一定要放到 from 後面。NULL 不是假,也不是真,而是"空"；任何運算符,判斷符碰到NULL,都得NULL；NULL的判斷只能用is null,is not null；NULL 影響查詢速度,一般避免使值為NULL。exists查詢可以與in型子查詢互換,它們之間區別以後語句優化時會詳細講解。

3、 排序查詢

1）語法

2）舉栗

⭐ 注意：order by 一定要放到 語句最後（limit前面）

4、分組查詢
1）語法

2）特點

①可以按單個欄位分組

②和分組函數一同查詢的欄位最好是分組後的欄位

③分組篩選（where 和 having區別）

④可以按多個欄位分組，欄位之間用逗號隔開

⑤可以支持排序

⑥having後可以支持別名

3）舉栗

⭐ 注意：關鍵字順序是where —>group by—>having—>order by—>limit（ having不能單獨使用，需結合group by ，表示對分組後的結果進行篩選；而 group by 必須結合分組聚合函數一起使用 ，比如：count()、max()等）

5、 常見函數

1）單行函數

2）分組函數

3）分組函數特點

①以上五個分組函數都忽略null值，除了count(*)

②sum和avg一般處理數值型，max、min、count可以處理任何數據類型

③都可以搭配distinct使用，用於統計去重後的結果

④count的參數可以支持：欄位、*、常量值，一般放1

6、連接查詢（多表查詢）

單個表不能滿足需求時，需要結合多張表，去除有關聯的數據。這時就需要用連接查詢，連接查詢有三種，通常join使用的最多。

①等值連接的結果 = 多個表的交集

②多個表不分主次，沒有順序要求

③一般為表起別名，提高閱讀性和性能

①語法

②好處

語句上，連接條件和篩選條件實現了分離，簡潔。

⭐ 注意：左右連接可互換 A left join B 等價於B right join A；內連接是左</pre>

右連接的交集；mysql沒有外連接。

自連接相當於等值連接，但是等值連接涉及多個表，而自連接僅僅是它自己。如下：在員工信息表裡，查詢員工名和直接上級的名。

7、子查詢

一條查詢語句中又嵌套了另一條完整的select語句，其中被嵌套的select語句，稱為子查詢或內查詢。在外面的查詢語句，稱為主查詢或外查詢。

①子查詢都放在小括弧內

②子查詢可以放在from後面、select後面、where後面、having後面，但一般放在條件的右側

③子查詢優先於主查詢執行，主查詢使用了子查詢的執行結果

④子查詢根據查詢結果的行數不同分為以下兩類：

2）舉栗

8、分頁查詢 （可選）
實際web開發中，當顯示的數據，一頁顯示不完時，需要分頁提交sql請求。

2）特點

①起始條目索引默認從0開始

②limit子句放在查詢語句的最後

③公式：select * from 表 limit （page-1）*sizePerPage,

3）舉栗

9、union聯合查詢

union用於把涉及多個表的SELECT語句的結果組合到一個結果集合中。適用於查詢條件較多，多個表之間沒有連接關系的場景。</pre>

2）特點

①多條查詢語句的查詢的列數必須是一致的

②多條查詢語句的查詢的列的類型幾乎相同

③union 代表去重，union all 代表不去重

3）舉栗

UNION 和 UNION ALL 運行結果的區別如下：

⭐ 注意：在多個 SELECT 語句中，第一個 SELECT 語句中被使用的欄位名稱將被用於結果的欄位名稱。當使用 UNION 時，MySQL 會把結果集中重復的記錄刪掉，而使用 UNION ALL ，MySQL 會把所有的記錄返回，且效率高於 UNION

好，今天學習到這里。工作中用的最多就是查詢。如果能消化本文涉及到的所有內容，大概能解決80%的工作需求。本文更多的是原理介紹，例子不多，只有先知道是什麼，才能知道怎麼學。那麼，接下來最重要的是要多練習實踐。因為實際的業務場景要復雜很多，給大家推薦兩個刷題的網站，力扣和牛客網，裡面有大量的sql面試題。能進一步提高我們sql的水平。這篇文章主要是SQL的常用查詢。明天繼續學習SQL的DML增刪改。一起加油！

⑥ PL/SQL最差實踐

超長的PL/SQL代碼

影響 可維護性 性能

症狀

在復雜的企業應用中 存在動輒成百上千行的存儲過程或上萬行的包

為什麼是最差

太長的PL/SQL代碼不利於閱讀 第三方工具在調試時也會出現代碼行混亂等問題 PL/SQL存儲對象（存儲過程 包 函數 觸發器等）行數上限約為 行 但實際工作中 當包大小超過 行就會出現調試問題

解決之道

PL/SQL代碼在執行前會被載入到shared pool中 shared pool以位元組為單位 UNIX下為 K 桌面環境下為 K 可以通過查詢數據字典USER_OBJECT_SIZE的PARSED_SIZE欄位查看對象大小 對於較大的包 應採用拆包策略 抽取復用部分 減少重復代碼 對於較大的存儲過程 應將存儲過程組織到包中 易於管理 對於較大的匿名塊 應將匿名塊重新定義成子過程保存在資料庫中

脫離控制的全局變數

影響 可維護性

症狀 在包中使用了全局變數 在多個位置對全局變數進行操作

CREATE OR REPLACE PACKAGE BODY PKG_TEST IS

GN_全局變數 NUMBER( );

PROCEDURE 過程A IS

BEGIN

GN_全局變數:= ;

END;

PROCEDURE 過程B IS

BEGIN

GN_全局變數:= ; 這里對全局變數進行了另外的操作

END;

為什麼是最差

全局變數可以在整個包范圍內被訪問到 因此對全局變數的跟蹤和調試會比較困難 如果變數是在package中定義的 變數還可以被其他包訪問 這將會更為危險

解決之道

減少或取締全局變數的使用 對於要在過程間交互的變數 通過參數傳遞來實現 如果必須使用全局變數 應對全局變數進行get/set函數封裝 規范對全局變數的訪問

PL/SQL中嵌入復雜SQL語句

影響 可維護性

症狀

在PL/SQL代碼中嵌入SQL語句 如

PROCEDURE 過程A IS

BEGIN

UPDATE T_A SET COL = ;

END;

PROCEDURE 過程B IS

BEGIN

DELETE FROM T_A WHERE COL = ;

END;

為什麼是最差

? PL/SQL代碼中嵌入SQL語句使得代碼含義變得難於閱讀和理解

? 在多個位置對表進行訪問 不利於SQL優化

解決之道

? 將分散SQL語句進行封裝 例如上例中的刪除語句 可以封裝為 prc_刪除T_A() 過程參數為T_A的type類型 對T_A的刪除操作都委託此過程處理 當T_A表增加或刪除欄位時 主要的變化都集中在這些過程中 對其他邏輯影響較少

? 對SQL的優化集中在封裝的過程中

異常 的異常處理

影響 可維護性 健壯性

症狀 我們來看下面的代碼

PROCEDURE 過程A(錯誤代碼 out varchar 錯誤信息 out varchar ) IS

BEGIN

UPDATE T_A SET COL = ;

SELECT FROM T_A WHERE ;

DELETE FROM T_A WHERE COL = ;

EXCEPTION

WHEN OTHERS THEN

END;

為什麼是最差

整個過程只有一個WHEN OTHERS 的異常段 示例中的三個語句發生的異常只能被最外層捕捉 無法區分發生異常的種類和位置

解決之道

? 不使用WHEN OTHERS捕捉所有異常 例如不應該捕捉NO_DATA_FOUND異常 使用專用的Exception來捕捉特定的異常

? 聲明自己的異常處理機制 處理與業務相關的異常 將業務異常與系統運行期異常分開處理

? 自定義完整的異常信息 異常信息中包含異常發生時的場景

固定的變數長度和變數類型

影響 可維護性

症狀 當聲明基於欄位類型的變數時 尤其是varchar 類型 直接使用固定長度聲明

為什麼是最差

? 這種硬編碼的變數大小很可能與資料庫中實際大小不符

? 如果欄位的類型 大小等發生變化 還需要到PL/SQL中調整變數

解決之道

使用%Type聲明與欄位類型相關的變數

不做單元測試

影響 健壯性

症狀 PL/SQL代碼中蘊含大量的業務邏輯 這些邏輯編寫完畢後 沒有提供合適的單元測試用例用於驗證

為什麼是最差 不做單元測試的危害這里就不再廢話了

解決之道

PL/SQL並沒有提供諸如JUnit之類易用的單元測試工具 現在有一些開源工具可以使用 使用utPLSQL()工具進行單元測試 或DBUnit進行二次開發 滿足不同應用的需要

使用代碼值而不使用代碼名稱

影響 可維護性

症狀 我們看下面的代碼

方法

V_sex:= ; 男

方法

CONST_MALE CONSTANT VARCHAR ( ) := ; 定義常量 男

V_sex:=CONST_MALE;

為什麼是最差

? 從例子中可以看出 同樣是使用性別 方法 是直接使用代碼值 方法 是使用常量 看上去似乎方法 要比方法 麻煩一些 但方法 比方法 更為直觀 代碼的可讀性也更好 代碼的閱讀者不需要關注 代表什麼含義

? 當其他項目男性性別定義修改為 時 採用方法 編碼的程序需要仔細查找每一段代碼 容易產生錯誤 而採用方法 編碼的程序只修改常量定義即可

解決之道

將常量定義放入到公共的代碼包中 供其他程序共享 所有涉及到代碼值的比較 引用等都必須使用常量名 而不能直接書寫代碼值 對於一些復雜的代碼值間的關系可以進一步封裝 以函數的方式提供調用

不對PL/SQL對象進行配置管理

影響 可維護性

症狀 PL/SQL對象（package package body trigger procere type type body 函數等）的代碼沒有使用配置管理工具進行維護和更新

為什麼是最差

因為Oracle內部結構的差異 對象的管理具有一定的難度 尤其是在並行開發的情況下

? 對象職責劃分不清 造成多人同時修改一個對象 在編譯時 如果後來者沒有獲取最新的代碼 會造成前一個開發人員修改的代碼被覆蓋

? Oracle對象不能追溯既往 資料庫中只能保存最新

解決之道

? 規范開發過程 以配置管理工具上的PL/SQL代碼為最新

? 使用第三方插件減少同步工作量 如PL/SQL Developer下的VCS版本控制插件

IF … ELSE …的壞味道

影響 可維護性

症狀 大量使用IF … ELSE

為什麼是最差

大量存在IF/ELSE 造成代碼邏輯混亂 不易修改 無論是PL/SQL還是其他編程語言 這種代碼都已經飄著 bad *** ell 了

解決之道

? 使用Oracle資料庫的繼承特性 通過type實現對象的繼承 利用策略模式封裝差異 對外提供統一的調用介面

? 將頻繁使用的IF/ELSE代碼重構為單獨的過程或函數 供其他代碼復用

在非自治事務中控制事務

影響 數據一致性

症狀

在PL/SQL非自治事務代碼中控制事務 例如

PROCEDURE 過程A(錯誤代碼 out varchar 錯誤信息 out varchar ) IS

BEGIN

SAVEPOINT A;

UPDATE T_A SET COL = ;

MIT;

DELETE FROM T_A WHERE COL = ;

ROLLBACK TO A;

EXCEPTION

WHEN OTHERS THEN

END;

為什麼是最差

這種行為是我認為最差實踐中危害最大的一種 隨處可見的事務控制代碼會造成數據不一致 引發的問題難於跟蹤和調試

解決之道

? 由調用者決定何時提交或回滾事務

? 對於需要特殊事務管理的過程如記載日誌 使用自治事務

不使用綁定變數

影響 性能

症狀 直接使用值而不使用綁定變數進行查詢 尤其是在拼寫sql的程序中 這種情況更突出

為什麼是最差

這是一個常見問題 當代碼中大量充斥固定的代碼值時 資料庫引擎每次都需要重新解析 不能使用既有的執行計劃

解決之道 對於這種經常執行的語句 使用綁定變數而非實際參數值執行

慎用ROWNUM=

影響 可維護性 數據一致性

症狀 在讀取數據時 有時只需要取一行 這時WHERE條件中就會用到ROWNUM=

為什麼是最差

之所以將這個實踐評成最差 是因為筆者在實際工作中曾經遇到過這類問題 跟蹤和調試都很困難 ROWNUM本身的處理順序是在ORDER BY 之前 所以當ROWNUM= 時產生的結果很可能是隨機的

解決之道 了解要查詢數據的含義 使用其他條件限制結果集

靈活的動態SQL

影響 可維護性 性能

症狀 EXECUTE IMMEDIATE SELECT A FROM TAB INTO v_a;

為什麼是最差

動態SQL失去了編譯期檢查能力 將發生問題的可能性推遲到運行期 動態SQL也不利於優化 因為只有在運行期才能得到完整的SQL語句

解決之道 盡量避免使用動態SQL 對於易變的業務邏輯可以抽取到中間層實現

對ROWID進行訪問

影響 數據一致性

症狀 使用ROWID作為數據更新 刪除的WHERE條件

為什麼是最差

ROWID屬於Oracle底層存儲結構 會隨著數據的遷移 導入 導出發生變化 而業務邏輯則不應依賴底層存儲結構

lishixin/Article/program/Oracle/201311/17942

⑦ SQLServer · 最佳實踐 · 如何將SQL Server 2012降級到2008 R2

SQL
Server資料庫是不支持降級還原的,如果你要將高版本的資料庫轉換為低版本的資料庫,可以採取以下方法:
在高版本的資料庫(比如SQL
Server
2012)中生成資料庫的腳本文件,在生成腳本文件的高級選項中選擇目標伺服器的版本(比如SQL
Server
2008),數據類型為"架構和數據".
腳本生成後在目標伺服器中運行腳本文件即可.

⑧ SQL如何實踐字元串拼接：

--建表
createtableM
(
idint,
dictvarchar(10)
)
CreateTableN
(
billnoint,
dictdocvarchar(100)
)

--插入數據
insertintoMvalues(1,'A')
insertintoMvalues(2,'B')
insertintoMvalues(3,'C')
insertintoMvalues(4,'D')

insertintoNvalues(1,'1,2,3')
insertintoNvalues(2,'2,3')
insertintoNvalues(3,'1,2,3,4')

--創建函數
CreateFunctionfn_myStr(@StrVarchar(8000))
returnsVarchar(8000)
As
Begin
Declare@RStVarchar(8000)=@Str
Select@RSt=replace(@RSt,id,dict)
FromM
Return@RSt
End

--查詢
Select*fromM
selectbillno,dbo.fn_myStr(dictdoc)AsdictdocfromN

⑨ 怎麼將sql高級查詢靈活運用~！要實踐~！哪裡有大量的案例給我去實踐拜託各位了 3Q

恩~! 你可以試試編寫一個ATM取款機的資料庫~! 或著做個某某公司的財務管理~~~等等~~~生活中的還有很多~! 如果你還學了.NET之類的話~~還可以試著把界面一起給做出來~!

⑩ SQL操作實踐之范圍分區間統計的使用

最近有接觸到一個統計的需求，要求輸出數值各個范圍的計數。舉個例子，一個班有N個人，要求輸出60以下，60-70，70-80，80，100各個分段的人數。像這種范圍比較少的情況，可以使用如下的第一種方式即case。在MySQL和Hive中都是支持的。但是如果所要統計的范圍很多，或者說不是確定的，如果使用case的方式，可以想見，將非常繁瑣。這時候，就可以用到下面的第二種方式，巧妙利用計算來完成對應的統計。

可以很明顯看到是比較繁瑣的。

如上，通過 floor 函數，先除以500再乘以500，這樣就將數據按照500做了分割，再將這個范圍計數使用 group by 聚合，完成了實際的統計。 因此得到的數據0，即表示0-499， 1表示500-999，范圍可以隨實際數據而變化，比較方便簡潔。當然，如果需要的范圍不是分散，需要定製的，在少量的情況下， case between 可能更適合些。

以上就是本期的內容，作為一篇操作備忘。

1. Hive SQL 分區間統計問題