本文会提到52条SQL语句性能优化策略(图)

本文将提到52种SQL语句性能优化策略。

1、要优化查询，尽量避免全表扫描，首先考虑在WHERE和ORDER BY涉及的列上建索引。

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

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

4、应尽量避免在WHERE子句中使用OR连接条件，否则会导致引擎放弃使用索引而进行全表扫描。您可以使用 UNION 来合并查询： id from t where num=10 union all id from t where num=20。

5、IN 和 NOT IN 也应该谨慎使用，否则会导致全表扫描。对于连续数字，请勿使用 IN: id from t where num 1 和 3。

6、下面的查询也会导致全表扫描： id from t where name like ‘%abc%’ or id from t where name like ‘%abc’ 为了提高效率，考虑全文搜索。来自 t 的 id 像 ‘abc%’ 这样的名称仅使用索引。

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

8、尽量避免对WHERE子句中的字段进行表达式操作，尽量避免对WHERE子句中的字段进行函数操作。

9、很多时候用它来代替IN 是一个不错的选择：num from a where num in(num from b)。替换为以下语句：num from a where ( 1 from b where num=a.num)。

10、索引可以提高相应的效率，但也降低了效率。因为索引可能会不时重建，如何建立索引需要慎重考虑，视具体情况而定。一张表的索引数不要超过6个。如果索引太多，要考虑是否需要在一些很少使用的列上建索引。

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

12、尽可能使用数字字段。如果字段只包含数字信息，尽量不要设计字符字段，这样会降低查询和连接的性能，增加存储开销。

13、尽量使用，而不是char、nchar。因为变长字段的存储空间小，可以节省存储空间，其次，对于查询来说，相对较小的字段的搜索效率明显更高。

14、最好不要使用 all: from t，将“*”替换为特定字段的列表，不要返回任何没有使用的字段。

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

16、使用表别名（Alias）：在一条SQL语句中连接多个表时，使用表别名，并在每张表前加上别名。这减少了解析时间并减少了由歧义引起的语法错误。

17、使用“临时表”来暂存中间结果：

简化 SQL 语句的一个重要方法是使用临时表来临时存储中间结果。然而，临时表的好处远不止这些。临时结果暂存在临时表中，后续查询都在其中，可以避免程序中多次扫描主表，大大减少程序执行中的“共享锁”阻塞。更新锁”，减少阻塞，提高并发性能。

18、应该增加一些SQL查询语句，读写会互相阻塞，以提高并发性能。对于一些查询，可以加mysql内建查询函数，这样在读的时候可以允许写，但是缺点是可能会读到未提交的脏数据。

有3个使用原则：

19、常用的简化规则如下：

没有超过 5 个表连接（JOIN），考虑使用临时表或表变量来存储中间结果。使用较少的子查询，不要嵌套太深的视图。通常，建议不要嵌套超过 2 个视图。

20、预计算要查询的结果，并在查询前放入表中。这是SQL7.0之前最重要的方法，比如医院住院费的计算。

21、OR子句可以分解成多个查询，多个查询可以通过UNION连接起来。它们的速度只与是否使用索引有关。如果查询需要使用联合索引，使用 UNION all 效率更高。多个 OR 子句不使用索引，将其重写为 UNION 形式并尝试匹配索引。一个关键问题是是否使用索引。

22、在IN之后的值列表中，将出现频率最高的值放在最前面，出现次数最少的值放在最后，减少判断次数。

23、 尽量把数据处理放在服务器上，减少网络开销，比如使用存储过程。

存储过程是经过编译、优化、组织成执行计划并存储在数据库中的 SQL 语句。它们是控制流语言的集合，当然速度很快。对于重复执行的动态SQL，可以使用临时存储过程，（临时表）放在.

24、服务器有足够内存时，配置线程数=最大连接数+5，可以最大化效率；否则，使用配置线程数

25、查询的关联写法顺序相同：

a., * 来自 a, b 其中 = b。和一个。=”（A = B，B =’数字’）

a., * 来自 a, b 其中 a. = 乙。和一个。=”和b。=”（A = B，B =’数字’，A =’数字’）

a., * 来自 a, b 其中 b. =”和一个。=”（B =’数字’，A =’数字’）

26、尽可能使用代替count(1)来判断一条记录是否存在。count函数只在统计表中所有行的时候使用，count(1)是比 count(*) 更有效。

27、尽可能使用“>=”，而不是“>”。

28、索引的使用说明：

29、以下SQL条件语句中的列被正确索引，但是执行速度很慢：

* 从哪里 (, 1, 4) = ‘5378’ — 13 秒

* 从哪里 /30

* FROM WHERE (char(10), date, 112) = ” –10 秒

分析：

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

如果这些结果在查询编译时可用，它们可以通过 SQL 优化器进行优化，使用索引，避免表搜索，因此重写 SQL 如下：

* 来自“5378%”之类的地方——

* 从哪里

* FROM WHERE 日期 = ‘1999/12/01’ —

30、当有批量插入或更新时，使用批量插入或批量更新永远不会一条一条地更新记录。

31、在所有的存储过程中，我永远不会使用循环来实现SQL语句。

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

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

解析器以从右到左的顺序处理 FROM 子句中的表名。FROM 子句中写入的最后一个表（基表表）将被首先处理。在 FROM 子句中包含多个表的情况下mysql内建查询函数，必须选择记录最少的表作为基表。

如果要加入查询的表超过3张，则需要选择交叉表（表）作为基表，交叉表是指被其他表引用的表。

33、通过在GROUP BY之前过滤掉不必要的记录来提高GROUP BY语句的效率。以下两个查询返回相同的结果，但第二个查询要快得多。

低效：

工作，平均（SAL）

来自 EMP

按工作分组

工作 = ”

或工作 = ”

高效的：

工作，平均（SAL）

来自 EMP

工作地点 = ”

或工作 = ”

按工作分组

34、SQL语句是大写的，因为SQL语句总是先被解析，小写字母转换成大写后再执行。

35、别名的使用，别名是大型数据库的应用技巧，即查询中表名和列名用字母加别名，查询速度1.5倍比建立连接表更快。

36、避免死锁，在你的存储过程和触发器中总是以相同的顺序访问同一张表；尽可能缩短交易时间，尽可能少涉及交易金额的数据；永远不要在交易中等待用户输入。

37、避免使用临时表。除非有必要，否则应尽量避免使用临时表。相反，您可以改用表变量。大多数时候（99%），表变量驻留在内存中，因此比驻留在数据库中的临时表更快，因此对临时表的操作需要跨数据库通信，这自然会更慢。

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

39、索引创建规则：

40、MySQL查询优化总结：

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

性能总会随时间变化，避免对整张表使用count(*)，可能会锁定整张表，使查询保持一致，以便后续类似查询可以使用查询缓存，适当时使用GROUP BY，使用索引列在 WHERE、GROUP BY 和 ORDER BY 子句中，保持索引简单，并且不要在多个索引中包含同一列。

有时MySQL会使用错误的索引，这种情况下使用USE INDEX，检查使用=的问题，对于少于5条记录的索引字段，UNION时使用LIMIT而不是OR。

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

基准查询，包括使用服务器上的负载，有时一个简单的查询会影响到其他查询，当服务器上负载增加时，使用 SHOW 看看慢的和有问题的查询，都是在开发环境中产生的镜像数据中测试的可疑查询.

41、MySQL备份过程：

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

43、 以mid为分表标准查询方便吗？一般业务需求中，基本都是使用查询依据，应该是hash取模分表。

如果分表，MySQL的作用就是这样做，对代码透明；在代码级别实现它似乎不合理。

44、我们应该为数据库中的每个表设置一个ID作为其主键，最好是INT类型（推荐），并在其上设置自增标志。

45、在所有存储过程和触发器的开头设置SET ON，最后设置SET OFF。存储过程和触发器的每条语句执行完毕后，无需向客户端发送消息。

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

47、 查询用于跟踪查看性能：

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

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

当您查询该表一段时间时，您已经知道只会有一个结果，但是因为您可能需要获取游标，或者您可能会检查返回的记录数。

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

49、为表选择合适的存储引擎：

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

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

例如，在设计数据库表时，尽可能使用较小的整数类型，以占用更少的磁盘空间。（只是比 int 好）

例如时间字段：和。占用8个字节，占用4个字节，只用了一半。指示的范围是 1970-2037，适用于更新时间。

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

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

例如：在定义邮政编码字段时，如果设置为CHAR(255)，显然会给数据库增加不必要的空间。即使使用这种类型也是多余的，因为CHAR(6)可以很好地完成任务。

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

对于某些文本字段，例如“”或“”，我们可以将它们定义为 ENUM 类型。因为在 MySQL 中，ENUM 类型被视为数值数据，数值数据的处理速度比文本类型快得多。这样，我们可以再次提高数据库的性能。

51、 数据类型：char, , text 选择区别。

52、对列的任何操作都会导致表扫描，包括数据库函数、计算表达式等，查询时尽量将操作移到等号右边。

文章来源:https://www.163.com/dy/article/GUL7NPDQ0552WWM5.html

------本页内容已结束，喜欢请分享------

感谢您的来访，获取更多精彩文章请收藏本站。