- 10 建構索引時的空
- 11 仔細考慮索引的建議以減少索引與資料掃描
- 12 索引不只用於過濾
- 13 不要濫用觸發器
- 14 以過濾索引包括或排除一組資料
- 15 宣告約束代替程式檢查
- 16 認識你的產品使用的SQL並據此撰寫
- 17 知道何時使用索引中的計算結果
確保你的SQL查詢很好的執行的關鍵之一是適當的製作索引
10 建構索引時的空
- 檢查你要索引的欄是否帶有空值。
- 如果想要搜尋空值，但欄大部分的值可能是NULL，最好不要對該欄製作索引。這也表示資料表可能需要重新設計。
- 想要更快的搜尋欄但大部分的值為NULL時，若資料庫有支援則建構沒有空值的索引。
- 每個資料庫系統對索引中的空值的支援不同。建構可能帶有空值的欄的索引前要確保你知道資料庫系統的選項。
11 仔細考慮索引的建議以減少索引與資料掃描
- 分析資料以建構提升效能的索引。
- 確保你建構的索引會被使用。
12 索引不只用於過濾
- WHERE 句子中的欄是否有索引會影響查詢的效能。
- SELECT 句子中的欄是否有索引也會影響查詢的效能。
- 兩個資料表的欄是否有索引會影響連接的效能。
- 索引會影響 ORDER BY 句子的效能。
- 存在多個索引會影響寫入操作。
13 不要濫用觸發器
觸發器合適的原因:
- 維護重複或衍生資料
- 複雜的欄約束
- 複雜的預設值
- 跨資料庫參考完整性
注意事項:
- 在使用觸發器的情況下，對檢視表而非資料表建構觸發器比較好。這會讓事情更簡單，因為你可能不會想要觸發器在大批量匯入/匯出操作時啟動，但需要在應用程式中啟動。
- DBMS對約束或預設值的能力有不同的限制。舉例來說，有些DBMS並不允許以子查詢建構 CHECK 約束，必須以觸發器替代。檢查你的 DBMS 的支件以判別是否能夠完成你需要的工作而不動用到觸發器。
14 以過濾索引包括或排除一組資料
- 過濾索引在索引只用於一小部分列時對節省空間有幫助。
- 過濾索引可用於實作一部份列(例如 WHERE active = ‘Y’) 的獨特約束。
- 過濾索引可用於避免排序操作。
- 分割資料表可提供類似過濾索引無須維護其他索引的好處。
15 宣告約束代替程式檢查
約束有以下六種:
- NOT NULL 預設上，資料表的欄可保存空值。NOT NULL 約束可藉由拒絕接受空值而確保欄一定有值。
- UNIQUE UNIQUE 約束確保指定欄不能輸入重複值。你可以使用 UNIQUE 約束確保未參加主鍵的欄不會有重複值。與 PRIMARY KEY 約束不同， UNIQUE 約束容許空值。
- PRIMARY KEY 類似 UNIQUE 約束，PRIMARY KEY 約束限制資料庫的資料表的每一筆紀錄能獨特的識別。除了帶有獨特值外，PRIMARY KEY 也不能帶有空值。一個資料表可定義多個 UNIQUE 約束，但只能定義一個 PRIMARY KEY 約束。
- FOREIGN KEY 一個資料表的外來鍵指向另一個資料表的主鍵。
- CHECK 約束可對單一欄或資料表定義。在單一欄定義 CHECK 約束時，只有指定值可儲存在該欄。定義於資料表時，特定欄的值會根據同一列的其他欄的值做限制。
- DEFAULT DEFAULT 句子用於定義欄的預設值。若新增紀錄時沒有指定其他值，資料庫系統會使用預設值。
注意事項
- 技術上來說，根據 SQL 標準的定義，DEFAULT 句子並非約束。但它可用於強制商業規則，通常會與 NOT NULL 約束並用。
- SQL Server 在 UNIQUE 索引約束中只允許欄有一個空值。除非加上 WHERE NOT NULL 過濾，否則 DB2 在 UNIQUE 索引約束中允許欄有一個空值。
有幾種機制可實作程序性參考完整性
- 用戶端應用程式的程式
- 預儲程序
- 觸發器
16 認識你的產品使用的SQL並據此撰寫
| 排序結果集 | 限制結果集 | BOOLEAN 資料型別 | UNIQUE 約束 | |
|---|---|---|---|---|
| IBM DB2 | 空比非空值高 | 支援所有標準方式 | 不支援 | 依循非選項的部分的 UNIQUE 約束。並未實作選擇性的”容許空的功能” |
| Microsoft Access | 空比非空值低 | 不支援任何標準方式 | 提供不可為空的 Yes/No 型別 | 依循標準 |
| Microsoft SQL Server | 空比非空值低 | 支援 ROW_NUMBER() 與游標標準方式 | 不支援。可用BIT型別(值可為0、1，或NULL)替代 | 提供”容許空”功能，但最多只能有一個空值(因此違反標準的第二個特質) |
| MySQL | 空比非空值低 | 提供 LIMIT 運算子作為替代方法以及游標標準方式 | 提供不相同的(是TINYINT(1)型別的別名之一) | 依循標準，包括選擇性的”容許空”功能 |
| Oracle | 空比非空值高 | 支援 ROW_NUMBER() 與游標標準方式以及 ROWNUM 欄 | 不支援 | 提供”容許空”功能(註一) |
| PostgreSQL | 空比非空值高 | 支援所有標準方式 | 遵守標準。接受NULL作為Boolean實字；不接受UNKNOWN作為Boolean實字 | 依循標準，包括選擇性的”容許空”功能 |
- 註一 : 若 UNIQUE 約束加諸於單一欄，該欄可帶有任意數量的空(同標準的第二個特質)。但若 UNIQUE 約束指定給多個欄，Oracle 會將兩個帶有至少一個NULL欄與其餘欄非空且相同的列視為違反約束。
17 知道何時使用索引中的計算結果
- 不要濫用索引
- 分析資料庫的預期使用以確保過濾索引只在合理的地方使用
Reference
Effective SQL Ch1