重生之豆包帮我优化sql

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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;

优化说明

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

注意事项

  • 确保相关列上有合适的索引，例如 tbl_activity 表的 activity_name 列、sys_user 表的 user_id 列等，以加快过滤操作的速度。
  • 可以使用 EXPLAIN 命令分析优化后的 SQL 执行计划，查看是否按照预期的方式进行连接和过滤操作。
通过以上优化，利用小表驱动大表的原则，可以进一步降低查询延时，提升 SQL 的执行性能。
posted @   最萌小胡胡  阅读(5)  评论(0)  编辑  收藏  举报
相关博文：
· oceanbase2.2集群——MySQL租户的使用体验
· OceanBase基本语法（MySQL租户）
· MySQL之慢查询sql排查及优化
· 【实战必备】MySQL慢查询优化实战，让你的SQL飞起来！
· MySQL慢查询之慢 SQL 定位、日志分析与优化方案
阅读排行：
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署
点击右上角即可分享
微信分享提示