一、mysql的存储引擎分类
INNODB:事务性速记引擎。5.5版本后mysql的默认存储引擎。支持ACID事务、行锁、锁表。特点:写入和查询性能较好(最常见)
MYISAM:5.5版本前mysql的默认存储引擎。特点:插入数据性能较高、查询速度优秀,但不支持事务(常见)
二、MYISAM和INNODB的分析与对比
1、MYISAM
(1)储存文件的格式
MYISAM磁盘上有三个文件
文件名和表名相同,但扩展名不同(记住)
注:恢复、备份需要用到
.frm存储的表结构
.MYD存储的数据文件
.MYI存储的索引文件
(2)查看存储引擎
(3)修改存储引擎
方法1:alter table 表名 engine=myisam;
方法2:在创表时修改存储引擎
方法3:vim /etc/my.cnf(只对新建表有效)
(4)特点
1)表级锁定。更新数据时,整个表都会被锁定(隔离级别改为不可重复读)
2)数据库在读写过程中相互阻塞(不能对同一个表又读又写)
2、INNODB(重点)
(1)特点
1)不保存表的行数。若要统计表的行数,会扫描整个表计算行数
2)自增长字段必须包含只有该字段的索引
3)delete清空表时是一行一行删,速度较慢。推荐使用truncate
(2)注意事项
1)使用like模糊查询,会进行全表扫描,锁定整个表
2)若对没有创建索引的字段进行增删改也会进行全表扫描,在此期间锁定整个表【表锁】
3)使用索引进行增删改,则是行级锁定【行锁】
(3)储存文件的格式
表名.frm——表结构文件
表名.idb——数据文件和索引文件
dp.opt——表属性文件
三、INNODB行锁和索引关系以及表锁、排他锁、死锁
1、行锁
如果使用的id字段是主键,INNODB对主键使用聚簇索引,锁定整行的记录
结论:对索引进行操作,行锁
如果name字段是一个普通索引,会锁住索引行,对应的主键一并锁住,实际上就是行锁
2、表锁
要对一个非索引键进行操作
当一个事务对非索引列进行操作,因为要全表扫描过滤,全表都被锁定,另一个事务只能查
3、for update排他锁(悲观锁)
【一个表只有一个排他锁】
4、乐观锁
不会有任何提示,只是数据不能写入,数据提交更新时会进行校验,若发生冲突,数据不生效,没有其他的报错或卡停
在表中配置一个version字段,可以自增来进行校验。通过自增校验查看数据是否冲突。或用时间戳来进行校验
5、死锁
事务之间相互等待对方资源,最后形成一个环路【基于排他锁才能发生死锁】
发生死锁时,数据库会自动选择一个事务作为受害者,然后先解除死锁再回滚事务;mysql默认的死锁机制:一旦发生死锁,会选择一个事务作为死锁的牺牲品,直接终止其中一个事务,但不会自动回滚
四、如何尽可能避免死锁?
1、业务逻辑合理,以固定的顺序访问呢表和行(开发)
2、若事务的类型复杂,对事务进行拆分,在业务允许的情况下把大事务拆分成小事务,分次执行
3、在同一事务中,尽可能一次性锁定所有需要的资源,可以减少死锁的概率
4、使用read commit隔离级别可以避免死锁
5、添加合理索引,可以减少死锁概率