《SQL 反模式》 學習筆記

小蔣不素小蔣發表於2020-04-19
SQL模式筆記

第一章 引言

GoF 所著的的《設計模式》，在軟體領域引入了“設計模式”（design pattern）的概念。

而後，Andrew Koenig 在 1995 年造了 反模式（anti-pattern） （又稱反面模式）這個詞，靈感來自於 GoF 所著的的《設計模式》。

反模式指的是在實踐中經常出現但又低效或是有待優化的設計模式，是用來解決問題的帶有共同性的不良方法。它們已經經過研究並分類，以防止日後重蹈覆轍，並能在研發尚未投產的系統時辨認出來。

所以，反模式是特殊的設計模式，而這種設計模式是欠妥的，起到了反效果。

但有的時候，出於權衡考量，也會使用反模式。

例如資料庫的結構中使用的反規範化設計。

下面的每一章，都會列舉一種特定場景下的反模式，然後再給出避免使用反模式的建議。

有個別章節，我略去了反模式，直接寫解決方案了。

第二章 亂穿馬路

假設有 Product 和 Account 兩個實體。

1、一對一關係

假設：Product 只有一個 Account（即 Account 也只有一個 Product）。

方案：只用一張表，用兩個欄位（Product + Account）關聯即可。

如無必要，就別用多個表，這會增加複雜度（除非考慮未來的擴充性等其他情況）。

2、一對多關係

假設：Product 可以有多個 Account。

方案1：兩張表，一個 Product 表，一個 ProductAccount 表，此表存 ProductId + AccountName。

ProductAccount 表稱之為從屬表。

方案2：只用一張表，即 Product 表，然後此表有個 Account 欄位，存以逗號分隔的 AccountName。

此為反模式，不推薦使用。

方案3：還有一種擴充性更好的、也是本人工作中更常用的做法，直接用下面 ”3、多對多關係“ 的方案 。

3、多對多關係

假設：Product 可以有多個 Account，Account 也可以有多個 Product。

方案：用三張表，一個 Product 表，一個 Account 表，一個 ProductAccount 表，此表存 ProductId + AccountId。

ProductAccount 表稱之為交叉表。

第三章 單純的樹

1、需求

建立一張表，存放（帖子的）評論（可巢狀回覆評論）。

2、方案1：鄰接表

新增 parent_id 列，指向同一張表的id。

這樣的設計叫做鄰接表。這可能是程式設計師們用來儲存分層結構資料中最普通的方案了。

缺點：

  • 查詢一個節點的所有後代很複雜
  • 從一棵樹中刪除一個節點會變得比較複雜。如果需要刪除一棵子樹，你不得不執行多次查詢來找到所有的後代節點（其實這點跟上一個點實質一樣），然後逐個從最低階別開始刪除這些節點以滿足外來鍵完整性。

[擴充]

某些品牌的資料庫管理系統提供擴充套件的 SQL 語句，來支援在鄰接表中儲存分層資料結構。

  • SQL-99 標準定義了遞迴查詢的表示式規範，使用 WITH 關鍵字加上公共表表示式。
  • oracle 可以使用層次化查詢 connect by 遍歷表資料。
  • postgreSQL 資料庫中，我們使用 RECURSIVE 引數配合 with 查詢來實現遍歷。如果安裝了 tablefunc 擴充套件，也可以使用 PG 版本的 connectby 函式。這個沒有Oracle那麼強大，但是可以滿足基本要求。
  • mysql 暫不支援。

這裡的遞迴查詢暫時不深究，待寫。

3、方案2：路徑列舉

建立一個 path 欄位，存路徑，如1/4/6/7/

缺點：

  • 資料庫不能確保路徑的格式總是正確或者路徑中的節點確實存在。依賴於應用程式的邏輯程式碼來維護路徑的字串，並且驗證字串的正確性的開銷很大。
  • 無論將 VARCHAR 的長度設定為多大，依舊存在長度限制，因而並不能夠支援樹結構的無限擴充套件。

    可以用 PG 的 text 型別，最高支援儲存 1G 的字串，應該是夠了。

4、方案3：巢狀集

建立 nsleft 和 nsright 欄位，儲存子孫節點的相關資訊，而不是節點的直接祖先.

每個節點通過如下的方式確定 nsleft 和nsright 的值：nsleft 的數值小於該節點所有後代的ID，同時 nsright 的值大於該節點所有後代的ID。這些數字和 comment_id 的值並沒有任何關聯。

確定這三個值（nsleft，comment_id，nsrigh）的簡單方法是對樹進行一次深度優先遍歷，在逐層深入的過程中依次遞增地分配 nsleft 的值，並在返回時依次遞增地分配 nsright 的值。

最後結果形如：

缺點：
如果簡單快速地查詢是整個程式中最重要的部分，巢狀集是最佳選擇——比操作單獨的節點要方便快捷很多。然而，巢狀集的插入和移動節點是比較複雜的，因為需要重新分配左右值，如果你的應用程式需要頻繁的插入、刪除節點，那麼巢狀集可能並不適合。

5、方案4：閉包表（推薦）

閉包表是解決分級儲存的一個簡單而優雅的解決方案，它記錄了樹中所有節點間的關係，而不僅僅只有那些直接的父子關係。

在設計評論系統時，我們額外建立了一張叫做 TreePaths 的表，它包含兩列：ancestor 和 descendant，每一列都是一個指向評論表的id的外來鍵。

TreePaths 表結構如下：

6、總結

​

設計 表 查詢子 查詢樹 插入 刪除 引用完整性
鄰接表 1 簡單 困難 簡單 簡單 是
遞迴查詢 1 簡單 簡單 簡單 簡單 是
列舉路徑 1 簡單 簡單 簡單 簡單 否
巢狀集 1 困難 簡單 困難 困難 否
閉包表 2 簡單 簡單 簡單 簡單 是

​
​
​鄰接表是最方便的設計，並且很多軟體開發者都瞭解它。

如果你使用的資料庫支援W ITH 或者 CONNECT BY PRIOR 的遞迴查詢，那能使得鄰接表的查詢更為高效。

閉包表是最通用的設計，並且本章所描述的設計中只有它能允許一個節點屬於多棵樹。它要求一張額外的表來儲存關係，使用空間換時間的方案減少操作過程中由冗餘的計算所造成的消耗。

我之前做過的評論功能，需求都會盡量簡化，例如弄成扁平化，只能回覆評論一次，即不能評論評論的評論。如果下次鄙人真的要實現這個複雜的評論功能了，關於閉包表的具體設計及操作實現，準備回頭再看原書。

第四章 需要 ID

1、什麼是偽主鍵

在這樣的表中，需要引入一個對於表的域模型無意義的新列來儲存一個偽值。這一列被用作這張表的主鍵，從而通過它來確定表中的一條記錄。這種型別的主鍵列我們通常稱其為偽主鍵或者代理鍵。

可以把 偽主鍵 理解成 偽鍵 或者 主鍵。

2、偽主鍵的作用

  • 確保一張表中的資料不會出現重複行；

    按照關係型資料庫的定義，表裡是不可以出現重複行的，但是實際中確實會出現，怎麼辦，引入偽鍵就不會重複了。

  • 在查詢中引用單獨的一行記錄；
  • 支援外來鍵。

3、各家資料庫產品中的偽主鍵

偽主鍵直到 SQL:2003 才成為一個標準，因而每個資料庫都使用自己特有的 SQL 擴充套件來實現偽主鍵，甚至不同資料庫中對於偽主鍵都有不同的名稱（不同的表述），如下表：

名稱 資料庫
AUTO_INCREMENT MySQL
GENERATOR Firebird, InterBase
IDENTITY DB2 Derby, Microsoft SQL Server, Sybase
ROWID SQLite
SEQUENCE DB2 Firebird, Informix, Ingres, Oracle, PostgreSQL
SERIAL MySQL, PostgreSQL

雖然各家資料庫產品的偽主鍵叫法不同，但是給偽主鍵指派的列名，確是出奇的一致，那就是 id。

第五章 不用鑰匙的入口

1、反模式 —— 不用外來鍵

有時你被迫使用不支援外來鍵約束的資料庫產品（比如 MySQL 的 MyISAM 儲存引擎，或者比 SQLite 3.6.19 早的版本）。

如果是這種情況，那你不得不使用別的方法來彌補。

2、推薦：使用外來鍵

外來鍵的好處：

  • 自動維持引用完整性（否則需要自己寫監控指令碼）
  • 級聯更新/刪除（否則需要自己寫邏輯程式碼）

總結來看就是：避免編寫不必要的程式碼，節省了大量開發、除錯以及維護時間。

軟體行業中每千行程式碼的平均缺陷數約為 15~50 個。在其他條件相同的情況下，越少的程式碼，意味著越少的缺陷。

外來鍵的缺點：

  • 需要多一點額外的系統開銷。

但這是值得的。

第六章 實體-屬性-值

1、需求

表支援可變（可擴充）屬性（列）。

例如：你有一個 Prodcut 表，記錄了兩種型別的產品：

  • 產品1：product_type = "電影"，此外還有 product_name、total_duration（總時長） 屬性。
  • 產品2：product_type = "圖書"，此外還有 product_name、total_page（總頁數） 屬性。

2、反模式

對於某些程式設計師來說，當他們需要支援可變屬性時，第一反應便是建立另一張表，將屬性當成行來儲存。

這樣的設計稱為實體—屬性—值，簡稱EAV。有時也稱之為：開放架構、無模式或者名—值對。

例如：

  • Prodcut 表：id
  • ProdcutAttr 表：id、product_id、attr_name、attr_value

    ProdcutAttr 表資料形如：

    • (1，1, "product_type", "電影")
    • (2，1, "product_name", "阿甘正傳")
    • (3，1, "total_duration", 120)
    • (4，2, "product_type", "圖書")
    • (5，2, "product_name", "簡愛")
    • (6，2, "total_page", 300)

3、推薦

（1）單表繼承

最簡單的設計是將所有相關的型別都存在一張表中，為所有型別的所有屬性都保留一列。同時，使用一個屬性來定義每一行表示的子型別。在這個例子中，這個屬性稱作issue_type。

對於所有的子型別來說，既有一些公共屬性，但同時又有一些子型別特有屬性。這些子型別特有屬性列必須支援空值，因為根據子型別的不同，有些屬性並不需要填寫，從而對於一條記錄來說，那些非空的項會變得比較零散。

例如：

  • Prodcut 表：id、product_type、product_name、total_duration、total_page

    Prodcut 表資料形如：

    • (1，"電影", "阿甘正傳", 120, NULL)
    • (2，"圖書", "簡愛", NULL, 300)

缺點：

  • 沒有任何的元資訊來記錄哪個屬性屬於哪個子型別

適用場景：

  • 當資料的子型別很少，以及子型別特殊屬性很少
  • 使用 Active Record 模式來訪問單表資料庫時
（2）實體表繼承

為每個子型別建立一張獨立的表。每個表包含那些屬於基類的共有屬性，同時也包含子型別特殊化的屬性。

例如：

  • ProdcutMovie 表：id、product_name、total_duration
  • ProdcutBook 表：id、product_name、total_page

缺點：

  • 很難將通用屬性和子類特有的屬性區分開來

    可以建立一個檢視聯合這些表，僅選擇公共的列。

  • 如果將一個新的屬性增加到通用屬性中，必須為每個子類表都加一遍。
（3）類表繼承

此種方法模擬了繼承，把表當成物件導向裡的類。建立一張基類表，包含所有子型別的公共屬性。對於每個子型別，建立一個獨立的表，通過外來鍵和基類表相連。

這裡需要用到資料庫產品自帶的表繼承功能。

例如：

  • Prodcut 表：id、product_name
  • ProdcutMovie 表：id、total_duration
  • ProdcutBook 表：id、total_page
（4）半結構化資料模型

使用一個BLOB 列來儲存資料，用 XML 或者 JSON 格式——同時包含了屬性的名字和值。Martin Fowler 稱這個模式為：序列化大物件塊（Serialized BLOB）。

優點：優異的擴充套件性

缺點：就是在這樣的一個結構中，SQL 基本上沒有辦法獲取某個指定的屬性。你不能在一行blob 欄位中簡單地選擇一個獨立的屬性，並對其進行限制、聚合運算、排序等其他操作。你必須獲取整個blob 欄位結構並通過程式去解碼並且解釋這些屬性。

但現在的資料庫，例如 PG，可以直接支援使用 JSON(B) or XML 的資料型別。所以不會存在必須整個獲取再解析的麻煩了。

第七章 多型關聯

1、需求

怎麼宣告一個指向多張表的外來鍵？

例如，Comments 表的外來鍵（issue_id）要引用 Bugs 表 or FeatureRequests 表。形如（這種寫法是無效的）：

FOREIGN KEY  (issue id)
REFERENCES Bugs (issue_id) OR FeatureRequests (issue_id)

2、反模式

有一個解決方案已經流行到足以正式命名了，那就是：多型關聯。有時候也叫做雜亂關聯。

例如：
除了 Comments 表 issue_id 這個外來鍵之外，你必須再新增一列：issue_type，這個額外的列記錄了當前行所引用的表名，取值範圍是 "Bugs" / "FeatureRequests"。

缺點：沒有任何保障資料完整性的手段來確保 Comments.issue_id 中的值在其父表中存在。

當你使用一個物件導向的框架（諸如Hibernate）時，多型關聯似乎是不可避免的。這種型別的框架通過良好的邏輯封裝來減少使用多型關聯的風險(即依賴上層程式程式碼而不是資料庫的後設資料)。如果你選擇了一個成熟、有信譽的框架，那可以相信框架的作者已經完整地實現了相關的邏輯程式碼，不會造成錯誤。

3、推薦

（1）交叉表

把 Comments 表向下拆分，分出兩個多的交叉表，即 BugsComments 和 FeatureRequestsComments

（2）共用的超級表

基於 Bugs 表和 FeatureRequests 表，建立共用的超級表：Issues。

第八章 多列屬性

1、反模式 —— 可擴充的列

例如： 有一個 Bug 表，每個 Bug 自身可能會有多個 tag。

CREATE TABLE Bug
bug_id SERIAL PRIMARY KEY
description VARCHAR (1000)
tagl VARCHAR (20)
tag2 VARCHAR (20)
tag3 VARCHAR (20)

每次要修改 Bug 自身的最大 tag 數，會動表結構，可擴充性很差。

2、推薦

在原有 Bug 表的基礎上，再建立一個 BugTag 表。包含下面幾列：

  • bug_id
  • tag

第九章 後設資料分裂

1、反模式

用形如 Crevenue2002、Crevenue2003、Crevenue2004 的多列，來記錄銷售額。

這裡的問題在於部分資料存在於列名中，即混淆了後設資料和資料。

還有一種常見的反模式是，將資料（年份）追加在基本表名之後。

2、推薦

如果是因為同一張表資料量太多導致這種反模式，建議：

（1）水平分割槽（or 分片）

你僅需要定義一些規則來拆分一張邏輯表，資料庫會為你管理餘下的所有事情。物理上來說，表的確是被拆分了，但你依舊可以像查詢單一表那樣執行SQL 查詢語句。

分割槽在 SQL 標準中並沒有定義，因此每個不同的資料庫實現這一功能的方式都是非標準的。

（2）垂直分割槽（or 分片）

鑑於水平分割槽是根據行來對錶進行拆分的，垂直分割槽就是根據列來對錶進行拆分

比如說，會在Products 表中為每個單獨的產品儲存一份安裝檔案。這種檔案通常都很大，但BLOB 型別的列可以儲存龐大的二進位制資料。如果你有使用萬用字元“*”進行查詢的習慣，那麼將如此大的檔案儲存在Products 表中，而且又不經常使用，很容易就會在查詢時遺漏這一點，從而造成不必要的效能問題。

正確的做法是將BLOB 列存在另一張表中，和Products 表分離但又與其相關聯。

（3）建立關聯表

把列轉為行。

第十章 取整錯誤

1、為什麼

關於計算機二進位制浮點數表示法導致的精度丟失和取整錯誤，可以看我這一篇：《關於 JavaScript 的 精度丟失 與 近似舍入》，原理是一樣的。

2、怎麼辦

解決方案：使用 SQL 中的 NUMERIC 或 DECIMAL 型別來代替 FLOAT 及與其類似的資料型別進行固定精度的小數儲存。

哪怕不是存小數而是存整數，也不要用 FLOAT！同樣會存在錯誤隱患。

第十一章 每日新花樣

需求：限定列的有效值。

1、反模式

1、CHECK 約束

缺點：

  • 添刪有效值不方便，需要重新 drop 並 create 約束。
  • 取列的有效值的 list 很麻煩，且不可複用。

2、域

缺點：屬於資料庫高階操作，殺雞焉用牛刀。不贅述了。


3、使用者自定義型別（UDT）

缺點：屬於資料庫高階操作，殺雞焉用牛刀。不贅述了。

2、推薦

1、使用列舉型別 ENUM

優點：

  • 添刪有效值很方便
  • 可複用。可以把有效值寫在應用程式碼中，結合 ORM，即可以 for 資料庫，也可以 for 前端顯示（例如 展示在 select 元件）

2、建立一個單獨的表，存列的有效值，其他表使用外來鍵引用

優點：

  • 上面 ENUM 的優點都有。
  • 更加靈活、擴充性更強。

第十二章 幽靈檔案

原始圖片檔案可以以二進位制格式儲存在 BLOB 型別中，就像之前我們儲存超長欄位那樣。

然而，很多人選擇將圖片儲存在檔案系統中，然後在資料庫裡用 VARCHAR 型別來記錄對應的路徑。這其實是一種反模式。

具體要不要用這種反模式，見仁見智，要按照具體使用場景來判斷。

現在普遍還是流行這種反模式，例如我司，因為靜態資源都是上傳到 OSS 託管，有 CDN 加成。

第十三章 亂用索引

略

第十四章 對未知的恐懼

1、需求：如何篩選出兩個列值不相等的行?

假設：我們有 test 表：

id left right
1 111 222
2 333 333
3 444 NULL
4 NULL 555
5 NULL NULL

正確結果是 id 為 1、3、4 的行。

2、反模式

錯誤方法：直接使用 where "left" != "right" ，但 != 對 NULL 無效。

正如下面這個例子：

select 1 != 1; #f
select 1 != 2; #t
select 1 != NULL; #null（不是我們想要的結果，應該返回 t)
select NULL != NULL; #null（不是我們想要的結果，應該返回 f)

後兩種情況結果為 NULL，是因為 sql 是三值邏輯而不是二值邏輯，具體可以看我之前的一篇：《SQL基礎教程》+《SQL進階教程》學習筆記，裡面有詳細介紹。

3、推薦

（1）將 NULL 視為特殊值

將 NULL 視為特殊值，額外用 IS ( NOT ) NULL 判斷：where "left" != "right" or ( "left" is null and "right" is not null ) or ( "left" is not null and "right" is null )

這種寫法很累贅。

（2）IS ( NOT ) DISTINCT FROM

直接用 IS DISTINCT FROM，即：where "left" IS DISTINCT FROM "right" ，不需要額外對 NULL 判斷。


IS ( NOT ) DISTINCT FROM 的支援情況：

每個資料庫對 IS ( NOT ) DISTINCT FROM 的支援是不同的。PostgreSQL、IBM DB2 和 Firebird 直接支援它，Oracle 和 Microsoft SQL Server 暫時還不支援。MySQL 提供了一個專有的表示式 <=>，它的工作邏輯和 IS NOT DISTINCT FROM 一致。

第十五章 模稜兩可的分組

1、反模式

例如，有 test 表：

id type name join_time
1 老師 趙老師 2020-01-01
2 老師 錢老師 2020-01-02
3 同學 張三 2020-01-03
4 同學 李四 2020-01-04
5 同學 王五 2020-01-05

需求：我們需要在 老師 or 同學 分別裡找出 join_time 最早的一條記錄。

執行 SELECT "type", MIN("join_time"), "name" FROM "test" GROUP BY "type"

name 列就是有歧義的列，可能包含不可預測的和不可靠的資料：

  • 在 MySQL 中，返回的值是這一組結果中的第一條記錄。
  • 在 Postgres 中，會報錯。

2、推薦

解決方案：無歧義地使用列。

（1）只查詢功能依賴的列

最直接的解決方案就是將有歧義的列排除出查詢。

執行 SELECT "type" FROM "test" GROUP BY "type"

但這滿足不了我們的需求，pass。

（2）對額外的列使用聚合函式

執行 SELECT "type", MIN("join_time"), MIN("name") FROM "test" GROUP BY "type"

如果不能保證 MIN("join_time") 和 MIN("name") 是指向同一行，那這個寫法就是錯的。有風險，pass。

（3）使用關聯子查詢
SELECT * FROM test as t1
WHERE NOT EXISTS 
(
	SELECT * FROM test as t2
	WHERE t1."type" = t2."type" and t1.join_time > t2.join_time 
)

缺點：效能不好。


[擴充] 用關聯子查詢寫出來的思路：

涉及 SQL 基礎的全程量化和存在量化的知識點，詳細可參考我的舊文：《SQL基礎教程》+《SQL進階教程》學習筆記

如果需求變成：我們需要在 老師 or 同學 分別裡找出 join_time 最晚的一條記錄，那隻需要把 t1.join_time > t2.join_time 變成 t1.join_time < t2.join_time 即可：

SELECT * FROM test as t1
WHERE NOT EXISTS 
(
	SELECT * FROM test as t2
	WHERE t1."type" = t2."type" and t1.join_time > t2.join_time 
)
（4）使用衍生表 JOIN
SELECT * FROM test as t1 
INNER JOIN
(
	SELECT "type", MIN("join_time") as "join_time" FROM test  
	GROUP BY "type" 
) as t2 
ON t1.join_time = t2.join_time

缺點：效能不好。


如果需求變成：我們需要在 老師 or 同學 分別裡找出 join_time 最晚的一條記錄，那隻需要把 MIN("join_time") 變成 MAX("join_time") 即可：

SELECT * FROM test as t1 
INNER JOIN
(
	SELECT "type", MAX("join_time") as "join_time" FROM test  
	GROUP BY "type" 
) as t2 
ON t1.join_time = t2.join_time
（5）直接使用 LEFT JOIN
SELECT * FROM test as t1 
LEFT JOIN test as t2
ON t1."type" = t2."type" 
AND  
(
		t1.join_time > t2.join_time 
)
WHERE t2."id" IS NULL

解釋：t1.join_time > t2.join_time 搭配 WHERE t2."id" IS NULL 是利用 LEFT JOIN 的特性，即如果找到匹配行則可以生成多行，但若找不到匹配行，則另一邊置 NUll。

缺點：效能稍好，但是較難維護。


如果需求變成：我們需要在 老師 or 同學 分別裡找出 join_time 最晚的一條記錄，那隻需要把 t1.join_time > t2.join_time 變成 t1.join_time < t2.join_time 即可：

SELECT * FROM test as t1 
LEFT JOIN test as t2
ON t1."type" = t2."type" 
AND  
(
		t1.join_time < t2.join_time 
)
WHERE t2."id" IS NULL
（6）視窗函式
SELECT
	* 
FROM
	(
	SELECT
		*,
		RANK() OVER ( PARTITION BY "type" ORDER BY "join_time" ASC ) AS "rank" 
	FROM
	  test  
	) as t1
WHERE
	"t1"."rank" = 1

關於更多視窗函式的介紹，可看我的舊文：《SQL基礎教程》+《SQL進階教程》學習筆記


如果需求變成：我們需要在 老師 or 同學 分別裡找出 join_time 最晚的一條記錄，那隻需要把 ASC 變成 DESC 即可：

SELECT
	* 
FROM
	(
	SELECT
		*,
		RANK() OVER ( PARTITION BY "type" ORDER BY "join_time" DESC ) AS "rank" 
	FROM
	  test  
	) as t1
WHERE
	"t1"."rank" = 1

第十六章 隨機選擇

相比於將整個資料集讀入程式中再取出樣例資料集，直接通過資料庫查詢拿出這些樣例資料集會更好。

本章的目標就是要寫出一個僅返回隨機資料樣本的高效 SQL 查詢。

1、傳統方法、random()

SELECT * FROM test ORDER BY random() limit 1

缺點：

  • 整個排序過程無法利用索引
  • 效能不好。好不容易對整個資料集完成排序，但絕大多數的結果都浪費了，因為除了返回第一行之外，其他結果都立刻被丟棄了。

2、推薦方法1、從 1 到最大值之間隨機選擇

一種避免對所有資料進行排序的方法，就是在 1 到最大的主鍵值之間隨機選擇一個。

但要考慮 1 到最大值之間有縫隙的情況。

利用 JOIN：

SELECT
	t1.* 
FROM
	test AS t1
	JOIN ( SELECT CEIL( random() * ( SELECT MAX ( "id" ) FROM test ) ) AS "id" ) AS t2
ON
	t1."id" >= t2."id"
ORDER BY
	t1."id" 
LIMIT 1

3、推薦方法2、使用偏移量選擇隨機行

計算總的資料行數，隨機選擇0 到總行數之間的一個值，然後用這個值作為位移來獲取隨機行。

利用 OFFSET：

SELECT
	* 
FROM
	test 
	LIMIT 1 OFFSET ( 
		SELECT CEIL( 
			random() * ( SELECT COUNT ( * ) FROM test )
		) - 1 
	)

4、推薦方法3、專有解決方案

每種資料庫都可能針對這個需求提供獨有的解決方案：

-- Microsoft SQL Server 2005 增加了一個 TABLE-SAMPLE 子句。
-- Oracle 使用了一個類似的 SAMPLE 子句，比如返回表中1%的記錄。
-- Postgres 也有類似的叫 TABLESAMPLE。

但是這種取樣的方法返回結果的行數很不穩定，感覺還是不推薦了。

第十七章 可憐人的搜尋引擎

1、需求

全文搜尋。

2、反模式

使用 LIKE 或者正規表示式進行模式匹配搜尋。

缺點：使用模式匹配操作符的最大缺點就在於效能問題。它們無法從傳統的索引上受益，因此必須進行全表遍歷。

3、推薦

解決方案：使用正確的工具。

（1）資料庫擴充套件

每個大品牌的資料庫都有對全文搜尋這個需求的解決方案。

例如，PostgreSQL 8.3 提供了一個複雜的可大量配置的方式，來將文字轉化為可搜尋的詞彙集合，並且讓這些文件能夠進行模式匹配搜尋。即，為了最大地提升效能，你需要將內容存兩份：一份為原始文字格式，另一份為特殊的 TSVECTOR 型別的可搜尋格式。

空間換時間。

① 步驟：

建表時建立 TSVECTOR 資料型別的列。

② 步驟：

你需要確保 TSVECTOR 列的內容和你所想要搜尋的列的內容同步。PostgreSQL 提供了一個內建的觸發器來簡化這一操作。

觸發器寫法略，可看原書。

③ 步驟：

你也應該同時在 TSVECTOR 列上建立一個反向索引（GIN）。

寫法略，可看原書。

④ 步驟：

在做完這一切之後，就可以在全文索引的幫助下使用PostgreSQL 的文字搜尋操作符@@來高效地執行搜尋查詢。

寫法略，可看原書。

（2）自己實現 反向索引

太複雜，略。

（3）第三方搜尋引擎

你不必使用 SQL 來解決所有問題。

兩個產品：Sphinx Search 和 Apache Lucene。

使用略，可看原書。

第十八章 義大利麵條式查詢

1、反模式

一條精心設計的複雜 SQL 查詢，相比於那些直接簡單的查詢來說，不得不使用很多的JOIN、關聯子查詢和其他讓 SQL 引擎難以優化和快速執行的操作符。而程式設計師直覺地認為越少的SQL 執行次數效能越好。

2、推薦

目標：減少 SQL 查詢數量。

解決方案：

  • 分而治之，一步一個腳印
  • 你可以將幾個查詢的結果進行 UNION 操作，從而最終得到一個結果集

好處：

  • 效能更好
  • 便於開發、維護

第十九章 隱式的列

1、反模式

我所遇到的程式設計師使用SQL萬用字元時問得最多的問題是：“有沒有選擇除了幾個我不想要的列之外所有列的方法？

答案是“沒有”。

其實我還是希望資料庫廠商能加上，現在網上有很多 hack 的方法，需求畢竟是在的。誒。

2、推薦

解決方案：明確列出列名，而不是使用萬用字元或者隱式列的列表。

第二十章 明文密碼

可以參考我之前的文章：《資料庫裡賬號的密碼，需要怎樣安全的存放？—— 密碼雜湊（Password Hash）》

第二十一章 SQL 注入

1、需求

防止 SQL 注入。

2、反模式

（1）轉義

比如，在PHP 的 PDO 擴充套件中，可以使用一個 quote()函式來定義一個包含引號的字串或者還原一個字串中的引號字元。

3、推薦

解決方案：不信任任何人。

（1）check 資料
  • 過濾輸入內容.比如在 PHP 中，可以使用filter 擴充套件

    Node.js 的 joi 庫。

  • 正規表示式來匹配安全的子串

    用上一條的過濾庫也可以實現。

  • 用型別轉換函式
（2）引數化動態內容

你應該使用查詢引數將其和 SQL 表示式分離。

沒有哪種 SQL 注入的攻擊能夠改變一個引數化了的查詢的語法結構。

缺點：

① 會影響優化器的效果，最終影響效能。

比如說，假設在 Accounts 表中有一個 is_active 列。這一列中99%的記錄都是真實值。對 is_active = false 的查詢會得益於這一列上的索引，但對於 is_active = true 的查詢卻會在讀取索引的過程中浪費很多時間。然而，如果你用了一個引數 is_active = ? 來構造這個表示式，優化器不知道在預處理這條語句的時候你最終會傳入哪個值，因此很有可能就選擇了錯誤的優化方案。

要規避這樣的問題，直接將變數內容插入到SQL 語句中會是更好的方法，不要去理會查詢引數。一旦你決定這麼做了，就一定要小心地引用字串。

可以結合下面的 ”（3）將使用者與程式碼隔離“ 一起使用。


② 這還不是一個通用的解決方案，因為查詢引數總被視為是一個字面值。

例如：

  • 多個值的列表不可以當成單一引數： 例如 in（x，x，x）

    解決方案：使用了一些 PHP 內建的陣列函式來生成一個佔位符陣列。

  • 表名、列名、SQL 關鍵字 無法作為引數。

    解決方案：可以用儲存過程；或者通過事先在應用邏輯程式碼裡，先用字串拼接的方式生成好 sql 程式碼。


③ 不好除錯

這意味著，如果你獲取到一個預先準備好的SQL 查詢語句，它裡面是不會包含任何實際的引數值的。當你除錯或者記錄查詢時，很方便就能看到帶有引數值的SQL 語句，但這些值永遠不會以可讀的SQL 形式整合到查詢中去。

解決方案：除錯動態化SQL 語句的最好方法，就是將準備階段的帶有佔位符的查詢語句和執行階段傳入的引數都記錄下來。（自己動手，豐衣足食。）

（3）資料訪問框架

你可能看過資料訪問框架的擁護者聲稱他們的庫能夠抵禦所有SQL 注入的攻擊。對於所有允許你使用字串方式傳入SQL 語句的框架來說，這都是扯淡。

沒有任何框架能強制你寫出安全的 SQL 程式碼。一個框架可能會提供一系列簡單的函式來幫助你，但很容易就能繞開這些函式，然後使用通常的修改字串的辦法來編寫不安全的SQL語句。

就是你得保證自己寫的是符合框架規範的寫法，不然人為因素還是會導致出錯。

（4）儲存過程

略

（5）將使用者與程式碼隔離

將請求的引數作為索引值去查詢預先定義好的值，然後用這些預先定義好的值來組織 SQL 查詢語句。

例如把請求的引數經過 if else ，來分配進預先定義好的 SQL 查詢語句。

（6）找個可靠的人來幫你審查程式碼

找到瑕疵的最好方法就是再找一雙眼睛一起來盯著看。

有條件可以結對程式設計。

第二十二章 偽鍵潔癖

1、反模式

不能忍受主鍵中間出現不連續的缺位。

重用主鍵並不是一個好主意，因為斷檔往往是由於一些合理的刪除或者回滾資料所造成的。

2、推薦

（1）克服心裡障礙

它們不一定非得是連續值才能用來標記行。

將偽鍵當做行的唯一性標識，但它們不是行號。別把主鍵值和行號混為一談。


問：怎麼抵擋一個希望清理資料庫中偽鍵斷檔的老闆的請求？
答：這是一個溝通方面的問題，而不是技術問題。

（2）使用GUID

GUID （Globally Unique Identifier 全域性唯一識別符號）是一個128 位的偽隨機數（通常使用32 個十六進位制字元表示）。

GUID 也稱 UUID (Universally unique identifier 通用唯一識別符號)。

GUID 相比傳統的偽鍵生成方法來說，至少有如下兩個優勢：

  • 可以在多個資料庫伺服器上併發地生成偽鍵，而不用擔心生成同樣的值。
  • 沒有人會再抱怨有斷檔——他們會忙於抱怨輸入32 個十六進位制字元做主鍵。

第二十三章 非禮勿視

1、反模式 —— 忽略錯誤處理

“我不會讓錯誤處理弄亂了我的程式碼結構的。”

導致問題：

  • 程式碼健壯性不好
  • 出現錯誤不好回溯
  • 使用者體驗差（使用者看不見程式碼，他們只能看見輸出。當一個致命錯誤沒有被處理時，使用者就只能看到一
    個白屏，或者是一個不完整的異常資訊。）

2、推薦 —— 優雅地從錯誤中恢復

一些電腦科學家推測在一個穩固的程式中，至少有50%的程式碼是用來進行錯誤處理的。

所有喜歡跳舞的人都知道，跳錯舞步是不可避免的。優雅的祕訣就是弄明白怎麼挽回。給自己一個瞭解錯誤產生原因的機會，然後就可以快速響應，在任何人注意到你出醜之前，神不知鬼不覺地回到應有的節奏上。

第二十四章 外交豁免權

1、反模式

技術債務（technical debt），是程式設計及軟體工程中的一個比喻。指開發人員為了加速軟體開發，在應該採用最佳方案時進行了妥協，改用了短期內能加速軟體開發的方案，從而在未來給自己帶來的額外開發負擔。這種技術上的選擇，就像一筆債務一樣，雖然眼前看起來可以得到好處，但必須在未來償還。軟體工程師必須付出額外的時間和精力持續修復之前的妥協所造成的問題及副作用，或是進行重構，把架構改善為最佳實現方式。

2、推薦

  • 畫實體關係圖（ER 圖）,更復雜一點的ER 圖包含了列、主鍵、索引和其他資料庫物件。

    還有些工具能夠通過SQL 指令碼或者執行中的資料庫直接通過反向工程得到 ER 圖。

  • 寫文件
  • 原始碼管理
  • 測試

[擴充] 版本管理 之 管理資料庫：

版本管理工具管理了程式碼，但並沒有管理資料庫。Ruby on Rails 提供了一種技術叫做“遷移”，用來將版本控制應用到資料庫例項的升級管理上。

大多數其他的網站開發框架，包括PHP 的Doctrine、Python 的Django 以及微軟的 ASP.NET，都支援類似於Rails 的“遷移”這樣的特性。

我目前 Node.js 用的 sequelize 就包含了這種資料庫的遷移指令碼。

缺點：但它們還不是完美的，只能處理一些簡單型別的結構變更。而且從根本上說，它們在原有版本控制服務之外又建立了一個版本系統。

第二十五章 魔豆

略

好詞好句

所謂專家，就是在一個很小的領域裡把所有錯誤都犯過了的人。 —— 尼爾斯·玻爾

規範僅僅在它有幫助時才是好的。

Mitch Ratcliffe 說：“計算機是人類歷史中最容易讓你犯更多錯誤的發明……除了手槍和龍舌蘭之外。”

相關文章