原因
- 查询语句写得烂
- 索引失效
- 关联查询过多(设计缺陷)
- 服务器调优及各个参数设置(缓冲、线程数等)
Join查询的SQL执行顺序
- 手写
SELECT <select_list>
FROM <left_table><join_type>
JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
- 机读
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
- 总结
共七种Join
左连接: SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key;
右链接: SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;
内连接: SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key=B.Key;
只有A: SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key WHERE B.Key IS NULL;
只有B: SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key WHERE B.Key IS NULL;
全连接: SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key=B.Key;
A、B各自独有: SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key=B.Key WHERE A.Key IS NULL OR B.Key IS NULL;
MySQL没有FULL OUTER语法:
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
union
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;