14.1、存储引擎的使用

  • 数据库中的各表均被（在创建表时）指定的存储引擎来处理。
  • 服务器可用的引擎依赖于以下因素：
    • MySQL 的版本
    • 服务器在开发时如何被配置
    • 启动选项
  • 为了解当前服务器中有哪些存储引擎可用，可使用 SHOW ENGINES 语句：
  • mysql> SHOW ENGINES\G
  • 三、存储引擎 - 图1

  • 在创建表时，可使用 ENGINE 选项为 CREATE TABLE 语句显式指定存储引擎。
  • CREATE TABLE TABLENAME (NO INT) ENGINE = MyISAM;
  • 如果在创建表时没有显式指定存储引擎，则该表使用当前默认的存储引擎
  • 默认的存储引擎可在 my.ini 配置文件中使用 default-storage-engine 选项指定。
  • 现有表的存储引擎可使用 ALTER TABLE 语句来改变：ALTER TABLE TABLENAME ENGINE = INNODB;
  • 为确定某表所使用的存储引擎，可以使用 SHOW CREATE TABLE 或 SHOW TABLE STATUS 语句：
  • mysql> SHOW CREATE TABLE emp\G

  • mysql> SHOW TABLE STATUS LIKE ‘emp’ \G

    14.2、常用的存储引擎

    14.2.1、MyISAM 存储引擎

    MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但 MyISAM不支持事务和行级锁(myisam 改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

  • MyISAM 存储引擎是 MySQL 最常用的引擎。

  • 支持表锁

  • 它管理的表具有以下特征：

    • 使用三个文件表示每个表：
      • 格式文件 — 存储表结构的定义（mytable.frm）
      • 数据文件 — 存储表行的内容（mytable.MYD）
      • 索引文件 — 存储表上索引（mytable.MYI）
    • 灵活的 AUTO_INCREMENT 字段处理
    • 可被转换为压缩、只读表来节省空间

      14.2.2、InnoDB 存储引擎

  • InnoDB 存储引擎是 MySQL 的缺省引擎。

  • 支持行锁和表锁，支持事务，并发量高，聚簇索引，全文检索，支持外键。
  • 它管理的表具有下列主要特征：
    • 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
  • InnoDB 表空间 tablespace 被用于存储表的内容
    • 提供一组用来记录事务性活动的日志文件
  • 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
    • 提供全 ACID 兼容
  • 在 MySQL 服务器崩溃后提供自动恢复
    • 多版本（MVCC）和行级锁定

  • 支持外键及引用的完整性，包括级联删除和更新

    • 默认隔离级别为可重复读

      14.2.3、MEMORY 存储引擎

  • 使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。

  • MEMORY 存储引擎管理的表具有下列特征：
    • 在数据库目录内，每个表均以.frm 格式的文件表示。
    • 表数据及索引被存储在内存中。
    • 表级锁机制。
    • 不能包含 TEXT 或 BLOB 字段。

  • MEMORY 存储引擎以前被称为 HEAP 引擎。

    14.3、选择合适的存储引擎

  • MyISAM 表最适合于大量的数据读而少量数据更新的混合操作。MyISAM 表的另一种适用情形是使用压缩的只读表。

  • 如果查询中包含较多的数据更新操作，应使用 InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
  • 可使用 MEMORY 存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

查询较多的选择 MYISAM：
InnoDB 的表是根据主键进行展开的 B+tree 的聚集索引。MyIsam 则非聚集型索引，myisam 存储会有两个文件，一个是索引文件，另外一个是数据文件，其中索引文件中的索引指向数据文件中的表数据。
聚集型索引并不是一种单独的索引类型，而是一种存储方式，InnoDB 聚集型索引实际上是在同一结构中保存了 B+tree 索引和数据行。当有聚簇索引时，它的索引实际放在叶子页中。
三、存储引擎 - 图2

innodb 保存时一个.frm 文件，一个.idb 文件.索引文件和数据文件是放在同一个文件中的
myisam 有三个文件，索引文件和数据文件分开存放 .MYD 文件表示数据。.MYI 文件表示索引。.frm 文件
InnoDB 默认可以创建 16 个索引

  • InnoDB 支持事务，MyIsam 不支持事务，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放到 begin 和 commit 之间，组成一个事务；
  • InnoDB 支持外键，而 MyIsam 不支持，对一个包含外键的 InnoDB 表转成 MyIsam 表会失败
  • InnoDB 是聚集索引，数据文件和索引绑定在一块，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后在通过主键查询到数据。因此主键不应该过大。主键过大的时候，其它索引也会很大。而 MyIsam 是非聚集索引，数据和文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
  • InnoDB 不支持全文检索，而 MyIsam 支持全文检索，查询效率上 MyIsam 要高

转载 https://www.yuque.com/jykss/jykss/mds7qx#izCti