MySQL存储引擎

一、MySQL存储引擎概述

把连接管理，查询缓存，语法解析，查询优化这些并不涉及真实数据存储的功能划分为 MySQL server的功能，而真实存取数据的功能划分为存储引擎的功能。所以MySQL server完成了查询优化后，只需按照生产的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端。

MySQL提出了存储引擎的概念。简而言之存储引擎就是指文件系统中存储表已经表数据的类型。其实存储引擎以前叫做表处理器，后来改名为存储引擎。

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能 。

MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。

查看存储引擎

 

show engines;

 

列（字段）说明：

• Engine：表示存储引擎名称。
• Support：表示是否支持该引擎：YES 支持，NO 不支持。DEFAULT 表示默认支持的存储引擎。
• Comment：表示对存储引擎的描述。
• Transactions：表示存储引擎是否支持事务：YES 支持，NO 不支持。
• XA：表示存储引擎是否支持分布式事务：YES 支持，NO 不支持。
• Savepoints：表示该存储引擎是否支持部分事务回滚：YES 支持，NO 不支持。

设置存储引擎

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

 

二、如何选择合适的存储引擎

①InnoDB：用于事务处理应用程序，支持外键。对事务的完整性有较高要求，除了查询和插入操作外，还有很多更新和删除操作。有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚。实现了四个标准的隔离级别，默认级别是可重复读。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

• MySQL 3.23.34 就有了InnoDB 存储引擎，在 5.5 之后，默认采用了InnoDB 引擎。
• InnoDB 是 MySQL 的 默认事务型引擎，它用来处理大量的短期的事务。可以确保事务的完整提交（Commit）和回滚（Rollback）
• InnoDB 是 行级锁（操作时只锁某一行，不对其它行有影响，适合高并发操作）
• 数据文件结构：
  • 表名.frm 存储表结构（8.0 之后，合并在 表名.ibd 中）
  • 表名.ibd 存储数据和索引（8.0 之后，存储表结构，数据和索引）
• InnoDB 就是 为处理巨大数据量的最大性能而设计的。
• 相比 MyISAM 存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引
• InnoDB 存储引擎 不仅缓存索引还要缓存真实数据，对内存要求较高。
• 在 InnoDB 存储引擎中：（聚簇索引）索引既数据。
• 在开发中，除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。

②MyISAM：如果应用是以查询和插入操作为主，只有极少更新和删除操作，并且对事务的完整性没有要求，没有并发写操作。

• 5.5之前默认的存储引擎
• MyISAM 提供了大量的特性，包括全文索引，压缩，空间函数（gis）等...
• 但MyISAM 不支持事务，行级锁，外键，还有一个最致命的缺陷就是 崩溃后无法安全恢复。
• 在对事务完整性没有要求或以 select ，insert 为主的应用（表），访问速度是优于 InnoDB 的
• 数据文件结构：
  • 表名.frm 存储表结构
  • 表名.MYD 存储数据（MYData）
  • 表名.MYI 存储索引（MYIndex）
• 应用场景：只读应用（表）或者以读为主的业务（表）

③MEMORY：数据存在内存中，默认使用HASH索引，访问速度快，服务关闭数据就会丢失。通常用于更新不太频繁的小表，可以快速得到访问结果。缺陷是对表的大小有限制，表太大无法缓存在内存中，其次要确保表的数据可以恢复。

• Memory同时 支持哈希（HASH）索引 和 B+树索引。

  • 哈希索引相等的比较快，但是对于范围的比较慢很多。
  • 默认使用哈希（HASH）索引，其速度要比较使用B型树（Btree）索引快。

• Memory表至少比MyISAM表要 快一个数量级。

• Memory 表的大小是受限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size。

  • max_rows 可以在创建表时指定；
  • max_heap_table_size 默认为：16MB ；可以按需求进行扩大

• 数据文件结构：

  • 表名.frm 只存储表结构（存储在文件系统中的）
  • 表（底层）数据都是存储在内存中

• 缺点：内存（表）数据易丢失，生命周期短。谨慎使用。

• Memory存储引擎的场景：

         • 目标数据比较小，而且非常 频繁的进行访问，在内存中存储放数据，如果太大的数据会造成 内存溢出。可以通过 max_heap_table_size 控制 Memory表的大小，限制Memory表的最大的大小。

        • 如果 数据是临时的，而且 必须立即可用 ，那么就可以放到内存中。

        • 存储在 Memory 表中的数据如果突然间 丢失的话也没有太大的关系。

④MERGE：是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身没有数据，查询、更新、删除操作实际上是对内部的MyISAM表进行的。DROP操作只是删除MERGE表的定义，对内部的表没有任何影响。优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。

⑤Archive 引擎：用于数据存档

• Archive 存储引擎，仅仅支持 插入 和 查询 两种功能（被插入后不能再修改）。
• 在MySQL 5.5以后 支持索引 功能。
• 拥有很好的压缩机制，使用 zlib压缩库，对记录的数据实时的进行压缩，经常被用来作为仓库使用。
• 在同样数据量下，Archive表比MyISAM表大约小 75%，比支持事务的 InnoDB表 大约小 83 %。
• 数据文件结构：
  • 表名.ARZ （存储数据的压缩包）
• Archive 存储引擎采用了 行级锁。支持 auto_increment 列属性。auto_increment 列可以具有唯一索引或非唯一索引。其他列无法创建索引。
• Archive 存储引擎 适合日志和数据采集（档案）类应用；适合存储大量的历史记录数据。拥有 很高的插入速度，查询效率较差。

⑥CSV 引擎：存储数据时，以逗号分隔各个数据项

• CSV引擎可以将 普通的CSV文件作为MySQL的表来处理，但 不支持索引。
• CSV引擎可以作为一种 数据交换的机制，非常有用。
• CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
• CSV引擎是不支持列为null，换句话就是，CSV引擎表的列都必须是非空的。
• 对于数据的快速导入，导出是有明显优势的。
• 数据文件结构：
  • 表名.CSM 存储表的云数据的。
  • 表名.CSV 存储数据。

⑦Federated 引擎：访问远程表

• Federated 引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务器的灵活性，但也经常带来问题，因此 默认是禁用的。

⑧Ndb 引擎：MySQL集群专用存储引擎

• 也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集群。

三、MyIASM和InnoDB区别

①InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

②InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

③InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

④InnoDB 最小的锁粒度是行锁，MyISAM最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从MyISAM 变成 InnoDB 的重要原因之一；

InnoDB和MyISAM对比表

对比项	InnoDB	MyISAM
外键	支持	不支持
事务	支持	不支持
行表锁	行锁，操作时之锁某一行，不对其它行有影响，适合高并发的操作	表锁，即使操作一条记录也会锁住整个表，不适合高并发操作
缓存	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响	只缓存索引，不缓存真实数据
表空间	大	小
自带系统表使用	N	Y
关注点	事务：并发写，事务，更大资源	性能：节省资源，消耗少，简单业务
默认安装	Y	Y
默认使用	Y	N

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

• 如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；
• 如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

哪个存储引擎执行 select count(*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。

在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键。设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

MyISAM：

• MyISAM的相关了解

        MylSAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。
        访问速度快，对事务完整性没有要求。
        MylSAM适合查询、插入为主的应用。
        MylSAM在磁盘.上存储成三个文件，文件名和表名都相同，但是扩展名分别为：
        .frm文件存储表结构的定义
        数据文件的扩展名为.MYD (MYData)
        索引文件的扩展名是.MYI (MYIndex)

• MyISAM的特点

        表级锁定形式，数据在更新时锁定整个表
        数据库在读写过程中相互阻塞：
        数据写入的过程阻塞用户数据的读取
        数据读取的过程中阻塞用户的数据写入数据单独写入或读取，速度过程较快且占用资源相对少。

• MyISAM表支持3种不同的存储格式

      （1）静态（固定长度）表
               静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。

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

      （3）压缩表
               压缩表由 myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。（压缩的过程中会占用CPU性能。

• MyISAM适用的生产场景

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

• MyISAM索引结构

InnoDB：

•  InnoDB的相关了解

        支持事务，支持4个事务隔离级别
        MySQL从5.5.5版本开始，默认的存储引擎为InnoDB
        读写阻塞与事务隔离级别相关
        能非常高效的缓存索引和数据
        表与主键以簇的方式存储 BTREE
        支持分区、表空间，类似oracle数据库
        支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
        对硬件资源要求还是比较高的场合
        行级锁定，但是全表扫描仍然会是表级锁定
        使用like进行模糊查询时，会进行全表扫描，锁定整个表。
        对没有创建索引的字段进行查询，也会进行全表扫描锁定整个表。
        使用索引进行查询，则是行级锁定。

•  InnoDB的特点

InnoDB中不保存表的行数，如 select count() from table; 时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。需要注意的是当count()语句包含where条件时MyISAM也需要扫描整个表。
对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立组合索引。
delete清空整个表时，InnoDB 是一行一 行的删除，效率非常慢。MyISAM则会重建表。

• InnoDB适用的生产场景

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

• InnoDB索引结构