本文主要从工作经验中总结出来的经验总结sql语句优化问题,下面我们用Demo来具体说明如何提高sql的执行效率:
1、关于limit分页优化的问题
SELECT * FROM message_1 LIMIT 10000,10
这条语句执行速度很快,当我们把语句改成下面语句的时候再看看用了多久?
SELECT * FROM message_1 LIMIT 1000000,10
运行结果如下图:
上面的语句整整用了17.7秒!这么慢,用户可是等不了了,那么我们如何优化这句sql呢?
解决方法:
我们加“order by id”改造后的sql为:
SELECT * FROM message_1 order by id LIMIT 1000000,10
执行图如下:
只用了1.1秒,可以说稍微改动一下,效率提升了17倍。这是因为这个sql语句使用了主键id来作为索引,所以速度很快。
2、like语句的优化
SELECT content message_1 A WHERE content like '%我要学习%'
like后面使用了“%”,所以该sql语句查询会进行全表扫描,使索引失效,因此速度会很慢,如果想走索引的话,可以改下下面的语句:
SELECT content FROM message_1 WHERE content like '我要学习%'
3、避免where语句中使用IS NULL或IS NOT NULL语句
SELECT content FROM message_1 WHERE content IS NULL
上面的语句同样会使索引失效而进行全表扫描,我们在where语句中避免使用 IS NULL或者IS NOT NULL来进行条件选择,这样做会使索引失效,从而导致查询速度很慢
4、不要在where条件中出现函数、算数运算或其他表达式运算
SELECT content FROM message_1 WHERE datediff(day,createTime,'2020-05-25')=0
改为
SELECT content FROM message_1 WHERE createTime>='2020-05-01' and createTime
这句话告诉我们不要在where条件中出现函数、算数运算或其他表达式运算,否则也会使索引失效。
5、排序的索引问题
如果mysql查询语句中只用了一个索引,而where条件中已经使用了索引,则order by中的字段就不会使用索引。因此尽量不要同时对多个字段进行排序,如果有这样的场景存在的话,那最好给这些字段设置联合索引。
6、union all替换union
union和union all的区别在于前者需要合并两个以上的结果集,然后在进行唯一性过滤操作,这样做肯定会涉及到数据的排序,增大cpu的运算与资源消耗和延迟。因此,当在确定没有重复数据或者不关心重复数据的情况下,要使用union all。
7、left joinright join和inner join
SELECT A.id,A.name,B.id,B.name FROM message_1 LEFT JOIN message_2 b ON b.id =B.id;SELECT A.id,A.name,B.id,B.name FROM message_1 RIGHT JOIN message_2 b ON B A.id= B.id;SELECT A.id,A.name,B.id,B.name FROM message_1 INNER JOIN message_2 b ON A.id =B.id;
上面的语句运行后发现inner join运行速度比较快,因为inner join是等值连接,返回的行数比较少。所以在项目开发过程中最好使用inner join
8、索引的数量控制在5个以内
因为不是索引越多就越好。索引尽管提高了查询效率,但是也是降低修改和新增的效率。而且insert和update有重建索引的可能,所以一张表的索引数最好不要超过五个,如果超过五个那么就得针对这张表做优化处理了
9、任何情况下都禁止使用 select * from table
因为这样会进行全表扫描而导致效率很低
SELECT * FROM message_1
应改为
SELECT content FROM message_1
10、优化批量插入
INSERT INTO message_1(id,content) values(1,'内容1')INSERT INTO message_1(id,content) values(2,'内容2')INSERT INTO message_1(id,content) values(3,'内容3')INSERT INTO message_1(id,content) values(4,'内容4')INSERT INTO message_1(id,content) values(5,'内容5')
应改为
INSERT INTO message_1(id,content) values(1,'内容1'),(2,'内容2'),(3,'内容3'),(4,'内容4'),(5,'内容5'
11、使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则
表结构:(有一个联合索引 idxuseridage,userId 在前,age 在后)
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` int(11) NOT NULL,`age` int(11) DEFAULT NULL,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `idx_userid_age` (`userId`,`age`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
反例:
select * from user where age = 10;
正例:
//符合最左匹配原则select * from user where userid=10 and age =10;//符合最左匹配原则select * from user where userid =10;
理由如下:
当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。
联合索引不满足最左原则,索引一般会失效,但是这个还跟 MySQL 优化器有关的。
12、慎用 distinct 关键字
distinct 关键字一般用来过滤重复记录,以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时,给查询带来优化效果。但是在字段很多的时候使用,却会大大降低查询效率。
反例:
SELECT DISTINCT * from user;
正例:
select DISTINCT name from user;
理由:带 distinct 的语句 CPU 时间和占用时间都高于不带 distinct 的语句。
因为当查询很多字段时,如果使用 distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较、过滤的过程会占用系统资源,CPU 时间。
13 、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表的所有记录。
In 操作的流程原理如同一下代码
List resultSet={};
Array A=(select * from A);Array B=(select id from B);
for(int i=0;i
可以看出,当B表数据较大时不适合使用in(),因为会把B表数据全部遍历一次
如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差。
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升。
结论:in()适合B表比A表数据小的情况
exist()会执行A.length()次,执行过程代码如下
List resultSet={};Array A=(select * from A);for(int i=0;i
当B表比A表数据大时适合使用exists(),因为它没有那么多遍历操作,只需要再执行一次查询就行。
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果。
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库
原创声明:本文为【Java学习提升】原创博文,转载请注明出处。
本文来源于公众号:【Java学习提升】 专注于Java领域技术分享,Java知识体系学习、分享面试经验,让我们结伴而行,共同成长!