MySQL； LEFT OUTER 在 DATEDIFF 上加入索引需要 300 多秒(但只是第一次)

Question

MySQL 的内置缓存确实让这个问题在一天中的大部分时间里都毫无意义，但是第一次运行以下查询时，性能很糟糕:第一次花费了 300 秒以上，而后续查询则花费了 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 年 7 月 1 日之前的项目。

bigTable 有 1000 万条记录，我只想将这些记录的“配对”与较小的表相匹配。部分问题是他们无法共享将它们链接在一起的直接 key 或引用，因此我留下了 LEFT OUTER JOIN并猜测如果两个事件的时间戳相距 < 11 天(并且共享其他条件)，那么它们一定是相关的。

排除测试DATEDIFF ( bt.datetime, st.datetime) < 11创建了 14k 个“结果”，说明 DATEDIFF 的数量“需要发生”的计算是 14k-8k(又名 6k)。

索引:datetime每个表的字段，code ，以及item_id s。

我在两个表上都有复合索引，顺序为 (item_id, datetime) 。根据我的理解，这是必要的顺序，因为我们在 select 语句中以 DATEDIFF( bt.datetime, st.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 条记录)
• 从未发生过更新或删除
• 我可能可以运行两个查询 - 将这个查询更改为 INNER JOIN，然后运行第二个查询，从总记录中减去结果数，以查找 BT 中没有相应结果的数字
• 我们已经执行了 phpmyadmin 的 Check Table , Defragmentation ，和Optimize Table在英国电信

[aside]这是否是使用 Mongo 等 NoSQL 数据库的好方案？

为什么第一轮和第二轮会有这么大的差距？更重要的是:可以采取哪些措施来改善首次运行的时机？

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

更新:成功!第一次运行查询现在只花费了 6 秒，考虑到它的来源，这是令人惊奇的。后续查询只用了0.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) 上的复合索引应该会有所帮助。