MYSQL 在使用 OR 时提高查询性能

Question

我有一个非常简单的 MYSQL 表，有 2 列，我运行这个查询:

SELECT * FROM table WHERE (col1 = '123' AND col2 = '456')
                       OR (col1 = '456' AND col2 = '123')

Col1 和 col2 是复合主键:PRIMARY KEY('col1','col2')。两者中的每一个也是另一个表中主键的外键

当我为上述查询运行 EXPLAIN 命令时，我得到以下信息:

id  select_type  table  type    possible_keys   key key_len ref rows    Extra
1   SIMPLE       table  index   PRIMARY,col2    col2    8   NULL    1   Using where; Using index

上面结果中的 type 是 index ，它与 All 非常相似，因此在大型数据库上很可能会很慢。有没有办法改进上面的select命令

Answer 1

在现实中，诸如likely to be slow on a large database 之类的陈述应该是一个危险信号。

如果您要拥有一个大型数据集，那么分析和测试对于首先确定它是否会成为一个问题，然后确定它是否足以保证开发时间和成本来解决是至关重要的。通常这意味着微优化不太可能对大多数代码库产生任何影响。

无论如何，让我们回答这个问题。

是的，假设我们使用索引文件，如果您有大量数据并经常查询此表，则可以通过将查询拆分为多个执行集而不是在查询中使用表达式运算符来优化它，如果您只想像示例中那样查询两次，则可以使用 union 实现更高的性能，例如:

(
    SELECT * FROM test 
    WHERE
    (
        col1 = 123 AND col2 = 456
    )
)
UNION 
(
    SELECT * FROM test
    WHERE
    (
        col1 = 456 AND col2 = 123
    )
)

此查询的EXPLAIN如下:

ID      SELECT_TYPE  TABLE     TYPE POSSIBLE_KEYS   KEY KEY_LEN REF ROWS    EXTRA
1       PRIMARY      test      ref  PRIMARY PRIMARY 4   const   1   Using where; Using index
2       UNION        test      ref  PRIMARY PRIMARY 4   const   2   Using where; Using index
(null)  UNION RESULT <union1,2> ALL (null)  (null)  (null)  (null)  (null)  

看看这个 SQL fiddle http://sqlfiddle.com/#!2/9dc07a/1/0 的简单测试用例。

我在这篇文章中使用的语言，例如“可能”、“可能”等，是因为我没有预先加载这个包含数亿条记录的示例 - 我强烈建议您这样做并进行评估和分析您的查询更详细。

不幸的是，对于优化，并不总是有一个明确而简单的答案来执行 x 以获得更高的性能 - 查询优化器是一个复杂的野兽，有时试图获得每一点性能实际上会削弱你的应用程序(我是从这里的经验来说)所以请，除非您必须担心这些微优化 - 如果您这样做，请在决定方法之前对其进行全面评估、概要分析和测试。