mysql - 组织 `where` 使其更快

Question

重要的事情

是否可以确定 WHERE 条件的最佳顺序以使其更快？例如，我有一个包含 6 个条件的查询。一些简单，另一些带有子查询或函数。我的想法是对查询进行概要分析，以确定条件语句 true 的常见程度以及运行成本。

阅读范例

比如我有:

WHERE
    table.ageMin >= :ageFilter AND       #1 age-more-than
    table.ageMax <= :ageFilter AND       #2 age-less-than
    YEAR(table.date) >= :dateFilter AND  #3 year
    (SELECT ...) = TRUE AND              #4 first-query
    (SELECT ...) = FALSE AND             #5 second-query
    USER_FUNCTION(table.tag, :tagFilter) #6 user-function

那么假设，在一个月内，我们有:

1. 在 95% 的情况下都是正确的；
2. 在 69% 的时间里是正确的；
3. 在 15% 的时间里是正确的；
4. 在 97% 的情况下都是正确的；
5. 在 50% 的情况下是正确的；
6. 在 99% 的情况下都是正确的；

考虑到(伪 ms 值):

1. 是一个简单的条件查询，查询成本为 0.005 毫秒；
2. 是一个简单的条件查询，查询成本为 0.005 毫秒；
3. 使用 MySQL YEAR() 查询耗时 0.030ms；
4. 使用子查询，查询耗时0.140ms；
5. 使用子查询，查询耗时0.260ms；
6. 使用用户函数，查询耗时0.450ms；

所以，我大概会这样想:

1. #3 将在 85% 的时间内失败，查询成本仅为 0.030 毫秒；
2. #2 将在 31% 的时间内失败，查询成本仅为 0.005 毫秒；
3. #1 将在 5% 内失败，但查询成本仅为 0.005ms；
4. #5 比 #4 多花费 +0.120 毫秒，但会在 50% 的时间内失败；
5. #4 耗时 0.140 毫秒，但只有 3% 的失败率；
6. #6 耗时 0.450 毫秒，但只有 1% 的失败率；

所以我的 WHERE 订单将是这样的:

WHERE
    YEAR(table.date) >= :dateFilter AND  #3 up, up
    table.ageMax <= :ageFilter AND       #2 maintained
    table.ageMin >= :ageFilter AND       #1 down down
    (SELECT ...) = FALSE AND             #5 up
    (SELECT ...) = TRUE AND              #4 down
    USER_FUNCTION(table.tag, :tagFilter) #6 maintained

所以如果我在一个月内有 1.000.000 个查询，在第一个例子中我将有:

1. 5% 将在 age-than 失败，花费 5.000 秒，休息 950.000；
2. 31% 将在 age-less-than 中失败，花费 4.750 秒，休息 655.500；
3. 85% 一年失败，花费 19.665 秒，休息 98.325 秒；
4. 3% 会在第一个子查询中失败，耗时 13.765 秒，剩余 95.375 秒；
5. 50%会在第二次子查询中失败，耗时24.797秒，剩余47.687秒；
6. 1% 会在用户函数中失败，花费 21.459 秒，休息 47.210 秒；

所以在 100 万次查询中，只有 47.210 次为真。它将花费 89.436 秒来处理一个月的所有查询。

在我重新排序我的 WHERE 之后，它将是:

1. 一年中有 85% 会失败，花费 30.000 秒，休息 150.000 秒；
2. 31% 将在 age-less-than 中失败，花费 750 秒，休息 103.500；
3. 5% 将在 age-than 失败，花费 517 秒，休息 98.325；
4. 50% 会在第二次子查询中失败，耗时 25.564 秒，剩余 49.165 秒；
5. 3% 会在第一个子查询中失败，耗时 6.883 秒，剩余 47.690 秒；
6. 1% 会在用户函数中失败，花费 21.460 秒，休息 47.210 秒；

因此，在 100 万次查询中，它将花费 85.174 秒，比原始订单少大约 4.262 秒。在这个简单的比较中，差异是 5%，但我有大约 50 个条件的表(一些复合物，其他则没有)。我尝试修复顺序，查询从 0.500 毫秒减少到 0.075 毫秒。

但是，一项一项地检查并决定哪个更常见且成本更低是一项艰巨的工作。那么，有一些工具可以让这项工作成功吗？

Answer 1

WHERE 中的条件顺序条款无关紧要。 MySQL 查询优化器做了很多类型的 changes查询以使其尽可能快地运行。

由于子查询，您的查询很慢。优化器可以将某些类型的子查询转换为表连接，这种变化带来了运行速度的较大提升。

我无法对您的子查询提出任何改进建议，因为您没有提供它们。

您可以改进条件的评估 YEAR(table.date) >= :dateFilter容易地。使用函数 (YEAR()) 会阻止 MySQL 使用索引。如果改成比较table.date针对一年中的第一天(例如，在 2015-01-01 中放置 2015 而不是 dateFilter)，然后 MySQL 可以使用索引并加快执行速度。

当然，如果您为该列创建了一个索引，则 MySQL 会使用一个索引。请确保您在列上有索引 date , ageMin和 ageMax .

放 EXPLAIN 在您的查询前面并根据 EXPLAIN output format 的文档检查结果找出您的查询缓慢的原因以及如何改进它。