文章

SQL语句性能优化的建议

发表于
作者 deathwhispers
19 分钟阅读
SQL语句性能优化的建议

1. 避免全表扫描

对查询进行优化，应尽量避免全表扫描。首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引。

2. 避免 NULL 值判断

应尽量避免在 WHERE 子句中对字段进行 NULL 值判断。创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。

3. 避免使用 != 或 <>

应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，因为这可能导致引擎放弃使用索引而进行全表扫描。MySQL只有对以下操作符才使用索引：<、<=、=、>、>=、BETWEEN、IN，以及某些情况下的 LIKE。

4. 使用 UNION ALL 代替 OR

应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

优化前：

1

SELECT id FROM t WHERE num = 10 OR num = 20;

优化后：

1
2
3

SELECT id FROM t WHERE num = 10
UNION ALL
SELECT id FROM t WHERE num = 20;

5. 慎用 IN 和 NOT IN

IN 和 NOT IN 也要慎用，否则可能导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN。

1
2
3
4
5

-- 不推荐
SELECT id FROM t WHERE num IN (1, 2, 3);

-- 推荐
SELECT id FROM t WHERE num BETWEEN 1 AND 3;

6. LIKE 的索引使用

LIKE 查询中，以通配符 % 开头的模式（如 '%abc%'）不会使用索引，而会导致全表扫描。只有当模式不以通配符开头时（如 'abc%'），才会使用索引。

1
2
3
4
5
6

-- 不走索引
SELECT id FROM t WHERE name LIKE '%abc%';
SELECT id FROM t WHERE name LIKE '%abc';

-- 走索引
SELECT id FROM t WHERE name LIKE 'abc%';

若要提高效率，可以考虑使用全文检索。

7. 避免在 WHERE 子句中使用参数

如果在 WHERE 子句中使用参数，也可能导致全表扫描。

8. 避免在 WHERE 子句中对字段进行操作

应尽量避免在 WHERE 子句中对字段进行表达式或函数操作，这会导致引擎放弃使用索引。

优化前：

1
2
3

SELECT * FROM record WHERE SUBSTRING(card_no, 1, 4) = '5378';
SELECT * FROM record WHERE amount / 30 < 1000;
SELECT * FROM record WHERE CONVERT(CHAR(10), date, 112) = '19991201';

优化后：

1
2
3

SELECT * FROM record WHERE card_no LIKE '5378%';
SELECT * FROM record WHERE amount < 1000 * 30;
SELECT * FROM record WHERE date = '1999-12-01';

9. 使用 EXISTS 代替 IN

很多时候用 EXISTS 代替 IN 是一个好的选择，因为 EXISTS 只关心是否存在，而不关心具体的值。

优化前：

1

SELECT num FROM a WHERE num IN (SELECT num FROM b);

优化后：

1

SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num);

10. 索引并非越多越好

索引可以提高 SELECT 的效率，但会降低 INSERT 及 UPDATE 的效率，因为更新数据时可能需要重建索引。一个表的索引数最好不要超过6个。

11. 避免更新聚集索引

应尽可能避免更新聚集索引（Clustered Index）的数据列，因为这会导致整个表记录的物理存储顺序调整，耗费大量资源。

12. 优先使用数字型字段

若只含数值信息的字段，尽量不要设计为字符型。这会降低查询和连接的性能，并增加存储开销。

13. 使用 VARCHAR/NVARCHAR 代替 CHAR/NCHAR

变长字段存储空间小，可以节省存储空间。对于查询来说，在一个相对较小的字段内搜索效率也更高。

14. 避免使用 SELECT *

用具体的字段列表代替 *，不要返回用不到的任何字段，这可以减少网络开销和数据传输量。

15. 避免返回大量数据

尽量避免向客户端返回大数据量。若数据量过大，应考虑需求是否合理，或者采用分页等方式处理。

16. 使用表别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列上，以减少解析时间和避免列歧义。

17. 善用临时表

简化SQL的一个重要方法是采用临时表暂存中间结果。这可以避免多次扫描主表，减少锁竞争，提高并发性能。

18. 慎用 NOLOCK

对于一些查询，可以加上 NOLOCK 提示，允许在读数据时不对其加锁，从而提高并发性。但缺点是可能读到未提交的“脏数据”。

使用 NOLOCK 的三条原则：

  • 查询结果用于“增、删、改”的不能加 NOLOCK。
  • 查询的表属于频繁发生页分裂的，慎用 NOLOCK。
  • 能用临时表提高并发性能的，优先使用临时表。

19. 简化复杂查询

  • 不要有多于5个以上的表连接（JOIN），可以考虑使用临时表。
  • 少用子查询，视图嵌套不宜过深（一般不超过2层）。

20. 预先计算结果

将需要查询的结果预先计算好放在表中，查询时直接 SELECT。这在处理复杂报表或统计数据时非常有效。

21. OR 字句的优化

用 OR 的字句可以分解成多个查询，并通过 UNION ALL 连接。如果查询的列上有索引，UNION ALL 的效率通常更高。

22. IN 列表值的顺序

在 IN 后面的值列表中，将出现最频繁的值放在最前面，最少的放在最后面，以减少判断次数。

23. 使用存储过程

存储过程是预编译、优化过的SQL语句集合，执行效率高。对于反复执行的动态SQL，可以考虑使用临时存储过程。

24. 线程配置

当服务器内存充足时，可将线程数量配置为 最大连接数 + 5 以发挥最大效率。否则，应启用SQL Server的线程池来管理。

25. EXISTS vs COUNT

判断记录是否存在时，尽量使用 EXISTS 代替 SELECT COUNT(1)。COUNT 函数只有在统计表中所有行数时才高效，且 COUNT(1) 比 COUNT(*) 效率更高。

26. 尽量使用 >= 代替 >

在某些情况下，>= 的效率可能优于 >。

27. 索引使用规范

  • 联合索引：必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。
  • 索引维护：周期性地重建索引、重新编译存储过程。
  • 强制索引：必要时可以通过 INDEX index_name 来强制指定索引。

28. 批量操作

当有一批插入或更新操作时，应使用批量处理，而不是逐条执行。

29. 避免循环，善用SQL

在存储过程中，能用SQL语句实现的，就不要用循环。例如，可以使用递归查询（如Oracle的 CONNECT BY）来生成连续日期，而不是在程序中循环。

30. 表连接顺序

在基于规则的优化器中（如旧版Oracle），FROM子句中写在最后的表（基础表）会被最先处理。因此，应将记录数最少的表作为基础表。

31. 优化 GROUP BY

通过在 GROUP BY 之前过滤掉不需要的记录来提高其效率。

低效:

1
2
3
4

SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER';

高效:

1
2
3
4

SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER'
GROUP BY JOB;

32. SQL语句使用大写

Oracle总是先解析SQL语句，将小写字母转换成大写再执行。因此，直接使用大写可以省去这个转换过程。

33. 使用别名

在大型数据库应用中，为表和列使用简短的别名可以提高查询速度，尤其是在多表连接时。

34. 避免死锁

  • 在存储过程和触发器中以相同的顺序访问同一个表。
  • 事务应尽可能缩短，减少涉及的数据量。
  • 不要在事务中等待用户输入。

35. 避免使用临时表

除非确有需要，否则应尽量避免使用临时表，可以考虑用表变量代替。表变量驻留在内存中，通常比存放在TempDB中的临时表更快。

36. 慎用触发器

触发器会耗费资源。如果能用约束实现的，就不要用触发器。同时，不要为不同的触发事件（INSERT、UPDATE、DELETE）使用相同的触发器，也不要在触发器中使用事务型代码。

索引创建规则：

  1. 表的主键、外键必须有索引。
  2. 数据量超过300的表应该有索引。
  3. 经常与其他表进行连接的表，在连接字段上应该建立索引。
  4. 经常出现在 WHERE 子句中的字段，特别是大表的字段，应该建立索引。
  5. 索引应该建在选择性高的字段上。
  6. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。
  7. 复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替。
  8. 正确选择复合索引中的主列字段，一般是选择性较好的字段。
  9. 如果复合索引的字段经常同时以 AND 方式出现在 WHERE 子句中，可以建立复合索引。
  10. 如果复合索引中包含的字段经常单独出现在 WHERE 子句中，则应分解为多个单字段索引。
  11. 如果复合索引所包含的字段超过3个，仔细考虑其必要性。
  12. 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引。
  13. 频繁进行数据操作的表，不要建立太多的索引。
  14. 删除无用的索引，避免对执行计划造成负面影响。
  15. 尽量不要对数据库中某个含有大量重复值的字段建立索引。

37. MySQL 查询优化与备份

查询优化总结：

  • 使用慢查询日志去发现慢查询。
  • 使用 EXPLAIN 去判断查询是否正常运行。
  • 查询语句保持一致，以便利用查询缓存。
  • 在适当情况下使用 GROUP BY 而不是 DISTINCT。
  • 在 WHERE, GROUP BY 和 ORDER BY 子句中使用有索引的列。
  • 保持索引简单，不在多个索引中包含同一个列。
  • 如果MySQL用错索引，可使用 USE INDEX 强制指定。
  • 使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 避免在更新前 SELECT。
  • 使用 LIMIT M, N 时要小心，某些情况下可能减缓查询。
  • 在 WHERE 子句中使用 UNION 代替子查询。

MySQL 备份过程:

  • 从二级复制服务器上进行备份，并在备份期间停止复制。
  • 如果使用 mysqldump，请同时备份二进制日志文件。
  • 使用 mysqldump 时加上 –opt 参数。
  • 在导入数据前，临时禁用外键约束和唯一性检测以加快速度。
  • 定期监控数据尺寸的增长和复制实例的状态。

38. 减少SQL语句中的空格

查询缓冲并不自动处理空格，因此在写SQL语句时，应尽量减少空格的使用，特别是在首尾空格。

39. 合理设计分表键

分表时应根据业务需求选择合适的字段作为分表键。例如，如果业务通常按用户名查询，则应使用 username 的哈希值来分表，而不是用户ID。可以考虑使用MySQL的 PARTITION 功能，它对应用层是透明的。

40. 表主键设计

  • 为每张表设置一个ID作为其主键，最好是 INT 型（推荐 UNSIGNED），并设置为 AUTO_INCREMENT。
  • 在所有存储过程和触发器的开始处设置 SET NOCOUNT ON，在结束时设置 SET NOCOUNT OFF，以减少网络传输。

41. MySQL性能优化技巧

  • 启用查询缓存：当同一个查询被执行多次时，从缓存中提取数据会快很多。
  • 使用 EXPLAIN：分析SQL语句的执行计划，找出性能瓶颈。
  • 使用 LIMIT 1：当确定结果只有一条时，加上 LIMIT 1 可以让数据库在找到一条记录后立即停止搜索。
  • 选择合适的存储引擎：
    • MyISAM: 适用于以读和插入为主，较少更新和删除的场景。
    • InnoDB: 适用于需要事务处理和高并发读写的场景。
  • 优化数据类型：
    • 原则：更小、更简单、避免 NULL。
    • 示例：用 MEDIUMINT 代替 INT；用 TIMESTAMP（4字节）代替 DATETIME（8字节）。
    • 尽量将字段设置为 NOT NULL，查询时无需比较 NULL 值。
    • 对“省份”、“性别”等字段使用 ENUM 类型，其处理速度比文本类型快。

42. 字符串数据类型选择

合理选择 CHAR、VARCHAR、TEXT 等数据类型，以平衡空间和性能。

43. 将操作移到等号右边

任何对列的操作（如数据库函数、计算表达式）都将导致表扫描。查询时要尽可能将操作移至等号右边。

Database
Sql Database Performance
本文由作者按照 CC BY 4.0 进行授权