海南的房产网站建设,合肥市科技中心网站,专业网站设计制作优化排名,户外网站做Sql Sqserver 相关知识总结 文章目录 Sql Sqserver 相关知识总结前言优化语句查询#xff08;select#xff09;条件过滤#xff08;Where#xff09;分组处理#xff08;GROUP BY#xff09;模糊查询#xff08;like#xff09;包含#xff08;in#xff09;合集select条件过滤Where分组处理GROUP BY模糊查询like包含in合集UNION分页LIMIT关联查询JOIN内联接(INNER JOIN)左联接(LEFT JOIN)右联接RIGHT JOIN全联接FULL JOIN 索引设置索引的优点与缺点聚集索引和非聚集索引聚集索引非聚集索引 两种索引如何选择创建方法聚集索引非聚集索引删除索引 触发器Insert触发器Delete触发器Update触发器 检查sqlserver状态查看执行计划SHOWPLAN_ALL查看磁盘使用率STATISTICS IO查询时间耗时较长的语句死锁处理sqlserver 自动杀死锁 前言
本文主要从如何优化sql语句检查sqlserver状态SQL server各种索引的对比这几个方面来开展学习和探讨总结了我近几年的经验。希望对大家有所帮助。 优化语句
查询select
避免使用select * from xxx
反例
select * from xxx正例
select id,UserName,Pwd from xxx原因使用具体字段可以节省资源、减少网络开销且能避免回表查询
条件过滤Where
避免在 WHERE 子句中使用 OR
反例
SELECT * FROM user WHERE userid1 OR age18;正例
-- 使用 UNION ALL
SELECT * FROM user WHERE userid1
UNION ALL
SELECT * FROM user WHERE age18;原因OR 会导致索引失效并引发全表扫描。
不要在 where 子句中的“”左边进行函数
反例
SELECT * FROM user WHERE age - 1 10;正例
SELECT * FROM user WHERE age 11;原因 系统将可能无法正确使用索引
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 userid1
UNION
SELECT * FROM user WHERE age10;正例
SELECT * FROM user WHERE userid1
UNION ALL
SELECT * FROM user WHERE age10;分页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.id2;正例
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_numclass_num1
where class_id(select class_id from inserted)Delete触发器
create trigger stu_delete
on student
for delete
as
update class set class_numclass_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_id0601004where 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 学号 123GOSET SHOWPLAN_ALL OFFGO
查看磁盘使用率STATISTICS IO
数据检索语句所花费的磁盘活动量也是用户比较关心的性能之一。通过设置STATISTICS IO选项可以是SQL Server显示磁盘IO信息。
设置是否显示磁盘IO统计的命令为
SET STATISTICS IO ON| OFFUSE bookGOSET STATISTICS IO ONGOSELECT * FROM book1WHERE 编号 YBZT246GOSET 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/21, (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 [完整语法],dbnamedb_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_count1
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_typeOBJECT --杀死死锁进程
kill 354 --显示死锁相关信息
exec sp_who2 354
sqlserver 自动杀死锁
GOSELECT * FROM master.dbo.sysdatabases WHERE name posserverGOIF 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:01ENDWAITFOR DELAY 00:00:05ENDENDGOEXEC Up_AutoKillBlockedGO
