mysql 亿级数据量查询优化 - 优化sql语句

最近负责的项目数据量巨大,单表 50亿 条数据,而与之关联的另外两张表也都是数百万乃至上亿的数据量,这么说吧,之前的代码一个月的数据量查询耗时37秒,当然,我费尽力气将其优化也只能到 2.14 秒,而这还是没有任何查询条件的时候,可想而知,如果再有查询条件会有多慢。

在优化前,我先关闭了 mysql 缓存,即:

set global query_cache_size=0;
set global query_cache_type=0;

最初的 sql,耗时 37秒:

SELECT * 
FROM (
  SELECT a.*, ROW_NUMBER() OVER (ORDER BY physicalDetailItemId DESC) AS rownum
  FROM (
    SELECT p.xxx,xxxx
    FROM physical_detail_item pdi
      LEFT JOIN physical_detail pd ON pd.id = pdi.detail_id
      LEFT JOIN physical p ON p.id = pd.physical_id
    WHERE p.hospital_Id = 'AHSLTJZX'
  ) a
) b
WHERE rownum > (1000 - 1) * 20
  AND rownum <= 1000 * 20;

这里,我省略了 select 部分的字段,性能与其无关。之所以省略,毕竟是公司项目哈。这里的 row_number over 其实 是 sql server 里的,类似 mysql 的 limit,但是 mysql 兼容 row_number  over 而 sql server 却不支持 limit。physical 是体检表,包含用户信息等,可以理解为用户信息和体检信息,physical_detail 可以理解为 体检的项目,而 physical_detail_item 则是 体检的具体结果,你可以假设一个用户 有 10 -20 个体检项,有 1000 - 1500条 体检结果,即 physical 里 一条记录 对应 physical_detail 里的 10 -20 条记录,对应 physical_detail_item 里的 1000 - 1500 条记录。

首先,我将其优化为普通的 mysql sql 语句,如下:

SELECT p.xxx,xxx
FROM physical_detail_item pdi
  LEFT JOIN physical_detail pd ON pd.id = pdi.detail_id
  LEFT JOIN physical p ON p.id = pd.physical_id
WHERE p.hospital_Id = 'AHSLTJZX'
ORDER BY physicalDetailItemId DESC
LIMIT 0, 20;

此时,耗时 13- 18 秒,分页越大越慢,但整体而言,优化了将近一半吧,算是巨大的提升了。

再优化下分页:

SELECT p.xxx,xxx
FROM physical_detail_item pdi
  LEFT JOIN physical_detail pd ON pd.id = pdi.detail_id
  LEFT JOIN physical p ON p.id = pd.physical_id
WHERE p.hospital_Id = 'AHSLTJZX' and pdi.id > (select id from physical_detail_item order by id desc limit 1499979, 1)
ORDER BY physicalDetailItemId DESC
LIMIT 20;

此时,保持在 13 - 14 秒之间,这性能依旧不怎么样,毕竟没有查询,或者说就是个联合查询,除了联合查询的条件外,没有任何额外的条件。

最后,我将语句优化为:

SELECT p.xxx,xxx
FROM physical_detail_item pdi
  LEFT JOIN physical_detail pd ON pd.id = pdi.detail_id
  LEFT JOIN physical p ON p.id = pd.physical_id
WHERE pdi.id IN (
  SELECT t.id
  FROM (
    SELECT id
    FROM physical_detail_item
    ORDER BY id DESC
    LIMIT 1499980, 20
  ) t
)
ORDER BY physicalDetailItemId DESC;

此时,查询只需要 2.14 秒,优化了 7倍 !

现在,我们回过头来研究下,为啥这几条 sql 为啥会有这么大的性能差距,第一二条,很明显是 row_number over 和 limit 的性能问题了,不在本文的讨论之内,而之后是对分页做的优化,按理说,第三条性能最佳,但是第三条没有限定 id 上限,这是败笔,如果第三条同时限制了最大和最小的id,那么性能不应该输给第四条!遗憾的是,这个已经无法再证实了,但是,按我的理解,如果同时限定了 pdi.id 的最小和最大值,性能当最佳。

回过头来,总结下:对于多表关联的查询,最好限定最大的那张表的查找范围,否则性能极差。这里,pdi 这张表最大,如果不限定它的查找范围,就意味着所有的数据都符合该语句的查找,然后返回的就是数百万数据,最后再来个 limit,其性能最差。而如果限定了最大的这张表的查找范围,那么查找的数据可能只有区区几十条而已,这个性能提升显而易见。

下一篇,我将接着讲述,我是如何将这个语句继续优化,做到 10亿 数据关联查询只需要区区几十毫秒。

本博客若无特殊说明则由 full-stack-trip 原创发布
转载请点名出处:全栈之旅 > mysql 亿级数据量查询优化 - 优化sql语句
本文地址:https://www.kpromise.top/mysql-optimize-about-limit/

发表评论

电子邮件地址不会被公开。 必填项已用*标注