1. 存储引擎概述

  • 存储引擎是决定如何存储数据库中的数据、如何为数据建立索引、如何更新和查询数据的机制
  • MySQL数据库管理系统提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，也可以根据自己的需要编写自己的存储引擎
  • MySQL常用的存储引擎有InnoDB、MyISAM、MEMORY和MERGE等
  • 可以查看MySQL支持的存储引擎，查看命令如下：SHOW ENGINES；

1、MySQL的默认存储引擎是InnoDB，如果想把其他存储引擎设置为默认存储引擎，可以使用如下命令：

  1. SET DEFAULT_STORAGE_ENGINE=存储引擎名;

2、如果不确定MySQL当前默认的存储引擎，可以使用如下命令：

  1. SHOW VARIABLES LIKE '%storage_engine%';

2. InnoDB存储引擎

  • MySQL5.5之后，InnoDB是MySQL的默认存储引擎
  • InnoDB是事务型数据库的首选引擎，具有提交、回滚和崩溃修复能力
  • InnoDB提供专门的缓冲池，是为处理巨大数据量时的最大性能设计
  • InnoDB支持外键约束，是 MySQL 上第一个提供外键约束的存储引擎

  • InnoDB存储引擎将表和索引存储在一个表空间中，表空间可以包含多个文件（或原始磁盘分区）

    3. MyISAM存储引擎

  • MySQL5.5之前，MyISAM是MySQL的默认存储引擎；

  • MyISAM不支持事物处理，也不支持外键约束，但是，MyISAM具有高效的查询速度，插入数据的速度也很快，是在Web、数据仓储等应用环境中最常使用的存储引擎之一；
  • MyISAM的修复时间与数据量的多少成正比，随着数据量的增加，MyISAM的恢复能力的性能会变弱；
  • MyISAM不提供专门的缓冲池，必须依靠操作系统来管理读取与写入的缓存，因此在某些情况下，其数据访问效率会比InnoDB低；

  • 使用MyISAM创建数据库，将生成三个文件。文件的主文件名与表名相同，扩展名包括“.frm”、“.myd”和“.myi”。

    4. MEMORY存储引擎

  • MEMORY类型的表中的数据存储在内存中，如果数据库重启或者发生崩溃，表中的数据都将消失；

  • MEMORY类型的表适用于暂时存放数据的临时表、作为统计操作的中间表，以及数据仓库中的维度表；
  • 每个MEMORY类型的表对应于一个文件，其主文件名与表名相同，扩展名为“.frm”，该文件只存储数据表的定义，而数据表中的数据存储在内存中，这样可以有效地提高数据的处理速度；

  • MEMORY默认使用哈希（HASH）索引。

    5. 其他存储引擎

    MERGE存储引擎
    MERGE存储引擎是一组具有相同结构的MyISAM表的组合。MERGE表本身没有数据，对MERGE表可以进行查询、更新和删除操作，这些操作实际上是对内部的MyISAM表进行的。
    BLACKHOLE存储引擎
    BLACKHOLE存储引擎可以用来验证存储文件语法的正确性。还可以对二进制日志记录进行开销测量，通过比较，允许与禁止二进制日志功能的BLACKHOLE的性能。可以用来查找与存储和引擎自身不相关的性能瓶颈。
    CSV存储引擎
    CSV存储引擎实际上操作的是一个标准的CSV文件，不支持索引。CSV文件是很多软件都支持的较为标准的格式，当需要把数据库中的数据导出成一份报表文件时，可以先在数据库中建立一张CVS表，然后将生成的报表信息插入到该表，得到CSV报表文件。
    ARCHIVE存储引擎
    ARCHIVE存储引擎主要用于通过较小的存储空间来存储过期的很少访问的历史数据。

    6. MySQL存储引擎的选择

    在实际工作中，可以根据应用场景的不同，对各种存储引擎的特点进行对比和分析，选择适合的存储引擎。

  • 如果实际应用需要事物处理，在并发操作时要求保持数据的一致性，而且除了查询和插入操作，还经常要进行更新和删除操作，这种情况可以选择InnoDB，可以有效降低更新和删除操作导致的锁定，并且可以确保事务的完整性提交和回滚。

  • 如果实际应用不需要事物处理，以查询和插入操作为主，更新和删除操作较少，并且对事物的完整性和并发性要求不是很高，可以选择MyISAM。
  • 如果实际应用不需要事物处理，需要很快的读写速度，并且对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，MEMORY适用于创建相对较小的数据库表。