sql server高级知识梳理三:sql调优笔记

一.索引的失效，查看执行过程，标识应为index seek

1.常见的语法导致，如not in，！=，like '%%'，null，使用字段上加function等

2.参数导致索引失效，sql server无法通过统计信息优化sql，可用with（index）强制使用索引

3.复合索引使用顺序应从左到右依次使用，不对导致索引部分失效

4.索引尽量包含需要查询的列(太多也会导致树层数更深，也不可取)，否则需要回到聚集索引中再查询一遍

5.离散读导致索引失效，当复合索引查出来的数据需要回聚集索引中查询的时候，如果数据量太大(我试出来是总数据量的1.5%－2%)，数据库会直接对聚集索引进行全表扫描。

6.sql 使用变量导致索引失效 ，无法生成正确的执行计划，在sql最后加上变量提示 , 如果sql中变量使用@StartTime 和@EndTime -> option(OPTIMIZE FOR (@StartTime='2024-04-30 08:30:00.000',@EndTime='2024-05-01 08:30:00.000'))

7. 关于sql server中会自动优化执行顺序，及小表驱动大表，如果小表与大表是1对多，如果大表在此字段设定索引，则可能出现大表查询数据量过大的情况，此时需要限定大表范围或者优化执行顺序

二.cte的嵌套调用，嵌套调用cte会导致多次查询表，尽量一次使用一个cte

三.数据量少用@表变量代替临时表(100条以下，可适当变通)

四.数据量太大需要分区或者表拆分减少io开销，可根据页的大小(8kb)/索引字符大小估算出子结点个数，最好索引树保持在三层，即将之前算出来的数平方估算出表的适合大小。另外，一行数据尽量不超过8k。

五.读写分离，可有效提升查询速度

六.建表优化

1.字段尽量使用较小的类型，比如tinyint，smallint代替int，定长用char，varchar分配其需要的空间

2.单表中不要有大多的字段

七.关于锁表导致阻塞

数据库慢最重要的有两点，

第一个就是数据量大又没有索引

第二个就是锁表导致阻塞

锁表常常发生在update，insert，delete，根据隔离级别，表分行锁和表锁

表锁:隔离级别最高:不会死锁，但是阻塞效果100%

行锁:默认为行锁，锁住更改的行，阻塞效果低

减少阻塞的方法

1.使用行锁

2.减少update，delete，insert操作次数(减少次数可以减少表扫描，主键申请，索引维护时产生的开销)

3.使用with (nolock）可以大大的减少阻塞，但是会产生脏读，使用with nolock尽量不使用事务操作，但是不使用事务又会造成数据不一致。