查询sql where 后的条件中有时间比较的如果使用了DATE_FORMAT这种函数的话就不会走索引查询就会慢,优化方式:去掉了DATE_FORMAT函数,
如下:
表xxxx_premium数据量很大,operatedate为datetime类型,场景为查询每天的数据,用date_format函数:
EXPLAIN SELECT
*
FROM
xxxx_premium p
WHERE
1 = 1
AND DATE_FORMAT(p.operatedate,'%Y-%m-%d') = '2022-06-02'
(或者是AND Date(p.operatedate) = '2022-06-02')
会全表查询
而去掉date_format,改用between,并且operatedate建索引
EXPLAIN SELECT
*
FROM
xxxx_premium p
WHERE
1 = 1
AND p.operatedate BETWEEN '2022-06-02 00:00:00' AND '2022-06-02 23:59:59';
查询量变少了很多,并且耗时显著提升