mysql之存储引擎

一、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、添加合理索引，可以减少死锁概率