SqlServer 基础和优化

1、SqlServer字段类型

SqlServer字段类型详解

2、索引

2.1、什么是索引？

索引是与表或视图关联的独立的、物理的数据库结构，可以加快从表或视图中检检索行的速度。
索引包含由表或视图中的一列或多列生成的键。
这些键存储在一个结构（B树）中，是SQL Server 可以快速有效地查找与键值关联的行。

数据库中的索引与书籍的目录非常相似，在数据库中索引使数据库程序无需对整个表进行扫描，
就可以找到所需数据，数据库索引是一列或多列生成的键。

2.2、索引的作用

索引的作用就是为了提高数据库从表或视图中查询数据的速度，改善数据库性能。索引页需要空间来存放这些键，
这些存放索引的空间在数据库中称为索引页。

索引页：数据库中存储索引的数据页，存放键值以及指向数据行位置的指针。

2.3、索引分类：索引可以分为三类：唯一索引、聚集索引、非聚集索引

1、唯一索引：不允许有两行相同的索引值，所以唯一所以一般在主键或创建了唯一约束的列上创建。
当在列上创建了唯一约束后，将自动在此列上创建一个唯一索引。

2、聚集索引：聚集索引根据数据行的键值在表或视图中排序和存储这些数据行，即表中行的物理顺序与索引顺序相同。
每个表只能有一个聚集索引，因为数据行本身只能按照一个顺序排序。
如果表具有聚集索引，则改变成为聚集表，表中的数据行按索引顺序进行排序；
如果表中没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

3、非聚集索引：非聚集索引具有独立于数据行的结构。
非聚集索引宝航非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。
从非聚集索引中的索引行指向数据行的指针称为行定位器，行定位器的结构取决于数据页是存储在堆中还是数据表中。
对于堆，行定位器是指向行的指针；
对于聚集表，行定位器是聚集索引键。每个表可以有多个非聚集索引。

索引可以减少为返回查询结果集而必须读取的数据量，还可以强制表中的行具有唯一性，从而确保表数据的完整性。

3、语句 优化

Sql 语句执行顺序

1、 首先要搞明白什么叫执行计划？

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，
比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，
如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用 “全表扫描”方式。

可见，执行计划并不是固定的，它是“个性化的”。产生一个正确的“执行计划”有两点很重要：

(1)、SQL语句是否清晰地告诉查询优化器它想干什么？
(2)、查询优化器得到的数据库统计信息是否是最新的、正确的？

2、 统一SQL语句的写法

对于以下两句SQL语句，你可能认为是相同的，但数据库查询优化器认为是不同的。 

select*from dual 
select*From dual 


3、使用视图加速查询

把表的一个子集进行排序并创建视图，有时能加速查询。
它有助于避免多重排序 操作，而且在其他方面还能简化优化器的工作。


4、必要时强制查询优化器使用某个索引，如在 where 子句中使用参数，也会导致全表扫描。
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。
然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
如下面语句将进行全表扫描：

select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num 



5、使用“临时表”暂存中间结果

简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，
将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，
也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

6、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，
避免造成大量 log ，以提高速度；
如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。 

7、尽量不要用SELECT INTO语句，SELECT INOT 语句会导致表锁定，阻止其他用户访问该表。 

8、避免频繁创建和删除临时表，以减少系统表资源的消耗。 

9、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。



10、OLTP系统SQL语句必须采用绑定变量 

select*from orderheader where changetime >'2010-10-20 00:00:01'
select*from orderheader where changetime >'2010-09-22 00:00:01'

以上两句语句，查询优化器认为是不同的SQL语句，需要解析两次。如果采用绑定变量
select*from orderheader where changetime >@chgtime

@chgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，
这可以大大降低数据库解析SQL语句的负担。一次解析，多次重用，是提高数据库效率的原则。

11、绑定变量窥测
	
事物都存在两面性，绑定变量对大多数OLTP处理是适用的，但是也有例外。比如在where条件中的字段是“倾斜字段”的时候。

“倾斜字段”指该列中的绝大多数的值都是相同的，比如一张人口调查表，其中“民族”这列，90%以上都是汉族。
那么如果一个SQL语句要查询30岁的汉族人口有多少，那“民族”这列必然要被放在where条件中。
这个时候如果采用绑定变量@nation会存在很大问题。

试想如果@nation传入的第一个值是“汉族”，那整个执行计划必然会选择表扫描。
然后，第二个值传入的是“布依族”，按理说“布依族”占的比例可能只有万分之一，应该采用索引查找。
但是，由于重用了第一次解析的“汉族”的那个执行计划，那么第二次也将采用表扫描方式。
这个问题就是的“绑定变量窥测”，建议对于“倾斜字段”不要采用绑定变量。



12、在需要当条件的列上加索引，避免全表扫描，首先应考虑在 where 及 order by。

13、where条件过滤数据多的放在前面，减少产生的临时表的数据量。



14、使用like进行模糊查询时应注意。有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

 关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%。

15、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

16、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

17、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

18、in 和 not in 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：

select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3 

19、能用 DISTINCT 的就不用 GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改为：
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

20、能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源
	
21、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

SELECT * FROM T1 WHERE F1/2=100
应改为:
SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
应改为:
SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
应改为:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。 

22、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where 
num=a.num)


23、select * 查询	

尽量不要使用：	
select * from tablename	
取而代之的则是：	
select columnname1,columnname2 from tablename
	
24、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

25、尽可能的使用 varchar/nvarchar 代替 char/nchar，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

26、不要更新聚集索引数据列，索引数据列的顺序就是表记录的物理存储顺序，
一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。	

27、在使用索引字段作为条件时，如果该索引是复合索引，
那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，
否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 

4、SQL Server数据库读写分离

SQL Server数据库读写分离提高并发性

5、SQL Server事务的隔离级别和锁

SQL Server事务的隔离级别和锁