SQLSERVER 的主键索引真的是物理有序吗?

sqlserver,主键,索引,真的,物理,有序 · 浏览次数 : 637

小编点评

**总结** * 物理无序的实现目标是提升性能,但由于物理无序做成的逻辑有序操作无法实现物理有序。 * 逻辑有序可以利用 SQL Server 的物理无序操作,将表记录无序抽象成逻辑有序。 * 逻辑有序可以利用 Page 的 Slot 布局,将表记录无序抽象成逻辑有序。

正文

一:背景

1. 讲故事

最近在看 SQL SERVER 2008 查询性能优化,书中说当一个表创建了聚集索引,那么表中的行会按照主键索引的顺序物理排列,这里有一个关键词叫:物理排列,如果不了解底层原理,真的会被忽悠过去,其实仔细想一想不可能实现严格的 物理排列 ,那对性能是非常大的损害,本篇我们就从底层出发聊一聊到底是怎么回事。

二:原理探究

1. 我认为的物理排列

如果用 C# 代码来演示严格的物理排列,大概是这样的。


        static void Main(string[] args)
        {
            List<int> list = new List<int>() {1,2,4,5 };

            list.Insert(2, 3);

            Console.WriteLine(string.Join(",", list));
        }

从代码看我用 Insert 将 3 插入到了 list 集合中形成了物理有序,但不要忘了 Insert 的复杂度是 O(N),而且还要将 3 后面的数据整体挪动,可以参考源码中的 Array.Copy 方法。


public void Insert(int index, T item)
{
    if (_size == _items.Length)
    {
        EnsureCapacity(_size + 1);
    }
    if (index < _size)
    {
        Array.Copy(_items, index, _items, index + 1, _size - index);
    }
    _items[index] = item;
    _size++;
    _version++;
}

现在你可以想一想,如果我们每次在 Insert 的时候 SQLSERVER 都要将数据页上的数据往后挪,那这个性能有多差?

2. 观察聚集索引下的数据排序

为了方便讲述,先创建一个测试表,插入 4 条记录,再创建一个聚集索引,sql 代码如下:


IF OBJECT_ID('t') IS NOT NULL DROP TABLE t;
CREATE TABLE t (a CHAR(5), b INT)

INSERT INTO t(a,b) VALUES('aaaaa',1);
INSERT INTO t(a,b) VALUES('ddddd',4);
INSERT INTO t(a,b) VALUES('ccccc',3);
INSERT INTO t(a,b) VALUES('eeeee',5);

CREATE CLUSTERED INDEX idx_a ON t(a);

从图中看数据果然是有序的,严格的按照 a , c, d , e 排序,接下来用 dbcc 观察下在底层数据页上这几条记录是不是物理有序的? 查询 SQL 如下:


DBCC TRACEON(3604)
DBCC IND(MyTestDB,t,-1)
DBCC PAGE(MyTestDB,1,472,2)

Page数据页的输出结果如下:


PAGE: (1:472)

PAGE HEADER:


Page @0x000002C6E75D0000

m_pageId = (1:472)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 269   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594055557120                                
Metadata: PartitionId = 72057594048348160                                Metadata: IndexId = 1
Metadata: ObjectId = 850102069      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 13                        m_slotCnt = 4                       m_freeCnt = 8024
m_freeData = 160                    m_reservedCnt = 0                   m_lsn = (49:1616:23)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x40 ALLOCATED   0_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x000000DF137F8000

000000DF137F8000:   01010000 04000001 00000000 00000d00 00000000  ....................
000000DF137F8014:   00000400 0d010000 581fa000 d8010000 01000000  ........X...........
000000DF137F8028:   31000000 50060000 17000000 00000000 00000000  1...P...............
000000DF137F803C:   00000000 01000000 00000000 00000000 00000000  ....................
000000DF137F8050:   00000000 00000000 00000000 00000000 10000d00  ....................
000000DF137F8064:   61616161 61010000 00030000 10000d00 63636363  aaaaa...........cccc
000000DF137F8078:   63030000 00030000 10000d00 64646464 64040000  c...........ddddd...
000000DF137F808C:   00030000 10000d00 65656565 65050000 00030000  ........eeeee.......
000000DF137F80A0:   00002121 21212121 21212121 21212121 21212121  ..!!!!!!!!!!!!!!!!!!
...

Memory Dump 区节的内存地址看,这四条记录果然是有序的,

3. 真的按照物理有序吗

接下来就是关键了,到底是不是物理有序,我们再插入一条 bbbbb 记录,看下会不会将 ccccc 所在的内存地址上的内容整体往后挪?测试的 sql 语句如下:


INSERT INTO t(a,b) VALUES('bbbbb',2);
SELECT * FROM t;

从图片看,貌似真的给塞进去了,那到底是不是这样呢? 带着好奇心再次观察下底层的索引数据页


PAGE: (1:472)

PAGE HEADER:


Page @0x000002C6D76C4000

m_pageId = (1:472)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 269   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594055557120                                
Metadata: PartitionId = 72057594048348160                                Metadata: IndexId = 1
Metadata: ObjectId = 850102069      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 13                        m_slotCnt = 5                       m_freeCnt = 8006
m_freeData = 176                    m_reservedCnt = 0                   m_lsn = (49:1640:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 487522741              DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x40 ALLOCATED   0_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:

Memory Dump @0x000000DF0FDF8000

000000DF0FDF8000:   01010000 00000001 00000000 00000d00 00000000  ....................
000000DF0FDF8014:   00000500 0d010000 461fb000 d8010000 01000000  ........F...........
000000DF0FDF8028:   31000000 68060000 02000000 00000000 00000000  1...h...............
000000DF0FDF803C:   b5010f1d 01000000 00000000 00000000 00000000  ....................
000000DF0FDF8050:   00000000 00000000 00000000 00000000 10000d00  ....................
000000DF0FDF8064:   61616161 61010000 00030000 10000d00 63636363  aaaaa...........cccc
000000DF0FDF8078:   63030000 00030000 10000d00 64646464 64040000  c...........ddddd...
000000DF0FDF808C:   00030000 10000d00 65656565 65050000 00030000  ........eeeee.......
000000DF0FDF80A0:   10000d00 62626262 62020000 00030000 00002121  ....bbbbb.........!!
000000DF0FDF80B4:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
...
000000DF0FDF9FF4:   21219000 80007000 a0006000                    !!....p...`.

OFFSET TABLE:

Row - Offset                        
4 (0x4) - 144 (0x90)                
3 (0x3) - 128 (0x80)                
2 (0x2) - 112 (0x70)                
1 (0x1) - 160 (0xa0)                
0 (0x0) - 96 (0x60)      

Memory Dump 节的内存地址看,bbbbb 并没有插入到 aaaaa 和 cccccc 之间,而是写入到页面尾部的空闲空间中,接下来就有一个问题了,为什么 sql 输出中是有序的呢?怎么做到的? 如果你了解 Page 的 Slot 布局,你会发现 Slot1 指向的就是 bbbbb 这条记录的首地址,画一张图就是这样。

从图中我们就明白了最终的原理,当 Insert 时,SQLSERVER 并没有对表记录重排,而只是将指向的 Slot 槽位进行了重排,将物理无序做成了一种逻辑有序。

三:总结

其实大家只要往高性能上想,肯定不会实现物理有序的,太伤性能了,在 物理无序 上抽象出一层 逻辑有序 不失为一种好办法。

与SQLSERVER 的主键索引真的是物理有序吗?相似的内容:

SQLSERVER 的主键索引真的是物理有序吗?

一:背景 1. 讲故事 最近在看 SQL SERVER 2008 查询性能优化,书中说当一个表创建了聚集索引,那么表中的行会按照主键索引的顺序物理排列,这里有一个关键词叫:物理排列,如果不了解底层原理,真的会被忽悠过去,其实仔细想一想不可能实现严格的 物理排列 ,那对性能是非常大的损害,本篇我们就从

[转帖]SQL Server 体系结构中的2个主要引擎 说明

2020-03-18 16:2321450原创SQLServer 本文链接:https://www.cndba.cn/dave/article/4498 SQL Server 由两个主要引擎组成∶关系引擎(relational engine)和存储引擎(storage engine)。 1 关系引擎

[转帖]SQL Server 不同版本之间的 区别说明

2021-05-12 23:5062070原创SQLServer 本文链接:https://www.cndba.cn/dave/article/4527 SQL Server 数据库版本也是在不断的进行迭代。目前主流存在的版本有:SQL Server 2008、2012、2014、2016、2017

基于SqlSugar的开发框架循序渐进介绍(27)-- 基于MongoDB的数据库操作整合

SqlSugar的开发框架本身主要是基于常规关系型数据库设计的框架,支持多种数据库类型的接入,如SqlServer、MySQL、Oracle、PostgreSQL、SQLite等数据库,非关系型数据库的MongoDB数据库也可以作为扩展整合到开发框架里面,通过基类的继承关系很好的封装了相关的基础操作功能,极大的减少相关处理MongoDB的代码,并提供很好的开发效率。本篇随笔介绍如何在SqlSuga

[转帖]Sql Server中通过sql命令获取cpu占用及产生锁的sql

https://www.jb51.net/article/266255.htm 这篇文章主要介绍了Sql Server中通过sql命令获取cpu占用及产生锁的sql,需要的朋友可以参考下 获取SQLSERVER中产生锁的SQL语句 1 2 3 4 5 6 7 8 9 10 11 12 13 14 1

构建高可用性的 SQL Server:Docker 容器下的主从同步实现

摘要:本文将介绍如何在 Docker 环境下搭建 MS SQL Server 的主从同步,帮助读者了解主从同步的原理和实现方式,进而提高数据的可靠性和稳定性。 一、前言 在当今信息化的时代,数据的安全性和稳定性显得尤为重要。数据库是许多企业和组织存储和管理数据的核心,因此如何保证数据库的高可用性和数

每日一库:GORM简介

GORM(Go Object-Relational Mapping)是一个用于Go语言的ORM库,它提供了一种简单、优雅的方式来操作数据库。GORM支持多种数据库,包括MySQL、PostgreSQL、SQLite和SQL Server。以下是GORM的一些主要特性: 1. **全功能ORM**:G

SQLSERVER 的复合索引和包含索引到底有啥区别?

一:背景 1. 讲故事 在 SQLSERVER 中有非常多的索引,比如:聚集索引,非聚集索引,唯一索引,复合索引,Include索引,交叉索引,连接索引,奇葩索引等等,当索引多了之后很容易傻傻的分不清,比如:复合索引 和 Include索引,但又在真实场景中用的特别多,本篇我们就从底层数据页层面厘清

SQLSERVER 的 nolock 到底是怎样的无锁?

一:背景 1. 讲故事 相信绝大部分用 SQLSERVER 作为底层存储的程序员都知道 nolock 关键词,即使当时不知道也会在踩过若干阻塞坑之后果断的加上 nolock,但这玩意有什么注意事项呢?这就需要了解它的底层原理了。 二:nolock 的原理 1. sql 阻塞还原 为了方便讲述,先创建

SQLSERVER 的四个事务隔离级别到底怎么理解?

一:背景 1. 讲故事 在有关SQLSERVER的各种参考资料中,经常会看到如下四种事务隔离级别。 READ UNCOMMITTED READ COMMITTED SERIALIZABLE REPEATABLE READ 随之而来的是大量的文字解释,还会附带各种 脏读, 幻读, 不可重复读 常常会把