事务
Innodb引擎支持ACID事务,提高了事务的四种隔离级别。
数据库事务的特性与隔离级别_我是小Gua牛的博客-CSDN博客
MyISAM引擎不支持事务。
锁
Innodb
锁粒度是行锁。
排他锁添加,select语句末尾添加 for update,insert,update,delete会自动添加排他锁。
共享锁添加,select语句末尾添加 lock in share mode。
Innodb的行锁必须建立在有索引的基础下,否则行锁会升级为表锁。同样如果索引失效,行锁同样升级为表锁。
行锁优点:减少了锁冲突,提高了处理并发的能力。
缺点:开销大,加锁慢。有可能会导致死锁。例如事务A更新id=1的数据,事务B更新id=2的数据,AB都没有提交,此时事务A更新id=2的数据,事务B更新id=1的数据,双方在等待对方释放锁,于是变成了死锁。
MyISAM
粒度是表锁。
排他锁添加 lock table tablename write。在执行insert,update,delete时会自动加排他锁。
共享锁添加 lock table tablename read。
表锁优点:开销小,加锁快。
缺点:不适合用于写操作,如果写操作频繁发生,可能导致永久阻塞。
外键
Innodb引擎支持外键。
MyISAM引擎不支持外键。
索引
Innodb引擎是聚簇索引,聚簇索引将数据和索引放在同一个文件中,默认选择主键作为聚簇索引,如果没有主键,则选择唯一非空索引。如果没有唯一非空索引,则隐性定义一个主键作为聚簇索引。
聚簇索引根据主键值可以直接找到数据。
而建立在聚簇索引上的如复合索引,唯一索引,前缀索引等则统称为辅助索引。通过辅助索引找到的是主键值,拿到主键值再到聚簇索引去找到数据,所以需要查询两次。
所以Innodb在使用主键进行范围查询和排序的效率是很高的。并且不需要维护辅助索引,只需要维护聚簇索引。但是维护主键的代价高,有可能在插入主键时出现页分裂等情况,同时Innodb需要同时缓存数据和索引。
MyISAM引擎是非聚簇索引,数据储存文件格式为MYD,索引存储文件格式为MYI,数据和索引是分开存储的。而非聚簇索引的B+树,主索引和辅助索引存放的都是直接指向数据文件的地址,不需要二次查询。MyISAM只需要缓存索引。
Innodb引擎要维护的东西很多,所以查询上MyISAM要比Innodb快。
在做select count(*) from table时,MyISAM可以直接从缓存中获得记录,而Innodb需要去做扫描。但是在有where约束条件后,两者的速度其实差不多。
Innodb有一个MVCC多版本并发控制机制,会隐性地给每一行数据添加创建和销毁两个字段值,通过维护这两个字段值来保证数据在并发情况下的安全。
Innodb使用的聚簇索引会先定位到块,再定位到行最后拿到数据,而非聚簇索引时直接通过内存地址去数据文件中找。
在需要事务处理并发的情况下选择Innodb引擎,保证事务安全。在查询多,并发少的情况下选择MyISAM引擎查询提高效率。