快速查询，理论上使用索引，仍然在慢速查询日志中。

Question

我很难解释这些查询的EXPLAIN结果。它们都在慢速查询日志中结束，但执行时间约为0.0050ms，最终结果集总是在100行以下。这里怎么了？我的第二个“改进”版本会更好吗？

有什么建议吗？

  mysql> # Original
  mysql> EXPLAIN SELECT SQL_NO_CACHE relSrc, relDst, 1 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships1
      -> LEFT JOIN productsRelationshipsDesc on 1=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 2 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships2
      -> LEFT JOIN productsRelationshipsDesc on 2=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 3 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships3
      -> LEFT JOIN productsRelationshipsDesc on 3=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 5 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships5
      -> LEFT JOIN productsRelationshipsDesc on 5=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 6 as relType, relTypeDesc, fracQty, '24794' as source      FROM productsRelationships6
      -> LEFT JOIN productsRelationshipsDesc on 6=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 7 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships7
      -> LEFT JOIN productsRelationshipsDesc on 7=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> ORDER BY relType, relSrc, RelDst;
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+
  | id | select_type  | table                     | type        | possible_keys       | key           | key_len | ref   | rows  | Extra                                   |
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+
  |  1 | PRIMARY      | productsRelationships1    | index       | PRIMARY,src-1       | src-1         | 2       | NULL  |   663 | Using where; Using index                |
  |  1 | PRIMARY      | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  2 | UNION        | productsRelationships2    | index       | src-dst-2           | src-dst-2     | 4       | NULL  | 13126 | Using where; Using index                |
  |  2 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  3 | UNION        | productsRelationships3    | index       | PRIMARY             | PRIMARY       | 4       | NULL  | 11459 | Using where; Using index                |
  |  3 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  4 | UNION        | productsRelationships5    | index       | PRIMARY,src-5       | src-5         | 2       | NULL  |   369 | Using where; Using index                |
  |  4 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  5 | UNION        | productsRelationships6    | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2     | NULL  |     2 | Using union(dst-6,PRIMARY); Using where |
  |  5 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  6 | UNION        | productsRelationships7    | index       | PRIMARY,src-7       | src-7         | 2       | NULL  |     1 | Using where; Using index                |
  |  6 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  | NULL | UNION RESULT | <union1,2,3,4,5,6>        | ALL         | NULL                | NULL          | NULL    | NULL  |  NULL | Using filesort                          |
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+
  13 rows in set (0.00 sec)

  mysql>
  mysql>
  mysql> # Improved?
  mysql> EXPLAIN SELECT SQL_NO_CACHE relSrc, relDst, relType, fracQty, source, relTypeDesc FROM (
      -> SELECT relSrc, relDst, 1 as relType, 0 as fracQty, '24794' as source FROM productsRelationships1
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 2 as relType, 0 as fracQty, '24794' as source FROM productsRelationships2
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 3 as relType, 0 as fracQty, '24794' as source FROM productsRelationships3
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 5 as relType, 0 as fracQty, '24794' as source FROM productsRelationships5
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 6 as relType,      fracQty, '24794' as source FROM productsRelationships6
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 7 as relType, 0 as fracQty, '24794' as source FROM productsRelationships7
      -> WHERE relDst='24794' OR relSrc='24794'
      -> ) AS rels
      -> LEFT JOIN productsRelationshipsDesc ON relType=relTypeID
      -> ORDER BY relType, relSrc, RelDst;
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+
  | id | select_type  | table                     | type        | possible_keys       | key           | key_len | ref          | rows  | Extra                                                |
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+
  |  1 | PRIMARY      | <derived2>                | ALL         | NULL                | NULL          | NULL    | NULL         |    38 | Using filesort                                       |
  |  1 | PRIMARY      | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | rels.relType |     1 | Using index                                          |
  |  2 | DERIVED      | productsRelationships1    | index       | PRIMARY,src-1       | src-1         | 2       | NULL         |   663 | Using where; Using index                             |
  |  3 | UNION        | productsRelationships2    | index       | src-dst-2           | src-dst-2     | 4       | NULL         | 13126 | Using where; Using index                             |
  |  4 | UNION        | productsRelationships3    | index       | PRIMARY             | PRIMARY       | 4       | NULL         | 11459 | Using where; Using index                             |
  |  5 | UNION        | productsRelationships5    | index       | PRIMARY,src-5       | src-5         | 2       | NULL         |   369 | Using where; Using index                             |
  |  6 | UNION        | productsRelationships6    | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2     | NULL         |     2 | Using union(dst-6,PRIMARY); Using where; Using index |
  |  7 | UNION        | productsRelationships7    | index       | PRIMARY,src-7       | src-7         | 2       | NULL         |     1 | Using where; Using index                             |
  | NULL | UNION RESULT | <union2,3,4,5,6,7>        | ALL         | NULL                | NULL          | NULL    | NULL         |  NULL |                                                      |
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+
  9 rows in set (0.00 sec)

这些是表的定义。

  mysql> SHOW CREATE TABLE productsRelationships1\G
  *************************** 1. row ***************************
         Table: productsRelationships1
  Create Table: CREATE TABLE `productsRelationships1` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`),
    UNIQUE KEY `src-1` (`relSrc`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships2\G
  *************************** 1. row ***************************
         Table: productsRelationships2
  Create Table: CREATE TABLE `productsRelationships2` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    KEY `src-dst-2` (`relSrc`,`relDst`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships3\G
  *************************** 1. row ***************************
         Table: productsRelationships3
  Create Table: CREATE TABLE `productsRelationships3` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships5\G
  *************************** 1. row ***************************
         Table: productsRelationships5
  Create Table: CREATE TABLE `productsRelationships5` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`),
    UNIQUE KEY `src-5` (`relSrc`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships6\G
  *************************** 1. row ***************************
         Table: productsRelationships6
  Create Table: CREATE TABLE `productsRelationships6` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relType` tinyint(2) unsigned NOT NULL DEFAULT '6',
    `fracQty` int(2) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`),
    UNIQUE KEY `src-6` (`relSrc`),
    UNIQUE KEY `dst-6` (`relDst`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships7\G
  *************************** 1. row ***************************
         Table: productsRelationships7
  Create Table: CREATE TABLE `productsRelationships7` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`),
    UNIQUE KEY `src-7` (`relSrc`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationshipsDesc\G
  *************************** 1. row ***************************
         Table: productsRelationshipsDesc
  Create Table: CREATE TABLE `productsRelationshipsDesc` (
    `relTypeID` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
    `relTypeDesc` varchar(100) NOT NULL,
    UNIQUE KEY `relTypeID` (`relTypeID`,`relTypeDesc`)
  ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

这是每个表中的数据量。

  mysql> SELECT COUNT(1) FROM productsRelationships1\G
  *************************** 1. row ***************************
  COUNT(1): 663
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships2\G
  *************************** 1. row ***************************
  COUNT(1): 263
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships3\G
  *************************** 1. row ***************************
  COUNT(1): 8551
  1 row in set (0.01 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships5\G
  *************************** 1. row ***************************
  COUNT(1): 369
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships6\G
  *************************** 1. row ***************************
  COUNT(1): 80
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships7\G
  *************************** 1. row ***************************
  COUNT(1): 0
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationshipsDesc\G
  *************************** 1. row ***************************
  COUNT(1): 7
  1 row in set (0.00 sec)

UPDATE:根据EXPLAIN，它似乎在使用索引，但是缓慢的查询日志显示这个# Query_time: 0.005458 Lock_time: 0.000340 Rows_sent: 38 Rows_examined: 50579，这是我取出ORDER子句的时候

这可能是一个快速的查询，但几乎在系统的每一个操作中都会调用它。

UPDATE2:好吧，我快疯了

# Query_time: 0.003527  Lock_time: 0.000164 Rows_sent: 38  Rows_examined: 8554
SET timestamp=1370017780;
SELECT SQL_NO_CACHE relSrc, relDst, 3 as relType, 0 as fracQty, '24794' as source
FROM   productsRelationships3
WHERE relSrc='24794' OR relDst='24794';

这是怎么可能的？productsRelationships3有一个组合索引PRIMARY KEY (relSrc,relDst)，为什么它的S检查每一行？

Answer 1

评论中有很多很好的内容，但我忽略了一些显而易见的东西，这使得问题的答案变得相当简单。

我错过了在查询中反复使用OR，我最初误读为AND.这就产生了很大的不同。你想要的是：

WHERE relDst='24794' OR relSrc='24794'

不幸的是，MySQL的EXPLAIN使用了短语using index，因为这并不意味着它正在索引中进行查找。如果表中所需的所有列都包含在单个索引中，则实际上可以执行相当于全表扫描的操作，并且仍然具有Using index。事实上，正如我下面所展示的，这正是这里所发生的事情。

Using index意味着优化器决定从索引中读取数据，而不是从实际的表行读取数据。由于索引几乎总是表中列的适当子集，因此，如果可以在单个索引中找到SELECT中的所有列，则意味着要处理的数据字节减少，从而可能降低查询在CPU、I/O和/或内存方面的成本。所以这是个好策略..。但这并不意味着索引被用来查找..。只是它是读取必要的列数据的一个成本较低的地方。

大多数查询的type列显示index。再一次，这并不意味着正在使用索引。这实际上与type=ALL相同--一个完整的表扫描--但是读取索引树而不是表数据.因此，type中的这个值与Using index是同时存在的。

用于查找的索引通常显示在key输出的EXPLAIN列中.但是当type=index和ref=NULL时，情况就不是这样了。

因此，在大多数查询中，优化器决定从头到尾读取整个PRIMARY键索引，在一个列OR中查找一个匹配的值。

多列索引不能用于解析OR条件。它只能用于解决AND条件.或涉及以索引中的第一列开头的连续列子集的条件。

一个2列索引很像一个打印的城市电话目录，按姓氏排序，然后再按名字排序.名字通常用粗体打印，地址用较小或不太突出的字体打印。

我不能用电话簿找到姓"Jones“OR名字"John”的每个人，但我可以非常有效和高效地使用它来查找每个姓"Jones“的人，AND姓”John“。(因此，我之前忽略了AND与OR之间的巨大差异。)我也可以非常有效地使用它来找到每个姓"Jones“的人，而不管他们的名字是什么，但如果我只想找到每个姓"John”的人，而不管他们的姓是什么，那是完全没用的。

或者不完全是。这并不是最优的，但在某种程度上仍然是有用的。继续这个类比，假设我仍然需要找到每个名字都是"John“的人，我将不得不阅读目录中的每一行，但我不需要读没有粗体的打印--地址不会告诉我任何关于名字的信息。

因此直接从索引读取数据的值，即使我不能从索引中的数据排序中受益.Using index意味着优化器找到了一个覆盖索引，可以满足它读取列数据的需要，无论索引是否用于查找或联接。

有一张桌子是个例外：

|  5 | UNION        | productsRelationships6    | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2     | NULL  |     2 | Using union(dst-6,PRIMARY); Using where |

这里的区别是，在这两个列上都有索引，其中感兴趣的列是索引中最左边的(在本例中是唯一的)列。

UNIQUE KEY `src-6` (`relSrc`),
UNIQUE KEY `dst-6` (`relDst`)

优化器巧妙地选择index_merge优化--在两个索引中查找所需的值并合并已识别的行。

如果您的所有表都有类似的安排，那么在慢速查询日志中检查的行应该会少得多--而且查询可能会完全从慢速查询日志中删除。

这些索引不一定是唯一的索引，尽管如果数据支持它，这是理想的，因为唯一的索引只能返回一个值。但我建议在每一张桌子上添加以下内容：

KEY dst_src (relDst,relSrc)

...or只是..。

KEY (relDst)

现在，您有两个索引--主键和这个新索引(同样，如果适合您的数据，或者不适合它，您可能希望使其为UNIQUE ).其中一个按relSrc (主键)排序，另一个按relDst排序.因此，优化器可以选择合并来自该键和主键的结果，并显著减少所涉及的行数。

文件无法避免，因为UNION ALL总是在幕后创建一个临时表.不能有索引..。但基于上述，我倾向于认为，这并不是查询被记录为“不使用索引”的原因。

最后，当您进行基准测试时，您可能希望在查询中使用SELECT SQL_NO_CACHE，以确保每次都执行查询，而不会看到看似小或奇怪地不一致的响应时间，因为如果在您的系统上启用了查询缓存，那么您将从查询缓存中获得结果。