-
1 # 藍風24
-
2 # 愛可生雲資料庫
通用表示式在各個商業資料庫中比如ORACLE,SQL SERVER等早就實現了,MySQL到了8.0 才支援這個特性。這裡有兩個方面來舉例說明WITH的好處。
第一,易用性。
第二,效率。
舉例一 WITH表示式的易用性
我們第一個例子, 對比檢視的檢索和WITH的檢索。我們知道檢視在MySQL裡面的效率一直較差,雖說MySQL5.7 對檢視做了相關固化的最佳化,不
通用表示式在各個商業資料庫中比如ORACLE,SQL SERVER等早就實現了,MySQL到了8.0 才支援這個特性。這裡有兩個方面來舉例說明WITH的好處。
第一,易用性。
第二,效率。
舉例一 WITH表示式的易用性
我們第一個例子, 對比檢視的檢索和WITH的檢索。我們知道檢視在MySQL裡面的效率一直較差,雖說MySQL5.7 對檢視做了相關固化的最佳化,不過依然不盡人意。考慮下,如果多次在同一條SQL中訪問檢視,那麼則會多次固化檢視,勢必增加相應的資源消耗。MySQL裡之前對這種消耗的減少只有一種,就是動態處理,不過一直語法較為噁心,使用不是很廣。MySQL8.0後,又有了一種減少消耗的方式,就是WITH表示式。我們假設以下表結構:
有1000行測試記錄。這裡我們建立一個普通的檢視:
檢索語句A:對視圖裡的最大和最小值欄位rank1進行過濾檢索出符合條件的記錄行數。我們用WITH表示式來重寫一遍這個查詢。查詢語句B:
功能性演示, 索引表面上看執行時間差不多, 我們來對比下兩條實現語句的查詢計劃,
A的計劃:
B的計劃:
從以上圖我們可以看出,B比A少了一次對檢視的固化,也就是說,不管我訪問WITH多少次,僅僅固化一次。有興趣的可以加大資料量,加大併發測試下效能。
舉例二 WITH表示式的功能性
我們第二個例子,簡單說功能性。
比如之前MySQL一直存在的一個問題,就是臨時表不能開啟多次。我們以前只有一種解決辦法就是把臨時表固化到磁碟,像訪問普通表那樣訪問臨時表。現在我們可以用MySQL8.0自帶的WITH表示式來做這樣的業務。
比如以下臨時表:我們還是用之前的查詢,這裡會提示錯誤。現在我們可以用WITH來改變這種思路當然WITH的用法還有很多,感興趣的可以去看看手冊上的更深入的內容。
過依然不盡人意。考慮下,如果多次在同一條SQL中訪問檢視,那麼則會多次固化檢視,勢必增加相應的資源消耗。
MySQL裡之前對這種消耗的減少只有一種,就是動態處理,不過一直語法較為噁心,使用不是很廣。
MySQL8.0後,又有了一種減少消耗的方式,就是WITH表示式。我們假設以下表結構:
有1000行測試記錄。
這裡我們建立一個普通的檢視:
檢索語句A:
對視圖裡的最大和最小值欄位rank1進行過濾檢索出符合條件的記錄行數。
我們用WITH表示式來重寫一遍這個查詢。
查詢語句B:
我的函式很少,僅作功能性演示, 索引表面上看執行時間差不多, 我們來對比下兩條實現語句的查詢計劃,
A的計劃:
B的計劃:
從以上圖我們可以看出,B比A少了一次對檢視的固化,也就是說,不管我訪問WITH多少次,僅僅固化一次。有興趣的可以加大資料量,加大併發測試下效能。
舉例二 WITH表示式的功能性
我們第二個例子,簡單說功能性。
比如之前MySQL一直存在的一個問題,就是臨時表不能開啟多次。我們以前只有一種解決辦法就是把臨時表固化到磁碟,像訪問普通表那樣訪問臨時表。現在我們可以用MySQL8.0自帶的WITH表示式來做這樣的業務。
比如以下臨時表:
我們還是用之前的查詢,這裡會提示錯誤。
現在我們可以用WITH來改變這種思路。
當然WITH的用法還有很多,感興趣的可以去看看手冊上的更深入的內容。
回覆列表
你寫上去的編譯的時候有點小錯誤,正確的應該是這樣寫的
Create table [dbo].[adminitable](
[adminpassword] [varchar](50) null,
[adminname] [varchar](20) null,
constraint [pk_adminitable] primary key clustered
(
[adminname] asc
)
with (pad_index=off,statistics_norecompute=off,ignore_dup_key=off,allow_row_locks=on,allow_page_locks=on)
on [primary])
on [primary]
constraint 是子句限制
on [primary]是指的該表位於primary檔案組,也就是主檔案組,一個數據庫可以分為n個檔案組
with 後面接的是索引描述
pad_index是指定非頁級索引頁的資料充滿度...