常用的SQL优化技巧

常见的字段类型选择

•字符类型建议采用varchar/nvarchar数据类型
•金额货币建议采用money数据类型
•科学计数建议采用numeric数据类型
•自增长标识建议采用bigint数据类型   (数据量一大,用int类型就装不下,那以后改造就麻烦了)
•时间类型建议采用为datetime数据类型
•禁止使用text、ntext、image老的数据类型
•禁止使用xml数据类型、varchar(max)、nvarchar(max)

约束与索引

每张表必须有主键

•每张表必须有主键,用于强制实体完整性
•单表只能有一个主键(不允许为空及重复数据)
•尽量使用单字段主键

不允许使用外键

•外键增加了表结构变更及数据迁移的复杂性
•外键对插入,更新的性能有影响,需要检查主外键约束
•数据完整性由程序控制

索引设计准则

•应该对 WHERE 子句中经常使用的列创建索引
•应该对经常用于连接表的列创建索引
•应该对 ORDER BY 子句中经常使用的列创建索引
•不应该对小型的表(仅使用几个页的表)创建索引,这是因为完全表扫描操作可能比使用索引执行的查询快
•单表索引数不超过6个
•不要给选择性低的字段建单列索引
•充分利用唯一约束
•索引包含的字段不超过5个(包括include列)

不要给选择性低的字段创建单列索引

•SQL SERVER对索引字段的选择性有要求,如果选择性太低SQL SERVER会放弃使用•
•不适合创建索引的字段:性别、0/1、TRUE/FALSE
•适合创建索引的字段:ORDERID、UID等

充分利用唯一索引

唯一索引给SQL Server提供了确保某一列绝对没有重复值的信息,当查询分析器通过唯一索引查找到一条记录则会立刻退出,不会继续查找索引

•表索引数不超过6个, 表索引数不超过6个(这个规则只是携程DBA经过试验之后制定的。。。)
•索引加快了查询速度,但是却会影响写入性能
•一个表的索引应该结合这个表相关的所有SQL综合创建,尽量合并
•组合索引的原则是,过滤性越好的字段越靠前
•索引过多不仅会增加编译时间,也会影响数据库选择最佳执行计划

SQL查询

•禁止在数据库做复杂运算
•禁止使用SELECT *
•禁止在索引列上使用函数或计算
•禁止使用游标
•禁止使用触发器
•禁止在查询里指定索引
•变量/参数/关联字段类型必须与字段类型一致
•参数化查询
•限制JOIN个数
•限制SQL语句长度及IN子句个数
•尽量避免大事务操作
•关闭影响的行计数信息返回
•除非必要SELECT语句都必须加上NOLOCK
•使用UNION ALL替换UNION
•查询大量数据使用分页或TOP
•递归查询层级限制
•NOT EXISTS替代NOT IN
•临时表与表变量
•使用本地变量选择中庸执行计划
•尽量避免使用OR运算符
•增加事务异常处理机制
•输出列使用二段式命名格式

禁止在数据库做复杂运算

•XML解析
•字符串相似性比较
•字符串搜索(Charindex)
•复杂运算在程序端完成

禁止使用SELECT *

•减少内存消耗和网络带宽
•给查询优化器有机会从索引读取所需要的列
•表结构变化时容易引起查询出错

•禁止在索引列上使用函数或计算

禁止在索引列上使用函数或计算

假设在字段Col1上建有一个索引,则下列场景将可以使用到索引:

[Col1]=3.14

[Col1]>100

[Col1] BETWEEN 0 AND 99

[Col1] LIKE ‘abc%’

[Col1] IN(2,3,5,7)

像上面这样的查询,将无法用到O_OrderProcess表上的PrintTime索引

LIKE查询的索引问题

•[Col1] like “abc%”  –index seek  这个就用到了索引查询
•[Col1] like “%abc%”  –index scan  而这个就并未用到索引查询
•[Col1] like “%abc”  –index scan 这个也并未用到索引查询
我想从上而三个例子中,大家应该明白,最好不要在LIKE条件前面用模糊匹配,否则就用不到索引查询。

禁止使用游标

•关系数据库适合集合操作,也就是对由WHERE子句和选择列确定的结果集作集合操作,游标是提供的一个非集合操作的途径。一般情况下,游标实现的功能往往相当于客户端的一个循环实现的功能。
•游标是把结果集放在服务器内存,并通过循环一条一条处理记录,对数据库资源(特别是内存和锁资源)的消耗是非常大的。
(再加上游标真心比较复杂,挺不好用的,尽量少用吧)

禁止使用触发器

•触发器对应用不透明(应用层面都不知道会什么时候触发触发器,发生也也不知道,感觉莫名其秒)

禁止在查询里指定索引

With(index=XXX)( 在查询里我们指定索引一般都用With(index=XXX) )

•随着数据的变化查询语句指定的索引性能可能并不最佳
•索引对应用应是透明的,如指定的索引被删除将会导致查询报错,不利于排障
•新建的索引无法被应用立即使用,必须通过发布代码才能生效

变量/参数/关联字段类型必须与字段类型一致(这是我之前不太关注的)

避免类型转换额外消耗的CPU,引起的大表scan尤为严重

看了上面这两个图,我想我不用解释说明,大家都应该已经清楚了吧。

如果数据库字段类型为VARCHAR,在应用里面最好类型指定为AnsiString并明确指定其长度

如果数据库字段类型为CHAR,在应用里面最好类型指定为AnsiStringFixedLength并明确指定其长度

如果数据库字段类型为NVARCHAR,在应用里面最好类型指定为String并明确指定其长度

参数化查询

以下方式可以对查询SQL进行参数化:

•sp_executesql
•Prepared Queries
•Stored procedures
用图来说明一下,哈哈。

限制JOIN个数

•单个SQL语句的表JOIN个数不能超过5个
•过多的JOIN个数会导致查询分析器走错执行计划
•过多JOIN在编译执行计划时消耗很大

限制IN子句中条件个数

•在 IN 子句中包括数量非常多的值(数以千计)可能会消耗资源并返回错误 8623 或 8632,要求IN子句中条件个数限制在100个以内

尽量避免大事务操作

•只在数据需要更新时开始事务,减少资源锁持有时间
•增加事务异常捕获预处理机制
•禁止使用数据库上的分布式事务
用图来说明一下
也就是说我们不应该在1000行数据都更新完成之后再commit tran,你想想你在更新这一千行数据的时候是不是独占资源导致其它事务无法处理。

关闭影响的行计数信息返回

在SQL语句中显示设置Set Nocount On,取消影响的行计数信息返回,减少网络流量

除非必要,尽量让所有的select语句都必须加上NOLOCK

指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据,其他事务设  置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作,但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错,向 用户显示从未提交过的数据,或者导致用户两次看到记录(或根本看不到记录)

使用UNION ALL替换UNION

UNION会对SQL结果集去重排序,增加CPU、内存等消耗

查询大量数据使用分页或TOP

合理限制记录返回数,避免IO、网络带宽出现瓶颈

递归查询层次限制

使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环

临时表与表变量

使用本地变量选择中庸执行计划

在存储过程或查询中,访问了一张数据分布很不平均的表格,这样往往会让存储过程或查询使用了次优甚至于较差的执行计划上,造成High CPU及大量IO Read等问题,使用本地变量防止走错执行计划。

采用本地变量的方式,SQL在编译的时候是不知道这个本地变量的值,这时候SQL会根据表格里数据的一般分布,“猜测”一个返回值。不管用户在调用存储过程或语句的时候代入的变量值是多少,生成的计划都是一样的。这样的计划一般会比较中庸一些,不一定是最优的计划,但一般也不会是最差的计划

如果查询中本地变量使用了不等式运算符,查询分析器使用了一个简单的 30% 的算式来预估
Estimated Rows =(Total Rows * 30)/100
如果查询中本地变量使用了等式运算符,则查询分析器使用:精确度 * 表记录总数来预估
Estimated Rows = Density * Total Rows

尽量避免使用OR运算符

对于OR运算符,通常会使用全表扫描,考虑分解成多个查询用UNION/UNION ALL来实现,这里要确认查询能走到索引并返回较少的结果集

增加事务异常处理机制

应用程序做好意外处理,及时做Rollback。
设置连接属性 “set xact_abort on”

输出列使用二段式命名格式

二段式命名格式:表名.字段名

有JOIN关系的TSQL,字段必须指明字段是属于哪个表的,否则未来表结构变更后,有可能发生Ambiguous column name的程序兼容错误

架构设计

•读写分离
•schema解耦
•数据生命周期

读写分离

•设计之初就考虑读写分离,哪怕读写同一个库,有利于快速扩容
•按照读特征把读分为实时读和可延迟读分别对应到写库和读库
•读写分离应该考虑在读不可用情况下自动切换到写端

Schema解耦

禁止跨库JOIN

数据生命周期

根据数据的使用频繁度,对大表定期分库归档

主库/归档库物理分离

日志类型的表应分区或分表

对于大的表格要进行分区,分区操作将表和索引分在多个分区,通过分区切换能够快速实现新旧分区替换,加快数据清理速度,大幅减少IO资源消耗

频繁写入的表,需要分区或分表

自增长与Latch Lock

闩锁是sql Server自己内部申请和控制,用户没有办法来干预,用来保证内存里面数据结构的一致性,锁级别是页级锁

统计信息, SQLServer自动更新和自动创建统计信息选项

统计信息是如何提高SQLServer查询性能的?统计直方图用作在查询执行计划中查询优化器的选择依据。如果一个查询谓词包含统计信息的列,那么查询优 化器不需要预测该查询中影响行数,因此,查询优化器有足够的信息去创建执行计划。SQLServer创建执行计划有一下几种不同的方式:

  • 统计信息会在每个新创建的索引中自动创建统计信息。
  • 如果数据库中AUTO_CREATE_STATISTICS被设置为ON,SQLServer将会自动对查询中用到的,且没有索引的列自动创建统计信息。

AUTO_CREATE_STATISTICS选项:

当把该选项设为ON时,查询优化器会对在谓词中使用的到列,如果这些列的统计信息不可用,则会单独对每列创建统计信息。这些统计信息对创建一个查询 计划非常必要。它们创建于那些现有统计对象中不存在直方图的列上,名字包括列名和对象ID的十六进制格 式:_WA_Sys_<column_name>_<XXXX>。这些统计信息用于查询优化器决定使用何种优化后的执行计划。

可以通过以下语句启用自动统计信息创建功能:

ALTER  DATABASE[你的库名]

SET AUTO_CREATE_STATISTICS ON

Auto Update Statistics选项:

统计信息会在查询编译或者执行缓存执行计划前被检查。当在以下情况下,统计信息会被认为过期:

1、  在一个空表中有数据的改动。

2、  当统计信息创建时,表的行数只有500或以下,且后来统计对象中的引导列的更改次数大于500.

3、  当表的统计信息收集时,超过了500行,且统计对象的引导列后来更改次数超过500+表总行数的20%时。

4、  在Tempdb中的表,少于6行且最少有6行被更改。 Continue reading “统计信息, SQLServer自动更新和自动创建统计信息选项”

SQL with(nolock)详解

大家在写查询时,为了性能,往往会在表后面加一个nolock,或者是with(nolock),其目的就是查询是不锁定表,从而达到提高查询速度的目的。

什么是并发访问:同一时间有多个用户访问同一资源,并发用户中如果有用户对资源做了修改,此时就会对其它用户产生某些不利的影响,例如:

1:脏读

一个用户对一个资源做了修改,此时另外一个用户正好读取了这条被修改的记录,然后,第一个用户放弃修改,数据回到修改之前,这两个不同的结果就是脏读。

2:不可重复读

一个用户的一个操作是一个事务,这个事务分两次读取同一条记录,如果第一次读取后,有另外用户修改了这个数据,然后第二次读取的数据正好是其它用户修改的数据,这样造成两次读取的记录不同,如果事务中锁定这条记录就可以避免。

3:幻读

指用户读取一批记录的情况,用户两次查询同一条件的一批记录,第一次查询后,有其它用户对这批数据做了修改,方法可能是修改,删除,新增,第二次查询时,会发现第一次查询的记录条目有的不在第二次查询结果中,或者是第二次查询的条目不在第一次查询的内容中。

为什么会在查询的表后面加nolock标识?为了避免并发访问产生的不利影响,SQL Server有两种并发访问的控制机制:锁、行版本控制,表后面加nolock是解决并发访问的方案之一。

1.锁

每个事务对所依赖的资源会请求不同类型的锁,它可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源。当事务不再依赖锁定的资源时,锁将被释放。

锁的类型:1:表类型:锁定整个表;2:行类型:锁定某个行;3:文件类型:锁定某个数据库文件;4:数据库类型:锁定整个数据库;5:页类型:锁定8K为单位的数据库页。

锁的分类还有一种分法,就是按用户和数据库对象来分:

1). 从数据库系统的角度来看:分为独占锁(即排它锁),共享锁和更新锁

1:共享 (S) :用于不更改或不更新数据的操作(只读操作),一般常见的例如select语句。

2:更新 (U) :用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

3:排它 (X) :用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

2). 从程序员的角度看:分为乐观锁和悲观锁。

1:乐观锁:完全依靠数据库来管理锁的工作。

2:悲观锁:程序员自己管理数据或对象上的锁处理。

一般程序员一看到什么锁之类,觉的特别复杂,对专业的DBA当然是入门级知识了。可喜的是程序员不用去设置,控制这些锁,SQLServer通过设 置事务的隔离级别自动管理锁的设置和控制。锁管理器通过查询分析器分析待执行的sql语句,来判断语句将会访问哪些资源,进行什么操作,然后结合设定的隔 离级别自动分配管理需要用到的锁。

2.行版本控制

当启用了基于行版本控制的隔离级别时,数据库引擎 将维护修改的每一行的版本。应用程序可以指定事务使用行版本查看事务或查询开始时存在的数据,而不是使用锁保护所有读取。通过使用行版本控制,读取操作阻 止其他事务的可能性将大大降低。也就是相当于针对所有的表在查询时都会加上nolock,同样会产生脏读的现象,但差别在于在一个统一管理的地方。说到了基于行版本控制的隔离级别,这里有必要说下隔离级别的概念。

隔离级别的用处:控制锁的应用,即什么场景应用什么样的锁机制。

最终目的:解决并发处理带来的种种问题。

隔离级别的分类:

1:未提交读,隔离事务的最低级别,只能保证不读取物理上损坏的数据;

2:已提交读,数据库引擎的默认级;

3:可重复读;

4:可序列化;隔离事务的最高级别,事务之间完全隔离。

小结:NOLOCK 语句执行时不发出共享锁,允许脏读 ,等于 READ UNCOMMITTED事务隔离级别 。nolock确实在查询时能提高速度,但它并不是没有缺点的,起码它会引起脏读。

nolock的使用场景(个人观点):

1:数据量特别大的表,牺牲数据安全性来提升性能是可以考虑的;

2:允许出现脏读现象的业务逻辑,反之一些数据完整性要求比较严格的场景就不合适了,像金融方面等。

3:数据不经常修改的表,这样会省于锁定表的时间来大大加快查询速度。

最后说下nolock和with(nolock)的几个小区别:

1:SQL05中的同义词,只支持with(nolock);

2:with(nolock)的写法非常容易再指定索引。

跨服务器查询语句时 不能用with (nolock) 只能用nolock,同一个服务器查询时 则with (nolock)和nolock都可以用

SQL Server中的Transaction、error check、Lock、Isolation level、save point

Transaction及错误检查

SQL Server 中最重要的知识点莫过于事务,比如很多OLTP(联机事务处理)应用程序。什么是事务?事务就是一系列SQL语句的集合。事务包括隐性事务(例如Insert,Update等语句)和显性事务(用Begin Tran语句显式指明的事务)。事务中通常需要进行错误检查,用@@error来进行检查,比如:

Begin Tran
Update A set id =5 where id=1
If @@error<>0
rollback Tran
Update A set id =5 where id=2
If @@error<>0
rollback Tran
Commit Tran

Lock (锁)

按锁的粒度分,锁可以分成如下几类:

Key Lock(键锁)—>Row Lock(行级锁)—>Page Lock(页级锁)—>Extent Lock(扩展盘曲锁)—>Table Lock(表锁)—>Database Lock(数据库锁)

按锁的模式分,锁可以分为如下几类:

holdlock(共享锁),xlock(排它锁),Updlock(更新锁),Schlock(架构锁),Intent lock(意向锁)等等。如果要查看锁的类型,使用系统存储过程sys_lock来查看。

Isolation level (隔离级别)

事务有4中隔离级别,分别为:
read uncommitted(未提交读) — 读未提交,可以读取到内存中已经修改但是没有保存到硬盘上的信息,即允许数据脏读。
read committed(提交读) — 读提交,只能读取到已经提交到硬盘的信息,如果信息在内存中修改了,但是还没有提交到硬盘,即没有commit tran,则另一个事务什么也读取不到,被另一事物阻塞在此。当修改数据的事务一旦commit tran,则读取数据的事务立即运行,修改后的数据被读取到。
repeatable read(重复读) — 当事务A设置隔离级别为repeatable read在对数据进行读取,此时,事务B来修改数据,由于repeatable read隔离级别对操作的实体(行或者表)设置了更新锁,所以此时事务B不能对数据进行更新,但是事务B可以insert新数据,因为repeatable read隔离级别对操作的实体(行或者表)没有设置排它锁,所以事务A可以读取到幻象。
serializable(串行读) — 串行化,即事务一个接一个地进行操作,包括对操作实体的update,insert,select等等。

实际上隔离级别和锁的关系是密不可分的,隔离级别的实现本质上是对锁来进行操作,由于我们在操作一个实体对象的时候不能准确地判断到底应该上什么具体的锁 ,所以鉴于此,SQL server数据库为我们开辟了一个简单的途径,即使用隔离级别。实体的隔离级别越高,说明实体上锁的数量越多,种类越复杂;实体的隔离级别越高,并行化的几率越低,串行化的几率越高。

Save Point(保存点)

保存点的出现,是为了在事务恢复时更加地迅速和容易,因为不用把所有的操作都恢复,而是只用恢复到保存点即可,关于如何恢复以及更具体的知识,会在事务的恢复博客中详述。举个简单的例子:

Begin Tran
Update A set id =4 where id=1
Save tran t1
Update A set id =3 where id=2
If @@error<>0
rollback t1
Update A set id=5 where id =3
Commit Tran

T-SQL中的格式转换,CAST和CONVERT

在 SQL Server 2012 中将表达式由一种数据类型转换为另一种数据类型。

语法

CAST ( expression AS data_type [ ( length ) ] )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

参数

expression

任何有效的表达式。

data_type

目标数据类型。这包括 xml、bigint 和 sql_variant。不能使用别名数据类型。

length

指定目标数据类型长度的可选整数。默认值为 30。

style

指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL,则返回 NULL。该范围是由 data_type 确定的。有关详细信息,请参阅“备注”部分。

返回类型

返回转换为 data_type 的 expression。

备注

Date 和 Time 样式

如果 expression 为 date 或 time 数据类型,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式的日期格式。

不带世纪数位 (yy) (1) 带世纪数位 (yyyy) 标准 输入/输出 (3)
01001、2 默认 mon dd yyyy hh:miAM(或 PM)
1 101 美国 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英国/法国 dd/mm/yyyy
4 104 德语 dd.mm.yy
5 105 意大利语 dd-mm-yy
6 106 (1) dd mon yy
7 107 (1) Mon dd, yy
8 108 hh:mi:ss
91091、2 默认格式 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM)
10 110 美国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd

yyyymmdd

131131、2 欧洲默认格式 + 毫秒 dd mon yyyy hh:mi:ss:mmm(24h)
14 114 hh:mi:ss:mmm(24h)
20120 (2) ODBC 规范 yyyy-mm-dd hh:mi:ss(24h)
21121 (2) ODBC 规范(带毫秒) yyyy-mm-dd hh:mi:ss.mmm(24h)
126 (4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm(无空格)
127(6, 7) 带时区 Z 的 ISO8601。 yyyy-mm-ddThh:mi:ss.mmmZ

(无空格)

130 (1,2) 回历 (5) dd mon yyyy hh:mi:ss:mmmAM
131 (2) 回历 (5) dd/mm/yy hh:mi:ss:mmmAM

1 这些样式值返回不确定的结果。包括所有 (yy)(不带世纪数位)样式和一部分 (yyyy)(带世纪数位)样式。

2 默认值(style010091091311320120 以及 21121)始终返回世纪数位 (yyyy)。

3 转换为 datetime 时输入;转换为字符数据时输出。

4 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换,其输出格式如上一个表所述。

5 回历是有多种变体的日历系统。SQL Server 使用科威特算法。

重要提示
默认情况下,SQL Server 基于截止年份 2049 年来解释两位数的年份。换言之,就是将两位数的年份 49 解释为 2049,将两位数的年份 50 解释为 1950。许多客户端应用程序(如基于自动化对象的应用程序)都使用截止年份 2030 年。SQL Server 提供了 two digit year cutoff 配置选项以更改 SQL Server 使用的截止年份,从而进行一致的日期处理。建议您指定四位数年份。

6 仅支持从字符数据转换为 datetime 或 smalldatetime。仅表示日期或时间成分的字符数据转换为 datetime 或 smalldatetime 数据类型时,未指定的时间成分设置为 00:00:00.000,未指定的日期成分设置为 1900-01-01。

7 使用可选的时间区域指示符 (Z) 更便于将具有时区信息的 XML datetime 值映射到没有时区的 SQL Server datetime 值。Z 是时区 UTC-0 的指示符。其他时区则以 + 或 – 方向的 HH:MM 偏移量来指示。例如:2006-12-12T23:45:12-08:00。

从 smalldatetime 转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。使用相应的 char 或 varchar 数据类型长度从 datetime 或 smalldatetime 值转换时,可截断不需要的日期部分。

从样式包含时间的字符数据转换为 datetimeoffset 时,将在结果末尾追加时区偏移量。

float 和 real 样式

如果 expression 为 float 或 real,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

输出
0(默认值) 最多包含 6 位。根据需要使用科学记数法。
1 始终为 8 位值。始终使用科学记数法。
2 始终为 16 位值。始终使用科学记数法。
126, 128, 129 为了保持向后兼容而包括在内,在以后的版本中可能不推荐使用。

money 和 smallmoney 样式

如果 expression 为 money 或 smallmoney,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

输出
0(默认值) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。
1 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。
2 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。
126 转换为 char(n) 或 varchar(n) 时,等同于样式 2

xml 样式

如果 expression 为 xml,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

输出
0(默认值) 使用默认的分析行为,即放弃无用的空格,且不允许使用内部 DTD 子集。

注意
转换为 xml 数据类型时,SQL Server 的无用空格处理方式不同于 XML 1.0。有关详细信息,请参阅创建 XML 数据的实例。
1 保留无用空格。此样式设置将默认的 xml:space 处理方式设置为与指定了 xml:space=”preserve” 的行为相同。
2 启用有限的内部 DTD 子集处理。

如果启用,则服务器可使用内部 DTD 子集提供的以下信息来执行非验证分析操作。

  • 应用属性的默认值。
  • 解析并扩展内部实体引用。
  • 检查 DTD 内容模型以实现语法的正确性。

分析器将忽略外部 DTD 子集。此外,不评估 XML 声明来查看 standalone 属性是设置为 yes 还是 no,而是将 XML 实例当成一个独立文档进行分析。

3 保留无用空格,并启用有限的内部 DTD 子集处理。

二进制样式

如果 expression 为 binary(n)、varbinary(n)、char(n) 或 varchar(n),则 style 可以为下表中显示的值之一。表中没有列出的样式值将返回错误。

输出
0(默认值) 将 ASCII 字符转换为二进制字节,或者将二进制字节转换为 ASCII 字符。每个字符或字节按照 1:1 进行转换。

如果 data_type 为二进制类型,则会在结果左侧添加字符 0x。

1, 2 如果 data_type 为二进制类型,则表达式必须为字符表达式。 expression 必须由数量为偶数的十六进制数字(0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、a、b、c、d、e、f)组成。如果将 style 设置为 1,字符 0x 必须为表达式中的前两个字符。如果表达式中包含的字符数为奇数或者包含任何无效的字符,则会引发错误。

如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果。

如果固定长度 data_types 大于转换后的结果,则会在结果右侧添加零。

如果 data_type 为字符类型,则表达式必须为二进制表达式。每个二进制字符均转换为两个十六进制字符。如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果。

如果 data_type 为固定大小的字符类型,并且转换后的结果长度小于其 data_type 长度,则会在转换后的表达式右侧添加空格,以使十六进制数字的个数保持为偶数。

对于 style 1,将在转换后的结果左侧添加字符 0x。

SqlServer,MySql如何获得最新自增长字段的值

在向数据库中多个表同时插入修改数据的时候,我们1般需要使用事务来保证完整性,这段时间在维护1个银行项目,其中有较多的同时插入修改多张数据表的场景,作者使用的主键是nvarchar类型,由C# Guid.NewGuid()方法每次生成随机GUID进行填充,然后将该GUID作为主键与关链键(外键)同时插入多张数据表中。

根据我有限的数据库原理知识,这样的设计可以保证记录的唯一性,毕竟GUID重复的机率比宇宙灭亡还难见到,但这样设计会降低查询速率,因为我们知道自增长的主键,在设计的时候使用了数据结构中的顺序存储结构,即我们只需要知道0记录的地址A,第20条主键记录的位置就在A+(主键数据类型长度*20)上,查询效率可谓是数据库索引中最快的,而nvarchar主键是很慢的,不过主键应该都是簇索引的吧。

那么我们在同时向多张数据表中插入数据,并且需要维护外键的时候,该如何获得之前插入的最后1个自增长数据值呢?总不能”select top 1 * from tablename order by pkname desc”吧?其实不必这么复杂,数据库设计者们给我们提供了更简单的方法:

SQL SERVER中的三种获得自增长ID的方法

先看例子:

      insert into users(Code,name) values('1111','aaaa')
      select  SCOPE_IDENTITY()
      select  @@IDENTITY
      select  IDENT_CURRENT('users')

三种方法的区别:

SCOPE_IDENTITY()
返回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值。一个作用域就是一个模块――存储过程、触发器、函数或批处理。因此,如果两个语句处于同一个存储过程、函数或批处理中,则它们位于相同的作用域中。

@@IDENTITY
返回最后插入的标识值,跨表跨会话。

IDENT_CURRENT(表名)
返回为任何会话和任何作用域中的指定表最后生成的标识值。这个函数需要一个以表名为值的变量,也就是说虽然不受会话和作用域的限制,却会受到表的限制。

体会:加上事物处理,两个函数一个变量没有本质区别。不加事物处理两个函数一个变量受到其他会话、作用域的影响不一样。

MySQL中获得自增长的方法

在MySQL中,使用auto_increment类型的id字段作为表的主键。通常的做法,是通过“select max(id) from tablename”的做法,但是显然这种做法需要考虑并发的情况,需要在事务中对主表加以“X锁“,待获得max(id)的值以后,再解锁。这种做法需要的步骤比较多,有些麻烦,而且并发性也不好。有没有更简单的做法呢?答案之一是通过select LAST_INSERT_ID()这个操作。乍一看,它和select max(id)很象,但实际上它是线程安全的。也就是说它是具体于数据库连接的。下面通过实验说明:

  1. 在连接1中向A表插入一条记录,A表包含一个auto_increment类型的字段。
  2. 在连接2中向A表再插入一条记录。
  3. 结果:在连接1中执行select LAST_INSERT_ID()得到的结果和连接2中执行select LAST_INSERT_ID()的结果是不同的;而在两个连接中执行select max(id)的结果是相同的。

其实在MSSQL中SCOPE_IDENTITY()和IDENT_CURRENT()的区别和这里是类似的。使用SCOPE_IDENTITY()可以获得插入某个IDENTITY字段的当前会话的值,而使用IDENT_CURRENT()会获得在某个IDENTITY字段上插入的最大值,而不区分不同 的会话。

注意:使用select last_insert_id()时要注意,当一次插入多条记录时,只是获得第一次插入的id值。

在.Net中检查Sql注入

SQL 注入是一种攻击方式,在这种攻击方式中,恶意代码被插入到字符串中,然后将该字符串传递到 SQL Server 的实例以进行分析和执行。任何构成 SQL 语句的过程都应进行注入漏洞检查,因为 SQL Server 将执行其接收到的所有语法有效的查询。一个有经验的、坚定的攻击者甚至可以操作参数化数据。

SQL 注入的主要形式包括直接将代码插入到与 SQL 命令串联在一起并使其得以执行的用户输入变量。一种间接的攻击会将恶意代码注入要在表中存储或作为元数据存储的字符串。在存储的字符串随后串连到一个动态 SQL 命令中时,将执行该恶意代码。

注入过程的工作方式是提前终止文本字符串,然后追加一个新的命令。由于插入的命令可能在执行前追加其他字符串,因此攻击者将用注释标记“–”来终止注入的字符串。执行时,此后的文本将被忽略。

Continue reading “在.Net中检查Sql注入”

SQL注入

所谓SQL注入,就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令,比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的,这类表单特别容易受到SQL注入式攻击.

目录

  • sql注入
  • 注入大致方法
  • 常用sql注入语句

sql注入

什么时候最易受到sql注入攻击

当应用程序使用输入内容来构造动态sql语句以访问数据库时,会发生sql注入攻击。如果代码使用存储过程,而这些存储过程作为包含未筛选的用户输入的字符串来传递,也会发生sql注入。sql注入可能导致攻击者使用应用程序登陆在数据库中执行命令。相关的SQL注入可以通过测试工具pangolin进行。如果应用程序使用特权过高的帐户连接到数据库,这种问题会变得很严重。在某些表单中,用户输入的内容直接用来构造动态sql命令,或者作为存储过程的输入参数,这些表单特别容易受到sql注入的攻击。而许多网站程序在编写时,没有对用户输入的合法性进行判断或者程序中本身的变量处理不当,使应用程序存在安全隐患。这样,用户就可以提交一段数据库查询的代码,根据程序返回的结果,获得一些敏感的信息或者控制整个服务器,于是sql注入就发生了。

Continue reading “SQL注入”