目的:希望大家在写sql或者使用事务等操作数据库的时候少踩坑。
受益人群:对于工作时间越短的同学收益越大,因为随着工作的沉淀和知识的积累,这些知识点渐渐都会接触到。
本篇文章分为”实践篇“和”理论篇“,更多的是以问题的形式和大家互动。
案例分析(实践篇)
1.当有张表"score"正在线上使用的时候,我们的需求需要对这张表新加一个字段"english",有下面几种操作:
1. alter table score add 'english' int comment '英语成绩';
2. alter table score add 'english' int default '0' comment '英语成绩';
3. alter table score add 'english' int not null comment '英语成绩';
4. alter table score add 'english' int not null default '0' comment '英语成绩';
- 上面几种操作语句是否可行?
- 以及不同的操作语句会导致什么结果?
- 使用了not null后default还有意义吗?
- 使用mybatis的时候xxxSelective和不以selective结尾的语句使用有什么区别和影响?
答案:对于线上使用的表,新增字段的时候尽量不要加not null的约束,因为更改seql语句后,对于之前使用这个sql语句插入语句如果不传 这个字段可能会报错。
sql 1和2可行,3和4不可取(可能会导致插入报错)。不过sql 1没有默认值,如果该字段上加了索引可能会出现索引失效的问题
有人说既然使用了not null约束,代表必须要传入当前字段,default还有意义吗?答案是依然有意义。可以理解成not null是指真正执行插入语句的时候的约束(并非是mapper中必须要传入),defalut是插入之前的逻辑处理。例如 insert into语句中未显示插入的语句系统会给默认值
insert和insertSelective的区别详情下面的理论篇
2.下面有段代码:
@Transactional
public void updateXXX(){
1. update table set score = score + 1 where id = xxx;//更新语句
2. 各种逻辑
3. 各种插入语句
}
- 以上代码会会有问题吗?
- 并发比较高情况下会有问题吗?
- 如果有问题如何去优化?
答案:
- 首先,这个事务中存在逻辑和插入语句较多,属于大事务的一种,大事务的后果见下面理论篇问题3
- 其次,更新语句中的条件是where id = xx,这里加了数据库行锁,如果被锁住的同一个id访问较为频繁就成了典型的“热点账户”问题,而且锁的位置是事务开始就加锁了,直到事务结束才会释放行锁,会造成这个sql语句的串行访问,效率较低。
- 并发较低的时候问题不明显,并发较高的场景下,行锁没来得及释放的时候下一个请求又过来了会造成锁等待,数据库链接不释放等问题。
解决方案:
- 在不影响业务逻辑的前提下,如果更新语句可以往后放可以减少所得等待时间;
- 热点账户问题可以通过拆分子账户、使用mq/缓存等替代方式解决(不过需要通过业务手段保证数据一致性)。
3.下面有段代码:
@Transactional
public void xxx(){
1. select * from table;//一条正常的查询语句
2. 大量计算//根据第一步的查询结果进行计算
3. update ....;//批量更新,根据第二步的计算结果进行更新
}
- 以上代码会会有问题吗?
- 如果有问题如何去优化?
答案:
问题: 因为第2步中有大量计算,所以这数据一个大事务,这里会造成数据库链接不释放
优化:将第1、2部的查询和计算从事务中移除,让事务的范围尽量缩小,减小事务的执行的时间。
4.使用@Transactional和@Async标签的时候有哪些注意事项?
举例:下面这个事务会生效吗?
public class Demo{
public void method1(){
this.method2();
}
@Transactional
public void method2(){
insert操作..
insert操作..
}
}
答案:
当使用注解事务的时候,由于其使用了aop的方式实现事务,在同一个类中调用带事务注解的函数是不生效的,可以先获取代理类再调用。使用super同理,@Async标签也是通用的道理。
ps:使用@Transactional和@Async的函数不能定义成private,从使用上就限制了不能在当前类中调用
获取代理类的方式:@Autowired、AopContext.currentProxy()等方式获取相应的代理对象
1.使用@Autowired在当前类中注入本身即可
2.
@Service
public class ElectricService {
ElectricService electric = ((ElectricService) AopContext.currentProxy());
electric.xx();
}
理论篇:
1.insert和insertSelective的区别
详情:insert和insertSelective的区别
2.数据库的隔离级别你真的掌握了吗?
详情:数据库的隔离级别你真的掌握了吗
3.尽量避免大事务,大事务会有什么危害?什么场景下会出现大事务?
危害:接口超时、线程池耗尽、数据库连接池耗尽、数据库获取连接时间长、缓存雪崩、导致其他系统功能和业务受到影响等。
出现大事务的场景:大量计算、调用rpc、更新/插入语句较多、锁粒度较大
知识补充: 事务中有两个不释放:事务中数据库连接、数据库锁(x锁)不释放(s锁根据隔离级别而定,rr级别不释放,rc级别释放)。
正确做法:
- 如果事务中有数据库加锁操作,在不影响业务的前提下尽量将加锁操作往后放(因为加锁操作只有事务提交后才释放锁);
- 应该尽可能减小事务范围,事务中数据库连接不释放,如果事务中有大量计算等耗时工作,尽可能拆分事务,缩小事务粒度。
4.mysql的当前读与快照读
详情:mysql的当前读与快照读
5.主从延迟会带来什么问题?如何避免、解决主从延迟问题?
答案:主从延迟问题会出现在数据库的主从架构上,会导致数据写入主库后,查询从库查不到数据。
避免:首先,我们应该从研发设计和业务上极力去避免主从延迟问题,尽量不要出现插入后立发起读的操作;
解决方案:
- 主从架构改为主备机构
- 先查从库,查不到再查主库
- 直接查主库
- mysql+缓存双写的方式,并给缓存设置个过期时间。查的时候先查缓存,查不到再查数据库
- 监听主库的binlog
对于以上方案,每种都有缺点:
使用主备架构后没有从库,就不能发挥从库的强大的查询能力;
查主库的话主库的压力比较大,不能抗住很大的访问;
使用数据库+缓存,部分场景需要保持数据一致性,增加了复杂度;ps:这里涉及到查询的数据是否会更新,如果不更新就不涉及到数据一致性问题,可以采用这种方案
监听主库的binlog复杂度较高,大部分场景也不适用。