SQL优化

最新推荐文章于 2024-06-28 20:27:13 发布

举世武双

最新推荐文章于 2024-06-28 20:27:13 发布

阅读量123

点赞数

分类专栏：数据库开发文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq_33721382/article/details/112846971

版权

数据库开发专栏收录该内容

1 篇文章 0 订阅

订阅专栏

参考资料

SQL优化方向和思路

SQL查询慢。优化SQL语句，合理使用字段索引。
数据表层面：优化数据表结构、字段类型、字段索引、分表，分库、读写分离等等。
网络传输层面：减少数据库交互次数，如果能一次查询完毕的数据，就不要分成多次查询，网络传输会耗费不少时间，降低查询效率。
提升数据库服务器硬件配置，或者搭建数据库集群。
使用缓存。

SQL优化和编写技巧

合理使用索引。
- 索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能选择率高（重复值少）且被where频繁引用需要建立B树索引。
- 一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况。
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。 如：
```
select id from t where num is null
```
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询
```
select id from t where num=0
```
- 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库。
- 备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
应尽量避免在 where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
模糊查询，不能前置百分比号。前置百分比号会导致将导致全表扫描。 例如：
```
select id from t where name like '%abc%'	
```
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
```
select id from t where num=10 or num=20	
```
可以这样查询：
```
select id from t where num=10	
union all	
select id from t where num=20
```
in 和 not in 也要慎用，否则会导致全表扫描。 如：
```
select id from t where num in(1,2,3)	
```
对于连续的数值，能用 between 就不要用 in 了：
```
select id from t where num between 1 and 3	
```
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
```
select id from t where num/2=100
```
应改为:
```
select id from t where num=100*2
```
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
```
select id from t where substring(name,1,3)='abc'--name以abc开头的id	
```
应改为:
```
select id from t where name like 'abc%'
```
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
尽可能的使用 varchar代替char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

执行SQL时优化器需要将*转成具体的列；每次查询都要回表，不能走覆盖索引。

谨慎使用DISTINCT，在没有必要时不要使用它，它同UNION一样会让查询变慢。（因为查询要进行唯一性判断）
减少数据访问。最好不要频繁查询数据库，如果能一次把所需要的数据查询出来，就不要分成多次查询，多次查询会大大降低查询效率，因为网络传输耗费不少时间。

连表查询优化技巧

1. Inner join 和 left join、right join、子查询

第一：inner join内连接也叫等值连接是，left/rightjoin是外连接。能用inner join连接尽量使用inner join连接

第二，子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

反例:

Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

执行时间：2s左右

正例：

Select* from A inner join B ON A.uuid=B.uuid where b.uuid>=3000;

执行时间：1s不到

使用JOIN时候，应该用小的结果驱动大的结果。 left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向。
- 反例：
```
Select * from A left join B A.id=B.ref_id where  A.id>10
```
- 正例
```
select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;
```

exist 代替 in。

反例：

SELECT * from A WHERE id in ( SELECT id from B )

正例：

SELECT * from A WHERE id EXISTS ( SELECT 1 from A.id= B.id )

分析:
in 是在内存中遍历比较。
exist 需要查询数据库，所以当B的数据量比较大时，exists效率优于in。

in()只执行一次，把B表中的所有id字段缓存起来，之后检查A表的id是否与B表中的id相等，如果id相等则将A表的记录加入到结果集中，直到遍历完A表的所有记录。

SQL查询优化

把数据、日志、索引放到不同I/O设备上，增加读取速度。数据量越大，提高I/O更为重要。
纵向、横向分割表，减少表的尺寸。
根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。
- 索引应该尽量小，使用字节数小的列建立索引比较好，不要对有限的几个值的列建立单一索引。
使用OR的子句可以分解成多个查询，并且通过UNION连接多个查询。 它们的速度只与是否使用索引有关，如果查询需要用到联合索引，用UNION all执行的效率更高。
在查询SELECT语句中使用WHERE子句限制返回的行数，避免表扫描。
- 如果返回了不必要的数据，则会浪费服务器的I/O性能，加重了网络的负担，从而会降低查询性能。
- 如果表很大，在表扫描的期间会将表锁住，禁止其他的连接访问表，后果严重。
谨慎使用DISTINCT，在没有必要时不要使用它，它同UNION一样会让查询变慢。（因为查询要进行唯一性判断）
在IN后边值的列表中，将出现最频繁的值放在前边，出现得最少的放在后边，减少判断的次数。
一般在GROUP BY 和HAVING子句之前就能剔除多余的行，所以尽量不要用它们来剔除行的工作。
尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。
存储过程是编译好、优化过、并且被组织到一个执行规划里、存储在数据库中的SQL语句（存储过程是数据库服务器端的一段程序），是控制流语言的集合，速度当然比较快。存储过程有两种类型：
- 一种类似于SELECT查询，用于查询数据，检索到的数据能够以数据集返回给查询者。
  -另一种类似于INSERT和DELETE，它不返回数据，只是执行一个动作。
不要在一个SQL语句中多次使用相同的函数，浪费资源。