Myisam和InnoDB解析

数据库文件

两种不同引擎创建后生成的文件如下,说明这两个引擎数据和索引的组织方式是不一样的。

Innodb 创建表后生成的文件有:

  • frm:创建表的语句
  • idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有

  • frm:创建表的语句
  • MYD:表里面的数据文件(myisam data)
  • MYI:表里面的索引文件(myisam index)

从生成的文件看来,这两个引擎底层数据和索引的组织方式并不一样,
MyISAM 引擎把数据和索引分开了,一人一个文件,这叫做非聚集索引方式;
Innodb 引擎把数据和索引放在同一个文件里了,这叫做聚集索引方式。

下面将从底层实现角度分析这两个引擎是怎么依靠 B+树这个数据结构来组织引擎实现的。

MyISAM 引擎的底层实现(非聚集索引方式)

MyISAM 用的是非聚集索引方式,即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树,树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后,就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

当我们为某个字段添加索引时,我们同样会生成对应字段的索引树,该字段的索引树的叶子节点同样是记录了对应数据的物理地址,然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

Innodb 引擎的底层实现(聚集索引方式)

InnoDB 是聚集索引方式,因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树,如左下图所示,而 B+树的叶子节点存储的是主键 ID 对应的数据,比如在执行 select * from user_info where id=15 这个语句时,InnoDB 就会查询这颗主键 ID 索引 B+树,找到对应的 user_name='Bob'

这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树,这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢?比如我们要给 user_name 这个字段加索引,那么 InnoDB 就会建立 user_name 索引 B+树,节点里存的是 user_name 这个 KEY,叶子节点存储的数据的是主键 KEY。注意,叶子存储的是主键 KEY!拿到主键 KEY 后,InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

问题来了,为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据,但是其他索引树却不存具体数据呢,而要多此一举先找到主键,再在主键索引树找到对应的数据呢?

其实很简单,因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引,InnoDB 都会给每个加了索引的字段生成索引树,如果每个字段的索引树都存储了具体数据,那么这个表的索引数据文件就变得非常巨大(数据极度冗余了)。从节约磁盘空间的角度来说,真的没有必要每个字段索引树都存具体数据,通过这种看似“多此一举”的步骤,在牺牲较少查询的性能下节省了巨大的磁盘空间,这是非常有值得的。

总结

在进行 InnoDB 和 MyISAM 特点对比时谈到,MyISAM 查询性能更好,从上面索引文件数据文件的设计来看也可以看出原因:MyISAM 直接找到物理地址后就可以直接定位到数据记录,但是 InnoDB 查询到叶子节点后,还需要再查询一次主键索引树,才可以定位到具体数据。等于 MyISAM 一步就查到了数据,但是 InnoDB 要两步,那当然 MyISAM 查询性能更高。

最后再总结一下什么时候需要给你的表里的字段加索引吧:

  1. 较频繁的作为查询条件的字段应该创建索引;
  2. 唯一性太差的字段不适合单独创建索引,即使该字段频繁作为查询条件;
  3. 更新非常频繁的字段不适合创建索引。

区别

Myisam InnoDB
不支持事务 支持事务
支持表级锁 支持行级锁
适合执行大量select操作 适合执行大量insert或update操作
保存行数,count操作快 不保存行数,count操作要扫表
适合频繁查询和count操作 适合更新和查询都频繁的操作

行级锁中的 共享锁 和 排他锁 :

共享锁:也叫 读锁 或 S锁,就是多个事物对于同一数据可以共享一把锁,都能访问到数据,但是只能读,不能修改。

排他锁:也叫 写锁 或 X锁,就是不能与其他锁共存,如果一个事务获取了一行数据的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁。但是获取排他锁的事务是可以对数据进行读取和修改的。

普通select查询没有任何锁机制。

update、insert、delete操作会自动加上排他锁。