Category Archives: Backend

用SQL查询日 月 周 季

怎么用SQL查询昨天、今天、明天和本周的记录？又怎么用SQL查询一天，三天，一周，一个月，更长一些——一个季度的记录呢？本文中给出了一些方法。 SQL查询今天的记录：

SQL查询昨天的记录：  

本月记录：

本周记录：

本日记录：

一天

三天

一周

注意：此时不能用 datediff 差值为7,因为，datediff只表示间隔数 一月   

一季度

希望以上这些方法，能给大家一些启示。

View Details

SQL Server索引结构及其使用

一、深入浅出理解索引结构 实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别： 其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。 如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。 通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。 二、何时使用聚集索引或非聚集索引 下面的表总结了何时使用聚集索引或非聚集索引（很重要）： 事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。 三、结合实际，谈索引使用的误区 理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。 1、主键就是聚集索引 这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。 通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。 显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。 从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。 在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。 通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。 在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万条）： （1）仅在主键上建立聚集索引，并且不划分时间段：

用时：128470毫秒（即：128秒） （2）在主键上建立聚集索引，在fariq上建立非聚集索引：

用时：53763毫秒（54秒） （3）将聚合索引建立在日期列（fariqi）上：

用时：2423毫秒（2秒） 虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是：在各个select语句前加：

并在select语句后加：

2、只要建立索引就能显著提高查询速度 事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。 从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的 3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度 上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引（compound index）。 很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列）：

查询速度：2513毫秒

查询速度：2516毫秒

查询速度：60280毫秒 从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。 四、其他书上没有的索引使用经验总结 1、用聚合索引比用不是聚合索引的主键速度快 下面是实例语句：（都是提取25万条数据）

使用时间：3326毫秒

使用时间：4470毫秒 这里，用聚合索引比用不是聚合索引的主键速度快了近1/4。 2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

用时：12936

用时：18843 这里，用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。 3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个：

用时：6343毫秒（提取100万条）

用时：3170毫秒（提取50万条）

用时：3326毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的）

用时：3280毫秒 4、日期列不会因为有分秒的输入而减慢查询速度 下面的例子中，共有100万条数据，2004年1月1日以后的数据有50万条，但只有两个不同的日期，日期精确到日；之前有数据50万条，有5000个不同的日期，日期精确到秒。

用时：6390毫秒

用时：6453毫秒 五、其他注意事项 “水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。 所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。 当然，在实践中，作为一个尽职的数据库管理员，您还要多测试一些方案，找出哪种方案效率最高、最为有效。   原文出处：http://www.cnblogs.com/acafaxy/archive/2011/03/17/1987431.html

View Details

MS Sql Server数据库分区

对于Sql Server数据库分区问题相信很多人还是对其不时很了解，具体Sql Server数据库分区是怎么个情况？下文将为大家解答。 什么是数据库分区？ 数据库分区是一种对表的横向分割，Sql server 2005企业版和之后的Sql server版本才提供这种技术，这种对表的横向分割不同于2000中的表分割，它对访问用户是透明的，用户并不会感觉的表被横向分割了。（2000中的表横向分割是建n个表例如按时间建表每月一个表，表名不同，最后需要做一个大视图） 为什么要分区？ 显而易见分区是为了提高数据库的读写性能，提高数据库的效率； 分区是否总是可以提高效率？ 分区是一把双刃剑，并不总能提高效率，这和具体情况有关系。 之所以有分区技术，分区技术用的好的话可以提高性能，是因为一方面分区把一大块数据分成了n小块，这样查询的时候很快定位到某一小块上，在小块中寻址要快很多；另一方面CPU比磁盘IO快很多倍，而硬件上又有多个磁盘，或者是RAID（廉价磁盘冗余阵列），可以让数据库驱动CPU同时去读写不同的磁盘，这样才有可能可以提高效率。 分区在有些时候并不能提高读写效率，比如说我们经常看到的按照日期字段去分区MSDN例子，这个实例中是按照记录的生成时间来分区的，把一年的数据分割成12个分区，每月一个。这样的分区导致分区并不能实现CPU同步写并提高写入性能，因为在同一个时段CPU总是要写入到最新的那一个分区对应的磁盘中。另一个问题是：这样分区是否可以提高读取性能呢？答案是不一定，要看根据什么字段来查询，如果是根据时间来查询，根据时间生成报表那么这种分区肯定会提高查询的效率，但是如果是按照某个客户查询客户最近1年内的账单数据，这样数据分布到不同的分区上，这样的话效率就不一定能提高了，这要看数据在同一个分区上连续分布的读性能高，还是CPU从几个磁盘上同步读取，然后在合并数据的性能更高一些，这和读取数据的记录数也有关系。 如何分区？用什么字段做分区依据？ 具体如何分区和涉及的业务有关系，要看业务上最经常的写入和读取操作是什么，然后再考虑分区的策略。 既然与具体业务相关，我们就假定一个业务环境，假如我们要做一个论坛，对论坛的帖子和回复表进行分区。 论坛中最常见的写操作是1）发帖 2）回复帖子， 最常见的读操作是 1） 根据帖子id显示帖子详情和分页的帖子回复 2） 根据帖子版面帖子列表页根据版面id分页读取帖子列表数据 怎么分区更合适呢？ 现在还没有准确答案，我有两种可能的方案，写下来，大家讨论看看。 方案1. 根据帖子ID区域段分区（1-300w一个分区、300w-600w一个分区…），这样理论上可以提高帖子详细页的读取速度，而对于写操作性能没有益处，对于根据版面id读取帖子列表页有可能有益 方案2. 根据版面id进行分区，这样对于写性能应该有提高，不同的分区对应不同的版面，当有两个版面同时有发帖回帖操作时，有可能可以并发写。对于根据版面id获得帖子列表页数据也可以提高性能，而对于帖子详细信息页没有性能影响。 多大的数据量才需要分区？ 这个问题我只能说一个内部标准，如果一张表的记录超过在超过1000w，并以每月百万的数据量增长，那就需要分区。 上文从这几个方面解析了Sql Server数据库分区问题，现在对数据库分区大家应该都有一个大体的了解，这里介绍的只是Sql Server数据库分区的基本知识，对刚刚入门的初学者来说是很好的学习资料，希望能够帮到大家。

View Details

教你如何在SQL Server数据库中加密数据

导读：为了防止某些别有用心的人从外部访问SQL Server数据库，盗取SQL Server数据库中的用户姓名、密码、信用卡号等其他重要信息，在我们创建SQL Server数据库驱动的解决方案时，我们首先需要考虑的的第一条设计决策就是如何加密存储SQL Server数据，以此来保证它的安全，免受被他人窥测。 SQL Server数据库中有哪一种支持可以用于加密对象和数据?从一开始就讨论一下SQL Server数据库欠缺什么是明智的，或者是对于SQL Server数据库中的加密部分你不应该做什么。 首先，SQL Server数据库有两个内置的密码函数——即，pwdencrypt() 和 pwdcompare()。同时，还有两个SQL Server数据库用来管理密码哈希的没有正式记录的函数:pwdencrypt() 将密码哈希过后进行存储; pwdcompare()将提供的字符串与哈希后的字符串进行比较。不幸的是，这个哈希函数不是非常安全，它可以通过字典攻击算法被破解(类似命令行应用程序!)。 这些函数随着SQL Server的版本发展而不断进行修改，这也是另一个没有使用它们的原因。早期版本的SQL Server对密码进行的哈希，在后来的版本中无法解密，所以如果你依赖一个版本中的函数，那么当升级的时候，所有你的加密数据就都没有用了，除非你可以首先对其解密——这也就违背了加密的最初的目的。 第二，你可能会尝试去创建一个针对你的数据库的自制的加密解决方案，但是有以下三个理由说明你不要这样做: 除非你是加密专家，否则胡乱编写的加密系统只会提供非常低级的价值不高的保护。新鲜的是，单向密码哈希或者 "ROTx "形式的加密几乎不需要费事就可以被轻松打败。 如果由于你自己的能力的缺乏而导致加密被破解，那么你的数据就完蛋了。你需要将所有的东西进行没有加密的备份，是吗?(即使你加密了，那里有没有安全漏洞?) 当市面上提供有专业级别的，具有工业强度的加密解决方案的时候，你就不值得花费时间去自己做。把你的时间用于构建一个好的，坚固的数据库，而不是再重新发明一次车轮。 那么，什么才是好的加密数据的方式呢? 对于新手，微软提供了一个自己生成的加密解决方案，CryptoAPI 。对于轻量级的加密，军用级别的安全就不在考虑范围之内，它具有相对容易实现的优势:管理员可以安装一个名为CAPICOM 的ActiveX 控制，它可以在T-SQL存储过程中提供CryptoAPI 功能。CAPICOM 支持各种类型的双向加密和单向哈希算法，所以管理员可以挑选最适合应用程序的问题的部分。 如果你对使用微软的解决方案不感兴趣，还有一些很好的第三方的方案可以使用。一家名为ActiveCrypt 的软件有限责任公司制造了XP_CRYPT ，它是SQL Server的插件，可以在视图、程序和触发器中通过扩展存储过程和用户自定义函数(在SQL Server 2000中)来完成加密。你可以下载一个支持无线的MD5,DES ，以及SHA1哈希的免费版本的应用程序; 其他的加密模型就是在比特深度上进行的。(完全版本是无限的。)在你自己的代码中，你可以使用XP_CRYPT，与ActiveX 控制一样(在受限的免费版本中)。对于ASP程序员来说，一个名为AspEncrypt 的组件提供了一种将高级加密整合到你的代码中的简单方式。 对数据库文件自身进行加密或者提供传输层上的安全保护怎么样?对于前者，大家可以在Windows系统中持续使用加密文件系统。然而，你必须保存加密密钥的备份，在出现问题的时候，这个数据有可能会丢失。对于后者，有IPSec和SQL Server自己的SSL加密，都是SQL Server和Windows自带的大家的主要精力应该放在避免以明文存储敏感数据，因为从数据库中抽取没有加密的数据同样是最容易受到攻击的薄弱环节。 SQL Server数据库的安全防护工作时很重要的，希望大家能从上文中学到保障SQL Server数据库安全的方法，做好SQL Server数据库的安全工作，确保SQL Server数据库中数据库信息的绝对安全。

View Details

从外到内提高SQL Server数据库性能

如何提高SQL Server数据库的性能，该从哪里入手呢?笔者认为，该遵循从外到内的顺序，来改善数据库的运行性能。如下图：   第一层：网络环境 到企业碰到数据库反映速度比较慢时，首先想到的是是否是网络环境所造成的。而不是一开始就想着如何去提高数据库的性能。这是很多数据库管理员的一个误区。因为当网络环境比较恶劣时，你就算再怎么去改善数据库性能，也是枉然。 如以前有个客户，向笔者反映数据库响应时间比较长，让笔者给他们一个提高数据库性能的解决方案。那时，笔者感到很奇怪。因为据笔者所知，这家客户数据库的记录量并不是很大。而且，他们配置的数据库服务器硬件很不错。笔者为此还特意跑到他们企业去查看问题的原因。一看原来是网络环境所造成的。这家企业的客户机有200多台，而且都是利用集线器进行连接。这就导致企业内部网络广播泛滥，网络拥塞。而且由于没有部署企业级的杀毒软件，网络内部客户机存在病毒，掠夺了一定的带宽。不仅数据库系统响应速度比较慢，而且其他应用软件，如邮箱系统，速度也不理想。 在这种情况下，即使再花十倍、百倍力气去提升SQL Server数据库的性能，也是竹篮子打水一场空。因为现在数据库服务器的性能瓶颈根本不在于数据库本身，而在于企业的网络环境。若网络环境没有得到有效改善，则SQL Server数据库性能是提高不上去的。 为此，笔者建议这家企业，想跟他们的网络管理员谈谈，看看如何改善企业的网络环境，减少广播包和网络冲突;并且有效清除局域网内的病毒、木马等等。三个月后，我再去回访这家客户的时候，他们反映数据库性能有了很大的提高。而且其他应用软件，性能也有所改善。 所以，当企业遇到数据库性能突然降低的时候，第一个反应就是查看网络环境，看看其实否有恶化。只有如此，才可以少走冤枉路。 第二层：服务器配置 这里指的服务器配置，主要是讲数据库服务器的硬件配置以及周边配套。虽然说，提高数据库的硬件配置，需要企业付出一定的代价。但是，这往往是一个比较简便的方法。比起优化SQL语句来说，其要简单的多。 如企业可以通过增加硬盘的数量来改善数据库的性能。在实际工作中，硬盘输入输出瓶颈经常被数据库管理员所忽视。其实，到并发访问比较多的时候，硬盘输入输出往往是数据库性能的一个主要瓶颈之一。此时，若数据库管理员可以增加几个硬盘，通过磁盘阵列来分散磁盘的压力，无疑是提高数据库性能的一个捷径。 如增加服务器的内存或者CPU。当数据库管理员发现数据库性能的不理想是由内存或者CPU所造成的，此时，任何的改善数据库服务器本身的措施都将一物用处。所以，有些数据库管理专家，把改善服务器配置当作数据库性能调整的一个先决条件。 如解决部署在同一个数据库服务器上的资源争用问题。虽然我们多次强调，要为数据库专门部署一个服务器。但是，不少企业为了降低信息化的成本，往往把数据库服务器跟应用服务器放在同一个服务器中。这就会导致不同服务器之间的资源争用问题。如把文件服务器跟数据服务器部署在同一个服务器中，当对文件服务器进行备份时，数据库性能就会有明显的下降。所以，在数据库性能发现周期性的变化时，就要考虑是否因为服务器上不同应用对资源的争夺所造成的。 故，笔者建议，改善数据库性能时第二个需要考虑的层面，就是要看看能否通过改善服务器的配置来实现。 第三层：数据库服务器 当通过改善网络环境或者提高服务器配置，都无法达到改善数据库性能的目的时，接下去就需要考察数据库服务器本身了。首先，就需要考虑数据库服务器的配置。 一方面，要考虑数据库服务器的连接模式。提供了很多的数据库模式，不同的数据库连接模式对应不同的应用。若数据库管理员能够熟悉企业自身的应用，并且选择合适的连接模式，这往往能够达到改善数据库性能的目的。 其次，合理配置数据库服务器的相关作业。如出于安全的需要，数据库管理员往往需要对数据库进行备份。那么，备份的作业放在什么时候合适呢?当然，放在夜晚，夜深人静的时候，对数据库进行备份最好。另外，对于大型数据库，每天都进行完全备份将会是一件相当累人的事情。虽然累得不是我们，可是数据库服务器也会吃不消。差异备份跟完全备份结合将是改善数据库性能的一个不错的策略。 第四层：数据库对象 若以上三个层面后，数据库性能还不能够得到大幅度改善的话，则就需要考虑是否能够调整数据库对象来完成我们的目的。虽然调整数据库对象往往可以提到不错的效果，但是，往往会对数据库产生比较大的影响。所以，笔者一般不建议用户一开始就通过调整数据库对象来达到改善数据库性能的目的。 数据库对象有表、视图、索引、关键字等等。我们也可以通过对这些对象进行调整以实现改善数据库性能的目标。 如在视图设计时，尽量把其显示的内容缩小，宁可多增加视图。如出货明细表，销售人员可能希望看到产品编号、产品中英文描述、产品名字、出货日期、客户编号、客户名字等等。但是，对于财务来说，可能就不需要这么全的信息。他们只需要产品编号、客户编号、出货日期等等少量的信息即可。所以，能可浪费一点代码的空间，设计两张视图，对应不同部门的需求。如此，财务部门在查询数据时，不会为不必要的数据浪费宝贵的资源。 如可以通过合理设置索引来提高数据库的性能。索引对于提高数据的查询效率，有着非常好的效果。对一些需要重复查询的数据、或者数据修改不怎么多的表设置索引，无疑是一个不错的选择。 另外，要慎用存储过程。虽然说存储过程可以帮助大家实现很多需求。但是，在万不得已的情况下，不要使用存储过程。而利用前台的应用程序来实现需求。这主要是因为在通常情况下，前台应用程序的执行效率往往比后台数据库存储过程要高的多。 第五层：SQL 语句 若以上各个层面你都努力过，但是还不满足由此带来的效果的话，则还有最后一招。通过对SQL语句进行优化，也可以达到改善数据库性能的目的。 虽然说SQL Server服务器自身就带有一个SQL语句优化器。他会对用户的SQL语句进行调整、优化，以达到一个比较好的执行效果。但是，据笔者的了解，这个最多只能够优化一些粗略的层面。或者说，80%的优化仍然需要数据库管理员的配合。要数据库管理员跟SQL优化器进行配合，才能够起到非常明显的作用。 不过，SQL语句的调整对于普通数据库管理员来说，可能有一定的难度。除非受过专业的训练，一般很难对SQL语句进行优化。还好笔者受过这方面的专业训练，对这方面有比较深的认识。如在SQL语句中避免使用直接量。任何一个包含有直接量的SQL语句都不太可能被再次使用。我们数据库管理员要学会利用主机变量来代替直接量。不然，这些不可再用的查询语句将使得程序缓存被不可再用的SQL语句填满。这都是平时工作中的一些小习惯。 总之，笔者认为，在数据库性能调优的时候，若能够遵循如上的顺序，必定可以让我们少走冤枉路，不花无用功。其实，数据库调优并没有我们想象的这么难。只要我们能够掌握其中的诀窍，数据库调优将可以手到擒来。上文中讲到的提高SQL Server数据库性能大家要灵活运用，这些方法也不是什么灵丹妙药，要根据具体情况去操作，希望这篇文章在供大家参考的时候能够帮到大家。

View Details

大内存SQLServer数据库的加速剂

导读：为数据库配置比较大的内存，可以有效提高数据库性能。因为数据库在运行过程中，会在内存中划出一块区域来作为数据缓存。通常情况下，用户访问数据库时，数据先会被读取到这个数据缓存中。当下次用户还需要访问这个数据时，就会从这个数据缓存中读取。因为在数据缓存中读取数据要比在硬盘上读取数据快几百倍。所以扩大数据库服务器内存，可以有效提高数据库性能，特别是操作大型数据库时效果更加明显。 但是，现在企业中普遍采用的数据库服务器都是32位的操作系统。而这个32位的操作系统却有最大内存的使用限制。通常情况下，标准的32位地址最多可以采用4GB的内存。若数据库管理员想让数据库系统采用更多的内存来提高数据库的性能，则就需要进行额外的配置。下面笔者就介绍两种常用的配置方式，让SQLServer数据库服务器支持大内存，让其成为数据库的加速剂。 一、让数据库应用程序支持3GB的内存空间 虽然操作系统支持4GB内存。可是，这并不会全部给数据库等应用程序使用。默认情况下，在32位操作系统中，将有2GB的内存空间是为操作系统所保留的。即使没有用完，其他应用程序也是不能够染指的。而包含SQL Server数据库在内的所有应用程序，只能过采用剩余的2GB内存空间。 但是，在实际应用中，操作系统往往用不着多大2G的内存。根据笔者的经验，一般只要为操作系统保留1G的内存已经足够其使用。只要没有病毒等不良因素作怪，这个内存不会被完全适用。如此的话，应用程序可以采用的内存空间就会多达3G，比原先整整多出一个G来。 要实现这个转变，其实很简单。在Windows操作系统中，有一个BOOT启动配置文件。为了让数据库服务器支持3GB的用户模式进程空间，必须在这个配置文件中，加入一个/3gb的参数，然后重新启动操作系统即可。这么设置之后，应用程序就可以寻址3GB的进程地址空间 ，而为操作系统保留1GB的内存空间。 有时候，这个小小的配置可以在很大程度上提高数据库的性能。记得有一次，笔者为一家企业优化数据库性能。笔者查看了用户的数据库环境之后，就建议用户增大数据库服务器的内存，从2G增加到4G。可是，效果并没有很大的改善。正当笔者束手无措的时候，就想到了改变操作系统与应用程序的内存分配方式。为此，笔者就更改了BOOT启动配置文件，只给操作系统保留1G的内存空间。重新启动后，数据库性能得到了很大的改善。 二、为SQLServer启用更高的内存支持 如果数据库应用程序内存寻址空间达到3GB后，数据库管理员还不满足的话，则就需要通过增加物理内存的方式，来提高应用程序的性能。若需要服务器操作系统突破其默认4GB内存空间的限制，支持4GB以上的内存空间，也不是不可能的。只是需要进行额外的配置，并且，其维护的工作量也比较大。 若想要SQLServer数据库支持4GB以上的内存寻址空间，则往往需要进行如下配置。 第一步：锁定内存页。 默认情况下，内存大小与操作系统的虚拟内存之间有一个正比例关系。在这里，数据库管理员只想增大服务器的物理内存，而不想对虚拟内存有什么影响。故需要锁定内存页。锁定内存页的主要作用就是确定哪些帐户可以使用进程将数据保留在物理内存中，从而阻止系统将数据分页到磁盘的虚拟内存中。默认情况下，这个选项的只为OFF。也就是说，在必要的时候，系统会将数据分页到硬盘的虚拟空间中。为了最大程度发挥内存的效用，就需要把这个选项开启。不过这数据库管理员往往需要寻求系统管理员的帮助，因为只有具有系统管理员权限的用户，才能够给更改这个选项。 第二步：启用Awe Enable选项。 默认情况下，即使服务器操作系统支持4GB以上的内存空间，可是数据库应用程序并不一定支持。为了让SQLServer应用程序也支持这个，就必须更改数据库的配置参数。也就是说，需要将这个选项的值设置为1，然后重新启动数据库系统。这个配置比较简单，只需要利用命令sp_configure 'awe enabled', 1即可。不过在进行这个配置之前，需要注意两个细节方面的内容。一是数据库用户需要这个操作的权限。二是这里有一个BUG，即在SQL Server数据库中会有一个错误信息。数据库管理员可以忽略这个信息。 第三步：限制文件系统缓存。 若增加的内存给操作系统或者其他应用程序用了，那么数据库管理员不是白忙一场吗?为此，数据库管理员还需要优化数据库系统内存的使用情况。如需要限制系统用于文件缓存的内存量。如要这么处理的话，只需要简单的三个步骤即可。 首先，数据库管理员在操作系统中，找到控制面板，并双击网络连接，然后选中本地连接。其次，双击本地连接，在弹出的对话框中，找到常规选项卡，单击属性。选中网络文件与打印机共享，并单击属性。最后，在弹出的对话框中，去掉“最大化网络应用程序数据吞吐量”复选框。一路按确认即可。这个简单的步骤，就可以优化数据库内存的使用率。 三、大内存维护管理几个关键点 在通常情况下，往往不需要启用4GB以上的内存。但是，若在服务器上，同时启用了其他的应用程序服务。如在一台服务器上同时有数据库应用程序、邮件应用程序、文件服务器等多个应用服务的话，则可能原有的4GB内存无法满足。系统管理员不得不对内存进行升级。但是，对内存升级之后，数据库管理员需要手工对内存的分配进行干预，以免SQLServer应用程序占用比较多的内存空间，而影响其他应用程序的性能。 1、配置max server memory选项。虽然说这个选项并不是必须要修改的，但是笔者仍强烈建议数据库管理员要修改这个选项。特别是数据库应用程序与其他应用程序共享同一台服务器时。因为启动SQLServer对大内存的支持后(将Awe Enabled设置为1)，而且可用物理内存大于用户模式进程空间。则当启动数据库服务器时，运行的SQLServer实例将会占用几乎所有的可用内存(不管需不需要使用，数据库服务器程序会先锁定这些内存。这就叫占着茅坑不拉屎)。而这个max server memory选项就是用来配置其最大可以占用的内存数量。数据库管理员需要预先估算出一个合理的数值，然后进行配置。让数据库应用程序与其他应用服务能够共同改善，至少不能够对其他应用程序的性呢产生不良影响。在比较极端的情况下，可以在升级内存之前，先关闭数据库应用程序;然后启用其他应用程序服务。观测一段时间，看看他们所需要用到多少的内存。然后升级内存，并为其他应用程序至少保留以前所需要的内存空间。否则的话，就会对其他应用程序产生不良影响。牺牲其他应用程序的性能来提高数据库的性能，这是拆西墙补东墙的做法，不值得取。 2、多个SQLServer实例内存如何分配。往往在一个SQLServer数据库中，会配置多个数据库实例。一个数据库实例用来负责ERP系统的运行，另外一个则是给CRM系统使用。在同一个数据库系统中，有多个数据库实例，此时该如何在各个实例之间分配可用的内存呢?这基于操作系统的不同，又有所不同。如果数据库操作系统采用的是2000系列的，则需要为每个数据库实例配置max server memory选项。否则的话，其中某一个数据库实例就有可能锁定全部的可用内存。这主要是因为2000系列的服务器系统并不支持动态分配大内存。所以需要为每个数据库实例配置这个选项。但是，若数据库服务器采用的是2003系列的服务器操作系统，则不需要进行手工的配置这些参数。因为操作系统会动态地分配内存。也就是说，操作系统会按照总体系统要求平衡SQLServer各个实例之间内存的使用。不过为了提高数据库整体性能，最好还是为各个实例配置max server memory;而不让操作系统来搞平衡。 从上文中可以总结出大内存是好处多多，大内存加速了SQLServer数据库，使SQLServer数据库的操作性能更为的优化，实现了SQLServer数据库的高速发展。

View Details

改善SQL Server数据库查询速度慢的技巧

SQL Server数据库查询速度慢的原因有很多，常见的有以下几种： 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大(可以采用多次查询，其他的方法降低数据量) 7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列 10、查询语句不好，没有优化 可以通过以下方法来优化查询 : 1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量(尺寸)越大，提高I/O越重要。 2、纵向、横向分割表，减少表的尺寸(sp_spaceuse) 3、升级硬件 4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小，使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段。 5、提高网速。 6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。 配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的1.5倍。如果另外安装了全文检索功能，并打算运行Microsoft搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的3倍。将SQL Server max server memory服务器配置选项配置为物理内存的1.5倍(虚拟内存大小设置的一半)。 7、增加服务器CPU个数;但是必须 明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询 的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE,INSERT， DELETE还不能并行处理。 8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like "a%" 使用索引 like "%a" 不使用索引用 like "%a%" 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。 9、DB Server 和APPLication Server 分离;OLTP和OLAP分离 10、分布式分区视图可用于实现数据库服务器联合体。 联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。(参照SQL帮助文件"分区视图") a、在实现分区视图之前，必须先水平分区表 b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上 运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。 11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。 在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的： 1、 查询语句的词法、语法检查 2、 将语句提交给DBMS的查询优化器 3、 优化器做代数优化和存取路径的优化 4、 由预编译模块生成查询规划 5、 然后在合适的时间提交给系统处理执行 6、 最后将执行结果返回给用户。 SQL Server数据库查询速度慢给很多用户带来很多的不便，而且也影响了工作的进程，上面的方法就能彻底改变慢的状况，给SQL Server数据库查询加速。

View Details

浅析SQL Server 2008中的代码安全之六：对称密钥加密

证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据。而对称密钥相对简单，它们包含一个同时用来加密和解密的密钥。困此，使用对称密钥加密数据更快，并且用在大数据时更加合适。尽管复杂度是考虑使用它的因素，但它仍然是一个很好的加密数据的选择。 我们看一组例子： 示例一、创建对称密钥 对称密钥的特性是：在数据库会话中使用它对数据进行加密和解密前必须首先打开。 创建对称密钥使用如下命令:CREATE SYMMETRIC KEY  创建对称密钥。（http://msdn.microsoft.com/en-us/library/ms188357.aspx）

示例二、查看当前数据库中的对称密钥 使用目录视图sys.symmetric_keys(http://msdn.microsoft.com/en-us/library/ms189446.aspx)来查看。

示例三、修改非对称密钥的加密方式 你可以使用ALTER SYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）命令修改对称密钥的加密方式。但执行前必须使用OPEN SYMMETRIC KEY(http://msdn.microsoft.com/en-us/library/ms190499.aspx)命令打开它。

示例四、使用对称密钥对数据进行加密和解密。 1、为了使用对称密钥对数据进行加密，必须首先打开它，然后使用函数EncryptByKey 加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx) 2、使用DecryptByKey来解密使用对称密钥加密的数据。注意DecryptByKey不像甩EncryptByKey，无须使用对称密钥GUID。因此，为了解密，必须打开正确的对称密钥会话，否则会显示null。 下面是一个例子：

查看未加密的数据： --解密数据

至此，好像已经大功告成了，别，千万别高兴得太早！ 这里有个问题，如果恶意用户不知道CustomerID＝13的PasswordHintAnswer列的真实值，但知道CustomerID＝14的PasswordHintAnswer列的真实值,则完全可以通过恶意替换PasswordHintAnswer列而绕过加密！！此时，我们索性连CustomerID列作为验证列也一起加密，以绝后患 ! 注意：加密的验证列也可以由另一个相关表的列作为参数传入。 看一个完整的例子：

恶意替换开始：

此时，我们再查看：

郎勒个郎！爽吧！虽然复制了相同的二进制数据，可是读取结果令攻击者大失所望啊！ 示例五、删除对称密钥 命令：DROP SYMMETRIC KEY 删除指定的对称密钥( http://technet.microsoft.com/en-us/library/ms182698.aspx) 例子：

注意：如果加密密钥打开没有关闭，则drop失败。 小结： 1、本文主要介绍对称密钥的创建、删除、查看以及用它来修改加密方式、进行数据的加密和解密。 2、对称密钥的特性是：在数据库会话中使用它对数据进行加密和解密前必须首先打开。 3、对称密钥可用于大数据的加密。 下文将主要介绍证书加密(Certificate Encryption) 原文链接：http://www.cnblogs.com/downmoon/archive/2011/03/15/1984352.html

View Details