在上篇文章浅谈 SQL Server 内部运行机制 中, 与大家分享了 SQL Server 内部运行机制, 通过上次的分享, 相信大家已经能解决如下几个问题:
1.SQL Server 体系结构由哪几部分组成?
2.SQL Server 体系结构各模块之间关系是怎样的?
3.SQL Server 体系结构内部运行机制是怎样的?
4. 简单的一条 SELECT 语句, 在 SQL Server 中是如何一步一步执行的?
然而, 仅仅能解决如上几个问题, 是不具有 SQL Server 数据库优化能力的, 为什么这么说, 我们先提出如下几个问题:
1. 为什么会内存溢出?(上篇文章开篇提出的)
2. 为什么会产生死锁, 闩锁?
3. 什么叫执行计划, 如何分析执行计划?
4. Index Scan 与 Index Seek 区别?
5. 什么叫聚集索引和非聚集索引?
6. 什么叫堆和 B - 数?
7. 优化 SQL Server, 应该建立怎样的一套优化理念?
8.SQL Server 优化时, 常用的检测工具, 优化工具和优化手段都有哪些?
9. 你了解这些表与函数吗?
Sys.dm_exec_requests,Sys.dm_exec_sql_text,Sys.dm_exec_session,Sys.dm_exec_connections,Sys.dm_exec_query_stats,Sys.dm_exec_query_resource_semaphores
10. 为什么磁盘臂是 I/O 的最大开销?
11. 什么叫碎片, 为什么会产生碎片?
12. 什么叫跨域, 什么叫主从同步?
13. 为什么要分区, 为什么要拆表(水平拆分, 垂直拆分)?
..........
如上的这些基础问题, 若不能很好地解决, 就急忙去研究 SQL Server 优化, 甚至去实战, 是会绕很多弯路的, 且学得一知半解. 我们就拿索引举个例子, 一张 UserInfo(UserName,Address,Sex)有 1000 万条数据,
当我们查询时, 非常缓慢, 为了提高查询速度, 我们优先想到的是建立索引(其他条件不变情况下, 如不增加 CPU, 不增加内存, 不改变磁盘等), 有经验的 DBA 和数据库优化高手, 是不会选择 Addresss 和 sex 作为索引字
段的, 想想为什么?
基于如上的种种问题, 本篇文章还是继续从理论角度分析 SQL Server 的一些基本理论, 为后续的 SQL Server 优化实战打好良好的功底, 至于具体的优化实战, 应该会在第四篇或第五篇文章开始讲解(本篇文章
为 SQL Server 数据库优化系列第二篇), 本篇文章大致包括如下内容.(当然, 本篇文章未必能全部解决如上提出的问题, 但在 SQL Server 理论性问题介绍结束, 大家应该知道如何解决, 然后再去实战)
SQL Server 引擎及集群
SQL Server 数据文件存储
SQL Server table 表数据的存储形式
Page 的基本构成
若干基本概念: 堆(Heap), 分区, B-Tree, 行数据溢出, Master-Slave 等
一 SQL Server 引擎及集群
首先, 我们要知道什么叫做 SQL Server 服务器? SQL Server 服务器部署在服务器端, 用来存储数据的, 如系统数据 (如系统数据库 master,tempdb 等), 用户数据(如自定义数据库数据) 和日志数据 (如 Log Files) 等.
一般地, SQL Server 为我们提供了客户端访问工具 SSMS(Microsoft SQL Server Management Studio), 通过该工具, 我们能访问 SQL Server 服务器, 从而通过客户端 SQL 语句, 获取我们想要的数据, SQL Server 最为简
单的模式是: 客户端《=》服务器模式, 即只有一台 SQL Server 服务器, 供一个或多个客户端访问, 这种架构是最为简单的, 也是大部分小公司常用的架构. 下图为三个不同 IP 的 SQL Server 客户端工具 SSMS 访问同一个
SQL Server 服务器.
其次, 对于具有一定规模, 有一定数据量的公司, 单台 SQL Server 服务满足不了业务需求, 如系统访问速度慢 (一般用户能容忍的时间是 3 秒, 时间超过 3 秒, 用户就感觉不良好), 数据量大(单台 SQL Server 服务器无法支撑) 等,
这时, 就需要 2 台及以上 SQL Server 服务器(当然, 实际的架构中, 不仅仅是 SQL Server 服务器之间集群, 还有可能是 SQL Server 服务器, Oracle 服务器, MySQL 服务器之间跨服务器, 跨域的集群), 通过多台 SQL Server 服务器集群,
形成一个庞大的中央服务器, 来处理高并发, 大数据量, 访问速度等性能问题, 常见的是一个例子就是读写分离, 主从同步.
下图是在四个 IP 不同的服务器上分别部署一台 SQL Server 服务器引擎和每台服务器的 SQL Server 引擎包括的基本内容.
(一)SQL Server 引擎基本内容
1. 数据库
2. 安全性
3. 服务器对象
4. 复制
5.AlwaysON
6. 管理
7.Integration Services
(二)SQL Server 引擎之间关系
1. 跨域
2. 跨域数据主从同步
二 SQL Server 数据文件存储
在了解 SQL Server 服务器基本构成, SQL Server 服务器部署, SQL Server 服务器集群和客户端访问工具 SSMS 如何访问 SQL Server 服务器后, 接下来, 我们将目标定位在单个 SQL Server 服务器上, 研究
SQL Server 单个服务器, 通过第一部分, 我们知道单个 SQL Server 服务器的基本组成, 接下来, 我们来分析 SQL Server 服务器都有哪些文件, 且它们分别以什么样的形式存储, 存储在哪?
对于 SQL Server, 数据库文件和日志文件是其两大类核心文件.
数据文件主要用来存储相关数据的, 如系统数据库 (master,model,msdb,tempdb) 文件, 用户自定义数据库文件, 日志文件等.
1. 数据库文件主要包括两个核心文件 (.mdf - 文件为主要文件,.ndf - 文件为次要文件), 当创建数据库时, 系统默认会创建. mdf 文件和. ndf 文件, 这两个文件是以页(page) 的
方式存储的, 它们用来保存一些数据库对象, 如保存表数据, 索引数据, 约束等;
2. 日志文件(Virtual Log Files, 简称 VLF), 这种文件不是按照页的方式存储的, 换句说, 他们存储的大小是不确定的, 是任意的.
三 Table 表数据的存储形式
通过第二部分, 我们知道了 SQL Server 服务器主要有两大类资源文件, 即数据库文件和日志文件, 其中, 对于用户或者一般开发人员来说, 数据库文件应该算是他们最关心的文件,
然而, 数据库文件有很多资源对象, 如实体表 (table), 视图(View), 索引(Index), 约束(Constraint) 等等, 面对这么多数据库对象, 我们该如何研究呢? 是全部研究, 还是选择重点研究? 当然是选择重点研究,
我们将选择用户或开发人员使用频率最多的实体表 (table) 作为研究对象.
本小节, 我们主要讨论几个问题: table 是如何存储的, 什么是分区, 什么是堆, 什么是 B - 树, 以及它们之间的关系是怎样的?
(一)实体表的两种存储方式
对于 SQL Server 中的实体表数据, 在 SQL Server 中的存储形式表现为堆存储 (Heap) 和 B 树存储(B-Tree,B+Tree).
(二)Heap
1. 堆, 指不含有聚集索引的表, 之所以称为堆, 是因为它的数据不按任何顺序进行组织, 而是按分区组对数据进行组织.
2. 在堆中, 用于保存数据之间关系的唯一索引结构是索引分配映射 (IAM,Index Allocation Map) 的位图, 对于混合区 (mixed extent) 分配的前 8 个页, 这个位图中有指向这些页的指针,
它还包括一个大位图(每个位代表文件中的 4G 范围内的一个区).
3. 堆不是按照特定顺序来维护的, 所以新增加到列表中的行可以保存到任何数据页上. SQL Server 使用页可用空间页 (PFS,Page Free Space) 的位图来跟踪数据页中的可用空间, 以
便可以快速地找到有足够空间能够容纳新行的页面, 如果这样的页面不存在, 则分配一个新页面, 对于长度可变的列进行更新时, 行的大小就会扩展, 页可能会因为没有空间而无法容纳
新增加的行, 此时, SQL Server 会把扩展后的行移动到具有足够空间的页上, 而在原来的位置上保留一个所谓的正向指针(forwarding pointer),, 通过它指向行的新位置.
(三)区
区是由 8 个物理连续的页组成的单元. 当表或索引需要更多的空间以存储数据时, SQL Server 为对象分配一个完整的区.
1. 对于包含少量数据的对象, 当对象不足 64KB 时, SQL Server 通常只分配一个单独的页, 而不是整个区
2. 区按存储是否为同一对象, 可分为混合区 (区 8 个连续的页存储不同对象) 和非混合区(区 8 个连续的页存储相同的对象)
3. 当删除 (delete) 或清空 (Truncate) 表时, 将会释放区
4. 一些读操作, 如大型表或索引扫描的预读(read-ahead), 可以在区级别, 或更高的快级别读取数据
5.I/O 操作最大的开销是磁盘臂的移动, 而真正的磁盘读写操作开销要小得多, 因此, 读取一个页和读取一个区所用得时间几乎一样
(四)B Tree
B 树是一种存储结构, 如 B+Tree,B-Tree, 其中, B-Tree 是我们重点关心的, B-Tree 是一种平衡树, 主要用来存储聚集索引相关数据的, 在这里不重点论述, 只要知道有这么个概念即可,
在索引 (Index) 章节, 我会重点论述.
(五)行数据, 溢出数据和其他数据
详见本篇博文第四部分.
四 页的基本构成
通过前面几节介绍, 我们知道, 页是存储的最小单位(其实, 页也是 IO 的最小单位, 每次从磁盘 DB 中读取数据到缓冲池, 都是以页为单位读取的), 那么 SQL Server 中的 Page 又是什么呢? 它
的基本结构又是怎样的呢? 下图为 SQL Server 中, 一个 Page 的基本构成.
(一)页描述
页是 SQL Server 存储数据的基本单位, 大小为 8KB, 它可以包含表或索引数据, 分配位图, 可用空间信息等. 在 SQL Server 中, 页是数据存储的最小单位, 也是数据读取的最小 IO.
(二)页的基本构成
SQL Server Page 主要由四部分构成, 页头 (Page Header), 数据行(Data Row), 空闲区(Free) 和偏移量(Offset).
1. 页是数据存储的最小单位, 页是数据读取的最小 IO;
2. 一个页的大小为 8KB, 其中页头占据 96B(96 个字节), 页尾维护的行指针占据 2B(2 个字节), 还有其他保留字段以备后用.
3.SQL Server 2005 后, 为了满足 VARCHAR,NVARCHAR,VARBINARY,SQL_VARIANT 和 CLR 用户定义类型, 放宽了对行大小的限制, 这个技术就叫做行溢出数据.
4. 行溢出数据, 指当行超过 8060 字节时, 这些类型的值将被移动到一个成为行溢出分配单元中的页中, 而在原始页上保留一个 24 字节的指针, 指向行外的数据, 如此, 行就
可以跨多个页, 但行内数据任然在 8060 字节限制内. 如果类型值在 8000 字节以内, 它们的值将被移动到行溢出页中; 如果超过 8000 字节, 这些值在内部将被存储为一个大类型
对象, 而在原始行上维护一个 16 字节的指针, 指向该大型对象值.
五 参考文献
[01] 《SQL Server 2012 深入解析与性能优化 第 3 版》Christian Bolton,Justin Langford,Glenn Berry,Gavin Payne,Amit Banerjee,Rob Farley 著
[02] 《SQL Server 2008 查询性能优化》Grant Fritchey,Sajal Dam 著
[03] 《Microsoft SQL Server 2008 技术内幕: T-SQL 查询》ltzik Ben-Gran,Lubor Kollar,Dejan Sarka,Steve Kass 著
来源: https://www.cnblogs.com/wangjiming/p/10123887.html