萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 日期比較函數與範圍查詢最佳實踐:避開索引失效陷阱

日期比較是業務查詢高頻場景,寫法不當極易導致**索引失效、全表掃描**。本文系統講解 MySQL 日期比較函數、範圍查詢標準寫法、索引友好模式、常見反模式與修正方案。

核心日期比較函數

函數用途示例
DATEDIFF(date1, date2)天數差 (date1 - date2)DATEDIFF('2023-08-08', '2023-08-01') = 7
TIMESTAMPDIFF(unit, dt1, dt2)指定單位差值TIMESTAMPDIFF(DAY, '2023-01-01', '2023-08-08') = 219
DATE_ADD(date, INTERVAL n UNIT)日期加法DATE_ADD('2023-08-01', INTERVAL 7 DAY) = '2023-08-08'
DATE_SUB(date, INTERVAL n UNIT)日期減法DATE_SUB('2023-08-08', INTERVAL 7 DAY) = '2023-08-01'
DATE(expr)提取日期部分DATE('2023-08-08 14:30:00') = '2023-08-08'
YEAR/MONTH/DAY/HOUR/MINUTE/SECOND提取單一組件MONTH('2023-08-08') = 8
DAYOFWEEK/WEEKDAY星期索引DAYOFWEEK('2023-08-08') = 3 (1=週日)
QUARTER季度QUARTER('2023-08-08') = 3
DAYOFYEAR年間第幾天DAYOFYEAR('2023-08-08') = 220

❌ 索引失效反模式(嚴禁)

反模式原因影響
WHERE DATE(create_time) = '2023-08-08'函數包裹欄位,無法走索引全表掃描
WHERE YEAR(create_time) = 2023 AND MONTH(create_time) = 8函數包裹欄位全表掃描
WHERE create_time BETWEEN '2023-08-01' AND '2023-08-31 23:59:59'DATETIME 精度漏掉毫秒資料遺漏
WHERE create_time LIKE '2023-08%'隱式轉型為字串比較全表掃描 + 錯誤結果

✅ 索引友好標準寫法:半開半閉區間 [start, end)


-- 某一天
WHERE create_time >= '2023-08-08 00:00:00'
  AND create_time <  '2023-08-09 00:00:00'

-- 近 30 天(含今天)
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)

-- 本月
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND create_time <  DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')

-- 本週(週一開始)
WHERE create_time >= DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY)
  AND create_time <  DATE_ADD(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 7 DAY)

-- 本季度
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-01') - INTERVAL (MONTH(NOW())-1)%3 MONTH
  AND create_time <  DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01') - INTERVAL (MONTH(NOW())-1)%3 MONTH, INTERVAL 3 MONTH)

動態參數化查詢(應用層構建)


-- PHP PDO 範例
$start = (new DateTime('2023-08-01'))->format('Y-m-d H:i:s');
$end   = (new DateTime('2023-09-01'))->format('Y-m-d H:i:s');
$sql = "SELECT * FROM orders WHERE create_time >= ? AND create_time < ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$start, $end]);

特殊場景處理

1. 只存 DATE 類型(無時間部分)


-- 可直接用等值或 BETWEEN(含邊界)
WHERE order_date = '2023-08-08'
WHERE order_date BETWEEN '2023-08-01' AND '2023-08-31'

2. 跨時區查詢


-- 統一轉換到 UTC 比較
WHERE CONVERT_TZ(create_time, '+08:00', '+00:00') >= '2023-08-08 00:00:00'
  AND CONVERT_TZ(create_time, '+08:00', '+00:00') <  '2023-08-09 00:00:00'

-- 或應用層統一存儲 UTC,查詢時轉換參數

3. 歷史分區表按日期分區


CREATE TABLE orders (
    id BIGINT,
    create_time DATETIME,
    ...
) PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    ...
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 查詢自動分區裁剪
SELECT * FROM orders WHERE create_time >= '2023-08-01' AND create_time < '2023-09-01';

效能驗證清單

  1. EXPLAIN 確認 type=range/ref、key=目標索引、rows 合理。
  2. Handler_read_next / Handler_read_key 狀態變數觀察掃描行數。
  3. 慢查詢日誌記錄執行時間、掃描行數、鎖等待。
copyright © 萬盛學電腦網 all rights reserved