MySQL 5.5版本的两个执行引擎

 

执行引擎引入

我们真正的索引结构要去落地的时候呢也就是MySQL底层B+Tree数据结构要去落地的话那么一定要和我们的存储引擎相结合。接下来我们会说MySQL 5.5版本的两个执行引擎一个是5.5之前的MyISAM一个5.5之后的InnoDB而我们结合着索引本身的数据结构和MySQL数据库生成的文件去理解。

MySQL本身形成的数据文件分为表数据文件和用户文件两种。我们可以执行
 

show variables like 'datadir'

 
来查询数据存放的位置

 
 

MySQL执行引擎生成的文件

我们创建两个表
一个是my_table_innodb 执行引擎是InnoDB
一个是my_table_myisam 执行引擎是MyIsam

当他们设置的存储引擎不同MySQL形成的表的文件也不同
innoDB索引形成的两个文件是 my_table_innodb.frm 和 my_table_innodb .idb
my_table_myisam 形成的三个文件是 my_table_myisam.frm 和 my_table_myisam.MYI 和 my_table_myisam.MYD
 

 
其中都有 .frm 文件不同的是innoDB存储引擎产生的文件叫 idb 而myisam形成的文件叫 MYI 和 MYD。这个.frm 文件我们可以简单理解为记录我们表结构的文件比如字段和字段长度之类的

MyIsam

比如我们创建一个User表建立一个主键id,首先会去user.myi中去开辟一个空间去存放索引的结构。当我们执行
 

 select * from user where id = 12345

 
这个时候会从我们的user.myi（index,中去找最终找到了在叶子节点数据区中返回一个磁盘的地址值然后这个磁盘的地址值会去指向我们真正数据的位置也就是指向user.myd（data文件中记录的位置。所以说MyISAM中的索引的数据结构和数据文件是分开的。user.myi（index和user.myd（data。
 
 

所以Myisam执行引擎中不管我们在表中建立的是主键索引还是非主键索引我们所有的数据内容都存放在myd文件里面存储所有的索引结构全部都在myi文件里面存储。细节和上面的一样。
 
 

InnoDB

接下来我们来看一看我们的InnoDB执行引擎使用B+Tree的一个落盘操作。

我们还是针对user表建立主键idMySQL会在我们的idb文件中去开辟一个空间去存放我们B+Tree的数据结构。但是InnoDB与我们MyISAM存储引擎相不同的在哪里呢？

当我们的innodb里面我们执行
select * from user where id =12345

它一样通过B+Tree的数据结构在我们的idb文件中去找叶子节点数据区但是不一样的是在我们的MyISAM执行引擎中拿到叶子节点数据区后拿到的磁盘地址值指向的是我们myd文件中真正的数据位置进行返回。但是innodb执行引擎中叶子节点数据区挂载的是真正的行记录我们可以理解为我们user表中一行一行的记录。也就是如果我们执行上面的SQL直接在idb索引文件中找然后在叶子节点数据区找到之后直接返回。

所以InnoDB执行引擎把数据文件和索引文件都放到了ibd文件中。上述是针对InnoDB建立主键索引id的。

而接下来我们建立非主键索引name.
如果我们建立非主键索引首先一样会在我们的idb文件中开辟一个空间去存放非主键索引的数据结构但是与主键索引不同的是我们在InnoDB执行引擎中建立非主键索引的时候在我们B+Tree数据结构中叶子节点的数据区挂载的不是行记录而是当前建立索引的值name和主键的值id然后拿到这个主键值id再去我们主键的索引树中去找行记录过程与主机索引过程一样。

所以这也就引申出了另外几个概念聚簇索引与非聚簇索引。
 
 

聚簇索引与非聚簇索引

在我们的InnoDB执行引擎中只有主键是聚簇索引其他索引都是非聚簇索引。这个原因在于在InnoDB执行引擎中只有主键索引叶子节点数据区挂载的是真正的行记录。
 
 

稀疏索引

还有一个概念是稀疏索引。
稀疏索引说白了就是我们MyISAM存储引擎在索引结构就是一个稀疏索引不管我们MyIsam的主键索引和非主键索引都是稀疏索引InnoDB的非主键索引也是稀疏索引

因为他们在索引结构上面给我们提供的信息是辅助我们真正想要找到的内容的信息的。

 
 
问题

为什么InnoDB执行引擎要求一定要建立主键索引？
因为InnoDB引擎中主键是聚簇索引。因为我们要把MySQL中所有的行记录挂载到索引叶子节点数据区中这个时候如果我们不建立索引资源就没地方挂载了而我们我们确实没有建立主键索引MySQL会帮你自动建立一个隐藏索引而这个隐藏索引是int类型 6byte 而如果我们自己去建立索引int 4byte 所以浪费了资源。
而如果是隐藏主键在我们的事务中实现隔离性的LBCC 我们更新数据这里会导致行锁升级为表锁。