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】