SQL進階

徵羽宮發表於2023-11-12
SQL
系列文章：
MYSQL的資料型別和常用語句 Mysql的一些筆記 SQL進階 InnoDB引擎

首先，先說結論，Sql 進階最重要的一點就是理解 SQL是一種面向集合的程式語言

SQL是一種面向集合語言

很多人不會寫 SQL 或者寫的不好的主要原因就是沒有認識到這一點，用傳統的程式語言的思維去理解 SQL, 把資料表想象成一個 Excel 或者 多維陣列，簡單的查詢透過獲取想象中 Excel 中的某些行解決問題，複雜的查詢總是希望能夠先把自己腦海中的多個 Excel 合併到一個 Excel 然後再取出其中的某些行。 一旦要合併的 Excel 較多，或者壓根不能合併，就變成了無解的問題。還有人抱怨 Sql 沒有 變數 和 迴圈 ，搖搖頭然後把結果集的處理交給自己熟悉的程式語言。

之所以有上面的誤區，是因為大部分人先掌握了一門語言後會產生一種慣性思維，會用已有的程式語言的思維方式來思考新的問題。對於 SQL 這種面向集合的完全不同的程式語言必須拋開已有程式語言的認知去了解它。

比如我們之前把 JOIN 稱為表連線，現在我們把它想象成集合的關係， 比如 INNER JOIN 是求兩個集合的交集，LEFT JOIN 是左邊集合加右面集合的一部分，UNION 是求兩個集合的並集， 當然 table 和 子查詢的結果, 我們可以把它們看成一個又一個的集合。整個查詢過程變成透過集合運算獲取結果集的一個過程。

思路轉換過來，很多以前 SQL 看似沒法解決的問題就變得可以解決了。

舉個例子：
假設你現在一家水果攤上班(不要考慮水果攤養不養的起程式設計師)，老闆說今天公司開業大酬賓(也不要考慮水果攤算不算公司)，凡是今天進店的顧客都可以得到隨機贈送的水果兩個，你需要寫一個隨機生成兩個水果組合的新功能來實現老闆的願望。

顯然這個功能很簡單，就是先得到水果攤所有水果組成的集合的笛卡爾積，然後每次從笛卡爾積中隨機抽取一個即可。以前你可能是先從資料庫查出來所有的水果。 然後用程式語言寫一個求笛卡爾積的演算法, 這個演算法顯然不會太簡單 O(n^2)。 現在你知道 SQL 是一個 面向集合 的程式語言，集合語言求笛卡爾積可能會很簡單。 事實也確實是這樣：

// 我這裡用使用者表代替水果表進行測試 
SELECT `A`.`name`, `B`.`name` FROM
(SELECT `id`, `name` FROM `users` LIMIT 50 OFFSET 0) AS `A`,
(SELECT `id`, `name` FROM `users` LIMIT 50 OFFSET 0) AS `B`;

圖片

可以看到我模擬了50種水果， 2500種可能， 只用了一行程式碼加 25ms 就得到了結果。

有沒有感覺很神奇？ 然而SQL 能做的顯然還有更多。比如前幾天論壇裡一個求交集的SQL問題 問答：請教一個sql語句,我實現這個目前需要查兩次和求交集才能取到,如何使... ，最佳答案直接 INNER JOIN, 我的答案沒求交集， 也能得到結果。這就是SQL語言的魅力。

SQL中沒有順序

首先考慮下面這個問題
在不考慮快取的情況下， 下面那一條語句的查詢速度比較快？

SELECT * FROM `users` WHERE `id` IN (1, 2, 3, 4, 5, 6, 7, 8 ,9, 10);
SELECT * FROM `users` WHERE `id` IN (100001, 100002, 100003, 100004, 100005, 100006, 100007, 100008 , 100009, 100010);

答案是一樣快，我在一張 50萬條資料的使用者表中進行了測試， 都是8ms左右，沒有區別。原因也顯而易見，因為 SQL 作為面向集合的語言，這兩條語句都是從集合中獲取集合的一部分，沒有本質區別， 再往底層講你可以從索引上理解這個問題。總之它是一個集合，而集合是沒有順序的，不是你想象中的一個有順序的 Excel 或者 多維陣列。

雖然集合沒有順序，但並不是說結果集是沒有順序的。SQL 在進行一些操作時可能會在內部進行排序。這種排序行為就會消耗伺服器效能和時間，所以瞭解那些可能會引起內部排序的操作是有意義的。 常見的會引起SQL內部對集合進行排序的操作有：

  1. GROUP BY
  2. ORDER BY
  3. 各種聚合函式， 比如 SUM AVG 等等， SQL 為了避免重複計算會在底層先進行排序。
  4. DISTINCT，同樣為了避免重複計算。
  5. 集合運算子 比如 JOIN EXCEPT 等等， 這裡 SQL 是為了避免重複的集合元素所以需要進行排序

SQL中的層級

考慮下面這個問題

下面這條查詢重名使用者的SQL語句有什麼問題 ？

SELECT `id`, `name`, count(`name`) as count FROM `users` GROUP BY `name` LIMIT 200 OFFSET 0;

可能有的人覺得沒問題， 有的人在自己的資料庫跑了一下發現沒問題， 也有的人跑了一下發現報下面的錯。

 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

翻譯過來，意思就是說 id 欄位不依賴於 GROUP BY 中的列，不符合 only_full_group_by 這個 sql mode。

為什麼會出現這種情況呢？ 問題的關鍵在於 GROUP BY 之後得到的是分組(聚合)後的資料，原來操作的是行，現在操作的是集合。比如說行是0階的話，那麼集合就是1階，它倆完全不在一個層級。不同的層級之間等級森嚴， 不能相提並論。

瞭解了層級的概念後，我們再來說 WHERE 和 HAVING 的區別。

有使用過這兩個關鍵字的小夥伴可能說的上來， WHERE 一般寫在 GROUP BY 之前，HAVING 一般寫在 GROUP BY 之後。

比如下面這樣：

SELECT `name`, count(`name`) as count FROM `users` WHERE `id` < 10000 GROUP BY `name` HAVING count(`name`) > 0 ORDER BY count(name) DESC;

在瞭解了 SQL 的層級之後，我們就會對它倆的區別有更深刻的瞭解， 即：
它倆一個操作的是行這種 0階的物件， 另一個操作的是集合這樣 1階的物件。

SQL中的子查詢

我們已經瞭解了 子查詢 和 table 都是集合， 所以可以像操作 table 一樣操作 子查詢 比如可以寫在 FROM 後面， 可以和其他的 table 和 子查詢 進行各種集合運算等等。但是需要注意的是 table 是有索引的， 子查詢 得到的是新的集合沒有索引. 子查詢 的好處在於它可以在集合運算前控制集合的大小，但是並不能保證 子查詢 之間的集合運算效率必定比 table 之間更好。

然後說到子查詢， 子查詢有一個 EXISTS 特別好用, 它可以用來判斷一個集合是否有元素；

SELECT * FROM users WHERE EXISTS (SELECT * FROM `components`) LIMIT 20 OFFSET 0;
SELECT * FROM users as A WHERE EXISTS (SELECT * FROM users WHERE users.id = A.id AND users.id = 1) AND id IN  (1, 2) LIMIT 20 OFFSET 0;

在某些情況下 WHERE EXISTS 要比 WHERE IN 查詢效率高，WHERE IN 的時候是先把括號裡面的結果查出來，再進行判斷。 而 WHERE EXISTS 可能不需要查出所有的記錄就能返回值。

總結

SQL語言作為一種面向集合的語言，正確的理解它的本質，會對使用好這門語言有所幫助。本文是 MICK 的 SQL進階教程 一書的讀後感，有一些我不太深刻的知識沒有一一在這篇文章中羅列出來， 當然也可能出現錯誤。 歡迎大家指正， 感興趣的小夥伴也可以直接自己買這本書看， 應該是會有所幫助的。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章