sql连表优化心得

666

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

但是limit 了 结果还准确吗

你这个情况，应该能免去外层 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 位是日期。

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

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

第一个a表全表查询了吧

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

修改后，子查询的范围缩小了，提早进行选择where查询！ :+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