SQL 优化--杂项整理

电玩女神 2022-06-18 00:16 166阅读 0赞

第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。
    
    第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。
    
    第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。
    
    
    对查询进行优化，应尽量避免全表扫描。
    
    首先应考虑在 where 及 order by 涉及的列上建立索引。order by 按聚集索引列排序效率最高
    
    应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。可以设置默认值为0等其他方式避免出现 NULL
    
    应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
    
    应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
    
    应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
    
    应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。可以改用 UNION ALL的方式。即 使用 union 实现 or 操作
    不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
    致。
    
    在存储过程中,如果当索引结构发生变化时，在EXECUTE语句中(第一次)使用WITH RECOMPILE子句，以便存储过程可以利用最新创建的索引;
    
    对于连续的数值，能用 between 就不要用 in 
    模糊查询 like '%%' 前面带有 %号会导致全表扫描。
    
    很多时候用 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)
    
    用union在通常情况下比用or的效率要高的多.但是果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。
    
    字段提取要按照“需多少、提多少”的原则，避免“select *”
    
    用函数charindex()和前面加通配符%的LIKE执行效率一样
    
    exists 和 in 的执行效率是一样的(数据量10万级下，但是10万级以上 EXISTS 效率高)
    
    尽量少用NOT
    
    IN 的作用相当与OR 都要全表扫描
    
    修改 like 程序，去掉前置百分号。like语句却因为前置百分号而无法使用索引
    
    outer join很容易导致table scan或index scan。要尽量使用inner join避免scan整个表。
    
    不要有超过5个以上的表连接（JOIN）
    
    考虑使用临时表或表变量存放中间结果。
    
    少用子查询
    
    视图嵌套不要过深.
    
    有条件的使用union-all 替代 union：这样做排序就不必要了，效率会提高3到5倍
    
     
    
    尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
    
    在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先 create table，然后insert。
    
    如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
    
    在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
    
    当你使用count()时，SQL Server不知道你要做的是存在性检查，它会计算所有匹配的值，要么会执行全表扫描，要么会扫描最小的非聚集索引;
    
    当你使用EXISTS时，SQL Server知道你要执行存在性检查，当它发现第一个匹配的值时，就会返回TRUE，并停止查询。类似的应用还有使用IN或ANY代替count()。
    
    避免使用两个不同类型的列进行表的连接
    当连接两个不同类型的列时，其中一个列必须转换成另一个列的类型，级别低的会被转换成高级别的类型，转换操作会消耗一定的系统资源;
    如果你使用两个不同类型的列来连接表，其中一个列原本可以使用索引，但经过转换后，优化器就不会使用它的索引了。
    
    应尽量避免使用临时表，相反，可以使用表变量代替;
    表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。
    
    
    水平分区(也称 横切)和垂直分区(也称 竖切) 
     
    ----以下直接应用网络上的材料，因为易懂。
    在大表上应用分区
     水平分区：假设有一个表包括千万行记录，为了便于理解，假设表有一个自动增长的主键字段(如id)，我们可以将表拆分成10个独立的分区表，每个分区包含100万行记录，分区就要依据id字段的值实施，即第一个分区包含id值从1-1000000的记录，第二个分区包含1000001-2000000的记录，以此类推。这种以水平方向分割表的方式就叫做水平分区。
    
    　　垂直分区：假设有一个表的列数和行数都非常多，其中某些列被经常访问，其余的列不是经常访问。由于表非常大，所有检索操作都很慢，因此需要基于频繁访问的列进行分区，这样我们可以将这个大表拆分成多个小表，每个小表由大表的一部分列组成，这种垂直拆分表的方法就叫做垂直分区。
    
    1)添加文件组
    
    　　使用下面的命令创建一个文件组:
    
    　　ALTER DATABASE OrderDB ADD FILEGROUP [1999]
    
    　　ALTER DATABASE OrderDB ADD FILE (NAME = N'1999', FILENAME
    
    　　= N'C:\TestDB\20110701.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO
    
    　　FILEGROUP [1999]
    
    　2)创建分区函数  
    
    　　分区函数是定义分界点的一个对象，使用下面的命令创建分区函数：
    
    　　CREATE PARTITION FUNCTION FNOrderDateRange (DateTime) AS
    
    　　RANGE LEFT FOR VALUES ('19991231', '20001231', '20011231')
    
    　　上面的分区函数指定：
    
    　　DateTime<=1999/12/31的记录进入第一个分区;
    
    　　DateTime > 1999/12/31 且 <= 2000/12/31的记录进入第二个分区;
    
    　　DateTime > 2000/12/31 且 <= 2001/12/31的记录进入第三个分区;
    
    　　DateTime > 2001/12/31的记录进入第四个分区。
    3)创建分区方案
    
    　　通过分区方案在表/索引的分区和存储它们的文件组之间建立映射关系。创建分区方案的命令如下：
    
    　　CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION FNOrderDateRange
    
    　　TO ([1999], [2000], [2001], [2002])
    
    　　在上面的命令中，我们指定了：
    
    　　第一个分区应该进入1999文件组;
    
    　　第二个分区就进入2000文件组;
    
    　　第三个分区进入2001文件组;
    
    　　第四个分区进入2002文件组。
    
    4)在表上应用分区
    
    　　至此，我们定义了必要的分区原则，现在需要做的就是给表分区了。首先使用DROP INDEX命令删除表上现有的聚集索引，通常主键上有聚集索引，如果是删除主键上的索引，还可以通过DROP CONSTRAINT删除主键来间接删除主键上的索引，如下面的命令删除PK_Orders主键：
    
    　　ALTER TABLE Orders DROP CONSTRAINT PK_Orders;
    
    　　在分区方案上重新创建聚集索引，命令如下：
    
    　　CREATE UNIQUE CLUSTERED INDEX PK_Orders ON Orders(OrderDate) ON
    　　OrderDatePScheme (OrderDate)