vertica java_Vertica数据查询优化

定位问题

所谓的数据库调优、程序优化之类的工作，实际上是一个解决问题的过程，而解决问题，第一部就是需要定位问题。找到问题的手段多种多样，可以通过分析程序、监控生产上服务器的性能、定期生成数据库的负载报告等手段，而最不应该的就是通过生产上用户的反馈来反映问题了，因为到了那个时候，一切都已经晚了。具体到vertica来说，通过QUERY_PROFILES这个数据库本身提供的视图，可以找到耗时和执行的多的sql语句。以下三条sql语句，分别统计出执行次数top10，单次执行耗时top10，执行总耗时top10的sql语句。

SELECT

query,

count(*) as times

FROM

QUERY_PROFILES

WHERE

query_type = 'QUERY'

and query_start>='2015-02-13'

group by query

ORDER BY

times DESC limit 10;

SELECT

query,

avg(query_duration_us) as avg_cost

FROM

QUERY_PROFILES

WHERE

query_type = 'QUERY'

and query_start>='2015-02-13'

group by query

ORDER BY

avg_cost DESC limit 10;

SELECT

query,

sum(query_duration_us) as total_cost

FROM

QUERY_PROFILES

WHERE

query_type = 'QUERY'

and query_start>='2015-02-13'

group by query

ORDER BY

total_cost desc limit 10;

分析问题

数据库调优，其实非常依赖于数据库本身提供的各种性能分析工具，例如执行计划解释器，跟着profile工具。在vertica中，可以通过profile，分析一条具体的sql语句。我们分析一条第一步中获取到的sql语句：

获取到这个语句的transcation_id和 statement_id 以后，可以通过查询系统表 query_plan_profiles获得语句实际的执行计划和各个阶段的执行时间，这个不同于执行计划，这是真实的执行过程。如图：

sql的执行是从下往上的，在这个表里面列出了PATH ID,我们可以从PATH ID从大到小一步一步分析，每一步的执行耗时。注意PATH ID:4这一步，查询了一张事实表，cost是2K，处理了4M的数据。这一步就是我们分析的重点，因为它排在执行步骤的较前面并且处理了较多的数据。

解决问题

通过运行analyze_wordload，可以得到对某个表具体的优化建议。我们对，PATH ID:4的这个步骤查询的事实表，进行分析，可以得到如下优化建议：

其中第一条指的是，运行vertica提供的database designer工具，对这个事实表建立映射，此方法代价比较大，而且只能对特定的查询优化，这里进行第二条操作，对于此事实表进行分析，得到它的统计信息。

这条命令，只会访问此表10%的数据，返回0表示成功。进行了统计信息之后，重新执行第1步和第2步，得到新的计划如下：

可以观察到，执行步骤被调整了，原来PATH ID:4的步骤比较耗时，现在被提前到PATH ID:5了，而且执行的成本和消耗资源也不一样，以下是详细对比：

优化之前：

| | | +-- Outer -> STORAGE ACCESS for T330143 [Cost: 2K, Rows: 4M (NO STATISTICS)] (PATH ID: 4)

优化之后：

| | | +-- Outer -> STORAGE ACCESS for T330143 [Cost: 94, Rows: 18K] (PATH ID: 5)。

可见，对于事实表的dt自动进行分析以后，通过dt字段获取数据，扫描行数从4M减少到了18k，cost从2k减少了94，整个sql的执行时间也从0.34秒降低到了0.17秒。至此，此次优化得到了目的(执行时间减少了50%)。

分析背后的机制

为什么Vertica数据库获取了统计信息以后，就可以优化查询？因为这张事实表是按照dt字段进行分区，但是在没有统计信息的时候，即使查询条件带上了分区字段，仍然没有利用分区信息，通过查询v_monitor.query_events视图可以看到sql执行过程中发生的事件。优化之前：

优化之后：

其中PARTITIONS_ELIMINATED的描述Some storage containers will not be processed because they contain no relevant data说明分区信息起作用了。

总结

简单的vertica优化，一步的步骤如下：

收集sql统计信息，找出执行次数很多或者执行时间久的sql语句。

对要优化是sql进行profile，拿到此次执行profile的transaction_id和statement_id

利用vertica本身提供的工具和系统表，例如Datab Designer、Workload Analyzer、query_plan_profiles、v_monitor.query_events得到sql的执行计划和具体的优化建议。

对于查询的优化，从简单到复杂依次为：更新统计信息，运行database designer自动在表上建立projection(类似于物理视图)、手工建立projection 。

可以研究的地方

同样是分区表， 为什么在oracle中如果查询条件带上了分区健，就能正确的利用分区信息进行优化，而且vertica确不行呢？我猜可能有以下几点可能

建表方式不对，所谓的分区并没有起到作用。(类似于vertica中的主键约束)

vertica本身不支持，需要利用额外的统计信息去做优化。

因为应用是OLAP场景，每天导入的数据量过大，所以统计信息很快失效。