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&#61;&#34;$16", f5w0$o0&#61;&#34;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
相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
5月前
|
SQL 存储 关系型数据库
SQL优化策略与实践:组合索引与最左前缀原则详解
本文介绍了SQL优化的多种方式,包括优化查询语句(避免使用SELECT *、减少数据处理量)、使用索引(创建合适索引类型)、查询缓存、优化表结构、使用存储过程和触发器、批量处理以及分析和监控数据库性能。同时,文章详细讲解了组合索引的概念及其最左前缀原则,即MySQL从索引的最左列开始匹配条件,若跳过最左列,则索引失效。通过示例代码,展示了如何在实际场景中应用这些优化策略,以提高数据库查询效率和系统响应速度。
171 10
|
6月前
|
SQL 运维 监控
SQL查询太慢?实战讲解YashanDB SQL调优思路
本文是Meetup第十期“调优实战专场”的第二篇技术文章,上一篇《高效查询秘诀,解码YashanDB优化器分组查询优化手段》中,我们揭秘了YashanDB分组查询优化秘诀,本文将通过一个案例,助你快速上手YashanDB慢日志功能,精准定位“慢SQL”后进行优化。
|
6月前
|
SQL 索引
【YashanDB知识库】字段加上索引后,SQL查询不到结果
【YashanDB知识库】字段加上索引后,SQL查询不到结果
|
3月前
|
SQL 人工智能 关系型数据库
GitHub 热门!MindsDB 破解 AI + 数据库瓶颈,究竟有什么惊艳亮点?只需 SQL 即可实现智能预测
MindsDB 是一款将 AI 能力直接注入数据库的开源工具,支持 MySQL、PostgreSQL 等多种数据库连接,通过 SQL 即可完成模型训练与预测。它提供 AutoML 引擎、LLM 集成、联邦查询等功能,简化 MLOps 流程,实现数据到智能的无缝衔接。项目在 GitHub 上已获 32.4k 星,社区活跃,适用于客户流失预警、推荐系统、情感分析等场景。开发者无需深入模型细节,即可快速构建智能解决方案。项目地址:https://github.com/mindsdb/mindsdb。
228 0
|
5月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
5月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
7月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
162 2
|
SQL Oracle 关系型数据库
SQL调优技巧:统计信息(文末福利)
统计信息类似于战争中的侦察兵,如果情报工作没有做好,打仗就会输掉战争。同样的道理,如果没有正确地收集表的统计信息,或者没有及时地更新表的统计信息,SQL的执行计划就会跑偏,SQL也就会出现性能问题。收集统计信息是为了让优化器选择最佳执行计划,以最少的代价(成本)查询出表中的数据。
2301 0

热门文章

最新文章