sql调优的几种方式:避免使用select *、用union all 代替union、小表驱动大表、批量操作、多用limit、in中值太多、增量查询、高效的分页、用链接查询代替子查询、join数量不宜过多、join时需要注意、控制索引的数量、选择合理的字段类型、提升group by的效率、索引优化。
一.创建索引
1.要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
1)在经常需要进行检索的字段上创建索引,比如要按照表字段username进行检索,那么就应该在姓名字段上创建索引,如果经常要按照员工部门和员工岗位级别进行检索,那么就应该在员工部门和员工岗位级别这两个字段上创建索引。
2)创建索引给检索带来的性能提升往往是巨大的,因此在发现检索速度过慢的时候应该首先想到的就是创建索引。
3)个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。
二.避免在索引上使用计算
在where字句中,如果索引列是计算或者函数的一部分,DBMS的优化器将不会使用索引而使用全表查询,函数属于计算的一种,同时在in和exists中通常情况下使用EXISTS,因为in不走索引。
效率低:
select * from user where salary*22>11000(salary是索引列)
效率高:
select * from user where salary>11000/22(salary是索引列)
三.尽量将多条SQL语句压缩到一句SQL中
每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果,这个过程是非常耗时的,因此应该尽量避免过多的执行SQL语句,能够压缩到一句SQL执行的语句就不要用多条来执行。
四:用where字句替换HAVING字句
避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。
五:使用表的别名
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
1、避免使用select *
select *会查询所以字段,实际业务场景中不需要所有的字段,可以不进行查询。
2、用union all 代替union
union会排重,排重过程需要遍历,排序,比较,更消耗cpu资源。在确定唯一,没有重复数据的情况下,尽量用用union all。
3、小表驱动大表
in 的话里面驱动外面,in适合里子查询是小表
exist 的话外面驱动里面,适合外面是小表
4、批量插入
当然一次插入量也不能太大,可以分批插入。
5、使用limit
在不需要获取全部记录的情况下,使用limit获取指定数量。
6、in中值太多
查询出来数量太大,限制一次最大查询条数
还可以,多线程查询,最后把查询出来的数据汇总。
7、增量查询
select name,age from user where id>#{lastId} limit 100;
查询比上次id 大的100条
8、高效的分页
select id,name,age from user limit 10000, 20;
mysql会查询10020条,然后丢弃前面10000条,这个比较浪费资源
可以优化:
select id,name,age from user id>10000 limit 20;
找到上次分页最大id
假如id是连续的,并且有序,可以用between
注意: between要在唯一索引上分页,不然会出现每页大小不一致问题。
9、用连接查询代替子查询
MySQL如果需要在两张以上表中查询数据的话,一般有两种实现方式
子查询
连接查询
select * from order where user_id in (select id from user where name='vie');
子查询可以通过in实现,优点:这样简单,
但缺点是,MySQL执行子查询时,需要创建临时表,查询完成后再删除临时表,有一些额外开销。
可以改成连接查询:
select o.* from order o inner join user u on o.user_id = u.id where u.name='vie';
10、join表不宜过多
join表不宜超过3个,如果join太多,MySQL在选择索引时会非常复杂,很容易选错索引。
并且没有命中,nested loop join 就会分别从两个表读一行数据进行对比,时间复杂度n^2。
11、join时需要注意
join用的最多的时left join 和 inner join
left join:两个表的交集和左表的剩余数据
inner join:两个表的交集
inner join mysql会自动选择小表驱动,
left join 左边的表驱动右边的表
12、控制索引数量
索引不是越多越好,索引需要额外的存储空间,B+树保存索引,额外的性能消耗。
单表索引数量尽量控制在5个以内,且单个索引字段数量控制在5个以内。
13、选择合理的字段类型
能用数字类型就不用字符串,字符串处理速度比数字类型慢
14、提升group by效率
主要功能去重,分组
先过滤数据,减少数据,再分组
select id, name ,age from user
group by id
having id <50;
这种写法就不好,建议以下写法:
select id, name ,age from user
where id <50
group by id;
15、索引优化
强制走哪个索引
select * from user
force index(索引)