重生之豆包帮我优化sql

今天早上系统突然发生卡顿，图片加载和操作超级慢，重启几次服务后依然无效。前台工程师排查日志，发现是数据库sql查询超时。
在数据库监控平台，找到报错的TopSQL。接下来就是见证豆包奇迹的时刻：

你是一个资深数据库专家，如果你遇到一条oceanbase里mysql模式下的慢sql如下，

SELECT
  a .visit_id,
  a .activity_id,
  CASE
    WHEN LEFT(cust_id, ?) = ? THEN ?
    WHEN LEFT(cust_id, ?) = ? THEN ?
    WHEN LEFT(cust_id, ?) = ? THEN ?
  END AS cust_id,
  a .cust_name,
  a .cust_idno,
  a .cust_mobile,
  a .visit_enroll,
  a .visit_remark,
  a .sale_no,
  d.branch_name,
  c .sale_name,
  DATE_FORMAT(a .visit_time, '%Y-%m-%d %H:%i:%s') AS visit_time,
  a .visit_addr,
  a .check_stat,
  a .user_id,
  a .check_time,
  DATE_FORMAT(ifnull(a .visit_task, ?), '%Y-%m-%d') AS is_task,
  ifnull(cust_gift, ?) AS cust_gift,
  ifnull(cust_goods, ?) AS cust_goods
FROM
  pdm_visit a,
  tbl_activity b,
  sale_tbl c,
  branch d
WHERE
  a .activity_id = b.activity_id
  AND a .sale_no = c .sale_no
  AND c .sale_channel = ?
  AND c .branch_no = d.branch_no
  AND c .branch_no IN (
    SELECT
      a .sale_branch_no
    FROM
      sale_branch a,
      sys_user b
    WHERE
      a .branch_no = b.branch_no
      AND b.user_id = ?
  )
  AND DATE_FORMAT(a .visit_time, '%Y-%m-%d') = ?
  AND activity_name = ?
ORDER BY
  check_stat ASC

已知该sql里，check_stat是一个只有0、1、2三种值的离散值，该列没有索引，pdm_visit表有300w数据，sale_tbl有55w数据，其他两个表数据量小于1000，pdm_visit表里有联合索引(visit_id,activity_id,sale_no,visit_time)，sale_tbl有联合索引(sale_no,sale_channel,branch_no)，你该从哪方面进行排查与优化，降低该sql的查询延时，给出具体的优化措施。

================================喝一杯茶的功夫，豆包就帮我想好了===================================================

为了排查并优化这条 OceanBase 中 MySQL 模式下的慢 SQL，降低查询延时，我们可以从多个方面进行分析和处理，以下是具体的排查与优化措施：

1. 索引优化

虽然已有部分联合索引，但可能无法完全满足查询需求，我们可以根据查询条件和排序条件创建更合适的索引。

为 `pdm_visit` 表创建索引

由于查询中使用了 DATE_FORMAT(a.visit_time, '%Y-%m-%d') = ? 进行过滤，而这个函数会使索引失效，我们可以尝试将日期比较改为范围查询，并创建一个包含 visit_time 的索引。

-- 创建索引
CREATE INDEX idx_pdm_visit_visit_time ON pdm_visit(visit_time);

为 `sale_tbl` 表创建索引

根据子查询和连接条件，sale_tbl 表的索引基本满足需求，但可以考虑添加一个覆盖索引，以减少回表操作。

-- 创建覆盖索引
CREATE INDEX idx_sale_tbl_all ON sale_tbl(sale_no, sale_channel, branch_no, sale_name);

2. 查询优化

避免函数索引失效

DATE_FORMAT(a.visit_time, '%Y-%m-%d') = ? 会使 visit_time 上的索引失效，我们可以将其改为范围查询。假设查询日期为 2025-02-19，则可以改写为：

 

a.visit_time >= '2025-02-19 00:00:00' AND a.visit_time < '2025-02-20 00:00:00'

使用显式 JOIN 语法

将隐式 JOIN 改为显式 JOIN 语法，提高查询的可读性和可维护性。

子查询优化

子查询可以考虑改为连接查询，避免子查询带来的额外开销。

以下是优化后的 SQL：

SELECT
    a.visit_id,
    a.activity_id,
    CASE
        WHEN LEFT(a.cust_id, ?) = ? THEN ?
        WHEN LEFT(a.cust_id, ?) = ? THEN ?
        WHEN LEFT(a.cust_id, ?) = ? THEN ?
    END AS cust_id,
    a.cust_name,
    a.cust_idno,
    a.cust_mobile,
    a.visit_enroll,
    a.visit_remark,
    a.sale_no,
    d.branch_name,
    c.sale_name,
    DATE_FORMAT(a.visit_time, '%Y-%m-%d %H:%i:%s') AS visit_time,
    a.visit_addr,
    a.check_stat,
    a.user_id,
    a.check_time,
    DATE_FORMAT(IFNULL(a.visit_task, ?), '%Y-%m-%d') AS is_task,
    IFNULL(a.cust_gift, ?) AS cust_gift,
    IFNULL(a.cust_goods, ?) AS cust_goods
FROM
    pdm_visit a
JOIN tbl_activity b ON a.activity_id = b.activity_id
JOIN sale_tbl c ON a.sale_no = c.sale_no
JOIN branch d ON c.branch_no = d.branch_no
JOIN (
    SELECT
        a.sale_branch_no
    FROM
        sale_branch a
    JOIN sys_user b ON a.branch_no = b.branch_no
    WHERE
        b.user_id = ?
) sub ON c.branch_no = sub.sale_branch_no
WHERE
    c.sale_channel = ?
    AND a.visit_time >= ? AND a.visit_time < DATE_ADD(?, INTERVAL 1 DAY)
    AND b.activity_name = ?
ORDER BY
    a.check_stat ASC;

3. 排序优化

由于 check_stat 列没有索引，排序操作可能会导致性能问题。可以考虑为 check_stat 列创建索引，但由于该列只有 0、1、2 三种离散值，索引效果可能不明显。如果数据量较大，可以考虑在应用层进行排序，减少数据库的负担。

4. 统计信息更新

确保数据库的统计信息是最新的，这样查询优化器才能生成更优的执行计划。可以使用以下命令更新统计信息：

ANALYZE TABLE pdm_visit;
ANALYZE TABLE tbl_activity;
ANALYZE TABLE sale_tbl;
ANALYZE TABLE branch;
ANALYZE TABLE sale_branch;
ANALYZE TABLE sys_user;

5. 执行计划分析

使用 EXPLAIN 关键字分析优化后的 SQL 执行计划，查看是否使用了预期的索引，以及是否存在全表扫描等性能瓶颈。

EXPLAIN SELECT ...;

通过以上优化措施，可以有效降低该 SQL 的查询延时，提高查询性能。

==========================接着在问一把====================================

刚才我说，pdm_visit表有300w数据，sale_tbl有55w数据，其他两个表数据量小于1000，那么根据小表来驱动大表，有什么地方是刚才优化过后的sql可以再改进的吗？

在 SQL 查询优化中，“小表驱动大表” 是一个重要的原则，它能减少中间结果集的大小，从而提升查询性能。根据你提供的各表数据量，tbl_activity、branch、sale_branch 和 sys_user 表数据量小于 1000，属于小表；pdm_visit 表有 300w 数据，sale_tbl 有 55w 数据，属于大表。下面针对之前优化后的 SQL 进行进一步改进：

优化思路

调整 JOIN 顺序，让小表先进行连接操作，然后再与大表连接，这样可以减少中间结果集的行数。
提前过滤小表的数据，减少后续连接操作的数据量。

优化后的 SQL

-- 子查询提前过滤 sys_user 和 sale_branch 表的数据
WITH filtered_branches AS (
    SELECT
        a.sale_branch_no
    FROM
        sale_branch a
    JOIN sys_user b ON a.branch_no = b.branch_no
    WHERE
        b.user_id = ?
),
-- 提前过滤 tbl_activity 表的数据
filtered_activities AS (
    SELECT
        activity_id
    FROM
        tbl_activity
    WHERE
        activity_name = ?
)
SELECT
    a.visit_id,
    a.activity_id,
    CASE
        WHEN LEFT(a.cust_id, ?) = ? THEN ?
        WHEN LEFT(a.cust_id, ?) = ? THEN ?
        WHEN LEFT(a.cust_id, ?) = ? THEN ?
    END AS cust_id,
    a.cust_name,
    a.cust_idno,
    a.cust_mobile,
    a.visit_enroll,
    a.visit_remark,
    a.sale_no,
    d.branch_name,
    c.sale_name,
    DATE_FORMAT(a.visit_time, '%Y-%m-%d %H:%i:%s') AS visit_time,
    a.visit_addr,
    a.check_stat,
    a.user_id,
    a.check_time,
    DATE_FORMAT(IFNULL(a.visit_task, ?), '%Y-%m-%d') AS is_task,
    IFNULL(a.cust_gift, ?) AS cust_gift,
    IFNULL(a.cust_goods, ?) AS cust_goods
FROM
    -- 小表 filtered_activities 先参与连接
    filtered_activities fa
JOIN pdm_visit a ON fa.activity_id = a.activity_id
-- 小表 filtered_branches 先参与连接
JOIN sale_tbl c ON a.sale_no = c.sale_no
JOIN filtered_branches fb ON c.branch_no = fb.sale_branch_no
JOIN branch d ON c.branch_no = d.branch_no
WHERE
    c.sale_channel = ?
    AND a.visit_time >= ? AND a.visit_time < DATE_ADD(?, INTERVAL 1 DAY)
ORDER BY
    a.check_stat ASC;

优化说明

使用 CTE（公共表表达式）提前过滤小表数据：
- filtered_branches：通过 sys_user 和 sale_branch 表的连接操作，提前过滤出满足 user_id 条件的 sale_branch_no。
- filtered_activities：提前过滤出满足 activity_name 条件的 activity_id。
调整 JOIN 顺序：
- 让经过过滤的小表 filtered_activities 和 filtered_branches 先参与连接操作，然后再与大表 pdm_visit 和 sale_tbl 连接，这样可以减少中间结果集的行数，提高查询性能。

注意事项

确保相关列上有合适的索引，例如 tbl_activity 表的 activity_name 列、sys_user 表的 user_id 列等，以加快过滤操作的速度。
可以使用 EXPLAIN 命令分析优化后的 SQL 执行计划，查看是否按照预期的方式进行连接和过滤操作。

通过以上优化，利用小表驱动大表的原则，可以进一步降低查询延时，提升 SQL 的执行性能。

posted @ 2025-02-19 16:00 最萌小胡胡阅读(71) 评论(0) 收藏举报

刷新页面返回顶部

最萌小胡胡