上周,技术支持反映:客户的一个查询操作需要耗时6.1min左右,在跟进代码后,简化了数据库的查询后仍然收效甚微。后来,技术总监分析了sql后,给其中的一个表添加的一个非聚集索引(三个字段)后,同样的查询操作耗时只需要6s-7s。

SQL 语句主要可以划分为以下 3 个类别。

  Microsoft SQL Server(以下简称SQL
Server)作为一种中小型数据库管理系统,已经得到了广泛的应用,该系统更强调由系统来管理锁。在用户有SQL请求时,系统分析请求,自动在满足锁定条件和系统性能之间为数据库加上适当的锁,同时系统在运行期间常常自动进行优化处理,实行动态加锁。
  对于一般的用户而言,通过系统的自动锁定管理机制基本可以满足使用要求,但如果对数据安全、数据库完整性和一致性有特殊要求,就必须自己控制数据库的锁定和解锁,这就需要了解SQL
Server的锁机制,掌握数据库锁定方法。   锁的多粒度性以及锁升级
  数据库中的锁是指一种软件机制,用来指示某个用户(也即进程会话,下同)已经占用了某种资源,从而防止其他用户做出影响本用户的数据修改或导致数据库数据的非完整性和非一致性。这儿所谓资源,主要指用户可以操作的数据行、索引以及数据表等。根据资源的不同,锁有多粒度(multigranular)的概念,也就是指可以锁定的资源的层次。SQL
Server中能够锁定的资源粒度包括:数据库、表、区域、页面、键值(指带有索引的行数据)、行标识符(RID,即表中的单行数据)。
  采用多粒度锁的重要用途是用来支持并发操作和保证数据的完整性。SQL
Server根据用户的请求,做出分析后自动给数据库加上合适的锁。假设某用户只操作一个表中的部分行数据,系统可能会只添加几个行锁(RID)或页面锁,这样可以尽可能多地支持多用户的并发操作。但是,如果用户事务中频繁对某个表中的多条记录操作,将导致对该表的许多记录行都加上了行级锁,数据库系统中锁的数目会急剧增加,这样就加重了系统负荷,影响系统性能。因此,在数据库系统中,一般都支持锁升级(lock
escalation)。所谓锁升级是指调整锁的粒度,将多个低粒度的锁替换成少数的更高粒度的锁,以此来降低系统负荷。在SQL
Server中当一个事务中的锁较多,达到锁升级门限时,系统自动将行级锁和页面锁升级为表级锁。特别值得注意的是,在SQL
Server中,锁的升级门限以及锁升级是由系统自动来确定的,不需要用户设置。 

       原sql大概需要左联left join 十几个 ,left
join前后的两个表又是笛卡尔积。因此,只要其中有一个表的数据有很多,那么sql需要检索的数据集会无比巨大。根据where后面过滤的字段(至少两个字段)添加非聚集索引后,数据库会自动复制一份数据,然后sql查询时会去这份复制的数据检索,这样就大大地减少了数据库响应的时间。那么是否非聚集索引越多越好呢?通常来讲,一个表只能有一个聚集索引,但可以有多个非聚集索引。而数据库索引主要目的就是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间
。但这并不意味着索引(非聚集索引)越多越好。恰当的索引设计会显著提高性能,反之会得不偿失。当我们的技术支持给客户设计索引时,查询时间仍然没有如期提高。这就是非聚集索引设计不恰当的例子。因为那三个字段(前面提到)中包含一个字段:现场时间,而这个字段是会动态变化的,客户查询操作的一个过滤条件就是时间,所以并没有如期生效,因为sql查询时没有命中这个复制的数据集。所以当减少这个字段时,查询时间又回到6s-7s之间,命中率显著提高。

DDL(Data Definition
Languages)语句:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括
create、drop、alter等。

SQL Server锁类型

        此外,非聚集索引的增加会增加硬盘(或内存)的开销,世上没有一举两得事情,所以权衡利弊恰当地设计索引尤为重要。

 就是对数据库内部的对象进行创建、删除、修改的操作语言

1)    HOLDLOCK:
在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。
  

 

 

2)   
NOLOCK:不添加共享锁和排它锁,当这个选项生效后,可能读到未提交读的数据或“脏数据”,这个选项仅仅应用于SELECT语句。
  

DML(Data Manipulation Language)语句
:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性,常用的语句关键字主要包括
insert、delete、udpate 和select 等。

3)    PAGLOCK:指定添加页锁(否则通常可能添加表锁)。    

对数据库中表记录的操作

4)   
READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下,SQL
Server 2000 在此隔离级别上操作。

 

5)    READPAST:
跳过已经加锁的数据行,这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行,而不是阻塞直到其他事务释放锁,

DCL(Data Control
Language)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括
grant、revoke 等。

READPAST仅仅应用于READ
COMMITTED隔离性级别下事务操作中的SELECT语句操作。    

发表评论

电子邮件地址不会被公开。 必填项已用*标注