发布于 由admin

MySQL高级讲座篇之：`GROUP BY`与`HAVING`的执行顺序与优化。

各位观众老爷，大家好！我是你们的老朋友，今天咱们聊聊MySQL里一对“欢喜冤家”——GROUP BY和HAVING。它们啊，一个负责分组，一个负责过滤，听起来很简单，但真正用起来，那可是坑不少。今天咱们就来扒一扒它们背后的故事，搞清楚它们的执行顺序，再学几招优化技巧，保证各位以后写SQL再也不犯迷糊！

一、GROUP BY：分分合合的艺术

首先，咱们说说GROUP BY。这玩意儿啊，就像一个居委会大妈，专门负责把数据按某种规则分成若干组。举个例子，假设我们有一张orders表，记录了用户的订单信息：

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (user_id, order_date, amount) VALUES
(1, '2023-01-01', 100.00),
(1, '2023-01-02', 150.00),
(2, '2023-01-01', 200.00),
(2, '2023-01-03', 250.00),
(3, '2023-01-02', 300.00),
(3, '2023-01-04', 350.00);

现在，我想知道每个用户的订单总金额，就可以用GROUP BY：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

这条SQL的意思是：把orders表按照user_id分组，然后对每个组计算amount的总和，并起个别名叫total_amount。

GROUP BY的注意事项：

  • SELECT列表中的列： SELECT列表中，要么是GROUP BY子句中出现的列，要么就是聚合函数（比如SUM, AVG, COUNT, MAX, MIN）。 否则，MySQL会随机从每个组中选择一个值，这可不是你想要的结果。

    比如，以下SQL就是错误的：

    SELECT user_id, order_date, SUM(amount) AS total_amount -- order_date 不在 GROUP BY 中，也不是聚合函数
FROM orders
GROUP BY user_id;

    MySQL会报错，或者允许执行，但是返回的结果中order_date是不确定的。在开启ONLY_FULL_GROUP_BY SQL模式后会直接报错。

  • NULL值的处理： GROUP BY会将所有NULL值视为同一组。

  • 性能问题： 对大量数据进行GROUP BY操作可能会比较慢，需要注意索引优化。

二、HAVING：分组后的筛选器

HAVING就像一个挑剔的客人，在GROUP BY分组之后，对分组后的结果进行过滤。 注意，HAVING只能用于分组后的结果，不能用于原始数据。

比如，我想找出订单总金额超过300的用户：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 300;

这条SQL的意思是：先按照user_id分组，计算每个用户的订单总金额，然后筛选出总金额大于300的用户。

HAVING与WHERE的区别：

很多人容易把HAVING和WHERE搞混。它们都是用来过滤数据的，但作用的对象不一样：

  • WHERE：用于过滤原始数据，在GROUP BY之前执行。
  • HAVING：用于过滤分组后的数据，在GROUP BY之后执行。

可以用一个表格来总结：

特性 WHERE HAVING
作用对象 原始数据 分组后的数据
执行顺序 在GROUP BY之前 在GROUP BY之后
可以使用的列 表中的所有列 GROUP BY子句中的列，聚合函数
场景 过滤原始数据，例如：查找某个用户的订单 过滤分组后的数据，例如：查找订单总额大于某个值的用户

三、GROUP BY与HAVING的执行顺序

这才是今天的重头戏！ 搞清楚GROUP BY和HAVING的执行顺序，才能写出高效的SQL。

一般来说，它们的执行顺序是这样的：

  1. FROM： 首先确定要查询的表。
  2. WHERE： 根据WHERE子句过滤原始数据。
  3. GROUP BY： 按照GROUP BY子句进行分组。
  4. HAVING： 根据HAVING子句过滤分组后的数据。
  5. SELECT： 选择要查询的列。
  6. ORDER BY： 对结果进行排序。
  7. LIMIT： 限制结果的数量。

可以用一张图来表示：

graph LR
    A[FROM] --> B(WHERE)
    B --> C{GROUP BY}
    C --> D(HAVING)
    D --> E(SELECT)
    E --> F(ORDER BY)
    F --> G(LIMIT)

重要结论：

  • WHERE在GROUP BY之前执行，所以可以用WHERE过滤原始数据，减少GROUP BY的计算量。
  • HAVING在GROUP BY之后执行，所以只能用HAVING过滤分组后的数据。

举个例子：

我想找出订单总金额超过300的用户，并且只考虑2023年的订单。可以这样写：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE YEAR(order_date) = 2023  -- 先用WHERE过滤2023年的订单
GROUP BY user_id
HAVING SUM(amount) > 300;     -- 再用HAVING过滤总金额超过300的用户

四、GROUP BY与HAVING的优化

知道了GROUP BY和HAVING的执行顺序，就可以根据这个顺序来优化SQL。

1. 尽量使用WHERE过滤原始数据

WHERE子句在GROUP BY之前执行，所以可以用WHERE子句过滤掉不需要的数据，减少GROUP BY的计算量。

比如，以下两种写法，第一种效率更高：

-- 效率更高
SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE order_date >= '2023-01-01' AND order_date <= '2023-01-31'
GROUP BY user_id
HAVING SUM(amount) > 300;

-- 效率较低
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING SUM(CASE WHEN order_date >= '2023-01-01' AND order_date <= '2023-01-31' THEN amount ELSE 0 END) > 300;

2. 避免在HAVING中使用复杂的表达式

HAVING子句在GROUP BY之后执行，如果HAVING子句中包含复杂的表达式，会影响性能。 可以考虑将复杂的表达式移到WHERE子句中，或者创建一个计算列。

3. 使用索引

对GROUP BY子句中使用的列创建索引，可以加快分组的速度。

例如，对orders表的user_id列创建索引：

CREATE INDEX idx_user_id ON orders (user_id);

4. 避免不必要的GROUP BY

如果不需要分组，就不要使用GROUP BY。 很多时候，我们可以用其他的SQL语句来替代GROUP BY。

5. 使用临时表

对于复杂的GROUP BY操作，可以考虑使用临时表来存储中间结果，然后再对临时表进行查询。

CREATE TEMPORARY TABLE temp_orders AS
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

SELECT * FROM temp_orders WHERE total_amount > 300;

DROP TEMPORARY TABLE IF EXISTS temp_orders;

6. 优化器提示（Optimizer Hints）

MySQL提供了一些优化器提示，可以用来影响MySQL的执行计划。 但是，使用优化器提示需要谨慎，因为它们可能会导致SQL语句在不同的MySQL版本或不同的数据量下表现不佳。

五、 案例分析：一个更复杂的例子

假设我们有两张表：users表和orders表。

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  city VARCHAR(255)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO users (username, city) VALUES
('Alice', 'Beijing'),
('Bob', 'Shanghai'),
('Charlie', 'Beijing'),
('David', 'Guangzhou');

INSERT INTO orders (user_id, order_date, amount) VALUES
(1, '2023-01-01', 100.00),
(1, '2023-01-02', 150.00),
(2, '2023-01-01', 200.00),
(2, '2023-01-03', 250.00),
(3, '2023-01-02', 300.00),
(3, '2023-01-04', 350.00),
(4, '2023-01-05', 400.00);

现在，我想找出北京用户中，订单总金额超过500的用户，并显示用户名和订单总金额。

SELECT u.username, SUM(o.amount) AS total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.city = 'Beijing'  -- 先用WHERE过滤北京用户
GROUP BY u.username        -- 按照用户名分组
HAVING SUM(o.amount) > 500; -- 再用HAVING过滤总金额超过500的用户

这条SQL的执行顺序是：

  1. FROM：users表和orders表。
  2. JOIN：连接users表和orders表。
  3. WHERE：过滤city为’Beijing’的用户。
  4. GROUP BY：按照username分组。
  5. HAVING：过滤total_amount大于500的组。
  6. SELECT：选择username和total_amount。

优化建议：

  • 对users表的city列创建索引：CREATE INDEX idx_city ON users (city);
  • 对orders表的user_id列创建索引：CREATE INDEX idx_user_id ON orders (user_id);

六、总结

今天咱们聊了GROUP BY和HAVING的执行顺序和优化技巧。 记住，WHERE在GROUP BY之前执行，HAVING在GROUP BY之后执行。 尽量使用WHERE过滤原始数据，避免在HAVING中使用复杂的表达式，并使用索引来优化查询。

希望今天的讲座能帮到大家，以后写SQL的时候，再也不用担心GROUP BY和HAVING的问题了！ 如果大家还有什么问题，欢迎留言讨论！ 咱们下期再见！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注