MySQL 索引和解释计划

Question

我有这个问题:

SELECT * FROM dwDimDate d 
LEFT JOIN tickets t FORCE INDEX FOR JOIN (idx_tickets_id_and_date) ON 
DATE_FORMAT(t.ticket_date, '%Y%m%d') = d.date_key 
LEFT JOIN sales s ON s.ticket_id = t.ticket_id
WHERE d.date_key BETWEEN 20130101 AND 20131231
GROUP BY d.date_key 

我正在寻求优化它的帮助。我一直在阅读所有我能理解的解释计划，并据此进行优化，但我似乎无法阻止 MySQL 在票证表上使用 ALL 类型查找。

索引:

解释计划:

我已经尝试使用 FORCE INDEX FOR JOIN 来尝试让它从日期开始索引，但它似乎没有接受提示。

dwDimDate 是一个包含一年中第几天的日期维度，因此在这种情况下，我认为限制为 365 天会很快，然后找到该日期范围内的所有工单。在该日期范围内应该只有大约 5000 张票。

如有任何帮助，我们将不胜感激。我不知道如何弄清楚采用什么策略来删除“ALL”查找。我想了解以后如何做到这一点，所以如果你能帮助“授之以渔”，那就太好了。

编辑该查询当前需要 11 秒才能运行，这将成为生产中的一个问题。

Answer 1

ON DATE_FORMAT(t.ticket_date, '%Y%m%d') = d.date_key 

当您像这样在 t.ticket_date 列上使用函数时，这将永远不会使用索引。

FORCE INDEX 不会神奇地将非 sargable 表达式变成 sargable 表达式。它只是暗示优化器假设表扫描是无限昂贵的。因此，优化器会说，“好吧，这对你来说很糟糕，因为这个连接表达式需要进行表扫描。”

一种解决方案是以通用格式存储 t.ticket_date 和 d.date_key。两者都使用 DATE 列或“YYYYmmdd”字符串。

第二种可能的解决方案:根据 t.ticket_date 创建一个虚拟列并为该虚拟列建立索引。

ALTER TABLE tickets 
  ADD COLUMN ticket_date_yyyymmdd AS (DATE_FORMAT(ticket_date, '%Y%m%d'),
  ADD INDEX (ticket_date_yyyymmdd);