主页 / dba / 问题 / 1792
Accepted
Patrick
Asked: 2011-03-18 12:56:41 +0800 CST

在 MySQL 中，WHERE 子句中列的顺序会影响查询性能吗？

  • 772

我在某些可能具有大量结果集的数据库查询上遇到性能问题。

有问题的查询，我AND在 WHERE 子句中有三个

条款的顺序重要吗？

如，如果我将 ASI_EVENT_TIME 子句放在首位（因为这会从任何子句中删除大部分结果。

这会改善查询的运行时间吗？

询问：

SELECT DISTINCT  activity_seismo_info.* 
FROM `activity_seismo_info` 
WHERE 
    activity_seismo_info.ASI_ACTIVITY_ID IS NOT NULL  AND 
    activity_seismo_info.ASI_SEISMO_ID IN (43,44,...,259) AND 
    (
        activity_seismo_info.ASI_EVENT_TIME>='2011-03-10 00:00:00' AND 
        activity_seismo_info.ASI_EVENT_TIME<='2011-03-17 23:59:59'
    ) 

ORDER BY activity_seismo_info.ASI_EVENT_TIME DESC

查询说明：

+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
| id | select_type | table   | type  | possible_keys             | key          | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
|  1 | SIMPLE      | act...o | range | act...o_FI_1,act...o_FI_2 | act...o_FI_1 | 5       | NULL | 65412 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+

使用：

PHP 5.2

MySQL 5.0.51a-3ubuntu5.4

推进 1.3

Symfony 1.2.5

mysql performance

6 个回答

  1. Best Answer

    我不这么认为。查询优化器应该足够聪明。

    您可以尝试重新排列 WHERE 子句，并查看 EXPLAINS 在每种情况下都告诉您相同的内容。

    关于可以做些什么来优化这个查询：ASI_EVENT_TIME 上有索引吗？（这是我认为对于这个查询最关键的，因为您也使用它对结果进行排序）。

    其他两个字段（ASI_SEISMO_ID 和 ASI_ACTIVITY_ID）是否有索引？

    如果您发布表结构，这将很有帮助。

    • 28

  2. 从文档中：

    如果表有一个多列索引，优化器可以使用索引的任何最左边的前缀来查找行。例如，如果您在 (col1, col2, col3) 上有一个三列索引，则您在 (col1)、(col1, col2) 和 (col1, col2, col3) 上有索引搜索功能。

    如果列不构成索引的最左前缀，则 MySQL 不能使用索引。

    所以是的，它应该与复合索引中列的顺序相同。

    • 19

  3. 不，没关系。

    优化器在解析 SQL 后立即执行一系列简单的转换——这就是其中之一。

    • 10

  4. WHERE foo 和 bar

    优化与

    WHERE bar 和 foo

    然而，

    WHERE non-equal#1 AND non-equal#2

    无法优化这两个部分。例如，

    其中 a 介于 1 和 3 之间且 b > 17

    不能很好地利用 INDEX(a,b) 或 INDEX(b,a)

    换一种说法，首先使用 WHERE 子句中的任何“=”测试 AND 一起使用，然后可以处理一个非“=”（IN、BETWEEN、> 等）。可以有效优化的不超过一个。

    您的查询有 3 个这样的子句。

    事实证明，INDEX(EVENT_TIME) 可能是最有用的——它将帮助处理 AND 之一，并且可以用来避免 ORDER BY 的“文件排序”。

    如果没有重复的行（为什么会有重复的行？），然后摆脱 DISTINCT。这会导致更多的努力。

    询问性能问题时，请提供 SHOW CREATE TABLE 和 SHOW TABLE STATUS。

    更新...IN( list of constants )在某些情况下，较新的版本（例如 MySQL 5.7 ）可以将=. 为了安全起见，请遵循以下顺序（每个部分都是可选的）：

    1. 任意数量=。
    2. 一些INs.
    3. 最多一个范围。
    • 8

  5. MySQL 优化文档说：

    您可能很想重写查询以使算术运算更快，同时牺牲可读性。因为MySQL 会自动进行类似的优化，所以您通常可以避免这项工作，并将查询保留在更易于理解和维护的形式中。MySQL执行的一些优化如下：

    • ...

    • 对于连接中的每个表，构造一个更简单的 WHERE以获得对表的快速 WHERE 评估并尽快跳过行。

    • 查询每个表索引，并使用最佳索引，除非优化器认为使用表扫描更有效。曾经，根据最佳索引是否跨越超过 30% 的表来使用扫描，但固定百分比不再决定使用索引还是扫描之间的选择。优化器现在更加复杂，它的估计基于其他因素，例如表大小、行数和 I/O 块大小。

    这样，查询优化器忽略我们在查询中使用列的 HOW-order 是合理的（不仅 MySQL，SQL 是一种声明性语言，必须做我们想做的事情，而不是我们想要的事情）。

    但是我仍然喜欢在查询中对复合键的列进行相同的排序，但有时这是不可避免的，例如当我们使用 ORM 或 ActiveRecord 时，在 yii2 等一些框架中，自定义关系条件将附加到末尾“开启”条件，但我们仍然需要在应用程序的不同部分使用 QueryBuilders 的功能。

    • 1

  6. 在 WHERE/HAVING 子句中使用且具有高选择性（唯一值的数量/记录总数 > 10%~20%）的任何字段都必须被索引。

    因此，如果您的ASI_EVENT_TIME列有许多可能的值，请首先将它们全部索引。然后正如@ypercube 所说，尝试重新排列它们，看看 EXPLAIN 告诉你什么。应该都是一样的。

    此外，希望您看看Indexing SQL LIKE Filters。虽然这不是您需要的答案，但您仍将了解索引如何在幕后工作。

    *编辑： 请参阅下面评论中提供的链接以了解有关索引的更多信息。

    • -2

相关问题