PostgreSQL的B-tree索引(上)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: PostgreSQL的B-tree索引


B-tree索引适合用于存储排序的数据。对于这种数据类型需要定义大于、大于等于、小于、小于等于操作符。

通常情况下,B-tree的索引记录存储在数据页中。叶子页中的记录包含索引数据(keys)以及指向heap tuple记录(即表的行记录TIDs)的指针。内部页中的记录包含指向索引子页的指针和子页中最小值。

B-tree有几点重要的特性:

1、B-tree是平衡树,即每个叶子页到root页中间有相同个数的内部页。因此查询任何一个值的时间是相同的。

2、B-tree中一个节点有多个分支,即每页(通常8KB)具有许多TIDs。因此B-tree的高度比较低,通常4到5层就可以存储大量行记录。

3、索引中的数据以非递减的顺序存储(页之间以及页内都是这种顺序),同级的数据页由双向链表连接。因此不需要每次都返回root,通过遍历链表就可以获取一个有序的数据集。

下面是一个索引的简单例子,该索引存储的记录为整型并只有一个字段:

该索引最顶层的页是元数据页,该数据页存储索引root页的相关信息。内部节点位于root下面,叶子页位于最下面一层。向下的箭头表示由叶子节点指向表记录(TIDs)。


等值查询


例如通过"indexed-field = expression"形式的条件查询49这个值。

root节点有三个记录:(4,32,64)。从root节点开始进行搜索,由于32≤ 49 < 64,所以选择32这个值进入其子节点。通过同样的方法继续向下进行搜索一直到叶子节点,最后查询到49这个值。

实际上,查询算法远不止看上去的这么简单。比如,该索引是非唯一索引时,允许存在许多相同值的记录,并且这些相同的记录不止存放在一个页中。此时该如何查询?我们返回到上面的的例子,定位到第二层节点(32,43,49)。如果选择49这个值并向下进入其子节点搜索,就会跳过前一个叶子页中的49这个值。因此,在内部节点进行等值查询49时,定位到49这个值,然后选择49的前一个值43,向下进入其子节点进行搜索。最后,在底层节点中从左到右进行搜索。

(另外一个复杂的地方是,查询的过程中树结构可能会改变,比如分裂)


非等值查询


通过"indexed-field ≤ expression" (or "indexed-field ≥ expression")查询时,首先通过"indexed-field = expression"形式进行等值(如果存在该值)查询,定位到叶子节点后,再向左或向右进行遍历检索。

下图是查询 n ≤ 35的示意图:

大于和小于可以通过同样的方法进行查询。查询时需要排除等值查询出的值。


范围查询


范围查询"expression1 ≤ indexed-field ≤ expression2"时,需要通过 "expression1 ≤ indexed-field =expression2"找到一匹配值,然后在叶子节点从左到右进行检索,一直到不满足"indexed-field ≤ expression2" 的条件为止;或者反过来,首先通过第二个表达式进行检索,在叶子节点定位到该值后,再从右向左进行检索,一直到不满足第一个表达式的条件为止。

下图是23 ≤ n ≤ 64的查询示意图:

案例


下面是一个查询计划的实例。通过demo database中的aircraft表进行介绍。该表有9行数据,由于整个表只有一个数据页,所以执行计划不会使用索引。为了解释说明问题,我们使用整个表进行说明。


demo=# select * from aircrafts;
  aircraft_code |        model        | range
 ---------------+---------------------+-------
  773           | Boeing 777-300      | 11100
  763           | Boeing 767-300      |  7900
 SU9           | Sukhoi SuperJet-100 |  3000
  320           | Airbus A320-200     |  5700
  321           | Airbus A321-200     |  5600
  319           | Airbus A319-100     |  6700
 733           | Boeing 737-300      |  4200
  CN1           | Cessna 208 Caravan  |  1200
 CR2           | Bombardier CRJ-200  |  2700
 (9 rows)
 demo=# create index on aircrafts(range);
 demo=# set enable_seqscan = off;

(更准确的方式:create index on aircrafts using btree(range),创建索引时默认构建B-tree索引。)

等值查询的执行计划:

1. demo=# explain(costs off) select * from aircrafts where range = 3000;
2.                     QUERY PLAN                     
3. ---------------------------------------------------
4.  Index Scan using aircrafts_range_idx on aircrafts
5.    Index Cond: (range = 3000)
6. (2 rows)

非等值查询的执行计划:

1. demo=# explain(costs off) select * from aircrafts where range < 3000;
2.                     QUERY PLAN                    
3. ---------------------------------------------------
4.  Index Scan using aircrafts_range_idx on aircrafts
5.    Index Cond: (range < 3000)
6. (2 rows)

范围查询的执行计划:

1. demo=# explain(costs off) select * from aircrafts
2. where range between 3000 and 5000;
3.                      QUERY PLAN                      
4. -----------------------------------------------------
5.  Index Scan using aircrafts_range_idx on aircrafts
6.    Index Cond: ((range >= 3000) AND (range <= 5000))
7. (2 rows)

排序


再次强调,通过index、index-only或bitmap扫描,btree访问方法可以返回有序的数据。因此如果表的排序条件上有索引,优化器会考虑以下方式:表的索引扫描;表的顺序扫描然后对结果集进行排序。


排序顺序


当创建索引时可以明确指定排序顺序。如下所示,在range列上建立一个索引,并且排序顺序为降序:


demo=# create index on aircrafts(range desc);


本案例中,大值会出现在树的左边,小值出现在右边。为什么有这样的需求?这样做是为了多列索引。创建aircraft的一个视图,通过range分成3部分:


1. demo=# create view aircrafts_v as
2. select model,
3.        case
4.            when range < 4000 then 1
5.            when range < 10000 then 2
6.            else 3
7.        end as class
8. from aircrafts;
9. 
10. 
11. demo=# select * from aircrafts_v;
12.         model        | class
13. ---------------------+-------
14.  Boeing 777-300      |     3
15.  Boeing 767-300      |     2
16.  Sukhoi SuperJet-100 |     1
17.  Airbus A320-200     |     2
18.  Airbus A321-200     |     2
19.  Airbus A319-100     |     2
20.  Boeing 737-300      |     2
21.  Cessna 208 Caravan  |     1
22.  Bombardier CRJ-200  |     1
23. (9 rows)

然后创建一个索引(使用下面表达式):


1. demo=# create index on aircrafts(
2.   (case when range < 4000 then 1 when range < 10000 then 2 else 3 end),
3.   model);


现在,可以通过索引以升序的方式获取排序的数据:


1. demo=# select class, model from aircrafts_v order by class, model;
2.  class |        model        
3. -------+---------------------
4.      1 | Bombardier CRJ-200
5.      1 | Cessna 208 Caravan
6.      1 | Sukhoi SuperJet-100
7.      2 | Airbus A319-100
8.      2 | Airbus A320-200
9.      2 | Airbus A321-200
10.      2 | Boeing 737-300
11.      2 | Boeing 767-300
12.      3 | Boeing 777-300
13. (9 rows)
14. 
15. 
16. demo=# explain(costs off)
17. select class, model from aircrafts_v order by class, model;
18.                        QUERY PLAN                       
19. --------------------------------------------------------
20.  Index Scan using aircrafts_case_model_idx on aircrafts
21. (1 row)

同样,可以以降序的方式获取排序的数据:

1. demo=# select class, model from aircrafts_v order by class desc, model desc;
2.  class |        model        
3. -------+---------------------
4.      3 | Boeing 777-300
5.      2 | Boeing 767-300
6.      2 | Boeing 737-300
7.      2 | Airbus A321-200
8.      2 | Airbus A320-200
9.      2 | Airbus A319-100
10.      1 | Sukhoi SuperJet-100
11.      1 | Cessna 208 Caravan
12.      1 | Bombardier CRJ-200
13. (9 rows)
14. demo=# explain(costs off)
15. select class, model from aircrafts_v order by class desc, model desc;
16.                            QUERY PLAN                            
17. -----------------------------------------------------------------
18.  Index Scan BACKWARD using aircrafts_case_model_idx on aircrafts
19. (1 row)

然而,如果一列以升序一列以降序的方式获取排序的数据的话,就不能使用索引,只能单独排序:

1. demo=# explain(costs off)
2. select class, model from aircrafts_v order by class ASC, model DESC;
3.                    QUERY PLAN                    
4. -------------------------------------------------
5.  Sort
6.    Sort Key: (CASE ... END), aircrafts.model DESC
7.    ->  Seq Scan on aircrafts
8. (3 rows)

(注意,最终执行计划会选择顺序扫描,忽略之前设置的enable_seqscan = off。因为这个设置并不会放弃表扫描,只是设置他的成本----查看costs on的执行计划)

若有使用索引,创建索引时指定排序的方向:

1. demo=# create index aircrafts_case_asc_model_desc_idx on aircrafts(
2.  (case
3.     when range < 4000 then 1
4.     when range < 10000 then 2
5.     else 3
6.   end) ASC,
7.   model DESC);
8. 
9. 
10. demo=# explain(costs off)
11. select class, model from aircrafts_v order by class ASC, model DESC;
12.                            QUERY PLAN                            
13. -----------------------------------------------------------------
14.  Index Scan using aircrafts_case_asc_model_desc_idx on aircrafts
15. (1 row)
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
16天前
|
存储 NoSQL 关系型数据库
为什么MySQL不使用红黑树做索引
本文详细探讨了MySQL索引机制,解释了为何添加索引能提升查询效率。索引如同数据库的“目录”,在数据量庞大时提高查询速度。文中介绍了常见索引数据结构:哈希表、有序数组和搜索树(包括二叉树、平衡二叉树、红黑树、B-树和B+树)。重点分析了B+树在MyISAM和InnoDB引擎中的应用,并讨论了聚簇索引、非聚簇索引、联合索引及最左前缀原则。最后,还介绍了LSM-Tree在高频写入场景下的优势。通过对比多种数据结构,帮助理解不同场景下的索引选择。
53 6
|
13天前
|
SQL 关系型数据库 MySQL
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
|
7天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
39 3
Mysql(4)—数据库索引
|
1天前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理?
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理?
10 1
|
1月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
397 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
16天前
|
监控 关系型数据库 MySQL
MySQL数据表索引命名规范
MySQL数据表索引命名规范
26 1
|
16天前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
|
22天前
|
存储 关系型数据库 MySQL
MySQL中的索引及怎么使用
综上所述,MySQL索引的正确使用是数据库性能调优的关键一环。通过合理设计索引结构,结合业务需求和数据特性,可以有效提升数据库查询响应速度,降低系统资源消耗,从而确保应用的高效运行。
49 1
|
2天前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述,深入理解和有效实施这些索引优化策略,是解锁MySQL 8.0数据库高性能查询的关键。
9 0
|
1月前
|
关系型数据库 MySQL 数据库
MySQL删除全局唯一索引unique
这篇文章介绍了如何在MySQL数据库中删除全局唯一的索引(unique index),包括查看索引、删除索引的方法和确认删除后的状态。
64 9