数据库SQLServer经验小记

目录(?)[-]

2. WindowsSQL2000 性能测试结果
3. 数据库SQL语句规则和优化方案
4. 数据库基本原理描述
5. 误区
6. 建议
7. 其他

数据库SQLServer经验小记

Author:FreeKnight

Date: 2011-3-8

Version: 1.0.0

1：WindowsSQL2000 性能测试结果

1>     测试方面

MSSQL是关系型数据库，该类数据库在随着数量级或者容量级的数据急剧膨胀下，数据库的性能将会有飞速下降。所以重点测试以下情况。

@ 当数据库在大容量状态下的执行情况。

@ 当数据库在大记录量状态下的执行情况。

2>     测试环境

@ OS：    Windows 2000 Server

@ DB:        MS SQL Server 2000

@ DBS:      ADV2000

@ 连接方式：ADO

3>     测试步骤。

@ 创建数据库 test ，设置其大小为10GB。（免除了动态内存分配引起的性能代价）

@ 在 test 数据库里创建一个 tab 表，包含以下字段

           字段名     数据类型         字段情况                可否为空

           ID              int, 4                主键，自叠加         不可

           Name       char,10              --                                   不可

           Age           int,4                   --                                   不可

           Photo        Image,16          --                                   可

@ 使用ADO接口，链接数据库服务器，测试程序完成以下功能

1)       插入2000条数据 insert

2)       选择1000条数据 select

3)       更新1000条数据 update

4)       删除1000条数据 delete

5)       插入100000条带图片的数据 （大容量测试）

6)       插入1000000条不带图片的数据（大记录量测试）

4>     结果分析和推论

@ 大容量数据测试中，我们选择的是一张 41958 字节的图片，插入的是100000条记录，我们大约可以估算当时数据表内的容量为 ( 41958 * 100000 ) / ( 1024 * 1024 ) = 4001.43 MB.

@ 测试结果1为

对一个空表进行操作，其中每个记录内的Photo字段是一张字节为41958的图片。

对空表内Insert 2000 记录                                                  132.781秒

对2000记录的表进行选择1000个记录                        41.94秒

对2000记录的表进行1000个记录的Update              0.841秒

对2000记录的表delete其中1000条记录          1.552秒

                   结论：

插入新记录代价远比更新删除记录代价大。

即使数据量不大，选择记录的代价也比较大。

@ 测试结果2为

对一个已经插入了100000条带图片记录的数据库表进行处理，该数据库表约4001.43MB大小。

         对该表内Insert2000记录                                                    139.05秒

         对102000条数据进行选择1000条记录                        42.36秒

         对102000条数据进行1000条数据的update              0.971秒

         对102000条数据delete其中1000条记录          2.264秒

结论：

         在大数据量（百万条数据，表大小为4001MB左右时）对SQL2000影响并不大，整体性能基本不会有太大变动。

         但是在大容量数据环境下，时间增加的百分比则不同了。如下

两次Insert2000数据的时间增加比：                  4.72%

两次Select1000数据的时间增加比：                  1.001%

两次Update1000数据的时间增加比：               15.46%

两次Delete1000数据的时间增加比：                 45.88%

表中可看出，Delete操作时，大容量环境下降性能损失较为严重。原因是删除记录时，数据库会重建索引，所以当数据量太大的情况下，会导致重建索引消耗更多的时间。

                   @测试结果3为

                   对一个空表进行操作，但是与测试1不同的是，Photo字段为空，不再插入任何数据。

对空表内Insert 2000 记录                                                  16.274秒

对2000记录的表进行选择1000个记录                        0.07秒

对2000记录的表进行1000个记录的Update              0.04秒

对2000记录的表delete其中1000条记录          0.04秒

                   结论：

                            当字段数据量小的时候，数据库操作将有大幅度的效率提高。

                   @测试结果4为

                   对一个表内增加1000000条数据。（注意，比测试2多了10倍），这些数据内photo字段为空。

对该表内Insert2000记录                                                    16.574秒

         对1002000条数据进行选择1000条记录                      0.05秒

         对1002000条数据进行1000条数据的update            0.05秒

         对1002000条数据delete其中1000条记录                 0.051秒

结论：

                            在大数据量下，MSSQL性能损失不大。

5>     总体结论

1：MSSQL 在常规的大容量和大记录量环境下，效能可以保持较高水平。

2：避免单表字段容量过大，会较大影响性能。

3：大容量和大记录量对MSSQL影响并不致命。

4：插入新数据代价较大。

2：数据库SQL语句规则和优化方案

         1：where条件中不使用非索引列。若未建立索引，则该列就不存在index seek，只能用传统的index scan或table scan，性能消耗极大。

         附加说明：Index seek是一个B树，查询时是从B数根节点开始，一级一级查找到目标。而Index scan则是全部遍历一遍。

         2：尽量避免使用 不等号（!=）或NOT 逻辑运算符。这两个运算符相当于强制要求数据库进行全员遍历index scan。

         3：查询条件中不要包含运算，这样将导致无法进行index seek只能进行index scan或table scan了。例如下列都是错误的做法。

         select * from Users where UserName + “Knight “ = “FreeKnight”; // 字符串连接运算。

         Select * from tabel1 where Col1 like ‘%aa’;  // 通配符(“%”)在(“aa”)前面的Like运算。

         Select * from table2 where datapart( dd, logtime ) = 3; // 使用其他自定义函数，标量函数。

4：查询条件内不要包含同一张表内不同列之间的运算。

         例如下列运算都是错误的做法：

         Select * from table1 where ( col1 – col2 = 1997 );  // 加减乘除运算

         Select * from table1 where col1 > col2;  // 比较运算

         这样情况下，即使col1,col2建立了索引，甚至覆盖索引，连携索引均是无效的。遇到这样的情况，可以在表内创建一个计算字段，值设置为运算后的结果，再在该结果字段上创建索引，则可以了。

         5：在一个频繁发生插入操作的表上创建聚簇索引时，不要建在有单调上升（IDENTITY）列上，否则经常引起锁冲突。

         6：在聚簇索引中不要包含经常修改的列，因为当列的码值被修改后，数据行必须移动到新的物理位置。

         7：对有索引的列进行查询时，避免搜索查询结果大于20%数据量。可能被逃开index seek而直接进行table scan。

         8：非聚簇索引避免过大，建议小于20字节的会比较合适。

         9：避免出现select * from table 语句，要明确的查出字段。

         10：在判断有无符合条件的记录时，建议不要使用 select count( * ) 和select top 1语句，以减少数据的处理量。

         11：应该绝对避免在order by子句中使用表达式。

         若一定要在语句中使用表达式，尽量使用

         Select id from table1 where num = 10 * 2

         替代

         Select id from table1 where num/2 = 10

         12：如果需要从关联表读数据，关联的表一般不要超过7个。

         13：小心使用IN和OR，需要注意IN集合中的数据量，建议集合中的数据不要超过200个。在where中使用or将导致数据库无法使用索引而进行全表扫描。

若一定要求使用or,则建议使用

         Select id from table1 where num = 10

         Union all

         Select id from table1 where num = 20

         替代

         Select id from table1 where num = 10 or num = 20

         若一定要使用in或者not in，建议可能的话使用between 替代，或者使用exists代替，例如，使用

                   Select num from tableA where exists( select 1 from tabelB where num = tableA.num )

                   替代

                   Select num from tableA where num in ( select num from tabelB )

         14： <> 尽量使用 < 和 > 替代， > 尽量使用 >= 替代， < 尽量使用 <= 替代，这样可以有效的使用索引，否则数据库将放弃索引而使用全表扫描。

         15：尽量在where和order by 涉及的列上建立索引。

         16：应尽量避免进行null值判断，Null值无法进行索引。对num类型的建议设置默认值为0。

         Select id from table1 where num is null 效率远低于 select id from table1 where num =0;

         17：应该避免在where语句中对字段进行函数操作。

         若一定要求使用，可使用

         Select id from table1 where name like ‘abc%’  //找name前三个字符为abc的数据。

         Select id from table1 where date >= ‘2011-3-7’ and date < ‘2011-3-8’  // 找date等于2011-3-7的数据

         替代

         Select id from table1 where substring( name, 1, 3 ) = ‘abc’

         Select id from table1 where datediff( day, date, ‘2011-3-7’ )

         18：若使用复合索引字段作为条件时，那么必须使用该复合索引中的第一个字段作为where条件才能保证数据库正确使用该索引。若使用多个字段查询，则需要字段顺序与复合索引顺序一致才可以进行。

         19：建立索引会消耗硬盘空间之外，还会加大insert和update效率，适度创建。一般一个表的索引最好不要超过6个。

         20：可以的话，尽量使用数字型字段，避免使用字符型字段，这回降低查询性能和存储开销。数据库对字符串的比对是需要逐字符的比对，数字型的则只需要一次比对。

         21：尽量使用varchar/nvarchar替代char/nchar，因为变长字段存储空间小，且，对于较小的字段进行查询搜索效率也会高一些。

         22：尽量使用表变量( table类型对象 )来替代临时表，避免频繁的创建和删除临时表，可减少系统消耗。因为任何的临时表创建和销毁都会锁住系统表。

         23：若不可避免的使用了临时表，在存储过程最后务必显式的删除临时表，先truncate table再drop table可以避免长时间的表锁定。

         24：避免向数据库用户返回大数据量。

         25：在所有的存储过程和触发器的开始处设置SET NOCOUNT ON，结束时设置SET NOCOUNT OFF。可以设置存储过程中的语句无需返回许多冗余数据，大量减少了返回数据量，可显著提高性能。

         26：在确定结果没有重复性的前提下，Union all效率比union高，性能好。及时有重复，更建议在调用数据库的应用程序上进行重复性检查，因为此时数据已经较小。

         27：若插入大的二进制值到image列，使用存储过程，不要使用内嵌insert。因为这样的话数据库将首先将二进制数据转换为字符串，保存时又将转换为二进制文件，存储过程则可以跳过这些动作。但是更建议的还是二进制数据保存在varbinary类型下，就不会出现上述问题。

         28：尽量避免使用distinct和order by，他们增加了额外开销。这些限制可以在调用数据库的应用程序上执行。

         29：一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快。

         30：一次更新多条记录比多次更新一条记录快，建议进行批处理。

         31：当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。

         32：按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现。

         33：

3：数据库基本原理描述

1：索引块和数据块的区别

                  我们都知道数据库创建索引可以提高效率，但是，索引是如何提高效率的呢？OK。我们假设我们现在一个角色主表内一个记录有8000字节的数据，我们对其角色ID，四个字节的一个字段创建索引，假设我们记录的用户数据为10W个。

                   我们知道SQL Server上最小空间分配单元是”Page页”，一个页在磁盘占用8K空间，那么我们一个页内只能记录一个角色主表记录，但是可以记录角色ID为2000个。

                   如果我们没有索引的话，我们搜索10W个页面才可以找到结果，但，若在检索字段上有索引的话，那么我们只需要搜索 10W / 2000 = 50个页面就可以找到了满足条件的索引块，然后我们根据索引块上的指针找到结果数据块，IO访问少出很多。

         2：聚簇索引和非聚簇索引

                   聚簇索引和非聚簇索引都是平衡树。见图

                  

                   上面是聚簇索引，下面是非聚簇索引

                   若想更直接的发现两图的不同，那就看最底下一排。在聚簇索引下，数据本身是有序的，聚簇索引无需保存每个数据的指针。非聚簇索引则需要保存每个数据的指针，因为数据是无序的，我们只能靠有序的页节点保存指针来有序访问。

                   SQLServer默认缺省情况下创建的索引是非聚簇索引。

                   假设我们没有索引，那么我们查找一个数据时将需要遍历全部的数据，若有索引（无论聚簇与否），我们直接查找它属于哪一段便可以迅速定位到该数据的数据物理位置。

聚簇索引的顺序就是数据的物理存储顺序，所以一个表之可能有一个聚簇索引。

非聚簇索引虽然也是平衡树，但每个索引的节点却是一个指针，指向数据的指针节点，每个指针节点指向一个对应的数据块，在SQLServer中，一个表最多允许与249个非聚簇索引。

因为需要维护索引有一定的代价，要维护额外的平衡数，所以当一个表内索引增多时，对表进行插入删除操作将消耗更大的代价。

我们来计算一下，依旧假设我们一个角色主表内一个记录为8000字节，其中一个需要建立索引的字段长度为4字节。我们记录的用户数据为10W个。

此时，我们若为4字节字段创建非聚簇索引主键，则需要节点为10W×2 = 20W节点，而这20W节点中将有10W个叶节点指向一个数据记录。此时，数据将占用10W×8000 字节/ 8096字节 = 10W个页面。索引将占用 20W × 4字节 / 8096字节 = 100页面。而同样一个表，我们为4字节字段创建聚簇索引主键，由于聚簇索引的叶节点就是数据节点，所以索引节点只有10W个，占50个页面。

         3：索引优化技术

                   有些时候，我们可能使用索引反而会更加麻烦。例如，我们检索全部的表内记录，若我们建立了索引，则会遍历访问全部索引，再根据索引结果查找全部的数据页面，这意味着我们先访问50个页面的索引块，然后根据找到的全部索引再访问全部的数据库10W页面，则消耗10W零50个页面的访问，这显然还不如不用索引。

                  但是SqlServer在上面的情况下，SQLServer的查询计划会自动的进行tableScan表扫描的方式检索数据，而不会使用任何索引。那么SQLServer为什么会做这种判断？这是因为SQLServer除了保存数据之外，还会保存一份数据统计信息。这些数据统计信息里包括了数据的密度信息和数据分布信息，这些信息帮助SQLServer制定查询计划以及查询是否使用索引，以及使用什么样的索引。

                   为SQLServer黄建索引的列，都会付出一定代价来维护这个索引，这时SQLServer会自动分析是否使用该列的索引，若返回结果集比较大的列（例如100W数据返回50W结果的列），SQLServer将可能不使用列上索引，而直接使用table Scan全表扫描的方法。

                   附PS： SET SHOWPLAN_TEXT ON // 可以显示执行计划，查看语句使用了哪些索引

                   索引可以大幅提高数据检索速度，但是like等模糊查询列完全无需设置索引，是没有意义的，只有 = > IN 等操作符时才考虑使用索引。SQLServer在进行数据插入，更新，删除时都会更新相应的索引，这将大大增加更新时间。所以，当查询的性能比更新的性能更重要的时候才创建索引。

         4：复合索引

                   对多个列创建索引，这种索引是复合索引，它是针对于单一索引（对一个列创建索引）相对而言的。

                   创建很容易 create index idx1 on table1( col1, col2, col3 )

                   我们若进行这样的查询，select * from table1 where col1 = A and col2 = B and col3 = C

                   则此时我们无需扫描表数据了，而可以直接从索引中取得数据，这种不需要访问数据的，只访问索引区的查询叫做覆盖式查询。

                  注意：复合索引的索引顺序是重要的，例如上面的复合索引，如果查询语句是

                   select * from table1 where col3 = A and col2 = B and col1 = C 这样的或者

                   select * from table1 where col3 = A 这样都将不会有效的使用复合索引。

         5：include索引/覆盖索引

                   这个索引是SqlServer2005开始提供的新功能。复合索引上面已经提到了，是建立在多个列上的索引。但是”include索引”是允许添加非键列到非聚合索引的叶节点内的索引。它又被称为覆盖索引。

                   覆盖索引是指那些索引项里包含了查询所需要的全部信息的非聚簇索引。换言之，非聚簇索引的叶节点原本只保存了数据的指针，假设我们要根据ID去查找角色名称。那么ID是一个非聚簇索引，若这个非聚簇索引的叶节点内保存了 角色名称 的信息，那么我们就可以直接根据页节点获得我们需要的角色名称信息，免去了根据叶节点内保存的指针信息去查找数据的代价，这样我们可以更高效的查找得到我们需要的数据。它可以大幅的提高性能。

                   覆盖索引因为要求在叶节点保存了列数据，所以，Hash等不能存储值的索引是无效的，只能B-Tree这种设计的数据存储才可以使用。MySQL,SQLServer,Oracl等数据库均是采用的B-Tree.

                   复合索引创建方式如下

                   Create index idx_tbA_col1_col2 on tableA

{

         Col1,

         Col2

}

创建覆盖索引的方式如下

Create index idx_tbA_col1_col2_col3 on tableA

{

         Col1

} include( col2, col3 )

         6：Union和Union All

看个例子：

         Drop table test;

         Create table test

{

         Id int primary key,

         Name nvarchar( 50 ) not null,

};

Insert int test values( 1, ‘Freedom’ );

Insert int test values( 2, ‘knight’  );

Insert int test values( 3, ‘duzhi’ );

Insert int test values( 4, ‘Freedom’ );

Insert int test values( 5, ‘knight’  );

Insert int test values( 6, ‘duzhi’ );

若语句是

Select * from test where id < 3

Union

Select * from test where id > 1 and id < 4

结果将是

1         Freedom

2         Knight

3         Duzhi

若语句是

Select * from test where id < 3

Union all

Select * from test where id > 1 and id < 4

结果将是

1         Freedom

2         Knight

2  Knight

3         Duzhi

可见，两者区别是对结果重复性的检查上。

4：误区

1：primary key 就是聚簇索引。

         A： primary key上必须要有索引，但是需要注意这个索引未必是聚簇索引。例如下面的语句

         Create table A

         {

                   Id int indentity(1,1) primary key,

                   Name varchar(64)

         }

         通常会在id列上创建聚簇索引，主键也经常是被作为聚簇索引，但是，这仅仅是“通常”并非必然和必须。我们同样可以指定主键为非聚簇索引。

         Create table A

         {

                   Id int indentity(1,1) primary key nonclustered,

                   Name varchar(64)

         }

         这样是完全可以的，只是多数情况下，主键字段创建索引有利于效率。

2：看到一个表，习惯就加个自增的ID列。

         A：有些表，例如Log表，初始配置表有ID列是无任何意义的。例如Log，通常我们关心Log时间，Log重要层级，Log内容信息，UseID等，对Log本身的ID是完全不需要关心的。我们完全可以砍掉。

3：在where条件中对一个表中列进行运算比较时候，通常认为创建了某种类型的索引可以提高效率。

         A：其实，当两表内进行运算比较时，即使创建复合索引都是无法提高性能的。例如 select count(*) from user where MaxHp – CurHp > 10，这样的语句，无论复合索引还是在maxHP, curHP上分别建立索引均是无效的，无法让性能得到提升。

4：编写where查询的存储过程中大量使用局部变量，而不使用参数变量。

         A：例如

         Create proc test

         {

                   @id int

}

As

         Select * from tableA where ColID > @id

效率就远比

Alter proc test

{

         @id int

}

As

         Declare @local int

         Set @local = @id

         Select * from where ColID > @local

高出许多，具体可参见查询计划中的估计查询行数。

5：查询条件中，类型不匹配。

         A：

         例如查询条件为 where AAA = @BBB，而AAA实际是varchar(64)类型，@BBB是bigint类型，这样做可能不会一定出什么错误，但很可能直接导致index seek无法使用，只得进行idnex scan.

5：建议

1：多查看SQLServer自带的查询计划，根据看到的查询开销和流程进行优化。

2：可以使用profiler跟踪查询，得到查询所需时间，再进行优化。

3：使用SQL Server performance monitor监视相应硬件负载。

1>     Memory: Page Faults / sec计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。

2>     %Processor Time计数器 如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。

3>     % User Time表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。

4>     Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。

5>     Cache Hit Ratio计数器该值越高越好。如果持续低于80%，应考虑增加内存.( 不过，需要注意，这个值在运行一段时候之后，将无法正确反映系统当前值。)

6：其他

1：PHP对数据库的性能测试数据

硬件：Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz         4G内存

1)：哈希型数据库

第一次写：500万条记录 309秒

第二次写：500万条记录 321秒

第三次写：500万条记录 309秒

第四次写：500万条记录 314秒

第五次写：1000万条记录 601秒

第六次写：1000万条记录 635秒

第七次写：2000万条记录 1289秒

第八次写：4000万条记录 php发生错误无返回信息 但数据都有写到数据库

500 万条记录里 循环1万次查找： 没找到记录时共约490~540毫秒 找到一条记录时共约740~770毫秒

1000万条记录里 循环1万次查找： 没找到记录时共约520~550毫秒 找到一条记录时共约740~770毫秒

1500万条记录里 循环1万次查找： 没找到记录时共约490~550毫秒 找到一条记录时共约630~700毫秒

2000万条记录里 循环1万次查找： 没找到记录时共约490~550毫秒 找到一条记录时共约650~750毫秒

3000万条记录里 循环1万次查找： 没找到记录时共约490~550毫秒 找到一条记录时共约650~750毫秒

4000万条记录里 循环1万次查找： 没找到记录时共约470~550毫秒 找到一条记录时共约650~750毫秒

6000万条记录里 循环1万次查找： 没找到记录时共约470~550毫秒 找到一条记录时共约500~600毫秒

1亿条记录里     循环1万次查找： 没找到记录时共约480~550毫秒 找到一条记录时共约650~750毫秒

官方的测试数据时100万条读0.6秒，写0.7秒。我的测试包含网络存取开销，另外我的服务器还运行其他程序速度比较不稳定。

2)：Table数据库  

        表结构

        name sex age memo1 memo2 memo3 memo4 memo5 memo6 memo7 memo8 memo9 memo10 memo11 memo12 memo13

对age字段做索引

        第一次写：10万条记录 20秒

第二次写：40万条记录 140秒

第三次写：20万条记录 293秒

        写数据，当记录数增大的时候，写入的数据变慢

        读：1000次 没找到：大概120毫秒 找到一条：大概190毫秒 10万条记录

        读：1000次 没找到：大概120毫秒 找到一条：大概190毫秒 50万条记录

        读：1000次 没找到：大概120毫秒 找到一条：大概190毫秒 70万条记录

        记录数的增加，对查找速度没影响。如果有返回信息则影响速度。返回的信息越多速度越慢。

2：SQLSERVER2005压力测试

         一亿两千七百万条数据，占用硬盘40G左右，索引占用6G左右，一个聚簇索引。

         此时1分钟约可插入10W条记录，简单查询select count(1) from Table 一次消耗1分多钟。