全部产品
云市场

SQL 调优方法与实战

更新时间:2019-11-26 11:30:27

通常,找出要调优的慢 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;
  2. 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")
  3. BKAJoin(condition="c_nationkey = n_nationkey", type="inner")
  4. Gather(concurrent=true)
  5. LogicalView(tables="nation", shardCount=2, sql="SELECT * FROM `nation` AS `nation` WHERE (`n_regionkey` = ?)")
  6. Gather(concurrent=true)
  7. 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;
  2. Gather(concurrent=true)
  3. 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;
  3. Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  4. HashJoin(condition="o_custkey = c_custkey", type="inner")
  5. Gather(concurrent=true)
  6. LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
  7. Gather(concurrent=true)
  8. 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;
  3. Gather(concurrent=true)
  4. 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;
  3. Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  4. HashJoin(condition="o_custkey = c_custkey", type="inner")
  5. Gather(concurrent=true)
  6. LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
  7. Gather(concurrent=true)
  8. 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;
  3. Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  4. BKAJoin(condition="o_custkey = c_custkey", type="inner")
  5. Gather(concurrent=true)
  6. LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")
  7. Gather(concurrent=true)
  8. 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 验证这一点。为了节约篇幅,这里不再展示了。

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