使用Sql Server也快有一年了,本来是不想在这个数据库上纠结太多的,但是由于工作的需要还是浅浅地学了一点,有几点体会。

1. Sql Server 的临时表

当我刚开始接触Sql Server的时候,看着别人写的SP代码,发现好多以#开头的表(如这样的#TMP_Customer_L1M),后来才知道,这就是Sql Server的临时表。这种临时表使用很灵活,主要是创建方便(直接select ... into #tmp_table from ...就创建成了),操作速度快(SQL Server的临时表是放在内存中的,从这点也可以体会出“SQL Server很喜欢吃内存”这点),使用起来和使用变量有相通之处。我感觉Sql Server与Oracle的临时表除了名字一样,其它的相同点少得可怜,他们的不同之处可以看文章《Oracle临时表和SQL Server临时表的不同点对比》(更多从技术角度来分析),以及《临时表在Oracle数据库与SQL Server数据库中的异同》(结合业务来分析),还有一个视频《Comparing Temporary Tables in Oracle and SQL Server

2. Sql Server的内置函数

这一点我其实就是想吐槽的。它的内置函数是在是少啊,功能不够用啊,需要处理略复杂的逻辑时就很痛苦。当需要取某月的第一天时,好怀念Oracle的这种写法:

 SELECT TRUNC(SYSDATE,'mm') FROM dual;

在Sql Server里需要自己去做加减法来得到本月的第一天,在社区里被顶得最多的写法如下:

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

还有Sql Server的对正则表达式的支持实在太差,相比于Oracle的REGEXP_LIKE(), REGEXP_SUBSTR(), REGEXP_INSTR(), REGEXP_REPLACE()正则表达式函数,SQL Server表示它一无所有(好像最接近的函数就是PATINDEX了,但是它只能查找,功能也弱),但是它提供了CLR(Common Language Runtime),可以通过调用.NET来实现正则的相关处理(这个没有研究过),或者自己建几个基础的正则表达式函数(这个就是在重复造轮子),再或者就是想出各种曲折的算法来达到相同的目的。

还有Sql Server分析函数功能也弱,没有Oracle的Family Tree函数,等等,总体感觉和Oracle的内置函数比起来,Sql Server还有很长的路要走啊,使用太不方便了!

3. Sql Server的死锁

之前我知道死锁,但是是Sql Server让我认识到了数据库里的死锁。刚接触时,同事就告诫说引用表时记得要在后面加上WITH(NOLOCK),虽然会有脏读的可能性,但是在我们目前这个系统出现的可能性极低,所以做ETL时都会加上这个hint。本来我对这个问题认识估计也就停留在此了,直到最近要编写完ETL调度的脚本时,面对多并发的情况,频繁发生死锁,我才学了一些关于Sql Server锁的相关知识,见此文

关于Sql Server的锁的介绍可以看官方文档或这篇文章《T-SQL查询进阶—理解SQL Server中的锁》的介绍。

查看锁可以通过使用sys.dm_tran_locks这个DMV或者用Profile查看器。用锁锁定的对象可以从行到数据库。

Sql Server的锁大致分为几种:

  • 共享锁(S锁):用于读取数据时所加的锁,默认是读取前加共享锁,读取完一行就释放。共享锁之间不互斥;
  • 拍他锁(X锁):用于修改数据,排斥其他所有的锁;
  • 更新锁(U锁):相当于是S锁+U锁,用于更新;
  • 意向锁(IS,IU,IX):SQL Server锁定一个粒度比较低的资源时,会在其父资源上加上意向锁,告诉其他查询这个资源的某一部分已经上锁。

可以看到Sql Server对于查询一般是要加共享锁的。

下面讲讲死锁,死锁的发生有几个条件:资源的互斥性(mutual exclusion);占有并等待(hold and wait);不可剥夺(no preemption);循环等待(bounded waiting)。

死锁的处理:当Sql Server发生的死锁被检测到时,系统会自动决定终止其中一个进程让其它进程可以继续执行下去,被中断的进程会报错1205的错误,如下。

deadlock

死锁的预防正是程序员所要考虑到的问题。如何将死锁减至最少,官方文档里有提供一些建议:

  • 按同一顺序访问对象
  • 避免事务中的用户交互。
  • 保持事务简短并处于一个批处理中。
  • 使用较低的隔离级别。
  • 使用基于行版本控制的隔离级别。
    • 将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON,使得已提交读事务使用行版本控制。
    • 使用快照隔离。
  • 使用绑定连接。

官方文档中有对这些建议的详细解释。

对于锁,Sql Server提供了一些Hint,可以用来在程序中人工干预锁的一些属性,官方文档里有详细介绍。

SQL Server的官方文档里共列了8行来说明它的索引种类,但其实SQL Server的索引主要就两个维度:是否是唯一性索引(Unique),是否是聚集索引(Cluster)。其它几种索引都是对特殊数据类型所做的索引。

创建索引的最基本的语法如下(详见MSDN):
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )

关于索引的第一个维度较好理解,唯一性索引会限制表的数据,使其每行中索引列的值都是唯一的,阻止重复值数据的插入。唯一性索引需在创建索引时显示地指明Unique。

关于索引的第二个维度,是否用聚簇(Clustered),这个几乎是SQL Server索引的核心内容,下面详细介绍下。

在一个表上的几个列上建立聚簇索引后,SQL Server将这个表按照B-树结构组织起来(未建立聚簇索引的表按照堆Heap的方式组织),下图取值MSDN的官方文档,很好的解释了聚簇索引的层级结构。

Levels of a Clustered Index

在SQL Server中最小的存储单元是页(Page),大小为8KB,这意味着每次SQL Server都是从硬盘中读取整数个页到内存中去处理的。如上图,在索引树中每个Page称为是索引节点(index node),一棵索引树从根节点(root node)开始,进入到下一层的索引节点,除叶子节点外的所有节点都储存了这样<值,指针>的键值对所组成的Index rows,其中的“值”代表的是索引列的值,指针则是指向下一层的node,而叶子节点(Leaf node)是实际储存数据的地方。

所以也就可以得到聚簇索引的几个性质:

1. 一个表上最多只能建立一个聚簇索引

2. 聚簇索引额外占用空间相对于数据储存空间而言是很小的

这些都是因为聚簇索引只是代表了表在底层的一种组织方式。

而非聚簇索引(Unclustered)则不一样,它是在建索引的表外又自己维护了一个表,这个系统自己维护的表也是采用B-树结构组织的,这树与聚簇索引的B-树的唯一区别在它的叶子节点上,前者的叶子节点只存了索引列的数据以及到该记录的指针,如下图所示:

Levels of a Nonclustered Index

(其实当表是以heap的方式组织时,此处叶子节点才存的是指针,当表上有建有聚簇索引是存的是聚簇索引对该条记录的索引唯一值,详见MSDN。)

自此可以看出“Clustered”意味着物理排序,而“Nonclustered”意味着逻辑排序。用网上流传的一个比方来说就是Clustered意味着新华字典里的拼音排序,而Nonclustered大致类似于部首索引。

一般将聚簇索引建在主键ID上一方面是因为ID是自增的,而聚簇索引作为一种有序的物理组织方式,所以不断增加的ID行就可以直接加在后面,否则将行插入中间的某个位置就很有可能要拆页,这样的行为多了就会产生碎片,浪费空间。另一个原因是非聚簇索引的叶子节点会存储聚簇索引的索引值,当聚簇索引用ID列时可以减少非聚簇索引的空间使用情况。

参考文章:

SQL Server的索引页可以参考MSDN这里

T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他