sql server 索引阐述系列四 表的B-Tree组织

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文:sql server 索引阐述系列四 表的B-Tree组织一.概述     说到B-tree组织,就是指索引,它可以提供了对数据的快速访问。索引使数据以一种特定的方式组织起来,使查询操作具有最佳性能。
原文: sql server 索引阐述系列四 表的B-Tree组织

一.概述  

  说到B-tree组织,就是指索引,它可以提供了对数据的快速访问。索引使数据以一种特定的方式组织起来,使查询操作具有最佳性能。当数据表量变得越来越大,索引就变得十分明显,可以利用索引查找快速满足条件的数据行。某些情况还可以利用索引帮助对数据进行排序,组合,分组,筛选。

   一个B-tree,根是唯一的遍历的起点。中间页 层次数是根据表的行数以及索引行的大小而变化。索引中的底层节点称为叶节点。叶节点它容纳了一行或多行具有指定键值的记录,对于聚集或非聚集,叶节点都是按照键值的顺序组成,对于复合索引就是若干键值的组合。

  1.聚集索引

  在聚集索引的叶节点里不仅包含了索引键,还包含了数据页。也就是说数据本身也是聚集索引的一部分。聚集索引基于键值联系使表中的数据有序。决定哪个键值作为聚集键是重要因素,当遍历到叶级别时,可以获取数据本身,而不是简单地得到一个指向数据的指针(非聚集索引数据未覆盖)。聚集索引在 sys.partitions区中有一行,其中,索引使用每个分区的 index_id = 1,默认情况下,聚集索引是单个分区。如果聚集索引有四个分区,就有四个 B-tree 结构,每个分区中有一个 B-tree结构,关于分区在sql server 分区(上)中有讲到。由于数据页链只能按一种方式排序,因此表只有一个聚集索引,一般情况查询优化器非常倾向于采用聚集索引,因为可以直接在叶级别找到数据。  查询优化器也只需要在某一段范围的数据页,进行扫描。聚集索引结构按物理顺序存储不是磁盘上的顺序,聚集索引的排序顺序仅是表数据链在逻辑上有序的。

  2.非聚集索引

  非聚集索引与聚集索引有一个相似的 B -tree索引结构。不同的是,非聚集索引不影响数据行的顺序。什么意思呢,就是说非聚集索引,叶级别不包含全部的数据,只包含了键值以及,在每个叶节点中的索引行包含了一个书签(bookmark),书签在聚集索引里就是相应的数据行的聚集索引键,在堆里就是行标识符RID,该书签告诉sql server可以在哪里找到与索引键相应的数据行。
理解了非聚集索引叶节点不包含全部数据时,就知道非聚集索引的存在并不影响数据分页的组织,因此每张表上最多249个非聚集索引。
非聚集索引在 sys.partitions 区中有一行, 非聚集索引标识 index_id >1。默认情况下,一个非聚集索引一个分区。

二. 缺少索引与索引查找的区别

   在简单介绍了索引原理后,我们来直观感觉下索引在查询时的重要性。下面演示一个product表,表中的数据有12236142条,如果用户根据表中的型号(model)来搜索。下面来看看缺少索引(没有使用到索引),以及索引查找(就是应用到了索引功能)。二者的区别

  2.1 缺少索引的演示

--查询型号model 值STI5203 在全表中有三条
SELECT Model FROM dbo.Product WHERE Model='STI5203'

图下告诉我们缺少索引,如果加了索引将提高性能99.94%, 该查询扫描计数5 (扫描了5个区),逻辑读取次数为69951次(一次一页),耗时954毫秒。
执行计划告诉我们是索引扫描也叫缺少索引,索引名是ixUpByMemberID,注意索引扫描不是索引查找,索引扫描是说把索引组织上的页全部扫描了一遍。

  再通过下图我们清楚知道,ixUpByMemberID有5个区。5个区加起来的data_pages总页数是69730。上图逻辑读取是69951。相当于把索引中的页全部扫描了一遍。也可说是把12236142条数据全扫描了一次。

  在锁的介绍中我们知道,锁越多,发生阻塞和死锁的几率就越大。
  通过下图,对于page资源来说,就有IS锁(意向共享锁)上1000个。IS锁与X排它锁又不兼容,此时多用户在修改,删除表中数据时,将会发生阻塞或死锁的影响。

    总结:如果在生产环境,面对大数据表,条件查询很频繁,又缺失索引,系统整体性能将会被拖垮。

   2.2 查询索引查找的演示

  用户根据model查询,缺少了一个索引,在给model建立索引后,再来看

--查询型号model 值STI5203 在全表中有三条
SELECT Model FROM dbo.Product WHERE Model='STI5203'

  下图的执行计划告诉我们是索引查找,也就是索引使用上了,该索引名叫ix_mdoel. 扫描计数1 个区,逻辑读取次数为4次,耗时0毫秒.

  再来看下索引查找的锁状态,下图告诉我们,只有锁往了一个page资源。

  总结:在大表上,合理使用了索引查找后,不但查询响应时间变快了,而且没有了大量的锁,相应的在其它page页上的修改,删除应不会受到影响。

三. B-tree组织存储空间的影响

  我们知道了对于聚集索引,它的叶子层就是数据本身,但当一个表有多个非聚集索引时,就需要对数据库存储空间加倍来支持这些索引的存储,所以从占用存储空间来说,在建非聚集索引时需要好好规划。下面是来自生产环境的一个表,有聚集索引和四个非聚集索引,来看看索引存储空间
在index_id=1的聚集索引中占用的空间total_pages是1448806页,也就是表的数据本身。 而非聚集索引占用空间total_pages是2180034页, 非聚集索引占用空间比表数据本身大了1.5倍。

  

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
30天前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
59 2
|
19天前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
25天前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
2月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
489 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
2月前
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
|
24天前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
2月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
25天前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
25天前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤
|
28天前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引:详细步骤与技巧
在数据库管理和优化中,确认SQL查询是否有效利用了索引是提升性能的关键步骤