假设您有这样的查询...
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)
这是关于此的非常好的博客文章: venublog/2010/03/06/how-to-improve-subqueries-derived-tables-performance/注意案例2是我正在寻找的解决方案,但看来MySQL目前不支持此解决方案. :(
EDIT 3: Here is a really nice blog post about this: venublog/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).
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优化程序在派生表上使用索引?
发布评论