全部产品

SQL调优方法与实战

更新时间:2020-09-04 10:02:10

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

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

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

下推更多的计算

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

  • 过滤条件,如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,可以给分表增加索引来解决,这里不再详述。

PolarDB-X自5.4.1 版本开始支持全局二级索引,可以通过增加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` > ?))")

更多关于全局二级索引的使用细节,请参见全局二级索引

执行计划调优

以下内容适用于PolarDB-X 5.3.12或以上版本。

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

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

下面的查询,PolarDB-X查询优化器综合了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是比Hash JOIN更好的选择(关于BKAJOIN和Hash JOIN的介绍,请参见JOIN与子查询的优化和执行)。

我们可以通过如下/*+TDDL:BKA_JOIN(orders, customer)*/Hint强制优化器使用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固定执行计划。具体操作如下:

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';r

这样一来,对于这条SQL(参数可以不同),PolarDB-X都会采用如上固定的执行计划。

更多关于执行计划管理的信息,请参见执行计划管理