| gongzizhenshao 发表于 2007-11-26 14:35 只看TA 1楼 |
|---|
|
|
[交流] 使用数据库的兄弟请进（SQLSERVER）,绝对有好处 SQL server的所有活动都会产生锁。锁定的单元越小，就越能越能提高并发处理能力，但是管理锁的开销越大。如何找到平衡点，使并发性和性能都可接受是SQL Server的难点。SQL Server有如下几种琐： 1、 共享锁 用于只读操作(SELECT)，锁定共享的资源。共享锁不会阻止其他用户读，但是阻止其他的用户写和修改。 2、 更新锁 更新锁是一种意图锁，当一个事物已经请求共享琐后并试图请求一个独占锁的时候发生更新琐。例如当两个事物在几行数据行上都使用了共享锁，并同时试图获取独占锁以执行更新操作时，就发生了死锁：都在等待对方释放共享锁而实现独占锁。更新锁的目的是只让一个事物获得更新锁，防止这种情况的发生。 3、 独占锁 一次只能有一个独占锁用在一个资源上，并且阻止其他所有的锁包括共享缩。写是独占锁，可以有效的防止’脏读’ 4、 意图缩 在使用共享锁和独占锁之前，使用意图锁。从表的层次上查看意图锁，以判断事物能否获得共享锁和独占锁，提高了系统的性能，不需从爷或者行上检查。 5、 计划锁 Sch-M,Sch-S。对数据库结构改变时用Sch-M，对查询进行编译时用Sch-S。这两种锁不会阻塞任何事物锁，包括独占锁。 读是共享锁，写是排他锁，先读后更新的操作是更新锁，更新锁成功并且改变了数据时更新锁升级到排他锁。锁的类型有： DB-----数据库，由于 dbid 列已包含数据库的数据库 ID，所以没有提供任何信息 FIL----文件 IDX----索引 PG-----页，数据或索引页。页码。页由 fileid:page 组合进行标识，其中，fileid 是 sysfiles 表中的 fileid，而 page 是该文件内的逻辑页码。 KEY----键,用于保护可串行事务中的键范围 TAB----表，包括所有数据和索引在内的整个表。由于 ObjId 列已包含表的对象 ID，所以没有提供任何信息 EXT----区域, 相邻的八个数据页或索引页构成的一组。正被锁定的扩展盘区中的第一个页码。页由 fileid:page 组合进行标识 RID----行，表内已锁定行的行标识符。行由 fileid:page:rid 组合进行标识，其中，rid 是页中的行标识符 锁的状态： Grant---能使用被授权的资源 Wait----能使用被其他任务阻塞的资源 Cnvrt---Convert,锁正在被转换 细分锁的模式： 0 Null 没有得到资源的访问权限 1 Sch-S (Schema stability) 对查询进行编译时。能防止加锁的对象被删除直到解锁 2 Sch-M (Schema Modification) 改变数据库结构时发生。能防止其他的事物访问加锁的对象 3 IS (Intent Shares) 意图共享锁。 4 SIU(Share Intent Update) 意图在维护资源的共享锁时，把更新锁放到锁层次结构的下层资源上 5 IS-S(Intent Share-shared) 复合键范围锁 6 IX(Intent Exclusive) 意图排他锁 7 SIX(Share Intent Exclusive) 8 S(Share) 共享锁 9 U(Update) 更新锁。防止死锁 10 Iin-Nul(Intent Insert-Null) 索引行层次的锁定，复合键范围锁 11 IS-X(Intent Share-Exclusive) 12 IU(Intent Update) 意图更新锁 13 IS-U(Intent Share Update) 串行更新扫描 14 X(Exclusive) 排他锁 15 BU 块操作使用的锁 所以有如下的结论。 1、一个连接在修改数据块时别的连接不能修改这个数据块，直到解锁。 并行访问是任何数据库解决方案都最为重视的问题了，为了解决并行访问方面的问题各类数据库系统提出了各种各样的方案。SQL Server采用了多线程机制，它当然能够一次处理多个请求。不过，在用户修改数据的情况下并行访问问题就变得复杂起来了。显然，数据库通常只允许唯一用户一次修改特定的数据。当某一用户开始修改某块数据时， SQL Server能很快地锁定数据，阻止其他用户对这块数据进行更新，直到修改该数据的第一位用户完成其操作并提交交易或者回滚。但是，当某一位用户正在修改某块数据时假设另一位用户又正想查询该数据的信息时会发生什么情况呢？ 2、通常情况下，一个连接在修改数据块时别的连接也不能查询这个数据块，直到解锁。反之亦然：读的时候不能写和修改。这个方案会降低系统的性能和效率，尽管现在是行级锁（7.0以前是锁页甚至是锁表），如果你一次修改多行数据，SQL Server则会把数据锁定范围提升到页级别乃至锁定整个数据表，从而不必针对每一记录跟踪和维护各自的数据锁，这样能加快修改的速度，消耗小的服务器资源，但是并发性就差了。。 3、一个连接写的时候，另一个连接可以写，但是不得读 4、多个连接可以同时读同一行。 所以锁发生在读、写的竞争上。 5、设置事物的级别 SET TRANSACTION ISOLATION LEVEL A、READ COMMITTED ：指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 SQL Server 的默认值。 B、READ UNCOMMITTED：执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。这是四个隔离级别中限制最小的级别。 C、REPEATABLE READ：锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。 D、SERIALIZABLE：在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。 注释 一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显式更改该选项为止。这是默认行为，除非在语句的 FROM 子句中在表级上指定优化选项。 SET TRANSACTION ISOLATION LEVEL 的设置是在执行或运行时设置，而不是在分析时设置。 36、当用SELECT INTO时，它会锁住系统表(syso b j e c ts，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一个连接中SELECT * from syso b j e c ts可以看到 SELECT INTO 会锁住系统表，Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。 37、一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快 41、一次更新多条记录比分多次更新每次一条快,就是说批处理好 42、少用临时表，尽量用结果集和Table类性的变量来代替它,Table 类型的变量比临时表好 43、在SQL2000下，计算字段是可以索引的，需要满足的条件如下： a、计算字段的表达是确定的 b、不能用在TEXT,Ntext，Image数据类型 c、必须配制如下选项 ANSI_NULLS = ON, ANSI_PADDINGS = ON, ……. 44、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。 以前由于SQL SERVER对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程 45、不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快 46、SELECT COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的！！！ 47、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率； 否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。 48、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就 会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现 49、通过SQL Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec计数器 如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。 Process: 1、% DPC Time 指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百分比。(DPC 正在运行的为比标准间隔优先权低的间隔)。 由于 DPC 是以特权模式执行的，DPC 时间的百分比为特权时间 百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部 分。这个总数显示了作为实例时间百分比的平均忙时。 2、%Processor Time计数器 如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。 3、% Privileged Time 指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。 特权时间的 % 包括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。 4、% User Time表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增 加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。 Physical Disk: Curretn Disk Queue Length计数器 该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 SQLServer:Cache Hit Ratio计数器 该值越高越好。如果持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。 40、分析select emp_name form employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。 41、查询的关联同写的顺序 select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = ‘号码’） select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B ,B = ‘号码’， A = ‘号码’） select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = ‘号码’， A = ‘号码’） 42、(1) IF 没有输入负责人代码 THEN code1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: SELECT 负责人名 FROM P2000 WHERE 负责人代码>=:code1 AND负责人代码 <=:code2 (2) IF 没有输入负责人代码 THEN SELECT 负责人名 FROM P2000 ELSE code= 负责人代码 SELECT 负责人代码 FROM P2000 WHERE 负责人代码=:code END IF 第一种方法只用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时,第二种方法显然比第一种方法执行效率高,因为它没有限制条件;在输入了负责人代码时,第二种方法仍然比第一种方法效率高,不仅是少了一个限制条件,还因相等运算是最快的查询运算。我们写程序不要怕麻烦 43、关于JOBCN现在查询分页的新方法（如下），用性能优化器分析性能的瓶颈，如果在I/O或者网 络的速度上，如下的方法优化切实有效，如果在CPU或者内存上，用现在的方法更好。请区分如下的方法，说明索引越小越好。 begin DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20)) insert into @local_variable (ReferenceID) select top 100000 ReferenceID from chineseresume order by ReferenceID select * from @local_variable where Fid > 40 and fid <= 60 end 和 begin DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20)) insert into @local_variable (ReferenceID) select top 100000 ReferenceID from chineseresume order by updatedate select * from @local_variable where Fid > 40 and fid <= 60 end 的不同 begin create table #temp (FID int identity(1,1),ReferenceID varchar(20)) insert into #temp (ReferenceID) select top 100000 ReferenceID from chineseresume order by updatedate select * from #temp where Fid > 40 and fid <= 60 drop table #temp end 由于SQL是面向结果而不是面向过程的查询语言，所以一般支持SQL语言的大型关系型数据库都使用一个基于查询成本的优化器，为即时查询提供一个最佳的执行策略。对于优化器，输入是一条查询语句，输出是一个执行策略。 一条SQL查询语句可以有多种执行策略，优化器将估计出全部执行方法中所需时间最少的所谓成本最低的那一种方法。所有优化都是基于用记所使用的查询语句中的where子句，优化器对where子句中的优化主要用搜索参数(Serach Argument)。 搜索参数的核心思想就是数据库使用表中字段的索引来查询数据，而不必直接查询记录中的数据。 带有 =、<、<=、>、>= 等操作符的条件语句可以直接使用索引，如下列是搜索参数： emp_id = "10001" 或 salary > 3000 或 a =1 and c = 7 而下列则不是搜索参数： salary = emp_salary 或 dep_id != 10 或 salary * 12 >= 3000 或 a=1 or c=7 应当尽可能提供一些冗余的搜索参数，使优化器有更多的选择余地。请看以下3种方法： 第一种方法： select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (department.dep_code='01') and (employee.dep_code='01'); 它的搜索分析结果如下： Estimate 2 I/O operations Scan department using primary key for rows where dep_code equals '01' Estimate getting here 1 times Scan employee sequentially Estimate getting here 5 times 第二种方法： select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (department.dep_code='01'); 它的搜索分析结果如下： Estimate 2 I/O operations Scan department using primary key for rows where dep_code equals '01' Estimate getting here 1 times Scan employee sequentially Estimate getting here 5 times 第一种方法与第二种运行效率相同，但第一种方法最好，因为它为优化器提供了更多的选择机会。 第三种方法： select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (employee.dep_code='01'); 这种方法最不好，因为它无法使用索引，也就是无法优化…… 使用SQL语句时应注意以下几点： 1、避免使用不兼容的数据类型。例如，Float和Integer，Char和Varchar，Binary和Long Binary不兼容的。数据类型的不兼容可能使优化器无法执行一些本可以进行的优化操作。例如： select emp_name form employee where salary > 3000; 在此语句中若salary是Float类型的，则优化器很难对其进行优化，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。 2、尽量不要使用表达式，因它在编绎时是无法得到的，所以SQL只能使用其平均密度来估计将要命中的记录数。 3、避免对搜索参数使用其他的数学操作符。如： select emp_name from employee where salary * 12 > 3000; 应改为： select emp_name from employee where salary > 250; 4、避免使用 != 或 <> 等这样的操作符，因为它会使系统无法使用索引，而只能直接搜索表中的数据。 |
| 0 |
|
|---|
