MySQL 時間範圍查詢優化：從 BETWEEN 到 UNIX_TIMESTAMP 的索引命中之道

業務場景

訪問日志表 access_log 儲存百萬級記錄，需統計每日 PV：

SELECT COUNT(*) FROM access_log 
WHERE visit_time >= '2023-10-01 00:00:00' 
  AND visit_time < '2023-10-02 00:00:00';

方案對比：字符串比較 vs 整數比較

方法一：傳統 DATETIME 字符串比較

-- 索引定義
ALTER TABLE access_log ADD INDEX idx_visit_time (visit_time);

-- 查詢
SELECT COUNT(*) FROM access_log 
WHERE visit_time >= '2023-10-01 00:00:00' 
  AND visit_time < '2023-10-02 00:00:00';

• 優點：可讀性強、原生類型、支持時區感知
• 缺點：
  • 字符串比較涉及字符集校對規則，CPU 開銷大
  • 索引鍵值長度較長（DATETIME 8 字節 + 校對元數據）
  • 範圍查詢需遍歷 B+ 樹葉子節點，隨機 I/O 多

方法二：UNIX_TIMESTAMP 整數索引（高性能）

-- 1. 添加整數時間戳列（或修改現有列類型）
ALTER TABLE access_log ADD COLUMN visit_ts INT UNSIGNED NOT NULL DEFAULT 0;
-- 回填數據
UPDATE access_log SET visit_ts = UNIX_TIMESTAMP(visit_time);
-- 建立索引
ALTER TABLE access_log ADD INDEX idx_visit_ts (visit_ts);

-- 2. 查詢轉換
SELECT COUNT(*) FROM access_log 
WHERE visit_ts >= UNIX_TIMESTAMP('2023-10-01 00:00:00')
  AND visit_ts < UNIX_TIMESTAMP('2023-10-02 00:00:00');

-- 3. 應用層預計算（推薦，避免函數調用）
-- PHP: $start = strtotime('2023-10-01'); $end = strtotime('2023-10-02');
-- SQL: WHERE visit_ts >= ? AND visit_ts < ?

性能對比實測（百萬級數據，SSD，InnoDB）

MySQL 內置時間轉換函數

進階優化：分區表 + 時間戳

-- RANGE 分區按天/月劃分，實現分區剪枝
CREATE TABLE access_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    visit_ts INT UNSIGNED NOT NULL,
    url VARCHAR(500),
    ...
    PRIMARY KEY (id, visit_ts)  -- 分區鍵必須包含在 PK
) ENGINE=InnoDB
PARTITION BY RANGE (visit_ts) (
    PARTITION p202310 VALUES LESS THAN (1698710400),  -- 2023-11-01
    PARTITION p202311 VALUES LESS THAN (1701388800),  -- 2023-12-01
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查詢自動命中單分區
EXPLAIN PARTITIONS SELECT COUNT(*) FROM access_log 
WHERE visit_ts >= 1696118400 AND visit_ts < 1696204800;
-- partitions: p202310

避坑指南

現代最佳實踐：MySQL 5.6.4+ 原生微秒精度 DATETIME(3)

-- 原生支持毫秒，索引效率接近 INT，且保留時區語義
ALTER TABLE access_log MODIFY visit_time DATETIME(3) NOT NULL;
ALTER TABLE access_log ADD INDEX idx_visit_time (visit_time);

-- 查詢無需轉換
WHERE visit_time >= '2023-10-01 00:00:00.000' 
  AND visit_time < '2023-10-02 00:00:00.000'

• 存儲 8 字節 (5.6.4+ 壓縮格式)，支持 1000-9999 年、微秒精度
• 原生時區轉換 (CONVERT_TZ)、日期運算 (DATE_ADD)、格式化 (DATE_FORMAT)
• **推薦新項目直接使用 DATETIME(3) + 索引**，避免手動維護時間戳列

總結

時間範圍查詢優化核心在於 **「索引列類型最小化、比較運算整數化、避免函數破壞 SARGable」**。

1. **存量系統**：增 INT UNSIGNED 時間戳列 + 索引，應用層預計算參數
2. **增量系統**：直接用 DATETIME(3) + 索引，享受原生類型紅利
3. **超大表**：結合 PARTITION BY RANGE (visit_ts) 實現物理分區剪枝
4. **統一時區**：全鏈路 UTC，消除時區轉換 Bug

整數索引在「寫入轉換一次、讀取無數次受益」的場景下，仍是極高性價比的優化手段。