MyISAM

MySQL 5.5.5 以前的版本，使用 MyISAM 作为默认存储引擎。MyISAM 提供高速存储和检索，以及全文搜索的能力。

每个 MyISAM 表在磁盘上存储成三个文件，文件名和表明相同，扩展名如下：

  • .frm：存储表定义
  • .MYD：存储数据 (MYData)
  • .MYI：存储索引 (MYIndex)
    数据文件(.MYD)和索引文件(.MYI)可以放置在不同目录，平均分配 IO，获取更快的速度。自定义数据文件和索引文件的路径，可以在创建表的时候使用DATA DIRECTORY和INDEX DIRECTORY语句来指定，文件路径需要使用绝对路径。

MyISAM 表支持三种不同的存储格式：

  • 静态表
    静态表为默认的存储格式。静态表中的字段都是非变长字段，即每个记录都是固定长度的，因为静态表在存储的时候会根据列定义的宽度自动填补空格。但在访问的时候并不会得到这些空格，在返回数据之前会经这些空格处理掉。但要注意，在某些情况下可能需要返回字段后的空格，使用这种格式时后面的空格会被自动处理掉。
    静态表的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用空间通常比动态表多。

  • 动态表
    动态表包含变长字段，这样存储的优点是占用空间少，但频繁的更新删除记录会产生空间碎片，需要定期执行OPTIMIZE TABLE或myisamchk -r来改善性能。而且动态表出现故障的时候恢复相对比较困难。

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

InnoDB

从 MySQL 5.5.5 开始, 使用 InnoDB 作为默认存储引擎。具有 ACID 特性：

  • A: atomicity 原子性
    事务是一个原子操作单元，其对数据的修改，要么全都执行，要么都不执行。如果事务执行到一半出现错误，数据库就会回滚到事务开始执行的状态。
  • C: consistency 一致性
    在事务开始和结束后，数据库的完整性约束没有被破坏。如，A 向 B 转账，A 扣钱成功，则 B 必然收款成功。
  • I: isolation 隔离性
    同一时间，只允许一个事务请求同一数据，不同事务之间彼此没有任何干扰。
  • D: durability 持久性
    事务完成之后，它对数据的修改是永久性的，即使出现系统故障也能够保持。

MyISAM 与 InnoDB 比较

  • CURD 操作

    • 如果表更新不频繁，以查询为主，则执行SELECT查询，MyISAM 速度更快。
    • 如果表修改频繁，处于性能考虑，应该使用 InnoDB。
    • 清空整张表的数据时 (DELETE FROM table_name)，InnoDB 是一行一行的删除，效率非常慢。所以，清空 InnoDB 表最好使用TRUNCATE table。

  • 事务支持

    • MyISAM 不支持事务；InnoDB 支持事务，外键等。具有事务的提交 (commit)、回滚 (rollback) 和故障修复 (crash recovery) 的特性。
    • 而且 InnoDB 的 AUTOCOMMIT 是默认开启的，即每条 SQL 语句会默认被封装成一个事务，自动提交。所以，允许的情况下，尽量将多条 SQL 语句放在 BEGIN TRANSACTION 和 COMMIT 之间，组成一个事务提交，这样可以减小数据库多次提交导致的开销。

  • 存储结构

    • MyISAM：每个 MyISAM 表在磁盘上存储成三个文件 (.frm，存储表定义；.MYD，存储表数据；.MYI，存储表索引)
    • InnoDB：所有表都保存在同一个数据文件中 (也可能是多个文件，或者是独立的表空间文件)，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。

  • 存储空间

    • MyISAM：支持三种不同的存储格式 (静态表、动态表和压缩表)，所以 MyISAM 存储空间可以更小。
    • InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓存池用于高速缓冲数据和索引。

  • 锁差异

    • MySQL 表级锁有两种：表共享锁 (Table Read Lock) 和表独占写锁 (Table Write Lock)。
    • MyISAM：只支持表级锁，用户在操作 MyISAM 表时，SELECT, UPDATE, DELETE, INSERT语句都会给表自动加锁。MyISAM 表的读操作 (表共享锁) 不会阻塞其它线程对该表的读操作，但会阻塞对该表的写操作；MyISAM 表的读和写操作之间，以及写和写操作之间是串行的。即当一个线程对一个表加写锁 (表独占写锁) 后，其它线程的读和写操作都会阻塞，直到这个线程释放写锁。
    • MyISAM 表的写锁优先级比读锁高，即使读请求比写请求先到锁等待队列，写锁也会插到读请求之前。所以，MyISAM 引擎不太适合有大量更新操作和查询操作的表，大量的更新操作会造成查询操作很难获得读锁，从而大大影响查询操作。
    • InnoDB：支持行级锁，行锁大幅度提升了多用户并发操作的性能。

  • 主键

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

  • 表的行数

    • MyISAM：保存有表的总行数，SELECT COUNT(*) FROM table_name; 会直接取出该值。
    • InnoDB：没有保存表的总行数，SELECT COUNT(*) FROM table_name; 会遍历整张表。
    • 因为 MyISAM 保存的是整张表的总行数，所以当添加条件查询表行数的时候，MyISAM 和 InnoDB 查询处理方式都一样 SELECT COUNT(*) FROM table_name WHERE ...。

MEMORY

MEMORY 存储引擎，直接将数据保存到内存中，所以速度非常快。但是，MEMORY 表只能使用不变长度的字段，所以BLOG和TEXT不能够使用。VARCHAR是一种可变的类型，但因为它在 MySQL 内部当做长度固定不变的CHAR类型，所以可以使用。

MEMORY 引擎使用场景：

  • 因为存储在内存，所以表数据量不能太大
  • 内存中保存的数据不具备持久性，而且稳定性不高，所以数据容易丢失所造成的影响不大的情况

MEMORY 引擎支持 HASH 索引和 B-tree 索引。

ARCHIVE

ARCHIVE 引擎仅仅支持基本的插入和查询，MySQL 5.5 之后，开始支持索引功能。ARCHIVE 拥有很好的压缩机制，使用 zlib 压缩库，在记录被请求是会实时压缩，所以经常被用来当做仓库使用。