Java性能优化-书写高质量SQL的建议(如何做Mysql优化)

场景

Mysql中varchar类型数字排序不对踩坑记录：

Mysql中varchar类型数字排序不对踩坑记录_mysql vachar排序有问题-博客

为避免开发过程中针对mysql语句的写法再次踩坑，总结开发过程中常用书写高质量sql的一些建议。

注：

实现

查询SQL尽量不要使⽤select *而是select具体字段

理由：

只取需要的字段，节省资源、减少⽹络开销。

select * 进⾏查询时，很可能就不会使⽤到覆盖索引了，就会造成回表查询。

如果知道查询结果只有⼀条或者只要最大最小⼀条记录建议用limit 1

反例：

登录后复制

select id,name from student where name ='霸道'

• 1.

正例:

登录后复制

select id,name from student where name ='霸道' limit 1

• 1.

理由:

加上limit 1后,只要找到了对应的⼀条记录,就不会继续向下扫描了,效率将会⼤⼤提⾼。

当然，如果name是唯⼀索引的话，是不必要加上limit 1了，

因为limit的存在主要就是为了防⽌全表扫描，从⽽提⾼性能,

如果⼀个语句本身可以预知不⽤全表扫描，有没有limit ，性能的差别并不⼤。

应尽量避免在where子句中使用or来连接条件

反例：

登录后复制

select id,name from student where userid=1 or age = 18

• 1.

正例:

使用union all

登录后复制

select id,name from student where userid=1 union all  select id,name from student where age = 18

• 1.

理由:

使用or可能会使索引失效，从而全表扫描。

假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步：全表扫描+索引扫描+合并，如果它一开始

就走全表扫描，直接一遍扫描就完事。

优化limit分页

反例:

登录后复制

select id,name from student limit 10000,10

• 1.

正例:

方案1：返回上次查询的最大记录(偏移量)

登录后复制

select id,name from student where id > 10000 limit 10

• 1.

方案2：order by + 索引

登录后复制

select id,name from student order by id limit 10000,10

• 1.

方案3：在业务允许的情况下限制页数

登录后复制

select id,name from student order by id limit 10,10

• 1.

理由:

当偏移量最⼤的时候，查询效率就会越低，因为Mysql并⾮是跳过偏移量直接去取后⾯的数据，

⽽是先把偏移量+要取的条数，然后再把前⾯偏移量这⼀段的数据抛弃掉再返回的。

如果使⽤优化⽅案⼀，返回上次最⼤查询记录（偏移量），这样可以跳过偏移量，效率提升不少。

⽅案⼆使⽤order by+索引，也是可以提⾼查询效率的。

优化like语句

⽇常开发中，如果⽤到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。

反例：

登录后复制

select id,name from student where name like '%badao'

• 1.

正例：

登录后复制

select id,name from student where name like 'badao%'

• 1.

理由：

Mysql中LIKE查询以%开头不一定会让索引失效。如果查询的结果中只包含主键和索引字段则会使用索引，反之则不会。

下面进行验证，在sname字段行添加普通索引

登录后复制

CREATE INDEX name_index on test_student(sname)

• 1.

测试以%开头且只包含主键和索引字段，测试结果为使用索引

登录后复制

explain SELECT sid,sname,sage from test_student WHERE sname like '%三';

• 1.

测试以%开头单包含主键和索引字段以及其它字段，测试结果为全表扫描

登录后复制

explain SELECT sid,sname,sage from test_student WHERE sname like '%三';

• 1.

测试以%结尾且包含主键和索引字段以及其它字段，测试结果为使用索引

登录后复制

explain SELECT sid,sname,sage from test_student WHERE sname like '张%';

• 1.

使用where条件限定要查询的数据避免返回多余的行

比如查询某个用户是否是会员

反例：

登录后复制

List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");

boolean isVip = userIds.contains(userId);

• 1.
• 2.
• 3.

正例：

登录后复制

Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")

boolean isVip = userId！=null;

• 1.
• 2.
• 3.

理由：

需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。

尽量避免在索引列上使用mysql内置的函数

业务需求：

查询最近七天内登陆过的⽤户(假设loginTime加了索引)

反例：

登录后复制

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

• 1.

正例：

登录后复制

select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7DAY);

• 1.

理由：

索引列上使用Mysql的内置函数，会导致索引失效

失效测试

登录后复制

explain SELECT sid,sname,sage from test_student WHERE DATE_ADD(sdate,INTERVAL 7 DAY)>=NOW();

• 1.

不失效测试

登录后复制

explain SELECT sid,sname,sage from test_student WHERE sdate >=DATE_ADD(NOW(),INTERVAL -7 DAY);

• 1.

应尽量避免在where子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫描

在sage上添加索引

登录后复制

CREATE INDEX age_index on test_student(sage)

• 1.

反例:

登录后复制

explain SELECT sid,sname,sage from test_student WHERE sage-1 = 10

• 1.

正例：

登录后复制

explain SELECT sid,sname,sage from test_student WHERE sage = 11

• 1.

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

Inner join 内连接，在两张表进⾏连接查询时，只保留两张表中完全匹配的结果集

left join 在两张表进⾏连接查询时，会返回左表所有的⾏，即使在右表中没有匹配的记录。

right join 在两张表进⾏连接查询时，会返回右表所有的⾏，即使在左表中没有匹配的记录。

都满足SQL需求的前提下，推荐优先使⽤Inner join（内连接），如果要使⽤left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。

反例：

登录后复制

select * from tab1 t1 left join tab2 t2 on t1.size = t2.size where t1.id>2;

• 1.

正例：

登录后复制

select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;

• 1.

理由：

如果inner join是等值连接，或许返回的⾏数⽐较少，所以性能相对会好⼀点。

同理，使⽤了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的⾏数可能⽐较少。

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

反例:

登录后复制

explain SELECT sid,sname,sage from test_student WHERE sage <> 11

• 1.

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

组合索引是在多个字段上创建一个索引。

登录后复制

CREATE INDEX group_index on fruit(id,name,price);

• 1.

可以看到如果查询时包含了id则使用了索引，否则不使用索引

登录后复制

EXPLAIN SELECT * FROM fruit WHERE id='1' AND city = '北京';
EXPLAIN SELECT * FROM fruit WHERE name='苹果' AND city = '北京';

• 1.
• 2.

理由：

当我们创建⼀个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，

这就是最左匹配原则。

联合索引不满⾜最左原则，索引⼀般会失效，但是这个还跟Mysql优化器有关的。

对查询进行优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描

见文知意，不再举例。

如果插入数据过多，考虑批量插入

参考如下

SpringBoot+MybatisPlus+Mysql实现批量插入万级数据多种方式与耗时对比：

SpringBoot+MybatisPlus+Mysql实现批量插入万级数据多种方式与耗时对比_mybaytis-plus大数据批量插入

在适当的时候，使用覆盖索引

覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，⼤⼤提⾼了查询效率。

覆盖索引（Covering Index）是指一个查询操作中，数据库可以通过遍历索引而不需要访问数据行就可以返回查询所需要的数据的情况。

这样可以减少磁盘I/O，提高查询效率。

在MySQL中，要创建覆盖索引，需要确保查询中的所有列都是索引的一部分，并且索引的顺序尽可能地匹配查询中列的顺序。

例如，假设有一个表users，它有以下列和索引：

登录后复制

CREATE TABLE test_users (
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id)
);
 
CREATE INDEX idx_name ON test_users (first_name, last_name);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

如果你想要查询用户的first_name和last_name，并且你知道这些信息将用于很多查询，你可以创建一个覆盖索引

登录后复制

CREATE INDEX idx_covering ON test_users (first_name, last_name);

• 1.

这样，当你执行一个只需要这些列的查询时

登录后复制

SELECT first_name, last_name FROM test_users WHERE first_name = 'John';

• 1.

MySQL可以通过扫描索引idx_covering而不是查询数据行来提供结果，从而提高查询效率。

慎用distinct关键字

distinct 关键字⼀般⽤来过滤重复记录，以返回不重复的记录。

在查询⼀个字段或者很少字段的情况下使用时，给查询带来优化效果。

但是在字段很多的时候使⽤，却会大大降低查询效率。

反例：

登录后复制

SELECT DISTINCT * from user;

• 1.

正例：

登录后复制

select DISTINCT name from user;

• 1.

理由：

带distinct的语句cpu时间和占⽤时间都⾼于不带distinct的语句。

因为当查询很多字段时，如果使⽤distinct，数据库引擎就会对数据进⾏⽐较，过滤掉重复数据，

然⽽这个⽐较、过滤的过程会占⽤系统资源，cpu时间。

删除冗余和重复索引

反例：

登录后复制

KEY 'idx_userId' ('userId') KEY 'idx_userId_age' ('userId','age')

• 1.

正例：

删除userId索引，因为组合索引(A,B)相当于创建了(A)和(A,B)索引。

理由：

重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能的。

如果数据量较大，优化你的修改/删除语句

避免同时修改或删除过多数据，因为会造成cpu利⽤率过⾼，从而影响别人对数据库的访问。

⼀次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

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

反例：

登录后复制

select * from user where age is not null;

• 1.

正例：

登录后复制

select * from user where age>0;

• 1.

设置age列0为默认值。

理由：

并不是说使⽤了is null 或者 is not null 就会不⾛索引了，这个跟mysql版本以及查询成本都有关。

如果mysql优化器发现，⾛索引比不⾛索引成本还要⾼，肯定会放弃索引，这些条件 !=，isnull，isnotnull 经常被认为让索引失效，

其实是因为⼀般情况下，查询的成本⾼，优化器⾃动放弃索引的。

如果把null值，换成默认值，很多时候让⾛索引成为可能，同时，表达意思会相对清晰⼀点。

不要有超过5个以上的表连接

连表越多，编译的时间和开销也就越⼤。

把连接表拆开成较小的几个执行，可读性更⾼。

如果⼀定需要连接很多表才能得到数据，那么意味着糟糕的设计了

exist&in的合理利用

假设表A表示某企业的员⼯表，表B表示部门表，查询所有部⻔的所有员⼯，很容易有以下SQL

登录后复制

select * from A where deptId in (select deptId from B);

• 1.

这样写等价于：

先查询部门表B

select deptId from B

再由部门deptId，查询A的员⼯

select * from A where A.deptId = B.deptId

除了使用in,我们也可以使用exist实现一样的查询功能

登录后复制

select * from A where exists (select 1 from B where A.deptId = B.deptId);

• 1.

因为exists查询的理解就是，先执⾏主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），

来决定主查询的数据结果是否保留。

那么，这样写就等价于

select * from A,先从A表做循环

select * from B where A.deptId = B.deptId,再从B表做循环.

数据库最费劲的就是跟程序链接释放。

假设链接了两次，每次做上百万次的数据集查询，查完就⾛，这样就只做了两次；

相反建⽴了上百万次链接，申请链接释放反复重复，这样系统就受不了了。

即mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使⽤in，

如果B的数据量大于A，即适合选择exist。

尽量使用union all 替换union

如果检索结果中不会有重复的记录，推荐union all 替换 union

反例：

登录后复制

select * from user where userid=1 union select * from user where age = 10

• 1.

正例：

登录后复制

select * from user where userid=1 union all select * from user where age = 10

• 1.

理由：

如果使用union，不管检索结果有没有重复，都会尝试进⾏合并，然后在输出最终结果前进行排序。

如果已知检索结果没有重复记录，使⽤union all 代替union，这样会提⾼效率。

索引不宜太多，一般5个以内

索引并不是越多越好，索引虽然提⾼了查询的效率，但是也降低了插⼊和更新的效率。

insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。

⼀个表的索引数最好不要超过5个，若太多需要考虑⼀些索引是否没有存在的必要。

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

理由：

相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销。

另外，参考文首博客踩坑。

索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段

因为SQL优化器是根据表中数据量来进⾏查询优化的，如果索引列有⼤量重复数据，

Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

尽量避免向客户端返回过多数据量

分页实现。

当在SQL语句中连接多个表时,请使⽤表的别名，并把别名前缀于每⼀列上，这样语义更加清晰

规范使用表别名。

尽可能使用varchar/nvarchar代替char/nchar

理由：

因为⾸先变长字段存储空间小，可以节省存储空间。

其次对于查询来说，在⼀个相对较小的字段内搜索，效率更高。

为了提⾼group by语句的效率，可以在执⾏到该语句前，把不需要的记录过滤掉

反例：

登录后复制

select job，avg(salary) from employee group by job having job ='president' or job = 'managent'

• 1.

正例：

登录后复制

select job，avg(salary) from employee where job ='president' or job= 'managent' group by job；

• 1.

如果字段类型是字符串，where时⼀定用引号括起来，否则索引失效

反例：

登录后复制

explain select * from test_student where eid = 1;

• 1.

正例：

登录后复制

explain select * from test_student where eid = '1';

• 1.

理由：

因为不加单引号时，是字符串跟数字的⽐较，它们类型不匹配，MySQL会做隐式的类型转换，

把它们转换为浮点数再做比较。

使用explain分析你SQL的计划

原创作者: BADAOLIUMANGQZ 转载于: https://blog.51cto.com/BADAOLIUMANGQZ/11553754