SQL优化

最新推荐文章于 2024-07-20 17:45:06 发布

我觉得海星_98

最新推荐文章于 2024-07-20 17:45:06 发布

阅读量204

点赞数

分类专栏：数据库DB 文章标签： sql 数据库 sqlserver 面试 mysql

本文链接：https://blog.csdn.net/qq_42709715/article/details/129487483

版权

数据库DB 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

优化基础

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

优化原则

最大化利用索引
尽可能避免全表扫描
减少无效数据的查询

优化策略

SQL优化策略适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。

SQL执行顺序

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

语法顺序

在这里插入图片描述

执行顺序

在这里插入图片描述

优化方法

建表优化

在表中建立索引，优先考虑where、order by使用到的字段
尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
查询数据量大的表会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：
```
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050
```

不走索引的场景

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

数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
```
SELECT * FROM t WHERE username LIKE '%陈%'
```
```
SELECT * FROM t WHERE username LIKE '陈%'
```

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

SELECT * FROM t WHERE id IN (2,3)

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用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);

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

SELECT * FROM t WHERE id = 1 OR id = 3

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描
```
SELECT * FROM t WHERE score IS NULL
```
可以给字段添加默认值0，对0值进行判断：
```
SELECT * FROM t WHERE score = 0
```
尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描
```
SELECT * FROM T WHERE score/10 = 9
```
```
SELECT * FROM T WHERE score = 10*9
```
当数据量大时，避免使用where 1=1的条件，数据库引擎会放弃索引进行全表扫描（通常为了方便拼装查询条件，我们会默认使用该条件）
```
SELECT username, age, sex FROM T WHERE 1=1
```
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。
查询条件不能用 != 或者 <>

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

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引：
```
select col1 from table where col_varchar=123;
```

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

SELECT * FROM t order by age;

SELECT * FROM t where age > 0 order by age;

SELECT其他优化

避免出现select *：使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗
避免出现不确定结果的函数：特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。
多表关联查询时，小表在前，大表在后：在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了
使用表的别名：当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误
用where字句替换HAVING字句：避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中

查询条件优化

对于复杂的查询，可以使用中间临时表暂存数据
优化group by语句：默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，…;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，…;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序
```
SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;
```
优化join语句：MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)…替代
```
SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
```
```
SELECT col1 FROM customerinfo 
   LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
      WHERE salesinfo.CustomerID IS NULL
```
优化union查询：MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高
```
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
 
UNION 
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';
```

增删改语句优化

大批量插入数据：如果同时执行大量的插入，建议使用多个值的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);
```
适当使用commit：适当使用commit可以释放事务占用的资源而减少消耗
- 事务占用的undo数据块；
- 事务在redo log中记录的数据块；
- 释放事务施加的，减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。
使用truncate代替delete：当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

注意：使用truncate替代，不会记录可恢复的信息，数据不能被恢复
避免重复查询更新的数据：针对业务中经常出现的更新行同时又希望获得改行信息的需求，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;
```