MySQL 数据库--存储引擎（MyISAM 与 InnoDB）

前言

存储引擎的实质就是如何实现存储数据，为存储数据建立索引以及查询、更改、删除数据等技术实现的方法，MYSQL数据库支持多种不同的数据引擎，可以根据具体的要求，针对每一个表使用不同的数据引擎，摒弃了很多其他数据库采取的“一个尺码满足一切需求”的解决方案，正是这种灵活性，使得 MySQL 数据库更加受欢迎

一、MySQL存储引擎

MySQL数据库中的组件，负责执行实际的数据I/O操作

MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

最为常用的存储引擎是 MyISAM 和 InnoDB

表——>存储引擎——>数据

1.MyISAM的特点

不支持事务，也不支持外键约束，支持全文索引，数据文件和索引文件时分开保存的
访问速度快，对事务完整性没有要求
适合查询（select)、插入（insert into）为主的应用
MyISAM在磁盘上存储三个文件，文件名和表名都相同，但是扩展名分别为：
.frm文件存储表结构的定义
数据文件的扩展名.MYD(MYData)
索引文件的扩展名时.MYI(MYIndex)
表级锁定形式，数据在更新时锁定整个表
数据库在读写的过程中相互阻塞
会在数据写入的过程中阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
数据单独写入或读取，速度过程较快且占用资源相对少
MyISAM支持的存储格式
静态（固定长度）表（默认的存储格式，静态表中的字段都是非可变字段，这中存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复，缺点是占用的空间通常比动态多）——char的数据类型
动态表（包含可变字段，记录不是固定长度的，这样存储优点是占用空间少，但是频繁的更新、删除记录会产生碎片，需要定期 OPTIMIZETABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难）——varchar的数据类型
压缩包（由myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支）

2.MyISAM适用的生产场景举例

场景一：

公司业务不需要事务的支持，这点是非常明确的，因为 MyISAM 没有事务
一般单方面读取或写入数据比较多的业务，因为 MyISAM 具有读写互相阻塞的特点，数据读写都比较频繁的场景不适合使用
使用读写并发访问相对较低的业务
数据修改相对较少的业务
场景二：

公司的业务不需要事务的支持
一般单方面读取数据比较多的业务，或单方面写入数据比较多的业务，以及读写并发访问相对较低的业务
以读为主的业务，如 www、blog 图片信息库、用户数据库、商品库等业务
对业务一致性要求不是非常高的业务
服务器硬件资源相对比较差

二、InnoDB 存储引擎

1.InnoDB的特点
支持事务，支持4个事务隔离级别
支持外键约束，MySQL 5.5 以前不支持全文索引，5.5 版本以后支持
读写阻塞与事务隔离级别相关
能非常高效的缓存索引和数据
表的主键以簇的方式存储
支持分区、表空间，类似oracle数据库
对硬件资源要求还是比较高的场合
行级锁定，但是全表扫描仍然会是表级锁定
2.InnoDB 适用的生产场景
业务需要事务的支持

行级锁定对高并发有很好的适应能力，但需要确保查询时通过索引来完成

业务数据更新较为频繁的场景，如论坛、微博等

业务数据一致性要求较高，例如银行业务

硬件设备内存较大，利用 InnoDB 较好的缓存能力来提高内存利用率，减少磁盘 IO 的压力

3.企业选择存储引擎的依据
选择哪种存储引擎，应根据企业系统的实际需要，考虑每个存储引擎提供了哪些不同的核心功能及应用的常见
主要依据以下几个方面来决定：

①存储引擎支持的字段和数据类型

所有的引擎都支持通用的数据类型，但不是所有的引擎都支持其他的字段类型
如二进制对象
②锁定类型

不同的存储引擎支持不同级别的锁定
表锁定：MyISAM 支持
行锁定：InnoDB 支持
③索引的支持

建立索引在搜索和恢复数据库中的数据时能够显著提高性能
不同的存储引擎提供不同的制作索引的技术
有些存储引擎根本不支持索引
④事务处理的支持

事务处理功能提供向表中更新和插入信息期间的可靠性
可根据企业业务是否要支持事务选择存储引擎

三、配置合适的存储引擎

1.查看数据库可配置的存储引擎类型

show engines;

2.查看表正在使用的存储引擎类型

方法一

show table status from 库名 where name = '表名'\G;
#加\G能竖向查看，不加则是横向


例：
show table status from school where name='class'\G;

方法二

use 库名;
show create table 表名;


例：
use school;
show create table class\G;

3.配置存储引擎为所选择的类型

方法一: 通过使用 alter table 命令修改

use 库名;
alter table 表名 engine=MyISAM;


例：
use school;
alter table class engine=myisam;
show create table class;

方法二: 通过修改 MySQL 的配置文件 my.cnf，可以指定 default-storage-engine 选项设置默认的存储引擎

quit

vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB

systemctl restart mysql.service
#一定记得重启服务以让修改的配置生效

#注：此方法只针对新建的表，对以前已存在的表没有影响，即不会修改之前表的存储引擎类别

方法三： 通过使用 create table 创建表时用 engine 指定存储引擎，会使用指定的存储引擎

use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;


例：
mysql -uroot -p123123

use school;
create table fanren (name varchar(10),age char(4))engine=myisam;

show create table fanren;

总结

总结
InnoDB 中不保存表的行数，需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可

需要注意的是，当"count( )"语句包含 where 条件时，MyISAM 也需要扫描整个表

对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立组合索引

清空整个表时，InnoDB 是一行一行的删除，效率非常慢；而MyISAM 则会重建表

MyISAM不支持事务，InnoDB支持事务