Sql Sqserver 相关知识总结

Sql Sqserver 相关知识总结

---

---

前言

本文主要从如何优化sql语句，检查sqlserver状态，SQL server各种索引的对比，这几个方面来开展学习和探讨，总结了我近几年的经验。希望对大家有所帮助。

---

优化语句

查询（select）

避免使用select * from xxx

反例：

select * from xxx

正例：

select id,UserName,Pwd from xxx

原因：使用具体字段可以节省资源、减少网络开销，且能避免回表查询

条件过滤（Where）

1. 避免在 WHERE 子句中使用 OR

反例：

SELECT * FROM user WHERE userid=1 OR age=18;

正例：

-- 使用 UNION ALL
SELECT * FROM user WHERE userid=1
UNION ALL
SELECT * FROM user WHERE age=18;

原因：OR 会导致索引失效并引发全表扫描。

2. 不要在 where 子句中的“=”左边进行函数

反例：

SELECT * FROM user WHERE age - 1 = 10;

正例：

SELECT * FROM user WHERE age = 11;

原因： 系统将可能无法正确使用索引

3. where条件避免 != 或 <> 操作符

反例：

SELECT age, name FROM user WHERE age <> 18;

正例：

select age,name  from user where age <18;
select age,name  from user where age >18;

分组处理（GROUP BY）

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

SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING city = '北京';

正例：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE city = '北京'
GROUP BY user_id;

模糊查询（like）

使用like进行查询的话,简单的使用index是不行的,但是全文索引,耗空间. like ‘a%’ 使用索引 like ‘%a’ 不使用索引用 like ‘%a%’ 查询时,查询耗时和字段值总长度成正比,所以不能用CHAR类型,而是VARCHAR.对于字段的值很长的建全文索引

反例：

SELECT userId, name FROM user WHERE userId LIKE '%123';

正例：

SELECT userId, name FROM user WHERE userId LIKE '123%';

包含（in）

用EXISTS替代IN、用NOT EXISTS替代NOT IN;在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

反例：

select EMPNO, id from user WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = "MELB")

正例：

select EMPNO, id from EMP (基础表) WHERE EMPNO > 0 
AND EXISTS (SELECT "X" FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = "MELB")

合集（UNION）

当数据中没有重复数据的时候使用 UNION ALL 替换 UNION
反例：

SELECT * FROM user WHERE userid=1
UNION
SELECT * FROM user WHERE age=10;

正例：

SELECT * FROM user WHERE userid=1
UNION ALL
SELECT * FROM user WHERE age=10;

分页（LIMIT）

避免深分页，使用“标签记录法”或“延迟关联法”提升性能。我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下，也就是出现深分页问题。
反例：

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;

  select * from 表 where 条件过滤 order by 索引字段  offset ((页码-1)*10) rows fetch next 每页查询数量 rows only; 

我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
标签记录法就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。
假设上一次记录到100000，则SQL可以修改为：

select  id,name,balance FROM account where id > 100000 limit 10;

这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。延迟关联法延迟关联法，就是把条件转移到主键索引树，然后减少回表。

select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;

优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。

关联查询（JOIN）

内联接(INNER JOIN)

返回两个表中有匹配的记录。

SELECT a.column1, b.column2
FROM tableA a
INNER JOIN tableB b ON a.common_column = b.common_column;SELECT a.column1, b.column2 from  tableB b,tableA a WHERE a.common_column = b.common_column;

左联接(LEFT JOIN)

返回左表的所有记录，即使右表中没有匹配。使用左联接时左表数据结果尽量小，这样性能才不会太差。

SELECT a.column1, b.column2
FROM tableA a
LEFT JOIN tableB b ON a.common_column = b.common_column;

右联接（RIGHT JOIN）

返回右表的所有记录，即使左表中没有匹配。同理用右联接时，也应该是右边的表数据量较少才好。

SELECT a.column1, b.column2
FROM tableA a
RIGHT JOIN tableB b ON a.common_column = b.common_column;

全联接（FULL JOIN）

返回两表中任意一个表的所有记录。

SELECT a.column1, b.column2
FROM tableA a
FULL JOIN tableB b ON a.common_column = b.common_column;

推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。
反例：

select * from tab1 t1 left join tab2 t2  on t1.size = t2.size where t1.id>2;

正例：

select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;

索引设置

索引的优点与缺点

优点：

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

聚集索引和非聚集索引

聚集索引

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，与非聚集索引相比，聚集索引有着更快的检索速度。

举个栗子
比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

非聚集索引

该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致

举个栗子
比如，您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

两种索引如何选择

动作描述	使用聚集索引	使用非聚集索引
列经常被分组和排序	√	√
用来进行范围判断	√	×
一个或者极少不同值	×	×
小数目的不同值	√	×
大数据不同值	×	√
频繁更新的列	×	√
外键列	√	√
主键列	√	√

创建方法

聚集索引

CREATE INDEX 索引名称 ON 表名 (字段名)CREATE CLUSTERED INDEX [索引名称] ON [dbo].[表名称]
([字段名称] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

非聚集索引

USE [lswx]
GOCREATE NONCLUSTERED INDEX 索引名称 ON 表名称
(
//（数据量大值不相同且常用的列比如订单创建时间或者订单状态）表列1 ASC,表列2 DESC,表列3 ASC,表列4 ASC,表列5 ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

删除索引

drop index 索引名称 on 表名;

建议：如果你的数据库是事务型的平均每个表上不能超过5个索引 如果你的数据库是数据仓库型平均每个表可以创建10个索引都没问题

Sqlserver重建索引不锁表

重建索引时将ONLINE选项设置为ON这样可以保证重建索引时表仍然可以正常使用

触发器

Insert触发器

create trigger stu_insert
on student
for insert
as
update class set class_num=class_num+1
where class_id=(select class_id from inserted)

Delete触发器

create trigger stu_delete
on student
for delete
as
update class set class_num=class_num-1
where class_id=(select class_id from deleted)

Update触发器

create trigger stu_update
on student
instead of update
as
print '修改学生表'
drop trigger stu_update
--测试
update student set stu_id='0601004'where stu_name='鲁斌'

检查sqlserver状态

查看执行计划（SHOWPLAN_ALL）

显示查询计划是SQL Server将显示在执行查询的过程中连接表时所采取的每个步骤，以及是否选择及选择了哪个索引，从而帮助用户分析有哪些索引被系统采用。

通常在查询语句中设置SHOWPLAN_ALL选项，可以选择是否让SQL Server显示查询计划。
SET SHOWPLAN_ALL ON ︳OFF 或 SET SHOWPLAN_TEXT ON | OFF

例题：在book数据库中的User表上查询“学号=123“的学生，并分析哪些索引被系统采用。

  USE bookGOSET SHOWPLAN_ALL ONGOSELECT * FROM UserWHERE 学号= '123'GOSET SHOWPLAN_ALL OFFGO

查看磁盘使用率（STATISTICS IO）

数据检索语句所花费的磁盘活动量也是用户比较关心的性能之一。通过设置STATISTICS IO选项，可以是SQL Server显示磁盘IO信息。

设置是否显示磁盘IO统计的命令为：

SET STATISTICS IO ON| OFFUSE bookGOSET STATISTICS IO ONGOSELECT * FROM book1WHERE 编号 = 'YBZT246'GOSET STATISTICS IO OFFGO

查询时间耗时较长的语句

SELECT TOP 20total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法],dbname=db_name(qt.dbid),object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC

死锁处理

select    request_session_id spid,   OBJECT_NAME(resource_associated_entity_id) tableName    
from    sys.dm_tran_locks   
where    resource_type='OBJECT' --杀死死锁进程
kill 354 --显示死锁相关信息
exec sp_who2 354

sqlserver 自动杀死锁

GOSELECT * FROM master.dbo.sysdatabases WHERE name = 'posserver'GOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'Up_AutoKillBlocked')
DROP PROCEDURE Up_AutoKillBlockedGOCREATE PROCEDURE Up_AutoKillBlocked
WITH ENCRYPTION
AS
BEGINDECLARE @blockid smallintDECLARE @proc_name varchar(200)SET @blockid = 0WHILE 1 > 0BEGINWHILE EXISTS(SELECT blocked FROM master.dbo.sysprocesses WHERE  blocked > 0 AND dbid = 100)BEGINSELECT TOP 1 @blockid = blocked FROM master.dbo.sysprocesses WHERE  blocked > 0 AND dbid = 100IF ISNULL(@blockid,0) > 0 BEGINSET @proc_name = 'KILL ' + CONVERT(VARCHAR(10), @blockid)EXEC @proc_nameENDWAITFOR DELAY '00:00:01'ENDWAITFOR DELAY '00:00:05'ENDENDGOEXEC Up_AutoKillBlockedGO

---