MySQL数据库引擎(存储引擎)深入分析
存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式。
存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。
Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应引擎,或者编写存储引擎。
MySQ存储引擎主要有几种类型: merge、archive、innodb、MyIASM
MERGE
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,尽管其使用不如其它引擎突出,但是在某些情况下非常有用。说白了,Merge表就是几个相同MyISAM表的聚合器;Merge表中并没有数据,对Merge类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。
对于服务器日志这种信息,一般常用的存储策略是将数据分成很多表,每个名称与特定的时间端相关。例如:可以用12个相同的表来存储服务器日志数据,每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表,这意味着需要编写并更新多表查询,以反映这些表中的信息。与其编写这些可能出现错误的查询,不如将这些表合并起来使用一条查询,之后再删除Merge表,而不影响原来的数据,删除Merge表只是删除Merge表的定义,对内部的表没有任何影响。
ARCHIVE
Archive是归档的意思,在归档之后很多的高级功能就不再支持了,仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前,Archive是不支持索引,但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制,它使用zlib压缩库,在记录被请求时会实时压缩,所以它经常被用来当做仓库使用。
Innodb引擎
目前MySQL AB所发行新版的标准,被包含在所有二进制安装包里。和其他的存储引擎相比,InnoDB引擎的优点是支持兼容ACID的事务(类似于PostGreSQL),以及参数完整性(即对外键的支持)。Oracle公司与2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行,也允许其他想将InnoDB结合到商业软件的团体获得授权。
InnoDB是事务型数据库的首选引擎,InnoDB是默认的MySQL引擎。Innodb引擎提供了对数据库ACID事务的支持,支持行锁定和外键约束,并且实现了SQL标准的四种隔离级别。它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。
当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。
支持事物主要面向OLTP数据库应用。支持行锁,支持外键,并支持类似于Oracle的行锁定读,即默认读取操作不会产生锁。
InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。
MySQL从3.23.34a开始支持InnoDB。从MySQL5.5.8,InnoDB成为MySQL默认的存储引擎。
从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。
InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。
除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需深入
InnoDB存储引擎中支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空,且值必须唯一,且必须为主键。在执行插入操作时,若不指定自动增长列的值,或自动增长列的值为0或NULL,则插入的值为自动增长后的值。若插入任意值,该值在该列中没有出现过,则可以直接插入。
InnoDB存储引擎中,创建的表的结构存储于.frm文件中。数据和索引存储在innodb_data_home和innodb_data_path表空间中。
对于表的数据存储,InnoDB存储引擎采用了聚集的方式,每张表的存储都是按主键顺序进行存放。若没有显示地在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键。支持外键,外键所在的表为子表,外键依赖的表为父表。父表中被主表外键支持的字段必须为主键。当删除、更新浮标的某条信息时,子表也必须有相应的改变。
InnoDB引擎特点
1.支持事务:支持4个事务隔离界别,支持多版本读。
2.行级锁定(更新时一般是锁定当前行):通过索引实现,全表扫描仍然会是表锁,注意间隙锁的影响。
3.读写阻塞与事务隔离级别相关(有多个级别,这就不介绍啦~)。
4.具体非常高效的缓存特性:能缓存索引,也能缓存数据。
5.整个表和主键与Cluster方式存储,组成一颗平衡树。(了解)
6.所有SecondaryIndex都会保存主键信息。(了解)
7.支持分区,表空间,类似oracle数据库。
8.支持外键约束,不支持全文索引(5.5之前),以后的都支持了。
9.和MyISAM引擎比较,InnoDB对硬件资源要求还是比较高的。
- InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中,可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来,甚至在同一个查询中也可以混合
- InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
- InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘文件)。这与MyISAM表不同,比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上。
- InnoDB支持外键完整性约束,存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键。
虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。
第一个重大区别是:InnoDB的数据文件本身就是索引文件。
从 上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
小结:三个重要功能:Supports transactions,row-level locking,and foreign keys
InnoDB引擎适用的生产业务场景
1.需要事务支持(具有较好的事务特性,例银行业务)
2.行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成。
3.数据更新较为频繁的场景,如:BBS(论坛)、SNS(社交平台)、微博等
4.数据一致性要求较高的业务,例如:充值转账,银行卡转账。
5.硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘IO,可以通过一些参数来设置,这个就不细讲啦~~~
6.相比MyISAM引擎,Innodb引擎更消耗资源,速度没有MyISAM引擎快
InnoDB引擎调优精要
1.主键尽可能小,避免给Secondery index带来过大的空间负担。
2.避免全表扫描,因为会使用表锁。
3.尽可能缓存所有的索引和数据,提高响应速度,较少磁盘IO消耗。
4.在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交,有开关可以控制提交方式。
5合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性。
如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作。
6.避免主键更新,因为这会带来大量的数据移动。
MyIASM引擎
MyIASM是MySQL默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。不过和Innodb不同,MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。
MySQL5.5.8之前MyISAM是MySQL默认的存储引擎。
MyISAM不支持事务,不支持外键,支持全文索引,处理速度快。主要面向OLAP数据库应用。
MyISAM存储引擎的表存储成3个文件,文件名与表名相同,扩展名分别为:frm,MYD,MYI。
frm文件:存储表的结构。
myd文件:存储数据。
myi文件:存储存储索引。
MySQL 5.0版本之前,MyISAM默认支持的表大小为4GB,若需要支持大于4GB的MyISAM表时,则需要指定MAX_ROWS和AVG_ROW_LENGTH属性。从MySQL5.0开始,MyISAM默认支持256T的单表数据。
MyISAM的缓冲池只缓存索引文件,而不缓存数据文件。数据文件的缓存交给操作系统完成。这与大多数使用LRU算法缓存数据的数据库都不同。在MySQL5.1.23之前,无论32位系统或64位系统,缓存最大只能设置为4GB,在之后的版本中,64位的系统可以支持大于4GB的索引缓冲区。
MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事务。
MyISAM主要特性有:
- 大文件(达到63位文件长度)在支持大文件的文件系统和操作系统上被支持。
- 当把删除、更新和插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块,以及若下一个块被删除,就扩展到下一块自动完成。
- 每个MyISAM表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16
- NULL被允许在索引的列中,这个值占每个键的0~1个字节
- 可以把数据文件和索引文件放在不同目录(InnoDB是放在一个目录里面的)
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。
1.不支持事务 事务是指逻辑上的一组操作,组成这组操作的各个单元,要么全成功要么全失败。
2.表级锁定 数据更新时锁定整个表:其锁定机制是表级锁定,也就是对表中的一个数据进行操作都会将这个表锁定,其他人不能操作这个表,这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
3.读写互相阻塞 不仅会在写入的时候阻塞读取,MyISAM还会再读取的时候阻塞写入,但读本身并不会阻塞另外的读。
4.只会缓存索引 MyISAM可以通过key_buffer_size的值来提高缓存索引,以大大提高访问性能减少磁盘IO,但是这个缓存区只会缓存索引,而不会缓存数据。
5.读取速度较快 占用资源相对较少 6.不支持外键约束,但只是全文索引 7.MyISAM引擎是MySQL5.5版本之前的默认引擎,是对最初的ISAM引擎优化的产物。
MyISAM引擎适用的生产业务场景
1.不需要事务支持的业务(例如转账就不行,充值也不行)
2.一般为读数据比较多的应用,读写都频繁场景不适合,读多或者写多的都适合。
3.读写并发访问都相对较低的业务(纯读纯写高并发也可以)(锁定机制问题)
4.数据修改相对较少的业务(阻塞问题)
5.以读为主的业务,例如:www.blog,图片信息数据库,用户数据库,商品库等业务
6.对数据一致性要求不是很高的业务。
7.中小型的网站部分业务会用。 小结:单一对数据库的操作都可以示用MyISAM,所谓单一就是尽量纯读,或纯写(insert,update,delete)等。
MyISAM引擎调优精要
1.设置合适的索引(缓存机制)(where、join后面的列建立索引,重复值比较少的建索引等)
2.调整读写优先级,根据实际需求确保重要操作更优先执行,读写的时候可以通过参数设置优先级。
3.启用延迟插入改善大批量写入性能(降低写入频率,尽可能多条数据一次性写入)。
4.尽量顺序操作让insert数据都写入到尾部,较少阻塞。
5.分解大的操作,降低单个操作的阻塞时间,就像操作系统控制cpu分片一样。
6.降低并发数(减少对MySQL访问),某些高并发场景通过应用进行排队队列机制Q队列。
7.对于相对静态(更改不频繁)的数据库数据,充分利用Query Cache(可以通过配置文件配置)或memcached缓存服务可以极大的提高访问频率。
8.MyISAM的Count只有在全表扫描的时候特别高效,带有其他条件的count都需要进行实际的数据访问。
9.可以把主从同步的主库使用innodb,从库使用MyISAM引擎。主库写,从库读可以(不推荐,有些麻烦的地方,市场上有人这么用)。
InnoDB和MyISAM两种引擎的选择
大尺寸的数据集趋向于选择InnoDB引擎,因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短,InnoDB可以利用事务日志进行数据恢复,这会比较快。主键查询在InnoDB引擎下也会相当快,不过需要注意的是如果主键太长也会导致性能问题,关于这个问题我会在下文中讲到。大批的INSERT语句(在每个INSERT语句中写入多行,批量插入)在MyISAM下会快一些,但是UPDATE语句在InnoDB下则会更快一些,尤其是在并发量大的时候。
Index——索引
索引(Index)是帮助MySQL高效获取数据的数据结构。MyIASM和Innodb都使用了树这种数据结构做为索引,关于树我也曾经写过一篇文章树是一种伟大的数据结构,只是自己的理解,有兴趣的朋友可以去阅读。下面我接着讲这两种引擎使用的索引结构,讲到这里,首先应该谈一下B-Tree和B+Tree。
B-Tree和B+Tree
B+Tree是B-Tree的变种,那么我就先讲B-Tree吧,相信大家都知道红黑树,这是我前段时间学《算法》一书时,实现的一颗红黑树,大家可以参考。其实红黑树类似2,3-查找树,这种树既有2叉结点又有3叉结点。B-Tree也与之类似,它的每个结点做多可以有d个分支(叉),d称为B-Tree的度,如下图所示,它的每个结点可以有4个元素,5个分支,于是它的度为5。B-Tree中的元素是有序的,比如图中元素7左边的指针指向的结点中的元素都小于7,而元素7和16之间的指针指向的结点中的元素都处于7和16之间,正是满足这样的关系,才能高效的查找:首先从根节点进行二分查找,找到就返回对应的值,否则就进入相应的区间结点递归的查找,直到找到对应的元素或找到null指针,找到null指针则表示查找失败。这个查找是十分高效的,其时间复杂度为O(logN)(以d为底,当d很大时,树的高度就很低),因为每次检索最多只需要检索树高h个结点。
接下来就该讲B+Tree了,它是B-Tree的变种,如下面两张图所示:
从图中就可以看出,B+Tree的内部结点不存储数据,只存储指针,而叶子结点则只存储数据,不存储指针。并且在其每个叶子节点上增加了一个指向湘…�"/database/DB2/" target="_blank" class="keylink">DB2tK219O92rXjtcTWuNXro6zV4rj208W7r8zhuN/H+Lzkt8POyrXE0NTE3KOsscjI59Tatdq2/tXFzbzW0NKqsunRr7z8zqq00zE4tb00ObXEy/nT0Mr9vt2jrLWx1dK1vTE4uvOjrNa70OjLs9fFvdq147rN1rjV68uz0PKx6cD6vs2/ydLU0ru0ztDUt8POyrW9y/nT0Mr9vt292rXjo6y8q7TzzOG1vcHLx/i85LLp0a/Qp8LKoaM8L3A+DQo8aDMgaWQ9"myisam引擎的索引结构">MyISAM引擎的索引结构
MyISAM引擎的索引结构为B+Tree,其中B+Tree的数据域存储的内容为实际数据的地址,也就是说它的索引和实际的数据是分开的,只不过是用索引指向了实际的数据,这种索引就是所谓的非聚集索引。
Innodb引擎的索引结构
MyISAM引擎的索引结构同样也是B+Tree,但是Innodb的索引文件本身就是数据文件,即B+Tree的数据域存储的就是实际的数据,这种索引就是聚集索引。这个索引的key就是数据表的主键,因此InnoDB表数据文件本身就是主索引。
因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
并且和MyISAM不同,InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址,所以当以辅助索引查找时,会先根据辅助索引找到主键,再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。建议使用自增的字段作为主键,这样B+Tree的每一个结点都会被顺序的填满,而不会频繁的分裂调整,会有效的提升插入数据的效率。