假设数据库中有一个表photo，其中包含2个key组成联合主键，分别名为userId和photoId，以及一个image类型的字段stream。此时有两种不同的思路，一种是在保存的时候进行计算，并存入第三个字段dataSize，另一种是每次都直接使用以下语句进行计算：
select sum(datalength(stream)) from photo where userId=@userid
第一种方法一般来说都挺不错的，但是有那么一个不那么完美的问题：很难确保数据一致性。假如某一次更新中的代码新添加了一种对stream进行修改的特殊代码，但很遗憾忘了修改dataSize字段。更新之后，程序不会表现出明显的异常，有可能会跑了很长一段时间之后才会发现。而发现之后，就需要专门的小程序来重新修复。并且修复之后，以我的经验，一定会有用户不认可发生的变化。也许有人会说，图片不都是只插入不更新的么？那我们就再来想另一个场景：这是一个类似Evernote的应用，用户在里面保存的文字数量也是限量的，同时也是需要修改的。有人还会说，修改stream的代码不应该都在一起吗，不应该修改的时候都会修改dataSize吗？问题是，应该的事情总有不发生的几率，而小概率事件则必然发生。
第二种方法则保证了每一次都是准确的，也不需要代码进行相应的计算和更新，于是就没有了前面的一个问题。这种方法在每个用户的数据量较少的时候问题不大，但是在数据量巨大的时候，会面临非常严重的性能问题。这是由Sql Server的数据存储结构所决定的。
在SqlServer当中，数据库每8K为1页（Page），8页为1片（Extent）。而一行数据中的定长数据通常都存在一个页当中，而不定长数据如varXXX一类，只要这一行数据没有超出8060字节，也会存在于当前页中。上述的数据成为行内数据（In_Row_Data）。而对于一行超出8060字节的情况下，不定长数据会存入超长数据页（Row_Overflow_Data）。而类似Image、text等类型的数据，则会保存在大对象数据页（Large_Object_Blob，简称LOB）当中。关于这些知识的解释，请参见此处的官方说明。
可惜的是，上述说明中并没有详细解释超长数据和大对象是如何在行内数据中记录的。对此，我们可以通过下述方式进行分析。
首先，我们需要通过下属语句找出保存数据的某一页：
select first_page from
sys.system_internals_allocation_units as u inner join
sys.partitions as p on u.container_id = p.partition_id
where
p.object_id=object_id('photo') and p.index_id=1 and u.type=1
这一个语句会得到一个类似如下的数据：
0x876543210500
这个16进制数据实际上分为两个部分：0x21436587是页码，而0x0005则是文件号码。注意，这两部分都需要对原始数据进行一个颠倒，有点类似BigEndian和LittleEndian的颠倒过程。
得到这两个数据之后，我们进一步将页码部分转换为十进制558065031，将文件码也转换成十进制，然后输入如下的命令：
dbcc traceon(3604)
dbcc page(databasename, 5, 558065031, 3)
dbcc traceoff(3604)
这一个命令会在消息中输出具体的存储信息，我们在里面首先找到这么一行数据：
stream = [Textpointer] Slot 0 Column 5 Offset 0xab Length 16
请记住这个偏移量0xab，然后在这一行往上找第一个Memory Dump，比如：
Memory Dump @0x0000000024A9C060
0000000000000000: 30008d00 653e0500 02000000 80000000 †0...e>..........
0000000000000010: 00000000 16640000 00ecf496 00749800 †.....d.......t..
0000000000000020: 00000000 00e52001 00000000 00000000 †...... .........
0000000000000030: 00ffffff ffffffff 60000000 00000000 †........`.......
0000000000000040: 00000000 0000f03f d0000000 00ecf496 †.......?........
0000000000000050: 00749800 00000000 00000000 0013f8fe †.t..............
0000000000000060: f1000000 0000f03f 00000000 0000f03f †.......?.......?
0000000000000070: cd000000 00030000 00000000 01000000 †................
0000000000000080: f89e015d 4a49ab3f 00000000 002f0000 †...]JI.?...../..
0000000000000090: 009c0370 7b0600ab 00bb80cb 00cb00cb †...p{...........
00000000000000A0: 00ef0001 4f1a4ecb 4ecd7e00 00f2fef8 †....O.N.N.~.....
00000000000000B0: 13000010 00000005 00010020 20202020 †...........
00000000000000C0: 20202020 20202020 20202031 64616130 † 1daa0
00000000000000D0: 6230332d 62313866 2d343462 302d6231 †b03-b18f-44b0-b1
00000000000000E0: 62302d35 64353739 38366164 623037††††b0-5d57986adb07
途中高亮部分就是刚才偏移量所指出的16个字节的数据。根据观察，后8个字节10000000 05000100 和堆数据的行定位数据结构（row locator）非常相似。而该结构的含义如下：
页码（4字节） 文件号（2字节） 槽号（2字节）
需要注意的是，这是Little Endian编码格式。经过转换，页码为16，文件号是5，槽号为1。然后我们再次执行dbcc page命令如下：
dbcc traceon(3604)
dbcc page(databasename, 5, 16, 3)
dbcc traceoff(3604)
我们在消息输出中，找到槽号（Slot）为1的位置，就可以看到如下的数据：
Blob row at: Page (5:16) Slot 1 Length: 4900 Type: 3 (DATA)
Blob Id:21960150089728
而上面的二进制大对象块（Blob）编号21960150089728的十六进制值为0x000013f 8fef20000，正好是上面高亮部分十六个字节的前8个字节。换句话说，这个字段的大小并非存在行内数据当中，而是存在于大对象页当中，也就是上面的黑体字所显示的地方。
因此，当我们执行类似select sum(datalength(stream)) from photo where userId=@userid 这样的语句时，数据库系统再怎么优化，也需要读取这些页，而不是只需要读取行内数据即可。由于大对象通常都很大，其数据密度是相当低的，通常一页当中只有3-4行对应的字段值。除此之外，大对象的保存顺序和聚集索引不同，和数据的增删改的顺序有关，而与聚集索引本身的顺序无关。于是，我们基本上可以根据如下的方式估算一次上述查询所需要读取的数据量：
行数= select count(*) from photo where userId=@userid
读取次数 = 行数
读取数据量 = 行数 * 8K
也就是说，当用户数据达到1000行时，读取的数据量可能会达到8M，而且还是随机读取。而用两块普通15krpm的SAS硬盘做镜像，随机读取能达到4MBps就不错了。因此每次查询将会消耗2秒的时间，其它的查询只要内存中没有缓存，都注定需要等待。
那么，是否只能够退化到添加一个静态字段，然后通过代码来更新的境地呢？其实还有另一种方案：添加计算字段并对其做索引。这种方案对于查询远多于修改的系统来说，是一个很不错的选择，方法如下：
首先，在数据库的属性中，修改算术终止已启用（ArithAbort）为是（True）。这一步非常关键，否则当你完成后面两个步骤之后，将会无法对数据进行更改。然后用下列语句添加一个计算列：
alter table photo
add dataSize as (datalength(stream))
然后再通过下面的语句添加一个索引：
CREATE NONCLUSTERED INDEX [IX_photo_datasize] ON [dbo].[photo]
(
[userId] ASC,
[photoId] ASC
)
INCLUDE (
[datasize]
) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
需要注意的是，上面的ONLINE一定要设置成ON，这样可以让系统不要锁柱整个表（仍然会短暂锁住，但几乎感觉不到）。而datasize一定要设置成包含列，因为这一列基本上不会用于检索，出现相同值的可能性也非常低。而是否应该添加其它的列（包括包含列），例如photoId，则需要根据根据实际情况分析处理。
这三个步骤完成之后，再执行查询select sum(datalength(stream)) from photo where userId=@userid，你会发现执行任务计划已经使用了IX_photo_datasize了！注意，你连sql查询语句都不需要修改，SqlServer会自动识别出datalength(stream)等同于datasize字段，而datasize字段存在于IX_photo_datasize索引。很先进吧！
看到这里，有人就会问了：为啥不直接添加一个持久化的计算字段呢？这是因为一旦设置为持久化，将会导致行内数据的结构发生变化——多了4个字节的数据嘛。于是，这将会强迫数据库系统对已有的数据进行修改。对于数据量较小的库来说还好，而对于数据量巨大的库来说，将会导致系统长时间锁定该表而无法正常提供服务。这在互联网应用上，通常来说会导致用户的强烈反应。当然，你也可以采取复制一个数据库，并且设定为只读的方式，让服务在只读状态下运行，于此同时则修整真实数据库，修整完之后再切换回来的办法。但是，为什么要采取一个这么复杂的步骤，来获得几乎相同的效果呢？
不过，需要注意的是，以下情况会导致索引失效：
- 查询中的函数不是计算字段中函数的一部分。比如说，查询字段设置成了IsNull(datalength(stream), 0)，而查询却为select datalength(stream) ……
- 查询中包含了某种导致索引失效的情况，比如：
select sum(streamsize) from
(
select row_number() OVER (order by userId, photoId) as rowno,
datalength(stream) as streamsize
from photo
where userid = @userid
) t
where rowno between (1, 1000)
对于第一种情况，你需要修改查询字段，而对于第二种情况，则必须修改sql语句，比如说改成如下所示：
select sum(datalength(stream)) from photo as p inner join
(
select row_number() OVER (order by userId, photoId) as rowno,
userId, photoId
from photo
where userid = @userid
) as t on p.userId = t.userId, p.photoId = t.photoId
where rowno between (1, 1000)
select row_number() OVER (order by userId, photoId) as rowno,
userId, photoId
from photo
where userid = @userid
) as t on p.userId = t.userId, p.photoId = t.photoId
where rowno between (1, 1000)
上述的方法有如下几个巨大的好处：
- 即便前期规划出现问题导致数据库结构不包含stream大小的静态列，也可以后期修改；
- 修改时服务不需要下线维护，同时也不会导致明显的服务阻塞感；
- 几乎不需要修改任何程序，即便需要修改，程序修改和数据库修改也没有严格的先后顺序，在同时修改完成之前，性能几乎没有明显的下降，服务连贯性非常好；
- 数据一致性非常好，不会受到程序设计、更新等人为失误的影响；
- 性能提升非常明显，由于数据密集度很高，甚至比静态字段效果还要好。
当然了，硬币的另一面是：
- 你需要修改数据库的默认行为（ArithAbort要改成True，一般影响不大）；
- 对于select * from photo where ...这样的查询，在执行计划中将会比原来多出一个计算datalength(stream)的步骤。不过由于stream本来就必须从文件中读取出来，因而计算datalength基本上不会增加可感觉到的运算时间；
- 存储空间和写入次数都比静态字段稍多一些。其实是多出2倍以上（假如索引去掉PhotoId列的话），但是因为每一行的数据量其实很小，因此通常可以忽略不计。比如对于1亿行的数据的表使用上述方法，可能会多出至少400MB的数据量。而对于有着1亿行数据的表来说，本身的数据量就不会少于2.4GB（4字节userId+4字节PhotoId+16字节stream的row locator），这还没有算上stream部分占用的空间。因此，基本上可以忽视其空间上的影响。
怎样，这个方案不错吧？
没有评论:
发表评论