MySQL存储引擎

1、存储引擎概述

数据库中的数据使用各种不同的技术存储在文件（或者内存）中；每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供不同的功能和能力；这些不同的技术以及配套的功能在MySQL中称为存储引擎。

存储引擎是数据库将数据存储在文件系统中的存储方式或者存储格式。

2、MySQL常用存储引擎

MySQL常用的存储引擎：

  • MyISAM
  • InnoDB

MySQL结构：

MySQL结构.png

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

3、MyISAM存储引擎

MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL也导入了InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

3.1 MyISAM存储引擎特点

  • 访问速度快
  • MyISAM不支持事务，也不支持外键
  • 对事务完整性没有要求
  • MyISAM在磁盘上存储成三个文件：
    • 扩展名为.frm文件存储表的定义
    • 数据文件的扩展名为.MYD
    • 索引文件的扩展名是.MYI
  • 表级锁定形式，数据在更新时锁定整个表
  • 数据库在读写过程中相互阻塞，在数据写入的过程阻塞用户数据的读取，也会在数据读取的过程中阻塞用户的数据写入
  • 数据单独写入或读取，速度过程较快且占用资源相对少
  • MyISAM支持多种存储格式：
    • 静态表
    • 动态表
    • 压缩表

3.2 MyIAM支持的存储格式

静态表：

  • 静态表是默认的存储格式。静态表中的字段都是非可变字段，每个记录长度都是固定的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度补足空格，但是在访问的时候并不会得到这些空格，这些空格在返回给程序之前已经去掉。

动态表：

  • 动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行相关语句或命令来改善性能，并且出现故障的时候恢复相对比较困难。

压缩表：

  • 压缩表由特定的工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

3.3 MyISAM适用的生产场景

  • 公司业务不需要事务的支持
  • 对数据业务一致性要求不是非常高的业务
  • MyISAM存储引擎数据读写都比较频繁场景不适合
  • 单方面读取或写入数据比较多的业务
  • 使用读写并发访问相对较低的业务
  • 数据修改相对较少的业务
  • 服务器硬件资源相对比较差

4、InnoDB存储引擎

InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，类似于PostgreSQL。目前InnoDB采用双轨制授权，一是GPL授权，另一是专有软件授权。

4.1 InnoDB存储引擎特点

  • 支持4个事务隔离级别
  • 读写阻塞与事务隔离级别相关
  • 行级锁定，但是全表扫描仍然会是表级锁定
  • 能非常高效的缓存索引和数据
  • 表与主键以簇的方式存储
  • 支持分区、表空间，类似oracle数据库
    • 分区：就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成
    • 分表：就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表名，然后使用
    • 表空间是数据库的逻辑划分；所有的数据库对象都存放在指定的表空间中；但主要存放的是表， 所以称作表空间
      • 一个数据库可以包含多个表空间，一个表空间只能属于一个数据库
      • 一个表空间包含多个数据文件，一个数据文件只能属于一个表空间
  • 支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
  • 对硬件资源要求比较高

4.2 InnoDB适用的生产场景

  • 业务需要事务的支持
  • 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
  • 业务数据更新较为频繁的场景（如：论坛，微博等）
  • 业务数据一致性要求较高（如：银行业务等）
  • 硬件设备内存较大，利用lnnodb较好的缓存能力来提高内存利用率，减少磁盘的IO压力

5、MyISAM和InnoDB的区别

MyISAM存储引擎相对简单所以在效率上要优于InnoDB，如果系统读多，写少，对原子性要求低，那么MyISAM最好的选择，且MyISAM恢复速度快，可直接用备份覆盖恢复。如果系统读少，写多的时候，尤其是并发写入高的时候InnoDB就是首选了。

1、存储结构

  • MyISAM：每个MyISAM在磁盘上存储成三个文件；第一个文件的名字以表的名字开始，扩展名指出文件类型：.frm文件存储表定义，数据文件的扩展名为.MYD，索引文件的扩展名是.MYI。
  • InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2、存储空间

  • MyISAM：可被压缩，存储空间较小；支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
  • InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、可移植性、备份及恢复

  • MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便；在备份和恢复时可单独针对某个表进行操作。
  • InnoDB：免费的方案可以是拷贝数据文件、备份binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

4、事务支持

  • MyISAM：强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持。
  • InnoDB：提供事务支持，外键等高级数据库功能；具有事务的提交(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全型表。

5、AUTO_INCREMENT（自动增长）

  • MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须有索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
  • InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须有索引，如果是组合索引也必须是组合索引的第一列。

6、表锁差异

  • MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
  • InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能；但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

7、全文索引

  • MyISAM：支持 FULLTEXT类型的全文索引
  • InnoDB：5.5之前不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8、表主键

  • MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
  • InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

9、表的具体行数

  • MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出该值。
  • InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

10、CURD操作

  • MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
  • InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令（注意误操作）。

11、外键

  • MyISAM：不支持
  • InnoDB：支持

6、企业选择存储引擎的依据

存储引擎的选择需要考虑每个存储引擎提供的核心功能及应用场景。

1、支持的字段和数据类型

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

2、锁定类型：不同的存储引擎支持不同级别的锁定

  • 行锁定
  • 表锁定

3、索引的支持

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

4、事务处理的支持

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

通过MyISAM和InnoDB的对比，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

7、MySQL修改存储引擎

//查看当前数据库支持的所有存储引擎
SHOW ENGINES;

//方法1：alter table 修改
ALTER TABLE table_name ENGINE = 引擎；

//方法2：create table 创建表时指定存储引擎
CREATE TABLE 表名(字段) ENGINE = 引擎

//方法3：修改配置文件，指定默认存储引擎并重启服务
default-storage-engine = InnoDB