MySQL；在DATEDIFF上左转，索引取300+秒(但仅为第一次)

Question

MySQL内置的缓存确实使这个问题在一天中没有意义，但在第一次运行以下查询时，性能非常糟糕:第一次使用300秒以上，而随后的查询可以在毫秒内完成。用SQL_NO_CACHE运行这个程序需要2-4秒(!)在这种情况下，这是非常可以接受的--但是初始运行时不是。

SELECT DATEDIFF( bt.`datetime`, st.`datetime`) AS 'day_separation'
FROM `smallerTable` AS st
LEFT OUTER JOIN `bigTable` AS bt ON bt.item_id = st.item_id
  AND bt.code = 'X'
  AND bt.`datetime` > st.`datetime`
  AND DATEDIFF ( bt.datetime, st.datetime) < 11
  AND st.`datetime` > '2012-07-01' AND st.`datetime` < 'yesterdays-date 23:59:59'

我已经介绍了多列索引(thanks to this question)，但是它仍然不能解决这个特殊的问题。This solution看起来很受启发，但我不认为它适用，因为我不知道如何结合这些结果。

这张较小的桌子有8000张记录，我现在就想数一数/包括所有的记录。它最终将变得更大，并包含2012-07-01年之前的项目。

bigTable有1000万条记录，我只想将这些记录的“配对”匹配到较小的表中。问题的一部分是，他们不能共享一个直接键或引用将它们连接在一起，所以我只剩下一个LEFT OUTER JOIN，并猜测如果这两个事件的时间戳相距小于11天(并共享其他条件)，那么它们必须是相关的。

不包括测试，DATEDIFF ( bt.datetime, st.datetime) < 11创建了14k‘结果’，说明“需要发生”的DATEDIFF计算的数量是14k-8k (即k.a)。6k)。

索引：每个表、code和item_id的datetime字段。

两个表都有按(item_id, datetime)顺序排列的复合索引。据我理解，这是必要的顺序，因为我们以DATEDIFF( bt.datetime, st.datetime)的形式使用select语句中的datetime字段。

(code, item_id, datetime) 上的联合索引会彻底改变这个查询吗？(是的，它做到了！)

这一解释对我未受过训练的人来说几乎没有什么启示，除了它使用的是一张临时的桌子，而我知道这可能是很费时的。

id * select_type * table * type  * possible_keys * key                * key_len * ref           * rows * extra
1  * SIMPLE      * st    * index * NULL          * items_for_datetime * 59      * NULL          * 8295 * using index; using temporary; using filesort
1  * SIMPLE      * BT    * ref   * [many]        * items_for_datetime * 51      * master.st.item_id * 3    *

根据MySQL的奇想，bigTable有时会显示它更喜欢item_id键而不是items_for_datetime。我是否应该鼓励使用我的联合索引，相信我知道的更好？

一些额外的信息：

• 这些表中的插入每天发生一次(BT的1~5k记录)
• 从未发生任何更新或删除。
• 我可能会运行两个查询--将此查询更改为内部联接，然后运行第二个查询，从总记录中减去结果数，以查找BT中没有相应结果的数字。
• 我们已经在BT上执行了phpmyadmin的Check Table、Defragmentation和Optimize Table

此外，这是否是使用NoSQL数据库(如Mongo )的一个好方案？

为何第一轮和第二轮会有这样的差距？更重要的是：可以做些什么来改善第一次运行的时间？

更新：新尝试需要新的一天才能发现其有效性。明天，我将使用BETWEEN和DATE_ADD来尝试Barmar的建议。我还在(code, item_id, datetime)上创建了一个组合索引。我将在明天报告结果，但欢迎任何其他想法。

更新：成功！这个查询的第一次运行现在只花了6秒，考虑到它来自何处，这是令人惊奇的。随后的查询只花了.035秒！多好的梦啊。毫无疑问，(code, item_id, datetime)的综合指数对这一成功起了作用。下面是一个新的查询:谢谢大家！

SELECT DATEDIFF( bt.`datetime`, st.`datetime` ) AS  'day_separation'
FROM  `smallerTable` AS st
LEFT OUTER JOIN bigTable AS bt USE INDEX (  `cmd_item_time` ) 
ON bt.item_id = st.item_id
  AND bt.code =  'X'
  AND bt.`datetime` BETWEEN st.`datetime` AND DATE_ADD( st.`datetime`, INTERVAL 10 DAY ) 
  AND st.datetime BETWEEN '2012-07-01' AND  'yesterdays-date 23:59:59'

Answer 1

试着改变：

AND bt.`datetime` > st.`datetime`
AND DATEDIFF ( bt.datetime, st.datetime) < 11

至：

AND bt.`datetime` BETWEEN st.`datetime` AND date_add(st.`datetime`, interval 11 day)

这可能允许使用bt.datetime上的索引。

如果code = 'X'过滤掉了bigTable的很大一部分，那么(code, item_id)上的复合索引应该会有所帮助。

Answer 2

查询的问题很可能是这一行: st.datetime > '2012-07-01‘和st.datetime <’昨天-日期23:59:59‘

通过将日期时间转换为字符串(以便进行比较)，您将失去索引的优势.