MySQL 建表與查詢優化實戰筆記：冗余時間戳、INSERT IGNORE/REPLACE、唯一索引三板斧

表結構設計決定系統上限。本文分享三個高頻實戰技巧：雙時間戳冗余支撐快照體系、INSERT IGNORE / REPLACE INTO 簡化寫入邏輯、業務唯一索引 加速查詢與保障數據質量。

技巧一：雙時間戳冗余 —— add_time + update_time

`add_time` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '創建時間戳',
`update_time` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '最後更新時間戳',

價值：

• 快照腳本按 update_time > 上次同步時間 增量抽取，避免全表掃描
• 數據修復時定位「最近變更窗口」，精準重跑
• 審計追蹤、冷熱分離判斷依據

自動維護：ON UPDATE CURRENT_TIMESTAMP（5.6.5+支援）或應用層/觸發器維護。

技巧二：INSERT IGNORE 與 REPLACE INTO —— 簡化寫入邏輯

實戰對比：腳本循環寫入

-- 方案A：應用層 SELECT 判斷再 INSERT/UPDATE（慢、併發有竄改）
-- 方案B：REPLACE INTO（簡單但副作用大）
-- 方案C：INSERT IGNORE INTO（僅去重不更新）
-- 方案D：INSERT ... ON DUPLICATE KEY UPDATE cnt = cnt + 1（推薦）

技巧三：業務唯一索引 —— 查詢加速與數據質量雙贏

• 主鍵是技術主鍵（自增ID），**業務唯一性必須建唯一索引**
• 常見：uk_mobile (mobile)、uk_email (email)、uk_order_no (order_no)、uk_user_device (user_id, device_id)
• 優勢：
  1. WHERE mobile = ? 走唯一索引，極速定位（ref 類型，rows=1）
  2. 天然防重，數據庫層面保證業務不重複
  3. 支援 INSERT ... ON DUPLICATE KEY UPDATE 高效寫入

綜合實戰：用戶設備綁定表

CREATE TABLE user_device (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '技術主鍵',
  user_id BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
  device_id VARCHAR(64) NOT NULL COMMENT '設備指紋',
  bind_type TINYINT NOT NULL DEFAULT 1 COMMENT '綁定類型',
  status TINYINT NOT NULL DEFAULT 1 COMMENT '狀態',
  add_time INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '創建時間',
  update_time INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新時間',
  PRIMARY KEY (id),
  UNIQUE KEY uk_user_device (user_id, device_id),  -- 業務唯一
  KEY idx_user_status (user_id, status),           -- 常用查詢
  KEY idx_update_time (update_time)                -- 增量同步
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶設備綁定';

避坑提醒

• INSERT IGNORE 會吞噬**所有**錯誤（外鍵、NOT NULL、分區等），僅在「確信只有唯一鍵衝突」時使用
• REPLACE INTO 觸發 ON DELETE CASCADE 可能誤刪關聯數據
• 唯一索引欄位**不可為 NULL**（或僅允許單 NULL），否則唯一約束失效
• 冗余時間戳用 INT UNSIGNED 存 Unix 時間戳，跨時區、無年份 2038 問題（64位系統）

「好表結構勝過千行優化代碼」，三板斧打造健壯、高效、可演進的數據層。