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亿 数据关联查询只需要区区几十毫秒。