RDS PostgreSQL索引推荐原理及最佳实践

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 前言很多开发人员都知道索引对于数据库的查询性能至关重要,一个好的索引能使数据库的性能提升成千上万倍。但给数据库加索引是一项相对专业的工作,需要对数据库的运行原理有一定了解。同时,加了索引有没有性能提升、性能提升了多少,这些都是加索引前就想知道的。这项繁杂的工作有没有更好的方案呢?有!就是今天重磅推出...

前言

很多开发人员都知道索引对于数据库的查询性能至关重要,一个好的索引能使数据库的性能提升成千上万倍。但给数据库加索引是一项相对专业的工作,需要对数据库的运行原理有一定了解。同时,加了索引有没有性能提升、性能提升了多少,这些都是加索引前就想知道的。这项繁杂的工作有没有更好的方案呢?有!就是今天重磅推出的索引推荐。

索引推荐这项技术概括起来就是通过分析SQL,枚举可能的索引组合,并通过优化器What-If的能力,选出其中收益最高的索引组合推荐给用户。索引推荐可以极大降低用户的使用门槛,增加数据库智能化能力。RDS PostgreSQL在新版本中已经自带索引推荐功能,可以通过访问PostgreSQL数据库亦或通过RDS控制台使用索引推荐功能。

技术原理

1. 索引推荐流程

  1. 分析 Indexable Column,分析出SQL中哪些列可以利用索引,例如:

  • Where条件中的 =, >, <, between, in等列

  • Order By的排序列

  • Group By的聚合列

  • MIN,MAX函数列

  • Join的Condition列

  1. 构建 Candidate Index

  • 从IndexableColumn中构建出所有可能的Candidate Index

  • Candidate Index分为单列索引和联合索引,单列索引包括所有Indexable Column,联合索引以一定规则组合Indexable Column

  1. 优化器What-If选择最优

  • 利用优化器What-If的能力,将Candidate Index逐一评估,通过{CPU cost + IO cost}衡量代价,最终选择出使得SQL执行代价最低的Candidate Index

2. 优化器What-If能力

PG查询优化是基于代价的,分为启动代价,运行代价,总代价,计算方式为{CPU cost + IO cost}。

  • 启动代价:读取到第一条元组前花费的代价,比如索引扫描节点的启动代价就是读取目标表的索引页,获取到第一个元组的代价。

  • 运行代价:获取全部元组的代价。

  • 总代价:二者之和。

索引的代价计算是由固定公式得来,只要构造索引时补充公式需要的变量,就可以利用到优化器的What-If能力。

方案实现

1. 总体流程

  1. 采用通用的索引推荐流程,注册planner_hook,遍历查询树,构造索引项,依赖优化器的What-If能力得到结果。

image.png
  1. 智能化索引推荐

RDS PG

索引准确性

综合考虑PG的优化器行为、DBA索引优化经验

产品特性

支持单SQL和Workload级别

易用性

EXPLAIN信息

"Index Scan using "<1>t_a_idx" on t (cost=0.42..2.64 rows=1 width=8)"

DEBUG信息输出具体的table name、column name

2. 详细设计

从查询树到candidate index

针对一条SQL,我们利用内核构造的查询树,精确找到哪些列可以成为索引,制造出索引候选项,交由优化器选择。

image.png

最佳实践

1、从RDS控制台进行可视化操作

进入RDS实例详情页面 -> 自治服务 -> 慢SQL ,可以在此处获得相关操作指引。

image.png

2、实操步骤

  1. 创建表

CREATE TABLE t( a INT, b INT );
INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
ANALYZE t;
所生成的表包含以下各行:
   a   |   b
-------+-------
     0 | 99999
     1 | 99998
     2 | 99997
     3 | 99996
       .
       .
       .
 99997 |     2
 99998 |     1
 99999 |     0

  1. 查询单条SQL建议说明

如果希望索引推荐分析查询并提出索引编制建议但不实际执行查询,将EXPLAIN关键字作为SQL语句的前缀,示例如下:

postgres=# EXPLAIN SELECT * FROM t WHERE a < 10000;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=9983 width=8)
   Filter: (a < 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..256.52 rows=9983 width=8)
         Index Cond: (a < 10000)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE a = 100;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (a = 100)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (a = 100)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE b = 10000;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (b = 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_b_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (b = 10000)
(6 rows)

可通过psql命令行查询index_advisory表内存储的索引编制建议,示例如下:

postgres=# SELECT * FROM index_advisory;
 reloid | relname | attrs | benefit | original_cost | new_cost | index_size | backend_pid |            timestamp
--------+---------+-------+---------+---------------+----------+------------+-------------+----------------------------------
  16438 | t       | {1}   | 1337.43 |          1693 |  355.575 |       2624 |       79370 | 18-JUN-21 08:55:51.492388 +00:00
  16438 | t       | {1}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:00.319336 +00:00
  16438 | t       | {2}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:07.814453 +00:00
(3 rows)

类型

说明

reloid

oid

索引的基表的 OID

relname

name

索引的基表的名称

attrs

integer[]

建议的索引列(由列编号标识)

benefit

real

此查询的索引的计算收益

original_cost

real

使用索引之前的平均代价(即执行SQL的预估时间)

new_cost

real

使用索引之后的平均代价(即执行SQL的预估时间)

index_size

integer

磁盘页中的估计索引大小

backend_pid

integer

生成此建议的进程ID

timestamp

timestamp

生成此建议的日期/时间

如果语句不带EXPLAIN关键字前缀,索引推荐将在语句执行期间分析语句并记录建议。

  1. 查询WorkLoad级别建议

通过show_index_advisory()函数获取单个会话的WorkLoad建议,此函数用于获取单个会话的索引推荐(由后端进程ID标识),可通过指定会话的进程ID来调用该函数:

SELECT show_index_advisory( pid );

其中,pid 是当前会话的进程 ID。如果不知道当前会话的进程 ID,则传递值 NULL 也将为当前会话返回结果集。

postgres=# SELECT show_index_advisory(null);
                                                             show_index_advisory
----------------------------------------------------------------------------------------------------------------------------------------------------
 create index idx_t_a on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
 create index idx_t_b on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)

说明 结果集中每行的表示意义如下:

  • 创建索引推荐建议的索引所需的SQL语句。

  • 索引页的估计大小。

  • 使用索引的总收益(benefit)。

  • 使用索引的增益(gain=benefit/size)。

  • 使用索引之前的平均代价(即执行SQL的预估时间)。

  • 使用索引之后的平均代价(即执行SQL的预估时间)。

通过select_index_advisory视图获取所有会话的WorkLoad建议,此视图包含计算的指标和CREATE INDEX语句,展示当前位于index_advisory表中所有会话的索引编制建议。表t中列a和列b的索引编制建议显示如下:

postgres=# SELECT * FROM select_index_advisory;
 backend_pid |                                                             show_index_advisory
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------
       79370 | create index idx_t_a on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
       79370 | create index idx_t_b on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)

在每个会话中,从同一建议的索引中受益的所有查询的结果将被组合起来,以便按每个建议的索引生成一组指标,此指标反映在名为benefit和gain的字段中,字段公式如下所示:

size = MAX(index size of all queries)
benefit = SUM(benefit of each query)
gain = SUM(benefit of each query) / MAX(index size of all queries)

说明 如果单条SQL建议同时创建多个索引,则index_advisory表中记录的new_cost为创建了多个索引之后的代价,而非创建某一个索引之后的代价。

当对给定会话期间得到的不同建议索引的相对优势进行比较时,gain指标十分有用。gain值越大,从索引中得到的成本效益就越高,这可以抵消索引可能消耗的磁盘空间。

未来展望

阿里云RDS PostgreSQL的索引推荐功能未来还会朝着以下几个方面进行扩展:

  1. 支持GIN、GIST、BRIN索引的推荐。BRIN索引为block索引,对于无法评估数据分布的场景无法推荐;GIST是数据聚集后的结果,也需要对数据分布有所了解;

  2. WorkLoad级别的推荐可以更加细化,当前是以benefit做聚合和排序,得出索引推荐,后续可以更加精细化。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
SQL 数据库 数据库管理
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
142 11
|
2月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
57 1
|
2月前
|
存储 C# 关系型数据库
“云端融合:WPF应用无缝对接Azure与AWS——从Blob存储到RDS数据库,全面解析跨平台云服务集成的最佳实践”
【8月更文挑战第31天】本文探讨了如何将Windows Presentation Foundation(WPF)应用与Microsoft Azure和Amazon Web Services(AWS)两大主流云平台无缝集成。通过具体示例代码展示了如何利用Azure Blob Storage存储非结构化数据、Azure Cosmos DB进行分布式数据库操作;同时介绍了如何借助Amazon S3实现大规模数据存储及通过Amazon RDS简化数据库管理。这不仅提升了WPF应用的可扩展性和可用性,还降低了基础设施成本。
56 0
|
2月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
132 0
|
2月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
165 0
|
5月前
|
关系型数据库 OLAP Linux
ADB PG最佳实践之高效复制数据到RDS PG
ADB PG是一个经典MPP数据库,长项在于查询分析处理,面对客户联机分析和联机交易(HTAP)场景就显得力不从心,我们在某银行核心系统DB2 for LUW迁移到ADB PG时就遇到类似问题,因此我们提出ADB PG+RDS PG混搭技术架构,来解决客户此类HTAP需求。该混搭架构的精髓在于扬长避短,充分发挥分析型数据库和交易型数据库的长处和特性,分析型数据库专注于数据加工跑批场景,然后批量加工的结果数据卸载到RDS PG,通过RDS PG对外提供高并发对客交易服务。
238 0
ADB PG最佳实践之高效复制数据到RDS PG
|
5月前
|
存储 搜索推荐 关系型数据库
基于阿里云RDS PostgreSQL打造实时用户画像推荐系统(varbitx))
用户画像在市场营销的应用重建中非常常见,已经不是什么新鲜的东西,比较流行的解决方案是给用户贴标签,根据标签的组合,圈出需要的用户。通常画像系统会用到宽表,以及分布式的系统。宽表的作用是存储标签,例如每列代表一个标签。但实际上这种设计不一定是最优或唯一的设计,本文将以PostgreSQL数据库为基础,给大家讲解一下更加另类的设计思路,并且看看效率如何。
290 0
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
348 0
|
存储 缓存 关系型数据库
|
存储 SQL 并行计算
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(中)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
399 0

热门文章

最新文章

下一篇
无影云桌面