全部产品
云市场

SQL调优概览

更新时间:2019-09-18 13:14:39

SQL调优的目的

SQL调优,是通过综合分析SQL的执行计划、执行监控信息、系统配置、系统负载等多方面因素,调整SQL的执行策略,以使其符合预期的过程。

SQL调优的目的一般可分为两类:

1. 针对单一SQL的调优

针对单一SQL的调优的优化主体是被调试的SQL执行本身,一般调优的目标可以包括该SQL的执行时间、试行期的资源消耗等等,常见的调优手段包括调整访问路径、执行顺序、逻辑改写等等。

2. 针对系统的SQL调优

针对系统的SQL调优过程往往比较复杂,需要综合分析当前系统的负载特征,结合多条SQL的执行计划分析问题的症结所在,目标往往是提高整个系统的吞吐量或者系统利用率等等,关注的问题也往往超越了单条SQL的执行计划,例如热点行竞争、buffer cache命中率等全局性的调优点。

SQL问题来源

1.用户SQL写法

用户SQL的写法对SQL的执行性能往往有决定性的作用。在使用过程中,用户应尽量遵循OceanBase开发规范的要求。

2.代价模型缺陷

最佳的执行计划生成依赖OceanBase内建的代价模型,代价模型是server的固有逻辑。因此,一旦出现由代价模型导致的计划选择错误,用户只能通过执行计划绑定来确保选择“正确”的执行计划。

3.统计信息不准确

查询优化过程依赖数据统计信息的准确性,OceanBase优化器默认会在数据合并过程中收集一些统计信息,当用对数据进行了大量修改时,可能会导致统计信息落后于真实数据的特征,用户可以通过发起每日合并,主动更新统计信息。

除了优化器收集的统计信息以外,优化器还会根据查询条件对存储层进行采样,用以后续的优化选择。OceanBase目前仅支持对本地存储进行采样,对于数据分区在远程节点上的情况,只能使用默认收集的统计信息进行代价估计,可能会引入代价偏差。

4.数据库物理设计

查询的性能很大程度上取决于数据库的物理设计,包括所访问对象的schema信息等。例如,对于二级索引,如果所需的投影列没有包括在索引列之中,则需要使用回表的机制访问主表,查询的代价会增加很多。此时,可以考虑将用户的投影列加入到索引列中,构成所谓的“覆盖索引”,避免回表访问。

5.系统负载

系统的整体负载除了会影响系统的整体吞吐量,也会引起单条SQL的响应时间变化。OceanBase的SQL引擎采用队列模型,针对用户请求,如果可用线程全部被占用,则新的请求需要在请求队列中排队,直到某个线程完成当前请求。请求在队列中的排队时间可以在(g)v$sql_audit中看到。

6.客户端路由

ObProxy的一个主要功能是将SQL查询路由到恰当的server节点。具体来说,如果用户查询没有指定使用弱一致性读属性,proxy需要将其路由到所涉及的表(或具体分区)的主节点上,以避免server节点之前的二次转发;否则,proxy会根据预先设置好的规则将其转发到恰当的节点。

由于proxy与server之间采用松耦合的方式,proxy上缓存的数据物理分布信息刷新可能不及时,导致错误的路由选择。可能导致路由信息变化的场景有:

  1. 网络不稳导致server间重新选主。
  2. 由server上下线、轮转合并等导致的重新选主。
  3. 负载均衡导致重新选主。

当在SQL audit或plan cache中发现有大量远程执行时,需要考虑是否与上述场景吻合。客户端与server之间有路由反馈逻辑,一旦发生错误,客户端会主动刷新数据物理分布信息,随后路由的选择也将恢复正常。

OceanBase SQL调优与传统数据库的差异

SQL调优是数据库最常见的问题之一,相关的文档、手册、书籍也是长篇累牍。OceanBase作为关系型数据库,其调优的方法与思路与传统数据库有很多相似的地方,但也有一些由其自身特点造成的不同之处:

1.LSM-tree存储引擎

OceanBase​的存储引擎采用了两层LSM-tree的架构,数据分为静态数据(SSTable)和动态数据(MemTable)两部分。针对写请求,用户的修改按照BTree的方式写入内存中的MemTable,并定期通过合并过程合入到存储在磁盘上的SSTable中;针对读请求,存储引擎需要读取MemTable、SSTable两部分数据,合成最终的行。这一特点决定了OceanBase在基表访问路径的代价模型上与传统数据库有较大的差异。例如,MemTable为空(全部数据在SSTable中)时,存储引擎的执行效率比MemTable中有数据的场景的执行效率要高很多。因此,当数据合并后,SQL的执行效率往往都有明显的提升。

另外一个明显的例子是“buffer”表。“buffer”表是指那些被用户当做业务过程中的临时存储的数据表,这些表的访问模型往往是:写入-修改-删除,且整个过程周期很短,往往在几分钟或几个小时之内。由于OceanBase所有的DML操作都是“逻辑”的,数据被删除时,并不做原地修改,而只是在内存中做“标记”删除。因此,即使用户的行被删除了,访问对应的“删除”标记也需要花费一定的时间。OceanBase对此的应对是通过row purge,在BTree中标记一个范围段的删除标记以加速数据的访问。row purge的过程是异步的,因此,当有大量删除操作后立即访问被删除的数据范围,仍然有可能遇到由于访问标记删除节点而导致的执行变慢的问题。

2.分布式架构

OceanBase与传统数据库的另一个不同之处源于其分布式share-nothing架构。在传统的share-disk数据库中,执行计划并不区分数据所在的物理节点,所有的数据访问都可以认为是“本地”的。但在OceanBase中,不同的数据被存储在不同的节点上,SQL执行计划的生成必须考虑到数据的实际物理分布,并可能因此表现出不同的性能特征。例如,当连接两张表时,如果两个表的数据分布在不同的物理节点上,则必然涉及节点之间的数据传输,执行计划也变为分布式执行计划,相比两表数据分布在同一个节点上的场景,执行代价必然有所增加。

另一个常见的场景是数据的切主,由于数据的主可以在分布在不同节点上的副本之间进行切换,有可能出现之前的本地执行计划(所访问的数据在本机)变为远程执行计划或者分布式执行计划的问题,也可能增加一定的执行时间。这一问题在用户开启轮转合并功能之后尤其明显(在数据重新选主后,客户端路由可能由于数据物理位置信息刷新不及时而不准确)。