目录

MySQL之存储引擎

1.MySQL存储引擎介绍

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储。遗憾的是，其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能，要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL，我们仅需要修改我们使用的存储引擎就可以了

2.MySQL结构

MySQL架构总共四层，在上图中以虚线作为划分。 　　首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。 　　第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。　　第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。　　第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上。

3.MySQL存储引擎分类

几种常用引擎介绍

1. MyISAM引擎

MyISAM引擎特点：

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引擎。主库写，从库读可以(不推荐，有些麻烦的地方，市场上有人这么用)。　

2. InnoDB引擎

InnoDB引擎        介绍：InnoDB引擎是MySQL数据库的另一个重要的存储引擎，正称为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。和其他的存储引擎相比，InnoDB引擎的优点是支持兼容ACID的事务(类似于PostGreSQL)，以及参数完整性(即对外键的支持)。Oracle公司与2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行，也允许其他想将InnoDB结合到商业软件的团体获得授权。

InnoDB引擎特点：

1.支持事务：支持4个事务隔离界别，支持多版本读。        2.行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。        3.读写阻塞与事务隔离级别相关(有多个级别，这就不介绍啦~)。        4.具体非常高效的缓存特性：能缓存索引，也能缓存数据。        5.整个表和主键与Cluster方式存储，组成一颗平衡树。(了解)        6.所有SecondaryIndex都会保存主键信息。(了解)        7.支持分区，表空间，类似oracle数据库。        8.支持外键约束，不支持全文索引(5.5之前)，以后的都支持了。        9.和MyISAM引擎比较，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.避免主键更新，因为这会带来大量的数据移动。

InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 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 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。

3.其他引擎的介绍

#NDB 存储引擎2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。#Memory 存储引擎正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。#Infobright 存储引擎第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。#NTSE 存储引擎网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。#BLACKHOLE黑洞存储引擎，可以应用于主备复制中的分发主库。MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。

4.存储引擎的使用

1、创建表时指定引擎

create table innodb_t2(id int)engine=innodb;

2、在配置文件中指定默认的存储引擎

linux:vim /etc/my.cnf   windows:my.ini文件[mysqld]default-storage-engine=INNODB  #配置默认引擎，现在用的mysql默认基本都是InnoDB，所以其实都可以不用配置了innodb_file_per_table=1  #表示独立表空间存储，可以不写

3、不同引擎在创建表的时候生成文件的不同

创建四个表，分别使用innodb，myisam，memory，blackhole存储引擎，进行插入数据测试#下面是使用四个不同的引擎来创建的表create table t1(id int)engine=innodb;create table t2(id int)engine=myisam;create table t3(id int)engine=memory;create table t4(id int)engine=blackhole;

4、通过四种引擎来创建的表，其生成的文件来看一下区别如下图：

1.db.opt文件：用来记录该库的默认字符集编码和字符集排序规则用的。也就是说如果你创建数据库指定默认字符集和排序规则，那么后续创建的表如果没有指定字符集和排序规则，那么该新建的表将采用db.opt文件中指定的属性。2.后缀名为.frm的文件：这个文件主要是用来描述数据表结构(id,name字段等)和字段长度等信息3.后缀名为.ibd的文件：这个文件主要储存的是采用独立表储存模式时储存数据库的数据信息和索引信息；4.后缀名为.MYD（MYData）的文件：从名字可以看出，这个是存储数据库数据信息的文件，主要是存储采用独立表储存模式时存储的数据信息；5.后缀名为.MYI的文件：这个文件主要储存的是数据库的索引信息；6.ibdata1文件:主要作用也是储存数据信息和索引信息，这个文件在mysql安装目录的data文件夹下。   从上面可以看出，.ibd储存的是数据信息和索引信息，ibdata1文件也是存储数据信息和索引信息，.MYD和.MYI也是分别储存数据信息和索引信息，那他们之间有什么区别呢？    主要区别是再于数据库的存储引擎不一样，如果储存引擎采用的是MyISAM，则生成的数据文件为表名.frm、表名.MYD、表名的MYI;而储存引擎如果是innoDB，开启了innodb_file_per_table＝1,也就是采用独立储存的模式，生成的文件是表名.frm、表名.ibd，如果采用共存储模式的，数据信息和索引信息都存储在ibdata1(在里面进行分类，从外面看是一个文件)中;    在进行数据恢复的时候，如果用的是MYISAM数据引擎，那么数据很好恢复，只要将相应.frm, .MYD, .MYI文件拷贝过去即可。但是如果是innodb的话，则每一个数据表都是一个单独的文件，只将相应的.frm和.ibd文件拷贝过去是不够的，必须在你的ibd文件的tablespace id和ibdata1文件中的元信息的tablespace id一致才可以。msyql人家设定的规则就是这样存储表的，使用人家的系统，就要理解人家的规则。

5.总结

　　在Oracle 和SQL Server等所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。　　Oracle中不存在引擎的概念，数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。