MySQL DISTINCT 介绍与使用 – wiki基地

深入理解 MySQL DISTINCT 关键字：去重原理、使用方法与性能优化

在数据库查询中，我们经常需要从表中检索数据。然而，原始数据往往包含重复的记录或组合，而我们很多时候只关心其中独一无二的值或行。这时，DISTINCT 关键字就成为了 MySQL（以及其他SQL数据库）中一个不可或缺的工具。它能够帮助我们轻松地去除查询结果集中的重复项，呈现干净、精炼的数据视图。

本文将对 MySQL 的 DISTINCT 关键字进行深入探讨，从其基本概念出发，详细介绍其语法、各种使用场景、与其他相关概念（如 GROUP BY）的区别与联系，并重点分析其性能影响及相应的优化策略。

1. DISTINCT 是什么？核心概念解析

DISTINCT 关键字用于从查询结果集中排除重复的行。当你在 SELECT 语句中使用 DISTINCT 时，MySQL 会对返回的所有行进行评估，只保留那些完全唯一的行，而丢弃所有重复的副本。

这里的关键点在于，“重复的行”是基于你 SELECT 语句中 所有被选定的列的组合值 来判断的。换句话说，如果两行在所有你指定要查询的列上的值都完全相同，那么它们就被认为是重复的，DISTINCT 会只保留其中的一行。

为什么需要 DISTINCT？

现实世界的数据往往不是完美无缺的。数据库中可能存在以下情况导致重复：

数据录入错误： 同一条信息被多次录入。
业务逻辑： 某个实体（例如用户）可能与另一个实体（例如订单）有多条关联记录，但在某些查询中，你只关心用户的列表，不希望用户出现多次。
JOIN 操作： 当通过 JOIN 连接多个表时，如果关联条件不是一对一的，或者连接的表中存在一对多甚至多对多的关系，结果集中很可能会出现重复的行。例如，查询“所有购买过产品的顾客”，如果一个顾客购买了多种产品，简单的 JOIN 可能会让该顾客的名字在结果中出现多次。

在这些场景下，DISTINCT 提供了一种简单直接的方式来获得去重后的结果集，使得数据分析和展示更加准确和有意义。

2. DISTINCT 的基本语法与使用

DISTINCT 关键字直接放在 SELECT 关键字之后，紧跟着你想要应用的列名列表。

基本语法：

sql SELECT DISTINCT column1, column2, ... FROM table_name WHERE condition;

示例 1：去除单列重复值

假设我们有一个 customers 表，其中包含 customer_id, name, city, state 等列。我们想知道我们的客户分布在哪些不同的城市。

“`sql
— 原始数据可能包含多个客户在同一个城市
SELECT city FROM customers;

— 使用 DISTINCT 去除重复城市
SELECT DISTINCT city FROM customers;
“`

在这个例子中，DISTINCT city 会返回一个列表，其中每个城市名都只出现一次，即使 customers 表中有成百上千的客户住在同一个城市。

示例 2：去除多列组合重复值

现在，我们想知道客户分布在哪些不同的“城市-州”组合。

“`sql
— 原始数据可能包含相同的城市，但州不同，或者相同的城市-州组合出现多次
SELECT city, state FROM customers;

— 使用 DISTINCT 去除重复的城市-州组合
SELECT DISTINCT city, state FROM customers;
“`

在这里，DISTINCT city, state 会考虑 city 和 state 两列的组合。只有当两行记录的 city 值和 state 值都完全相同时，它们才被视为重复。例如，”New York, NY” 和 “New York, London” 不会被视为重复，而两个 “Los Angeles, CA” 会被视为重复，DISTINCT 只保留一个。

重要注意事项：

DISTINCT 修饰的是 SELECT 语句中所有后续列的组合，而不是单独的某一列。你不能写成 SELECT DISTINCT column1, column2, DISTINCT column3 这样的形式。DISTINCT 只能出现一次，且作用于整个 SELECT 列表。
DISTINCT 会处理 NULL 值。在 DISTINCT 的上下文中，所有 NULL 值都被认为是相同的。因此，如果某一列或多列组合中存在多个 NULL 值，DISTINCT 最终只会保留一个代表 NULL 的结果行。

3. DISTINCT 与其他 SQL 构造的结合使用

DISTINCT 关键字可以与 WHERE, JOIN, ORDER BY, LIMIT 等子句一起使用。

与 WHERE 子句结合：

WHERE 子句用于过滤原始数据，DISTINCT 则在过滤后的结果集上执行去重。WHERE 子句先于 DISTINCT 执行。

sql -- 查找加州有哪些不同的城市 SELECT DISTINCT city FROM customers WHERE state = 'CA';

与 JOIN 操作结合：

这是 DISTINCT 非常常见的应用场景。如前所述，JOIN 操作经常会产生重复行。DISTINCT 可以用来清理 JOIN 的结果。

假设我们有一个 orders 表 (order_id, customer_id, order_date) 和 customers 表 (customer_id, name). 我们想列出所有下过订单的客户姓名。

“`sql
— 简单的 JOIN 可能导致客户姓名重复，如果一个客户下了多个订单
SELECT c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

— 使用 DISTINCT 去除重复的客户姓名
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
“`

与 ORDER BY 子句结合：

ORDER BY 子句用于对最终结果集进行排序。当与 DISTINCT 一起使用时，排序发生在去重之后。你可以根据 SELECT 列表中包含的任何列进行排序。

“`sql
— 获取不同的城市，并按城市名称字母顺序排序
SELECT DISTINCT city
FROM customers
ORDER BY city;

— 获取不同的城市-州组合，并按州、城市排序
SELECT DISTINCT city, state
FROM customers
ORDER BY state, city;
“`

与 LIMIT 子句结合：

LIMIT 子句用于限制返回的行数。它应用于 DISTINCT 处理后的结果集。

sql -- 获取前 10 个不同的城市 SELECT DISTINCT city FROM customers LIMIT 10;

4. DISTINCT 与聚合函数

DISTINCT 关键字也可以用在聚合函数内部，最常见的用法是 COUNT(DISTINCT column_name)。

COUNT(DISTINCT column_name)：

这个用法非常强大，它可以计算指定列中 非 NULL 的唯一值 的数量。

“`sql
— 计算共有多少个不同的城市
SELECT COUNT(DISTINCT city) FROM customers;

— 计算共有多少个不同的城市-州组合 (注意：这里 DISTINCT 不能直接用于 COUNT 的多个参数，需要变通)
— 方法 1: 如果组合列可以唯一标识，直接 COUNT DISTINCT on one column if unique
— 例如，如果city+state 组合可以唯一标识地点
— SELECT COUNT(DISTINCT CONCAT(city, ‘,’, state)) FROM customers; — 这种方式处理 NULL 和分隔符需要小心
— 更常见且标准的方法是先 DISTINCT 组合，再 COUNT 总行数
SELECT COUNT(*)
FROM (SELECT DISTINCT city, state FROM customers) AS distinct_locations;
“`

与其他聚合函数结合 (SUM, AVG, MIN, MAX)：

DISTINCT 也可以用于 SUM, AVG, MIN, MAX 等聚合函数。它意味着在进行聚合计算之前，先对指定的列值进行去重。这个用法相对较少见，但有时非常有用。

“`sql
— 假设一个订单表 orders 包含 order_id, product_id, price
— 可能同一个 product_id 出现在多个订单中，价格有时会浮动
— 如果想计算所有独特产品价格的总和（忽略同一产品在不同订单中的重复价格）
SELECT SUM(DISTINCT price) FROM order_items;

— 计算独特价格的平均值
SELECT AVG(DISTINCT price) FROM order_items;
“`

重要区别：

SELECT DISTINCT column1, column2, ... 是对整行进行去重。
COUNT(DISTINCT column_name) 是对 单个列 的值进行去重后再计数。
SUM(DISTINCT column_name), AVG(DISTINCT column_name) 等是对 单个列 的值去重后再进行聚合计算。

注意 COUNT(DISTINCT column1, column2) 这样的语法在标准的 SQL 中是无效的，但在某些数据库系统（如 PostgreSQL）中是支持的。MySQL 的 COUNT(DISTINCT ...) 只接受一个参数（可以是列名或表达式）。如果需要对多列组合进行计数，通常需要使用子查询或者 GROUP BY 后再计数，如上面计算不同城市-州组合数量的例子所示。

5. DISTINCT 与 GROUP BY 的区别与联系

DISTINCT 和 GROUP BY 有时可以达到相同的去重效果，但这仅仅是巧合，它们的本质目的和工作原理是不同的。理解它们的区别对于编写高效且正确的查询至关重要。

DISTINCT 的目的： 从最终的结果集中移除完全重复的行。它关注的是输出结果的唯一性。
GROUP BY 的目的： 将具有相同值的行分组到一起，通常用于对每个组应用聚合函数（如 COUNT, SUM, AVG）。它关注的是将数据按照某个或某些列进行划分，并对每个分组进行处理。

场景一：仅仅是获取某一列的唯一值

sql SELECT DISTINCT city FROM customers;

与

sql SELECT city FROM customers GROUP BY city;

在这类简单的场景下，两者都能达到获取唯一城市列表的目的。从执行计划上看，MySQL 优化器通常会将 SELECT DISTINCT col FROM table 转化为 SELECT col FROM table GROUP BY col 来执行，因此它们的内部实现可能非常相似。

场景二：获取多列组合的唯一值

sql SELECT DISTINCT city, state FROM customers;

与

sql SELECT city, state FROM customers GROUP BY city, state;

同样，在这类只进行去重而没有使用其他聚合函数的场景下，两者也能达到相同的目的，且内部执行方式可能也非常相似（都是对指定的列进行排序或哈希，然后找出唯一组合）。

场景三：使用聚合函数

这是 DISTINCT 和 GROUP BY 根本区别体现的地方。GROUP BY 主要就是为聚合函数服务的。

例如，我们要统计每个城市有多少客户：

sql -- 必须使用 GROUP BY SELECT city, COUNT(*) FROM customers GROUP BY city;

使用 DISTINCT 无法完成这个任务，因为 DISTINCT 只是去重，它不会对行进行分组后进行计数。

总结：

当你只想简单地移除结果集中的重复行时，使用 DISTINCT 更直观、更清晰。
当你需要根据某些列对数据进行分组，并对每个分组执行聚合计算时，必须使用 GROUP BY。
在只进行去重且没有聚合函数的情况下，SELECT DISTINCT col1, col2... 和 SELECT col1, col2... FROM table GROUP BY col1, col2... 的结果集通常是相同的，且执行计划可能也很相似。选择哪个更多取决于个人偏好和查询的意图清晰度。通常认为 DISTINCT 更明确地表达了“我只想要不重复的行”。

6. DISTINCT 的性能影响与优化

DISTINCT 操作往往是查询中的一个性能瓶颈，尤其是在处理大量数据时。理解其工作原理有助于我们进行优化。

工作原理对性能的影响：

为了确定哪些行是重复的，数据库系统需要对结果集进行比较。这个比较过程通常依赖于两种主要策略：

排序 (Sorting): 将所有要进行 DISTINCT 操作的列的值进行排序。在排序后的数据中，相同的行会紧密相邻，数据库可以轻松地遍历排序结果，只保留每组连续相同行的第一条。
哈希 (Hashing): 构建一个哈希表（或散列表）。数据库遍历每一行，计算要进行 DISTINCT 操作的列的哈希值。如果哈希表中已经存在这个哈希值（并且经过详细比较确认行确实相同），则该行被认为是重复的并丢弃；否则，将该行的哈希值和行数据（或关键部分）添加到哈希表中。

无论采用哪种策略，DISTINCT 都需要额外的计算资源：

内存/磁盘I/O： 如果结果集很大，无法完全放入内存，数据库会使用磁盘上的临时文件进行排序（filesort）或哈希，这会导致大量的磁盘I/O操作，显著降低性能。
CPU： 排序和哈希计算本身也需要消耗 CPU 资源。
临时表： MySQL अक्सर 需要创建一个内部临时表来存储中间结果，尤其是在 DISTINCT 涉及到的列没有合适的索引，或者需要同时处理 ORDER BY 和 DISTINCT 时。

你可以使用 EXPLAIN 命令来查看 MySQL 如何执行包含 DISTINCT 的查询。注意观察输出中的 Extra 列，如果出现 Using temporary 或 Using filesort，通常意味着 DISTINCT 操作正在耗费额外的资源。

sql EXPLAIN SELECT DISTINCT city, state FROM customers WHERE country = 'USA';

优化策略：

针对 DISTINCT 可能带来的性能问题，可以考虑以下优化策略：

只选择必要的列： DISTINCT 对所有指定的列进行评估。选择的列越多，进行比较的数据量越大，排序或哈希的成本越高。确保 SELECT 列表中只包含你确实需要去重的列。
提前过滤数据： 在执行 DISTINCT 之前，尽可能使用 WHERE 子句过滤掉不相关的行。数据量越小，DISTINCT 操作的处理量就越小。
考虑索引： 虽然 DISTINCT 操作本身可能需要 filesort 或临时表，但如果 DISTINCT 所涉及的列上有索引，特别是复合索引，有时可以帮助 MySQL 更快地定位和处理数据，甚至在某些情况下避免完全的 filesort。例如，如果 SELECT DISTINCT city, state FROM customers，并且在 (city, state) 或 (state, city) 上有索引，MySQL 可能能利用索引来更有效地找到唯一组合。然而，这并非总是能避免临时表或排序。
使用 GROUP BY 作为替代（如果适用）： 如前所述，在某些纯粹去重的场景下，GROUP BY 和 DISTINCT 效果相同。有时 GROUP BY 的执行计划在特定情况下可能略优，但这需要通过 EXPLAIN 进行比较。更重要的是，如果你后续还需要对分组进行聚合，那么 GROUP BY 是唯一选择。
考虑业务逻辑变通： 有时业务需求可以通过不同的查询方式来实现，避免直接对大结果集进行 DISTINCT。例如，如果只是想检查某个值是否存在于某个列的唯一集合中，可以使用 EXISTS 或 IN 子查询，这通常比先获取所有唯一值再检查更高效。
增加系统资源： 如果优化查询本身困难，而性能瓶颈确实在 DISTINCT 需要的排序或临时表上，考虑增加数据库服务器的内存（减少磁盘 I/O）或提升磁盘性能（使用 SSD）。
分析大文本/Blob 列： 对 TEXT 或 BLOB 列进行 DISTINCT 操作可能非常耗费资源，因为需要比较大量的数据。在某些老版本或特定配置下，甚至可能不支持直接对这类大对象列进行 DISTINCT。如果必须对这些列去重，考虑是否可以对这些列的某种摘要（如哈希值）进行去重，或者重新设计数据模型。

7. 总结

MySQL 的 DISTINCT 关键字是一个强大且常用的工具，用于从查询结果集中去除重复的行。它基于 SELECT 语句中所有指定列的组合值来判断唯一性。我们可以将其与 WHERE, JOIN, ORDER BY, LIMIT 等子句结合使用，并且它在聚合函数（如 COUNT(DISTINCT)）内部有特殊的应用。

理解 DISTINCT 和 GROUP BY 的区别至关重要：DISTINCT 专注于结果行的唯一性，而 GROUP BY 专注于数据分组和聚合。在简单的去重场景下，它们可能达到相同的效果，但在涉及聚合时，GROUP BY 是必需的。

需要注意的是，DISTINCT 操作由于其需要对结果集进行排序或哈希的内在机制，可能会消耗大量的计算资源和 I/O，尤其是在处理大型数据集时。因此，在使用 DISTINCT 时，应始终关注其性能影响。通过只选择必要的列、提前过滤数据、考虑索引以及使用 EXPLAIN 分析查询计划，我们可以有效地优化包含 DISTINCT 的查询，确保数据库性能。

掌握 DISTINCT 的正确使用方法和潜在的性能陷阱，能够帮助我们编写出更高效、更准确的 SQL 查询，更好地利用数据库中的数据。