MySQL存储引擎

1. MyISAM（非聚簇索引）

1.1 简介

MyISAM引擎基于ISAM（Indexed Sequential Access Method，有索引的顺序访问方法）存储引擎基础上进行改进而来，具有较高的插入速度、查询速度。

1.2 优点

• 在表有读取查询的同时，支持往表中插入新记录。
• 支持Blob和Text的前500个字符串索引，支持全文索引。
• 对于不会进行修改的表，支持压缩表，极大减少磁盘空间的占用。
• 支持延迟更新（更新的索引数据不会立即写入到磁盘，而是先写到内存中的缓冲区中，只有在清除缓冲区时候才会将对应的索引写入磁盘）极大地提升了写入性能。

1.3 缺点

• 不支持事务。
• 不支持外键。
• 不支持崩溃后的恢复。
• 不支持行锁，读取数据时对需要的表加共享锁，写入时对表加排他锁。（MyISAM只有表级锁）

若以MyISAM引擎创建数据库表Create table user (......)，它实际是生成3个文件：

• user.myi：索引文件。
• user.myd：数据文件。
• user.frm：数据结构类型。

当我们执行select * from user where id = 1;的时候，它的执行流程：

• 查看该表的myi文件有没有以id为索引的索引树。
• 根据这个id索引找到叶子节点的id值，从而得到它里面的数据地址。(叶子节点存的- 是索引和数据地址)
• 根据数据地址去myd文件里面找到对应的数据返回出来。

2. InnoDB（聚簇索引）

2.1 简介

MySQL（5.5版之前）的默认数据库引擎是MyISAM。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。InnoDB（事务性数据库引擎），MySQL（5.5版之后）默认的存储引擎为InnoDB。大多数时候我们使用的都是 InnoDB 存储引擎，但是在读操作频繁的情况下适合使用MyISAM。

若以InnoDB引擎创建数据库表Create table user (......)，它实际是生成2个文件：

• user.ibd，索引文件。
• user.frm，数据结构类型。

InnoDB引擎创建表时，默认的是主键索引，所以不需要.myi索引文件。

3. MYISAM和InnoDB引擎区别

• 是否支持行级锁
  MyISAM只有表级锁(table-level locking)，而InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。

• 是否支持事务和崩溃后的安全恢复
  MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe)特性的表。

• 是否支持外键
  MyISAM不支持，而InnoDB支持。

• 是否支持MVCC
  仅InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效，MVCC只在READ COMMITTED（读已提交）和REPEATABLE READ（可重复读）两个隔离级别下工作。MVCC可以使用乐观锁和悲观锁来实现。

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。