MySQL（五）——存储引擎（MyISAM、InnoDB和MEMORY的区别）

一、MySQL存储引擎介绍

MySQL最大的特点就是插件式的存储引擎。
MySQL常用的存储引擎是MyISAM、InnoDB、MEMORY。
MySQL默认的存储引擎是InnoDB。

二、MyISAM介绍

• 特点：
  • MyISAM不支持事务，不支持外键，索引采用非聚集索引，其优势是访问速度快
  • 对事务的完整性没有要求或者以select、insert为主的应用基本上都可以采用这个存储引擎来创建表
• MyISAM的表在磁盘中存储三个文件：
  • 文件名和表名相同，后缀名不同
    • .frm (存储表的定义)
    • .MYD(MY Data 存储数据)
    • .MYI(My Index 存储索引)

三、InnoDB介绍

• 特点：
  • InnoDB存储引擎提供具有提交、回滚和崩溃恢复能力的事务安全，支持自动增长列、外键等功能
  • 索引采用聚集索引
• 索引和数据存储在同一个文件中：
  • InnoDB的表在磁盘中有两个文件
    • .frm（存储表的定义）
    • .ibd（存储数据和索引）

四、MEMORY介绍

• 特点：
  • MEMORY存储引擎使用存在内存中的内容来创建表
  • MEMORY类型的表访问非常快，因为它的数据存储在内存上，并且默认使用hash索引（不适合范围查询）
  • 但是一旦服务关闭，表中的数据就会丢失
• 每个MEMORY的表对应一个磁盘上文件:
  • .frm(存储表的定义)

五、MyISAM、InnoDB和MEMORY的区别

种类	锁机制	B-树索引	哈希索引	外键	事务	索引缓存	数据缓存
MYISAM	表锁	支持	不支持	不支持	不支持	支持	不支持
INNODB	行锁	支持	不支持	支持	支持	支持	支持
memory	表锁	支持	支持	不支持	不支持	支持	支持

• 锁机制：
  表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度
• B-树索引和哈希索引：
  加速SQL的查询速度
• 外键：
  子表的字段依赖父表的主键，设置两张表的依赖关系
• 事务：
  多个SQL语句，保证他们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败需要考虑回滚事务
• 索引缓存和数据缓存：
  和MySQL server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘I/O，读取上一次内存中查询的缓存就可以了

六、设置存储引擎

• 查看MySQL版本都支持哪些存储引擎

   show engines;
  

  
• 创建表时指定存储引擎

  create table table_name() engine=innodb;
  

• 在已存在的表上修改存储引擎

  alter table table_name engine = innodb;
  

  也可以修改配置文件，MySQL server会启动时加载配置文件，Windows下是my.ini文件，Linux下是my.cof文件，打开配置文件，在[mysqld]下面添加信息：default-storage-engine = InnoDB; 修改完配置修改保存重启就可以生效。