mysql - 范围查询不使用mysql中的索引

Question

我正在尝试优化我们用来生成报告的查询。我认为我在某些方面做了很好的优化。以下是原始查询:

select trat.asset_name as group_name,trat.name as sub_group_name,
       trat.asset_id as group_id,
       if(trat.cause_task_type='AccessRequest',true,false) as is_request_task,
       '' as grouped_on,
       concat(trat.asset_name,' - {0} (',count(*),')') as table_heading
    from t_remote_agent_tasks trat
    where trat.status in ('completed','failedredundant')
      and trat.name not in ('collect-data','update-conn-params')
    group by trat.asset_name, trat.name
    order by field(trat.name,'create-account','change-attr',
                             'add-member-to-group',
                             'grant-access','disable-account','revoke-access',
                             'remove-member-from-group','update-license')  

当我在 Extra 列中看到执行明文时，它显示 using where、Using Temporary、filesort。

所以我像这样优化查询

select  trat.asset_name as group_name,trat.name as sub_group_name,
        trat.asset_id as group_id,
        if(trat.cause_task_type='AccessRequest',true,false) as is_request_task,
        '' as grouped_on,
        concat(trat.asset_name,' - {0} (',count(*),')') as table_heading
    from t_remote_agent_tasks trat
    where trat.status in ('completed','failedredundant')
      and trat.name not in ('collect-data','update-conn-params')
    group by trat.asset_name,trat.name
    order by null

这给了我使用where，使用临时的执行计划。因此，文件排序不再使用，并且没有额外的开销，因为优化器不必排序，这将在 group by 期间得到照顾。

我再次继续按照在 group by 中提到的相同顺序在 group by 列上创建索引(这很重要，否则优化不会发生)，即在 (trat.asset_name,trat.name) 上创建索引。现在，这种优化使我只能在额外的列中使用 where 。此外，查询执行时间几乎减少了一半(之前是 0.568 秒，现在是 0.345 秒，虽然每次都不准确，但或多或​​少都在这个范围内)。

现在我想优化范围查询，位于查询部分下方

    trat.status in ('completed','failedredundant')
and trat.name not in ('collect-data','update-conn-params')

我正在阅读mysl reference guide to optimize range query ，也就是说not in is not in range查询，所以我在查询中做了这样的修改

    trat.status in ('completed','failedredundant')
and trat.name in ('add-member-to-group','change-attr','create-account',
                  'disable-account','grant-access', 'remove-member-from-group',
                  'update-license')

但它在 Extra 中没有显示出任何改进(我的意思是使用索引应该在那里，它仍然显示使用在哪里)。我还尝试将范围部分拆分为联合(这将更改查询结果，但执行计划仍然没有改进)

我需要一些有关如何进一步优化此查询的帮助，主要是范围部分(部分)。如果我需要对此进行任何其他优化？

感谢您的宝贵时间，提前致谢

编辑1我忘了提到我在trat.status上也有索引，所以下面是索引

1. (trat. Assets 名称,trat.名称)
2. (trat.status)

Answer 1

几乎在所有情况下，SELECT 中仅使用一个索引。因此，一个人必须拥有最好的。

前两个查询可能会从相同的“复合”索引中受益最多:

INDEX(asset_name, name)

通常情况下，人们会尝试处理 WHERE索引中的条件，但它们看起来不适合索引。 (下面有更多讨论。)第二个选择是 GROUP BY ，这是我推荐的。但是，由于(在第一种情况下)ORDER BY和 GROUP BY不同，必然会为 GROUP BY 的输出创建一个 tmp 表。这样就可以按照 ORDER BY 排序了。 (可能还有一个 tmp 并针对 GROUP BY 进行排序；我不知道。)

“使用索引”意味着使用了“覆盖”索引。 “覆盖”索引是一个复合索引，其中包括 SELECT 中任何地方使用的所有列。 。这大约有 5 列，尝试可能并不明智。 (更多内容见下文。)

还有一点需要注意，即使是这么简单的事情:

WHERE x IN (11,22)
GROUP BY y

不能使用任何索引来处理 WHERE和GROUP BY 。因此，您的查询无法同时使用两者(除非通过“覆盖”)。

覆盖索引在使用时只有部分用处。它说所有的工作都是在索引的 BTree 中完成的。但这可能包括完整索引扫描——这并不比全表扫描快多少。这是反对建议“覆盖”的另一个论点。

在某些情况下，IN或OR可以通过将其变成 UNION 来加快速度:

( SELECT ... WHERE status in ('completed') )
UNION ALL
( SELECT ... WHERE status in ('failedredundant') )

但这只会导致您偶然发现NOT IN(...)条款，这比 IN 更糟糕.

寻找最佳索引的目标是找到在 BTree 中连续存在行(在索引和/或表中)的索引。

要对此查询进行任何进一步的改进，可能需要重新考虑架构 - 它似乎迫使您拥有 IN , NOT IN , FIELD以及其他难以优化的结构。