首页 > Java开发常用SQL优化指南
头像
一个java六年半实习生
编辑于 2021-12-08 10:46
+ 关注

Java开发常用SQL优化指南

大家好,我是你们的间歇性热血🔥、持续性沙雕🌟小奕😈。如果觉得写的不错有帮忙,欢迎 👍 关注 ➕;加我微信:Xi*ng**,邀你进粉丝群,每天分享技术鸡汤,相互成长,成为更优秀的🐒~

此文将持续更新Java开发必须了解的SQL军规以及注意事项和使用场景,属于必会内容 ,初步估计将会有40至60余条,后续还会陆续更新~ 敬请期待。

另外有准备找工作的同学可以看看之前写的 粉丝社招一年面经(含BATMJ) & 楼主当年阿里面经分享

文末一楼评论有楼主平时面试ta人总结必考知识范围,以及阿里校招、社招考察知识点~ 福利

🌿避免使用 【select * 】

在很多时候,我们为了方便,直接使用 select *,一次性将所有的数据查出来,其实我们在实际使用的过程中只需要其中的一个或多个字段,并不需要所有的字段。(同时注意没有必要查询的字段不要查询出来)

【select * 】不会走 覆盖索引,会出现大量的 回表 操作;
多查询的字段就浪费了数据库资源;多查询的字段在传输过程中,会增加网络传输压力和传输时间;我们列出需要查询的字段,增加代码的可读性。

覆盖索引:select的数据列只用从索引中就能够取得,不必读取数据行,换句话说就是,一个索引已经包含了我们需要查询的数据。

🌿负向条件查询不能使用索引

select * from order where status!=0 and stauts!=1not in/not exists都不是好习惯。
可以优化为in查询:
select * from order where status in(2,3)

注意:其实负向查询并不绝对会索引失效,这要看MySQL优化器的判断,全表扫描或者走索引哪个成本低了。因为数据量少时,MySQL的优化器有时会判定全表扫描,就不会命中索引了。

🌿数据区分度不大的字段不宜使用索引

select * from user where sex=1原因:性别只有男,女,每次过滤掉的数据很少,不宜使用索引。
通常说,能过滤80%数据时就可以使用索引。对于一些状态字段,如果状态值很少,不宜用索引,如果状态值很多,能够过滤大量数据,则应该建立索引。

🌿尽量避免使用【union】,以【union all】代替

如果我们在知道查询结果不会有重复数据的情况下,我们尽量使用【union all】来替代【union】。

反例
select id from user where id 100;

正例
select id from user where id 100;

如果使用【union】,无论检索结果有没有重复,都会尝试合并,然后在输出前进行排序,而【union all】直接输出查询结果,效率更高。

🌿只需要查询一条数据时,使用【limit 1】

当我们明确需要查询一条数据时,我们可以使用【limit 1】,这样保证了我们查询的结果最多只有一条数据,直接用相对应的对象接收数据,不用使用集合。

反例select id from user where name = '张三';

正例select id from user where name = '张三' limit 1;

【limit 1】一旦查询到结果,不会继续执行,避免了全表扫描,提高了效率;若是唯一索引,使不使用【limit】效果相同; 接收数据可以直接使用对象接收,提高效率,节约资源。

🌿优化【limit】分页语句

当我们做分页查询时,通过【limit】实现,但是在偏移量(起始行)比较大时,查询效率会变得比较低下。

反例select id from user limit 1000, 10;

正例
方式1:直接跳过偏移量select id from user where id > 1000 limit 10;

方式2:通过【order by + 索引】优化select id from user order by id limit 1000, 10;

【limit】在查询的过程中,不是跳过偏移量,而是把所有的数据(偏移量+返回条数)取出来之后将偏移量的数据舍弃返回;在实际业务中,如果能舍弃前面的偏移量直接取结果(方式1);通过【order by + 索引】使用索引的方式进行优化。

🌿【inner/left/right join】,优先使用【inner join】,如果是【left join】,左边表结果尽量小

当我们在使用多表操作时,应优先使用【inner join】,这样关联的数据会相对来说较少。若使用【left join】尽量以小表驱动大表。

反例select o.id from order o left join user u ON u.id = o.user_id where u.age > 18;

正例select o.id from user u left join order o ON u.id = o.user_id where u.age > 18;

【inner join】内连接,在关联查询中,只保留两张表中完全匹配的结果;【left join】左连接,返回左表为主表返回所有数据,就需要以小表为主表,这样可以避免扫描不必要的数据,提高效率。

🌿 尽量避免使用【distinct】

当我们查询有很多字段,但是只有一个字段需要去重时,这时可以使用【group by】替代。

反例:select distinct * from user;

正例:此处为了表明要查询的字段比较多,使用【*】 select * from user group by name;

虽然【group by】所做的工作会比【distinct】会多一点,但是当我们数据量比较大,查询的字段很多时,而【distinct】要将查询出来的结果进行比较,去除重复的数据(查询的数据比较少或者字段比较少的时候可以考虑使用)。

🌿 尽量用【where】替换【having】

我们为了提高【group by】的效率,尽量避免使用【having】,将查询条件放在【where】中进行查询。

反例select id, name, age from user group by age having age > 18;

正例select id, name, age from user where age > 18 group by age;

【having】是在查询出结果再进行筛选的,而【where】是在聚合之前进行筛选出符合的数据,将需要的数据筛选出来之后再进行聚合,提高了效率。除非必要在聚合之后才可以筛选,否则尽量避免使用【having】。

🌿 优化【where】后面的查询条件顺序

我们通常的【where】会有多个查询条件,当我们合理地排好顺序,将筛选效率高且能去除大量数据的条件放在前面,能大大提高效率。

反例select user.id from user where age > 18 and name = '张三';

正例select user.id from user where name = '张三' and age > 18;

先去除大量的数据,之后的查询条件需要查询的数据量小,提高执行效率。

🌿优化【like】语句

在实际业务中,【like】语句是必不可免的,但同时其可能也会使索引失效,所以我们需要更好地使用【like】语句。

反例select id from user where name LIKE '%三';

正例select id from user where name LIKE '张%';

【like】中若将【%】放在前面,会使索引失效;

🌿避免使用【or】

当我们在多条件进行查询,需要使用【or】进行关联时,我们可以通过使用【union all】来替代【or】的情况。

反例select id from user where name = '张三' or age < 10;

正例select id from user where name = '张三' union all select id from user where age < 10;

🌿尽量避免在列上使用内置函数

有时候,我们的业务场景需要用到数据库的内置函数,但是我们尽量避免将内置函数放在参数一侧,而不是对应列的那一侧。

反例select id, create_time from user where DATE_ADD(create_time, INTERVAL 1 YEAR) >= NOW();

正例select id, create_time from user where create_time <= DATE_ADD(NOW(), INTERVAL -1 YEAR);

🌿尽量避免字段的类型的转换

我们在写sql时,要先清楚字段的数据类型,字段类型最好不要进行强转,否则索引会失效,同时数据库在进行数据类型转换时也是需要消耗系统资源的。

反例select id from user where age > '18';

正例select id from user where age > 18;

🌿尽量避免在【where】子句中对字段进行表达式操作

我们在写【where】子句时,字段这一侧应尽量避免进行表达式操作,这样也会导致索引失效,同时也不美观。

反例select id, age from user where age - 1 = 17;

正例select id, age from user where age = 18;

🌿尽量避免使用【!=】和【】

我们在实际业务中,若出现想要筛选出不等于的数据,可以选择使用【in】替代,将不等于某个值的其他情况取出。

反例select id from user where status 1;

正例:此处假设status取值为[1,2,3,4] select id from user where status IN (2, 3, 4);

🌿使用联合索引,需要注意索引序列的顺序

我们建立了联合索引,一般遵循最左匹配原则。所以在写条件语句时要注意顺序。

key name_age_sex (age, sex) using btree

反例
select id from user where sex = 1 AND age > 18 ;

正例:假如联合索引为(a, b, c),使用索引的情况有 [a][a, b][a, b, c] select id from user where age > 18 and sex = 1;

🌿避免使用【is null】或者【is not null】

我们在建表和插入数据时,我们应该使用默认值来替代【null】,这样就避免使用【is null】或者【is not null】。

反例select id from user where age is not null;

正例select id from user where age > 0;

注意: 并不是【is null】或者【is not null】会使索引失效,这个要根据具体的情况分析。当MySQL优化器发现,走索引的情况比不走索引的效率还要低下,查询的成本高,优化器自动放弃使用索引。此类情况有【is null】【is not】【】【!=】【in】

全部评论

(1) 回帖
加载中...
话题 回帖