浅谈SQL Server数据优化之若干要点

       在上篇文章 浅谈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著

六 版权区

 

  • 感谢您的阅读,若有不足之处,欢迎指教,共同学习、共同进步。
  • 博主网址:http://www.cnblogs.com/wangjiming/。
  • 极少部分文章利用读书、参考、引用、抄袭、复制和粘贴等多种方式整合而成的,大部分为原创。
  • 如您喜欢,麻烦推荐一下;如您有新想法,欢迎提出,邮箱:2098469527@qq.com。
  • 可以转载该博客,但必须著名博客来源。
(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐