如何寫優雅的SQL原生語句

步履不停
發布: 2019-06-18 14:54:33
原創
2610 人瀏覽過

如何寫優雅的SQL原生語句

前言:

上一篇講Mysql基本架構時,以"sql查詢語句在MySql架構中具體是怎麼執行的" 進行了全面的講解。知道了sql查詢語句在MySql架構中的具體執行流程,但是為了能夠更好更快的寫出sql語句,我覺得非常有必要知道sql語句中各子句的執行順序。看過上一篇文章的小夥伴應該都知道,sql語句最後各子句的執行應該是在執行器中完成的,儲存引擎對執行器提供的資料讀寫介面。現在開始我們的學習

語句中各子句完整執行順序概括(按照順序號執行)

  1. #from (註:這裡也包含from中的子語句)

  2. join

  3. on

  4. where

  5. group by(開始使用select中的別名,後面的語句中都可以使用)

  6. #avg,sum.... 等聚合函數

  7. having

  8. select

  9. #distinct

  10. order by

  11. limit

每個子句執行順序分析

所有的查詢語句都是從from開始執行的,在執行過程中,每個步驟都會為下一個步驟產生一個虛擬表,這個虛擬表將作為下一個執行步驟的輸入。

1. from

form是一次查詢語句的開端。

  • 如果是一張表,會直接操作這張表;

  • #如果這個from後面是子查詢,會先執行子查詢中的內容,子查詢的結果也就是第一個虛擬表T1。 (注意:子查詢中的執行流程也是依照本篇文章講的順序哦)。

  • 如果需要關聯表,使用join,請看2,3

#2.join

#如果from後面是多張表,join關聯,會先對前兩個表執行一個笛卡爾乘積,這時候就會產生第一個虛擬表T1(注意:這裡會選擇相對小的表作為基礎表);

3.  on

對虛表T1進行ON篩選,只有那些符合的行才會被記錄在虛表T2中。 (注意,這裡的這裡如果還有第三個表與之關聯,會用T2與第三個表進行笛卡爾乘積生產T3表,繼續重複3. on步驟生成T4表,不過下面的順序講解暫時不針對這裡的T3和T4,只是從一個表關聯查詢T2繼續說)

4. where

對虛擬表T2進行WHERE條件過濾。只有符合的記錄才會插入到虛擬表T3。

5.group by

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;
登入後複製

查詢結果:

如何寫優雅的SQL原生語句

  • 先簡單說一下我要查詢的內容:

想要查询pk记录表中分数最高的9个用户记录和他们的头像。

  • 通过这段sql实际想一遍sql各字句的执行顺序

pk记录表的数据结构设计,每个用户每天每个馆下可能会有多条记录,所以需要进行分组,并且查询结果只想拿到每个分组内最高的那条记录

这段sql的一些说明:

  1. 可能有些同学会认为子查询没有必要 直接查询pk记录表就可以,但是并不能拿到预期的结果,因为分组后的每个组结果是不进行排序的,而且max拿到的最高分数肯定是对应的该分组下最高分数,但是其它记录可能就不是最高分数对应的那条记录。所以子查询非常有必要,它能够对原始的数据首先进行排序,分数最高的那条就是第一条对应的第一条记录。

看一下代码和执行结果与带有子查询的进行比较,就能理解我上面说的一段话:

//不使用子查询SELECT `userspk`.`avatar` AS `user_avatar`, 
`pkrecord`.`user_id`, 
`pkrecord`.`answer_record`, 
`pkrecord`.`id`, 
 MAX(`score`) AS `score`FROM pkrecordINNER 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原生語句2. 在子查询中对数据已经进行排序后,外层排序方式如果和子查询排序分数相同,都是分数倒序,外层的排序可以去掉,没有必要写两遍。

sql语句中的别名

别名在哪些情况使用

在 SQL 语句中,可以为表名称及字段(列)名称指定别名

  • 表名称指定别名

同时查询两张表的数据的时候: 未设置别名前:

SELECT article.title,article.content,user.username FROM article, userWHERE 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’
登入後複製
  • 使用可参数化的搜索条件,如=, >, >=, , !=, !>, !

  • 当需要验证是否有符合条件的记录时,使用exists,不要使用count(*),前者在第一个匹配记录处返回,后者需要遍历所有匹配记录

  • Where子句中列的顺序与需使用的索引顺序保持一致,不是所有数据库的优化器都能对此顺序进行优化,保持良好编程习惯(索引相关)

  • 不要在where子句中对字段进行运算或函数(索引相关)

  1. 如where  amount / 2 > 100,即使amount字段有索引,也无法使用,改成where amount > 100 * 2就可使用amount列上的索引

  2. 如where substring( Lastname, 1, 1) = ‘F’就无法使用Lastname列上的索引,而where Lastname like ‘F%’或者where Lastname >= ‘F’ and Lastname

  • 在有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条件,并用>=和=和

  • 調整join操作順序以使效能最優,join操作是自頂向下的,盡量把結果集小的兩個表關聯放在前面,可提高效能。 (join相關) 注意:索引和關聯我會單獨拿出來兩篇文章進行詳細講解,在這個注意事項中只是簡單提一下。

更多MySQL相關技術文章,請造訪MySQL教學欄位學習!

以上是如何寫優雅的SQL原生語句的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
sql
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板
關於我們 免責聲明 Sitemap
PHP中文網:公益線上PHP培訓,幫助PHP學習者快速成長!