-- 錯(cuò)誤示例：字符串與DATETIME比較
SELECT * FROM orders 
WHERE create_time = '2023-01-01';  -- 隱式轉(zhuǎn)換可能發(fā)生

-- 錯(cuò)誤示例：非標(biāo)準(zhǔn)日期格式
SELECT * FROM events 
WHERE event_time = '01/01/2023';  -- 可能無(wú)法識(shí)別或需要轉(zhuǎn)換

-- 錯(cuò)誤示例：DATETIME算術(shù)運(yùn)算
SELECT * FROM logs 
WHERE log_time + INTERVAL 1 DAY > NOW();  -- 雖然有效，但需注意上下文

-- 正確做法：使用標(biāo)準(zhǔn)格式
SELECT * FROM orders 
WHERE create_time = '2023-01-01 00:00:00';  -- 明確且高效

-- 正確做法：顯式轉(zhuǎn)換
SELECT * FROM events 
WHERE event_time = CAST('2023-01-01 10:00:00' AS DATETIME);

-- 使用DATE()函數(shù)提取日期部分
SELECT * FROM orders 
WHERE DATE(create_time) = '2023-01-01';

-- 使用TIME()函數(shù)提取時(shí)間部分
SELECT * FROM schedules 
WHERE TIME(start_time) BETWEEN '09:00:00' AND '17:00:00';

// PHP示例
$stmt = $pdo->prepare("SELECT * FROM orders WHERE create_time > ?");
$stmt->execute(['2023-01-01 00:00:00']);  // 明確傳遞DATETIME字符串

CREATE INDEX idx_create_time ON orders(create_time);

-- 高效：DATETIME在前面，適合按時(shí)間范圍+狀態(tài)查詢
CREATE INDEX idx_time_status ON orders(create_time, status);

-- 可能低效：狀態(tài)在前，時(shí)間在后
CREATE INDEX idx_status_time ON orders(status, create_time);

-- 查詢某天的所有記錄
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

-- 更精確的寫(xiě)法（避免邊界問(wèn)題）
SELECT * FROM orders 
WHERE create_time >= '2023-01-01 00:00:00' 
AND create_time < '2023-01-02 00:00:00';

-- 查詢上個(gè)月的記錄
SELECT * FROM transactions 
WHERE transaction_time >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
AND transaction_time < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01 00:00:00');

-- 查詢最近7天且狀態(tài)為'completed'的訂單
SELECT * FROM orders 
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
AND status = 'completed'
ORDER BY create_time DESC;

CREATE TABLE large_log (
    id BIGINT NOT NULL AUTO_INCREMENT,
    event_time DATETIME NOT NULL,
    -- 其他字段
    PRIMARY KEY (id, event_time)
) PARTITION BY RANGE (TO_DAYS(event_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    -- 更多分區(qū)...
);

EXPLAIN SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31';

-- 測(cè)試1：使用BETWEEN（可能有問(wèn)題）
SELECT SQL_NO_CACHE COUNT(*) FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31 23:59:59';

-- 測(cè)試2：使用>=和<（推薦）
SELECT SQL_NO_CACHE COUNT(*) FROM orders 
WHERE create_time >= '2023-01-01' AND create_time < '2023-02-01';

SELECT * FROM logs 
WHERE DATE(log_time) = '2023-01-01';  -- 對(duì)列使用函數(shù)導(dǎo)致索引失效

SELECT * FROM logs 
WHERE log_time >= '2023-01-01 00:00:00' 
AND log_time < '2023-01-02 00:00:00';

SELECT * FROM events 
WHERE YEAR(event_time) = 2023 AND MONTH(event_time) = 1;  -- 索引失效

SELECT * FROM events 
WHERE event_time >= '2023-01-01' 
AND event_time < '2023-02-01';

SET time_zone = '+08:00';  -- 例如設(shè)置為東八區(qū)

-- 創(chuàng)建覆蓋索引
CREATE INDEX idx_covering ON orders(create_time, id);

-- 高效查詢（不需要回表）
SELECT id, create_time FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31';

-- 確保以下查詢能利用ICP
SELECT * FROM orders 
WHERE create_time > '2023-01-01' AND status = 'pending';

-- 添加生成列存儲(chǔ)日期部分
ALTER TABLE orders ADD COLUMN create_date DATE 
GENERATED ALWAYS AS (DATE(create_time)) STORED;

-- 為生成列創(chuàng)建索引
CREATE INDEX idx_create_date ON orders(create_date);

-- 現(xiàn)在可以高效查詢
SELECT * FROM orders WHERE create_date = '2023-01-01';