有没有办法强制执行MySQL的命令? [英] Is there a way to force MySQL execution order?

问题描述

我知道我可以使用FORCE INDEX (abc)关键字来更改MySQL执行查询的方式.但是有办法改变执行顺序吗?

I know I can change the way MySQL executes a query by using the FORCE INDEX (abc) keyword. But is there a way to change the execution order?

我的查询如下:

SELECT c.*
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1_id
INNER JOIN table3 c ON b.itemid = c.itemid
WHERE a.itemtype = 1
  AND a.busy = 1
  AND b.something = 0
  AND b.acolumn = 2
  AND c.itemid = 123456

对于我使用的每个关系/约束，我都有一个密钥.如果我在此语句上运行解释，我会看到mysql首先开始查询c.

I have a key for every relation/constraint that I use. If I run explain on this statement I see that mysql starts querying c first.

id    select_type    table    type
1     SIMPLE         c        ref
2     SIMPLE         b        ref
3     SIMPLE         a        eq_ref

但是，我知道按a -> b -> c顺序查询会更快(我证明了) 有没有办法告诉mysql使用特定命令?

However, I know that querying in the order a -> b -> c would be faster (I have proven that) Is there a way to tell mysql to use a specific order?

更新:这就是我知道a -> b -> c更快的原因.

Update: That's how I know that a -> b -> c is faster.

以上查询需要1.9秒才能完成，并返回7行.如果我将查询更改为

The above query takes 1.9 seconds to complete and returns 7 rows. If I change the query to

SELECT c.*
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1_id
INNER JOIN table3 c ON b.itemid = c.itemid
WHERE a.itemtype = 1
  AND a.busy = 1
  AND b.something = 0
  AND b.acolumn = 2
HAVING c.itemid = 123456

查询在0.01秒内完成(不使用我得到10.000行). 但是，这不是一个很好的解决方案，因为此查询是一个简化的示例.在现实世界中，我从c连接到其他表.由于HAVING是对整个结果执行的过滤器，因此这意味着我从数据库中提取的记录比不必要的要多.

the query completes in 0.01 seconds (Without using having I get 10.000 rows). However that is not a elegant solution because this query is a simplified example. In the real world I have joins from c to other tables. Since HAVING is a filter that is executed on the entire result it would mean that I would pull some magnitues more records from the db than nescessary.

Edit2:仅提供一些信息:

Just some information:

• 此查询中的可变部分是c.itemid.其他所有内容都是固定不变的值.
• 索引设置良好，mysql为我选择了正确的索引
  • a和b之间存在1:n关系(使用索引PRIMARY)
  • b和c之间存在多对多关系(使用索引IDX_ITEMID)
  • The variable part in this query is c.itemid. Everything else are fixed values that don't change.
  • Indexes are setup fine and mysql chooses the right ones for me
    • between a and b there is a 1:n relation (index PRIMARY is used)
    • between b and c there is a many to many relation (index IDX_ITEMID is used)

    关键是mysql应该开始查询表a，然后将其工作到c，而不是相反.为实现该目标而进行的任何更改.

    the point is that mysql should start querying table a and work it's way down to c and not the other way round. Any change to achive that.

    解决方案:并不是我想要的，但这似乎可行:

    Solution: Not exactly what I wanted but this seems to work:

    SELECT c.*
    FROM table1 a
    INNER JOIN table2 b ON a.id = b.table1_id
    INNER JOIN table3 c ON b.itemid = c.itemid
    WHERE a.itemtype = 1
      AND a.busy = 1
      AND b.something = 0
      AND b.acolumn = 2
      AND c.itemid = 123456
      AND f.id IN (
             SELECT DISTINCT table2.id FROM table1
             INNER JOIN table2 ON table1.id = table2.table1_id
             WHERE table1.itemtype = 1 AND table1.busy = 1)
    

    推荐答案

    也许您需要使用STRAIGHT_JOIN.

    http://dev.mysql.com/doc/refman/5.0/en/join.html

    STRAIGHT_JOIN与JOIN相似，不同之处在于始终在右表之前读取左表.这可以用于联接优化器以错误的顺序放置表的那些(很少)情况.

    STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

    这篇关于有没有办法强制执行MySQL的命令?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！