MySQL 資料表與 SQL 設計
適用範圍
- 當任務或專案確定 MySQL 為目標資料庫，且涉及資料建模、資料表設計、SQL、索引、交易或並行控制時適用；無需明確呼叫。
- 可獨立於 SDLC 工作流程使用。請勿為通用資料庫工作選用 MySQL，或套用於僅涉及其他資料庫的任務。
使用方式
- 使用官方 MySQL 8.4 語義作為驗證基準，但不限制實際部署的版本。若差異重要，請驗證實際目標版本中的功能支援、約束強制執行及棄用情況。
- 建立與任務相關的業務意義、完整性需求、主要存取模式、資料規模與成長、並行及一致性需求。使用現有設計與工作負載證據（如有）；若關鍵資訊缺失，請說明假設及其影響。
- 將交易與並行規則套用於本機 InnoDB 交易。明確將交易內查詢、
CHECK約束、外部鍵約束及預存程序的禁止標示為專案慣例，而非 MySQL 限制。 - 假設每條記錄每秒最多修改一次。此假設不保證
updated_at是不可重複使用的版本識別碼；在將其用於並行控制前，請先套用時間戳檢查中的條件。 - 僅套用與任務相關的規則。需要澄清規則時，請閱讀 MySQL 設計範例中的對應主題；請勿預設載入所有範例。
資料建模
實體、關係與欄位歸屬
-
資料列粒度：定義資料列所代表的業務物件、關係或事件；在同一資料表內保持粒度一致。請參閱範例。
-
關係與唯一性：在兩端定義最小與最大基數。將一對多參考儲存在多端，使用唯一參考將一對一關係限制為最多一個匹配，並使用連結資料表處理多對多關係。對於相同物件的重複關聯，請定義每個關聯的業務意義、身份及唯一性範圍。請參閱範例。
-
欄位歸屬：將欄位置於決定其值的物件、關係或事件中；避免重複目前事實。保留事件時間值的歷史快照，不受目前值更新影響。請參閱範例。
-
參照完整性：定義建立或變更參考時，被參考目標必須存在且滿足哪些狀態。定義當目標被刪除或變為無效時，如何處理現有參考。透過鍵值與約束及並行控制下的業務寫入流程強制執行這些需求。請參閱範例。
資料表設計
名稱與註解
-
使用小寫字母、數字和底線命名資料庫、資料表和欄位；不要以數字開頭、使用保留字或不明確的縮寫。
-
根據欄位或用途命名索引。
uniq_和idx_可標識唯一和普通索引；主索引名稱為PRIMARY。 -
當意義、狀態值或約束變更時，更新欄位註解及相關定義。
資料類型
-
用於參考或比較相同值的欄位，應在資料類型和符號性上相容；盡可能使用一致的定義。
-
以指定精度和小數位數的
DECIMAL表示確切貨幣金額，或使用定義最小單位的整數；定義貨幣和四捨五入規則。在儲存、參數和計算中保留確切表示；轉換前不要在浮點數中計算。 -
不要在
FLOAT、DOUBLE或DECIMAL上設定已棄用的UNSIGNED。 -
使用
is_xxx和TINYINT UNSIGNED表示布林值，並透過應用程式驗證將值限制為 0 或 1。 -
使用
CHAR時，請驗證其尾部空格處理是否與欄位的業務意義相符。 -
TIMESTAMP在連線時區與 UTC 之間轉換；DATETIME不轉換。需要時，請單獨儲存原始時區名稱或偏移量。 -
使用
created_at表示建立時間，updated_at表示最後修改時間。可變的業務記錄需要兩者；唯追加記錄僅需建立時間。根據稽核需求，將它們加入連結或其他資料表。 -
對稽核時間戳使用
DATETIME(3)、毫秒精確度及台北時間，連線時區設為+08:00。 -
將稽核時間戳設為
NOT NULL DEFAULT CURRENT_TIMESTAMP(3)，並為updated_at加上ON UPDATE CURRENT_TIMESTAMP(3)。讓資料庫維護它們；不要應用程式重複指定。
鍵值與約束
-
為 InnoDB 資料表明確定義穩定、簡短的主鍵。次要索引記錄包含主鍵欄位，因此寬主鍵會增加次要索引的成本。
-
將必要欄位標記為
NOT NULL。 -
不要使用
CHECK；在每個應用程式寫入入口點驗證範圍、列舉和欄位組合，拒絕無效寫入。請參閱範例。 -
使用唯一索引強制執行業務唯一性，而非先查詢再插入。唯一索引允許多個
NULL值。 -
在適用的業務操作和租戶範圍內，為冪等識別碼強制執行唯一性，所有參與欄位均為
NOT NULL。 -
不要使用外部鍵約束或串聯；透過業務寫入流程維護參照完整性。
字元集與定序
-
文字使用
utf8mb4。 -
用於參考或比較相同值的文字欄位，應在字元集和定序上相容；盡可能使用一致的定義。
-
在變更定序前，檢查等值變更以及可能違反唯一性的新重複值。
SQL 程式設計
- 不要使用預存程序。應用程式程式碼控制分支、提交和回滾。
查詢
-
在穩定的業務查詢中明確選取必要欄位；避免
SELECT *。 -
當分頁或單列選取需要確定性順序時，使用能唯一排序資料列的排序鍵組合。
-
在游標中包含所有排序鍵。並行排序鍵變更可能導致跨頁遺漏/重複；游標分頁不保證一致的跨頁快照。
資料修改
-
不要使用
INSERT IGNORE或UPDATE IGNORE隱藏應失敗的錯誤。 -
防止
UPDATE/DELETE因空參數或缺少動態謂詞而退化為全資料表操作。LIMIT不能取代業務範圍條件。 -
在以上稽核時間戳定義下，
updated_at在資料列內容變更時自動變更；它不記錄每個更新陳述式的執行時間。 -
不要以
REPLACE INTO取代普通更新：在主鍵/唯一鍵衝突時，其取代語義可能將未指定的欄位重設為預設值，並觸發刪除/插入行為。
表達式與函數
-
比對比較類型；不要讓隱式轉換決定關鍵的業務比較。
-
在長度檢查中區分位元組、字元和使用者可見的字素。
LENGTH計算位元組，CHAR_LENGTH計算字元，而非可見字素。 -
如果
NOT IN使用的集合包含NULL，則未匹配任何成員的值可能傳回UNKNOWN；不要將其解釋為確認的非歸屬。 -
COUNT(DISTINCT col)排除NULL；其多欄位形式僅在所有參與欄位非NULL時計算不同的組合。 -
SUM對空或全NULL輸入傳回NULL。如果GROUP BY未形成任何群組，則不傳回任何資料列。僅當業務規則將空聚合定義為零時，才將其替換為零。 -
偏好半開時間區間：包含開始，排除結束。在業務時區中計算邊界，使用與儲存值相同的時間基準。
交易與一致性
交易管理
-
交易內的陳述式：在明確交易外部執行查詢、業務驗證和寫入準備。在交易內部，僅允許必要的資料修改和交易控制。禁止在寫入中包含普通查詢、鎖定查詢和查詢元件，包括子查詢。請參閱範例。
-
交易連線：必須一起成功執行的寫入應在同一連線的同一交易內執行。連線切換、非同步執行和傳播設定必須保留該邊界。請參閱範例。
-
隱含提交：不要在業務寫入之間使用
START TRANSACTION或將autocommit從 0 切換為 1 來模擬巢狀交易；兩者都會隱含提交。將 DDL（如ALTER TABLE和TRUNCATE TABLE）保留在業務寫入交易之外。請參閱範例。 -
失敗處理：當必要寫入錯誤或業務條件未滿足時，停止後續寫入並回滾整個交易。不要假設失敗的陳述式會復原先前的寫入。在死結或鎖定逾時後，確保完全回滾，僅在安全時重試整個交易，並設定有限的重試次數。對於不確定的提交，請先使用冪等識別碼驗證。請參閱範例。
-
外部操作：本機交易不會自動提交/回滾外部呼叫或訊息傳送。請參閱範例。
並行控制
-
業務條件更新：偏好寫入時機的業務欄位謂詞。當從交易外部讀取的資料計算新值時，包含檢查相關原始值或有效版本識別碼的謂詞，以避免覆蓋並行變更。當一個條件陳述式足夠時，不要新增明確交易。請參閱範例。
-
時間戳檢查：如果缺乏合適的業務欄位，僅在每次相關修改都會變更
updated_at，且舊值在驗證期間無法重複使用時，才匹配原始的updated_at。在讀取、寫入、序列化和繫結中保留毫秒。否則，使用不可重複使用的版本識別碼或其他並行機制。請參閱範例。 -
參照完整性：使用條件更新、唯一性和必要的交易來協調參考建立/變更與目標刪除/無效化。請參閱範例。
-
更新結果：判斷驅動程式報告的是匹配還是更改的資料列，然後評估必要寫入是否成功。未滿足的謂詞不是成功；僅零更改資料列不能證明謂詞未滿足。請參閱範例。
-
寫入順序：競爭的交易應使用一致的資料表/記錄順序。SQL 文字順序不決定實際鎖定順序。請參閱範例。
資料庫安全性
-
將應用程式資料作為參數繫結，而非串接到 SQL 中。
-
透過程式碼白名單將動態資料表/欄位名稱和排序方向對應到 SQL 結構中；值預留位置無法取代它們。
效能最佳化
- 請求往返次數：減少每次業務操作的資料庫請求次數，同時保持正確性和整體效能。
索引最佳化
-
索引重複使用與變更：在主要查詢中重複使用索引。當現有路徑滿足效能需求時，不要僅為新的 SQL/謂詞新增索引。在合併或取代前，檢查其他查詢和唯一約束。請參閱範例。
-
低基數欄位：不要僅因相異值少就拒絕索引。考慮目標分佈、組合篩選、排序、覆蓋效益、資料表查找和寫入成本。請參閱範例。
-
前綴索引：從實際資料中選擇字串前綴長度。唯一前綴索引可能拒絕共用前綴的相異完整值，且無法取代完整值的唯一性。請參閱範例。
-
複合索引：平衡篩選、左前綴重複使用、排序和分組。相等欄位通常排在範圍欄位之前；不要普遍按選擇性排序。請參閱範例。
-
排序成本：
filesort單獨出現不代表索引不佳。比較新增索引的排序、掃描和維護成本。請參閱範例。 -
索引欄位與表達式：對每日篩選偏好直接的時間範圍謂詞。如果需要表達式，請評估匹配的函數索引或產生欄位索引。請參閱範例。
-
前置萬用字元：普通 B-tree 索引通常無法將前置萬用字元搜尋轉換為有效的範圍掃描。全文或專用搜尋必須保留原始匹配語義。請參閱範例。
查詢最佳化
-
批次查詢：將可合併的逐列查詢替換為聯結或子查詢。
-
查詢重寫：簡化複雜表達式，避免不必要的巢狀結構。
-
使用 EXPLAIN：使用
EXPLAIN分析查詢執行計畫，識別潛在的效能瓶頸。 -
避免全表掃描：確保查詢使用適當的索引，避免全表掃描。
-
限制結果集：使用
LIMIT限制結果集大小，減少資料傳輸。 -
快取策略：考慮使用應用層快取減少資料庫負載。