如何计算SQL查询耗时判断效率 两个log_timestamp索引表查询哪个更快

问题中的两个查询 #

第一个查询 #

SELECT
    name
FROM
    user_table
WHERE
    DATE(DATETIME_ADD(log_timestamp , INTERVAL 7 HOUR)) >= DATE('2018-01-01')
    AND 
    DATE(DATETIME_ADD(log_timestamp , INTERVAL 7 HOUR)) < DATE('2019-01-01');

第二个查询 #

SELECT
    name
FROM
    user_table
WHERE
    log_timestamp >= DATETIME_SUB('2018-01-01', INTERVAL 7 HOUR)
    AND 
    log_timestamp < DATETIME_SUB('2019-01-01', INTERVAL 7 HOUR);

第二个查询执行速度明显更快。

• 数据库的索引是基于字段的原始值构建的：本题中log_timestamp列的索引存储的都是该列的原始时间值，只要在查询时对索引字段做了函数/表达式处理，数据库就没法直接匹配索引里的存储内容。
• 第一个查询无法触发索引：它的WHERE条件里给log_timestamp套了DATETIME_ADD和DATE两层函数，数据库只能逐行扫描全表所有记录，先给每行的log_timestamp做计算，再判断是否符合条件，数据量越大性能损耗越明显。
• 第二个查询可以充分利用索引：它把所有时间计算都挪到了常量侧，log_timestamp本身没有任何额外处理，只是和两个提前计算好的时间边界做比较，数据库可以直接通过索引快速定位到符合要求的行范围，不需要全表扫描，执行效率高很多。

额外说明：两个查询的返回结果完全等价，第二种写法只是做了逻辑等价的条件转换，符合数据库索引优化的通用规范。

内容的提问来源于stack exchange，提问作者Fahmi Fachrizal