863      阿里云

SQL优化__最佳实践_分布式关系型数据库 DRDS-阿里云

DRDS是一个高效稳定的分布式关系数据库系统。但是，由于其处理的是分布式的关系查询，因而它对于SQL的查询优化与传统的单一数据库（如mysql，oracle）的查询优化有所不同。后者在查询优化时，主要考虑的就是磁盘IO的开销，但前者在优化时还需要考虑另外一个更为重要的IO开销—网络。为了优化DRDS的SQL执行，其核心的优化思想就是减少网络IO。为此，DRDS会尽量地将原本DRDS这一层的工作下发到其底层的各个分库（如RDS 等）来做。这样就可以将原本需要走网络的IO开销转换为单机的磁盘IO开销，从而提升查询的执行效率。因此，我们在使用DRDS时若遇到了慢SQL，则需要针对DRDS的特点将SQL进行适当的改写。

SQL条件优化

DRDS的数据是按拆分键进行水平切分的，查询中若带上拆分键对于减少SQL在DRDS的执行时间很有意义。查询条件尽量带分库键，就可以让DRDS根据分库键的值将查询直接路由到特定的分库，这有助于避免DRDS做全库扫描。含分库键的条件的选择度越高（或区分度越高），越有助于提高DRDS的查询速度。例如，等值查询会比范围查询执行得更快。

SQL的JOIN优化

在SQL中，Join操作常常会成为最为耗时的操作。DRDS在大多数情况下使用的Join算法都是Nested Loop及其派生算法（若Join有排序要求，则使用Sort Merge 算法）。DRDS 基于Nested Loop算法的Join过程是这样的：对于Join的左右两个表，DRDS首先从Join的左表（又叫驱动表）取出数据，然后将所取出数据中的Join列的值放到右表并进行IN查询，从而完成Join过程。因此，如果Join的左表的数据量越少，那么DRDS对右表做IN查询就次数就越少，如果右表的数据量也很少或建有索引，则Join的速度会更快。因此，在DRDS中，Join的驱动表的选择对于Join的优化非常重要。

小表作为Join驱动表

所谓的小表，并不是说这个表在数据库中的记录数目，而是这个表在查询中在查询中经过条件过滤后的所返回的记录数。因此，要确定一张表的实际数据量，最简单的方法，就是附带上与这个表相关联的where条件和join on条件，放到DRDS里单独做一次count(*)查询，查看数据量。例如，假设有如下的SQL:

select t.title, t.price
from t_order o,
    ( select * from t_item i where i.id=242002396687 ) t
where  t.source_id=o.source_item_id and o.sellerId<1733635660;

它的查询速度很慢，如下所示：

约需要24秒。咋看上述的SQL，这是一个inner JOIN，我们并不知道其中o表与t表在JOIN过程中的实际数据量，但是，我们可以分别去对o表与t表做count()查询得到这组数据。对于o表，我们观察where条件中的o.sellerId<1733635660只与o表相关，可以将它提取出来，附加到o表的count()查询中，即得到如下的查询结果：

于是我们可以知道o表有50W条记录；类似地，对于t表，这是一个子查询，直接将其抽取出来单独进行count(*)的查询，则有:

这样就可以知道t表的数据量只有一条。于是，我们可以确定o表为大表，而t表为小表，根据尽量将小表作为Join驱动表的原则，我们将SQL调整为

select t.title, t.price
from 
( select * from t_item i where i.id=242002396687 ) t,
 t_order o
where  t.source_id=o.source_item_id and o.sellerId<1733635660

此时的查询结果为:

查询时间从24秒减少为0.15秒，提升巨大。

广播表作为Join的驱动表

DRDS的广播表在各个分库都会存一份，所以当它作为Join的驱动表时，它和其它表的Join的都可以转化为单机的Join，从面提高查询性能。例如，假设有以下的SQL（其中表t_area是广播表）:

select t_area.name
from  t_item i join t_buyer b on i.sellerId=b.sellerId join t_area a on b.province=a.id
where  a.id < 110107 
limit 0, 10

这是三个表做JOIN，它的查询结果如下:

执行时间比较长，约15秒。现在，我们调整一下join的顺序，将广播表放在在最左边作为join的驱动表，即:

select t_area.name
    from t_area a join t_buyer b on b.province=a.id join t_item i on i.sellerId=b.sellerId
    where  a.id < 110107 
limit 0, 10

这样整个join在DRDS中会被下推为单机join。我们再观察一下调整后的SQL的执行结果:

SQL的Limit优化

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

SELECT * 
FROM t_order
ORDER BY t_order.id
LIMIT 10000,2

它虽然只取10000与10001两条记录，可它的执行时间为12秒左右，这是因为DRDS实际读取的记录数为10002，如下图：

针对上述的情况，SQL的优化方向是先查SQL的ID集合，再通过in查询真正的记录内容，改写后的SQL，如下所示：

SELECT * 
FROM t_order o
WHERE o.id IN ( 
        SELECT id 
        FROM t_order 
        ORDER BY id  
LIMIT 10000,2  )

这样改写的目的是先用内存缓存ID（id数目不多），这样磁盘IO就减少。如果t_order表的分库键是id，那么DRDS还可以将这样的in查询通过规则计算路由到不同的分库来查询，避免全库扫描。我们再观察改写后的SQL的查询效果

执行时间由原来的12秒变成1.08秒，缩减了一个数量级。

SQL的ORDER BY 优化

在DRDS中，默认情况下请尽可能保证Distinct，Group by 与Order By语句后所带的列名相同，并使最终的SQL只返回少量数据。因为在这种情况下，我们能够让分布式查询中消耗的网络带宽最小，并且不需要取出大量数据在临时表内进行排序，系统的性能能够达到最优状态。

例如，对于下边的一条SQL：

select buyer_id, 
        count(*) as maxSize 
from t_trade 
group by buyer_id 
order by maxSize desc 
limit 1

DRDS执行过程中首先需要对数据按buyer_id排序以进行聚合操作，然后还需要对聚合结果按maxSize排序。因为存在两个不同的排序列要求，DRDS不用临时表保存中间结果的话，无法一次完成该SQL，因此这个SQL的实际执行会报以下的错

报错的内容提示了不允许使用临时表。DRDS支持使用临时表，但默认并不推荐，因为临时表的使用一般就意味着系统存在性能瓶颈，因此如果没有特殊必要，请尽可能通过Distimct Group by 与Order by语句所带列名相同的方式来规避使用临时表。如果让上述的SQL顺利执行，可以通过往SQL中添加HINT的方式来告诉DRDS可以允许使用临时表，SQL修改如下（绿色部分）：

/*+TDDL({'extra':{'ALLOW_TEMPORARY_TABLE':'TRUE'}})*/
select buyer_id, 
       count(*) as maxSize 
from t_trade 
group by buyer_id 
order by maxSize desc 
limit 1

这样DRDS就可以顺利执行这个SQL了。不过，这个HINT在一些mysql 客户端有可能会被当作注释忽略掉，因而hint一般加在通过mysql connector直接给DRDS发送SQL的时候。请注意，请确定在临时表内只有很少量数据的前提下才使用临时表，否则系统会有严重性能问题####

最后更新：2016-11-23 16:04:05

  上一篇： 数据导入和导出__最佳实践_分布式关系型数据库 DRDS-阿里云

  下一篇： 排查DRDS慢SQL__开发手册_分布式关系型数据库 DRDS-阿里云