SQL 调优方法与实战
通常,找出要调优的慢 SQL 之后,我们首先通过 EXPLAIN 查看执行计划,然后按照以下思路依次进行尝试:
- 是否能让更多的计算下推到 MySQL 执行?
- 是否能通过适当增加索引来加速执行?
- 是否可以通过优化执行计划来加速执行?
我们接下来会通过例子一一进行讲解。
下推更多的计算
上一章节中提到,DRDS 会尽可能将更多的计算下推到 MySQL。下推计算能够减少数据传输,减少网络层和 DRDS 层的开销,提升 SQL 语句的执行效率。DRDS 支持下推几乎所有算子,包括:
- 过滤条件,如 WHERE 或 HAVING 中的条件
- 列、函数,如 NOW() 函数等
- 聚合,如 COUNT,GROUP BY 等(分成两阶段聚合)
- 排序,如 ORDER BY
- Join和子查询(两边的 Join Key 分片方式必须一样,或其中一边为广播表)
下面我们通过一个简单的例子讲解:如何将更多的计算下推到 MySQL 来加速执行。我们从下面这条慢 SQL 开始:
> EXPLAIN select * from customer, nation where c_nationkey = n_nationkey and n_regionkey = 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")
BKAJoin(condition="c_nationkey = n_nationkey", type="inner")
Gather(concurrent=true)
LogicalView(tables="nation", shardCount=2, sql="SELECT * FROM `nation` AS `nation` WHERE (`n_regionkey` = ?)")
Gather(concurrent=true)
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 (国家)表数据量并不大、且几乎没有修改操作,我们可以将其重建成广播表:
--- 修改后 ---
CREATE TABLE `nation` (
`n_nationkey` int(11) NOT NULL,
`n_name` varchar(25) NOT NULL,
`n_regionkey` int(11) NOT NULL,
`n_comment` varchar(152) DEFAULT NULL,
PRIMARY KEY (`n_nationkey`)
) BROADCAST; --- 声明为广播表
修改后,可以看到执行计划中不再出现 Join——几乎所有计算都被下推到 MySQL 执行了(LogicalView 中),而上层仅仅是将结果收集并返回给用户(Gather 算子),执行性能大大增强。
> EXPLAIN select * from customer, nation where c_nationkey = n_nationkey and n_regionkey = 3;
Gather(concurrent=true)
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 下推更多算子:
> EXPLAIN select o_orderkey, c_custkey, c_name from orders, customer
where o_custkey = c_custkey and o_orderdate = '2019-11-11' and o_totalprice > 100;
Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
HashJoin(condition="o_custkey = c_custkey", type="inner")
Gather(concurrent=true)
LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
Gather(concurrent=true)
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
作为覆盖列包含在索引中用于避免回表
> create global index i_o_custkey on orders(`o_custkey`) covering(`o_orderdate`, `o_totalprice`)
DBPARTITION BY HASH(`o_custkey`) TBPARTITION BY HASH(`o_custkey`) TBPARTITIONS 4;
增加 GSI 并通过 force index(i_o_custkey)
强制使用索引后,跨分片 Join 变为 MySQL 上的局部 Join (IndexScan 中),并且通过覆盖列避免了回表操作,查询性能得到提升。
> EXPLAIN select o_orderkey, c_custkey, c_name from orders force index(i_o_custkey), customer
where o_custkey = c_custkey and o_orderdate = '2019-11-11' and o_totalprice > 100;
Gather(concurrent=true)
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 两边的代价
> EXPLAIN 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;
Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
HashJoin(condition="o_custkey = c_custkey", type="inner")
Gather(concurrent=true)
LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
Gather(concurrent=true)
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):
> EXPLAIN /*+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;
Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
BKAJoin(condition="o_custkey = c_custkey", type="inner")
Gather(concurrent=true)
LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")
Gather(concurrent=true)
LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer` WHERE (`c_custkey` IN ('?'))")
如果我们的思路没错的化,尝试执行一下加了 Hint 的查询:
/*+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';
这样一来,对于这条 SQL (参数可以不同),DRDS 都会采用我们固定下来的执行计划。你可以通过 EXPLAIN 验证这一点。为了节约篇幅,这里不再展示了。
更多关于执行计划管理的信息,可以参考文档“执行计划管理”。
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论