MySQL复杂SQL性能优化实战：多表联查与子查询的高效方法

一、多表联查（JOIN Operations）

多表联查是通过 JOIN 操作将多个表中的数据组合起来，基于表之间的关联关系进行查询。

（一）连接的类型（JOIN Types）

1. INNER JOIN（内连接/等值连接）

   • 作用：返回两个表中连接字段值相等的所有行组合。

   • 语法：

     SELECT 列名列表
     FROM 表1
     [INNER] JOIN 表2 ON 表1.关联字段 = 表2.关联字段
     [WHERE 条件];

   • 示例：查询所有有订单的客户信息（假设 customers 表有 customer_id，orders 表有 customer_id 外键）。

     SELECT c.customer_id, c.name, o.order_id, o.order_date
     FROM customers c
     INNER JOIN orders o ON c.customer_id = o.customer_id;

2. LEFT [OUTER] JOIN（左外连接）

   • 作用：返回左表的所有行，即使在右表中没有匹配的行。对于左表中存在而右表中没有匹配的行，右表相关的列将显示为 NULL。

   • 语法：

     SELECT 列名列表
     FROM 表1
     LEFT [OUTER] JOIN 表2 ON 表1.关联字段 = 表2.关联字段
     [WHERE 条件];

   • 示例：查询所有客户及其订单（包括没有下过单的客户）

     SELECT c.customer_id, c.name, o.order_id, o.order_date
     FROM customers c
     LEFT JOIN orders o ON c.customer_id = o.customer_id;

3. RIGHT [OUTER] JOIN（右外连接）

   • 作用：返回右表的所有行，即使在左表中没有匹配的行。对于右表中存在而左表中没有匹配的行，左表相关的列将显示为 NULL。

   • 语法：

     SELECT 列名列表
     FROM 表1
     RIGHT [OUTER] JOIN 表2 ON 表1.关联字段 = 表2.关联字段
     [WHERE 条件];

   • 示例：查询所有订单及其对应的客户信息（包括那些可能关联到无效客户的订单）。

     SELECT c.customer_id, c.name, o.order_id, o.order_date
     FROM customers c
     RIGHT JOIN orders o ON c.customer_id = o.customer_id;

4. FULL [OUTER] JOIN（全外连接）

   • 作用：返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表相关的列将显示为 NULL。如果两个表中有匹配的行，则进行连接。

   • 语法（MySQL 不支持直接的 FULL OUTER JOIN，需用 UNION 模拟）：

     SELECT 列名列表
     FROM 表1
     LEFT JOIN 表2 ON 表1.关联字段 = 表2.关联字段
     UNION [ALL]
     SELECT 列名列表
     FROM 表1
     RIGHT JOIN 表2 ON 表1.关联字段 = 表2.关联字段
     WHERE 表1.关联字段 IS NULL;

   • 示例：查询所有客户和所有订单（包括没有订单的客户和没有对应客户的订单）

     SELECT c.customer_id, c.name, o.order_id, o.order_date
     FROM customers c
     LEFT JOIN orders o ON c.customer_id = o.customer_id
     UNION
     SELECT c.customer_id, c.name, o.order_id, o.order_date
     FROM customers c
     RIGHT JOIN orders o ON c.customer_id = o.customer_id
     WHERE c.customer_id IS NULL;

5. CROSS JOIN（交叉连接/笛卡尔积）

   • 作用：返回两个表中所有可能的行组合。结果集的行数是 表1行数 * 表2行数。

   • 语法：

     SELECT 列名列表
     FROM 表1
     CROSS JOIN 表2;

   • 示例：生成所有产品和所有尺寸的组合。

     SELECT p.product_name, s.size_name
     FROM products p
     CROSS JOIN sizes s;

（二）多表连接（Joining More Than Two Tables）

可以连续使用多个 JOIN 子句连接多个表。

• 语法：

  SELECT ...
  FROM 表1
  JOIN 表2 ON 条件
  JOIN 表3 ON 条件
  ...
  [WHERE ...];

• 示例：查询订单的详细信息（客户名、订单日期、产品名、数量）。

  SELECT c.name, o.order_date, p.product_name, od.quantity
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  JOIN order_details od ON o.order_id = od.order_id
  JOIN products p ON od.product_id = p.product_id;

（三）自连接（Self Join）

自连接是将表与其自身连接，常用于表示层次结构（如员工-经理关系、类别-父类别）。

• 技巧：需要使用表别名（Alias）来区分同一个表的两个“实例”。

• 示例：查询员工及其经理的名字。

  SELECT e1.employee_name AS Employee, e2.employee_name AS Manager
  FROM employees e1
  LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

（四）自然连接（NATURAL JOIN）和 USING 子句

• NATURAL JOIN：自动连接所有同名列。强烈不推荐使用，因为它依赖于列名匹配，不明确且容易出错。

  SELECT ... FROM table1 NATURAL JOIN table2;

• USING 子句：当连接的两个表具有完全相同名称的关联字段时，可以用 USING 简化 ON。

  SELECT c.customer_id, c.name, o.order_id, o.order_date
  FROM customers c
  JOIN orders o USING (customer_id);

二、子查询（Subqueries）

子查询是嵌套在另一个 SQL 查询（主查询）内部的查询，子查询的结果被外部查询使用。

（一）子查询的位置（Where Subqueries Can Be Used）

• SELECT 子句（标量子查询）

• FROM 子句（派生表/内联视图）

• WHERE 子句（最常用）

• HAVING 子句

• INSERT / UPDATE / DELETE 语句的 VALUES 或 SET 部分

（二）子查询的主要类型

1. 标量子查询（Scalar Subquery）

   • 特点：返回单个值（一行一列）。

   • 用途：可以出现在任何期望单个值的地方（如 SELECT 列表、WHERE 条件中的比较运算符右侧）。

   • 示例：查询价格高于平均价格的产品。

     SELECT product_name, price
     FROM products
     WHERE price > (SELECT AVG(price) FROM products);

2. 列子查询（Column Subquery）

   • 特点：返回单列多行。

   • 用途：常与 IN、ANY/SOME、ALL 运算符一起用在 WHERE 或 HAVING 子句中。

   • 示例（IN）：查询至少订购过一次“Coffee”产品的客户。

     SELECT customer_id, name
     FROM customers
     WHERE customer_id IN (
           SELECT DISTINCT o.customer_id
           FROM orders o
           JOIN order_details od ON o.order_id = od.order_id
           JOIN products p ON od.product_id = p.product_id
           WHERE p.product_name = 'Coffee'
     );

3. 行子查询（Row Subquery）

   • 特点：返回单行多列。

   • 用途：与行比较运算符一起使用