Mysql 强制连接顺序

[zuoxingyu]

语法如下：

table_references:
    table_reference [, table_reference] …

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias]
        [{USE|IGNORE|FORCE} INDEX (key_list)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

table_reference STRAIGHT_JOIN table_factor
这个可以强制链接顺序，但是他是JOIN的，如果是LEFT JOIN或RIGHT JOIN呢??

怎么样强制某个表在前面？？

[小小小小周]

我也想知道..求解.

[WWWWA]

STRAIGHT_JOIN 无法应用于 LEFT JOIN 或 RIGHT JOIN。

变通一下：
SELECT * FROM a a1
STRAIGHT_JOIN b b1 ON a1.id=b1.id
LEFT JOIN a3 a2 ON a2.id=a2.id

[zuoxingyu]

select * from a left join b on a.id=b.id;

执行计划是先a 再b

2楼的这个变通办法没看明白。

[rucypli]

zz 5.1 参考手册
联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作，因为检查的表交换更少。请注意这说明如果执行下面类型的查询，MySQL进行全扫描b，因为LEFT JOIN强制它在d之前读取：

SELECT *
FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;

[zuoxingyu]

回楼上：

这里也没说明怎么样改变LEFT JOIN的连接顺序呀？

[zuoxingyu]

mysql> explain select * from t left join book on t.a=book.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
|  1 | SIMPLE      | t     | ALL    | NULL          | NULL    | NULL    | NULL       |    4 |       |
|  1 | SIMPLE      | book  | eq_ref | PRIMARY       | PRIMARY | 4       | mybook.t.a |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
2 rows in set (0.00 sec)

mysql> explain select * from book right join t on t.a=book.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
|  1 | SIMPLE      | t     | ALL    | NULL          | NULL    | NULL    | NULL       |    4 |       |
|  1 | SIMPLE      | book  | eq_ref | PRIMARY       | PRIMARY | 4       | mybook.t.a |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
2 rows in set (0.00 sec)

mysql>

怎么样写，才能够让他先 扫描BOOK表。就这个问题

[rucypli]

[Quote=引用 5 楼 zuoxingyu 的回复:]
回楼上：

这里也没说明怎么样改变LEFT JOIN的连接顺序呀？
[/Quote]
我的理解就是前面的表先读入

[shine333]

SELECT STRAIGHT_JOIN
 r.regionName, c.cityName, d.departmentName
FROM
 m_city c LEFT JOIN m_region r ON c.regionId = r.id,
 m_department d
WHERE c.id = d.cityId
ORDER BY r.regionName, c.cityName, d.departmentName

r和c的顺序不可能改变，但是dc的顺序可以改变

SELECT STRAIGHT_JOIN
 r.regionName, c.cityName, d.departmentName
FROM
 m_department d,
 m_city c LEFT JOIN m_region r ON c.regionId = r.id
WHERE c.id = d.cityId
ORDER BY r.regionName, c.cityName, d.departmentName

[zuoxingyu]

[Quote=引用 8 楼 shine333 的回复:]

SQL code
SELECT STRAIGHT_JOIN
r.regionName, c.cityName, d.departmentName
FROM
m_city c LEFT JOIN m_region r ON c.regionId = r.id,
m_department d
WHERE c.id = d.cityId
ORDER BY r.regionName, c.cit……
[/Quote]
你这个例子，CD是INNER JOIN的，所以可以交换顺序。

[shine333]

[Quote=引用 8 楼 shine333 的回复:]
r和c的顺序不可能改变，但是dc的顺序可以改变
[/Quote]
当然是先有左表，才可能去找右表，没左表，你找右表有什么意义？

[shine333]

小朋友们，我们现在来做游戏，这里有两个口袋，里面有M和N个球，每个口袋里面颜色不一样，但是两个口袋的球，彼此可能一样。现在我们要把这些球放到架子上面去

1 左边口袋和右边口袋颜色一样的，一起放到架子上面去，找不到一样的，就不放到架子上面去
INNER JOIN，这个时候，先左还是先右，无所谓

2 左边口袋的球，如果找得到右边口袋里面一样颜色的话，那么把两个球都放上去；如果找不到，就单独把左边口袋的球，放到右边架子上面去
LEFT JOIN，这个时候，你是先找左，还是先找右？

[ACMAIN_CHM]

LEFT JOIN 怎么优化啊。它肯定要全表扫描A表啊。