SQL优化

SQL优化

原文链接：https://zhuanlan.zhihu.com/p/265852739

1、前言

1.1 SQL优化为什么那么重要？

目前，SQL优化已经成为了衡量程序员是否优秀的硬性标准，各个招聘信息上都明码标注。

SQL优化可以极大地提高数据的查询性能和效率。

当我们在处理大量数据时，一个优化良好的SQL查询可以显著减少查询所需的时间和资源，从而提高应用程序的响应速度和整体性能。此外，SQL优化还可以帮助我们充分利用数据库的功能，避免潜在的安全漏洞，并确保数据一致性和完整性。

1.2 SQL优化具体优化哪里?

由图可知：
优化成本：硬件 > 系统配置 > 数据库表结构 > SQL及索引
优化效果：硬件 < 系统配置 < 数据库表结构 < SQL及索引

可以看出，优化的效果最明显的就是SQL和索引。

SQL优化遵循的原则：

1. 最小化数据访问：只检索需要的数据，尽量避免全表扫描和不必要的联合查询。
2. 使用恰当的索引：根据查询条件创建适当的索引，以加速数据检索操作。
3. 避免使用通配符：避免在查询语句中使用通配符，如“%”和“_”，因为它们会降低查询性能。
4. 避免使用子查询：尽量避免使用子查询，因为它们会增加数据库的负载和响应时间。
5. 选择合适的数据类型：选择合适的数据类型可以减小数据存储和处理的开销，提高查询效率。
6. 编写简洁的SQL语句：简单、直接的SQL语句更易于优化和维护。
7. 定期进行数据库维护：定期清理无用数据、重新构建索引、优化表结构等操作可以提高数据库性能。

1.3 SQL执行顺序

要理解SQL优化，首先要搞清楚SQL的执行顺序

select语句

select 
distinct filename
from tableName
joinType join tableName
on joinCondition
where whereCondition
group by filename
having havingCondition
order by orderByCondition	
limit

执行顺序

from 表名
将多个表数据通过笛卡尔积变成一个表
on 条件
对笛卡尔积的虚表进行筛选
join (内连接、左连接、有连接) 表名
指定join，用于添加数据到on之后的虚表中
group by  分组条件
having 分组筛选，
对分组后的结果进行聚合筛选
select 数据列表，
返回的单列必须在group by子句之外，聚合函数除外
distinct 数据去重
order by 排序条件
limit 行数限制

2、SQL优化

2.1 避免不走索引的场景

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

select * from tableName where username like '%张%'

优化方式：尽量在字段后面使用模糊查询。如下：

select * from tableName where username like '张%'

如果需求是要在前面使用模糊查询

• 使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置
• 使用FullText全文索引，用match against 检索
• 数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
• 当表数据量较少，可以直接用like '%xx%'

2. 尽量避免使用in 和not in，会导致引擎走全表扫描。

select * from tableName where id in (1,2)

优化方式：如果是连续数值，可以使用between代替，如下：

select * from tableName where id between 1 and 10

如果是子查询，可以使用exists代替

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。

select * from tableName where id = 1 or id = 3

优化方式：可以使用union代替or，如下：

select * from tableName where id = 1
union
select * from tableName where id = 3

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。

select * from tableName score is null

优化方式：对字段添加默认值0，对默认值进行判断，如下：

select * from tableName score = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。

select * from tableName where 1 = 1

优化方式：用代码拼装SQL时进行判断，没where条件就去掉where标签，有where条件就加and，在Mybatis中可以使用<where></where>标签。

7. 查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。

如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8. where条件仅包含复合索引非前置列

描述：有一个联合索引idx_port1_port2_port3包含三列key_port1,key_port2,key_port3。根据MySQL联合索引最左前缀法则，不会走联合索引。

select * from tableName where key_port2 = 1 and key_port3 = 2

9. 隐式类型转换造成不使用索引

比如：列为varchar类型，sql却给的int数值

select * from tableName where phone = 123

10. order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM tableName order by age;

-- 走age索引
SELECT * FROM tableName where age > 0 order by age;

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。
这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

11. 正确使用hint优化语句

MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。

一般而言，处于版本变更带来的表结构索引变化，更建议避免使用hint，而是通过Analyze table多收集统计信息。但在特定场合下，指定hint可以排除其他索引干扰而指定更优的执行计划。

1. use index(索引名) – 使用索引
2. ignore index(索引名) – 忽略索引
3. force index(索引名) – 强制使用索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用force index强制查询使用指定的索引。

12.数据表中记录数量少，优化器认为全表扫描比使用索引更快

13.数据表中的数据被频繁修改，导致索引失效

2.2 Select语句其它优化

1. 避免出现select *

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

2. 避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3.多表关联查询时，小表在前，大表在后。

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

4. 使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

5. 用where字句替换having字句

避免使用having字句，因为having只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。having中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where后面不能使用组函数

6.调整where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

2.3 增删改DML语句优化

1.主键顺序插入优于乱序插入

insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');

2.手动提交事务进行数据插入

start transaction;

insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');
insert into tb_test values(4,'tom'),(5,'cat'),(6,'jerry');
insert into tb_test values(7,'tom'),(8,'cat'),(9,'jerry');

commit;

3.大批量插入数据

如果同时执行大量的插入，建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快（方法一），一般情况下批量插入效率有几倍的差别。

方法一：

insert into T values(1,2); 
insert into T values(1,3); 
insert into T values(1,4);

方法二：

insert into T values(1,2),(1,3),(1,4); 

• 减少SQL语句解析的操作，MySQL没有类似Oracle的share pool，采用方法二，只需要解析一次就能进行数据的插入操作；
• 在特定场景可以减少对DB连接次数
• SQL语句较短，可以减少网络传输的IO。

4.避免重复查询更新的数据

针对业务中经常出现的更新行同时又希望获得改行信息的需求，MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法，在MySQL中可以通过变量实现。

例如，更新一行记录的时间戳，同时希望查询当前记录中存放的时间戳是什么，简单方法实现：

update t1 set time=now() where col1=1; 
select time from t1 where id =1; 

使用变量，可以重写为以下方式：

update t1 set time=now () where col1=1 and @now: = now (); 
select @now; 

前后二者都需要两次网络来回，但使用变量避免了再次访问数据表，特别是当t1表数据量较大时，后者比前者快很多。

5.查询优先还是更新（insert、update、delete）优先

MySQL 还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。
改变优先级还可以确保特定类型的查询被处理得更快。

我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。

下面我们提到的改变调度策略的方法主要是针对只存在表锁的存储引擎，比如 MyISAM 、MEMROY、MERGE，对于Innodb 存储引擎，语句的执行是由获得行锁的顺序决定的。MySQL 的默认的调度策略可用总结如下：

1）写入操作优先于读取操作。
2）对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。
3）对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符，允许你修改它的调度策略：

写优于读、某一时刻对于同一个数据表的写入只有一次，读操作可以有多次。

LOW_PRIORITY关键字：应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE；
HIGH_PRIORITY关键字：应用于SELECT和INSERT语句；
DELAYED关键字：应用于INSERT和REPLACE语句。

如果写入操作是一个 LOW_PRIORITY（低优先级）请求，那么系统就不会认为它的优先级高于读取操作。在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才允许写入者开始操作。这种调度修改可能存在 LOW_PRIORITY写入操作永远被阻塞的情况。

SELECT 查询的HIGH_PRIORITY（高优先级）关键字也类似。它允许SELECT 插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。

另外一种影响是，高优先级的 SELECT 在正常的 SELECT 语句之前执行，因为这些语句会被写入操作阻塞。如果希望所有支持LOW_PRIORITY 选项的语句都默认地按照低优先级来处理，那么 请使用--low-priority-updates 选项来启动服务器。通过使用INSERTHIGH_PRIORITY来把 INSERT 语句提高到正常的写入优先级，可以消除该选项对单个INSERT语句的影响。

2.4 查询条件优化

1. 对于复杂的查询，可以使用中间临时表 暂存数据；

2. 优化group by语句

默认情况下，MySQL会对 group by 分组的所有值进行排序。
如果显式包括一个包含相同的列的order by 子句，MySQL可以好不减速地对它进行优化。
因此，如果查询包括 group by 但你并不想对分组的值进行排序，可以指定order by null来禁止排序

3. 优化join语句

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接JOIN替代。

例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT col1 FROM customerinfo 
WHERE CustomerID NOT in (
  SELECT CustomerID 
  FROM salesinfo 
)

如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM customerinfo 
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
WHERE salesinfo.CustomerID IS NULL

连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

4. 优化union查询

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。
原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

5.拆分复杂SQL为多个小SQL，避免大事务

• 简单的SQL容易使用到MySQL的QUERY CACHE；
• 减少锁表时间特别是使用MyISAM存储引擎的表；
• 可以使用多核CPU。

6. 使用truncate代替delete

要删除全表中的记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。

7. 使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率 针对展现等分页需求，合适的分页方式能够提高分页的效率。

2.5 建表优化

1. 在表中建立索引，优先考虑where、order by使用到的字段。

2. 尽量使用数字型字段（如性别，男：1 女：2）

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3. 查询数据量大的表 会造成查询缓慢。

主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。

4. 用varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。