mysql数据库引擎分析

mysql的MyISAM和InnoDB两个存储引擎的索引实现方式：

• MyISAM引擎使用B+ Tree作为索引结构，叶节点存放的是数据记录的地址。
• MyISAM引擎的辅助索引（二级索引）和主索引在结构上没有区别，只是辅助索引的key可以重复，叶节点上存放的也是数据记录的地址。
• MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
• InnoDB中表数据本身就是按B+ Tree组织的一个索引结构，叶节点存放的就不是数据记录的地址，而是完整的数据记录。所以InnoDB这种存储方式，又称为聚集索引，使得按主键的搜索十分高效，但二级索引搜索需要检索两遍索引：首先二级索引获得主键，然后用主键到主索引中检索到数据记录。
• 因为主键是InnoDB表记录的”逻辑地址“，所以InnoDB要求表必须有主键，MyISAM可以没有。

MySQL中普遍使用B+Tree做索引，但在实现上又根据聚簇索引和非聚簇索引而不同。


聚簇索引
所谓聚簇索引，就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引。

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
当表有聚簇索引时，他的数据行实际上存放在索引的叶子页（leaf page）中。术语 “聚簇”表示数据行和相邻的键值紧凑地存储在一起（这并非总成立）。
因为无法同时把数据行存放在两个不同的地方，索引一个表只能有一个聚簇索引。

如下图所示：

注：叶子页面包含完整的元组，而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引，但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

何时使用聚集索引或非聚集索引？

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	使用	使用
返回某范围内的数据	使用	不使用
一个或极少不同值	不使用	不使用
小数目的不同值	使用	不使用
大数目的不同值	不使用	使用
频繁更新的列	不使用	使用
外键列	使用	使用
主键列	使用	使用
频繁修改索引列	不使用	使用

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
非聚簇索


非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中，如下图：

非聚簇索引比聚簇索引多了一次读取数据的IO操作，所以查找性能上会差。

MyisAM索引与InnoDB索引相比较

• MyisAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持；
• InnoDB支持事务，MyisAM不支持；
• MyisAM顺序储存数据，索引叶子节点保存对应数据行地址，辅助索引很主键索引相差无几；InnoDB主键节点同时保存数据行，其他辅助索引保存的是主键索引的值；
• MyisAM键值分离，索引载入内存（key_buffer_size），数据缓存依赖操作系统；InnoDB键值一起保存，索引与数据一起载入InnoDB缓冲池；MyisAM主键（唯一）索引按升序来存储存储，InnoDB则不一定
• MyisAM索引的基数值（Cardinality，show index 命令可以看见）是精确的，InnoDB则是估计值。这里涉及到信息统计的知识，MyisAM统计信息是保存磁盘中，在alter表或Analyze table操作更新此信息，而InnoDB则是在表第一次打开的时候估计值保存在缓存区内；
• MyisAM处理字符串索引时用增量保存的方式，如第一个索引是‘preform’，第二个是‘preformence’，则第二个保存是‘7，ance’，这个明显的好处是缩短索引，但是缺陷就是不支持倒序提取索引，必须顺序遍历获取索引

为什么选用B+/-Tree

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

简单点说说内存读取，内存是由一系列的存储单元组成的，每个存储单元存储固定大小的数据，且有一个唯一地址。当需要读内存时，将地址信号放到地址总线上传给内存，内存解析信号并定位到存储单元，然后把该存储单元上的数据放到数据总线上，回传。

写内存时，系统将要写入的数据和单元地址分别放到数据总线和地址总线上，内存读取两个总线的内容，做相应的写操作。

内存存取效率，跟次数有关，先读取A数据还是后读取A数据不会影响存取效率。而磁盘存取就不一样了，磁盘I/O涉及机械操作。磁盘是由大小相同且同轴的圆形盘片组成，磁盘可以转动(各个磁盘须同时转动)。磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不动，磁盘转动，但磁臂可以前后动，用于读取不同磁道上的数据。磁道就是以盘片为中心划分出来的一系列同心环(如图标红那圈)。磁道又划分为一个个小段，叫扇区，是磁盘的最小存储单元。

磁盘读取时，系统将数据逻辑地址传给磁盘，磁盘的控制电路会解析出物理地址，即哪个磁道哪个扇区。于是磁头需要前后移动到对应的磁道，消耗的时间叫寻道时间，然后磁盘旋转将对应的扇区转到磁头下，消耗的时间叫旋转时间。所以，适当的操作顺序和数据存放可以减少寻道时间和旋转时间。
为了尽量减少I/O操作，磁盘读取每次都会预读，大小通常为页的整数倍。即使只需要读取一个字节，磁盘也会读取一页的数据(通常为4K)放入内存，内存与磁盘以页为单位交换数据。因为局部性原理认为，通常一个数据被用到，其附近的数据也会立马被用到。

B-Tree：如果一次检索需要访问4个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次I/O操作，完成这次检索操作，最多需要3次I/O(根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O操作就少了，检索效率也就上去了。

B+Tree：非叶子节点只存key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O操作更少了。所以B+Tree拥有更好的性能。

一般来说，MySQL有以下几种引擎：ISAM、MyISAM、HEAP、InnoDB和Berkley（BDB）。注意：不同的版本支持的引擎是有差异的。当然啦，如果你感觉自己的确技术高超，你还能够使用MySQL++来创建自己的数据库引擎，这个已经outof my knowledge，牛人可以参照MySQL++API帮助来实现。下面逐一叙述这5种引擎各自的特性：

ISAM

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MySQL能够支持这样的备份应用程序。

 

MyISAM

MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZETABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的MyISAMPack工具。

MyISAM强调了快速读取操作，这可能就是为什么MySQL受到了Web开发如此青睐的主要原因：在Web开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和Internet平台提供商（InternetPresence Provider，IPP）只允许使用MyISAM格式。

 

HEAP

HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MyISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。让我再重复一遍：在你用完表格之后，不要忘记删除表格。

 

InnoDB和BerkleyDB

InnoDB和BerkleyDB（BDB）数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MySQL++API。在使用MySQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和MyISAM引擎慢很多，但是InnoDB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

 

认识完了这么多的引擎，我们也知道了它们应该在什么情况下拿来应急，那么接着我们就要学会如何来更换这些引擎了。

Globle：一种最简单的方法就是更改服务器配置，直接将其设置成你所需要的引擎。这个在win下通过更改服务器安装目录下的mysql.ini中的default-storage-engine项即可，也可以通过运行MySQLServer Instance Configuration Wizard做简单的设置。

PerTable:除了全局的方法外，还有一种更灵活的配置方法，那就是按表来设置引擎，这样我们就可以把那些需要用到事务处理的表设置成InnoDB，其他设置成MyISAM,将性能提升到极致，这是不是很令人兴奋呢？设置方法也比较简单：

1、你可以在CreateTable语句的最后添加扩展语句，如TYPE=MyISAM（或者ENGINE =INNODB）来指定当前标的引擎类型。也可以用Alter语句在建立表个后进行更改。当你不清楚当前数据库中各表的引擎时可以使用SHOWTABLE STATUS FROM DBname来查看。

2、使用随MySQL服务器发布同时提供的MySQL客户端来创建表，在创建时可以选择使用的存储引擎。

不同的引擎选择在不同的业务处理上，性能将有天壤之别！！