如何告诉MySQL优化程序在派生表上使用索引? [英] How do I tell the MySQL Optimizer to use the index on a derived table?

问题描述

假设您有这样的查询...

Suppose you have a query like this...

SELECT T.TaskID, T.TaskName, TAU.AssignedUsers
FROM `tasks` T
    LEFT OUTER JOIN (
        SELECT TaskID, GROUP_CONCAT(U.FirstName, ' ',
            U.LastName SEPARATOR ', ') AS AssignedUsers
        FROM `tasks_assigned_users` TAU
            INNER JOIN `users` U ON (TAU.UserID=U.UserID)
        GROUP BY TaskID
    ) TAU ON (T.TaskID=TAU.TaskID)

可以将多个人分配给给定任务.该查询的目的是在每个任务中显示一行，但将分配给该任务的人员显示在同一列中

Multiple people can be assigned to a given task. The purpose of this query is to show one row per task, but with the people assigned to the task in a single column

现在...假设您已经在tasks，users和tasks_assigned_users上设置了正确的索引.将tasks连接到派生表时，MySQL Optimizer仍将不使用TaskID索引. WTF?！?！?

Now... suppose you have the proper indexes setup on tasks, users, and tasks_assigned_users. The MySQL Optimizer will still not use the TaskID index when joining tasks to the derived table. WTF?!?!?

所以，我的问题是...如何使此查询使用task_assigned_users.TaskID上的索引?临时表很la脚，因此，如果这是唯一的解决方案，那么MySQL Optimizer是愚蠢的.

So, my question is... how can you make this query use the index on tasks_assigned_users.TaskID? Temporary tables are lame, so if that's the only solution... the MySQL Optimizer is stupid.

使用的索引:

• 任务
  • PRIMARY-TaskID
  • tasks
    • PRIMARY - TaskID
    • PRIMARY-用户ID
    • PRIMARY-(TaskID，UserID)
    • 附加索引UNIQUE-(UserID，TaskID)

    编辑:此外，本页表示，派生表在连接发生之前已执行/实例化.为什么不重新使用键来执行联接?

    Also, this page says that derived tables are executed/materialized before joins occur. Why not re-use the keys to perform the join?

    MySQL优化器不允许您放置派生表上的索引提示(大概是因为派生表上没有索引)

    EDIT 2: MySQL Optimizer won't let you put index hints on derived tables (presumably because there are no indexes on derived tables)

    这是关于此的非常好的博客文章:

    EDIT 3: Here is a really nice blog post about this: http://venublog.com/2010/03/06/how-to-improve-subqueries-derived-tables-performance/ Notice that Case #2 is the solution I'm looking for, but it appears that MySQL does not support this at this time. :(

    刚刚找到此:从MySQL 5.6.3开始，优化器可以更有效地处理FROM子句中的子查询(即派生表):...在查询执行期间，优化器可能会向其中添加索引派生表以加快从中进行行检索的速度."似乎很有希望...

    EDIT 4: Just found this: "As of MySQL 5.6.3, the optimizer more efficiently handles subqueries in the FROM clause (that is, derived tables):... During query execution, the optimizer may add an index to a derived table to speed up row retrieval from it." Seems promising...

    推荐答案

    MySQL Server 5.6中有一个解决方案-预览版(在撰写本文时).

    There is a solution to this in MySQL Server 5.6 - the preview release (at the time of this writing).

    http://dev.mysql. com/doc/refman/5.6/en/from-clause-subquery-optimization.html

    尽管如此，我不确定MySQL Optimizer是否会在将索引添加到派生表中"时重用已经存在的索引.

    Although, I'm not sure if the MySQL Optimizer will re-use indexes that already exist when it "adds indexes to the derived table"

    考虑以下查询:

    选择*从t1 JOIN(SELECT * FROM t2)AS named_t2 ON t1.f1 = derived_t2.f1;

    SELECT * FROM t1 JOIN (SELECT * FROM t2) AS derived_t2 ON t1.f1=derived_t2.f1;

    文档说:如果这样做可以允许对最低成本的执行计划使用ref访问，那么优化器将在f1的f1列上构造索引."

    The documentation says: "The optimizer constructs an index over column f1 from derived_t2 if doing so would permit the use of ref access for the lowest cost execution plan."

    好的，那很好，但是优化程序会重用t2的索引吗?换句话说，如果存在针对t2.f1的索引该怎么办?该索引会被重用，还是优化程序为派生表重新创建此索引?谁知道?

    OK, that's great, but does the optimizer re-use indexes from t2? In other words, what if an index existed for t2.f1? Does this index get re-used, or does the optimizer recreate this index for the derived table? Who knows?

    在MySQL 5.6之前，最好的解决方案是创建一个临时表，在该表上创建一个索引，然后在临时表上运行SELECT查询.

    The best solution until MySQL 5.6 is to create a temporary table, create an index on that table, and then run the SELECT query on the temp table.

    这篇关于如何告诉MySQL优化程序在派生表上使用索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！