MySQL高级应用 - 七种JOIN

本文已参与「新人创作礼」活动， 一起开启掘金创作之路。

优化分析

SQL慢

• 查询语句烂 (无索引)

• 索引失效

  • 单值索引

    CREATE INDEX idx_user_name ON user(name)
    

  • 复合索引

• 关联查询太多join (设计缺陷 / 不得已的需求)

• 服务器调优及各个参数设置 (缓冲、线程数等)

常见通用的JOIN查询

• SQL执行顺序

  • 手写

    SELECT DISTINCT # 不同的
    	< 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 > # 不符合ON也添加
    WHERE < where_condition > # 非聚合、非SELECT别名
    GROUP BY < group_by_list > # 改变对表引用
    HAVING < having_condition > # 之作用分组后
    SELECT 
    DISTINCT < select_list >
    ORDER BY < order_by_condition > # 可用SELECT别名
    LIMIT < limit_number > #rows、offset
    

七种JOIN

初始化

• 建表

  CREATE TABLE `tbl_dept` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `deptName` VARCHAR(30) DEFAULT NULL,
     `locAdd` VARCHAR(40) DEFAULT NULL,
     PRIMARY KEY (`id`)
  ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  

  CREATE TABLE `tbl_emp` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(20) DEFAULT NULL,
     `deptId` VARCHAR(11) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `fk_dept_id` (`deptId`)
  ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  

• 初始化数据

1. A ∩ B

SELECT < select_list >
FROM TableA A
INNER JOIN TableB B # 共有
ON A.Key = B.Key

2. A ( = A ∩ B + A )*

  ```mysql
  SELECT < select_list >
  FROM TableA A
  LEFT JOIN TableB B
  ON A.Key = B.Key
  ```

3. B ( = A ∩ B + B )*

  ```mysql
  SELECT < select_list >
  FROM TableA A
  RIGHT JOIN TableB B
  ON A.Key = B.Key
  ```

4. A ( = A - A ∩ B )*

  ```mysql
  SELECT < select_list >
  FROM TableA A
  LEFT JOIN TableB B
  ON A.Key = B.Key # ON时主表保留
  WHERE B.Key IS NULL # 筛选A表数据
  ```

5. B ( = B - A ∩ B )*

  ```mysql
  SELECT < select_list >
  FROM TableA A
  RIGHT JOIN TableB B
  ON A.Key = B.Key
  WHERE A.Key IS NULL
  ```

6. A ∪ B

  ```mysql
  SELECT < select_list >
  FROM TableA A
  FULL OUTER JOIN TableB B ## FULL OUTER 仅oracle支持
  ON A.Key = B.Key
  ```

7. A ∪ B - A ∩ B

  ```mysql
  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
  ```