摘要:如果應用了,那么后面的所有步驟都只能操作的列或者是執行聚合函數等。注意原因在于分組后最終的結果集中只包含每個組中的一行。等聚合函數聚合函數只是對分組的結果進行一些處理,拿到某些想要的聚合值,例如求和,統計數量等,并不生成虛擬表。
前言:
上一篇講Mysql基本架構時,以“sql查詢語句在MySql架構中具體是怎么執行的?”進行了全面的講解。知道了sql查詢語句在MySql架構中的具體執行流程,但是為了能夠更好更快的寫出sql語句,我覺得非常有必要知道sql語句中各子句的執行順序??催^上一篇文章的小伙伴應該都知道,sql語句最后各子句的執行應該是在執行器中完成的,存儲引擎對執行器提供的數據讀寫接口。現在開始我們的學習
語句中各子句完整執行順序概括(按照順序號執行)from (注:這里也包括from中的子語句)
join
on
where
group by(開始使用select中的別名,后面的語句中都可以使用)
avg,sum.... 等聚合函數
having
select
distinct
order by
limit
每個子句執行順序分析所有的 查詢語句都是從from開始執行的,在執行過程中,每個步驟都會為下一個步驟生成一個虛擬表,這個虛擬表將作為下一個執行步驟的輸入。
1. fromform是一次查詢語句的開端。
如果是一張表,會直接操作這張表;
如果這個from后面是一個子查詢,會先執行子查詢中的內容,子查詢的結果也就是第一個虛擬表T1。(注意:子查詢中的執行流程也是按照本篇文章講的順序哦)。
如果需要關聯表,使用join,請看2,3
2. join如果from后面是多張表,join關聯,會首先對前兩個表執行一個笛卡爾乘積,這時候就會生成第一個虛擬表T1(注意:這里會選擇相對小的表作為基礎表);
3. on對虛表T1進行ON篩選,只有那些符合
對虛擬表T2進行WHERE條件過濾。只有符合
group by 子句將中的唯一的值組合成為一組,得到虛擬表T4。如果應用了group by,那么后面的所有步驟都只能操作T4的列或者是執行6.聚合函數(count、sum、avg等)。(注意:原因在于分組后最終的結果集中只包含每個組中的一行。謹記,不然這里會出現很多問題,下面的代碼誤區會特別說。)
6. avg,sum.... 等聚合函數聚合函數只是對分組的結果進行一些處理,拿到某些想要的聚合值,例如求和,統計數量等,并不生成虛擬表。
7. having應用having篩選器,生成T5。HAVING子句主要和GROUP BY子句配合使用,having篩選器是第一個也是為唯一一個應用到已分組數據的篩選器。
8. select執行select操作,選擇指定的列,插入到虛擬表T6中。
9. distinct對T6中的記錄進行去重。移除相同的行,產生虛擬表T7.(注意:事實上如果應用了group by子句那么distinct是多余的,原因同樣在于,分組的時候是將列中唯一的值分成一組,同時只為每一組返回一行記錄,那么所以的記錄都將是不相同的。 )
10. order by應用order by子句。按照order_by_condition排序T7,此時返回的一個游標,而不是虛擬表。sql是基于集合的理論的,集合不會預先對他的行排序,它只是成員的邏輯集合,成員的順序是無關緊要的。對表進行排序的查詢可以返回一個對象,這個對象包含特定的物理順序的邏輯組織。這個對象就叫游標。
oder by的幾點說明
因為order by返回值是游標,那么使用order by 子句查詢不能應用于表表達式。
order by排序是很需要成本的,除非你必須要排序,否則最好不要指定order by,
order by的兩個參數 asc(升序排列) desc(降序排列)
11. limit取出指定行的記錄,產生虛擬表T9, 并將結果返回。
limit后面的參數可以是 一個limit m ,也可以是limit m n,表示從第m條到第n條數據。
(注意:很多開發人員喜歡使用該語句來解決分頁問題。對于小數據,使用LIMIT子句沒有任何問題,當數據量非常大的時候,使用LIMIT n, m是非常低效的。因為LIMIT的機制是每次都是從頭開始掃描,如果需要從第60萬行開始,讀取3條數據,就需要先掃描定位到60萬行,然后再進行讀取,而掃描的過程是一個非常低效的過程。所以,對于大數據處理時,是非常有必要在應用層建立一定的緩存機制)
開發某需求寫的一段sql</>復制代碼
SELECT `userspk`.`avatar` AS `user_avatar`,
`a`.`user_id`,
`a`.`answer_record`,
MAX(`score`) AS `score`
FROM (select * from pkrecord order by score desc) as a
INNER JOIN `userspk` AS `userspk`
ON `a`.`user_id` = `userspk`.`user_id`
WHERE `a`.`status` = 1
AND `a`.`user_id` != "m_6da5d9e0-4629-11e9-b5f7-694ced396953"
GROUP BY `user_id`
ORDER BY `a`.`score` DESC
LIMIT 9;
查詢結果:
先簡要說一下我要查詢的內容:
想要查詢pk記錄表中分數最高的9個用戶記錄和他們的頭像。
通過這段sql實際想一遍sql各字句的執行順序
pk記錄表的數據結構設計,每個用戶每天每個館下可能會有多條記錄,所以需要進行分組,并且查詢結果只想拿到每個分組內最高的那條記錄。
這段sql的一些說明:
可能有些同學會認為子查詢沒有必要
直接查詢pk記錄表就可以,但是并不能拿到預期的結果,因為分組后的每個組結果是不進行排序的,而且max拿到的最高分數肯定是對應的該分組下最高分數,但是其它記錄可能就不是最高分數對應的那條記錄。所以子查詢非常有必要,它能夠對原始的數據首先進行排序,分數最高的那條就是第一條對應的第一條記錄。
看一下代碼和執行結果與帶有子查詢的進行比較,就能理解我上面說的一段話:
</>復制代碼
//不使用子查詢
SELECT `userspk`.`avatar` AS `user_avatar`,
`pkrecord`.`user_id`,
`pkrecord`.`answer_record`,
`pkrecord`.`id`,
MAX(`score`) AS `score`
FROM pkrecord
INNER JOIN `userspk` AS `userspk`
ON `pkrecord`.`user_id` = `userspk`.`user_id`
WHERE `pkrecord`.`status` = 1
AND `pkrecord`.`user_id` != "m_6da5d9e0-4629-11e9-b5f7-694ced396953"
GROUP BY `user_id`
ORDER BY `pkrecord`.`score` DESC
LIMIT 9;
查詢結果
在子查詢中對數據已經進行排序后,外層排序方式如果和子查詢排序分數相同,都是分數倒序,外層的排序可以去掉,沒有必要寫兩遍。
sql語句中的別名 別名在哪些情況使用在 SQL 語句中,可以為表名稱及字段(列)名稱指定別名
表名稱指定別名
同時查詢兩張表的數據的時候:
未設置別名前:
</>復制代碼
SELECT article.title,article.content,user.username FROM article, user
WHERE article.aid=1 AND article.uid=user.uid
設置別名后:
</>復制代碼
SELECT a.title,a.content,u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid
好處:使用表別名查詢,可以使 SQL 變得簡潔而更易書寫和閱讀,尤其在 SQL 比較復雜的情況下
查詢字段指定別名
查詢一張表,直接對查詢字段設置別名
</>復制代碼
SELECT username AS name,email FROM user
查詢兩張表
好處:字段別名一個明顯的效果是可以自定義查詢數據返回的字段名;當兩張表有相同的字段需要都被查詢出,使用別名可以完美的進行區分,避免沖突
</>復制代碼
SELECT a.title AS atitle,u.username,u.title AS utitle FROM article AS a, user AS u where a.uid=u.uid
關聯查詢時候,關聯表自身的時候,一些分類表,必須使用別名。
別名也可以在group by與having的時候都可使用
別名可以在order by排序的時候被使用
查看上面一段sql
delete , update MySQL都可以使用別名,別名在多表(級聯)刪除尤為有用
</>復制代碼
delete t1,t2 from t_a t1 , t_b t2 where t1.id = t2.id
子查詢結果需要使用別名
查看上面一段sql
別名使用注意事項雖然定義字段別名的 AS 關鍵字可以省略,但是在使用別名時候,建議不要省略 AS 關鍵字
書寫sql語句的注意事項 書寫規范上的注意字符串類型的要加單引號
select后面的每個字段要用逗號分隔,但是最后連著from的字段不要加逗號
使用子查詢創建臨時表的時候要使用別名,否則會報錯。
為了增強性能的注意不要使select * from ……返回所有列,只檢索需要的列,可避免后續因表結構變化導致的不必要的程序修改,還可降低額外消耗的資源
不要檢索已知的列
</>復制代碼
select user_id,name from User where user_id = ‘10000050’
使用可參數化的搜索條件,如=, >, >=, <, <=, between, in, is null以及like ‘
當需要驗證是否有符合條件的記錄時,使用exists,不要使用count(*),前者在第一個匹配記錄處返回,后者需要遍歷所有匹配記錄
Where子句中列的順序與需使用的索引順序保持一致,不是所有數據庫的優化器都能對此順序進行優化,保持良好編程習慣(索引相關)
不要在where子句中對字段進行運算或函數(索引相關)
如where amount / 2 > 100,即使amount字段有索引,也無法使用,改成where amount > 100 * 2就可使用amount列上的索引
如where substring( Lastname, 1, 1) = ‘F’就無法使用Lastname列上的索引,而where Lastname like ‘F%’或者where Lastname >= ‘F’ and Lastname < ‘G’就可以
在有min、max、distinct、order by、group by操作的列上建索引,避免額外的排序開銷(索引相關)
小心使用or操作,and操作中任何一個子句可使用索引都會提高查詢性能,但是or條件中任何一個不能使用索引,都將導致查詢性能下降,如where member_no = 1 or provider_no = 1,在member_no或provider_no任何一個字段上沒有索引,都將導致表掃描或聚簇索引掃描(索引相關)
Between一般比in/or高效得多,如果能在between和in/or條件中選擇,那么始終選擇between條件,并用>=和<=條件組合替代between子句,因為不是所有數據庫的優化器都能把between子句改寫為>=和<=條件組合,如果不能改寫將導致無法使用索引(索引相關)
調整join操作順序以使性能最優,join操作是自頂向下的,盡量把結果集小的兩個表關聯放在前面,可提高性能。(join相關)
注意:索引和關聯我會多帶帶拿出來兩篇文章進行詳細講解,在這個注意事項中只是簡單提一下。
覺得本文對你有幫助?請分享給更多人
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://m.specialneedsforspecialkids.com/yun/75518.html
摘要:要是緊急排查個問題,媽蛋雖然有很多好處,比如和底層的無關。你的公司如果有,是不允許你亂用的。 知乎看到問題《SpringBoot開發使用Mybatis還是Spring Data JPA??》,順手一答,討論激烈。我實在搞不懂spring data jpa為啥選了hibernate作為它的實現,是Gavin King的裙帶關系么?DAO層搞來搞去,從jdbc到hibernate,從top...
閱讀 3012·2021-11-24 10:22
閱讀 3059·2021-11-23 10:10
閱讀 1368·2021-09-28 09:35
閱讀 1761·2019-08-29 13:16
閱讀 1402·2019-08-26 13:29
閱讀 2798·2019-08-26 10:27
閱讀 687·2019-08-26 10:09
閱讀 1451·2019-08-23 18:05