优化SQL语句

最新推荐文章于 2023-03-05 13:48:23 发布

u010009053

最新推荐文章于 2023-03-05 13:48:23 发布

阅读量277

点赞数

分类专栏：网站文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/u010009053/article/details/127897710

版权

网站专栏收录该内容

9 篇文章 0 订阅

订阅专栏

查询SQL尽量不要使用全查 select *，而是 select + 具体字段。

反例：select * from student;
正例：select id，name, age from student;
理由：

只取需要的字段，可以节省资源、减少CPU和IO以及网络开销。
select * 进行查询时，无法使用到覆盖索引，就会造成回表查询
使用具体字段可以减少表结构变动带来的影响。

禁止使用不含字段列表的insert语句

反例：insert into values ('a', 'b', 'c');
正例：insert into t(a, b, c) values ('a','b','c');
理由：
理由：

不含字段名的 insert 语句，很难区分到底对应的是什么字段，而且只能全值插入，可读性差。
一旦表结构发生改变，很难修改。

尽量避免 where 中含有字段or

反例：select id, user_id, age, name from user where userid=1 or age =18
正例：select id, user_id, age, name from user where userid = 1 union select * from user where age = 18
理由：

使用or可能会使索引失效，从而全表扫描。
对于 or + 没有索引的字段，如上面的 age 这种情况，假设它走了userId 的索引，但是走到 age 查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并，如果它一开始就走全表扫描，直接一遍扫描就结束。
mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效。

union 和 union all 联合查询
用于合并两个或多个 select 语句的结果集
【注意】union 内部的 select 语句必须拥有相同数量的列。列必须拥有相似的数据类型。同时，每条 select 语句中的列的顺序必须相同，才能联合查询。
【不同点】union 操作符只会选取不同的结果值（行数据）。如果需要允许重复的值，需要使用 union all

使用 where 条件查询，要限定要查询的数据，避免返回多余的行，同时避免数据类型的隐式转换

假设 id 为 int 类型，查询 id = 1 的数据
反例：select id, name from student where id = '1';
正例：select id, name from student where id = 1;
理由：

需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。
隐式转换会导致索引失效

尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

反例：select age,name from user where age <> 18;
正例：
可以考虑分开两条sql写，联合查询 select age,name from user where age < 18 union select age,name from user where age > 18
理由：

使用 != 和 <> 很可能会让索引失效

where子句中考虑使用默认值代替 null

反例：select user_id, name, age from user where age is not null;# (这种会全查所有数据)
正例：select user_id, name, age from user where age > 0;– 表字段 age 设置0为默认值代替null
理由：

并不一定使用 is null 或 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关。如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件 !=，<> isnull，is not null 经常让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的。
如果把 null 值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

如果查询结果只有一条或者只需要一条记录（可能最大/小值），建议使用 limit 1

反例: select id，name from student where name='Tom '
正例: select id，name from employee where name='Tom ' limit 1;
理由：

加上 limit 1 分页后，只要找到了对应的一条记录，就不会继续向下扫描了，效率将会大大提高。
如果name是唯一索引的话，是不必要加上 limit 1 了，因为limit的存在主要就是为了防止全表扫描，从而提高性能，如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

优化 limit 分页语句

我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下
反例：select id，name，age from student limit 10000,10
正例：-- 方案一：返回上次查询的最大记录(偏移量)
select id，name from student where id > 10000 limit 10;
– 方案二：order by + 索引
select id，name from student order by id limit 10000,10;
– 方案三：在业务允许的情况下限制页数：
理由：

当偏移量大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量 + 要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。
如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。
方案二使用 order by+索引，也是可以提高查询效率的。
方案三的话，建议跟业务讨论，有没有必要查这么后的分页。因为绝大多数用户都不会往后翻太多页。

优化like 语句

反例：select * from student where name like '%strive_day'; -- 或者使用 % 包裹 select * from student where name like '%strive_day%';
正例： select * from student where name like 'strive_day%';
理由：

把 % 放前面，不会走索引查询。
把 % 放关键字后面，会走索引进行查询。
将 % 包裹关键字，也不会走索引查询。
无前置 %，只有后置 % 才会走索引查询

使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则

反例：select user_id, name, age from user where age = 10;
正例：select user_id, name, age from user where user_id = 1 and age = 21; # 符合最左匹配原则
select user_id, name, age from user where user_id = 1;
理由：

当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、（k1,k2）和（k1,k2,k3）三个索引，这就是最左匹配原则。
联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集left join
在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。right join
在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

反例:select name, age from tab1 t1 left join tab2 t2 on t1.age = t2.age where t1.id = 2;
正例:select name, age from (select * from tab1 where id = 2) t1 left join tab2 t2 on t1.age = t2.age;
理由：