MySQL数据库存储引擎
1.MySQL引擎概述
1.1.什么是存储引擎？
数据库表里的数据存储在数据库里及磁盘上，它跟视频格式及存储磁盘文件系统格式的特征类似，也有很多存储方式。
但是，对于用户和应用程序来说，同样一张表的数据，无论采用什么引擎来存储，用户看到的数据都是一样的。对于不同的引擎存取，引擎功能、占用的空间大小、读取性能等可能都有区别。
存储引擎是MySQL数据库用来处理不同表类型的SQL操作的组件。
MySQL早期最常用的存储引擎为：MyISAM和InnoDB。目前，InnoDB是最常用的存储引擎，也是MySQL5.6默认的存储引擎。
1.2.MySQL存储引擎的架构
MySQL的存储引擎是MySQL数据库的重要组成部分。MySQL的每种存储引擎在MySQL里都是通过插件的方式使用的，可以轻易地从MySQL中进行加载和卸载，MySQL中可以同时支持多种存储引擎。
MySQL体系结构的组成部分：
1、连接池部分。
2、数据库管理部分。
3、SQL接口、查询分析器、优化器、缓存缓冲。
4、存储引擎部分。
5、数据库数据文件和各种日志文件。
6、文件系统磁盘。
2.查看MySQL支持的存储引擎
可以在MySQL中使用显示引擎的命令来得到一个可用引擎的列表：
select version();
show engines;
命令的结果显示了数据库可用引擎的全部名单，以及在当前的数据库中是否支持这些引擎，其中前四列比较重要，第一列是引擎名字，第二列是当前数据库是否支持，第三列是描述，第四列表示是否支持事务。
3.MySQL5.6支持的存储引擎
| 存储引擎 | 说明（带*的为重点） |
|---|---|
| InnoDB | InnoDB是MySQL5.6默认的存储引擎，InnoDB支持事务，具有提交、回滚的功能，并且可以通过崩溃恢复能力来保护用户的数据，读写数据是行级锁定，可提升多用户并发访问的能力，InnoDB以集群的索引方式存储用户数据，基于主键方式查询可提高I/O性能，InnoDB也支持外键，使得数据更完整、更安全。* |
| MyISAM | MyISAM是MySQL5.5.5以前默认的存储引擎，曾经用的很多，现在用的少了，MyISAM仅支持表级锁，读写性能都很有限。可用于只读或者绝大多数以读为主的业务场景。 |
| Memory | Memory以内存的方式存储所有数据，访问速度很快，不过其使用场景也是越来越少了。InnoDB的Buffer pool内存也可以缓存绝大多数的数据了。 |
| CSV | CSV这个引擎所对应的数据表格实际上是带有逗号分隔值的文本文件。CSV表格允许您以CSV格式导入或者转储数据，以便于读取和写入相同格式的脚本，与应用程序进行数据交换。由于CSV表是没有索引的，因此通常应在正常操作期间将数据保存在InnoDB表中，并且只能在导入或导出阶段使用CSV表。 |
| Archive | 这些紧凑、无索引的引擎表旨在存储和检索大量参考的历史、归档或安全审核信息。 |
| Blackhole | Blackhole存储引擎接受但不存储数据，类似于Unix/dev/null设备。查询总是会返回一个空集。这些表可用于将DML语句发送到从属服务器的复制配置，但是主服务器不保留其自己的数据副本。 |
| Merge | 使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将其作为一个对象引用。merge适用于数据仓库等VLDB环境。 |
| Federated | Federated可通过链接单独的MySQL服务器以从许多物理服务器创建一个逻辑数据库。其非常适合于分布式或数据集环境。 |
| Example | 该引擎作为MySQL源代码中的一个例子，说明了如何开始编写新的存储引擎。这主要是开发商感兴趣的。存储引擎是一个什么都不做的“stub”。您可以使用此引擎创建表，但不能存储数据或从中检索数据。 |
4.MySQL常用存储引擎特性对比
| 特性 | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| 存储限制 | 256TB | RAM | 64TB | NONE | 384EB |
| 事务 | NO | NO | YES | NO | NO |
| 锁粒度 | TABLE | TABLE | ROW | ROW | ROW |
| B-tree索引 | YES | YES | YES | NO | NO |
| T-tree索引 | NO | NO | NO | NO | YES |
| Hash索引 | NO | YES | NO | NO | YES |
| Full-text search索引 | YES | NO | YES | NO | NO |
| Clustered索引 | NO | NO | YES | NO | NO |
| 数据缓存 | NO | N/A | YES | NO | YES |
| 索引缓存 | YES | N/A | YES | NO | YES |
| 压缩数据 | YES | NO | YES | YES | NO |
| 加密数据 | YES | YES | YES | YES | YES |
| 集群数据库支持 | NO | NO | NO | NO | YES |
| 主从复制支持 | YES | YES | YES | YES | YES |
| 外键支持 | NO | NO | YES | NO | NO |
5.设置与更改MySQL的引擎
5.1.设置表的引擎
如果建表的时候不指定引擎，那么表的引擎就会和数据库的默认配置一致。
指定表的引擎建立表，建立一个学生表：
create tables `student` (
`Sno` int(10) not null comment '学号',
`Sname` varchar(16) not null comment '姓名',
`Ssex` char(2) not null comment '性别',
`Sage` varchar(16) default null,
`Sdept` varchar(16) default null comment '学生所在系别',
key `ind_sage` (`Sage`),
key `ind_sno` (`Sno`)
) engine=myisam default charset=utf8 # 最后一行括号外，指定引擎。
5.2.更改表的引擎
一般来说，更改MySQL引擎的需求并不多见，但偶尔也会有。更改表的引擎的几种修改方法。
5.2.1.利用SQL命令语句修改引擎
alter table oldboy engine = innodb;
alter table oldboy engine = myisam;
更改引擎：
show create table test\G
alter table test engine = myisam;
show create table test\G
使用此方法若要批量修改，则需要通过开发脚本实现，与分表分库脚本差不多。
5.2.2.使用sed对备份的SQL文件进行批量转换
使用sed对备份内容进行引擎转换：
nohup sed -e 's/MyISAM/InnoDB/g' oldboy.sql > oldboy_1.sql &
5.2.3.mysql_convert_table_format命令修改
mysql_convert_table_format --user=root --password=oldboy123 --socket=/data/3306/mysql.sock --engine=myisam oldboy test
该命令需要一些依赖包，安装方法为：
yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes
6.MyISAM引擎
6.1.什么是MyISAM引擎
MyISAM引擎是MySQL关系型数据库管理系统的默认存储引擎（MySQL5.5.5以前）。这种MySQL表存储结构可从旧的ISAM代码中扩展出许多有用的功能。在新版本的MySQL中，InnoDB引擎由于支持事务、外键等，有利于数据的一致性，以及其能支持更高的多用户并发性等优点，InnoDB已经取代了曾经常用的MyISAM引擎，不过由于数据库中的MySQL库的大部分表主要用于读取，因此，MyISAM引擎依然在使用。
6.2.MyISAM引擎的存储方式
每一个MyISAM引擎的表都对应于硬盘上的三个文件。这三个文件虽然具有一样的文件名，但是其不同的扩展名指示了其不同的类型用途：“.frm”文件用于保存表的定义，该文件并不是MyISAM引擎的一部分，而是服务器的一部分；“.MYD”用于保存表的数据；“.MYI”则是表的索引文件。“.MYD”和.MYI是MyISAM的关键点。
MySQL数据库系统的表大多数都使用MyISAM引擎。
6.3.MyISAM引擎的主要特点
| 特性 | 支持情况 | 说明 |
|---|---|---|
| 存储限制 | 256TB | |
| 事务支持 | NO | |
| 锁表粒度 | TABLE | 即数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小，但是同时也大大降低了其并发性能 |
| 全文索引 | YES | |
| 数据缓存 | NO | 不会缓存数据 |
| 索引缓存 | YES | MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能，减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据 |
| 外键支持 | NO | 不支持外键 |
| 资源占用 | 少 | 因为功能不多，且管理粒度较粗，因此，MyISAM消耗系统资源比InnoDB少很多 |
| 读写是否阻塞 | YES | 不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读 |
| 是否默认 | NO | MyISAM是MySQL5.5.5之前默认的存储引擎，因为性能问题，在MySQL后期版本中被取代 |
6.4.MyISAM引擎适用的生产业务场景
MyISAM引擎可以使用的生产业务场景。
1、不需要事务支持并且对数据一致性要求不高的业务。
2、一般适用于读请求较多的应用，读写都频繁的场景不适合。
3、读写并发访问相对较低的业务。
4、数据修改相对较少的业务（阻塞问题）。
5、硬件资源比较差的服务器。
6、使用读写分离的MySQL从库可以使用MyISAM。
当下99%的企业业务场景，都不需要使用MyISAM了，而是选择更有优势的InnoDB。
7.InnoDB引擎
7.1.什么是InnoDB引擎
InnoDB引擎是当下MySQL数据库最重要的存储引擎，其正在成为目前MySQL AB所发行新版的标准，被包含在所有的安装包里。与其他的存储引擎相比，InnoDB引擎的优点是更新数据行级锁定、支持ACID的事务、支持外键，它的设计目标是面向在线事务处理的应用，目前绝大多数互联网公司都在使用InnoDB引擎，该引擎替代了其他的引擎。MySQL5.6版本的默认引擎已变为InnoDB引擎。
7.2.InnoDB引擎的存储方式
InnoDB存储引擎将数据存放在一个像黑盒一样的逻辑表空间中，这个表空间分为共享表空间和独立表空间，从MySQL5.6开始，即默认支持将InnoDB引擎的表数据单独存放到各自独立的ibd文件中（独立表空间）。
7.3.InnoDB引擎的主要特点
| 特性 | 支持情况 | 说明 |
|---|---|---|
| 存储限制 | 64TB | 存储限制有些小 |
| 事务 | YES | 支持4个事务隔离级别，支持多版本读 |
| 锁粒度 | ROW | 更新数据仅锁定行 |
| B-tree索引 | YES | |
| T-tree索引 | NO | |
| Hash索引 | NO | |
| Full-text search索引 | YES | 从5.5开始支持全文索引 |
| Clustered索引 | YES | 数据和主键以Cluster方式进行存储，组成一颗平衡树 |
| 数据缓存 | YES | 高效缓存特性：能缓存索引，也能缓存数据 |
| 索引缓存 | YES | 高效缓存特性：能缓存索引，也能缓存数据 |
| 压缩数据 | YES | 可以压缩数据 |
| 加密数据 | YES | 可以加密数据 |
| 集群数据库支持 | NO | 不支持MySQL集群，NDB是集群的引擎 |
| 主从复制支持 | YES | 支持主从复制集群 |
| 资源占用 | 高 | 由于其功能和粒度都更强，因此对硬件的要求很高 |
| 分区支持 | YES | 支持分区，可以提升扩展性和性能 |
| 表空间支持 | YES | 支持共享和独立表空间，有利于管理和提升性能 |
7.4.InnoDB引擎适用的生产业务场景
1、需要事务支持的业务（具有很好的事务特性）。
2、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引来完成的。
3、数据读写及更新都较为频繁的场景，如BBS、SNS、微博、微信等。
4、数据一致性要求较高的业务，例如：充值转账、银行卡转账等。
5、硬件设备资源较好，特别是内存要大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO。
7.5.InnoDB引擎相关参数介绍
| InnoDB引擎的重要参数 | 说明 |
|---|---|
| innodb_buffer_pool_size = 2048M | InnoDB使用一个缓冲池来保存索引和原始数据，缓冲池设置的越大，理论上在存取表里面的数据时所需要的磁盘I/O就越少。官方建议将InnoDB的Buffer Pool值配置为物理内存的50%~80% |
| innodb_data_file_path = ibdata1:12M:autoextend | InnoDB数据文件的路径，默认为12MB大小ibdata1的单独文件，默认以64MB为单位自增（autoextend） |
| innodb_additional_mem_pool_size = 16M | 该参数用来设置InnoDB存储的数据目录信息和其他内部数据结构的内存池大小。应用程序里的表越多，就需要在其中分配越多的内存。对于一个相对稳定的应用来说，这个参数的大小也是相对稳定的，没有必要预留非常大的值。如果InnoDB将开始从操作系统分配内存，并且向MySQL错误日志中记录警告信息。默认为1MB，当发现错误日志中已经有相关的警告信息时，就应该适当地增加该参数的大小 |
| innodb_file_io_threads = 4 | InnoDB中的文件I/O线程。通常设置为4，如果是Windows则可以设置更大的值以提高磁盘I/O |
| innodb_thread_concurrency = 8 | 你的服务器中有几个CPU就设置为几，建议使用默认设置，一般设置为8 |
| innodb_flush_log_at_trx_commit = 2 | 若设置为0，就相当于innodb_log_buffer_size队列满后再统一存储，默认值为1，该值也是最安全的设置 |
| innodb_log_buffer_size = 16M | 默认为1MB，通常设置为8~16MB就足够了 |
| innodb_log_file_size = 128M | 确定日志文件的大小，更大的设置可以提高性能，但也会增加数据库恢复的时间 |
| innodb_log_files_in_group = 3 | 为提高性能，MySQL可以以循环的方式将日志文件写到多个文件。推荐设置为3 |
| innodb_max_dirty_pages_pct = 90 | InnoDB主线程刷新缓存池中的数据 |
| innodb_lock_wait_timeout = 120 | InnoDB事务被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。默认值为50秒 |
| innodb_file_per_table = 1 | InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。值为0表示关闭，值为1表示开启 |
| innodb_data_home_dir = /data/xxx | InnoDB数据的存放路径 |
| innodb_log_group_home_dir = /data/xxx | 日志分组的目录路径 |
7.6.InnoDB引擎调优的基本方法
1、主键应尽可能小，以避免对Secondary index带来过大的空间负担。
2、建立有效索引避免全表扫描，因为会使用表锁。
3、尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗。
4、在进行大批量小插入的时候，应尽量自己控制事务而不要使用autocommit自动提交。若有开关则可以控制提交方式。
5、合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
6、应避免主键更新，因为这会带来大量的数据移动。
8.Memory存储引擎
Memory就是内存的意思，因此Memory存储引擎（又称为heap引擎）的数据存储是放在内存（注意：由max_heap_table_size参数控制内存占用大小，默认为16MB。）中的，因此存取速度特别快，但是如果数据库宕机或重启，那么所有的数据就都会丢失，因此它比较适合用于存放临时表的数据，例如，discuz论坛数据库中的统计在线人数的session表采用的就是Memory引擎。Memory存储引擎默认采用的是Hash索引，而不像其他引擎（MyISAM和InnoDB）默认的是B-tree索引。
Memory存储引擎在使用上也有一些限制，例如，仅支持表锁，不支持TEXT和B1OB数据类型，还有当存储变长字段（varchar）时按照定长字段（char）来进行的，这也会浪费一些内存空间。Memory存储引擎在企业工作中应用的不是很多。
9.ARCHIVE存储引擎
ARCHIVE的中文意思是归档，因此ARCHIVE适用于存放大量归档历史数据（可查询但不能删除）的保存。
ARCHIVE引擎仅支持select、insert操作；MySQL5.1以后开始支持索引等操作。
ARCHIVE引擎使用zlib无损数据压缩算法，压缩比可达10：1，可大量节省磁盘空间，设计ARCHIVE引擎的目标是提供高速的插入和压缩等功能。
建立两个不同存储引擎的表，测试ARCHIVE存储引擎的表其占用空间的情况：
首先建立一个MyISAM存储引擎的表，插入数据：
create table t1 engine=myisam as select * from information_schema.columns;
show table status like 't1'\G
再建立一个ARCHIVE引擎表，插入数据：
create table t2 engine=archive as select * from information_schema.columns;
数据文件形式：
.ARZ是数据压缩文件，.frm是表结构定义文件
10.NDB存储引擎
NDB存储引擎是一个集群存储引擎，类似于oracle的RAC集群，但它是share nothing的架构，因此NDB能够提供更高级别的高可用和可扩展性。NDB的特点是数据全部存放在内存中，因此，通过主键进行查找的速度非常快。
关于NDB，有一个问题需要注意，它的连接（join）操作是在MySQL数据库层完成的，而不是在存储引擎层完成的，这就意味着，复杂的Join操作需要巨大的网络开销，查询速度会很慢，在中小型企业中，NDB引擎的使用频率极少。
