› 推特 ›阿里云技术文档›正文

SQL 调优方法与实战_SQL 调优指南_用户指南_分布式关系型数据库 DRDS

admin 阿里云技术文档 2020-02-11 221 0
阿里云服务器优惠

SQL 调优方法与实战

通常，找出要调优的慢 SQL 之后，我们首先通过 EXPLAIN 查看执行计划，然后按照以下思路依次进行尝试：

  1. 是否能让更多的计算下推到 MySQL 执行？
  2. 是否能通过适当增加索引来加速执行？
  3. 是否可以通过优化执行计划来加速执行？

我们接下来会通过例子一一进行讲解。

下推更多的计算

上一章节中提到，DRDS 会尽可能将更多的计算下推到 MySQL。下推计算能够减少数据传输，减少网络层和 DRDS 层的开销，提升 SQL 语句的执行效率。DRDS 支持下推几乎所有算子，包括：

  • 过滤条件，如 WHERE 或 HAVING 中的条件
  • 列、函数，如 NOW() 函数等
  • 聚合，如 COUNT，GROUP BY 等（分成两阶段聚合）
  • 排序，如 ORDER BY
  • Join和子查询（两边的 Join Key 分片方式必须一样，或其中一边为广播表）

下面我们通过一个简单的例子讲解：如何将更多的计算下推到 MySQL 来加速执行。我们从下面这条慢 SQL 开始：

  1. > EXPLAIN select * from customer, nation where c_nationkey = n_nationkey and n_regionkey = 3;
  3. Project(c_custkey="c_custkey", c_name="c_name", c_address="c_address", c_nationkey="c_nationkey", c_phone="c_phone", c_acctbal="c_acctbal", c_mktsegment="c_mktsegment", c_comment="c_comment", n_nationkey="n_nationkey", n_name="n_name", n_regionkey="n_regionkey", n_comment="n_comment")
  4.   BKAJoin(condition="c_nationkey = n_nationkey", type="inner")
  5.     Gather(concurrent=true)
  6.       LogicalView(tables="nation", shardCount=2, sql="SELECT * FROM `nation` AS `nation` WHERE (`n_regionkey` = ?)")
  7.     Gather(concurrent=true)
  8.       LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT * FROM `customer` AS `customer` WHERE (`c_nationkey` IN ('?'))")

执行计划中出现了 BKAJoin，它表示：每次从左表获取一批数据，拼成一个 IN 查询取出右表相关联的行，最后进行 Join。由于左表数据量很大，需要取很多次才能完成查询，执行很慢。

我们希望能将 Join 也下推下去，那么，为什么这个 Join 现在没有下推呢？当前情况下，nation 是按主键 n_nationkey 切分的，而本查询的 Join Key 是 c_custkey，二者不同，无法下推！

考虑到 nation （国家）表数据量并不大、且几乎没有修改操作，我们可以将其重建成广播表：

  1. --- 修改后 ---
  2. CREATE TABLE `nation` (
  3.   `n_nationkey` int(11) NOT NULL,
  4.   `n_name` varchar(25) NOT NULL,
  5.   `n_regionkey` int(11) NOT NULL,
  6.   `n_comment` varchar(152) DEFAULT NULL,
  7.   PRIMARY KEY (`n_nationkey`)
  8. ) BROADCAST;  --- 声明为广播表

修改后，可以看到执行计划中不再出现 Join——几乎所有计算都被下推到 MySQL 执行了（LogicalView 中），而上层仅仅是将结果收集并返回给用户（Gather 算子），执行性能大大增强。

  1. > EXPLAIN select * from customer, nation where c_nationkey = n_nationkey and n_regionkey = 3;
  3. Gather(concurrent=true)
  4.   LogicalView(tables="customer_[0-7],nation", shardCount=8, sql="SELECT * FROM `customer` AS `customer` INNER JOIN `nation` AS `nation` ON ((`nation`.`n_regionkey` = ?) AND (`customer`.`c_nationkey` = `nation`.`n_nationkey`))")

更多关于下推的原理和优化，请参见“查询改写与下推”章节。

增加索引

如果下推 SQL 中出现（物理）慢 SQL，可以给分表增加索引来解决，这里不再详述。

DRDS 自 5.4.1 版本开始支持 全局二级索引(Global Secondary Index, GSI)，可以通过增加 GSI 的方式使逻辑表拥有多个拆分维度。

下面以一个慢 SQL 作为例子来讲解如何通过 GSI 下推更多算子：

  1. > EXPLAIN select o_orderkey, c_custkey, c_name from orders, customer
  2.           where o_custkey = c_custkey and o_orderdate = '2019-11-11' and o_totalprice > 100;
  4. Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  5.   HashJoin(condition="o_custkey = c_custkey", type="inner")
  6.     Gather(concurrent=true)
  7.       LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
  8.     Gather(concurrent=true)
  9.       LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` > ?))")

执行计划中，orders 按照 o_orderkey 拆分而 和 customer 按照 c_custkey 拆分，由于拆分维度不同，Join 算子不能下推。

考虑到 2019-11-11 当天总价高于 100 的订单非常多，跨分片 Join 耗时很高，需要在 orders 表上创建一个 GSI 来使得 Join 算子可以下推

查询中使用到了 orders 表的 o_orderkey, o_custkey, o_orderdate, o_totalprice 四列，其中 o_orderkey, o_custkey 分别是主表和索引表的拆分键，o_orderdate, o_totalprice 作为覆盖列包含在索引中用于避免回表

  1. > create global index i_o_custkey on orders(`o_custkey`) covering(`o_orderdate`, `o_totalprice`) 
  2.         DBPARTITION BY HASH(`o_custkey`) TBPARTITION BY HASH(`o_custkey`) TBPARTITIONS 4;

增加 GSI 并通过 force index(i_o_custkey) 强制使用索引后，跨分片 Join 变为 MySQL 上的局部 Join (IndexScan 中)，并且通过覆盖列避免了回表操作，查询性能得到提升。

  1. > EXPLAIN select o_orderkey, c_custkey, c_name from orders force index(i_o_custkey), customer
  2.           where o_custkey = c_custkey and o_orderdate = '2019-11-11' and o_totalprice > 100;
  4. Gather(concurrent=true)                                                                                                                                               
  5.   IndexScan(tables="i_o_custkey_[0-7],customer_[0-7]", shardCount=8, sql="SELECT `i_o_custkey`.`o_orderkey`, `customer`.`c_custkey`, `customer`.`c_name` FROM `i_o_custkey` AS `i_o_custkey` INNER JOIN `customer` AS `customer` ON (((`i_o_custkey`.`o_orderdate` = ?) AND (`i_o_custkey`.`o_custkey` = `customer`.`c_custkey`)) AND (`i_o_custkey`.`o_totalprice` > ?))")

更多关于全局二级索引的使用细节，参考 “全局二级索引使用文档“

执行计划调优

以下内容适用于 DRDS 5.3.12 或更高版本。

大多数情况下，DRDS 的查询优化器可以自动产生最佳的执行计划。但是，少数情况下，可能因为统计信息存在缺失、误差等，导致生成的执行计划不够好，这时，可以通过 Hint 来干预优化器行为，使之生成更好的执行计划。

下面我们以一个例子来讲解执行计划的调优：

下面的查询，DRDS 查询优化器综合了 Join 两边的代价

  1. > EXPLAIN select o_orderkey, c_custkey, c_name from orders, customer
  2.           where o_custkey = c_custkey and o_orderdate = '2019-11-15' and o_totalprice < 10;
  4. Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  5.   HashJoin(condition="o_custkey = c_custkey", type="inner")
  6.     Gather(concurrent=true)
  7.       LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
  8.     Gather(concurrent=true)
  9.       LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")

但是，实际上 2019-11-15 这一天总价低于 10 元的订单数量很小，只有几条，这时候用 BKAJoin 是比 HashJoin 更好的选择。（对 BKAJoin 和 HashJoin 的介绍请参考“Join 与子查询的优化和执行”）

我们可以通过 Hint：/*+TDDL:BKA_JOIN(orders, customer)*/ 强制优化器使用 BKAJoin（LookupJoin）：

  1. > EXPLAIN /*+TDDL:BKA_JOIN(orders, customer)*/ select o_orderkey, c_custkey, c_name from orders, customer
  2.           where o_custkey = c_custkey and o_orderdate = '2019-11-15' and o_totalprice < 10;
  4. Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  5.   BKAJoin(condition="o_custkey = c_custkey", type="inner")
  6.     Gather(concurrent=true)
  7.       LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")
  8.     Gather(concurrent=true)
  9.       LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer` WHERE (`c_custkey` IN ('?'))")

如果我们的思路没错的化，尝试执行一下加了 Hint 的查询：

  1. /*+TDDL:BKA_JOIN(orders, customer)*/ select o_orderkey, c_custkey, c_name from orders, customer where o_custkey = c_custkey and o_orderdate = '2019-11-15' and o_totalprice < 10;

应该比之前快的多。

为了让 Hint 发挥作用，我们可以将应用中的 SQL 加上 Hint，或者，更方便的方式是：使用执行计划管理（Plan Management）功能对该 SQL 固定执行计划。具体操作是：

  1. BASELINE FIX SQL /*+TDDL:BKA_JOIN(orders, customer)*/ select o_orderkey, c_custkey, c_name from orders, customer where o_custkey = c_custkey and o_orderdate = '2019-11-15';

这样一来，对于这条 SQL （参数可以不同），DRDS 都会采用我们固定下来的执行计划。你可以通过 EXPLAIN 验证这一点。为了节约篇幅，这里不再展示了。

更多关于执行计划管理的信息，可以参考文档“执行计划管理”。

版权声明

本文仅代表作者观点，不代表本站立场。
本文系作者授权发表，未经许可，不得转载。

评论

-----