聚焦开源技术，共建鸿蒙生态
”
无论是面试还是实际开发（后端），SQL优化一直是绕不开的话题。 本文将提到52条SQL语句性能优化策略。
图片来自
有些优化策略需要你有一定的SQL实践才能明白其中的道理。 当然，你也可以根据这些优化策略进行练习，这会加深你的理解和记忆。
01
为了优化查询，应尽量避免全表扫描，并且应首先考虑在WHERE和ORDER BY涉及的列上建立索引。
02
尽量避免在WHERE子句中判断字段的NULL值。 NULL是创建表时的默认值，但大多数时候应该使用NOT NULL，或者使用特殊的值，如0、-1作为默认值。
03
应尽可能避免在 WHERE 子句中使用 != 或运算符。 MySQL 仅对以下运算符使用索引：=、IN，有时还有 LIKE。
04
尽量避免在WHERE子句中使用OR来连接条件，否则会导致引擎放弃使用索引而进行全表扫描。 您可以使用 UNION 来组合查询：
select id from t where num=10 union all select id from t where num=20
05
IN和NOT IN也要谨慎使用，否则会导致全表扫描。 对于连续值，如果可以，请不要使用 IN：
select id from t where num between 1 and 3
06
以下查询也将导致全表扫描：
select id from t where name like‘%abc%’
或者 id from t where name like '%abc' 为了提高效率，可以考虑全文检索。
来自 t where name like'abc%' 的 id 仅使用索引。
07
如果在 WHERE 子句中使用参数，它还会导致全表扫描。
08
应尽可能避免对WHERE子句中的字段进行表达式操作，并尽可能避免对WHERE子句中的字段进行函数操作。
09
很多时候，使用 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)
10
虽然索引可以提高相应的效率，但同时也降低了索引的效率。
因为索引可能会时不时地重建，所以如何构建索引需要仔细考虑，根据具体情况而定。
一个表的索引最好不要超过6个。 如果太多，就应该考虑是否有必要对一些不经常使用的列建立索引。
11
应尽可能避免更新索引数据列，因为索引数据列的顺序就是表记录的物理存储顺序。 一旦该列的值发生变化，整个表记录的顺序就会调整，这会消耗相当大的资源。
如果应用系统需要频繁更新索引数据列，那么就需要考虑是否应该将索引建为索引。
12
尽量使用数字字段，尽量不要将只包含数字信息的字段设计为字符类型，这样会降低查询和连接性能，增加存储开销。
13
尽可能使用，而不是char，nchar。 因为首先，变长字段的存储空间较小，可以节省存储空间，其次，对于查询来说，在相对较小的字段中搜索效率明显更高。
14
最好不要使用 all: from t，将“*”替换为特定的字段列表，并且不返回任何未使用的字段。
15
尽量避免向客户端返回大量数据。 如果数据量太大，就应该考虑相应的需求是否合理。
16
使用表别名（Alias）：在SQL语句中连接多个表时，请使用表别名，并在每个表前面加上别名。
这样，您可以减少解析时间并减少由歧义引起的语法错误。
17 号
使用“临时表”临时存储中间结果：简化SQL语句的一个重要方法就是使用临时表临时存储中间结果。
但临时表的好处远不止这些。 临时结果暂时存放在临时表中，后续的查询都在中间，这样可以避免程序中对主表的多次扫描，也大大减少了程序执行过程中的“共享锁”阻塞。 更新锁”，减少阻塞，提高并发性能。
18
应该添加一些SQL查询语句，读写会互相阻塞，以提高并发性能。
对于某些查询，可以加上，这样读的时候就可以允许写，但缺点是可能会读到未提交的脏数据。
有3个使用原则：
19
常见的简化规则如下： 不要超过5个表连接（JOIN），考虑使用临时表或表变量来存储中间结果。
少用子查询，并且不要嵌套视图太深。 一般来说，嵌套视图最好不要超过2个。
20
预先计算好需要查询的结果放入表中，然后等待查询。 这是SQL 7.0之前最重要的方法，比如入院计算。
21
使用OR的单词可以分解为多个查询，多个查询可以通过UNION连接起来。
它们的速度只与是否使用索引有关。 如果查询需要使用联合索引，使用UNION all执行效率更高。
多个OR子句没有使用索引，所以改写为UNION的形式，尽量匹配索引。 一个关键问题是是否使用索引。
22
IN后面的值列表中，将出现次数最多的值放在前面，出现次数最少的值放在最后，以减少判断次数。
23
尝试将数据处理放在服务器上以减少网络开销，例如使用存储过程。
存储过程是经过编译、优化、组织成执行计划并存储在数据库中的 SQL 语句。 它是控制流语言的集合，速度当然很快。
重复执行的动态SQL可以使用临时存储过程，将其放置在临时表中。
24
当服务器内存足够的情况下，配置线程数=最大连接数+5，这样可以最大化效率； 否则使用配置的线程数=“，不要使用”>”。
28
索引使用规范：
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
当有批量插入或更新时，使用批量插入或批量更新，千万不要一条一条更新记录。
31
在所有的存储过程中，我绝对不会使用循环来实现那些可以使用SQL语句的存储过程。
比如：列出上个月的每一天，我都会用by来递归查询，绝不会使用从上个月的第一天到最后一天的循环。
32
选择最有效的表名顺序（仅在基于规则的优化器中有效）：解析器的解析器按从右到左的顺序处理 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 EMP
WHERE JOB =’PRESIDENT’
OR JOB =’MANAGER’
GROUP BY JOB
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为分表标准，查询方便吗？ 一般业务需求中，基本都会考虑查询依据。 通常，应该使用哈希模来划分表。
至于分表，MySQL的作用就是干这个的，而且对代码是透明的； 在代码层面实现似乎不合理。
44
我们应该为数据库中的每个表设置一个ID作为其主键，最好是INT类型（推荐），并设置自增标志。
45
在所有存储过程和触发器的开头设置 SET ON，并在结尾设置 SET OFF。
无需在执行存储过程和触发器的每条语句后向客户端发送消息。
46
MySQL 查询可以启用高速查询缓存。 这是提高数据库性能的有效MySQL调优方法之一。
当多次执行相同的查询时，从缓存中获取数据并直接从数据库返回要快得多。
47
查询用于跟踪和查看效果：使用关键字可以让你知道MySQL如何处理你的SQL语句。
这可以帮助您分析查询语句或表结构中的性能瓶颈。 查询结果还会告诉您索引主键是如何使用的，以及数据表是如何搜索和排序的。
48
当只有一行数据时使用LIMIT 1：当你查询表时，你已经知道只会有一个结果，但是因为你可能需要取游标，或者你可能会检查返回的记录数。
在这种情况下，添加 LIMIT 1 可以提高性能。 这样，MySQL数据库引擎在找到一条数据后就会停止搜索，而不是继续搜索下一条与该记录匹配的数据。
49
为表选择合适的存储引擎：
①：应用以读和插入操作为主，只有少量的更新和删除，事务的完整性和并发性要求不是很高。
②：并发条件下要求的事务处理、数据一致性。 除了插入和查询之外，还包括许多更新和删除。 （有效减少删除和更新引起的锁）。
对于支持事务的表，影响速度的主要原因是默认设置是打开的，程序没有显式调用BEGIN启动事务，导致每次插入的项都会自动提交，严重影响速度。
可以在执行SQL之前调用begin，多个SQL形成一个东西（即使打开了），这样会大大提高性能。
50
优化表的数据类型，选择合适的数据类型：
原则：越小越好，简单就好，所有字段必须有默认值，尽量避免NULL。
例如：在设计数据库表时，尽可能使用较小的整数类型，以占用较小的磁盘空间。 （只是比 int 更合适）
例如时间字段：和。
占用8字节，占用4字节，只使用了一半。 指示的范围是1970-2037，适合更新时间。
MySQL可以很好地支持大量数据的访问，但一般来说，数据库中的表越小，对其执行查询的速度就越快。
因此，在创建表时，为了获得更好的性能，我们可以将表中字段的宽度设置得尽可能小。
例如：定义邮政编码字段时，如果设置为CHAR(255)，显然会给数据库增加不必要的空间。
即使使用这种类型也是多余的，因为 CHAR(6) 就可以很好地完成这项工作。
同样，如果可能的话，我们应该使用BIGIN而不是BIGIN来定义整型字段，并且应该尽量将字段设置为NOT NULL，这样数据库以后执行查询时就不必比较NULL值了。
对于一些文本字段，例如“省份”或“性别”，我们可以将它们定义为ENUM类型。
因为在MySQL中，ENUM类型被视为数值数据，而数值数据的处理速度比文本类型要快得多。 这样，我们就可以提高数据库的性能。
51
字符串数据类型：char、、text选择区别。
52
对列的任何操作都会导致表扫描，其中包括数据库函数、计算表达式等。查询时，尽量将操作移到等号右侧。
