2020-04-01

提高 SQL 速度小技巧

对查询进行优化，应尽量避免全表扫描，首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引。
尽量避免像客户端返回大数据量，查询语句中不要使用 SELECT * 返回所有的列，应按照实际需求选择需要的列。
使用表值函数。在查询的 SELECT 列表中使用标量函数时，该函数因结果集中的每一行而被调用，这会大幅降低查询的性能。然而可以将标量函数转换成表值函数，然后在查询中使用 CROSS APPLY，就可以大幅提升性能。
应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
SELECT ID FROM T WHERE NUM is NULL
可以在 NUM 上设置默认值0，确保表中 NUM 列没有 NULL 值，然后这样查询：
SELECT ID FROM T WHERE NUM = 0
应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 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
IN 和 NOT IN 要慎用，否则也会导致全表扫描，如：
SELECT ID FROM T WHERE NUM IN(1,2,3)
可以改成：
SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3
模糊查询需要注意，以下两种情况就不同：
全表查询
SELECT ID FROM T WHERE NAME LIKE '%abc%'
利用索引查询
SELECT ID FROM T WHERE NAME LIKE 'abc%'
避免在索引列上使用计算。WHERE 子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描，如：
ELECT ID FROM T WHERE ID * 2 > 1000
改为高效语句：
SELECT ID FROM T WHERE ID > 1000 / 2
使用 EXISTS 代替 IN 会提高查询效率，如：
SELECT NUM FROM T1 WHERE NUM IN(SELECT NUM FROM T2)
改为：
SELECT NUM FROM T1 WHEE EXISTS(SELECT 1 FROM T2 WHERE T1.NUM=T2.NUM)

如果只需查看数据是否存在，就不要计数行

SET @NUM = (SELECT COUNT(*) FROM T WHERE NUM = 10);
IF @NUM > 0
  BEGIN
    <Do something>
  END
改为：
IF EXISTS (SELECT 1 FROM T WHERE NUM = 10)
  BEGIN
    <Do something>
  END

不要使用触发器，触发器的问题：无论你希望触发器执行什么，都会在与原始操作同一个的事务中执行。如果你写一个触发器，以便更新 T1 表中的行时将数据插入到另外一个表 T2 中，会同时锁定这两个表，直到触发器执行完毕。如果你需要在更新后将数据插入到另一个表中，要将更新和插入放入到存储过程中，并在单独的事务中执行。
不要在同一个事务中对许多表执行复杂操作。这样会同时锁定每个表，知道所有操作完成。实际中应该讲每个表的操作分解成单独的事务，以便每个复杂操作只会锁定其中一个表。总之，应该把像这样的大事务分解成单独的小事务，以防阻塞。
尽量避免使用游标，因为游标的效率较差，如果游标的操作的数据超过一万行，那么就应该考虑改写。
避免频繁的创建和删除临时表，以减少系统表资源的消耗。
尽量用 UNION ALL 替换 UNION。当 SQL 语句需要 UNION 两个查询结果集合时，这两个结合会以 UNION ALL 的方式被合并，然后在输出最终结果前进行排序。如果用 UNION ALL 替代 UNION，这样排序就不必要了。

Glett的码字间

理想三旬

提高 SQL 速度小技巧