Category Archives: Database

注意那些容易被忽略的SQL注入技巧

导读：下面我要谈到一些Sqlserver新的Bug（注入），这些都是从长期的工作中总结出来的经验，现在拿出来与大家一起分享，希望能够对大家有所帮助。 1．关于Openrowset和Opendatasource 可能这个技巧早有人已经会了，就是利用openrowset发送本地命令。通常我们的用法是（包括MSDN的列子）如下： select * from openrowset('sqloledb',’myserver';’sa';",’select * from table') 可见（即使从字面意义上看)openrowset只是作为一个快捷的远程数据库访问，它必须跟在select后面，也就是说需要返回一个recordset 。那么我们能不能利用它调用XP_cmdshell呢？答案是肯定的！ select * from openrowset('sqloledb',’server';’sa';",’set fmtonly off exec master.dbo.XP_cmdshel l "dir c:\"') 必须加上setfmtonlyoff用来屏蔽默认的只返回列信息的设置，这样XP_cmdshell返回的output集合就会提交给前面的select显示，如果采用默认设置，会返回空集合导致select出错，命令也就无法执行了。那么如果我们要调用sp_addlogin呢，他不会像XP_cmdshell返回任何集合的，我们就不能再依靠fmtonly设置了，可以如下操作： select * from openrowset('sqloledb',’server';’sa';",’select "OK!" exec master.dbo.sp_addlogin Hectic') 这样，命令至少会返回select OK!’的集合，你的机器商会显示OK!，同时对方的数据库内也会增加一个Hectic的账号，也就是说，我们利用select 'OK!’的返回集合欺骗了本地的select请求，是命令能够正常执行，通理sp_addsrvrolemember和opendatasource也可以如此操作！至于这个方法真正的用处，大家慢慢想吧。 2．关于Msdasql两次请求的问题不知道大家有没有试过用msdasql连接远程数据库，当然这个api必须是sqlserver的管理员才可以调用，那么如下： select * from openrowset('msdasql',’driver={sql server};server=server;address=server,1433;uid=sa;pwd=;database=master;network=dbmssocn',’s elect * from table1 select * from table2′) 当table1和table2的字段数目不相同时，你会发现对方的sqlserver崩溃了，连本地连接都会失败，而系统资源占用一切正常，用pskill杀死 sqlserver进程后，如果不重启机器，sqlserver要么无法正常启动，要么时常出现非法操作，我也只是碰巧找到这个bug的，具体原因我还没有摸透，而且很奇怪的是这个现象只出现在msdasql上，sqloledb就没有这个问题，看来问题不是在于请求集合数目和返回集合数目不匹配上，应该还是msdasql本身的问题，具体原因，大家一起慢慢研究吧。 3．可怕的后门以前在网上看到有人说在 sqlserver上留后门可以通过添加triger、jobs或改写sp_addlogin和sp_addsrvrolemember做到，这些方法当然可行，但是很容易会被发现。不知道大家有没有想过sqloledb的本地连接映射。呵呵，比如你在对方的sqlserver上用sqlserver的管理员账号执行如下的命令： select * from openrowset('sqloledb',’trusted_connection=yes;data source=Hectic',’set fmtonly off exec master..XP_cmdshell "dir c:\"') 这样在对方的sqlserver上建立了一个名为Hectic的本地连接映射，只要sqlserver不重启，这个映射会一直存在下去，至少我现在还不知道如何发现别人放置的连接映射，好了，以上的命令运行过后，你会发现哪怕是sqlserver没有任何权限的guest用户，运行以上这条命令也一样能通过！而且权限是 localsystem！（默认安装）呵呵！这个方法可以用来在以被入侵过获得管理员权限的sqlserver上留下一个后门了。以上的方法在 sqlserver2000 sqlserver2000SP1上通过！另外还有一个猜测，不知道大家有没有注意过windows默认附带的两个dsn，一个是localserver一个是msqi，这两个在建立的时候是本地管理员账号连接sqlserver的，如果对方的 sqlserver是通过自定义的power user启动，那么sa的权限就和power user一样，很难有所大作为，但是我们通过如下的命令： select * from openrowset ('msdasql',’dsn=locaserver;trusted_connection=yes',’set fmtonly off execmaster..XP_cmdshell "dir c:\"') 应该可以利用localserver的管理员账号连接本地sqlserver然后再以这个账号的权限执行本地命令了，这是后我想应该能突破sa那个power user权限了。现在的问题是sqloledb无法调用dsn连接，而msdasql非管理员不让调用，所以我现在正在寻找guest调用msdasql […]

龙生 23 Mar 2011

View Details

SQL语句的22条法宝

SQL语句的22条法宝： 1、说明：备份sql server— 创建备份数据的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'— 开始备份BACKUP DATABASE pubs TO testBack 2、说明：增加一个列Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 3、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 4、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)法一：select * into b from a where 1<>1法二：select top 0 * into b from a 5、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)insert into b(a, b, c) select d,e,f from b; 6、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 7、说明：显示文章、提交人和最后回复时间select a.title,a.username,b.adddate from […]

龙生 23 Mar 2011

View Details

SQL Server数据库维度表和事实表概述

SQL Server数据库维度表和事实表概述: 一、事实表每个数据仓库都包含一个或者多个事实数据表。事实数据表可能包含业务销售数据，如现金登记事务所产生的数据，事实数据表通常包含大量的行。事实数据表的主要特点是包含数字数据(事实)，并且这些数字信息可以汇总，以提供有关单位作为历史的数据，每个事实数据表包含一个由多个部分组成的索引，该索引包含作为外键的相关性纬度表的主键，而维度表包含事实记录的特性。事实数据表不应该包含描述性的信息，也不应该包含除数字度量字段及使事实与纬度表中对应项的相关索引字段之外的任何数据。包含在事实数据表中的“度量值”有两中：一种是可以累计的度量值，另一种是非累计的度量值。最有用的度量值是可累计的度量值，其累计起来的数字是非常有意义的。用户可以通过累计度量值获得汇总信息，例如。可以汇总具体时间段内一组商店的特定商品的销售情况。非累计的度量值也可以用于事实数据表，单汇总结果一般是没有意义的，例如，在一座大厦的不同位置测量温度时，如果将大厦中所有不同位置的温度累加是没有意义的，但是求平均值是有意义的。一般来说，一个事实数据表都要和一个或多个纬度表相关联，用户在利用事实数据表创建多维数据集时，可以使用一个或多个维度表。二、维度表维度表可以看作是用户来分析数据的窗口，纬度表中包含事实数据表中事实记录的特性，有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据，以便为分析者提供有用的信息，维度表包含帮助汇总数据的特性的层次结构。例如，包含产品信息的维度表通常包含将产品分为食品、饮料、非消费品等若干类的层次结构，这些产品中的每一类进一步多次细分，直到各产品达到最低级别。在维度表中，每个表都包含独立于其他维度表的事实特性，例如，客户维度表包含有关客户的数据。维度表中的列字段可以将信息分为不同层次的结构级。三、结论: 1、事实表就是你要关注的内容; 2、维度表就是你观察该事务的角度，是从哪个角度去观察这个内容的。例如，某地区商品的销量，是从地区这个角度观察商品销量的。事实表就是销量表，维度表就是地区表。维度表和事实表在SQL Server数据库的操作中是很常用的两种表，学会了这两种表的应用，在操作SQL Server数据库时就会容易很多，初学者也可以首先学习这两种表的操作使用技巧。

龙生 23 Mar 2011

View Details

在SQL Server数据库中拆分字符串函数

SQL Server数据库中拆分字符串函数的具体方法： CREATE FUNCTION uf_StrSplit '1.1.2.50','.'(@origStr varchar(7000), --待拆分的字符串@markStr varchar(100)) --拆分标记，如','RETURNS @splittable table(　　str_id varchar(4000) NOT NULL, --编号ID　　string varchar(2000) NOT NULL --拆分后的字符串)ASBEGINdeclare @strlen int,@postion int,@start int,@sublen int,@TEMPstr varchar(200),@TEMPid intSELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,@TEMPstr=",@TEMPid=0if(RIGHT(@origStr,1)<>@markStr )beginset @origStr = @origStr + @markStrendWHILE((@postion<=@strlen) and (@postion !=0))BEGINIF(CHARINDEX(@markStr,@origStr,@postion)!=0)BEGINSET @sublen=CHARINDEX(@markStr,@origStr,@postion)-@postion;ENDELSEBEGINSET @sublen=@strlen-@postion+1;ENDIF(@postion<=@strlen)BEGINSET @TEMPid=@TEMPid+1;SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen);INSERT INTO @splittable(str_id,string)values(@TEMPid,@TEMPstr)IF(CHARINDEX(@markStr,@origStr,@postion)!=0)BEGINSET @postion=CHARINDEX(@markStr,@origStr,@postion)+1ENDELSEBEGINSET @postion=@postion+1ENDENDENDRETURNEND 例如：select * from uf_StrSplit('1，1，2，50′,’，') 输出结果： str_id string1 12 13 24 50 上文中涉及到很多的字符，对于一些初学者来说，可能是比较难理解，但是这个知识点确实是经常会用到的，而且相当实用，希望大家好好学习，争取能熟练的掌握。

龙生 23 Mar 2011

View Details

SQL Server数据库多种方式查找重复记录

SQL Server数据库多种方式查找重复记录: 示例：表stuinfo，有三个字段recno(自增)，stuid，stuname 建该表的Sql语句如下： CREATE TABLE [StuInfo] ([recno] [int] IDENTITY (1， 1) NOT NULL ，[stuid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ，[stuname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL) ON [PRIMARY]GO 1.查某一列(或多列)的重复值(只可以查出重复记录的值，不能查出整个记录的信息) 例如:查找stuid，stuname重复的记录 select stuid，stuname from stuinfogroup by stuid，stunamehaving(count(*))>1 2.查某一列有重复值的记录(此方法查出的是所有重复的记录，如果有两条记录重复的，就查出两条) 例如:查找stuid重复的记录 select * from stuinfowhere stuid in (select stuid from stuinfogroup by stuidhaving(count(*))>1) 3.查某一列有重复值的记录(只显示多余的记录，也就是说如果有三条记录重复的，就显示两条) 前提：需有一个不重复的列，此示例为recno。例如:查找stuid重复的记录 select * from stuinfo s1where recno not in (select max(recno) from stuinfo s2where s1.stuid=s2.stuid 关于SQL Server数据库中查询重复记录的方法就为大家介绍到这，这里介绍的方法可能也是不够全面的，以后如果有了更新的方法，我会及时与大家继续分享，希望对大家能有所帮助。

龙生 23 Mar 2011

View Details

提高SQL执行效率的几点建议

提高SQL执行效率的几点建议: 尽量不要在where中包含子查询：关于时间的查询，尽量不要写成：where to_char(dif_date,’yyyy-mm-dd')=to_char('2007-07-01′,’yyyy-mm-dd'); 在过滤条件中，可以过滤掉最大数量记录的条件必须放在where子句的末尾：FROM子句中写在最后的表(基础表，driving table)将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有三个以上的连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表; 采用绑定变量在WHERE中尽量不要使用OR 用EXISTS替代IN、用NOT EXISTS替代NOT IN; 避免在索引列上使用计算：WHERE SAL*12>25000; 用IN来替代OR： WHERE LOC_ID=10 OR LOC_ID=15 OR LOC_ID=20 避免在索引列上使用IS NULL和IS NOT NULL; 总是使用索引的第一个列; 用UNION-ALL替代UNION; 避免改变索引列的类型：SELECT…FROM EMP WHERE EMPNO=’123’，由于隐式数据类型转换，to_char(EMPNO)=’123’，因此，将不采用索引，一般在采用字符串拼凑动态SQL语句出现; '!=' 将不使用索引; 优化GROUP BY; 避免带有LIKE参数的通配符，LIKE '4YE%’使用索引，但LIKE '%YE’不使用索引避免使用困难的正规表达式，例如select * from customer where zipcode like "98___"，即便在zipcode上建立了索引，在这种情况下也还是采用顺扫描的方式。如果把语句改成select * from customer where zipcode>"98000"，在执行查询时就会利用索引来查询，显然会大大提高速度; 尽量明确的完成SQL语句，尽量少让数据库工作。比如写SELECT语句时，需要把查询的字段明确指出表名。尽量不要使用SELECT *语句。组织SQL语句的时候，尽量按照数据库的习惯进行组织。相信每个人都想提高SQL执行效率，那么大家不妨试试这几条建议，肯定不会让大家失望的，希望这些小建议能够对大家有所帮助。

龙生 23 Mar 2011

View Details

浅谈SQL Server2005的几种分页方法

SQL Server分页查询是我们经常会用到的功能，其实现方法也有很多，本文的几种分页方法摘自《SQL Server2005性能调优》一书。希望对您学习SQL Server分页查询方面能有所帮助。用以下脚本生成测试数据：

<ol class="dp-sql"><li class="alt">CREATE TABLE TRANS_TABLE(  </li><li>        MYID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,  </li><li class="alt">        MYDESC VARCHAR(10),  </li><li>        MYDATE DATETIME,  </li><li class="alt">        MYGROUPID INT)  </li><li>DECLARE @I INT </li><li class="alt">SET @I = 0WHILE @I < 1000000  </li><li>BEGIN </li><li class="alt">    INSERT INTO TRANS_TABLE  </li><li>    SELECT CHAR(ASCII('A') - 2 + (2 * (1 + ABS(CHECKSUM(NEWID())) % 26))),  </li><li class="alt">                 DATEADD(day, ABS(CHECKSUM(NEWID())) % 365, '01/01/2007'),  </li><li>                 (ABS(CHECKSUM(NEWID())) % 10)  </li><li class="alt">    SET @I = @I + 1  </li><li>END </li><li class="alt">CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYDATE  </li><li>ON TRANS_TABLE(MYDATE)  </li><li class="alt">CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYGROUPID  </li><li>ON TRANS_TABLE(MYGROUPID)  </li><li class="alt"> </li></ol>

1、基于CTE分页 1）用row_number()排名函数，派生表的方式分页

<ol class="dp-sql"><li class="alt">DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25  </li><li class="alt">select *  </li><li>  from ( select p.*, rownum rnum  </li><li class="alt">         FROM (  </li><li>            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *  </li><li class="alt">            FROM TRANS_TABLE (NOLOCK)  </li><li>                ) p  </li><li class="alt">          where rownum <= @START_ROW + @MAX_ROWS - 1  </li><li>       )  </li><li class="alt"> z where rnum >= @START_ROW  </li><li> </li></ol>

2）用CTE方式取代派生表

<ol class="dp-sql"><li class="alt">DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25;  </li><li class="alt">WITH PAGED AS (  </li><li>            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,     *  </li><li class="alt">            FROM TRANS_TABLE (NOLOCK)  </li><li>            )  </li><li class="alt">SELECT *  </li><li>FROM PAGEDWHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS-1 </li></ol>

3）也是CTE方法，但是根据测试数据显示这种性能比前两种都好

<ol class="dp-sql"><li class="alt">DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25;  </li><li class="alt">WITH PAGED AS (  </li><li>            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID  </li><li class="alt">            FROM TRANS_TABLE (NOLOCK)  </li><li>            )  </li><li class="alt">SELECT TT.*  </li><li>FROM PAGED PGD  </li><li class="alt">INNER JOIN TRANS_TABLE TT  </li><li>ON PGD.MYID = TT.MYID  </li><li class="alt">WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1  </li><li>ORDER BY MyDate, MYID  </li></ol>

2、基于ROW_COUNT的分页

<ol class="dp-sql"><li class="alt">DECLARE     @START_ID int, @START_ROW int, @MAX_ROWS int,  </li><li>         @START_DATETIME DATETIME, @TOT_ROW_CNT INT </li><li class="alt">SELECT @START_ROW = 1, @MAX_ROWS = 25  </li><li>-- Get the first row for the page  </li><li class="alt">SET ROWCOUNT @START_ROW  </li><li>SELECT @START_ID = MYID, @START_DATETIME = MYDATE FROM TRANS_TABLE (NOLOCK)  </li><li class="alt">        ORDER BY MYDATE, MYID  </li><li>-- Now, set the row count to MaximumRows and get  </li><li class="alt">-- all records >= @first_idSET ROWCOUNT @MAX_ROWS  </li><li>SELECT *  </li><li class="alt">FROM TRANS_TABLE (NOLOCK)  </li><li>WHERE MYID >= @START_ROW  </li><li class="alt">AND MYDATE >= @START_DATETIME  </li><li>ORDER BY MYDATE, MYID  </li><li class="alt">SET ROWCOUNT 0  </li></ol>

3、 TOP @X分页 SQL Server 2005中可以把返回行数做为参数传给top语句。

<ol class="dp-sql"><li class="alt">DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT INT, @START_DESC VARCHAR(10)  </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25  </li><li class="alt">-- Get the first row for the page  </li><li>SELECT TOP(@START_ROW) @START_ID = MYID, @START_DESC = MYDESC FROM TRANS_TABLE (NOLOCK)  </li><li class="alt">    ORDER BY MYDESC, MYID  </li><li>SELECT TOP(@MAX_ROWS) *  </li><li class="alt">FROM TRANS_TABLE (NOLOCK)  </li><li>WHERE MYID >= @START_ROW  </li><li class="alt">AND MYDESC >= @START_DESC  </li><li>ORDER BY MYDESC, MYID  </li><li class="alt"> </li></ol>

4、 Temp表分页

<ol class="dp-sql"><li class="alt">DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25;  </li><li class="alt">    SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,  </li><li>         MYID  </li><li class="alt">    into #TEMP </li><li>    FROM TRANS_TABLE (NOLOCK)  </li><li class="alt">SELECT TT.*  </li><li>FROM TRANS_TABLE (NOLOCK) TT  </li><li class="alt">INNER JOIN #TEMP TON TT.MYID = T.MYID  </li><li>WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1  </li><li class="alt">DROP TABLE #TEMP </li><li> </li></ol>

以上便是这次为您介绍的 SQL Server 2005中几种分页方法，希望对您学习SQL Server分页查询方面能有所帮助。原文链接：http://www.cnblogs.com/qiuwuyu/archive/2011/03/21/1989870.html

龙生 23 Mar 2011

View Details

用SQL查询日月周季

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

1	<ol class="dp-sql"><li class="alt"><span><span>datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,[Datetime],getdate())=0  把Datetime换为你的相应字段； </span></span></li></ol>

SQL查询昨天的记录：

1	<ol class="dp-sql"><li class="alt"><span><span>datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,[Datetime],getdate())=1  把Datetime换为你的相应字段，getdate()-Datetime即为时间差。 </span></span></li></ol>

本月记录：

<ol class="dp-sql"><li class="alt">SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0 </li></ol>

本周记录：

<ol class="dp-sql"><li class="alt">SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0  </li></ol>

本日记录：

<ol class="dp-sql"><li class="alt">SELECT * FROM 表 WHERE datediff(day,[dateadd],getdate())=0 </li></ol>

一天

<ol class="dp-sql"><li class="alt">select * from T_news where datediff(day,addtime,getdate())=0   </li></ol>

三天

一周

<ol class="dp-sql"><li class="alt">select * from T_news WHERE (DATEPART(wk, addtime) = DATEPART(wk, GETDATE())) AND (DATEPART(yy, addtime) = DATEPART(yy, GETDATE()))   </li></ol>

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

<ol class="dp-sql"><li class="alt">select * from T_news WHERE (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) AND (DATEPART(mm, addtime) = DATEPART(mm, GETDATE()))   </li></ol>

一季度

<ol class="dp-sql"><li class="alt">select * from T_news where DATEPART(qq, addtime) = DATEPART(qq, GETDATE()) and DATEPART(yy, addtime) = DATEPART(yy, GETDATE()) </li></ol>

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

龙生 23 Mar 2011

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）仅在主键上建立聚集索引，并且不划分时间段：

1	<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">Select</font></strong></span><span> gid,fariqi,neibuyonghu,title </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> tgongwen </span></span></li></ol>

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

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

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

1	<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> @d datetime  </span></span></li><li><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> @d=getdate() </span></li></ol>

并在select语句后加：

1	<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) </span></span></li></ol>

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

查询速度：2513毫秒

<ol class="dp-sql"><li class="alt">select gid,fariqi,neibuyonghu,title from Tgongwen  </li><li>where fariqi>''2004-5-5'' and neibuyonghu=''办公室'' </li></ol>

查询速度：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毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的）

<ol class="dp-sql"><li class="alt">select gid,fariqi,neibuyonghu,reader,title from Tgongwen  </li><li>where fariqi>''2004-1-1'' and fariqi<''2004-6-6'' </li></ol>

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

<ol class="dp-sql"><li class="alt">select gid,fariqi,neibuyonghu,reader,title from Tgongwen  </li><li>where fariqi>''2004-1-1'' order by fariqi </li></ol>

用时：6390毫秒

<ol class="dp-sql"><li class="alt">select gid,fariqi,neibuyonghu,reader,title from Tgongwen  </li><li>where fariqi<''2004-1-1'' order by fariqi </li></ol>

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

龙生 23 Mar 2011

View Details

« Previous 1 … 41 42 43 … 45 Next »

November 2024
M	T	W	T	F	S	S
« Oct
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30