MySQL 学习笔记2.0 SQL刨析

SQL查询

• 参数解释
  select_list 查询字段
  table_name 查询表名
  join_type 表连接方式
  join_table_name 连接表名
  join_condition 表连接条件
  where_condition 查询条件
  group_by_list 分组字段列表
  having_condition 分组查询条件
  order_by_condition 排序字段
  limit_number 分页参数
• 手写的sql

SELECT DISTINCT <select_list> FROM <table_name>
<join_type> JOIN <join_table_name> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list> HAVING <having_condition>
ORDER BY <order_by_condition> 
LIMIT <limit_number>

• 机读的sql

FROM <table_name> 
ON <join_condition>
<join_type> JOIN <join_table_name>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition> 
LIMIT <limit_number>

• SQL 解析流程
  

SQL JOIN类型

1. 内联（ inner join）

SELECT <select_list> FROM A INNER JOIN B ON A.Key = b.Key

2. 左联（ left join ）

SELECT <select_list> FROM A LEFT JOIN B ON A.Key = b.Key

3. 右联（ right join ）

SELECT <select_list> FROM A RIGHT JOIN B ON A.Key = b.Key

4. 左联且不包括A与B的交集

SELECT <select_list> FROM A LEFT JOIN B ON A.Key = b.Key WHERE B.Key IS NULL

5. 右联且不包括A与B的交集

SELECT <select_list> FROM A RIGHT JOIN B ON A.Key = b.Key WHERE A.Key IS NULL

6. 全连接（ full outer join ）

SELECT <select_list> FROM A FULL OUTER JOIN B ON A.Key = b.Key

注意：MySQL 不支持该语法，Oracle支持
解决方案：

SELECT <select_list> FROM A LEFT JOIN B ON A.Key = b.Key # 左联
UNION # union 自带去重
SELECT <select_list> FROM A RIGHT JOIN B ON A.Key = b.Key # 右联

7. 全连接（ full outer join ）但不包括A与B的交集

SELECT <select_list> FROM A FULL OUTER JOIN B ON A.Key = b.Key 
WHERE A.Key IS NULL OR B.Key IS NULL

MySQL下：

SELECT <select_list> FROM A LEFT JOIN B ON A.Key = b.Key WHERE B.Key IS NULL # 左联去除交集
UNION # union 自带去重
SELECT <select_list> FROM A RIGHT JOIN B ON A.Key = b.Key WHERE A.Key IS NULL # 右联去除交集

SQL变慢原因

1. 查询语句写的烂
2. 索引失效，查询时未用上索引
3. 关联查询太多join（设计缺陷或不得已的需求）
4. 服务器调优及各个参数配置（缓冲、线程数等）