如何优化通过Timestamp过滤且需返回全部字段的SQL查询

1 索引失效根因 #

你创建的联合索引仅在查询_Date、_Time时生效，核心问题是WHERE条件对索引字段使用了函数运算：TIMESTAMP(_Date, _Time)对索引列做了计算，导致BTREE索引无法匹配，仅查询两个索引字段时触发了覆盖索引优化，才会走索引。一旦需要查询其他字段，优化器判断回表成本高于全表扫描，就会放弃使用索引。

2 不改表结构的即时优化方案 #

2.1 改写SQL触发索引+延迟关联降低回表开销 #

将时间范围判断拆分为对_Date和_Time的独立条件，匹配联合索引的前缀规则，同时用延迟关联避免分页场景下的无效回表：

-- 第一步：仅通过联合索引查询符合条件的主键ID，无需回表
WITH target_ids AS (
    SELECT id FROM logsTable
    WHERE 
        _Date = '2021-08-29'
        AND _Time BETWEEN '13:04:00' AND '15:04:00'
    LIMIT 135, 15
)
-- 第二步：仅对最终要返回的15条数据回表查全字段，大幅减少TEXT字段的读取开销
SELECT t.* FROM logsTable t
INNER JOIN target_ids ids ON t.id = ids.id;

注：如果是跨天的时间范围查询，只需将_Date = 'xxx'改为_Date BETWEEN '起始日期' AND '结束日期'即可正常触发索引。

2.2 TEXT字段优化建议 #

• 若非业务必须返回全部字段，不要用SELECT *，按需指定字段可以避免读取不需要的TEXT溢出页
• 如果raw、desc字段的内容大部分长度低于65535字节，可在业务允许的前提下改为VARCHAR类型，减少溢出页存储的额外开销

3 改表结构的长期优化方案 #

新增冗余的datetime类型字段，直接存储_Date和_Time的合并值，避免每次查询拼接：

-- 新增冗余字段并初始化值
ALTER TABLE logsTable ADD COLUMN dt DATETIME NOT NULL COMMENT '日期时间合并冗余字段';
UPDATE logsTable SET dt = TIMESTAMP(_Date, _Time);
-- 给冗余字段建索引
ALTER TABLE logsTable ADD INDEX idx_dt (dt) USING BTREE;

后续查询直接使用冗余字段判断，写法更简洁，也完全避免函数运算问题：

WITH target_ids AS (
    SELECT id FROM logsTable
    WHERE dt BETWEEN '2021-08-29 13:04:00' AND '2021-08-29 15:04:00'
    LIMIT 135, 15
)
SELECT t.* FROM logsTable t
INNER JOIN target_ids ids ON t.id = ids.id;

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