优化SQL查询

Question

我正在努力优化这个慢查询（>2秒）

SELECT COUNT(*)
FROM crmentity c, mdcalls_trans_activity_update mtu, mdcalls_trans mt
WHERE (mtu.dept = 'GUN' OR  mtu.dept = 'gun') AND
      mtu.trans_code = mt.trans_code AND
      mt.activityid = c.crmid AND
      MONTH(mtu.ts) = 2 AND
      YEAR(mtu.ts) = YEAR(NOW()) AND
      c.deleted = 0 AND
      c.smownerid = 28

当我使用EXPLAIN时，以下为输出结果:

id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   SIMPLE  c   index_merge PRIMARY,crmentity_smownerid_idx,crmentity_deleted_smownerid_idx,crmentity_smownerid_deleted_idx crmentity_smownerid_idx,crmentity_deleted_smownerid_idx 4,8 NULL    91  Using intersect(crmentity_smownerid_idx,crmentity_deleted_smownerid_idx); Using where; Using index
1   SIMPLE  mt  ref activityid  activityid  4   pharex.c.crmid  60  
1   SIMPLE  mtu ref dept_idx    dept_idx    5   const   1530    Using where

我创建了一个索引（dept_idx），但是对于包含1,380,384条记录的数据集，仍需要超过2秒才能运行该查询。是否有其他更优的方式来表达此查询？

更新：根据David的建议，现在该查询只需要几毫秒就能执行完毕，而不是之前需要超过2秒（实际上，在MySQL 5.0版本上需要51秒）。

- FrancisV

---

我会写成 WHERE lower(mtu.dept) = 'gun' AND ... 但我猜你的数据库已经优化过了。 - initall

我发现，在Oracle中，至少在查询的左侧使用lower会导致巨大的减速。它是否比额外的字符串比较造成更多的减速... - graham.reeds

在列上使用 lower() 不使用任何索引是一个不错的方法。这可能解释了你的速度变慢的原因。 - David Schmitt

Graham，David，你们当然是对的。我不删除我的评论，这样反模式仍然存在;-) - initall

Answer 1

WHERE子句中最具选择性的部分是什么？也就是说，哪个条件从结果集中删除了最多的项目？

我猜测是mtu.ts过滤器。如果是这样，您还应该对mtu.ts列建立索引，并尝试以可以使用索引的方式进行约束；例如使用BETWEEN运算符。

其他提示：

• 将连接子句直接附加到连接上，使用JOIN ... ON ()，这使得查询更容易阅读，无论是对人还是优化器都是如此。
• 避免在查询中计算常量，如YEAR(NOW())。
• 避免在WHERE子句中选择列的函数，如MONTH(mtu.ts)。这大大减少了使用索引的可能性。
• 规范化数据以避免大小写问题，例如mtu.dept = 'GUN' OR mtu.dept = 'gun'；对表执行UPDATE mtu SET dept = lower(dept)并在表上适当使用CHECK dept = lower(dept)有助于避免这种疯狂。

- David Schmitt

Answer 2

1. 我会使用连接来重写查询语句。这样更清晰，让优化器有更好的机会。
2. 使用 mtu.ts between .. and .. 会更好，而不是 MONTH(mtu.ts) = 2 AND YEAR(mtu.ts) = YEAR(NOW())。

- burnall

---

你会如何重新编写这段代码呢？再次感谢。 - FrancisV

选择计数(*) 从crmentity c 内部连接mdcalls_trans mt on mt.activityid = c.crmid 内部连接mdcalls_trans_activity_update mtu on mtu.trans_code = mt.trans_code 其中mtu.ts在'20100201'和'20100228'之间，且(mtu.dept in ('GUN', 'gun')且c.deleted = 0且c.smownerid = 28)。 - burnall

谢谢这个例子。我在PHP中创建了一个函数来获取月份的开始日期和结束日期，并在“BETWEEN”语句中使用它。 - FrancisV

Answer 3

你能把文本字符串转换成数字吗？

- graham.reeds

Answer 4

我能看到的最明显的解决方案是将 COUNT(*) 更改为仅涵盖单个字段名称，否则您的索引可能几乎没有用处！

- daz-fuller

Answer 5

作为一个通用原则，分析这类问题的好方法是了解您正在匹配的数据，以及欣赏它的基数。

也就是说，按照最具选择性的顺序排列查询。 在您的数据中，更有可能的是dept = 'GUN'还是userId为28。

最后，您考虑过加入MT和MTU而不是过滤吗？ 这可能会使您的查询速度更快，因为您将限制需要进行日期比较的数据量。

- Russ Clarke

---

发布得太快了，基本上就是David Schmitt和Burnall所说的！ - Russ Clarke