# 資料庫設計原則
1. 欄位的原子性
1. 保證每欄位的原子性，不可分解
2. 意思表達要清楚，不能含糊
3. 高度概括欄位的含義，能用最少的欄位表達清楚就使用最少欄位
4. 能用一個欄位表達清楚的絕不使用第二個欄位，可以用兩個欄位表達清楚的絕不使用一個
2. PK設計
1. PK不要與業務邏輯有所關聯，最好是毫無意義的一串獨立不重複的數字(流水號)
2. 常見的例如UUID或者將PK設定為mysql auto increment 或 oracle sequence

3. 欄位使用次數
1. 對於頻繁修改的欄位，例如狀態類欄位，使用用獨立的數字或者單個字母去表示，不用使用中文字或者英文單字
5. 欄位長度與型態
1. 文字資料欄位若長度固定，如：身分證字號，就不要用 varchar 或 nvarchar，應該用 char 或 nchar
2. 設計欄位時，若其值可有可無，最好也給一個預設值，並設成「不允許 NULL」(一般欄位預設為「允許 NULL」)。在 SQL Server 在存放和查詢有 NULL 的資料表時，會花費額外的運算動作，造成 table scan
3. 欄位的長度儘量小，能用 small integer 就不要用 integer。例如員工資料表，若能用員工編號當主鍵，就不要用身分證字號(當然是能夠預期該表格的資料量大小為前提)
6. 動靜分離
1. 靜態表格和動態表格的分離，在命名上可以直接做區分
2. 靜態表：儲存著一些固定不變的資源，比如城市/地區名/國家
3. 動態表：一些頻繁修改的表格
7. 使用代號代替實際單字
1. 使用數字或者字母去代替實際的名字
2. 例如把STATUS_NAME 變成 STATUS_CODE，讓名字針對不同客戶可以有彈性變化
3. 代號一般是不會變化的.另一方面，儲存的字元較少，也能減少資料庫的壓力
8. 表格基本欄位
1. CREATE_TIMESTAMP 資料建立時間
2. UPDATE_TIMESTAMP 資料異動時間
3. CREATE_ID 資料建立人員
4. UPDATE_ID 資料異動人員
5. 若是有業務邏輯上的需要另外記錄在其他欄位，例如：表單送出時間、表單簽核時間，不要直接使用 UPDATE_TIMESTAMP 資料異動時間來判斷
9. 正確處理多對多的關係
1. 若兩個表格之間存在多對多的關係，則應消除這種關係
2. 消除的辦法是，在兩者之間增加第三個關聯表格(relate)
3. 原來一個多對多的關係，變為兩個一對多的關係

# 資料庫使用原則
1. 盡量避免在 where 子句中對欄位使用函數
1. 或是可以考慮使用 function based index 針對整個函式做索引
2. 在欄位使用函數，也等於對欄位做運算或串接的動作，無法有效地使用 index
3. 在 where 條件當中使用 函數會逐筆使用函式(table scan)，例如資料表內若有 10 萬筆資料，則在查詢時就需要呼叫函數 10 萬次
4. 該問題容易被忽略，在系統開發初期可能感覺不出差異，但當系統上線且資料持續累積後，這些語法細節所造成的效能問題就會逐步浮現
2. where 條件中的 and 跟 or
1. 在 and 運算中，「只要有一個」條件有用到索引，即可大幅提升查詢速度(會先過濾掉第一個條件、再比對第二個條件)
2. 在 or 運算中，則要「所有的」條件都有可用的索引，才能使用索引來提升查詢速度。因此 or 運算子的使用必須特別小心。
3. 改善 or 運算，可以使用 union或union all(但是是特別情況下)
MySQL can use an index on company for this query just fine. There's no need to do any UNION.
The more tricky case is where you have an OR condition that involves two different columns.
select username from users where company = 'bbc' or city = 'London';

select username from users where company = 'bbc'
union
select username from users where city = 'London'

---
Suppose there's an index on company and a separate index on city. Given that MySQL usually uses only one index per table in a given query, which index should it use? If it uses the index on company, it would still have to do a table-scan to find rows where city is London. If it uses the index on city, it would have to do a table-scan for rows where company is bbc.
The UNION solution is for this type of case.
select username from users where company = 'bbc' union
---
select username from users where city = 'London';
Now each sub-query can use the index for its search, and the results of the subquery are combined by the UNION.
---
3. join 跟 子查詢的使用
1. 子查詢關聯有關連到外部的條件的話，效率會較差(外層查詢的「每一次」查詢動作都需要引用內層查詢的資料，或內層查詢的「每一次」查詢動作都需要參考外層查詢的資料)
2. join 過多時可以靠慮合理的增加冗餘欄位
3. [每種sql語言都不盡相同](https://www.ptt.cc/bbs/Database/M.1470902555.A.0D6.html)
4. 盡量少用IN
1. 基本上所有的IN操作符都可以用EXISTS代替。
2. 不用NOT IN操作符，可以用NOT EXISTS或者外連接+替代。
3. 在執行IN查詢時，首先執行子查詢，將查詢結果放入臨時表再執行主查詢
4. EXIST則是首先檢查主查詢，再運行子查詢直到找到第一個匹配項。NOT EXISTS比NOT IN效率稍高
5. 不用“<>”或者“!=”操作
1. 對不等於操作符的處理會造成全表掃瞄，可以用“<” or “>”代替
2. Where子句中出現IS NULL或者IS NOT NULL時，會停止使用索引而執行table scan
3. 在設計表格時，對索引列設置為NOT NULL，提供預設值，這樣就可以用其他操作來取代判斷NULL的操作

# 不確定是否只有對 oracle 有效
1. Oracle從下到上處理Where子句中多個查詢條件，所以表連接語句應寫在其他Where條件前，可以過濾掉最大數量記錄的條件必須寫在Where子句的末尾
2. Oracle從右到左處理From子句中的表名，所以在From子句中包含多個表的情況下，將記錄最少的表放在最后。（只在採用RBO優化時有效，下文詳述）
3. Order By語句中的非索引列會降低性能，可以通過添加索引的方式處理。嚴格控制在Order By語句中使用表達式
4. 不同區域出現的相同的Sql語句，要保證查詢字符完全相同，以利用SGA共享池，防止相同的Sql語句被多次分析。
5. 多利用內部函數提高Sql效率。
6. 當在Sql語句中連接多個表時，使用表的別名，並將之作為每列的前置碼。這樣可以減少解析時間
7. 可能引起全表掃瞄的操作：

- 在索引列上使用NOT或者“<>”
- 對索引列使用函數或者計算
- NOT IN操作
- LIKE 查詢 % 位於查詢字符串的第一個字符
- IS NULL或者IS NOT NULL
- 多欄位索引，但它的第一個並沒有被Where使用
- 舉例 INDEX name (last_name,first_name)
- SELECT * FROM test WHERE last_name='Widenius';
- SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
- SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');
- SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N';

- 以下沒有使用到多欄位索引
- SELECT * FROM test WHERE first_name='Michael';
- SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';