MySQL 索引設計實戰：從業務分析到優化落地

案例背景：B2C 訂單表 goods_order

CREATE TABLE `goods_order` (
  `order_id`      INT UNSIGNED NOT NULL COMMENT '訂單號',
  `goods_id`      INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品款號',
  `order_type`    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '訂單類型',
  `order_status`  TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '訂單狀態',
  `color_id`      SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '顏色ID',
  `size_id`       SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '尺寸ID',
  `goods_number`  MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '數量',
  `depot_id`      INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '倉庫ID',
  `packet_id`     INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '儲位碼',
  `gmt_create`    TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '創建時間',
  `gmt_modify`    TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新時間',
  PRIMARY KEY (`order_id`, `goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

主鍵順序決策

PRIMARY KEY (order_id, goods_id) 而非反序，因為：

• order_id 區分度極高（近似唯一），篩選率接近 1。
• 業務 SQL 多以 order_id 為單獨查詢條件，符合最左前綴原則。

業務 SQL 分析與索引規劃

最終索引結構

-- 主鍵已存在 PRIMARY KEY (order_id, goods_id)

-- 覆蓋 goods_id 相關高頻查詢，注意字段順序：
ALTER TABLE goods_order
  ADD INDEX idx_goodsID_orderType_orderStatus_gmtmodify (goods_id, order_type, order_status, gmt_modify);

-- 覆蓋倉庫/儲位查詢，犧牲單一 depot_id 查詢性能，減少一個索引維護開銷：
ALTER TABLE goods_order
  ADD INDEX idx_depotID_packetID_gmtmodify (depot_id, packet_id, gmt_modify);

設計原則總結

1. 識別業務數據流、操作類型、頻率、優先級。
2. 分析條件字段篩選率（Cardinality），高區分度靠左。
3. 聯合索引遵循「等值查詢在左、範圍/排序在右」。
4. 權衡讀寫比例：寫多表少建索引，讀多適當增加；避免過度索引。
5. 定期用 EXPLAIN 驗證，根據慢查詢日誌持續優化。