SQL性能下降原因及Join查询

原因

1. 查询语句写得烂
2. 索引失效
3. 关联查询过多（设计缺陷）
4. 服务器调优及各个参数设置（缓冲、线程数等）

Join查询的SQL执行顺序

1. 手写

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>

2. 机读

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>

3. 总结
   共七种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;