【走进RDS】之RDS PostgreSQL索引推荐原理及最佳实践

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS SQL Server,基础系列 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列


2、构建 Candidate Index


  • 从IndexableColumn中构建出所有可能的Candidate Index
  • Candidate Index分为单列索引和联合索引,单列索引包括所有Indexable Column,联合索引以一定规则组合Indexable Column


3、优化器What-If选择最优

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


2、优化器What-if能力


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


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


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



方案实现


1、总体流程


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

image.png

2、智能化索引推荐


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

image.png


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



2、查询单条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)


image.png

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


3、查询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是数据聚集后的结果,也需要对数据分布有所了解;


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


作者信息


赵锐,花名:惜元,专注于RDS PostgreSQL内核研发,热爱和分享PostgreSQL数据库相关技术。欢迎有志之士加入RDS产品部!联系邮箱:vogts.wangt@alibaba-inc.com

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
85 4
|
4月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
3月前
|
存储 监控 关系型数据库
B-tree不是万能药:PostgreSQL索引失效的7种高频场景与破解方案
在PostgreSQL优化实践中,B-tree索引虽承担了80%以上的查询加速任务,但因多种原因可能导致索引失效,引发性能骤降。本文深入剖析7种高频失效场景,包括隐式类型转换、函数包裹列、前导通配符等,并通过实战案例揭示问题本质,提供生产验证的解决方案。同时,总结索引使用决策矩阵与关键原则,助你让索引真正发挥作用。
194 0
|
6月前
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
6月前
|
自然语言处理 搜索推荐 关系型数据库
MySQL实现文档全文搜索,分词匹配多段落重排展示,知识库搜索原理分享
本文介绍了在文档管理系统中实现高效全文搜索的方案。为解决原有ES搜索引擎私有化部署复杂、运维成本高的问题,我们转而使用MySQL实现搜索功能。通过对用户输入预处理、数据库模糊匹配、结果分段与关键字标红等步骤,实现了精准且高效的搜索效果。目前方案适用于中小企业,未来将根据需求优化并可能重新引入专业搜索引擎以提升性能。
258 5
|
2月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
2月前
|
SQL 关系型数据库 MySQL
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
|
3月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
96 9
|
4月前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
99 12

相关产品

  • 云数据库 RDS
  • 云数据库 RDS PostgreSQL 版
  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多