介绍

存储引擎是数据库的核心，在MySQL中，存储引擎是以插件的形式运行的。支持的引擎有十几种。一般常用的有InnoDB、MyISAM、Memory等。
image.png

InnoDB

InnoDB从MySQL5.5版本代替MyISAM成为默认引擎，相比MyISAM,InnoDB侧重于提供事务支持以及外部键等高级数据功能。

  • 支持事务。默认的事务隔离级别为可重复读，通过MVCC【并发版本控制】来实现。
  • 使用的锁粒度默认是行级锁，可以支持更高的并发。也支持表锁；
  • 支持外键约束：外键约束其实降低了表的查询速度，但是增加了表之间的耦合度；
  • 可以通过自动增长列，方法是auto_increment；
  • 配合一些热备工具可以支持在线热备份；
  • 在InnoDB中存在缓存管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
  • 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键组织。数据和索引放在一块，都位于B+数的叶子结点上；MySQL存储引擎 - 图2
  • InnoDB表的select count()比MyISAM慢很多；当执行select count() from t时，会先把数据读取出来，一行一行的累加，最后返回总数量。【当count(*) 语句包含where条件时，这两种引擎操作时一样的】。
  • DELETE FROM table时，InnoDB不会重新建立表，而是一行一行地删除；
  • InnoDB的存储表和索引有两种形式：
    • 共享表空间存储：所有的表和索引存放在同一个表空间中；
    • 多表空间存储：表结构放在.frm文件中，数据和索引放在.idb文件中。对于分区表，每个分区对应单独的.ibd文件。【分区表可以提升查询效率】；

InnoDB存储引擎的执行过程

  1. 首先MYSQL执行器根据执行计划，调用存储引擎的API查询数据；
  2. 存储引擎先从缓存池Buffer pool中查询数据，如果没有就会去磁盘中查询，如果查询到了就将其放到缓存池中；
  3. 在数据加载到Buffer Pool的同时，会将这条数据的原始记录保存到undo日志文件中；
  4. InnoDB会在Buffer Pool中执行更新操作；
  5. 更新后的数据会记录在redo log buffer中；
  6. 提交事务在提交的同时会做以下三件事：
    1. 将redo log buffer中的数据刷入到redo log文件中；
    2. 将本次操作记录写入到bin log文件中；
    3. 将bin log文件名字和更新内容在bin log中的位置记录到redo log中，同时在redo log最后添加commit标记；
  7. 使用一个后台线程，它会在某个时机将我们Buffer Pool中的更新后的数据刷到MySQL数据库中，这样就将内存和数据库的数据保持统一。

    MyISAM

    在mysql5.1之前的版本，使用MyISAM作为默认的存储引擎。支持全文索引、压缩、空间函数（GIS）等，但是MyISAM不支持事务和行级锁，而且崩溃后无法安全恢复。
    对于只读数据，表比较小，可以不考虑修复的操作，可以使用MyISAM引擎。

    存储

    MyISAM会将表存储在两个文件中：数据文件和索引文件，使用.MYD和.MYI作为扩展名。MyISAM可以包含动态或者是静态行。
    每张MyISAM表在磁盘上会对应三个文件
  • .frm文件：存储表的定义数据；
  • .MYD文件：存放表具体记录的数据；
  • .MYI文件：存储索引；

    特性

  1. 加锁和并发
    1. MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入的时候对表加上排它锁。但是在表有读取查询的时候，也可以对表进行插入操作【并发插入】；
    2. 不支持事务，不支持锁，在读写上效率高于InnoDB。
  2. 体积小，质量大。MyISAM的索引和数据是分开的，并且索引有压缩，内存使用率提高了很多。
  3. 修复
    1. 这里的修复和事务修复、崩溃之后的恢复不同。
    2. 执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。
    3. 通过CHECK TABLE mytable 来检查表的错误，如果有错误就执行 REPAIR TABLE mytable进行修复。
    4. 当mysql服务已经关闭，可以使用myisamchk命令行工具进行检查和修复操作；
  4. 索引特性
    1. 对于MyISAM表，BLOB和TEXT等长字段，也可以基于前500个字符创建索引，也支持全文索引，基于分词创建，支持复杂查询。

  5. 延迟更新索引键

    1. 创建表的时候，如果指定DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是写到内存的键缓冲区【in-memory key buffer】，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘中。
    2. 极大提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

      Memory

      相当于临时表，Memory是将数据直接存在内存中，适合数据量小的表，同时为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联，文件是frm；

      特性

  6. 不支持TEXT和BLOB类型。对于字符串类型的数据，只支持固定长度的行，varchar(64)会被自动存储为char(64)类型；

  7. 只支持表级锁。在访问量大的时候，表级锁会成为Memory引擎的瓶颈；
  8. 一旦服务器宕机，数据就会丢失【数据存放在内存中】；
  9. 在查询的时候，如果用到临时表，而且临时表中有BLOB、TEXT类型的字段，这个临时表就会转成MyISAM类型的表，性能会急剧降低；
  10. 默认是使用hash索引；