从实际项目看MySQL EXISTS的应用与性能优势

背景：

工作中，我要根据业务要求查询 “合适”的人员，合适人员的条件在另外两个表中，就是得根据另外两个表进行一些条件的限制。一开始我使用了连表查询，连三个表，但是这样做性能有点慢（虽然我们这是定时器任务，对速度没有那么大的要求，但是本着精益求精的想法，还是优化一下），就问了一下ai，对于这种 过滤 的场景，有没有什么手段

在这个过程中了解到了 mysql的 exists关键字，这里记录一下exists关键字

目录

1. 博客定位
   • Who（谁）
   • What（什么）
   • When（何时）
   • Where（何地）
   • Why（为什么）
2. 创建示例表并造数据
   • 创建表结构
   • 造数据SQL
   • 预期数据结果
3. 使用EXISTS查询
   • 场景1：使用exists 查询符合条件 （查询有订单的客户）
   • 场景2：使用 not exists 查询 不符合条件的内容（查询没有订单的客户）
4. EXISTS与IN的性能对比
   • 使用EXISTS查询
   • 使用IN查询
   • 性能对比分析
5. EXISTS的性能优化
   • 场景3：优化查询性能
6. 总结

1. 博客定位

Who（谁）

本篇博客适合数据库开发者、软件工程师以及对MySQL优化有兴趣的技术人员。特别是那些正在面对复杂查询和性能优化问题的开发者，本文会通过具体示例展示如何使用EXISTS来提升查询效率。

What（什么）

EXISTS是MySQL中用于判断子查询是否存在结果的关键字，常用于关联查询、子查询等场景。本文将详细介绍EXISTS的基本用法、与IN的区别，给出实际项目中的最佳实践，并演示如何通过EXISTS优化查询性能。

When（何时）

在面对以下情况时，你应该考虑使用EXISTS：

• 需要判断某个条件是否存在，且不关心实际返回的数据内容。
• 子查询数据量大，或者查询需要高效判断某个数据是否存在。
• 想要避免全表扫描或内存溢出等性能问题时。

Where（何地）

本博客中的示例和技术适用于任何MySQL数据库，尤其是在需要优化查询性能和减少内存占用时，EXISTS能够显著提升效率。

Why（为什么）

MySQL中的EXISTS在处理复杂查询时，能够提供比IN更优的性能，尤其是在子查询结果集较大时。通过具体的SQL示例和步骤，你将能够在实际项目中高效地应用EXISTS来提升查询速度和数据库性能。

2. 创建示例表并造数据

建表SQL

-- 创建 customers 表
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL
);

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

造数据SQL

-- 插入 customers 数据
INSERT INTO customers (customer_name) VALUES 
('张三'), 
('李四'), 
('王五'), 
('李6');

-- 插入 orders 数据
INSERT INTO orders (customer_id, order_date, amount) VALUES 
(1, '2023-01-01', 500.00),
(1, '2023-03-15', 1500.00),
(2, '2023-04-20', 200.00),
(3, '2023-02-10', 1200.00),
(4, '2023-07-22', 800.00);

预期数据结果

customers 表：

orders 表：

3. 使用EXISTS查询

场景1：查询有订单的客户

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

预期结果

所有客户都至少有一个订单，因此结果包括了所有客户。

场景2：查询没有订单的客户

SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

预期结果

这些是没有下过任何订单的客户。

4. EXISTS与IN的性能对比

场景1：使用EXISTS查询

SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

场景2：使用IN查询

SELECT customer_id, customer_name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
);

性能对比分析

• EXISTS会在找到第一个匹配的结果时就停止查询，而IN会将整个子查询结果加载到内存中并进行比较。
• 对于IN来说，子查询结果集较大时，可能导致内存溢出或查询效率下降。
• 而EXISTS通常能提供更高的性能，特别是在大数据量查询时。

5. EXISTS的性能优化

场景3：优化查询性能

假设你有一个非常大的orders表，你需要查询那些订单金额超过1000的客户。使用EXISTS能够有效避免全表扫描并优化查询效率。

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.amount > 1000
);

预期结果

只有这些客户的订单金额超过了1000。