解决慢查询的最佳实践

慢查询通常会影响系统的性能和用户体验。为了有效优化这些查询，你需要仔细分析执行计划，评估索引的使用情况，改写 SQL 语句，甚至可能需要调整程序逻辑。

在本篇文章中，我们将介绍解决慢查询的最佳实践。

适用版本

适用于 OceanBase 数据库 V3.x 和 V4.x 版本。

关键概念

• 慢查询：执行时间较长的 SQL 查询。
• 执行计划：数据库为 SQL 语句生成的执行路径。
• 索引：用于加速数据检索的一种数据库对象。
• 分区：将数据库表分为多个部分以提高查询效率。
• 并行执行：同时在多个处理器上运行多个子任务以提高查询效率。
• 分库分表：将单表数据分散到多个数据库中以提升性能。
• 连接次序优化：通过调整多表连接顺序来优化SQL性能。

OceanBase 数据库解决慢查询问题

OceanBase 数据库是一款原生分布式的 HTAP（混合事务/分析处理）数据库，专注于优化大规模查询场景。以下五种技术手段有效解决了慢查询问题。

方式一：通过并行执行

OceanBase 拥有成熟的并行执行能力，可以对普通查询、DDL、DML操作进行并行执行，并能灵活设置并行度。这使得开发者能够通过较少的 CPU 开销大幅提升 SQL 性能。

您可以手动设置并行执行，也可以自动设置并行执行。

• 手动设置并行

  • 使用 HINT /*+ parallel(degree) */ 可以为整个 SQL 语句指定统一的并行度。

  • 或者在创建表时，可以使用如下语法设置表级并行度：

    create table big_table(c1 int) parallel = 32;
    

• 自动设置并行度

  在实际业务场景中，并行资源为多少才合适呢？并行是否开启及并行度大小，根据查询的执行实际情况和业务需求以经验为基础来决定。

  在手动指定并行度时，对会话中本不需要并行加速或无需使用较高 DOP 的查询带来额外的并行执行开销，从而导致性能下降。另一方面，通过 hint 的方式来指定特定查询的并行度，但这需要对每一条业务查询进行单独考量，对于存在大量业务查询的情况，是不可行的。

  为了解决手动指定并行度的不便和限制，查询优化器可以通过 Auto DOP 功能在生成查询计划时评估查询需要执行的时间，自动确定是否开启并行和开启适量的并行度。这样可以避免由于手动指定并行度而导致的性能下降。

  您可以通过 HINT /*+ parallel(auto) */ 或设置系统变量来启用 Auto DOP。启用后，数据库会根据租户配置和表的数据量自动计算合适的并行度，从而省去手动设置并行度的麻烦，并避免固定并行度可能引发的不合理情况。

  用户可以根据实际业务需求调整并行参数 parallel_degree_policy 和 parallel_servers_target，以灵活控制并行度。具体参数说明如下：

  • parallel_degree_policy：用于控制 Auto DOP 的开关。
  • parallel_servers_target：表示租户在每个节点上可以申请的并行执行线程数量。

  此外，您还可以通过以下 SQL 命令开启并设置自动并行度：

  -- 开启并行
  SET GLOBAL parallel_degree_policy = AUTO;
  
  -- 设置基表最大扫描时间，单位为 ms，默认为 1000 ms；此处设置基表最大扫描时间为 100 ms，即基表扫描时间超过 100 ms 时，开启并行执行。
  SET GLOBAL parallel_min_scan_time_threshold = 100;
  

方式二：利用 OceanBase 的原生分布式分区功能

OceanBase 数据库作为原生分布式数据库，其分区是独立的存储、高可用、事务单位，表的不同分区可以分布于不同服务器上，利用多机性能加快大表查询速度。同时，OceanBase 的原生分布式能力使应用程序可以像调用单机数据库一样使用，减少业务改造成本。

分区是把一个大表的数据拆分成多个较小的、独立管理的部分（分区）。MySQL 的分区虽然会在物理文件和查询逻辑两个角度对表进行拆分，但单机库的特性本身决定了拆分后的分区还都会集中在单台机器上。这使得带上分区条件的 SQL 虽然能仅扫描特定的分区而不是整个表，但查询使用的资源仍局限在 MySQL 主的一个节点上。

OceanBase 数据库底层分布式的特性允许不同分区的 Leader 分布在不同的副本上，还能支持部分分区自动负载均衡到新节点上。分布式特性使得 OceanBase 数据库不同分区的负载进一步打散到了多节点上，可以充分发挥多机性能，避免分库分表的改造成本，同时最大程度提升大查询性能。

下面通过一个例子来简单说明一下分区对大查询的性能提升。

示例

• 业务问题：电商系统的优惠券表（5亿行数据）因单表数据量过大，使用 MySQL 时，通过 user_id 查询性能逐渐下降，接近单机性能瓶颈。

• 解决方案：通过 OceanBase 的分区与分布式能力，优化查询性能并支持弹性扩展。通过对优惠券表使用 user_id 字段进行 HASH 分区，将该表拆分成 16 个分区，这样单个分区平均只有 3000 多万行数据，对于优惠券查询有极大性能提升。

示例步骤：

1. 创建分区表：

   • 分区键选择：user_id 是查询条件的核心字段，通过 HASH 分区确保数据均匀分布。
   • 分区数量：16 个分区可将单分区数据量控制在约 3000 万行（5亿 ÷ 16 ≈ 3125 万），减少单次查询的数据扫描范围。

   -- 创建按 user_id 字段进行 HASH 分区的优惠券表，拆分为 16 个分区
   CREATE TABLE coupon (
    coupon_id BIGINT,
    user_id BIGINT,
    coupon_code VARCHAR(50),
    expire_time DATETIME,
    PRIMARY KEY (coupon_id, user_id)      -- 主键包含分区键 user_id
   )
   PARTITION BY HASH(user_id)
   PARTITIONS 16;
   

2. 查询优化示例：

   • 分区裁剪（Partition Pruning）：系统根据 user_id=123 的条件，自动定位到对应的分区（如 user_id % 16 = n 的分区），仅扫描该分区数据，避免全表扫描。
   • 分布式查询：查询请求会被路由到对应分区的 Leader 节点，而 Leader 可能分布在不同物理节点上，减少单节点压力。

   -- 查询用户 user_id=123 的优惠券（带分区键条件）
   SELECT * FROM coupon WHERE user_id = 123;
   

此外，在零售系统常见的大促和活动场景，还可以使用到 OceanBase 数据库的分布式扩展能力，增加节点以自动负载均衡。

方式三：绑定执行计划

对于查询条件过滤性较差的 SQL，可以通过绑定执行计划来临时优化性能。然而，如果该 SQL 长期存在性能问题，建议对其逻辑进行修改，以确保长期效果。

绑定执行计划

绑定执行计划将特定查询与其最优执行计划固定关联，适用于优化不足的查询。通过将某一具体 SQL 查询和其最优执行计划绑定，数据库在后续执行中直接使用该预设计划，从而降低优化开销，提升性能。

需要注意的是，绑定执行计划并非总是有效。如果数据分布或表结构发生变化，原有的绑定计划可能变得低效甚至导致查询性能下降。因此，建议定期审查绑定的执行计划，并根据需要进行更新或解绑。

• 上线前：可在 SQL 语句中添加 Hint，控制优化器按照 Hint 指定的行为进行计划生成。

• 已上线业务：如果优化器选择的计划效果不佳，可以通过绑定计划改善，而无需修改 SQL。这可以通过 DDL 操作将一组 Hint 加入到 SQL 中，使优化器生成更优计划，这组 Hint 称为 Outline。

  • 使用 SQL_TEXT 创建 Outline 的语法：

    CREATE [OR REPLACE] OUTLINE <outline_name> ON <stmt> [ TO <target_stmt> ];
    

  • 使用 SQL_ID 创建 Outline 的语法：

    CREATE OUTLINE outline_name ON sql_id USING HINT hint_text;
    

方式四：OceanBase 修改 SQL 逻辑机制，用户无感知

OceanBase 数据库通过一系列优化机制，显著提升查询性能，用户在使用过程中几乎无感知。

• 多表连接场景：对于多表连接的场景，OceanBase 实现了一套完备的连接枚举算法，能够灵活基于代价调整连接次序。可以高效处理内连接、外连接、反连接及半连接，甚至允许转换连接类型。

• 子查询场景：OceanBase 数据库查询改写模块提供了多种子查询优化策略，针对嵌套层次不深的子查询，能够将其转换为连接，并采取不同的连接算法进行优化。这使得子查询的执行效率得到了显著提升，确保整体查询性能的优化。

• 大表聚合场景：大表聚合是一个典型的性能优化场景，OceanBase具备预聚合优化能力，能够将大表数据拆分成多个组进行聚合处理，再进行最终汇总。结合并行执行，预聚合可以极大地提升执行效率，通常能提高数倍的性能。

方式五：利用列式存储实现大查询加速，优化即时分析

在业务中，尤其是涉及海量数据的复杂查询场景中，即席查询（Ad hoc 查询）是一个典型的性能挑战。以下是一个常见的例子：电子商务公司的后台运营人员在数据后台通过条件筛选（如客户名、下单时间、商品名等）生成动态 SQL 并下发到数据库。这些查询的特点是筛选条件不确定，无法有效利用传统的索引，往往导致全表扫描。

在数据量较小时，传统数据库如 MySQL 可能还能勉强应对，但随着数据量增加，这类全表扫描的查询往往需要几十秒甚至数分钟，严重影响响应速度和用户体验。为了解决这个问题，通常会采用 ETL（抽取、转换、加载）将数据同步到实时数仓，以满足复杂查询的需求。

OceanBase 提供了一种替代解决方案：列式存储。通过列式存储加速查询，OceanBase 可直接在数据库内高效处理大规模分析查询，简化架构并降低成本。以下是其特点和优势：

列式存储的特点

• 数据按列存储：与传统的行式存储不同，列式存储将同一列的数据物理上排列在一起。
• 按需读取列：查询时仅需扫描涉及的列数据，避免全表扫描，显著降低 IO 和内存消耗。
• 高压缩比：列式存储通常对相同类型的数据进行高效压缩，进一步减少存储和传输开销。

列式存储的优势

• 查询效率大幅提升：在分析场景中，查询只需扫描相关列，而不必加载整行数据。例如，即席查询通常只需读取部分列，通过列式存储可以快速过滤和聚合数据，响应时间大幅缩短。

• 架构简化：OceanBase 支持同时处理 TP（事务处理）和 AP（分析处理）负载，无需额外的 ETL 和外部数仓。相比传统的 MySQL + 实时数仓架构，OceanBase 将所有功能整合于一体，简化了系统部署和运维。

• 成本优化：通过减少中间环节（如 ETL 和外部存储），OceanBase 帮助企业节省硬件和软件成本，通常可降低整体成本约 30%。

如何启用列式存储

在 OceanBase 数据库中，您可以通过以下步骤启用列式存储：

1. 创建列式存储表：在建表时指定存储格式为列式：

   CREATE TABLE orders (
       order_id BIGINT,
       customer_name VARCHAR(100),
       product_name VARCHAR(100),
       order_time TIMESTAMP,
       amount DECIMAL(10,2)
   ) WITH COLUMN GROUP (each column);
   

2. 查询优化： OceanBase 的优化器会自动选择适合的存储和执行方式，无需额外调整即可获得性能收益。

3. 监控和调整：使用 OceanBase 的监控工具分析查询性能，并根据需求调整表设计或存储策略。

通过列式存储，OceanBase 实现了高效的数据筛选与聚合，无需外部数仓即可处理复杂的即席查询，极大地简化了数据架构并提升查询性能。这种方法尤其适用于需要快速响应的大数据查询场景，为企业提供了经济高效的解决方案。

实践总结

通过开启并行执行、利用 OceanBase 的原生分布式分区功能等实际操作，以及无感知的多表连接次序优化、子查询优化、大表聚合优化、TP 和 AP 隔离等技术，OceanBase 数据库能够有效解决 MySQL 慢查询问题。

相关文档

• 如果您想了解更多关于 OceanBase 的性能调优技术架构，请参考性能调优概述以及它的子章节。
• 如果您想了解更多关于 OceanBase 的性能调优的使用方法，请参考性能调优以及它的子章节。