项目早期数据量少,开发人员开发时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。
SQL优化整体主要体现在两个方面:
- 减少IO的次数,就是所有查询尽量全部走索引
- 减少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进行数据碎片处理。