阿里云首页

SQL 优化方法

本文档介绍 PolarDB-X 中 SQL 优化的原则和不同类型 SQL 的优化方法。文档中涉及到的基本概念和指令,详情请参见SQL 优化基本概念

SQL 优化的基本原则

在 PolarDB-X 中,可由 RDS MySQL 执行的 SQL 计算称为可下推计算。可下推计算能够减少数据传输,减少网络层和 PolarDB-X 层的开销,提升 SQL 语句的执行效率。

因此,PolarDB-X SQL 语句优化的基本原则为:尽量让更多的计算可下推到 RDS MySQL 上执行。

可下推计算主要包括:

  • JOIN 连接;
  • 过滤条件,如 WHERE 或 HAVING 中的条件;
  • 聚合计算,如 COUNT,GROUP BY 等;
  • 排序,如 ORDER BY;
  • 去重,如 DISTINCT;
  • 函数计算,如 NOW() 函数等;
  • 子查询。

注意:上述列表只是列出可下推计算的各种可能形式,并不代表所有的子句/条件或者子句/条件的组合一定是可下推计算。

不同类型和条件的 SQL 优化有不同的侧重点和方法,下面将针对以下几种情况介绍 SQL 优化的具体方法:

单表 SQL 优化

单表 SQL 优化有以下几个原则:

  • SQL 语句尽可能带有拆分键;
  • 拆分键的条件尽可能是等值条件;
  • 如果拆分键的条件是 IN 条件,则 IN 后面的值的数目应尽可能少(需要远少于分片数,并且数目不会随业务的增长而增多);
  • 如果 SQL 语句不带有拆分键,那么 DISTINCT、GROUP BY 和 ORDER BY 在同一个 SQL 语句中尽量只出现一种。

过滤条件优化

PolarDB-X 的数据是按拆分键水平切分的,过滤条件中应尽量包含带有拆分键的条件,可以让 PolarDB-X 根据拆分键对应的值将查询直接下推到特定的分库,避免 PolarDB-X 做全表扫描。

例如,表 test 的拆分键是 c1,过滤条件中若不带有拆分键,会做全表扫描:

  1. mysql> SELECT * FROM test WHERE c2 = 2;
  2. +----+----+
  3. | c1 | c2 |
  4. +----+----+
  5. | 2 | 2 |
  6. +----+----+
  7. 1 row in set (0.05 sec)

对应的执行计划为:

  1. mysql> EXPLAIN SELECT * FROM test WHERE c2 = 2;
  2. +------------------------------------------------+--------------------------------------------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +------------------------------------------------+--------------------------------------------------------------------+--------+
  5. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0004_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {} |
  6. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0007_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {} |
  7. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0005_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {} |
  8. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0002_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {} |
  9. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0003_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {} |
  10. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0006_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {} |
  11. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0000_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {} |
  12. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0001_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {} |
  13. +------------------------------------------------+--------------------------------------------------------------------+--------+
  14. 8 rows in set (0.00 sec)

含拆分键的过滤条件的取值范围越小,越有助于提高 PolarDB-X 的查询速度。

例如,对表 test 查询时包含带有拆分键 c1 的范围过滤条件:

  1. mysql> SELECT * FROM test WHERE c1 > 1 AND c1 < 4;
  2. +----+----+
  3. | c1 | c2 |
  4. +----+----+
  5. | 2 | 2 |
  6. | 3 | 3 |
  7. +----+----+
  8. 2 rows in set (0.04 sec)

对应的执行计划为:

  1. mysql> EXPLAIN SELECT * FROM test WHERE c1 > 1 AND c1 < 4;
  2. +------------------------------------------------+--------------------------------------------------------------------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +------------------------------------------------+--------------------------------------------------------------------------------------------+--------+
  5. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0002_RDS | select `test`.`c1`,`test`.`c2` from `test` where ((`test`.`c1` > 1) AND (`test`.`c1` < 4)) | {} |
  6. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0003_RDS | select `test`.`c1`,`test`.`c2` from `test` where ((`test`.`c1` > 1) AND (`test`.`c1` < 4)) | {} |
  7. +------------------------------------------------+--------------------------------------------------------------------------------------------+--------+
  8. 2 rows in set (0.00 sec)

等值条件会比范围条件执行得更快。例如:

  1. mysql> SELECT * FROM test WHERE c1 = 2;
  2. +----+----+
  3. | c1 | c2 |
  4. +----+----+
  5. | 2 | 2 |
  6. +----+----+
  7. 1 row in set (0.03 sec)

对应的执行计划为:

  1. mysql> EXPLAIN SELECT * FROM test WHERE c1 = 2;
  2. +------------------------------------------------+--------------------------------------------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +------------------------------------------------+--------------------------------------------------------------------+--------+
  5. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0002_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c1` = 2) | {} |
  6. +------------------------------------------------+--------------------------------------------------------------------+--------+
  7. 1 row in set (0.00 sec)

此外,在向拆分表中插入数据时,插入字段中必须带有拆分键。

例如,向表 test 中插入数据时带有拆分键 c1:

  1. mysql> INSERT INTO test(c1,c2) VALUES(8,8);
  2. Query OK, 1 row affected (0.07 sec)

查询返回行数优化

PolarDB-X 在执行带有 LIMIT [ offset, ] row_count 的查询时,实际上是依次将 offset 之前的记录读取出来并直接丢弃,这样当 offset 非常大的时候,即使 row_count 很小,也会导致查询非常缓慢。例如以下的 SQL:

  1. SELECT *
  2. FROM sample_order
  3. ORDER BY sample_order.id
  4. LIMIT 10000, 2

它虽然只返回第10000与10001两条记录,可它的执行时间为12秒左右,这是因为 PolarDB-X 实际读取的记录数为10002条:

  1. mysql> SELECT * FROM sample_order ORDER BY sample_order.id LIMIT 10000,2;
  2. +--------------+------------+--------------+--------------+------------+
  3. | id | sellerId | trade_id | buyer_id | buyer_nick |
  4. +--------------+------------+--------------+--------------+------------+
  5. | 242012755468 | 1711939506 | 242012755467 | 244148116334 | zhangsan |
  6. | 242012759093 | 1711939506 | 242012759092 | 244148138304 | wangwu |
  7. +--------------+------------+--------------+--------------+------------+
  8. 2 rows in set (11.93 sec)

对应的执行计划为:

  1. mysql> EXPLAIN SELECT * FROM sample_order ORDER BY sample_order.id LIMIT 10000,2;
  2. +------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
  5. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0004_RDS | select `sample_order`.`id`,`sample_order`.`sellerId`,`sample_order`.`trade_id`,`sample_order`.`buyer_id`,`sample_order`.`buyer_nick` from `sample_order` order by `sample_order`.`id` asc limit 0,10002 | {} |
  6. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0007_RDS | select `sample_order`.`id`,`sample_order`.`sellerId`,`sample_order`.`trade_id`,`sample_order`.`buyer_id`,`sample_order`.`buyer_nick` from `sample_order` order by `sample_order`.`id` asc limit 0,10002 | {} |
  7. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0005_RDS | select `sample_order`.`id`,`sample_order`.`sellerId`,`sample_order`.`trade_id`,`sample_order`.`buyer_id`,`sample_order`.`buyer_nick` from `sample_order` order by `sample_order`.`id` asc limit 0,10002 | {} |
  8. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0002_RDS | select `sample_order`.`id`,`sample_order`.`sellerId`,`sample_order`.`trade_id`,`sample_order`.`buyer_id`,`sample_order`.`buyer_nick` from `sample_order` order by `sample_order`.`id` asc limit 0,10002 | {} |
  9. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0003_RDS | select `sample_order`.`id`,`sample_order`.`sellerId`,`sample_order`.`trade_id`,`sample_order`.`buyer_id`,`sample_order`.`buyer_nick` from `sample_order` order by `sample_order`.`id` asc limit 0,10002 | {} |
  10. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0006_RDS | select `sample_order`.`id`,`sample_order`.`sellerId`,`sample_order`.`trade_id`,`sample_order`.`buyer_id`,`sample_order`.`buyer_nick` from `sample_order` order by `sample_order`.`id` asc limit 0,10002 | {} |
  11. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0000_RDS | select `sample_order`.`id`,`sample_order`.`sellerId`,`sample_order`.`trade_id`,`sample_order`.`buyer_id`,`sample_order`.`buyer_nick` from `sample_order` order by `sample_order`.`id` asc limit 0,10002 | {} |
  12. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0001_RDS | select `sample_order`.`id`,`sample_order`.`sellerId`,`sample_order`.`trade_id`,`sample_order`.`buyer_id`,`sample_order`.`buyer_nick` from `sample_order` order by `sample_order`.`id` asc limit 0,10002 | {} |
  13. +------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
  14. 8 rows in set (0.01 sec)

针对上述情况,SQL 优化方向是先查出ID集合,再通过 IN 匹配真正的记录内容,改写后的 SQL 查询如下:

  1. SELECT *
  2. FROM sample_order o
  3. WHERE o.id IN (
  4. SELECT id
  5. FROM sample_order
  6. ORDER BY id
  7. LIMIT 10000, 2 )

这样改写的目的是先用内存缓存ID(前提是ID数目不多),如果 sample_order 表的拆分键是ID,那么 PolarDB-X 还可以将这样的 IN 查询通过规则计算下推到不同的分库来查询,避免全表扫描和不必要的网络 IO。观察改写后的 SQL 查询效果:

  1. mysql> SELECT *
  2. -> FROM sample_order o
  3. -> WHERE o.id IN ( SELECT id FROM sample_order ORDER BY id LIMIT 10000,2 );
  4. +--------------+------------+--------------+--------------+------------+
  5. | id | sellerId | trade_id | buyer_id | buyer_nick |
  6. +--------------+------------+--------------+--------------+------------+
  7. | 242012755468 | 1711939506 | 242012755467 | 244148116334 | zhangsan |
  8. | 242012759093 | 1711939506 | 242012759092 | 244148138304 | wangwu |
  9. +--------------+------------+--------------+--------------+------------+
  10. 2 rows in set (1.08 sec)

执行时间由原来的12秒减少到1.08秒,缩减了一个数量级。

对应的执行计划为:

  1. mysql> EXPLAIN SELECT *
  2. -> FROM sample_order o
  3. -> WHERE o.id IN ( SELECT id FROM sample_order ORDER BY id LIMIT 10000,2 );
  4. +------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------+
  5. | GROUP_NAME | SQL | PARAMS |
  6. +------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------+
  7. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0002_RDS | select `o`.`id`,`o`.`sellerId`,`o`.`trade_id`,`o`.`buyer_id`,`o`.`buyer_nick` from `sample_order` `o` where (`o`.`id` IN (10002)) | {} |
  8. | SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0001_RDS | select `o`.`id`,`o`.`sellerId`,`o`.`trade_id`,`o`.`buyer_id`,`o`.`buyer_nick` from `sample_order` `o` where (`o`.`id` IN (10001)) | {} |
  9. +------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------+
  10. 2 rows in set (0.03 sec)

分组及排序优化

在 PolarDB-X 中,如果在一条 SQL 查询中必须同时使用 DISTINCT、GROUP BY 与 ORDER BY,应尽可能保证 DISTINCT、GROUP BY 与 ORDER BY 语句后所带的字段相同,且尽量为拆分键,使最终的 SQL 查询只返回少量数据。这样能够让分布式查询中消耗的网络带宽最小,并且不需要取出大量数据在临时表内进行排序,系统的性能能够达到最优状态。

JOIN 优化

PolarDB-X 的 JOIN 查询分为可下推的 JOIN 和不可下推的 JOIN(即分布式 JOIN)两类,其优化策略各不相同。

可下推的 JOIN 优化

可下推的 JOIN 主要分为以下几类:

  • 单表(即非拆分表)之间的 JOIN;
  • 参与 JOIN 的表在过滤条件中均带有拆分键作为条件,并且拆分算法相同(即通过拆分算法计算的数据分布在相同分片上);
  • 参与 JOIN 的表均按照拆分键作为 JOIN 条件,并且拆分算法相同;
  • 广播表(也称为小表广播)与拆分表之间的 JOIN。

使用 PolarDB-X 时,应尽可能将 JOIN 查询优化成能够在分库上执行的可下推的 JOIN 形式。

以广播表与拆分表之间的 JOIN 为例,应将广播表作为 JOIN 驱动表(将 JOIN 中的左表称为驱动表)。PolarDB-X 的广播表在各个分库都会存放一份同样的数据,当作为 JOIN 驱动表时,该表与分表的 JOIN 可以转化为单库的 JOIN 并进行合并计算,提高查询性能。

例如,有以下的三个表做 JOIN 查询(其中表 sample_area 是广播表,sample_item 和 sample_buyer 是拆分表),查询执行时间约15秒:

  1. mysql> SELECT sample_area.name
  2. -> FROM sample_item i JOIN sample_buyer b ON i.sellerId = b.sellerId JOIN sample_area a ON b.province = a.id
  3. -> WHERE a.id < 110107
  4. -> LIMIT 0, 10;
  5. +------+
  6. | name |
  7. +------+
  8. | BJ |
  9. | BJ |
  10. | BJ |
  11. | BJ |
  12. | BJ |
  13. | BJ |
  14. | BJ |
  15. | BJ |
  16. | BJ |
  17. | BJ |
  18. +------+
  19. 10 rows in set (14.88 sec)

如果调整一下 JOIN 的顺序,将广播表放在最左边作为 JOIN 驱动表,则整个 JOIN 查询在 PolarDB-X 中会被下推为单库 JOIN 查询:

  1. mysql> SELECT sample_area.name
  2. -> FROM sample_area a JOIN sample_buyer b ON b.province = a.id JOIN sample_item i ON i.sellerId = b.sellerId
  3. -> WHERE a.id < 110107
  4. -> LIMIT 0, 10;
  5. +------+
  6. | name |
  7. +------+
  8. | BJ |
  9. | BJ |
  10. | BJ |
  11. | BJ |
  12. | BJ |
  13. | BJ |
  14. | BJ |
  15. | BJ |
  16. | BJ |
  17. | BJ |
  18. +------+
  19. 10 rows in set (0.04 sec)

查询执行时间从15秒减少到0.04秒,性能提升非常明显。

注意:广播表在分库上通过同步机制实现数据一致,有秒级延迟。

分布式 JOIN 优化

如果一个 JOIN 查询不可下推(即 JOIN 条件和过滤条件中均不带有拆分键),则需要由 PolarDB-X 完成查询中的部分计算,即分布式 JOIN。

通常将分布式 JOIN 中的表按照数据量大小分为两类:

  • 小表:经过条件过滤后,参与 JOIN 计算的中间结果的数据量比较少(一般少于 100 条,或者相较于其它表数据更少)的表;
  • 大表:经过条件过滤后,参与 JOIN 计算的中间结果的数据量比较大(一般多于 100 条,或者相较于其它表数据更多)的表。

在 PolarDB-X 层的 JOIN 计算中,大多数情况下采用的 JOIN 算法都是 Nested Loop 及其派生算法(若 JOIN 有排序要求,则使用 Sort Merge 算法)。采用 Nested Loop 算法时,如果 JOIN 中左表的数据量越少,那么 PolarDB-X 对右表做查询的次数就越少,如果右表上建有索引或者表中的数据量也很少,则 JOIN 的速度会更快。因此,在 PolarDB-X 中,分布式 JOIN 的左表被称为驱动表,对分布式 JOIN 的优化应将小表作为驱动表,且让驱动表带有尽可能多的过滤条件。

以下面的分布式 JOIN 为例,查询约需要24秒:

  1. mysql> SELECT t.title, t.price
  2. -> FROM sample_order o,
  3. -> ( SELECT * FROM sample_item i WHERE i.id = 242002396687 ) t
  4. -> WHERE t.source_id = o.source_item_id AND o.sellerId < 1733635660;
  5. +----------------------------------+--------+
  6. | title | price |
  7. +----------------------------------|--------+
  8. | Sample Item for Distributed JOIN | 239.00 |
  9. | Sample Item for Distributed JOIN | 239.00 |
  10. | Sample Item for Distributed JOIN | 239.00 |
  11. | Sample Item for Distributed JOIN | 239.00 |
  12. | Sample Item for Distributed JOIN | 239.00 |
  13. | Sample Item for Distributed JOIN | 239.00 |
  14. | Sample Item for Distributed JOIN | 239.00 |
  15. | Sample Item for Distributed JOIN | 239.00 |
  16. | Sample Item for Distributed JOIN | 239.00 |
  17. | Sample Item for Distributed JOIN | 239.00 |
  18. +----------------------------------+--------+
  19. 10 rows in set (23.79 sec)

通过初步分析,上述 JOIN 查询是一个 INNER JOIN,并不知道参与 JOIN 计算的中间结果的实际数据量,可以对 o 表与 t 表分别做 COUNT() 查询得到实际数据。

对于 o 表,观察到 WHERE 条件中的 o.sellerId < 1733635660 只与 o 表相关,可以将其提取出来,附加到 o 表的 COUNT() 查询条件中,得到如下的查询结果:

  1. mysql> SELECT COUNT(*) FROM sample_order o WHERE o.sellerId < 1733635660;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 504018 |
  6. +----------+
  7. 1 row in set (0.10 sec)

o 表的中间结果约有50万条记录。类似地,t 表是一个子查询,直接将其抽取出来进行 COUNT() 查询:

  1. mysql> SELECT COUNT(*) FROM sample_item i WHERE i.id = 242002396687;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 1 |
  6. +----------+
  7. 1 row in set (0.01 sec)

t 表的中间结果只有1条记录,所以可确定 o 表为大表,t 表为小表。根据尽量将小表作为分布式 JOIN 驱动表的原则,将 JOIN 查询调整后的查询结果为:

  1. mysql> SELECT t.title, t.price
  2. -> FROM ( SELECT * FROM sample_item i WHERE i.id = 242002396687 ) t,
  3. -> sample_order o
  4. -> WHERE t.source_id = o.source_item_id AND o.sellerId < 1733635660;
  5. +----------------------------------+--------+
  6. | title | price |
  7. +----------------------------------|--------+
  8. | Sample Item for Distributed JOIN | 239.00 |
  9. | Sample Item for Distributed JOIN | 239.00 |
  10. | Sample Item for Distributed JOIN | 239.00 |
  11. | Sample Item for Distributed JOIN | 239.00 |
  12. | Sample Item for Distributed JOIN | 239.00 |
  13. | Sample Item for Distributed JOIN | 239.00 |
  14. | Sample Item for Distributed JOIN | 239.00 |
  15. | Sample Item for Distributed JOIN | 239.00 |
  16. | Sample Item for Distributed JOIN | 239.00 |
  17. | Sample Item for Distributed JOIN | 239.00 |
  18. +----------------------------------+--------+
  19. 10 rows in set (0.15 sec)

查询时间从约24秒减少到0.15秒,性能提升非常明显。

子查询优化

在包含子查询的 SQL 优化中,应尽可能将查询下推到具体的分库上执行,并减少 PolarDB-X 层的计算量。要达到这一目标,可以尝试两个方面的优化:

  • 将子查询的形式改写为多表 JOIN 形式,并参照 JOIN 优化方法进一步优化;
  • 尽量在 JOIN 条件或过滤条件中带上拆分键,有利于 PolarDB-X 将查询下推到特定的分库,避免全表扫描。

以下面的子查询为例:

  1. SELECT o.*
  2. FROM sample_order o
  3. WHERE NOT EXISTS
  4. (SELECT sellerId FROM sample_seller s WHERE o.sellerId = s.id)

可将其改写为 JOIN 的形式:

  1. SELECT o.*
  2. FROM sample_order o LEFT JOIN sample_seller s ON o.sellerId = s.id
  3. WHERE s.id IS NULL
首页 SQL 优化方法