Vertica数据查询优化doc.docxVIP

Vertica数据查询优化vertica是惠普公司推出的列式分布式数据库，在OLAP领域有其独到的地方，目前社区版免费，但是只能存放1T的数据。我在工作中维护的bi系统后端就是使用的vertica数据库，平时也经常需要对于数据库的查询进行一些优化。所以写下这篇博客记录一下。定位问题所谓的数据库调优、程序优化之类的工作，实际上是一个解决问题的过程，而解决问题，第一部就是需要定位问题。找到问题的手段多种多样，可以通过分析程序、监控生产上服务器的性能、定期生成数据库的负载报告等手段，而最不应该的就是通过生产上用户的反馈来反映问题了，因为到了那个时候，一切都已经晚了。具体到vertica来说，通过QUERY_PROFILES这个数据库本身提供的视图，可以找到耗时和执行的多的sql语句。以下三条sql语句，分别统计出执行次数top10，单次执行耗时top10，执行总耗时top10的sql语句。SELECT????query,????count(*) as?timesFROM????QUERY_PROFILESWHERE????query_type = QUERY????and??query_start=2015-02-13????group?by?queryORDER?BY????times DESC?limit 10;SELECT????query,???avg(query_duration_us) as?avg_costFROM????QUERY_PROFILESWHERE????query_type = QUERY????and??query_start=2015-02-13????group?by?queryORDER?BY???avg_cost DESC?limit 10;SELECT????query,???sum(query_duration_us) as?total_costFROM????QUERY_PROFILESWHERE????query_type = QUERY????and??query_start=2015-02-13????group?by?queryORDER?BY???total_costdesc??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字段进行分区，但是在没有统计信息的时候，即使查询条件带上了分区