如何从mysql中索引concat日期时间

Question

select *
from table
where '2022-09-30 13:05:30.000' between concat(start_date, ' 00:00:00.000') 
and concat(end_date, ' 23:59:59.999')

上面的例子没有使用索引，因为它使用了concat函数。我该怎么办？start_date，end_date类型'yyyy-MM-dd'

Answer 1

查询需要避免操作WHERE内部的列。这使得条件不可搜索

请澄清正在使用的数据类型。似乎start_date和end_date是DATE，而不是DATATIME。然而，字面上的"2022-09-30 13:05:30.000"似乎是DATATIME(更具体地说是DATETIME(3)(

DATE和DATETIME可以进行比较，因此这在不跟踪0时间的情况下是可以的：

start_date >= '2022-09-30 13:05:30.000'

除了从30日起什么都抓不到。

另一端更混乱，因为DATE映射到该日期早晨的午夜。这些作品中的任何一个：

end_date > '2022-09-30'
end_date > '2022-09-30 13:05:30.000'

除了你的"；end_ date"；可能设置为"2022-09-30"，意味着"；那天什么时候"；？

end_date >= DATE('2022-09-30 13:05:30.000')

同样有效。现在是"；sargable"；因为优化器可以在尝试执行查询之前评估CCD_ 11。

但是。。。指数是多少？没有"的索引；介数"；在日期或日期时间"；。优化器不具有这样的概念，例如；结束日期"；总是晚于"；开始日期"；。以及许多其他假设。

即使您有一个涉及start_date和/或end_date的索引，也只能使用其中一个。这是因为优化器没有办法进行两次"优化"；范围"；同时进行测试。如果它使用其中一个，那么它很可能是在扫描表格的前半部分或后半部分。在这一点上，它不会在索引上浪费时间。

如果你想进一步描述一下这个应用程序，我可能还有其他技巧。对于类似的任务，这里有一个非常有效(但很复杂(的方法：http://mysql.rjweb.org/doc.php/ipranges

Answer 2

使用STR_TO_DATE转换为DATETIME

SELECT
*
FROM
`table`
WHERE
STR_TO_DATE(
'2022-09-30 13:05:30.000', '%Y-%m-%d %H:%i:%s.%f'
) BETWEEN STR_TO_DATE(
CONCAT(start_date, ' 00:00:00.000'), '%Y-%m-%d %H:%i:%s.%f'
)
AND STR_TO_DATE(
CONCAT(end_date, ' 23:59:59.999'), '%Y-%m-%d %H:%i:%s.%f'
)