MySQL存储引擎及其索引实现

存储引擎指表的类型及表在计算机上的存储方式。

主要的存储引擎有InnoDB、MyISAM、Memory等。

MyIASM

1、使用这个存储引擎，每个MyISAM在磁盘上存储三个文件。

• frm文件：存储表的定义数据
• MYD文件：存放表具体记录的数据
• MYI文件：存储索引

2、特点：

• 不支持事务，每次查询具有原子性
• 支持表级锁，每次操作对整个表加锁
• 采用非聚簇索引，索引文件与数据文件分离。索引文件的数据域存储指向数据记录的地址。辅助索引与主键索引基本一致，只是主索引要求key是唯一的，辅索引的key不用保证唯一性。

3、主键索引与辅助索引

1）主索引

先加载MYI文件，其中存放索引，找到对应地址之后，将MYD文件加载到内存，根据刚才得到的地址找到对应行数据，返回给用户。

上图是MyISAM主键索引，设表一共有三列，假设我们以Col1为主键。

2）辅索引

如果在Col2上建立一个辅助索引，如图

同样data域存放数据记录的地址，但辅助索引的key可以重复。

总结

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

Memory

将数据存在内存

• 表级锁
• 由于数据存在内存中，一旦服务器出现故障，数据都会丢失
• 默认使用hash索引

InnoDB

默认的数据库存储引擎。

1、特点：

• 支持事务。默认事务隔离级别为可重复读（RR），通过MVCC实现。
• 支持行级锁，可以支持更高的并发。
• 支持外键约束；外键约束降低了表的查询速度，但增加了表之间的耦合度。
• 存在缓冲管理。通过缓冲池，将索引和数据全部缓存起来，加快查询速度。
• 采用聚簇索引（索引的数据域存储数据文件本身）

2、主键索引与辅助索引

InnoDB也使用B+树作为索引结构，但区别在于InnoDB的数据文件本身就是索引文件，InnoDB辅助索引的data域存储相应记录主键的值而不是地址。

1）主索引

InnoDB的表数据文件本身就是按B+树组织的一个数据结构，树节点data域保存完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）。

2）辅索引

InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得数据记录。

总结

InnoDB索引和MyISAM索引的区别：

• 主索引的区别：InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

• 辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。