超大数据量下高效查询多列各自唯一值的优化方案问询

原始数据 #

数据库表db的样本数据如下：

+-------+---------+
| col1  | col2    |
+-------+---------+
|  1    | a       |
|  2    | a       |
|  3    | a       |
|  2    | a       |
|  5    | d       |
|  5    | b       |
+-------+---------+

查询需求 #

需要编写SQL返回两列：column（列名）和value（对应列的唯一值），预期结果如下：

+---------+---------+
| column  | value   |
+---------+---------+
|  col1   | 1       |
|  col1   | 2       |
|  col1   | 3       |
|  col1   | 5       |
|  col2   | a       |
|  col2   | b       |
|  col2   | d       |
+---------+---------+

当前实现及问题 #

目前用以下SQL可以实现需求：

SELECT 'col1' AS column, col1 AS value FROM (SELECT DISTINCT col1 FROM db) t
UNION ALL
    SELECT 'col2' AS column, col2 AS value FROM (SELECT DISTINCT col2 FROM db) t

（注：原语句里DISTINCT(col1)的写法冗余，括号可去掉，改用子查询先去重更高效）

但实际场景中，表有3亿+行、300+列，用大量UNION ALL拼接查询会导致数据库性能骤降——数据库需要处理多个结果集的合并，临时表开销极大。

优化方案 #

针对这种大表多列的场景，推荐采用「拆分查询+客户端合并」的思路，避免在数据库端做大量UNION ALL操作：

方案1：拆分单列查询，在Python/R中合并 #

1. 获取所有列名：通过数据库元数据视图（如MySQL/PostgreSQL的information_schema.columns）获取目标表的所有列名。
2. 循环执行单列去重查询：对每个列单独执行SELECT '列名' AS column, 列名 AS value FROM (SELECT DISTINCT 列名 FROM db) t。每个查询仅处理单个列的去重，数据库可利用列索引（若存在）快速返回唯一值，效率远高于多列UNION ALL。
3. 客户端合并结果：在Python（用pandas）或R（用dplyr）中，将每个查询返回的结果拼接成最终数据集。

举个Python+pandas的示例代码：

import pandas as pd
import sqlalchemy

# 建立数据库连接
engine = sqlalchemy.create_engine("数据库连接字符串")

# 获取目标表的所有列名
cols = pd.read_sql("SELECT column_name FROM information_schema.columns WHERE table_name = 'db' AND table_schema = '你的库名'", engine)
col_list = cols['column_name'].tolist()

# 循环查询每个列的唯一值并合并
result = pd.DataFrame()
for col in col_list:
    sql = f"SELECT '{col}' AS column, {col} AS value FROM (SELECT DISTINCT {col} FROM db) t"
    df = pd.read_sql(sql, engine)
    result = pd.concat([result, df], ignore_index=True)

# 最终结果
print(result)

方案2：数据库层面的辅助优化 #

• 给高频查询列加索引：若列的唯一值查询是高频操作，给每个列建立普通索引，数据库可直接通过索引快速去重，无需扫描全表。
• 利用列式存储特性：如果使用列式数据库（如BigQuery、ClickHouse、Vertica），单列查询性能天然优于行式数据库，拆分查询的效率会更显著。
• 减少数据传输开销：若数据库支持导出功能，可将每个列的唯一值导出到本地文件，再在客户端合并，降低网络传输压力。

方案3：数据库端动态生成查询（适合支持存储过程的数据库） #

若不想在客户端处理，可在数据库中编写存储过程，动态生成每个列的去重查询并批量执行合并。但这种方式仍会在数据库端产生合并开销，效率不如客户端合并。

内容的提问来源于stack exchange，提问作者MLEN