sql连表优化心得

慢查询的sql:

select
    `a` .*,
    `b`.`zgzdf_date` as `max_rate_date`
from
    (
    select
        `stockcode`,
        `stockname`,
        `shareholder_name`,
        max(zgzdf) as max_rate
    from
        `shareholder_battle_fund`
    where
        `shareholder_name` = '香港中央结算有限公司'
        and `stocktype` = 1
    group by
        `stockcode` 
    ) as a
inner join `shareholder_battle_fund` as `b` on
    `a`.`stockcode` = `b`.`stockcode`
    and `a`.`max_rate` = `b`.`zgzdf`
    and `b`.`shareholder_name` = `a`.`shareholder_name`
order by
        `max_rate` desc
limit 20 OFFSET 10

这条sql的本意是:按股票代码分组,取最大的涨跌幅,并将最大涨跌幅所在的日期一并取出,然后按涨跌幅排序进行分页。

优化思路就一条,缩减join表的大小:

优化后的sql:

select
    *
from
    (
    select
        `a` .*,
        `b`.`zgzdf_date` as `max_rate_date`
    from
        (
        select
            `stockcode`,
            `stockname`,
            `shareholder_name`,
            max(zgzdf) as max_rate
        from
            `shareholder_battle_fund`
        where
            `shareholder_name` = '香港中央结算有限公司'
            and `stocktype` = 1
        group by
            `stockcode`
        order by
            `max_rate` desc
        limit 20 OFFSET 10
    ) as a
    inner join `shareholder_battle_fund` as `b` on
        `a`.`stockcode` = `b`.`stockcode`
        and `a`.`max_rate` = `b`.`zgzdf`
    where
        `b`.`shareholder_name` = '香港中央结算有限公司') as c
order by
    `c`.`max_rate` desc

优化后的sql中,a表被缩减至20条数据,b表被where条件缩减掉一批数据。执行耗时由原来的2200毫秒,缩减至149毫秒。

sql
本作品采用《CC 协议》,转载必须注明作者和本文链接
梦想星辰大海
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
讨论数量: 19

我最近也遇到这种,老系统上来就是left join ,还有分组排序好多,慢的一批

1周前 评论

但是limit 了 结果还准确吗

1周前 评论
梦想星辰大海 (楼主) 1周前
梦想星辰大海 (楼主) 1周前

你这个情况,应该能免去外层 JOIN?前提条件:

  1. zgzdf 能转换成 [-2 ^ 31, 2 ^ 31)
  2. zgzdf_date 是 32 位整数时间戳

大致是这样?

select
    `stockcode`,
    `stockname`,
    `shareholder_name`,
    max(转成32位整数(zgzdf) << 32 | zgzdf_date) as max_rate_and_date
from
    `shareholder_battle_fund`
where
    `shareholder_name` = '香港中央结算有限公司'
    and `stocktype` = 1
group by
    `stockcode`
order by
    `max_rate_and_date` desc
limit 20 OFFSET 10 

程序拿到数据后,高 32 位是转换后的涨跌幅(转回来就好),低 32 位是日期。

连表开销,应该远大于这点运算量吧。。

1周前 评论
梦想星辰大海 (楼主) 1周前
wxf666 (作者) 1周前
{
    "ID": "58EE1E0153507DC4",
    "Fingerprint": "select `a` *, `b`.`zgzdf_date` as `max_rate_date` from ( select `stockcode`, `stockname`, `shareholder_name`, max(zgzdf) as max_rate from `shareholder_battle_fund` where `shareholder_name` = ? and `stocktype` = ? group by `stockcode` ) as a inner join `shareholder_battle_fund` as `b` on `a`.`stockcode` = `b`.`stockcode` and `a`.`max_rate` = `b`.`zgzdf` and `b`.`shareholder_name` = `a`.`shareholder_name` order by `max_rate` desc limit ? offset ?",
    "Score": 10,
    "Sample": "select\n    `a` .*,\n    `b`.`zgzdf_date` as `max_rate_date`\nfrom\n    (\n    select\n        `stockcode`,\n        `stockname`,\n        `shareholder_name`,\n        max(zgzdf) as max_rate\n    from\n        `shareholder_battle_fund`\n    where\n        `shareholder_name` = '香港中央结算有限公司'\n        and `stocktype` = 1\n    group by\n        `stockcode`\n    ) as a\ninner join `shareholder_battle_fund` as `b` on\n    `a`.`stockcode` = `b`.`stockcode`\n    and `a`.`max_rate` = `b`.`zgzdf`\n    and `b`.`shareholder_name` = `a`.`shareholder_name`\norder by\n        `max_rate` desc\nlimit 20 OFFSET 10",
    "Explain": null,
    "HeuristicRules": [
        {
            "Item": "CLA.008",
            "Severity": "L2",
            "Summary": "请为 GROUP BY 显示添加 ORDER BY 条件",
            "Content": "默认 MySQL 会对 'GROUP BY col1, col2, ...' 请求按如下顺序排序 'ORDER BY col1, col2, ...'。如果 GROUP BY 语句不指定 ORDER BY 条件会导致无谓的排序产生,如果不需要排序建议添加 'ORDER BY NULL'。",
            "Case": "select c1,c2,c3 from t1 where c1='foo' group by c2",
            "Position": 0
        },
        {
            "Item": "CLA.009",
            "Severity": "L2",
            "Summary": "ORDER BY 的条件为表达式",
            "Content": "当 ORDER BY 条件为表达式或函数时会使用到临时表,如果在未指定 WHERE 或 WHERE 条件返回的结果集较大时性能会很差。",
            "Case": "select description from film where title ='ACADEMY DINOSAUR' order by length-language_id;",
            "Position": 0
        },
        {
            "Item": "JOI.002",
            "Severity": "L4",
            "Summary": "同一张表被连接两次",
            "Content": "相同的表在 FROM 子句中至少出现两次,可以简化为对该表的单次访问。",
            "Case": "select tb1.col from (tb1, tb2) join tb2 on tb1.id=tb.id where tb1.id=1",
            "Position": 0
        },
        {
            "Item": "RES.001",
            "Severity": "L4",
            "Summary": "非确定性的 GROUP BY",
            "Content": "SQL返回的列既不在聚合函数中也不是 GROUP BY 表达式的列中,因此这些值的结果将是非确定性的。如:select a, b, c from tbl where foo=\"bar\" group by a,该 SQL 返回的结果就是不确定的。",
            "Case": "select c1,c2,c3 from t1 where c2='foo' group by c2",
            "Position": 0
        },
        {
            "Item": "SUB.001",
            "Severity": "L4",
            "Summary": "MySQL 对子查询的优化效果不佳",
            "Content": "MySQL 将外部查询中的每一行作为依赖子查询执行子查询。 这是导致严重性能问题的常见原因。这可能会在 MySQL 5.6 版本中得到改善, 但对于5.1及更早版本, 建议将该类查询分别重写为 JOIN 或 LEFT OUTER JOIN。",
            "Case": "select col1,col2,col3 from table1 where col2 in(select col from table2)",
            "Position": 0
        },
        {
            "Item": "SUB.006",
            "Severity": "L2",
            "Summary": "不建议在子查询中使用函数",
            "Content": "MySQL将外部查询中的每一行作为依赖子查询执行子查询,如果在子查询中使用函数,即使是semi-join也很难进行高效的查询。可以将子查询重写为OUTER JOIN语句并用连接条件对数据进行过滤。",
            "Case": "SELECT * FROM staff WHERE name IN (SELECT max(NAME) FROM customer)",
            "Position": 0
        }
    ],
    "IndexRules": null,
    "Tables": [
        "`information_schema`.`shareholder_battle_fund`"
    ]
}
1周前 评论
guanguans (作者) 1周前
梦想星辰大海 (楼主) 1周前
晏南风 1周前
空山 1周前

第一个a表全表查询了吧

1周前 评论
梦想星辰大海 (楼主) 1周前

扔到chatgpt,让它帮你优化 :smile:

1周前 评论
梦想星辰大海 (楼主) 1周前

修改后,子查询的范围缩小了,提早进行选择where查询! :+1:

1周前 评论
select
    sbf.stockcode,
    sbf.stockname,
    sbf.shareholder_name,
    SUBSTRING_INDEX(GROUP_CONCAT(sbf.zgzdf order by sbf.zgzdf DESC), ',', 1) as max_rate,
    SUBSTRING_INDEX(GROUP_CONCAT(sbf.zgzdf_date order by sbf.zgzdf DESC), ',', 1) as max_rate_date
FROM
    shareholder_battle_fund sbf
WHERE
    shareholder_name  =  '香港中央结算有限公司'  and  stocktype  =  1
GROUP BY
    sbf.stockcode
ORDER BY
    sbf.max_rate desc
1天前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!