MySQL 時間範圍查詢優化：兩種高效實現方法對比

場景描述

統計每日訪問量、按月匯總銷售額、區間篩選日誌等，核心需求：高效查詢時間段內的數據。

方法一：傳統 DATETIME 範圍查詢

-- 查詢 2010-03-01 當天數據
SELECT COUNT(*) FROM access_log
WHERE dt >= '2010-03-01 00:00:00'
  AND dt <  '2010-03-02 00:00:00';

-- BETWEEN 寫法（含邊界，需注意精度）
SELECT * FROM orders
WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59';

優點

• 語義清晰，可讀性強
• 原生支援 DATE(), YEAR(), MONTH() 等時間函數
• 索引友好：WHERE dt >= '...' AND dt < '...' 可走範圍索引

缺點

• 字符串比較較整數慢
• 大表範圍掃描 I/O 大

方法二：Unix Timestamp 整數查詢（高性能）

核心函數

UNIX_TIMESTAMP(datetime)  -- 轉整數秒數
FROM_UNIXTIME(unixtime)   -- 轉回 datetime

示例

-- 轉換演示
SELECT UNIX_TIMESTAMP('2010-03-01 00:00:00');  -- 1267372800
SELECT FROM_UNIXTIME(1267372800);              -- '2010-03-01 00:00:00'

-- 查詢：先轉換邊界值，再整數比較
SELECT COUNT(*) FROM access_log
WHERE dt >= UNIX_TIMESTAMP('2010-03-01 00:00:00')
  AND dt <  UNIX_TIMESTAMP('2010-03-02 00:00:00');

-- 應用層預轉換（推薦）
-- PHP: $start = strtotime('2010-03-01');
-- Java: Instant.parse("2010-03-01T00:00:00Z").getEpochSecond()

優點

• 整數比較極快，CPU 友好
• 索引體積更小（INT 4 字節 vs DATETIME 8 字節）
• 分區鍵、分表鍵常用整數時間戳

缺點

• 可讀性差，需應用層/視圖轉換
• 時區處理複雜（TIMESTAMP 存 UTC，顯示按會話時區）
• 2038 年問題（32 位有符號整數上限），MySQL 5.6.4+ 內部用 64 位已解決
• 失去原生時間函數便利性

進階優化策略

1. 分區表按時間分區

CREATE TABLE access_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dt INT UNSIGNED NOT NULL COMMENT 'Unix timestamp',
  ...
  PRIMARY KEY (id, dt)
) PARTITION BY RANGE (dt) (
  PARTITION p202301 VALUES LESS THAN (1672531200),
  PARTITION p202302 VALUES LESS THAN (1675209600),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

查詢自動分區裁剪，僅掃描相關分區。

2. 時間列拆分（年/月/日三列）

ALTER TABLE logs ADD year SMALLINT, ADD month TINYINT, ADD day TINYINT;
-- 生成列 (MySQL 5.7+)
ALTER TABLE logs ADD year SMALLINT AS (YEAR(dt)) STORED,
                 ADD month TINYINT AS (MONTH(dt)) STORED,
                 ADD day TINYINT AS (DAY(dt)) STORED;

-- 查詢特定月份
SELECT * FROM logs WHERE year=2023 AND month=3;

配合組合索引 INDEX(year, month, day)，等值查詢極快。

3. 覆蓋索引避免回表

-- 只查 id, dt
CREATE INDEX idx_dt_id ON access_log (dt, id);
SELECT id FROM access_log WHERE dt BETWEEN ... AND ...;

最佳實踐建議

避坑指南

• 不要對時間列用函數：WHERE DATE(dt) = '2023-01-01' → 索引失效，改用範圍
• BETWEEN 邊界陷阱：BETWEEN '2023-01-01' AND '2023-01-31' 漏掉 31 日非 00:00:00 的數據，用 dt >= '2023-01-01' AND dt < '2023-02-01'
• 時區一致性：服務端 time_zone、連接會話 SET time_zone、應用層時區統一用 UTC 存儲