这篇文章从 15 个方面,分享了 sql 优化的一些小技巧,希望对你有所帮助
在实际业务场景中,可能我们真正需要使用的只有其中一两列。查了很多数据,但是不用,
【优化】:sql 语句查询时,只查需要用到的列,多余的列根本无需查出来
union
关键字后,可以获取排序、去重后的数据union all
关键字,可以获取所有数据,包含重复的数据排序、去重的过程需要遍历、排序和比较,它更耗时,更消耗 cpu 资源
【优化】:如果能用 union all 的时候,尽量不用 union。除非是有些特殊的场景,比如业务场景中是不允许产生重复数据的
小表驱动大表:用小表的数据集驱动大表的数据集
假如有 order 和 user 两张表,其中 order 表有 10000 条数据,而 user 表有 100 条数据
这时如果想查一下,所有有效的用户下过的订单列表
可以使用 in
关键字实现:
select * from order
where user_id in (select id from user where status=1)
也可以使用 exists
关键字实现:
select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)
这种业务场景,使用 in
关键字去实现业务需求,更加合适。
【总结】:
in
适用于左边大表,右边小表exists
适用于左边小表,右边大表不建议一次批量操作太多的数据,如果数据太多数据库响应也会很慢。批量操作需要把握一个度,建议每批数据尽量控制在 500 以内。如果数据多于 500,则分多批次处理
有时候,我们需要查询某些数据中的第一条,使用 limit 1
sql 语句如下:
select id,name from category
where id in (1,2,3...100000000);
如果我们不做任何限制,该查询语句一次性可能会查询出非常多的数据,很容易导致接口超时。
①:使用 limit 做限制:
select id,name from category
where id in (1,2,3...100)
limit 500;
②:代码层面校验
public List<Category> getCategory(List<Long> ids) {
if(CollectionUtils.isEmpty(ids)) {
return null;
}
if(ids.size() > 500) {
throw new BusinessException("一次最多允许查询500条记录")
}
return mapper.getCategoryList(ids);
}
③:分批查询
如果 ids 超过 500 条记录,可以分批用多线程去查询数据。每批只查 500 条记录,最后把查询到的数据汇总到一起返回(这只是一个临时方案,不适合于ids实在太多的场景。因为 ids 太多,即使能快速查出数据,但如果返回的数据量太大了,网络传输也是非常消耗性能的,接口性能始终好不到哪里去)
有时候,我们需要通过远程接口查询数据,然后同步到另外一个数据库
如果直接获取所有的数据,然后同步过去。这样虽说非常方便,但是带来了一个非常大的问题,就是如果数据很多的话,查询性能会非常差
【优化】:
select * from user
where id> #{lastId} and create_time >= #{lastCreateTime}
limit 100;
按 id 和时间升序,每次只同步一批数据,这一批数据只有 100 条记录。每次同步完成之后,保存这 100 条数据中最大的 id 和时间,给同步下一批数据的时候用
通过这种增量查询的方式,能够提升单次查询的效率
在 mysql 中分页一般用的 limit 关键字:
select id,name,age
from user limit 10,20;
如果表中数据量少,用limit关键字做分页,没啥问题。但如果表中数据量很多,用它就会出现性能问题
【优化】:
select id,name,age
from user where id > 1000000 limit 20;
先找到上次分页最大的 id,然后利用 id 上的索引查询。不过该方案,要求id是连续的,并且有序的
mysql 中如果需要从两张以上的表中查询出数据的话,一般有两种实现方式:子查询 和 连接查询
子查询的例子如下:
select * from order
where user_id in (select id from user where status=1)
子查询语句可以通过 in 关键字实现,一个查询语句的条件落在另一个 select 语句的查询结果中。程序先运行在嵌套在最内层的语句,再运行外层的语句
子查询语句的优点是简单,结构化,如果涉及的表数量不多的话
但缺点是 mysql 执行子查询时,需要创建临时表,查询完毕后,需要再删除这些临时表,有一些额外的性能消耗
【优化】:
这时可以改成连接查询。具体例子如下:
select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1
根据阿里巴巴开发者手册的规定,join 表的数量不应该超过 3 个
如果 join 太多,mysql 在选择索引的时候会非常复杂,很容易选错索引,并且如果没有命中中,nested loop join 就是分别从两个表读一行数据进行两两对比,复杂度是 n^2
所以我们应该尽量控制 join 表的数量
如果实现业务场景中需要查询出另外几张表中的数据,可以在 a、b、c 表中冗余专门的字段,比如:在表 a 中冗余 d_name 字段,保存需要查询出的数据
join 使用最多的是 left join
、inner join
inner join
:mysql 会自动选择两张表中的小表,去驱动大表,所以性能上不会有太大的问题left join
:mysql 会默认用 left join 关键字左边的表,去驱动它右边的表。如果左边的表数据很多时,就会出现性能问题用 left join 关联查询时,左边要用小表,右边可以用大表。如果能用 inner join 的地方,尽量少用 left join
阿里巴巴的开发者手册中规定,单表的索引数量应该尽量控制在 5 个以内,并且单个索引中的字段数不超过 5 个
mysql 使用的 B+ 树的结构来保存索引的,在 insert、update和delete 操作时,需要更新 B+ 树索引。如果索引过多,会消耗很多额外的性能
高并发系统如何优化索引数量?
能够建联合索引,就别建单个索引,可以删除无用的单个索引
将部分查询功能迁移到其他类型的数据库中,比如:ElasticSeach、HBase 等,在业务表中只需要建几个关键索引即可
我们在选择字段类型时,应该遵循这样的原则:
有很多业务场景需要使用 group by 关键字,它主要的功能是去重和分组。通常它会跟 having 一起配合使用,表示分组后再根据一定的条件过滤数据
select user_id,user_name from order
group by user_id
having user_id <= 200;
这种写法性能不好,它先把所有的订单根据用户 id 分组之后,再去过滤用户 id 大于等于 200 的用户
分组是一个相对耗时的操作,为什么我们不先缩小数据的范围之后,再分组呢?
【优化】:
select user_id,user_name from order
where user_id <= 200
group by user_id
使用 where 条件在分组前,就把多余的数据过滤掉了,这样分组时效率就会更高一些
WHERE
指定行对应得条件, HAVING
指定组对应的条件WHERE
子句,后执行 HAVING
子句SQL 的执行顺序:
or 两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用 union all 或者是 union (必要的时候)的方式来代替“or”会得到更好的效果
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
对字段就行了算术运算,这会造成引擎放弃使用索引
例如LIKE “%name”或者LIKE “%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。
那如何查询%name%?
答案:使用全文索引
在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面
很多时候 sql 语句,走了索引,和没有走索引,执行效率差别很大。所以索引优化被作为 sql 优化的首选