PolarDB-X 智能诊断之优化慢 SQL
背景
PolarDB-X作为一款商业化的分布式数据库,不仅注重强大的内核能力,而且还追求更加优秀的易用性,帮助用户更好地使用云原生分布式数据库。
传统单机数据库下,为了使数据库发挥出最大的性能,工程师们通常会设计出清晰的业务模型,减少JOIN操作,减少数据库的IO次数,增加索引等等方式。但是在使用分布式数据库情况下,数据分布在多个节点上,要优化查询性能,就有更多的方法选择:比如根据数据特点设计合适的拆分策略,尽量将JOIN操作下推,减少数据扫描行数,选择包括本地索引和全局二级索引在内的索引方案等等。
但是无论是单机数据库还是分布式数据库下,影响数据库性能的情况大部分和慢SQL相关,因此如何优化慢SQL是工程师们最常见的问题。慢SQL的优化方法一般先要准确地找到慢SQL,并且需要有该慢SQL的rt、返回行数、执行次数等等基本信息,可以帮助工程师快速判断慢SQL的影响范围和严重程度,然后进一步需要知道该慢SQL的真实执行计划,从而可以判断出数据库引擎可优化的具体执行计划算子,最后再根据基本信息和执行计划就可以分析出是需要添加索引,还是需要通过重新拆分数据或者强制指定算子等等方式来加速SQL语句。
根据以上思路,PolarDB-X智能诊断提供慢日志优化功能,可以帮助用户快速发现慢SQL,并且提供慢SQL的基本统计信息,还会帮用户分析出当时的执行计划,在此基础上更进一步地帮用户推荐出合适的索引优化方案,用户只需要一键复制便可直接执行索引创建操作来优化慢SQL。
原理简介
一条SQL会经过解析器、优化器、执行器,其中SQL的优化建议就是由优化器给出的,PolarDB-X优化器是以Volcano/Cascades模型作为框架的基于代价的优化器(Cost Based Optimizer),它可以为每一条SQL构造出搜索空间,并根据数据的统计信息,基数估计,算子代价模型为搜索空间中的执行机计划估算出执行所需要的代价(CPU/MEM/IO/NET),最终选出代价最小的执行计划作为SQL的具体执行方式。优化器详情可以参考: 《PolarDB-X CBO 优化器技术内幕》
简单来说,一条逻辑SQL可以生成多种物理执行计划,要在众多的物理执行计划中选择出一条最优的,就需要进行执行计划的代价估算,从而找到代价最小的那条物理执行计划。PolarDB-X优化器会根据代价模型来进行代价评估,其中CPU消耗量、内存占用量、磁盘IO次数、网络逻辑IO次数会作为代价估算的计算指标。
我们以该SQL举例说明索引推荐的过程,详细原理可以参考 《PolarDB-X 智能索引推荐技术尝鲜》:
SELECT sum(l_extendedprice) / 7.0 AS avg_yearly
FROM lineitem,
part
WHERE p_partkey = l_partkey
AND p_brand = 'Brand#23'
AND p_container = 'MED BOX'
AND l_quantity <
(SELECT 0.2 * avg(`l_quantity`)
FROM lineitem
WHERE l_partkey = p_partkey);
优化器首先会选择出WHERE条件中的字段p_partkey、p_brand、p_container、l_partkey、l_quantity,然后将这些字段进行组合,最多考虑2列组成的索引,然后根据生成的索引组合逐个计算执行代价,最终选择出最优的索引组合,生成一条索引创建语句,返回给用户。
在生成索引方案过程中,首先会考虑本地索引、全局二级索引和广播表的3种方式,基于优化器的what-if能力逐一计算出这3种方式的执行代价,选择出代价最小的方式,如果该3种方式中依然没有出现执行代价比原先代价小的方式,会再继续考虑全局覆盖索引的方式,取出join条件中所有字段组合成全局覆盖索引计算执行代价,如果最后依然没有推荐出合适索引,可以执行 ANALYZE TABLE table_name
的方式更新表统计信息后重试。
使用案例
案例一:计算节点CPU使用率陡增
当通过监控发现计算节点的CPU有陡增,可以在性能趋势页面拖拽选中对应时间段,然后点击诊断,可以快速进行慢日志的诊断,查看对应时间段的慢SQL详情。
点击“优化”按钮,即可以看到针对具体慢SQL的诊断优化建议。
案例二:数据节点CPU使用率陡增
当通过监控发现数据节点的CPU有陡增的情况,可以在慢日志页面查看数据节点的慢日志情况,通过慢日志明细可以查看发送到数据节点的慢日志信息,由于用户是无法直接修改发送到数据节点的SQL,因此需要查看对应的数据节点的SQL,在慢日志明细表格中点击对应行的“逻辑慢日志”即可查看对应的数据节点SQL,可以直接进行优化操作。
案例三:单一节点RT指标异常
当通过监控发现某一节点的RT指标和其他节点相差较大,可以通过慢日志排查该节点是否存在慢SQL,可以在慢日志页面点击对应的节点ID,跳转到单节点的慢日志页面,查看该节点的慢日志情况,并可针对具体慢SQL进行诊断优化。
References
《PolarDB-X 智能索引推荐技术尝鲜》
《PolarDB-X CBO 优化器技术内幕》
《云原生分布式数据库 PolarDB-X>用户指南>诊断与优化>慢日志》