SQL语句优化(姊妹篇2)

项目早期数据量少,开发人员开发时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。
SQL优化整体主要体现在两个方面:

  1. 减少IO的次数,就是所有查询尽量全部走索引
  2. 减少IO的数据量,比如mysql5.6后的索引下推等,尽量减少传输数据量

1、查询SQL尽量不要使用select *,而是select具体字段。

反例子:

select * from employee;

正例子:

select id,name from employee;

原由:

  • 只取需要的字段,节省资源、减少网络开销。
  • select * 进行查询时,很可能就不会使用到覆盖索引了,就会造成回表查询。

2、一条记录的查询用limit 1

反例:

select id,name from employee where name='jay'

正例

select id,name from employee where name='jay' limit 1;

原由:

  • 加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。
  • 当然,如果name是唯一索引的话,是不必要加上limit 1了,因为limit的存在主要就是为了防止全表扫描,从而提高性能。

3、or 可用 union all 代理

新建一个user表,它有一个普通索引userId,表结构如下:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假设现在需要查询userid为1或者年龄为18岁的用户,很容易有以下sql

反例:

select * from user where userid=1 or age =18

正例:

//使用union all 
select * from user where userid=1 
union all 
select * from user where age = 18

//或者分开两条sql写:
select * from user where userid=1
select * from user where age = 18

原由:

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

对于 or+没有索引的age 这种情况,假设它走了userId的索引,但是走到age查询条件时,它还得全表扫描,
也就是需要三步过程: 全表扫描+索引扫描+合并
如果它一开始就走全表扫描,直接一遍扫描就完事。
mysql是有优化器的,出于 效率 与 成本 考虑,遇到or条件,索引可能失效,看起来也合情合理。

4、优化limit分页, where条件辅助

我们日常做分页需求时,一般会用 limit 实现,但是当偏移量特别大的时候,查询效率就变得低下。

反例:

select id,name,age from employee limit 10000,10

正例:

//返回上次查询的最大记录(偏移量)
select id,name from employee where id>10000 limit 10

原由:

  • 当偏移量最大的时候,查询效率就会越低,因为Mysql 并非是跳过偏移量直接去取后面的数据,而是先把 偏移量+要取的条数,然后再把前面偏移量这一段的数据抛弃掉再返回的。
  • 如果使用优化方案,返回上次最大查询记录(偏移量),这样可以跳过偏移量,效率提升不少。

5、优化like语句

日常开发中用到 模糊查询关键字like 很可能让你的索引失效。

反例:

select userId,name from user where userId like '%123';

正例:

select userId,name from user where userId like '123%';

原由:

  • 把%放前面,并不走索引
  • 把% 放关键字后面,还是会走索引的

6、索引列上 避免使用mysql的内置函数

业务需求:查询最近七天内登陆过的用户(假设loginTime加了索引)

反例:

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

正例:

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

原由:

  • 索引列上使用mysql的内置函数,索引失效
  • 如果索引列不加内置函数,索引还是会走的

7、索引列上 避免使用 表达式操作

反例:

select * from user where id-1 =10;

正例:

select * from user where id =11;

原由:

  • 虽然id加了索引,但是因为对它进行运算,索引失效了。

8、Inner join 、left join、right join,优先使用Inner join,如果是left join,左边表结果尽量小

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

反例:

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

正例:

# 左侧表 一定要放 小表
select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;

原由:

  • 如果inner join是等值连接,返回的行数比较少时,性能相对会好一点。
  • 同理,使用了左连接,左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少。

9、避免: 不等符号( !=<> ),索引失效

反例:

select age,name  from user where age <>18;

正例:

//可以考虑分开两条sql写
select age,name  from user where age <18;
select age,name  from user where age >18;

原由:

  • 使用!=和<>很可能会让索引失效

10、联合索引:带头大哥不能死,中间兄弟不能断

表结构:(有一个联合索引idx_userid_age,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优化器有关的。

11、在 where 及 order by 涉及的 列 上建立索引

反例:

select * from user where address ='深圳' order by age ;

正例:

# 添加索引
alter table user add index idx_address_age (address,age)

12、如果插入数据过多,考虑合适数据地批量插入。

反例:

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例:

//一次500批量插入,分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

原由:

  • 合适数量的批量插入性,可以减少IO的消耗

13、在适当的时候,使用 覆盖索引

覆盖索引 :就是select的数据列仅仅用从 索引中 就能够取得,不必从数据表中读取,换句话说 查询列 要被所使用的索引覆盖。

反例:

// like模糊查询,不走索引了
select * from user where uid like '%123%'

正例:

//id为主键,那么为普通索引,即覆盖索引登场了。
select uid,name,age from user where uid like '%123%';

14、字段很多慎用distinct关键字

distinct 关键字一般用来过滤重复记录,以返回不重复的记录。
在查询一个 字段 或者 很少字段 的情况下使用时,给查询带来优化效果。
但是在 字段很多 的时候使用,却会 大大降低查询效率。

反例:

SELECT DISTINCT * from  user;

正例:

select DISTINCT name from user;

原由:

  • 带distinct的语句 cpu时间 和 占用时间 都高于不带distinct的语句。因为当查询很多字段时,如果使用distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较,过滤的过程会占用系统资源,cpu时间。

15、删除冗余、重复索引

反例:

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

正例:

  //删除userId索引,因为组合索引(A,B)相当于创建了(A)和(A,B)索引
  KEY `idx_userId_age` (`userId`,`age`)

原由:

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

16、如果数据量较大,优化你的修改/删除语句。

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

反例:

//一次删除10万或者100万+?
delete from user where id <100000;
//或者采用单一循环操作,效率低,时间漫长
for(User user:list){
   delete from user; 
}

正例:

//分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000;

原由:

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

17、where句中用 默认值 代替null。

反例:

select * from user where age is not null;

正例:

//设置0为默认值
select * from user where age>0;

原由:

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

如果mysql优化器发现,走索引比不走索引成本还要高,肯定会放弃索引,这些条件!=,>,is null,is not null经常被认为让索引失效,其实是因为一般情况下,查询的成本高,优化器自动放弃的。

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

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

  • 连表越多,编译的时间和开销也就越大。
  • 把连接表拆开成较小的几个执行,可读性更高。
  • 如果一定需要连接很多表才能得到数据,那么意味着糟糕的设计了。

19、exist & in的合理利用

假设表A 员工表,表B 部门表,需要查询 所有部门 的 所有员工,很容易有以下SQL:

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

这样写等价于:

  • 先查询部门表B
  • 再由部门deptId,查询A的员工,再从A表做循环.

显然,除了使用in,我们也可以用exists实现一样的查询功能,如下:

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

那么,这样写就等价于:

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

本着小表驱动大表的逻辑,让 小表 限制性,所以如果B的数据量小于A,适合使用in,如果B的数据量大于A,即适合选择exist

20、尽量用 union all 替换 union

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

反例:

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

正例:

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

原由:

  • 如果使用union,不管检索结果有没有重复,都会尝试进行合并,然后在输出最终结果前进行排序。如果已知检索结果没有重复记录,使用union all 代替union,这样会提高效率。

21、索引不宜太多,一般5个以内。

  • 索引并不是越多越好,索引虽然提高了查询的效率,但是也降低了插入和更新的效率。
  • insert或update时有可能会重建索引,所以建索引需要慎重考虑,视具体情况来定。
  • 一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否没有存在的必要。

22、尽量使用 数字型字段

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

反例:

king_id` varchar(20) NOT NULL COMMENT '守护者Id'

正例:

`king_id` int(11) NOT NULL COMMENT '守护者Id'`

原由:

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

23、索引 远离 大量重复数据的字段

因为SQL优化器是根据表中数据量来进行查询优化的,如果索引列有大量重复数据,
Mysql查询优化器推算发现不走索引的成本更低,很可能就放弃索引了。

24、字段类型是字符串,where时一定用 引号括起来,否则索引失效

反例:

select * from user where userid =123;
select * from user where userid ='123';

原由:

  • 为什么第一条语句未加单引号就不走索引了呢? 这是因为不加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL会做隐式的类型转换,把它们转换为浮点数再做比较。

25、SQL常规优化思路

一、通过慢查日志等定位那些执行效率较低的SQL语句
二、explain 分析SQL的执行计划

需要重点关注type、key、rows、filtered、extra。

type由上至下,效率越来越高

1、ALL 全表扫描  
  
2、index 索引全扫描  
  
3、range 索引范围扫描,常用语<,<=,>=,between,in等操作  
  
4、ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中  
  
5、eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询  
  
6、const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询  
  
7、null MySQL不访问任何表或索引,直接返回结果  

虽然上至下,效率越来越高,但是根据cost模型

假设有两个索引 idx1(a, b, c), idx2(a, c)  
SQL为"select * from t where a = 1 and b in (1, 2) order by c";  
如果走idx1,那么是type为range,如果走idx2,那么type是ref;  
当需要扫描的行数,使用idx2大约是idx1的5倍以上时,会用idx1,否则会用idx2  

Extra

1、Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。  
通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。  
然后关键字被排序,并按排序顺序检索行。  
  
2、Using temporary:使用了临时表保存中间结果,性能特别差,需要重点优化  
  
3、Using index:表示相应的 select 操作中使用了覆盖索引(Coveing Index)  
避免访问了表的数据行,效率不错!如果同时出现 using where,  
意味着无法直接通过索引查找来查询到符合条件的数据。  
  
4、Using index condition:MySQL5.6之后新增的ICP  
using index condtion就是使用了ICP(索引下推),在存储引擎层进行数据过滤  
而不是在服务层过滤,利用索引现有的数据减少回表的数据。  

三、show profile 分析

了解SQL执行的线程的状态及消耗的时间。

默认是关闭的,开启语句“set profiling = 1;”

SHOW PROFILES ;  
SHOW PROFILE FOR QUERY  #{id};  

四、trace

trace分析优化器如何选择执行计划,通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";  
set optimizer_trace_max_mem_size=1000000;  
select * from information_schema.optimizer_trace;  

五、确定问题,通用解决方案:

①优化索引

②优化SQL语句:修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤

③改用其他实现方式:ES、数仓等

④数据拆分处理,比如拆分业务,拆分SQL,数据碎片处理

场景分析

案例1、最左匹配

索引

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)  

SQL语句

select * from test where orderno=''  

查询匹配从左往右匹配,要使用order_no走索引,必须查询条件携带shop_id或者索引(shop_id,order_no)调换前后顺序。

案例2、隐式转换

索引

KEY `idx_mobile` (`mobile`)  

SQL语句

select * from _user where mobile=12345678901  

隐式转换相当于在索引上做运算,会让索引失效。mobile是字符类型,使用了数字,应该使用字符串匹配,否则MySQL会用到隐式替换,导致索引失效。

案例3、大分页

索引

KEY `idx_a_b_c` (`a`, `b`, `c`)  

SQL语句

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;  

对于大分页的场景,可以优先让产品优化需求,如果没有优化的,有如下两种优化方式,

1、把上一次的最后一条数据,也即上面的c传过来,然后做“c < xxx”处理,但是这种一般需要改接口协议,并不一定可行。

2、采用延迟关联的方式进行处理,减少SQL回表,但是要记得索引需要完全覆盖才有效果,SQL改动如下

select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;  

案例4、in + order by

索引

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)  

SQL语句

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10  

in查询在MySQL底层是通过n*m的方式去搜索,类似union,但是效率比union高。

in查询在进行cost代价计算时(代价 = 元组数 * IO平均值),是通过将in包含的数值,一条条去查询获取元组数的,因此这个计算过程会比较的慢,所以MySQL设置了个临界值(eq_range_index_dive_limit),5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200,即in条件超过了200个数据,会导致in的代价计算存在问题,可能会导致Mysql选择的索引不准确。

处理方式,可以(order_status, created_at)互换前后顺序,
并且调整SQL为延迟关联来进行数据查询

案例5、范围查询阻断,后续字段不能走索引

索引

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)  

SQL语句

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10  

范围查询还有“IN、between”

案例6、不等于、不包含不能用到索引的快速搜索
select * from _order where shop_id=1 and order_status not in (1,2)  
select * from _order where shop_id=1 and order_status != 1  

在索引上,避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

案例7、优化器选择不使用索引的情况

如果要求访问的数据量很小,则优化器还是会选择辅助索引,但是当访问的数据占整个表中数据的蛮大一部分时(一般是20%左右),优化器会选择通过聚集索引来查找数据。

select * from test_order where  order_status = 1  

查询出所有未支付的订单,一般这种订单是很少的,即使建了索引,也没法使用索引。

案例8、复杂查询
select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';  
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;  

如果是统计某些数据,可能改用数仓进行解决;

如果是业务上就有那么复杂的查询,可能就不建议继续走SQL了,而是采用其他的方式进行解决,比如使用ES等进行解决。

案例9、asc和desc混用
select * from _t where a=1 order by b desc, c asc  

desc 和asc混用时会导致索引失效

案例10、大数据

对于推送业务的数据存储,可能数据量会很大,如果在方案的选择上,最终选择存储在MySQL上,并且做7天等有效期的保存,那么需要注意,频繁的清理数据,会照成数据碎片,需要联系DBA进行数据碎片处理。

  • 21
    点赞
  • 29
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值