【讀後感1】SQL2008技術內幕- SQL邏輯查詢處理

引言觀點

1. 編程語言日新月異,但是從沒有人否定sql 在現代編程中的巨大作用和 持續的可利用性。SQL以對人類友好的閱讀體驗提供數據查詢能力( 相比其他編程語言 ), 同時在各種數據庫平台中,基礎SQL元素是相同或大同小異的,

從我們最早接觸的SQL,Mysql到公司大數據impala 支持SQL, Es也提供類似SQL的查詢, 阿里提出SQLFlow AI框架, SQL的生命力極其頑強。

2. 在我近6年的開發生涯中,確實覺得SQL語言沒有得到開發者足夠的重視,尤其是流行的ORM概念使得了編寫SQL機會越來越少,使用ORM映射框架是需要一些代碼的, 另外ORM只能用於基礎的關係型二維查詢,對於複雜的查詢無能為力,部分工作可通過巧妙的SQL查詢,存儲過程,觸發器來完成。

3. SQL編程有許多獨特之處: 面向集合的思維方式、 查詢元素的邏輯處理順序、三值邏輯(three value logic),理解不透的話在實際編寫SQL時會產生很多錯誤的寫法、性能低下的代碼。

1987年SQL稱為ISO標準,ANSI宣布該語言發音為“ess kyoo ell”, 但由於歷史原因,很多專業人士還是將SQL發音成sequel,而且從英文習慣上,sequel發音更為流暢。 互聯網如此之大,容得下不同的聲音。

結合《SQL2008 技術內幕 T-SQL查詢》和工作經驗,提煉出Web開發者需要熟練掌握以下SQL查詢。

  • SQL邏輯查詢處理

  • SQL 面向集合的思維方式

不敢妄自宣稱是高級編程經驗, 只是認為Web開發者應該Cover這些常見SQL用法。  

SQL邏輯查詢處理

  開發者、數據分析師每天都在寫【SELECT 列a,聚合函數 FROM 表名 WHERE 過濾條件 GROUP BY 列a HAVING 篩選條件】這樣的查詢語句。

  SQL與其他語言不同的最明顯特徵是代碼的處理順序,大多數編程語言中,代碼是按照編寫順序來處理的,但在SQL中第一個要處理的子句是FROM子句,儘管SELECT語句第一個出現,但基本都在最後處理。

       每一步都會生成一個虛擬表,該虛擬表會作為下一步的輸入, 這些虛擬表對於調用者(客戶端應用程序或者外部查詢)都是不可用的,只有最後一步生成的虛擬表才會返回給調用者,這種形態可對比LINQ理解。

 

①FROM        FROM階段負責標識表或要查詢的表,如果指定了表運算符(JOIN, APPLY,PIVOT,UNPIVOT ),還要進行表運算符的處理。

              例如:表聯接運算中涉及的階段是 笛卡爾積、ON篩選器和 添加外部行,FROM階段生成虛擬表VT1.

②WHERE           這個階段根據在WHERE子句中出現的謂詞對VT1中進行篩選,只有讓謂詞計算結果為TRUE的行,才會插入VT2中。

③GROUP BY     按照GROUP BY 子句中指定的列名列表,對VT2中的行進行分組,生成VT3, 最終每個分組只有一個結果行。

④HAVING          根據HAVING子句中出現的謂詞,對VT3中行記錄進行篩選,只有讓謂詞結果為TRUE的行記錄,才會進入VT4, Having 篩選器是唯一可用於分組數據的篩選器。

⑤SELECT    處理SELECT子句中字段(某些字段可能進行一些操作,形成新的字段),形成虛擬表VT5

⑥ORDER BY  根據ORDER BY子句中指定的列名列表,對VT5 中行進行排序,輸出最後結果。

 

着重理解:

  • 第一步的FROM表運算, 一般情況下是TABLE、TempTable,CTE, 還有可能是表運算符(我們常用的是聯接運算符), 所以不能單純認為FROM後面是一個表結構。

  • 表聯接運算符  ON篩選器 與 WHERE有所不同,若採用OUTER JOIN, 應用ON篩選出來的結果不一定是此階段最終結果,因為涉及【添加外部行】, 而WHERE過濾出的結果是此階段的最終結果。 

  • GROUP BY x,y 意味着將(x,y)作為一個整體來分組

  • 有SELECT 和WHERE的時候,先執行WHERE,再執行SELECT,這樣就很容易理解以下SQL的業務含義:

SELECT page_original_url,server_session_id,access_order-1 as access_order FROM PageViewMeasure WHERE access_order >= 2
--- 查詢過濾出access_order>=2的基礎數據集,然後將(原列值-1)重命名為原列名,重命名的用法業務上也許是為了形成新的SQL聯接

SELECT keyword_id,Coalesce(full_keywords,keywords)  as  not_nullField,profile_id,session_server_time,count (*) over () as Count FROM pageview 
WHERE  profile_id =5254 and keyword_id != '-' and day =20181008  and  not_nullField !='-'   
ORDER BY session_server_time 
--- SQL報錯:Could not resolve column/field reference: 'not_nullfield' 也容易理解了:先執行where, 執行where的時候not_nullField字段還沒有形成
  • ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY PageViewServerTime)排名函數中ORDER BY 與SQL語句最後的ORDER BY 同時存在,哪個ORDER BY起最終排序作用?

SELECT page_original_url as name,page_view_server_time, ROW_NUMBER() OVER(PARTITION BY page_original_url ORDER BY page_view_server_time ) as partition_rank ,wd3_page_duration
  FROM pageview WHERE profile_id=5198 AND day between 20190616 and 20190621   
  ORDER  BY wd3_page_duration desc 
  LIMIT 100

  可以認為 ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2) as rank 本質上還是產生一個列值,實際是對應以上的第⑤步,因此SQL最後的ORDER BY起最終排序作用,例證如下:

           某些轉載文章寫有: 以上over函數里的分組及排序的執行晚於“where,group by,order by”的執行 ,這樣的結論是錯誤的

  • 若存在LIMIT子句,則LIMIT子句必須在ORDER BY 語法之後

 

      上圖來自《SQL技術內幕T-SQL查詢》邏輯查詢處理一章

 

 這裏拋出一個困惑點:

  在FROM子句中,若存在JOIN表運算符, 可能會按照 【計算笛卡爾積】 【應用ON篩選】【添加外部行】的順序來完成 JOIN的過程, 但是試想一下: 如果兩個表都為大表,先計算笛卡爾積,再篩選 豈不很費內存,

  我也搜索了很多資料,某些資料認為先進行【ON篩選】再進行【JOIN】運算:

https://www.cnblogs.com/liuzhendong/archive/2011/10/27/2226805.html

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms189499(v=sql.100)

我更願意相信《SQL技術內幕T-SQL查詢》書中所言:

本章描述的某些邏輯處理步驟可能看起來非常低效,但要記住, 在實踐中,
查詢的實際物理處理可能與邏輯處理有很大不同

在SQL Server 中負責生成實際工作計劃的組件是查詢優化器,以何種順序訪問表、使用什麼訪問方法和索引,應用哪種聯接算法等都是查詢優化器來決定的,優化器會生成多個有效執行計劃並選擇一個開銷最低的計劃。

邏輯查詢處理中各個階段都有其特定的順序,而優化器缺經常可以在它生成的物理執行計劃中走捷徑。

 我們思考一個簡單的SQL:

SELECT * FROM pageview LEFT JOIN  share  ON  pageview.share_pv_id = share.page_view_id
 WHERE pageview.profile_id =5313 AND pageview.day  between 20190615 and 20190624

若實際物理查詢按照上面描述的 邏輯查詢處理, 先進行 FROM 子句中的 LEFT JOIN 計算,再進行 WHERE過濾, 根本無法查出(在FROM子句可能內存就爆滿了)

  現在我們能夠查詢出來,能夠印證 實際物理查詢確實與邏輯查詢處理有很大不同。 

PS: 以上是個人從現象上推斷書中理論,對於實際物理查詢處理並沒有理論支持,若網友們有相關資料,可留言給我。

 

作者: JulianHuang

感謝您的認真閱讀,如有問題請大膽斧正;覺得有用,請下方或加關注。

本文歡迎轉載,但請保留此段聲明,且在文章頁面明顯位置註明本文的作者及原文鏈接。

【精選推薦文章】

自行創業 缺乏曝光? 下一步"網站設計"幫您第一時間規劃公司的門面形象

網頁設計一頭霧水??該從何著手呢? 找到專業技術的網頁設計公司,幫您輕鬆架站!

評比前十大台北網頁設計台北網站設計公司知名案例作品心得分享

台北網頁設計公司這麼多,該如何挑選?? 網頁設計報價省錢懶人包"嚨底家"