开发者社区> 问答> 正文

SQL 优化方法有哪些?

本文档介绍 DRDS 中 SQL 优化的原则和不同类型 SQL 的优化方法。文档中涉及到的基本概念和指令,请参考文档 SQL 优化基本概念。

SQL 优化的基本原则

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

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

可下推计算主要包括:

JOIN 连接;
过滤条件,如 WHERE 或 HAVING 中的条件;
聚合计算,如 COUNT,GROUP BY 等;
排序,如 ORDER BY;
去重,如 DISTINCT;
函数计算,如 NOW() 函数等;
子查询。
注意:上述列表只是列出可下推计算的各种可能形式,并不代表所有的子句/条件或者子句/条件的组合一定是可下推计算。

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

单表 SQL 优化
过滤条件优化
查询返回行数优化
分组及排序优化
JOIN 优化
可下推的 JOIN 优化
分布式 JOIN 优化
子查询优化
单表 SQL 优化

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

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

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

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

mysql> SELECT * FROM test WHERE c2 = 2;
+----+----+
| c1 | c2 |
+----+----+
|  2 |  2 |
+----+----+
1 row in set (0.05 sec)
对应的执行计划为:

mysql> EXPLAIN SELECT * FROM test WHERE c2 = 2;
+------------------------------------------------+--------------------------------------------------------------------+--------+
| GROUP_NAME                                     | SQL                                                                | PARAMS |
+------------------------------------------------+--------------------------------------------------------------------+--------+
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0004_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {}     |
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0007_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {}     |
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0005_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {}     |
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0002_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {}     |
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0003_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {}     |
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0006_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {}     |
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0000_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {}     |
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0001_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c2` = 2) | {}     |
+------------------------------------------------+--------------------------------------------------------------------+--------+
8 rows in set (0.00 sec)
含拆分键的过滤条件的取值范围越小,越有助于提高 DRDS 的查询速度。

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

mysql> SELECT * FROM test WHERE c1 > 1 AND c1 < 4;
+----+----+
| c1 | c2 |
+----+----+
|  2 |  2 |
|  3 |  3 |
+----+----+
2 rows in set (0.04 sec)
对应的执行计划为:

mysql> EXPLAIN SELECT * FROM test WHERE c1 > 1 AND c1 < 4;
+------------------------------------------------+--------------------------------------------------------------------------------------------+--------+
| GROUP_NAME                                     | SQL                                                                                        | PARAMS |
+------------------------------------------------+--------------------------------------------------------------------------------------------+--------+
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0002_RDS | select `test`.`c1`,`test`.`c2` from `test` where ((`test`.`c1` > 1) AND (`test`.`c1` < 4)) | {}     |
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0003_RDS | select `test`.`c1`,`test`.`c2` from `test` where ((`test`.`c1` > 1) AND (`test`.`c1` < 4)) | {}     |
+------------------------------------------------+--------------------------------------------------------------------------------------------+--------+
2 rows in set (0.00 sec)
等值条件会比范围条件执行得更快。例如:

mysql> SELECT * FROM test WHERE c1 = 2;
+----+----+
| c1 | c2 |
+----+----+
|  2 |  2 |
+----+----+
1 row in set (0.03 sec)
对应的执行计划为:

mysql> EXPLAIN SELECT * FROM test WHERE c1 = 2;
+------------------------------------------------+--------------------------------------------------------------------+--------+
| GROUP_NAME                                     | SQL                                                                | PARAMS |
+------------------------------------------------+--------------------------------------------------------------------+--------+
| SEQPERF_1478746391548CDTCSEQPERF_OXGJ_0002_RDS | select `test`.`c1`,`test`.`c2` from `test` where (`test`.`c1` = 2) | {}     |
+------------------------------------------------+--------------------------------------------------------------------+--------+
1 row in set (0.00 sec)
此外,在向拆分表中插入数据时,插入字段中必须带有拆分键。

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

mysql> INSERT INTO test(c1,c2) VALUES(8,8);
Query OK, 1 row affected (0.07 sec)
查询返回行数优化

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

SELECT *
FROM sample_order
ORDER BY sample_order.id
LIMIT 10000, 2
它虽然只返回第10000与10001两条记录,可它的执行时间为12秒左右,这是因为 DRDS 实际读取的记录数为10002条:

mysql> SELECT * FROM sample_order ORDER BY sample_order.id LIMIT 10000,2;
+--------------+------------+--------------+--------------+------------+
| id           | sellerId   | trade_id     | buyer_id     | buyer_nick |
+--------------+------------+--------------+--------------+------------+
| 242012755468 | 1711939506 | 242012755467 | 244148116334 | zhangsan   |
| 242012759093 | 1711939506 | 242012759092 | 244148138304 | wangwu     |
+--------------+------------+--------------+--------------+------------+
2 rows in set (11.93 sec)
对应的执行计划为:

mysql> EXPLAIN SELECT * FROM sample_order ORDER BY sample_order.id LIMIT 10000,2;
+------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| GROUP_NAME                                     | SQL                                                                                                                                                                                                      | PARAMS |
+------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| 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 | {}     |
| 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 | {}     |
| 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 | {}     |
| 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 | {}     |
| 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 | {}     |
| 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 | {}     |
| 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 | {}     |
| 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 | {}     |
+------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
8 rows in set (0.01 sec)
针对上述情况,SQL 优化方向是先查出 id 集合,再通过 IN 匹配真正的记录内容,改写后的 SQL 查询如下:

SELECT *
FROM sample_order o
WHERE o.id IN (
      SELECT id
      FROM sample_order
      ORDER BY id  
      LIMIT 10000, 2 )
这样改写的目的是先用内存缓存 id(前提是 id 数目不多),如果 sample_order 表的拆分键是 id,那么 DRDS 还可以将这样的 IN 查询通过规则计算下推到不同的分库来查询,避免全表扫描和不必要的网络 IO。观察改写后的 SQL 查询效果:

mysql> SELECT *
    -> FROM sample_order o
    -> WHERE o.id IN ( SELECT id FROM sample_order ORDER BY id LIMIT 10000,2 );
+--------------+------------+--------------+--------------+------------+
| id           | sellerId   | trade_id     | buyer_id     | buyer_nick |
+--------------+------------+--------------+--------------+------------+
| 242012755468 | 1711939506 | 242012755467 | 244148116334 | zhangsan   |
| 242012759093 | 1711939506 | 242012759092 | 244148138304 | wangwu     |
+--------------+------------+--------------+--------------+------------+
2 rows in set (1.08 sec)
执行时间由原来的12秒减少到1.08秒,缩减了一个数量级。

对应的执行计划为:

mysql> EXPLAIN SELECT *
    -> FROM sample_order o
    -> WHERE o.id IN ( SELECT id FROM sample_order ORDER BY id LIMIT 10000,2 );
+------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------+
| GROUP_NAME                                     | SQL                                                                                                                               | PARAMS |
+------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------+
| 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)) | {}     |
| 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)) | {}     |
+------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------+
2 rows in set (0.03 sec)
分组及排序优化

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

JOIN 优化

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

可下推的 JOIN 优化

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

单表(即非拆分表)之间的 JOIN;
参与 JOIN 的表在过滤条件中均带有拆分键作为条件,并且拆分算法相同(即通过拆分算法计算的数据分布在相同分片上);
参与 JOIN 的表均按照拆分键作为 JOIN 条件,并且拆分算法相同;
广播表(也称为小表广播)与拆分表之间的 JOIN。
使用 DRDS 时,应尽可能将 JOIN 查询优化成能够在分库上执行的可下推的 JOIN 形式。

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

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

mysql> SELECT sample_area.name
    -> FROM sample_item i JOIN sample_buyer b ON i.sellerId = b.sellerId JOIN sample_area a ON b.province = a.id
    -> WHERE a.id < 110107
    -> LIMIT 0, 10;
+------+
| name |
+------+
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
+------+
10 rows in set (14.88 sec)
如果调整一下 JOIN 的顺序,将广播表放在最左边作为 JOIN 驱动表,则整个 JOIN 查询在 DRDS 中会被下推为单库 JOIN 查询:

mysql> SELECT sample_area.name
    -> FROM sample_area a JOIN sample_buyer b ON b.province = a.id JOIN sample_item i ON i.sellerId = b.sellerId
    -> WHERE a.id < 110107
    -> LIMIT 0, 10;
+------+
| name |
+------+
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
|  BJ  |
+------+    
10 rows in set (0.04 sec)
查询执行时间从15秒减少到0.04秒,性能提升非常明显。

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

分布式 JOIN 优化

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

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

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

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

mysql> SELECT t.title, t.price
    -> FROM sample_order o,
    ->      ( SELECT * FROM sample_item i WHERE i.id = 242002396687 ) t
    -> WHERE t.source_id = o.source_item_id AND o.sellerId < 1733635660;
+----------------------------------+--------+
| title                            | price  |
+----------------------------------|--------+
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
+----------------------------------+--------+
10 rows in set (23.79 sec)
通过初步分析,上述 JOIN 查询是一个 INNER JOIN,并不知道参与 JOIN 计算的中间结果的实际数据量,可以对 o 表与 t 表分别做 COUNT() 查询得到实际数据。

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

mysql> SELECT COUNT(*) FROM sample_order o WHERE o.sellerId < 1733635660;
+----------+
| count(*) |
+----------+
|   504018 |
+----------+
1 row in set (0.10 sec)
o 表的中间结果约有50万条记录。类似地,t 表是一个子查询,直接将其抽取出来进行 COUNT() 查询:

mysql> SELECT COUNT(*) FROM sample_item i WHERE i.id = 242002396687;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)
t 表的中间结果只有1条记录,所以可确定 o 表为大表,t 表为小表。根据尽量将小表作为分布式 JOIN 驱动表的原则,将 JOIN 查询调整后的查询结果为:

mysql> SELECT t.title, t.price
    -> FROM ( SELECT * FROM sample_item i WHERE i.id = 242002396687 ) t,
    ->      sample_order o
    -> WHERE  t.source_id = o.source_item_id AND o.sellerId < 1733635660;
+----------------------------------+--------+
| title                            | price  |
+----------------------------------|--------+
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
| Sample Item for Distributed JOIN | 239.00 |
+----------------------------------+--------+
10 rows in set (0.15 sec)
查询时间从约24秒减少到0.15秒,性能提升非常明显。

子查询优化

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

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

SELECT o.*
FROM sample_order o
WHERE NOT EXISTS
     (SELECT sellerId FROM sample_seller s WHERE o.sellerId = s.id)
可将其改写为 JOIN 的形式:

SELECT o.*
FROM sample_order o LEFT JOIN sample_seller s ON o.sellerId = s.id
WHERE s.id IS NULL

展开
收起
猫饭先生 2017-10-30 13:56:03 1796 0
0 条回答
写回答
取消 提交回答
问答排行榜
最热
最新

相关电子书

更多
SQL Server在电子商务中的应用与实践 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载