重点知识学习(9.4)--[浅入MySQL数据库锁机制以及SQL优化]

最新推荐文章于 2024-09-14 19:55:29 发布

小智RE0

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量942

点赞数 2

分类专栏：重点知识学习文章标签：数据库 mysql

本文链接：https://blog.csdn.net/MrTumnus/article/details/122634608

版权

19 篇文章 2 订阅

订阅专栏

咱们MySqL的锁机制,主要是针对于 写 --- 写操作,一个事务在对某个数据进行数据方面的更新时,要对这个操作上锁,不然的话,别的事务突然要抢占更新数据的话,就会出问题.

commit提交或者rollback回滚来释放锁

行锁, 间隙锁 , 表锁

行级锁 :
对于某段数据加锁,防止其他事务对此数据进行操作.
这个锁的粒度最小(发生锁冲突的概率最低), 但是并发过高,但是由于要频繁加锁释放锁导致开销过大,可能会出现死锁,

间隙锁 :
在进行条件范围操作时,会给满足条件的区间数据行加锁,一次锁定相邻的一组数据记录.

表锁:
这个锁的粒度就很大了,发生锁冲突的概率较高,它是直接把整张表给锁住了,
(咱们Innodb引擎用的少, 但MyIsam引擎仅支持表锁).

共享锁 ,排他锁

共享锁(S锁 / 读操作锁) :
对数据进行读取操作的时候，不会改变数据的值.
而且要是事务A对当前数据加了共享锁,也就表示事务A可以进行读操作,不允许进行写操作;
并且此时其他事务要是想操作,只能对这个数据加共享锁,也就是说允许多个事务进行读 --- 读操作;

如果说想要给select查询操作加共享锁;可用select … lock in share mode语句。

排他锁(X锁 / 写操作锁) :
获得写锁的事务既可以写数据也可以读数据。但是一旦事务A对当前数据上了排他锁,就会禁止其他事务再来为当前数据加锁.

update,delete,insert 涉及到的数据更新操作; 会自动为数据加排他锁;

如果说想要给select查询操作加排他锁;可用 select …for update 语句

乐观锁悲观锁
乐观锁：不会真正的去锁某行记录，会通过版本号来实现区别.
在读取数据时,乐观地认为不会有别的事务对当前数据进行写操作.

悲观锁：悲观地认为不加锁就会出现问题,所以在修改数据时不允许别人进行读取数据,
行锁,间隙锁,表锁都是悲观锁.

图源<<Java核心知识点>>

实际上,写sql也是需要优化地,养成良好的代码习惯.
随着时间的积累，业务数据量的增多，SQL 的执行效率对程序的运行效率的影响逐渐增大.

一些常用的优化方法

(1) 首先对于查询语句进行整体优化, 避免进行全表扫描 ; 建议在查询条件列,排序列添加索引;
(2)合理利用索引, 避免索引失效:
- (2.1) 在 where 查询子句中避免 num is null,即查询条件避免null值; 建议将该字段设置默认为0;则在查询时遵循 where num is 0 即可;
- (2.2) 在 where 子句中避免使用!=或<>操作符;in 和 not in也慎用，连续数值建议用between;
- (2.3) 在 where 子句中避免使用or作为连接条件;
- (2.4) 在where子句避免使用模糊查询,建议为字段设置全文索引.
- (2.5) 在where子句避免对字段进行表达式操作,比如select id from t where num/2=100;你可以写成select id from t where num=100*2;
- (2.6) 在where子句中避免对字段进行函数操作;比如substring()函数;
(3)索引尽量别太多,建议组合索引; 一个表的索引数最好不要超过 6 个;
(4)尽量使用数字型字段,比如性别,状态字段;
(5)尽可能的使用 varchar 代替 char ; varchar是可变长度的类型 ;char是定长类型的;比如你定义了姓名字段为varchar (10),然后你存了2个字,它就2个长度,但是用char(10),你存两个字,他还是10个长度;