推荐52条优化SQL语句性能的策略，收集

1。为了优化你的查询，你应该尽量避免全表扫描。首先，您应该考虑在 WHERE 和 ORDER BY 中包含的列上创建索引。

2。尽量避免对 WHERE 子句中的字段求值 NULL。 NULL是创建表时的默认值，但大多数时候应该使用NOT NULL或使用0、-1等特殊值作为默认值。

3。尽量避免在 WHERE 子句中使用 != 或 运算符。 MySQL 仅对以下运算符使用索引：=、BETWEEN、IN，有时还有 LIKE。

4。尽量避免在WHERE子句中使用OR来连接条件，否则引擎将放弃使用索引并执行全表搜索。您可以使用 UNION 合并查询： select id from t where num=10 union all select id from t where num=20。

5。 IN和NOT IN也应该谨慎使用，否则会导致全表搜索。对于连续值，如果可以使用 BETWEEN，则不要使用 IN：select id from t where num 介于 1 和 3 之间。

6。以下查询还将执行全表搜索：select id from t where name like '%abc%' 或 select id from t where name like '%abc'。如果想提高效率，可以考虑全文检索。仅当从名称如“abc%”的 t 中选择 id 时才使用索引。

7。如果在WHERE子句中使用参数，也会导致全表搜索。

8。应尽可能避免对WHERE子句中的字段进行表达式操作，并尽可能避免对WHERE子句中的字段进行函数操作。

9。很多时候，使用 EXISTS 而不是 IN 是一个不错的选择：select num from a where num in (select num from b)。替换为以下命令：从存在的 a 中选择 num（从 b 中选择 1，其中 num=a.num）。

10。虽然索引可以提高相应SELECT的效率，但它也会降低INSERT和UPDATE的效率。由于索引可以在 INSERT 或 UPDATE 过程中重建，因此应仔细考虑如何构建索引并取决于具体情况。表上的索引最好不要超过6个。如果太多，就应该考虑在一些不常用的列上创建索引。

11。应尽可能避免更新聚集索引数据列，因为聚集索引数据列的顺序就是物理表记录存储的顺序。一旦某列的值发生变化，整个表的记录顺序就会调整，这会花费很多钱。 H、如果应用系统需要频繁更新聚集索引的数据列，则必须考虑是否将该索引创建为聚集索引。

12。尝试使用数字字段。如果字段只包含数字信息，尽量不要将其设计为字符字段。这将降低查询和连接性能并增加存储开销。

13。尽可能使用varchar和nvarchar来代替char和nchar。因为首先，变长数组的存储空间较小，可以节省存储空间。其次，对于查询来说，在较小的领域内搜索效率明显更高。

14。最好不要使用 return all：select from t，用特定的字段列表替换“*”，并且不要返回任何未使用的字段。

15。尽量避免向客户端返回大量数据。如果数据量太大，就应该考虑相应的要求是否合理。

16。使用表别名（alias）：在 SQL 语句中连接多个表时，请使用表别名以及每列的别名前缀。这减少了解析时间并减少了由列歧义引起的语法错误。

17。使用“临时表”临时存储中间结果：

简化SQL语句的一个重要方法就是使用临时表临时存储中间结果。但临时表的好处远不止这些。临时结果临时存储在临时表中，后续查询位于 tempdb 数据库中。这样可以避免程序中对主表的多次扫描，也大大减少了程序执行过程中的“共享锁”阻塞。 “更新锁”减少阻塞，提高并发性能。

18。一些SQL查询语句应该加上nolock。读和写会互相阻塞，以提高并发性能。对于某些查询，您可以添加 nolock 以允许在读取时写入，但缺点是可以加载未提交的脏数据。

使用nolock有3个原则：

• 如果查询结果用于“插入、删除、修改”，则不能添加nolock；
• 查询表是分页频繁的表，所以要谨慎使用nolock；
• 临时表也可用于存储“数据对象”，其工作方式与 Oracle 的回滚表空间类似。如果可以使用临时表来提高并发性能，请不要使用 nolock。

19。简化的常见规则如下：

表的连接 (JOIN) 不得超过 5 个。考虑使用临时表或变量表来存储中间结果。使用更少的子查询并且不要将视图嵌套得太深。一般来说，建议最多嵌套 2 个视图。

20。预先计算好要查询的结果，放到一个表中，然后查询时选择。这是SQL7.0之前最重要的方法，比如计算医院费用。

21。使用 OR 的语句可以拆分为多个查询，并且可以通过 UNION 将多个查询链接起来。它们的速度只与是否使用索引有关。如果查询需要公共索引，UNION all 会更有效地工作。多个OR语句不使用索引，因此将它们重写为UNION并尝试比较索引。关键问题是是否使用索引。

22。在IN后面的值列表中，将出现次数最多的值放在开头，将出现次数最少的值放在末尾，以减少求值次数。

23。尝试将数据处理工作卸载到服务器以减少网络开销，例如使用存储过程。

存储过程是经过编译、优化、组织成执行计划并存储在数据库中的 SQL 语句。它是流控制语言的集合，当然速度很快。重复执行的动态 SQL 可以使用驻留在 Tempdb 中的临时存储过程（临时表）。 ？ ，启用SQL SERVER线程池来解决问题。如果的数量仍然=最大连接数+5，服务器性能将受到严重损害。

25。查询关联与写入顺序相同：

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B, B = '号码'） select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B, B = '号码', A = '号码'） select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码', A = '号码'）

26。尝试使用 EXISTS 而不是 select count(1) 来确定记录是否存在。 count函数仅在计算表中所有行时使用，并且count(1)比count(*)更有效。

27。尝试使用“>=”而不是“>”。

28。索引使用规范：

• 索引创建应与应用程序结合考虑。建议大型OLTP表索引不超过6个；
• 尽可能使用索引字段作为查询条件，尤其是聚类索引，如果需要可以使用索引index_name强制指定索引；
• 查询大表时，避免表扫描，必要时考虑创建新索引；
• 使用索引字段作为条件时，如果该索引是普通索引，那么必须使用索引中的第一个字段作为条件，以保证系统使用该索引，否则该索引不会被使用；
• 注意索引维护，定期构建索引，重新编译存储过程。

29。下面的条件SQL语句中的列有适当的索引，但执行速度很慢：

SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒 SELECT * FROM record WHERE amount/30 < 1000 --11秒 SELECT * FROM record WHERE convert(char(10), date, 112) = '19991201' --10秒

分析：

WHERE子句中任意列操作的结果是SQL运行时逐列计算的，因此它必须在不使用该列上的索引的情况下执行表搜索。

如果在编译查询时可以获得这些结果，则可以通过使用索引并避免表搜索的 SQL 优化器对其进行优化，因此将 SQL 重写如下：

SELECT * FROM record WHERE card_no like '5378%' -- < 1秒 SELECT * FROM record WHERE amount < 1000*30 -- < 1秒 SELECT * FROM record WHERE date = '1999/12/01' -- < 1秒

30。如果有一批附件或On update，请使用批量插入或批量更新，切勿逐条更新记录。

31。所有的存储过程中，如果可以使用SQL语句，我绝对不会使用循环来实现。

例如：列出上个月的每一天，我使用connect by递归查询，从不从上个月的第一天到最后一天循环。

32。选择最有效的表名顺序（仅在基于规则的优化器中有效）：

Oracle 解析器按照从右到左的顺序处理 FROM 子句中的表名，并且 FROM 子句中写入的表句末（基表驱动表）将首先被处理。如果FROM子句包含多个表，则必须选择记录数最少的表作为基表。

如果要连接3个以上的表进行查询，则必须选择交集表作为基表。交叉表是指被其他表引用的表。

33。为了提高GROUP BY语句的效率，可以在GROUP BY之前过滤掉不需要的记录。接下来的两个查询返回相同的结果，但第二个查询明显更快。

无效：

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

有效：

SELECT JOB, AVG(SAL) FROM EMPWHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

34。 SQL语句之所以用大写字母书写，是因为Oracle总是先解析SQL语句，将小写字母转换为大写字母，然后再执行。

35。使用别名。别名是大型数据库的一项应用技巧。这意味着查询中表名和列名由一个字母组合而成。查询速度比创建连接表时快1.5倍。

36。为了避免死锁，在存储过程和触发器中始终以相同的顺序访问同一个表；交易应尽可能缩短，交易所涉及的数据量应尽可能减少；永远不要等待用户输入交易。

37。避免使用临时表。除非必要，尽量避免使用临时表。相反，您可以使用表变量。大多数情况下（99%），变量表位于内存中，因此比位于 TempDb 数据库中的临时表更快，因此对临时表的操作需要跨数据库通信，本质上速度较慢。

38。最好不要使用触发器：

• 触发触发器并执行触发事件本身是一个资源密集型过程；
• 如果可以使用约束实现，尽量不要使用触发器；
• 不要对不同的触发事件（插入、更新和删除）使用同一个触发器；
• 不要在触发器中使用事务代码。？索引应该建立在；
• WHERE子句中频繁出现的字段，尤其是大表中的字段，应该建立索引；
• 索引应该建立在高度选择性的数组上；
• 索引应该基于小字段，不要为大文本字段甚至很长的字段创建索引；
• 创建复合索引需要仔细分析，尽量考虑使用单字段索引；
• 正确选择复合索引中主列的字段，一般是选择性较好的字段；
• AND模式下WHERE子句中是否同时出现多个复合索引字段？单字段查询很少或没有？如果是，则可以创建复合索引；否则，考虑单字段索引；
• 如果复合索引包含的字段经常单独出现在WHERE子句中，请将其分解为多个单字段索引；
• 如果复合索引If包含超过3个字段，那么仔细考虑必要性，考虑减少复合字段的数量；
• 如果这些字段同时存在单字段索引和复合索引，一般可以去掉复合索引；
• 对于数据操作频繁的表，不要创建过多的索引；
• 删除不必要的索引，避免对执行计划产生负面影响；
• 表上创建的每个索引都会增加存储开销，并且索引对于插入和删除很重要。更新操作也会增加处理开销。此外，如果存在单字段索引，过多的复合索引通常没有任何价值；相反，在添加和删除数据时也会降低性能，特别是对于频繁更新的表，负面影响更大。
• 尽量不要对数据库中包含大量重复值的字段建立索引。

40。 MySQL查询优化总结：

使用慢查询日志来查找慢查询，使用执行计划来确定查询是否正常运行，并始终测试您的查询以查看它们是否以最佳状态运行。

性能总是会随着时间的推移而改变，避免在整个表上使用count(*)，它可以锁定整个表，确保查询一致性，以便其他类似查询可以使用查询缓存，在适当的情况下使用来自DISTINCT的GROUP BY，在 WHERE、GROUP BY 和 ORDER BY 子句中使用索引列，保持索引简单，并且不要在多个索引中包含相同的列。

有时 MySQL 使用错误的索引。在这种情况下，请使用 USE INDEX 并使用 SQL_MODE=STRICT 检查是否存在问题。对于少于5条记录的索引数组，UNION OR中不使用LIMIT。

要避免在更新之前选择 SELECT，请使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE；不要使用UPDATE，不要使用MAX来实现；在某些情况下，使用索引字段和 ORDER BY LIMIT M, N 子句实际上会减慢查询速度。接下来，谨慎使用，在 WHERE 子句中使用 UNION 而不是子查询，记得在重新启动 MySQL 之前预热数据库以确保数据在内存中并且查询速度快，考虑持久连接而不是多个连接以减少开销。

基准查询，包括服务器负载使用情况。有时，当服务器负载增加时，一个简单的查询可能会影响其他查询。使用 SHOW PROCESSLIST 查看缓慢且有问题的查询。镜像在开发环境中创建的数据。所有可疑的查询都经过测试。

41。 MySQL备份过程：

• 从辅助复制服务器备份；
• 备份时停止复制，避免数据依赖不一致和外键约束；
• 完全停止MySQL，从数据库文件备份；
• 如果您使用 MySQL 转储进行备份，还要备份二进制日志文件 - 确保复制不会中断；
• 不要相信LVM快照，这很可能会导致数据不一致，从而给你以后带来问题；
• 为了更轻松地恢复一张表，请以表为单位导出数据 - 如果数据与其他表隔离。
• 使用mysqldump时使用--opt；
• 备份前检查并优化表；
• 在导入过程中暂时禁用外键限制以加快导入速度。 ;
• 为了更快导入，导入时暂时禁用唯一性检测；
• 每次备份后计算数据库、表和索引的大小，以便更好地监控数据大小的增长；
• 通过 自动调度脚本监控复制实例的错误和延迟；
• 定期备份。

42。查询缓冲区不会自动处理空格。因此，在编写 SQL 语句时应尽量减少空格的使用，尤其是前导和尾随 SQL 空格（因为查询缓冲区不会自动捕获前导和尾随空格）。

43。以mid为标准进行分表查询成员是否合适？在一般的业务需求中，一般都会使用用户名作为查询的依据。通常情况下，应该使用用户名作为分区表的哈希模块。

表分区是由MySQL分区函数完成的，对代码透明；在代码层面实现似乎不合理。

44。对于数据库中的每个表，我们应该设置ID作为主键，最好的类型是INT（推荐UNSIGNED），并设置AUTO_INCRMENT自动递增标志。

45。在所有存储过程和触发器的开头设置 SET NOCOUNT ON，在结尾设置 SET NOCOUNT OFF。在每次列出存储过程和触发器之后，无需向客户端发送 DONE_IN_PROC 消息。

46。 MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效MySQL优化方法之一。当多次执行同一个查询时，从缓存中拉取数据并直接从数据库返回要快得多。

47。 EXPLAIN SELECT 查询用于观察显示效果：

您可以使用 EXPLAIN 关键字来了解 MySQL 是如何处理您的 SQL 语句的。这可以帮助您分析查询语句或表结构的性能瓶颈。 EXPLAIN 查询结果还将告诉您如何使用索引主键以及如何搜索和排序数据表。

48。当只有一行数据时使用 LIMIT 1：

有时候查询表的时候你已经知道只会有一个结果，但是因为你可能需要加载一个游标或者你可以去检查返回的记录数。

在这种情况下，添加 LIMIT 1 可以提高性能。这样，MySQL数据库引擎在找到一条数据后就停止搜索，而不是继续寻找另一条与该记录匹配的数据。 ？ 。

50。优化表的数据类型，选择合适的数据类型：

原则：通常越小越好，简单就好，所有字段必须有默认值并尽量避免NULL。

例如：设计数据库表时，使用较小的整数类型，以占用尽可能多的磁盘空间。 （mediumint 优于 int）

例如时间字段：日期时间和时间戳。 datetime 占用 8 个字节，timestamp 占用 4 个字节，只使用了一半。时间戳表示的范围是1970-2037，适合更新时间。

MySQL可以很好地支持大量数据的访问，但一般来说，数据库中的表越小，对其执行查询的速度就越快。

因此，在创建表格时，为了获得更好的性能，我们可以将表格中字段的宽度设置得尽可能小。

例如：当您定义邮政编码字段时，如果将其设置为 CHAR(255)，则显然会向数据库添加不必要的空间。即使使用 VARCHAR 也是多余的，因为 CHAR(6) 可以很好地完成这项工作。

同样，如果可能的话，我们应该使用MEDIUMINT而不是BIGIN来定义整数字段，并且我们应该尽量将字段设置为NOT NULL，这样数据库在以后执行查询时就不必比较NULL值。

我们可以将一些文本字段（如“省”或“性别”）定义为 ENUM 类型。因为在MySQL中，ENUM类型被视为数值数据，而数值数据的处理速度比文本类型要快得多。这样我们就可以提高数据库的性能。

51。字符串数据类型：char、varchar、text。选择差异。

52。对列的任何操作都会导致表扫描，其中包括数据库函数、计算表达式等。查询时，操作应尽可能移至等号右侧。