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

1.锁机制2.SQL优化

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

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

行锁, 间隙锁 , 表锁

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

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

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

共享锁 ,排他锁

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

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

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

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

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

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

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

在之前学习数据库的时候,就知道在设计数据库关系时,要遵循三范式;
MySQL基础–(4)–[多表设计,数据库设计范式,内连接,外连接,数据库备份及导入还原 ]

图源<>

实际上,写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个长度;

char的存取速度要比varchar快得多，因为其长度固定，方便程序的存储与查找；但char为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可以说是以空间换取时间效率，而varchar则是以空间效率为首位的。

(6) 查询时尽量不要用select * ,先不说安全性了,可能查询到了一堆不用的数据,降低性能.(7)避免向客户端返回大数据量,可以考虑分页查询啊;(8)避免 出现null值,

空值(‘ ’)是不占用空间的; 但是 null值占用空间.
如果查询中包含可以为 null值 的列，不走索引;
比如用count()统计查询记录数时,若有null值,那么这行记录会被忽略,但''空值不会.