MySQL Views OR vs IN子句

Question

Ubuntu10.04上的MySQL服务器版本: 5.1.41

在修改一些查询时，我发现MySQL的行为有所不同，我想知道原因。

基本上我是在创建一个视图。当我查询视图时，结果集是相同的，但是IN子句和OR子句读取的行数不同。下面是一个简单的例子：

CREATE TABLE country ( 
    id_country int(11) NOT NULL AUTO_INCREMENT, 
    name varchar(50) NOT NULL, 
    PRIMARY KEY (id_country) 
) ENGINE=InnoDB; 

INSERT INTO country (name) VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'); 

CREATE TABLE status ( 
    id_status int(11) NOT NULL AUTO_INCREMENT, 
    id_country int(11) NOT NULL, 
    status tinyint(4) NOT NULL, 
    PRIMARY KEY (id_status) 
) ENGINE=InnoDB; 
ALTER TABLE status ADD INDEX ( id_country ); 
ALTER TABLE status ADD FOREIGN KEY ( id_country ) REFERENCES test.country (id_country) ON DELETE RESTRICT ON UPDATE RESTRICT ; 

INSERT INTO status(id_country, status) VALUES 
(1,0), (2,1), (3,0), (4,1), (5,0),(6,1), (7,0), (8,1); 

CREATE ALGORITHM=MERGE VIEW view_country 
AS 
    SELECT c.*, s.id_status, s.status 
    FROM country c JOIN status s ON c.id_country = s.id_country; 

下面的两个explain语句显示了不同的解析行数

mysql> EXPLAIN EXTENDED  SELECT * FROM view_country WHERE id_country IN (1, 2, 3)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
         type: ref
possible_keys: id_country
          key: id_country
      key_len: 4
          ref: test.c.id_country
         rows: 1
     filtered: 100.00
        Extra: 
2 rows in set, 1 warning (0.00 sec)

使用OR子句

mysql> EXPLAIN EXTENDED SELECT * FROM view_country WHERE id_country = 1 OR id_country = 2 OR id_country = 3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
         type: ALL
possible_keys: id_country
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
     filtered: 37.50
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.s.id_country
         rows: 1
     filtered: 100.00
        Extra: 
2 rows in set, 1 warning (0.00 sec)

如果你看一下这两个查询中的“行”-它们加起来是不同的

与IN相比，查询with OR子句读取的行数更少，后者加起来适用于大型表和连接。

有没有人能告诉我为什么会这样？

谢谢您抽时间见我。

Answer 1

请注意，执行计划与索引的状态和表的大小有很大关系。即使对于类似的查询，MySQL的执行方式也可能有所不同，有时MySQL甚至会猜错。

带有连接的视图肯定会使事情变得复杂，所以SELECT语句就不那么简单了。不要对MySQL为IN和OR选择不同的执行计划感到惊讶。

在第一个查询中，MySQL选择对这两个查询使用索引，这会在解释中产生特定且准确的行数。

但是，在第二个查询中，MySQL选择扫描状态表中的所有行。这是有意义的，因为只有很少的行，而且MySQL无论如何都必须访问表，因为没有覆盖索引来返回所有需要的行。如果第二个查询实际上没有比第一个更快，我也不会感到惊讶。另外，请注意EXPLAIN中的行数(用于扫描)是估计值，因此在分析查询时要考虑到这一点。

第一个查询必须进行6次查找，而第二个查询在非常短的表扫描之后只需要进行3次查找。

MySQL有许多技巧，有时仅限于非常特定的场景，以尝试基于当前索引和行数优化查询。有记录在案的案例中，for similar queries, MySQL will take two different approaches and end up with the same execution path。还有其他情况下，两个完全不同的执行计划会导致类似的性能，这就是其中之一。

无论如何，我希望这能解释为什么会有差异，但只要结果是相同的，性能是相似的，就没有什么好担心的。

在某些情况下，正如我前面所说的，MySQL不会做出最好的猜测，然后您可以使用索引提示和自然连接等工具。在你的情况下，我认为MySQL表现得很好。

要研究性能和执行计划的更多信息，请查看以下两个站点：

• http://www.mysqlperformanceblog.com/
• http://explainextended.com/

Answer 2

如果我理解正确的话，您得到的结果是相同的，您想知道“OR”和“IN”子句在速度方面的区别以及它们是如何工作的。

如果是这样，那么我认为你的问题可能是这个问题的重复：IN vs OR in the SQL WHERE Clause