mysql在简单或条件下不使用索引

Question

我遇到了一个由来已久的问题: MySQL拒绝为看似基本的东西使用索引。所涉问题如下：

SELECT c.*
FROM app_comments c
LEFT JOIN app_comments reply_c ON c.reply_to = reply_c.id
WHERE (c.external_id = '840774' AND c.external_context = 'deals')
 OR (reply_c.external_id = '840774' AND reply_c.external_context = 'deals')
ORDER BY c.reply_to ASC, c.date ASC

解释：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  c   ALL external_context,external_id,idx_app_comments_externals NULL    NULL    NULL    903507  Using filesort
1   SIMPLE  reply_c eq_ref  PRIMARY PRIMARY 4   altero_full.c.reply_to  1   Using where

external_id和external_context上分别有索引，我也尝试添加一个复合索引(idx_app_comments_externals)，但这一点也没有帮助。

在生产过程中，查询在4-6秒内执行(>1m记录)，但是删除WHERE条件的OR部分将减少到0.05s (尽管它仍然使用文件长度)。显然，索引在这里不起作用，但我不知道为什么。有人能解释一下吗？

我们使用的是MariaDB 10.3.18，这可能是错误的吗？

Answer 1

MySQL (和MariaDB)无法在不同的列或表上优化OR条件。注意，在查询计划的上下文中，c和reply_c被认为是不同的表。这些查询通常是使用UNION语句“手工”优化的，这些语句通常包含大量的代码重复。但是在您的例子中，对于支持CTEs (常用表表达式)的最近版本，您可以避免其中的大部分：

WITH p AS (
    SELECT *
    FROM app_comments
    WHERE external_id      = '840774'
      AND external_context = 'deals'
)
SELECT * FROM p
UNION DISTINCT
SELECT c.* FROM p JOIN app_comments c ON c.reply_to = p.id
ORDER BY reply_to ASC, date ASC

这个查询的好索引是(external_id, external_context)上的复合索引(按任何顺序排列)和(reply_to)上的单独索引。

虽然您不会避免使用"filesort"，但是当数据被过滤到一个小集合时，这就没有问题了。

Answer 2

使用WHERE子句中external_id列和external_context列上的相等谓词，MySQL可以有效地使用索引.当这些谓词指定可能满足查询的行子集时。

但是，将OR添加到WHERE子句后，从c返回的行不再受external_id和external_content值的限制。现在有可能返回带有这些列的其他值的行；带有这些列的任何值的行。

这否定了使用索引范围扫描操作的巨大好处.很快就从被考虑的范围内消除了大量行。是的，索引范围扫描用于快速定位行。那是真的。但问题的关键是，范围扫描操作使用索引快速绕过数以百万计的不可能返回的行。

这不是MariaDB 10.3特有的行为。我们将在MariaDB 10.2，MySQL 5.7，MySQL 5.6中观察到同样的行为。

我在质疑联接操作:当c中有多个匹配行时，是否需要从reply_c返回多个行的副本？还是规范只从c返回不同的行？

我们可以将所需的结果集分为两部分。

1) app_contents中在external_id和external_context上具有相等谓词的行

  SELECT c.*
    FROM app_comments c
   WHERE c.external_id       = '840774'
     AND c.external_context  = 'deals'
   ORDER
      BY c.external_id
       , c.external_context
       , c.reply_to
       , c.date

为了获得最佳的性能(不包括考虑覆盖索引，因为选择列表中的* )，这样的索引可以通过以下方式来满足范围扫描操作和顺序(消除使用文件短的操作)

   ... ON app_comments (external_id, external_context, reply_to, date)

2)结果的第二部分是与匹配行相关的reply_to行

  SELECT d.*
    FROM app_comments d
    JOIN app_comments e
      ON e.id = d.reply_to
   WHERE e.external_id       = '840774'
     AND e.external_context  = 'deals'
   ORDER
      BY d.reply_to
       , d.date

以前推荐的相同索引可用于访问e (范围扫描操作)中的行。理想情况下，该索引还将包括id列。我们最好的选择可能是修改索引，以便在id列之后包含date列。

   ... ON app_comments (external_id, external_context, reply_to, date, id)

或者，对于相同的性能，以牺牲额外的索引为代价，我们可以定义如下索引：

   ... ON app_comments (external_id, external_context, id)

对于使用范围扫描访问d中的行，我们可能需要一个索引：

   ... ON app_comments (reply_to, date)

我们可以将这两个集合与一个UNION ALL集运算符组合在一起；但是这两个查询都有可能返回相同的行。UNION运算符将强制进行唯一排序以消除重复行。或者，我们可以向第二个查询添加一个条件，以消除第一个查询将返回的行。

  SELECT d.*
    FROM app_comments d
    JOIN app_comments e
      ON e.id = d.reply_to
   WHERE e.external_id       = '840774'
     AND e.external_context  = 'deals'
  HAVING NOT ( d.external_id      <=> '840774'
           AND d.external_context <=> 'deals'
             )
   ORDER
      BY d.reply_to
       , d.date

将这两个部分组合在一起，将每个部分封装在一组parens中--添加UNION ALL set运算符和末尾的ORDER操作符(parens之外)，如下所示：

(
  SELECT c.*
    FROM app_comments c
   WHERE c.external_id       = '840774'
     AND c.external_context  = 'deals'
   ORDER
      BY c.external_id
       , c.external_context
       , c.reply_to
       , c.date
)
UNION ALL
(
  SELECT d.*
    FROM app_comments d
    JOIN app_comments e
      ON e.id = d.reply_to
   WHERE e.external_id       = '840774'
     AND e.external_context  = 'deals'
  HAVING NOT ( d.external_id      <=> '840774'
           AND d.external_context <=> 'deals'
             )
   ORDER
      BY d.reply_to
       , d.date
)
ORDER BY `reply_to`, `date`

这将需要对组合集进行“使用文件短”操作，但是现在我们已经很好地尝试为每个部分获得良好的执行计划。

当有多个匹配的reply_to行时，我们应该返回多少行，这仍然是我的问题。

Answer 3

但是，在下列查询中，名称索引不用于查找：

SELECT * FROM test
WHERE last_name='Jones' OR first_name='John';

在这里输入链接描述