MySQL中MyISAM和InnoDB两种主流存储引擎的特点

一、数据库引擎（Engines）的概念

　　MySQ5.6L的架构图：

　　MySQL的存储引擎全称为(Pluggable Storage Engines)插件式存储引擎。MySQL的所有逻辑概念，包括SQL Interface、Parser、Optimizer、Caches和Buffers等，都需要真正转为物理层的实际数据的，怎么存或者以什么方式存的问题就需要存储引擎来实现。存储引擎对关系型数据库来讲是一个非常重要的概念。在MySQL中常用的文件系统有MyISAM、InnoDB、Federated、Archive、Merge、Memory等等。其中MyISAM和InnoDB最为普遍。

　　简化的MySQL架构图（图片来自《高性能MySQL_第3版》）：

　　下面对MySQL中MyISAM和InnoDB两种引擎做具体对比介绍。

二、安装MySQL

　　在CentOS7上的官方yum库已经将MySQL换成了MariaDB，要安装MySQL可以去MySQL的官网下载安装，安装方式有MySQL官方yum源安装、源码编译安装、通用二进制三种方式。下面以第三种方式(通用二进制)为例演示MySQL的安装步骤。

1.下载通用二进制格式的MySQL程序包并解压

　　官方下载地址是https://dev.mysql.com/downloads/mysql/，目前最新的版本是MySQL8.0.13，但一般用到的是MySQL5系列，这里用MySQL5.7做示例，包名为 mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz 。

　　通用二进制格式的程序包安装起来和yum安装同样方便快捷，直接解压再做初始化配置就能直接使用了，并且和系统耦合性低，很方便做数据迁移。

 　　下载并解压 mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz 至 /usr/local/ 下

~]# tar xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

　　解压后创建软连接：

~]# cd /usr/local
~]# ln -sv mysql-5.7.18-linux-glibc2.5-x86_64 mysql

2.初始化MySQL

添加mysql用户，修改 /usr/local/mysql 目录的用户权限，将属组改为root，属组改为mysql。

~]# useradd mysql -M -s /sbin/nologin
~]# chmown -R root.mysql /usr/local/mysql/*

3.创建数据库目录

　　创建MySQL存放数据的目录,并修改权限。

~]# mkdir -pv /data/mysql
~]# chown mysql.mysql /data/mysql

4.将MySQL的安装路径添加至环境变量中

　　使MySQL的各指令使用相对路径就能使用。

~]# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
~]# . /etc/profile.d/mysql.sh #.后面有空格

5.用mysql_install_db部署mysql

　　mysql_install_db的官方解释为MySQL Database Deployment Utility.（MySQL数据库部署实施工具），常用选项：

~]# mysql_install_db --datadir=/data/mysql --basedir=/usr/local/mysql --insecure --user=mysql

注：在5.7后的版本中在MySQL初始化部署时可采用 mysqld --initialize 命令更快捷的进行初始化， --insecure 表示随机密码。例如：

~]# mysqld --initilize-insecure --datadir=/data/mysql --user=mysql --basedir=/usr/local/mysql

6.创建MySQL配置文件my.cnf

　　my.cnf文件可以放在/etc/、/etc/mysql/、/usr/local/mysql/etc/下，这里建议创建在/usr/local/mysql/etc/下，方便管理。

~]# vim /usr/local/mysql/etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
[mysqld_safe]
log-error=/usr/local/mysql/logs/error.log #需手动创建logs目录和error.log文件，并修改权限为mysql.mysql，目录和文件都需要修改，否则会出错
pid-file=/var/run/mysql/mysql.pid
!includedir /usr/local/mysql/etc/my.cnf.d

7.添加服务管理并启动服务

~]# chkconfig --add mysqld
~]# service start mysqld
Starting MySQL. SUCCESS!

 三、MySQL的MyISAM存储引擎和InnoDB存储引擎详解

1.查看MySQL支持的引擎类别与属性

　　从上表可以看出MySQL5.7支持8种存储引擎，其中InnoDB是默认的存储引擎，从Transactions这栏中可得知只有InnoDB支持事务，所以从某些角度讲，InnoDB是MySQL中功能最完善的存储引擎了，目前主流的存储引擎就是InnoDB和MyISAM。

　　在MySQL自带的库中，默认存储引擎为MyISAM，因为MyISAM通常对读多写少的数据库能发挥很好的性能。另一点需要强调的是：存储引擎是表级别的概念。同一DATABASE的不同表可以是不同类型的存储引擎。但这里强烈建议在同一DATABASE中使用同一存储引擎。

　　查看库中表的存储引擎类型可用 SHOW TABLE STATUS 命令，如：

mysql> USE mysql;
mysql> SHOW TABLE STATUS WHERE Engine=InnoDB\G; #过滤名为mysql库中的存储引擎为InnoDB的表。

　　在结果中可看出MySQL已经渐渐的由MyISAM引擎转向InnoDB了，下面再具体介绍两个主流存储引擎的区别。

2.InnoDB介绍

　　InnoDB的数据存储在表空间（table space）中，相较于其他存储引擎有所不同的是，其他存储引擎将数据存储在具体的文件系统中，如MyISAM的每个表都有一个属于自己的文件。但InnoDB的存储方式是基于黑盒模式的，就是说InnoDB将所有的数据都存储在一个空间中，任何库的任何存储引擎类型为InnoDB的表数据和索引全都在一个盒子中，这个盒子就称为表空间（table space），它内部有自己的文件系统。当表越来越多，占用空间越来越大时，会生成lbdata2、lbdata3、依次增加。在MySQL的数据目录下可以看到MySQL的默认表空间：

~]# ll -h /data/mysql/
total 121M
-rw-r-----. 1 mysql mysql   56 Oct 24 08:26 auto.cnf
-rw-r-----. 1 mysql mysql  419 Oct 24 08:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql  12M Oct 24 08:38 ibdata1 #这个文件就是默认的表空间文件，任何库的任何存储引擎类型为InnoDB的表都存储在这一文件中。

　　也可以在MySQL中设置将使用InnoDB引擎的表存储在自己单独的表空间（包括数据文件和索引信息）中，存储路径在所属库的目录下，而不是在MySQL数据目录下。代码为 innodb_file_per_table=ON 。下面演示说明，设置路径如下：

~]# vim /usr/local/mysql/etc/my.cnf.d/service.cnf
[mysqld]
innodb_file_per_table=ON

　　重启后在MySQL中查看：

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

　　创建一个新表：

mysql> CREATE TABLE tbl2(id INT,Name CHAR(30));

　　查看默认库（mysql库）中的文件：

　　tbl2.ibd就是表tbl2单独的表空间，tbl2.frm为表结构文件。若未启用 innodb_file_per_table=ON 则只有tbl2.frm文件。

 3.InnoDB的特性：

1.InnoDB是一种事务型存储引擎，适用于对事务要求较高的场景中，但较适用于处理大量的短期事务(short-lived)，若要处理大量长事务，可以选择Oracle。

2.InnoDB基于MVCC（Mutli Version Concurrency Control）多版本并发控制，支持高并发。

PS：所谓MVCC是指数据库管理系统常用的并发控制方法，用于提供对数据库的并发访问，并以编程语言实现事务内存。如果没有并发控制，如果用户A在用户B写入数据库的同时从数据库中读取数据，则用户A可能会看到写了一半或不完整的数据，也可能无法查看数据。这里所描述的多版本其实是指将原表做多个快照，在不同用户的不同访问时间点进行快照，因为每一个连接都是基于快照进行的，所以用户之间的访问互相不受影响，虽然这样的方式不能完全避免用户A读到和用户B修改后的数据，但能大大提高并发性。

3.支持四个隔离级别，默认为REPEATABLE-READ。

4.支持间隙锁防止幻读。

5.支持使用聚集索引，将数据和索引存储在同一处，找到索引便找到数据，从而提高性能，但每个表上只能有一个聚集索引。

6.支持自适应hash索引。

7.优秀的自动崩溃恢复特性。

4.InnoDB关键字

数据存储：表空间；
并发：MVCC，间隙锁，行级锁；
索引：聚集索引、辅助索引；
性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；
备份：支持热备；

5.MyISAM存储引擎

　　在MySQL较老的版本（5.1以前），MyISAM是默认的存储引擎。MyISAM存储引擎不支持事务和行级锁，并且崩溃后无法安全恢复。尽管MyISAM有这么多短板缺点，但不代表他一无是处，对于制度的数据，或者表比较小、可忍受的修复操作，依然可以使用MyISAM。MyISAM存储引擎设计简单，数据已紧密格式存储，所以在某些场景下还是很好用的。但MyISAM最典型的的性能问题还是表级锁的问题。MyISAM存储引擎出常用在只读或读多写少、表较小的场景。

　　MyISAM的特性：

1.支持加锁与并发访问控制。MyISAM的锁是表级别的，就是说MyISAM可对整张表加锁。读取时会对需要读到的所有表家共享锁，写入时则对表加排它锁。

2.支持修复功能。对于MyISAM表，MySQL可以手工或者自动执行检查和修复，但这里的修复和事务恢复以及崩溃恢复是不同的概念。

3.支持全文索引，非聚集索引。

4.表压缩功能。如果表在创建并导入数据后，不会再进行修改，那么这样的表就能使用MyISAM表压缩功能。这样可以减少磁盘空间占用以及减少磁盘I/O，从而提升查询性能。

6.MyISAM表创建示例

　　进入MySQL创建存储引擎为MyISAM的表：

mysql> CREATE TABLE tbl3(id INT,Name CHAR(30)) Engine 'MyISAM'; 

　　在数据库文件中可以看到用MyISAM创建的表的结构如下：

tbl_name.frm：表格式定义；
tbl_name.MYD：数据文件；
tbl_name.MYI：索引文件