萬盛學電腦網

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

MySQL 日期時間範圍查詢:高效利用索引的多種寫法與陷阱避坑

場景

表 jz_user 有 create_time INT UNSIGNED 存儲 Unix 時間戳,需查詢「2013-04-01 00:00:00 到 2013-04-30 23:59:59」創建的用戶。

寫法對比與索引利用分析

寫法SQL 示例能否用索引說明
函數包裹列(最差)WHERE DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') BETWEEN '2013-04-01' AND '2013-04-30'❌ 全表掃描列上套函數導致索引失效
隱式轉換WHERE create_time BETWEEN '2013-04-01' AND '2013-04-30'❌ 字符串轉數字,可能全掃類型不匹配觸發隱式 CAST
UNIX_TIMESTAMP 轉換常量(推薦)WHERE create_time BETWEEN UNIX_TIMESTAMP('2013-04-01 00:00:00') AND UNIX_TIMESTAMP('2013-04-30 23:59:59')✅ 索引範圍掃描常量預計算,列無運算,Sargable
直接寫數字(最快)WHERE create_time BETWEEN 1364745600 AND 1367337599✅ 索引範圍掃描無任何函數調用,極致性能
>= 和 < 組合(最佳實踐)WHERE create_time >= 1364745600 AND create_time < 1367424000✅ 索引範圍掃描避免 BETWEEN 邊界歧義,含首尾精確控制

核心原則:Sargable (Search ARGument ABLE)

  • 列不參與運算、函數、隱式轉換,优化器才能使用索引範圍掃描。
  • 常量側做所有轉換:UNIX_TIMESTAMP()、STR_TO_DATE()、DATE() 等均在常量上執行。
  • BETWEEN 包含邊界,時間範圍查詢推薦 >= start AND < end_of_next_day,避免毫秒級數據遺漏/重複。

相關時間函數速查

函數返回類型示例
NOW()DATETIME2024-01-15 14:30:00
CURRENT_DATEDATE2024-01-15
UNIX_TIMESTAMP()INT1705309800 (當前時間戳)
UNIX_TIMESTAMP('2024-01-15')INT1705248000 (指定日期 00:00:00)
FROM_UNIXTIME(ts)DATETIME2024-01-15 14:30:00
TO_DAYS('2024-01-15')INT738532 (從年份 0 起的天數)
DATE_SUB(NOW(), INTERVAL 30 DAY)DATETIME30 天前

實戰範例:近 30 天熱門文章


-- 假設文章表 article(view_count INT, create_time INT)
SELECT * FROM article
WHERE create_time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
ORDER BY view_count DESC
LIMIT 10;
  • DATE_SUB(NOW(), INTERVAL 30 DAY) 計算 30 天前 00:00:00,UNIX_TIMESTAMP() 轉為時間戳常量。
  • 列 create_time 直接比較,用上索引。

避坑指南

  1. 不要在列上用 YEAR()/MONTH()/DAY():WHERE YEAR(create_time)=2024 → 全表掃描。改用 WHERE create_time >= UNIX_TIMESTAMP('2024-01-01') AND create_time < UNIX_TIMESTAMP('2025-01-01')。
  2. 不要用 DATE(FROM_UNIXTIME(col)):雙重函數,極慢。
  3. 字符串日期字面量隱式轉換風險:WHERE create_time > '2024-01-01' 依賴 SQL 模式 NO_ZERO_DATE 等,行為不確定,永遠顯式用 UNIX_TIMESTAMP()。
  4. 時區一致性:確保 MySQL time_zone、應用層、JVM/容器時區統一,避免時間戳偏移。

總結

MySQL 日期範圍查詢核心:**常量側做轉換,列側保持原樣**。對時間戳整數列,直接用 BETWEEN 或 >= AND < 配合 UNIX_TIMESTAMP() 預計算常量,配合索引實現毫秒級千萬行範圍查詢。

copyright © 萬盛學電腦網 all rights reserved