SQL调优指南—智能索引推荐-阿里云开发者社区

开发者社区> -技术小能手-> 正文

SQL调优指南—智能索引推荐

简介: 索引优化通常需要依赖运维或开发人员对数据库引擎内部优化和执行原理的深入理解。为优化体验和降低操作门槛,PolarDB-X推出了基于代价优化器的索引推荐功能,可根据查询语句分析并推荐索引,帮助您降低查询耗时,提升数据库性能。
+关注继续查看

注意事项

索引推荐功能仅针对您当前指定的SQL查询语句进行分析与推荐。在根据推荐的信息创建索引前,您需要评估创建该索引对其它查询的影响。

环境说明

TPC-H是业界常用的基准测试方法,由TPC委员会制定发布,用于评测数据库的分析型查询能力。TPC-H基准测试方法包含8张数据表、22条复杂的SQL查询(即Q1~Q22)。下图为执行TPC-H中的Q17(小订单收入查询)的返回信息,可查看到执行该查询语句消耗的时间为28.76秒。本文将通过智能索引推荐功能,优化该查询语句的执行效率。

  1. 查询智能索引推荐信息如需查询某个查询语句的智能索引推荐信息,您只需在该查询语句前增加EXPLAIN ADVISOR命令,示例如下:
EXPLAIN ADVISOR
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);
  1. 执行上述命令后,PolarDB-X将返回推荐的索引创建语句、添加索引前后的代价等信息,详细的返回信息及其注释如下所示:说明
    • 本案例中,预计磁盘I/O提升百分比为3024.7%,表明使用推荐的索引将带来较大的收益。
    • 当PolarDB-X无法推荐索引时,返回信息中会建议您在业务低峰期,对目标表执行Analyze Table命令刷新统计信息(该操作会消耗较大的I/O资源)。当统计信息更新后,再次执行索引推荐可获得更准确的索引。SQL复制代码。
IMPROVE_VALUE: 2465.3%        # 预计综合代价提升百分比
  IMPROVE_CPU: 59377.4%       # 预计CPU提升百分比
  IMPROVE_MEM: 0.4%           # 预计内存提升百分比
   IMPROVE_IO: 3024.7%        # 预计磁盘I/O提升百分比
  IMPROVE_NET: 2011.1%        # 预计网络传输提升百分比
 BEFORE_VALUE: 4.711359845E8  # 添加索引前综合代价值
   BEFORE_CPU: 1.19405577E7   # 添加索引前CPU估算值
   BEFORE_MEM: 426811.2       # 添加索引前内存消耗估算值
    BEFORE_IO: 44339          # 添加索引前磁盘I/O估算值
   BEFORE_NET: 47.5           # 添加索引前网络传输估算值
  AFTER_VALUE: 1.83655008E7   # 添加索引后综合代价值
    AFTER_CPU: 20075.8        # 添加索引后CPU估算值
    AFTER_MEM: 425016         # 添加索引后内存消耗估算值
     AFTER_IO: 1419           # 添加索引后磁盘I/O估算值
    AFTER_NET: 2.2            # 添加索引后网络传输估算值
 ADVISE_INDEX: ALTER TABLE `lineitem` ADD  INDEX `__advise_index_lineiteml_partkey`(`l_partkey`);
/* ADVISE_INDEX中的内容为推荐的索引创建语句 */
     NEW_PLAN:                # 添加索引后预计执行计划
Project(avg_yearly="$f0 / ?0")
  HashAgg($f0="SUM(l_extendedprice)")
    Filter(condition="l_quantity < $16 * f17w0$o0")
      SortWindow(p_partkey="p_partkey", l_partkey="l_partkey", l_quantity="l_quantity", l_extendedprice="l_extendedprice", $16="$16", f5w0$o0="window#0AVG($2)", Reference Windows="window#0=window(partition {1} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED PRECEDING aggs [AVG($2)])")
        MemSort(sort="l_partkey ASC")
          BKAJoin(condition="l_partkey = p_partkey", type="inner")
            Gather(concurrent=true)
              LogicalView(tables="[0000,0001].part", shardCount=2, sql="SELECT `p_partkey` FROM `part` AS `part` WHERE ((`p_brand` = ?) AND (`p_container` = ?))")
            Gather(concurrent=true)
              LogicalView(tables="[0000,0001].lineitem", shardCount=2, sql="SELECT `l_partkey`, `l_quantity`, `l_extendedprice`, ? AS `$16` FROM `lineitem` AS `lineitem` WHERE (`l_partkey` IN (...))")
         INFO: LOCAL_INDEX    # 其它信息
  1. 根据推荐信息创建索引
    1. 评估创建该索引带来的收益,然后根据返回结果ADVISE_INDEX中的SQL语句创建索引。
ALTER TABLE `lineitem` ADD  INDEX `__advise_index_lineiteml_partkey`(`l_partkey`);
    1. 再次执行TPC-H中的Q17(小订单收入查询),耗时减少至1.41秒,查询效率得到大幅提升。44.png

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mybatis SQL性能调优
Mybatis SQL性能调优         1.  Mapper层参数为Map,由Service层负责重载       Mapper由于机制的问题,不能重载,参数一般设置成Map,但这样会使参数变得模糊,如果想要使代码变得清晰,可以通过service层来实现重载的目的,对外提供的Service层是重载的,但这些重载的Service方法其实是调同一个Mapper,只不过相应
12007 0
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
6915 0
sql server 索引阐述系列七 索引填充因子与碎片
原文:sql server 索引阐述系列七 索引填充因子与碎片 一.概述     索引填充因子作用:提供填充因子选项是为了优化索引数据存储和性能。 当创建或重新生成索引时,填充因子的值可确定每个叶级页上要填充数据的空间百分比,以便在每一页上保留一些剩余存储空间作为以后扩展索引的可用空间,例如:指定填充因子的值为 80 表示每个叶级页上将有 20% 的空间保留为空,以便随着向基础表中添加数据而为扩展索引提供空间。
797 0
sql server 索引阐述系列四 表的B-Tree组织
原文:sql server 索引阐述系列四 表的B-Tree组织 一.概述     说到B-tree组织,就是指索引,它可以提供了对数据的快速访问。索引使数据以一种特定的方式组织起来,使查询操作具有最佳性能。
846 0
PostgreSQL 11 新特性解读:分区表支持创建主键、外键、索引
PostgreSQL 10 版本虽然支持创建范围分区表和列表分区表,但创建过程依然比较繁琐,需要手工定义子表索引、主键,详见 PostgreSQL10:重量级新特性-支持分区表,PostgreSQL 11 版本得到增强,在父表上创建索引、主键、外键后,子表上将自动创建,本文演示这三种场景。
4607 0
sql server 索引阐述系列八 统计信息
原文:sql server 索引阐述系列八 统计信息 一.概述     sql server在快速查询值时只有索引还不够,还需要知道操作要处理的数据量有多少,从而估算出复杂度,选择一个代价小的执行计划,这样sql server就知道了数据的分布情况。
767 0
SQL性能调优实践&mdash;&mdash;SELECT COUNT
最近想深入学习SQL,在网上搜索到一些SQL 优化的资料要么是张冠李戴,Oracle 优化的资料硬是弄成啦MS SQL 优化的资料,而且被很多人转载,收藏,有些要么有些含糊不清,好像是那么回事,也没经过验证,实践出真知!下面是我对SELECT COUNT(*), SELECT COUNT(1),SELECT COUNT (0), SELECT COUNT(Field)等孰优孰劣的测试结果,如果测试方法有什么不足,也希望大家给点建议。
1038 0
1280
文章
0
问答
来源圈子
更多
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载