sql连表优化心得

梦想星辰大海的个人博客 / 22 / 19 / 创建于 11个月前 / 更新于 11个月前

慢查询的 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 协议》，转载必须注明作者和本文链接

梦想星辰大海

174 声望

划水师 @ 未来科技

暂无个人描述~

《L04 微信小程序从零到发布》

从小程序个人账户申请开始，带你一步步进行开发一个微信小程序，直到提交微信控制台上线发布。

《L03 构架 API 服务器》

你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程，JWT 概念及使用和 API 开发相关的进阶知识。

推荐文章：

更多推荐...

SQL进阶 15 / 4 |

高效办公小技巧——如何批量处理 SQL 文件？ 19 / 11 |

这15个SQL优化技巧，能让你的性能提升10倍 37 / 9 |

laravel-soar(2.x) - 自动监控输出 SQL 优化建议、辅助 laravel 应用 SQL 优化 35 / 42 |

[扩展推荐] laravel-showsql —— 输出 Eloquent Builder SQL 到调试工具中 10 / 1 |

在 laravel 中轻松容易的输出完整的 sql 语句 18 / 23 |

讨论数量: 19

chi-kwongli

3 声望

666

11个月前评论

晏南风

见习助教 149 声望

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

11个月前评论

晏南风

见习助教 149 声望

但是 limit 了结果还准确吗

11个月前评论

梦想星辰大海（楼主）

准确，因为慢sql的本意是通过连表的方式补一个zgzdf_date进来，排序和分页都是针对子查询结果，我在连表前先分页也是一样的。

梦想星辰大海（楼主）

limit准了，但是每页的结果是乱序，所以还得再套一层sql，或者数据拿出来后自己再排一次序

wxf666

68 声望

你这个情况，应该能免去外层 JOIN？前提条件：

zgzdf 能转换成 [-2 ^ 31, 2 ^ 31)
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 位是日期。

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

11个月前评论

梦想星辰大海（楼主）

测试了一波，大佬的思路就是666，学习到了。不过涨跌幅有负数，比如-0.09，而mysql的-1 << 32的结果是18446744069414584320，与预期的-4294967296不一致。

wxf666 （作者）

@梦想星辰大海试试 CAST(转成32位整数(zgzdf) << 32 | zgzdf_date AS SIGNED) ？

guanguans

课程读者 704 声望

{
    "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`"
    ]
}

代码已被折叠，点此展开

11个月前评论

guanguans （作者）

@梦想星辰大海对的，是小米的 soar。我这边写了 laravel 的扩展包(laravel-soar)，可以尝试下。

梦想星辰大海（楼主）

老哥你这个是什么工具，分析的明明白白啊。

晏南风

@梦想星辰大海小米的soar

空山

@guanguans 这个好！

php_yt

1.1k 声望

第一个 a 表全表查询了吧

11个月前评论

梦想星辰大海（楼主）

没有，shareholder_name字段有索引

lovewei

178 声望

扔到 chatgpt，让它帮你优化

11个月前评论

梦想星辰大海（楼主）

这个是OpenAI 的 GPT-3 的优化结果，少了一个where条件，执行耗时在1500毫秒左右。

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

emo猪

0 声望

修改后，子查询的范围缩小了，提早进行选择 where 查询！

11个月前评论

rrazoro

0 声望

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

10个月前评论

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

帮助

划水师 @ 未来科技

私信

文章归档

1 篇 2024 年 11 月 2 篇 2024 年 8 月 1 篇 2024 年 5 月 1 篇 2024 年 4 月 1 篇 2023 年 12 月 3 篇 2023 年 9 月 2 篇 2023 年 8 月 1 篇 2023 年 6 月 1 篇 2023 年 5 月 1 篇 2023 年 3 月 1 篇 2022 年 11 月 1 篇 2022 年 10 月 2 篇 2022 年 9 月 1 篇 2022 年 8 月 1 篇 2022 年 6 月 1 篇 2022 年 5 月 1 篇 2022 年 4 月 1 篇 2020 年 7 月

5个月前解决workerman的gateway的崩溃问题 8个月前 IOS16以下的系统，格式化日期转时间戳错误 8个月前在laravel下实现全双工的websocket开发 11个月前 sql连表优化心得 1年前微秒生成服务

21 在laravel下实现全双工的websocket开发 7 聊天表设计 4 sql连表优化心得 2 请求腾讯服务报错：Operation timed out 2 解决workerman的gateway的崩溃问题

博客标签

成为赞助商