MySQL关联查询

原创于 2025-07-07 15:49:05 发布 · 367 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

数据库专栏收录该内容

5 篇文章

订阅专栏

关联查询是MySQL在优化器阶段，对语法正确的SQL语句进行优化，然后生成执行计划的一个阶段。

关联查询（JOIN）详解

关联查询是数据库中最核心的多表数据检索操作，它通过表之间的关联条件将不同表中的数据组合起来，形成有意义的结果集。

一、关联查询的本质

关联查询（JOIN）是通过连接条件（Join Condition）将多个表中的数据横向合并的过程：

纵向合并：UNION（追加行）
横向合并：JOIN（扩展列）

二、关联查询的五大类型

1. 内连接（INNER JOIN）

SELECT a.*, b.* 
FROM table_a a
INNER JOIN table_b b ON a.key = b.key

特点：只返回两表中匹配成功的记录（交集）

2. 左连接（LEFT JOIN）

SELECT a.*, b.* 
FROM table_a a
LEFT JOIN table_b b ON a.key = b.key

特点：

保留左表全部记录
右表无匹配则填充NULL
实际业务中使用频率最高

3. 右连接（RIGHT JOIN）

SELECT a.*, b.* 
FROM table_a a
RIGHT JOIN table_b b ON a.key = b.key

特点：

保留右表全部记录
左表无匹配则填充NULL
可被LEFT JOIN替代（调换表位置）

4. 全连接（FULL JOIN）

-- MySQL需用UNION模拟
SELECT a.*, b.* FROM a LEFT JOIN b ON a.key = b.key
UNION
SELECT a.*, b.* FROM a RIGHT JOIN b ON a.key = b.key

特点：返回两表所有记录（并集）

5. 交叉连接（CROSS JOIN）

SELECT a.*, b.* 
FROM table_a a
CROSS JOIN table_b b

特点：

无连接条件
产生笛卡尔积（行数 = 表A行数 × 表B行数）
慎用！可能导致结果集爆炸

三、关联条件的形式

1. 等值连接（最常用）

ON a.user_id = b.id

2. 非等值连接

ON a.salary BETWEEN b.min_salary AND b.max_salary

3. 多条件连接

ON a.dept_id = b.dept_id 
AND a.hire_date > b.establish_date

4. 自连接（同一表的不同实例）

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id

四、关联查询的执行原理

1. 嵌套循环（Nested Loop）

外层循环驱动表
内层循环被驱动表
适合索引良好的场景

2. 哈希连接（Hash Join，MySQL 8.0+）

对小表构建哈希表
扫描大表进行哈希匹配
适合大表无索引场景

3. 排序合并（Sort-Merge）

对两表按连接键排序
双指针合并扫描
适合已排序数据

五、性能优化要点

1. 索引策略

-- 确保关联字段有索引
ALTER TABLE orders ADD INDEX idx_customer (customer_id);

2. 驱动表选择

小结果集作为驱动表
使用STRAIGHT_JOIN强制顺序

SELECT STRAIGHT_JOIN a.* FROM small_table a 
JOIN large_table b ON a.id = b.sid

3. 查询改写

-- 将IN子查询改为JOIN
SELECT * FROM products
WHERE id IN (SELECT product_id FROM orders)

-- 优化为：
SELECT p.* FROM products p
JOIN (SELECT DISTINCT product_id FROM orders) o
ON p.id = o.product_id

六、实际业务场景示例

场景1：电商订单查询

SELECT 
  o.order_id,
  u.user_name,
  p.product_name,
  o.quantity
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id
WHERE o.create_time > '2023-01-01'

场景2：部门员工统计

SELECT 
  d.dept_name,
  COUNT(e.emp_id) AS emp_count,
  AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id