【SQL技术】不同数据库引擎 SQL 优化方案剖析

一、引言

在数据处理和分析的世界里，SQL 是不可或缺的工具。不同的数据库系统，如 MySQL、PostgreSQL（PG）、Doris 和 Hive，在架构和性能特点上存在差异，因此针对它们的 SQL 优化策略也各有不同。这些数据库中常见和不常见 SQL 语句的优化方法，涵盖排序、聚合函数、条件查询、分组等操作，同时还会涉及 Hive 和 Doris 中 UDF 函数的优化。

SQL解析器：

二、MySQL 优化策略

存储引擎介绍

• InnoDB：支持事务、行级锁、外键约束，具有良好的并发性能和数据一致性，是MySQL 5.5及以后版本的默认存储引擎。
• MyISAM：不支持事务和外键约束，具有较高的插入和查询速度，适用于以读为主的应用场景。
• Memory：数据存储在内存中，访问速度极快，但数据在服务器重启时会丢失，适用于临时数据存储。
• Archive：用于存储大量的历史数据，只支持插入和查询操作，不支持更新和删除。
• CSV：以CSV格式存储数据，适用于数据交换和导入导出。
• BlackHole：所有写入的数据都会被丢弃，适用于测试和数据过滤。

SQL执行流程：

（一）条件查询优化

• 索引使用：确保在经常用于WHERE子句的列上创建索引。例如，如果经常根据user_id进行查询：

  CREATE INDEX idx_user_id ON users (user_id);

• 避免函数索引：在WHERE子句中避免对索引列使用函数，因为这会导致索引失效。例如，以下查询会使索引失效：

  SELECT * FROM users WHERE YEAR(created_at) = 2024;

可以改为：

SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

（二）排序优化

• 覆盖索引：如果排序的列和查询的列可以使用同一个索引，即覆盖索引，能显著提高排序性能。例如：

  CREATE INDEX idx_name_age ON users (name, age);
  SELECT name, age FROM users ORDER BY name, age;

• 控制排序数据量：尽量在WHERE子句中过滤掉不必要的数据，减少排序的数据量。

（三）聚合函数优化

• 分组索引：在GROUP BY列上创建索引，有助于提高分组聚合的性能。例如：

  CREATE INDEX idx_dept_salary ON employees (department_id, salary);
  SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

三、PostgreSQL 优化策略

存储引擎介绍

• Heap存储引擎：是PostgreSQL的默认存储引擎，支持MVCC（多版本并发控制），适用于大多数应用场景。
• B-Tree存储引擎：适用于需要高效的范围查询和排序的场景，如索引和排序操作。
• Hash存储引擎：适用于需要快速查找的场景，如哈希表和索引。
• GiST存储引擎：适用于处理几何数据和全文搜索的场景。
• SP-GiST存储引擎：是GiST的一种变体，适用于处理空间数据的场景。
• GIN存储引擎：适用于处理数组和JSON数据的场景。
• BRIN存储引擎：适用于处理大数据集的场景，如数据仓库和日志分析。
• Bitmap存储引擎：适用于处理位图数据的场景。
• Partial存储引擎：适用于处理部分索引的场景。
• Unique存储引擎：适用于处理唯一约束的场景。

SQL执行流程：

（一）条件查询优化

• 统计信息更新：定期更新表的统计信息，确保查询优化器能够做出更准确的查询计划。使用ANALYZE命令：

  ANALYZE users;

• 范围查询优化：对于范围查询，使用BRIN（块范围索引）可以提高性能。例如：

  CREATE INDEX idx_created_at ON users USING BRIN (created_at);

（二）排序优化

• 并行排序：PostgreSQL 支持并行排序，可以通过调整max_parallel_workers_per_gather参数来启用并行排序。

  SET max_parallel_workers_per_gather = 4;

（三）聚合函数优化

• 聚合索引：与 MySQL 类似，在GROUP BY列上创建索引可以提高聚合性能。同时，使用GROUPING SETS、ROLLUP和CUBE等高级聚合功能时，要确保数据分布均匀。

四、Doris 优化策略

Doris SQL引擎种类

• Heap存储引擎：是Doris的默认存储引擎，支持MVCC（多版本并发控制），适用于大多数应用场景。
• B-Tree存储引擎：适用于需要高效的范围查询和排序的场景，如索引和排序操作。
• Hash存储引擎：适用于需要快速查找的场景，如哈希表和索引。
• GiST存储引擎：适用于处理几何数据和全文搜索的场景。
• SP-GiST存储引擎：是GiST的一种变体，适用于处理空间数据的场景。
• GIN存储引擎：适用于处理数组和JSON数据的场景。
• BRIN存储引擎：适用于处理大数据集的场景，如数据仓库和日志分析。
• Bitmap存储引擎：适用于处理位图数据的场景。
• Partial存储引擎：适用于处理部分索引的场景。
• Unique存储引擎：适用于处理唯一约束的场景。

SQL执行流程：

（一）条件查询优化

• 分区和分桶：合理使用分区和分桶可以减少数据扫描量。例如，按日期分区，按用户 ID 分桶：

  CREATE TABLE sales (
  sale_date DATE,
  user_id INT,
  amount DECIMAL(10, 2)
  )
  PARTITION BY RANGE(sale_date) (
  PARTITION p202401 VALUES LESS THAN (‘2024-02-01’),
  …
  )
  DISTRIBUTED BY HASH(user_id) BUCKETS 32;

• 物化视图：对于频繁查询的复杂子查询，可以创建物化视图来提高查询性能。

  CREATE MATERIALIZED VIEW mv_sales_summary AS
  SELECT sale_date, SUM(amount) FROM sales GROUP BY sale_date;

（二）排序优化

• 预排序：在创建表时指定预排序键，Doris 会按照预排序键对数据进行排序存储，提高排序查询的性能。

  CREATE TABLE orders (
  order_date DATE,
  order_id INT,
  amount DECIMAL(10, 2)
  )
  ORDER BY order_date;

（三）UDF 函数优化

• 减少 UDF 调用次数：尽量将 UDF 函数的逻辑合并到 SQL 语句中，减少 UDF 调用的开销。
• 使用向量化 UDF：Doris 支持向量化 UDF，使用向量化 UDF 可以显著提高计算性能。

五、Hive 优化策略

Hive SQL引擎种类

• MapReduce引擎：这是Hive最初使用的执行引擎，它将SQL查询转换为一系列的MapReduce任务来执行。这种方式在处理大规模数据时非常有效，但由于MapReduce的启动开销较大，对于小数据量或实时性要求高的查询可能效率较低。
• Tez引擎：Tez是一种基于有向无环图（DAG）的执行引擎，它可以将多个MapReduce任务组合成一个更高效的执行计划，减少了任务的启动和调度开销，提高了查询性能。Tez特别适合处理复杂的查询和数据挖掘任务。
• Spark引擎：Spark是一种快速的、通用的大数据处理引擎，它提供了比MapReduce更高效的内存计算能力。Hive可以使用Spark作为执行引擎，通过将SQL查询转换为Spark任务来执行，从而提高查询的执行速度。
• Flink引擎：Flink是一种流处理和批处理统一的计算引擎，它提供了高效的分布式计算能力和低延迟的处理能力。Hive可以使用Flink作为执行引擎，通过将SQL查询转换为Flink任务来执行，从而提高查询的执行速度和实时性。

SQL执行流程：

（一）条件查询优化

• 分区裁剪：在WHERE子句中使用分区列进行过滤，避免全量数据扫描。例如：

  SELECT * FROM logs WHERE dt = ‘2024-01-01’;

• 谓词下推：启用谓词下推功能，让 Hive 在数据读取阶段就过滤掉不必要的数据。

  SET hive.optimize.ppd=true;

（二）排序优化

• 使用DISTRIBUTE BY和SORT BY：DISTRIBUTE BY用于将数据分发到不同的 reducer，SORT BY用于在每个 reducer 内部进行排序。例如：

  SELECT * FROM sales DISTRIBUTE BY user_id SORT BY amount DESC;

（三）聚合函数优化

• Map 端聚合：启用 Map 端聚合可以减少数据传输量。

  SET hive.map.aggr=true;

（四）UDF 函数优化

• 缓存中间结果：如果 UDF 函数的计算结果可以复用，在 UDF 内部实现缓存机制，避免重复计算。
• 使用 Hive 内置函数替代 UDF：优先使用 Hive 内置函数，因为它们经过了优化，性能通常比自定义 UDF 高。

六、SQL调优技巧

（一）慎重使用COUNT(DISTINCT col)

• 问题原因：在各个数据库中，DISTINCT操作会将所有数据保存在内存中以进行去重操作，在数据量较大时，这可能导致内存溢出（OOM）情况的发生。
• 解决方案：
  • MySQL、PG：考虑使用GROUP BY和COUNT组合来代替COUNT(DISTINCT col)。例如，如果要统计某列的不同值数量，可以通过SELECT col, COUNT(*) FROM table GROUP BY col，然后在应用层进行进一步处理得到去重后的数量。
  • Doris、Hive：除了GROUP BY替代法，还可以使用ROW_NUMBER() OVER(PARTITION BY col)函数。例如在Hive中，SELECT COUNT(*) FROM (SELECT col, ROW_NUMBER() OVER(PARTITION BY col ORDER BY col) AS row_num FROM table) WHERE row_num = 1。这种方式通过窗口函数为每个不同值分配一个序号，然后只计算序号为1的记录数量。

（二）小文件问题

• 问题原因：小文件会在数据库存储和查询过程中占用过多内存，因为每个小文件都需要一定的元数据管理开销，从而导致查询效率下降。
• 解决方案：
  • Hive：
    • 控制小文件产生数量，可以通过调整写入数据时的参数来实现。例如，在使用INSERT语句写入数据时，可以调整mapreduce.output.fileoutputformat.split.maxsize和mapreduce.output.fileoutputformat.split.minsize参数，使数据写入更大的文件块中。
    • 使用SequenceFile格式存储数据，这种格式对于小文件处理有较好的优化效果。例如，在创建表时指定STORED AS SEQUENCEFILE。
    • 减少Reducer数量，避免动态分区生成过多小文件。可以通过设置hive.exec.reducers.max参数来限制Reducer的最大数量。
  • Doris：在数据导入阶段，可以通过调整导入参数来控制小文件的生成。例如，设置合适的批处理大小，确保每次导入的数据量足够大，避免生成过多小文件。

（三）慎重使用SELECT *

• 问题原因：查询所有字段意味着数据库需要处理可能存在的无效数据，这无疑是对资源的浪费，特别是在大表查询时，会增加不必要的I/O开销。
• 解决方案：无论是MySQL、PG还是Doris、Hive(*一般限制数量，不走mr会稍快)，都应该指定所需字段名进行查询。例如，将SELECT * FROM table改为SELECT col1, col2, col3 FROM table，只获取实际需要的列数据。

（四）不要在表关联后加WHERE条件

• 解决方案：
  • MySQL、PG、Doris、Hive通用：采用谓词下推技术，将过滤条件尽可能放在表连接之前，提前过滤数据，减少中间结果集的数据量，从而减少数据传输和处理的开销。例如，将SELECT * FROM table1 JOIN table2 ON table1.id = table2.id WHERE table1.col > 10改为SELECT * FROM (SELECT * FROM table1 WHERE table1.col > 10) AS sub_table1 JOIN table2 ON sub_table1.id = table2.id。

（五）处理空值数据

• 问题原因：空值在数据库处理过程中可能会导致一些问题，如在MapReduce过程（特别是在Hive等基于MapReduce架构的数据库中）中的内存不足。
• 解决方案：
  • MySQL、PG：在查询时过滤掉NULL数据，可以使用IS NOT NULL或IS NULL条件进行筛选。例如，SELECT col FROM table WHERE col IS NOT NULL。
  • Doris、Hive：除了过滤空值，还可以为空值赋随机数（在某些特定场景下，如避免数据倾斜）。在Hive中，可以使用COALESCE函数来处理空值，例如SELECT COALESCE(col, 0) FROM table，这里如果col列为空，则将其替换为0。

（六）设置并行执行任务数

• 解决方案：
  • Hive：通过设置hive.exec.parallel为true开启并发执行，增加并行度。这在有多个子查询或者多个任务可以同时进行的情况下非常有效，可以提高整体查询效率。

（七）设置合理的Reducer数量

• 问题原因：过多的Reducer启动会消耗大量的时间和资源，因为每个Reducer都需要初始化和分配资源。
• 解决方案：
  • Hive：根据输入数据量和每个Reducer处理的数据量设置合理的

七、总结

不同的数据库系统有其独特的架构和性能特点，因此 SQL 优化策略也需要因地制宜。在实际应用中，需要根据具体的业务需求和数据特点，选择合适的优化方法。同时，定期监控数据库的性能指标，不断调整优化策略，才能确保数据库系统始终保持高效稳定的运行。希望本文介绍的优化方案能为你在数据库性能优化方面提供一些有益的参考。