【庆站庆】 SQLServer性能优化案例讨论

bq_wang

背景：某资讯公司构建了一套证券资讯信息库，出于备份和分发的目的，又构建了一套完整的备份库，原信息库和备份库是通过触发器/存储过程的方式进行实时同步的，而备份库与其他互联网上的分发库则是通过接口机和文件形式进行分发入库的，由于要考虑到分发的全局性和唯一性，这也造成了设计上的复杂性，即表中递增列主键、GUID随机主键和自然属性主键并存。

一、   数据库链接的性能问题

SELECT *

  FROM dbsource.dbname.dbo.table

WHERE guid in (SELECT guid

                  FROMtablechangelog

                 WHEREtablename='table'

                   AND ID<110000)

这个运行居然要40秒以上。

后来分析了一下

1、table和tablechangelog是在不同的服务器上

2、在tablechangelog有230万记录，ID是聚集索引

   在table上guid是主键，大概有30万条记录

解决步骤

首先执行SELECT GUID FROM tablechangelog WHERE tablename='table' ANDid<110000，发现时间忽略不计

再次还原到同一台服务器上测试运行，发现只要1秒

SELECT *

  FROM table

WHERE guid in (SELECT guid

                  FROMtablechangelog

                 WHEREtablename='table'

                   AND ID<110000)

也就是说该SQL语句性能瓶颈在于网络，而不是SQL本身。

既然问题在于网络，那应该可以通过减少数据网络传递来解决部分

登陆到目标服务器上执行

SELECT *

  FROM table

WHERE GUID IN (SELECT GUID

                  FROMdbdest.dbname.dbo.tablechangelog

                 WHEREtablename='table'

                   AND id<110000)

发现只需要1~3秒即可

本来想GUID应该是造成该SQL执行的最大问题，没想到居然是网络问题，既然优化已到达效果，就暂且不用去管GUID了

后话：

对于跨库的SQL，应尽量减少网络间的数据传输，必要时可以调整其主次关系。

问题二

关于GUID和递增性ID带来的问题

出于唯一性和系统维护的要求，在各个表中都存在以下两个字段GUID和ID。

ID一般定义为：ID  [int] IDENTITY (1, 1) NOT NULL，且为聚集索引+主键；

而GUID定义如下：[GUID] [varchar] (38) COLLATEChinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_GUID] DEFAULT ('{' + convert(char(36),newid())+ '}')

出于系统维护的要求，一般都会这样查询

SELECT * FROM tableA WHERE GUID NOT IN (SELECT GUID) FROM tableB)

但是GUID是不做唯一索引的，且即使加了唯一索引，考虑到GUID是无序且过于分散的，如果有几千上万的GUID的话，出于成本的原因，仍是不会走索引的。

关于ID，ID一般是递增的，是不要进行维护即可从数据库中获得的，同时由ado直接返回给前端程序，以便定位和显示、

INSERT INTO jobs (job_desc,min_lvl,max_lvl)

VALUES ('Accountant',12,125)

SELECT @@IDENTITY AS 'Identity'

但是再由sqlserver2000升级到sqlserver2008后，发现返回的@@identtiy明显是错误的

后来查了一下SQLServer2000联机帮助

在一条 INSERT、SELECT INTO 或大容量复制语句完成后，@@IDENTITY 中包含此语句产生的最后的标识值。若此语句没有影响任何有标识列的表，则 @@IDENTITY 返回 NULL。若插入了多个行，则会产生多个标识值，@@IDENTITY 返回最后产生的标识值。如果此语句激发一个或多个执行产生标识值的插入操作的触发器，则语句执行后立即调用 @@IDENTITY 将返回由触发器产生的最后的标识值。若 INSERT 或 SELECT INTO 语句失败或大容量复制失败，或事务被回滚，则 @@IDENTITY值不会还原为以前的设置。

发现通过

SELECT IDENT_CURRENT('tablename')能够返回正确的递增值

从sqlserver2005以后系统提供了NEWSEQUENTIALID ()，这个新的GUID

Creates a GUID that is greater than any GUID previously generated by thisfunction on a specified computer since Windows was started. After restartingWindows, the GUID can start again from a lower range, but is still globallyunique. When a GUID column is used as a row identifier, using NEWSEQUENTIALIDcan be faster than using the NEWID function. This is because the NEWID functioncauses random activity and uses fewer cached data pages. Using NEWSEQUENTIALIDalso helps to completely fill the data and index pages.

这个GUID是由操作系统产生的，但是每个GUID都会比前一个GUID要大，这即解决了唯一性问题，又解决了排序问题

讨论话题：
1、该案例是基于MSSQL2000的，目前已升级到了MSSQL2008，之前的订阅分发机制不够稳定，所以才采用了触发器和存储过程这种低效的同步方式。在实际生产环境中，大家更多都是采用什么同步方式？
2、在类似分布式的系统中，大家采用的是递增序列、自然主键、GUID还是UUID做同步键/主键？
3、跨库数据库连接应该注意什么问题，上面的SQL是否还能继续进一步优化？

活动时间：2012.9.25—2012.10.10

活动奖励：针对以上任意一个问题跟帖回答，我们会在讨论结束后，随机抽选2名讨论积极的会员各赠送《IT项目管理那些事儿》一本，呵呵，做个广告。

本期两名幸运会员分别为hwtong，Obuntu，谢谢大家的积极参与！

hwtong

第一个的语句可以修改成
select a.* from table as a where exists
        (select top 1 1 from dbdest.dbname.dbo.tablechangelog as b
                where a.guid=b.guid and b.tablename='table' and b.id<110000)
这样效果应该更好点

yxyup

支持，支持

无忧石头

顶

hitbird

支持支持～

Obuntu

支持bq兄。
再仔细看了下。有几个疑问和小看法。

1）接口机是什么东西？看这架构图，分发数据库是不是直接还原经网络传来的备库产生的备份文件？

2）看到dbsource.dbname.dbo.table 。第一反应就是：网络是不是出问题了。 对于网络瓶颈，除了物理上的原因，我想微软应该也会有一些优化的指导的，毕竟像这样的部署方式，对网络要求还是比较高。


3）关于讨论话题。


1、该案例是基于MSSQL2000的，目前已升级到了MSSQL2008，之前的订阅分发机制不够稳定，所以才采用了触发器和存储过程这种低效的同步方式。在实际生产环境中，大家更多都是采用什么同步方式？
这个一般是根据实际情况出发吧，如果对实时性要求不高的话或者对备库只需要做查询的话，一般用镜像或者日志传送就可以了。如果是对实时性要求比较高且需要对备库也进行操作，那可以采用订阅和复制的方式。目前看到的是，也没有一些比较成熟的使用方案，基本是根据官方提供的方案来实现的。另外，触发器和存储过程如果优化得当，应该也不会太低效吧，只是实际使用起来工作量会大而且比较容易出错 。

2、在类似分布式的系统中，大家采用的是递增序列、自然主键、GUID还是UUID做同步键/主键？
像图中的场景里，看到的是单向的分布式，而不是双向的。所以选取主键的方式应该不影响分布式的使用吧。 另外具体主键的选择当然是根据实际表来做决定了。但GUID一般不建议做主键吧？

3、跨库数据库连接应该注意什么问题，上面的SQL是否还能继续进一步优化？
跨数据库的连接确实是个问题呀，但这方面的资料感觉比较少，也没看到一些很相关的内容。记得以前碰到的问题好像是dblink的表不走索引？

lastwinner

sqlserver是得好好优化，不然并发量一大好像就撑不住了

dingjun123

支持

wabjtam123

SELECT *

  FROM dbsource.dbname.dbo.table

WHERE guid in (SELECT guid

                  FROMtablechangelog

                 WHEREtablename='table'

                   AND ID<110000)


==》这个语句返回多少条没说啊

shanks_gao

NEWID()的无序性使你插入后的分页,碎片问题多多...应用 NEWSEQUENTIALID()代替

1:repl同步机制.但订阅端readonly. 另:lz说的订阅分发机制不够稳定是指什么?repl本身问题?
2:lz描述非merge,没明白...
3:linkserver查询中确保数据源与本地数据排序规则相同.最好在Linkserver端过滤完数据再到本地server进行计算