LDKTC/Agent-P-FullStack

MySQL

database-schema-dev 與 backend-dev 的 MySQL 專精知識層： 這些代理不依賴特定引擎，會匹配專案既有的遷移工具/ORM——此技能是它們在引擎為 MySQL 或其相容分支 MariaDB 時實際運用的 MySQL 知識。 若 MariaDB 行為不同，會特別標註——請勿假設高階功能完全相容。

步驟 1 — 確認引擎確實為 MySQL/MariaDB

檢查 package.json 中是否有 mysql2/mysql、ORM 方言/提供者（Prisma 的 provider = "mysql"、Sequelize 的 dialect: 'mysql'、TypeORM 的 type: 'mysql'/'mariadb'）、docker-compose.yml 中是否使用 mysql/mariadb 映像檔，或是否存在 mysql:// 連線字串。若皆不符合，請停止——改用 database-schema-dev/backend-dev 的引擎無關預設值，或改用對應的引擎專用技能。

步驟 2 — 字元集、定序、儲存引擎

• 預設字元集為 utf8mb4，而非 utf8——MySQL 的 utf8 是最大 3 位元組的別名，無法容納多數表情符號/星際字元——請搭配明確定序（MySQL 8+ 使用 utf8mb4_0900_ai_ci；MariaDB 沒有 0900 定序，若需相容兩者請使用 utf8mb4_unicode_ci）。
• 除非有特定理由使用 MyISAM（無交易、無列鎖、無外鍵），否則使用 InnoDB——自 MySQL 5.5/MariaDB 10.x 起即為預設，因此通常只需確認無其他覆寫。
• _ci 定序表示 '[email protected]' 會匹配 '[email protected]'——對需區分大小寫的內容（令牌、機密）請使用 _bin/_cs（或雜湊欄位）。

步驟 3 — 正確的資料型別

• 金錢/精確小數：使用 DECIMAL(p,s)，絕不使用 FLOAT/DOUBLE——二進位浮點數無法精確表示多數十進位分數。
• 根據實際範圍選擇適當大小的整數（TINYINT/SMALLINT/INT/BIGINT），而非習慣性地全部使用 BIGINT——較小的型別意味著較小的索引。對 ID 欄位使用 UNSIGNED 可免費增加範圍，因為 ID 永不為負。
• DATETIME 用於無時區轉換需求的掛鐘時間；TIMESTAMP（UTC 標準化，依連線時區自動轉換，2038 年範圍限制）僅用於實際時間點——不要將其作為預設。
• VARCHAR(n) 的大小應符合實際約束，而非反射性地使用 VARCHAR(255)；TEXT/BLOB 用於真正無上限的內容——兩者皆不接受預設值或完整（非前綴）索引。
• ENUM 僅用於固定且極少變動的集合——值按定義順序而非字母順序排序（在 ORDER BY 中可能造成意外），且在 MySQL 8 上追加值僅為中繼資料操作，但在 MariaDB/舊版 MySQL 上可能重寫整個表格。當集合擴大或需要中繼資料時，查詢表格的擴充性更好。
• 原生 JSON（MySQL 5.7.8+/MariaDB 10.2+——MariaDB 的 JSON 實際上是 LONGTEXT 加上 CHECK，非二進位型別，因此不具備 MySQL 的二進位儲存/部分更新優勢）僅用於真正無綱要的資料——任何需要定期篩選或關聯的內容應屬於真實欄位。

步驟 4 — 索引設計

• 複合索引：先放等值篩選的欄位，再放範圍/排序欄位——(status, created_at) 可服務 WHERE status = ? ORDER BY created_at；反向順序則不行。
• 最左前綴規則：(a, b, c) 索引同時服務 (a) 和 (a, b) 查詢——不要在其旁新增冗餘的 (a) 索引。
• 覆蓋索引（包含所有選取欄位）讓 MySQL 僅從索引回應（EXPLAIN 顯示 Using index）——對高頻且窄範圍的查詢值得使用，但不要預設用於所有查詢。
• 長 VARCHAR/TEXT 使用前綴索引（col(20)）——透過 COUNT(DISTINCT LEFT(col, n)) 與 COUNT(DISTINCT col) 的比較來決定 n 的大小。
• 深度分頁：LIMIT 20 OFFSET 100000 每頁仍掃描並丟棄 100,000 行。鍵集分頁（WHERE (created_at, id) < (:last_created, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20）重複使用相同複合索引並直接定位到游標——用於無限滾動或深度 API 分頁，取代 OFFSET。
• 每個索引都會增加寫入時間和磁碟空間——只為真實查詢模式（database-schema-dev 步驟 1）添加索引，而非投機地每個欄位都加。

步驟 5 — 基於 EXPLAIN 的查詢調優

• 在信任查詢速度快之前，先執行 EXPLAIN（或 MySQL 8.0.18+/MariaDB 10.4+ 的 EXPLAIN ANALYZE）——不要僅從查詢形狀判斷。
• type: ALL 表示全表掃描；ref/range/const 表示實際使用了索引——超過小型表規模的查詢不應顯示 ALL。
• Extra: Using filesort/Using temporary 表示 MySQL 在索引外排序/實體化——通常可透過匹配 ORDER BY/GROUP BY 的索引解決。
• 對索引欄位使用包裝函式或隱含轉型（WHERE YEAR(created_at) = ...、將字串欄位與數字比較）會使索引失效——改用裸欄位範圍（created_at >= '2024-01-01' AND created_at < '2025-01-01'）。
• 對寬表使用 SELECT * 會破壞覆蓋索引並拉取未要求的 TEXT/BLOB 欄位——只選取實際使用的欄位。

步驟 6 — 交易與鎖定

• InnoDB 鎖定行而非表，但預設隔離等級 REPEATABLE READ 在 UPDATE/DELETE/鎖定 SELECT 的範圍掃描中還會使用間隙鎖——範圍條件可能鎖定比匹配行更多的內容。若不需要可重複讀取，READ COMMITTED 可避免間隙鎖。
• 該快照僅涵蓋普通 SELECT——同一交易中的鎖定讀取（SELECT ... FOR UPDATE、UPDATE、DELETE）會讀取最新的已提交資料，因此可能看到快照未顯示的行。不要假設快照隔離也能保護鎖定讀取。
• 保持交易簡短——開啟的交易會持有其鎖定，且在 REPEATABLE READ 下還會阻止 InnoDB 清除舊版本行。
• 使用 SELECT ... FOR UPDATE 鎖定在單一交易中先讀後寫的行（例如餘額檢查後扣款）；在整個程式碼庫中以一致的行順序獲取鎖定，以避免死結。
• 死結（ERROR 1213，SQLSTATE 40001）在高競爭環境下是可預期的——InnoDB 會自動回滾其中一方；應捕獲該程式碼並重試，而非透過延長交易來「修復」。SHOW ENGINE INNODB STATUS → LATEST DETECTED DEADLOCK 顯示雙方持有的鎖定與等待的資源。
• 預設每個連線都啟用自動提交——多語句原子性需要明確的交易（或 ORM 的包裝器），而非僅在程式碼中相鄰。

步驟 7 — MySQL 特有陷阱與 MariaDB 差異

• sql_mode 應包含 STRICT_TRANS_TABLES（自 MySQL 5.7/MariaDB 10.2 起為預設）——若無此設定，超出範圍/型別錯誤的值會被靜默截斷而非報錯；請確認未被放寬。
• AUTO_INCREMENT 值不保證連續（回滾仍會消耗一個值）——永遠不要依賴 ID 的連續性。
• 連線 time_zone 影響 TIMESTAMP 轉換但不影響 DATETIME——混合了不同時區設定的連線池會導致真正的「時間錯誤」；請明確固定時區。
• MariaDB 有自己的擴展功能，MySQL 中無對應項（CREATE SEQUENCE、寫入時 RETURNING）——若也需支援 MySQL，請勿使用；視窗函數/CTE 需要 MySQL 8.0+/MariaDB 10.2+，請先確認固定版本。
• MariaDB 的最佳化器和 EXPLAIN 輸出與 MySQL 不同——請在實際使用的引擎上重新執行 EXPLAIN，而非信任在另一個引擎上調優的計畫。

步驟 8 — 完成前自我檢查

確認每個新增/修改的欄位都有明確的型別，字元集回退為 utf8mb4。確認每個新索引都有真實的查詢模式支撐，且 EXPLAIN 輸出顯示其被使用，任何深度/無限列表端點使用鍵集分頁而非 OFFSET。確認多語句寫入已包裝在明確交易中，且鎖定讀取的隔離行為是經過深思熟慮而非假設的。若 compose 檔案/CI 固定使用 MariaDB，請確認未添加任何僅限 MySQL 8 的功能。

不應做的事項

• 不要使用 utf8 字元集或 FLOAT/DOUBLE 處理金錢——utf8mb4 和 DECIMAL 是正確的預設值。
• 不要添加沒有查詢模式支撐的索引，或在未檢查 EXPLAIN 前就稱查詢「快」。
• 不要在可能深度分頁或無限執行的列表上使用 OFFSET 分頁——請使用鍵集分頁。
• 不要讓交易保持開啟時間超過其保護的工作所需，特別是在 REPEATABLE READ 的間隙鎖下。
• 不要依賴 AUTO_INCREMENT 的連續性或隱含型別轉換來保證正確性。
• 不要假設 MariaDB 目標具有僅限 MySQL 8 的功能（原生 JSON、utf8mb4_0900_ai_ci），除非檢查過固定版本。
• 不要自行編寫與引擎無關的遷移/存取層程式碼——那是 database-schema-dev/backend-dev 的工作；此技能是它們在此基礎上應用的 MySQL 知識。