MySQL 存储引擎

1. 存储引擎概述

1.1 定义

        就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。

1.2 MySQL 中的存储引擎

        Oracle，SqlServer 等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应的存储引擎，或者编写自定义的存储引擎。

1.3 查看MySQL 支持的存储引擎

show engines;

图1. show engines执行结果

2. MySQL 存储引擎介绍

2.1 常用MySQL 存储引擎特性

是对图1的总结

表1. 常用存储引擎特性

特点	InnoDB	MyISAM	Memory	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁 (适合高并发)	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6 版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

2.2 InnoDB

1. 是MySQL 的默认存储引擎。InnoDB存储引擎提供了具有提交，回滚，崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
2. InnoDB 存储引擎最大特点（区别其他存储引擎）

• 事务控制

start transaction;

insert into xxx(xx, ...) values (xxx, ...);

-- 查询不到新插入的数据
select * from xxx;

-- 提交事务
commit;

-- 查询出新插入的数据
select * from xxx;

• 外键
• 存储方式

         ①. 使用共享表空间存储，这种方式创建的表的表结构存储在.frm文件中，数据和索引保存在innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。

        ②. 使用多表空间存储，这种方式创建的表的表结构仍然存储在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。

2.3 MyISAM

1. MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
2. 最大特点 

• 不支持事务
• 文件存储方式

        每个MyISAM 在磁盘上存储成了3个文件，其文件名都和表名相同，但拓展名分别为：

        .frm：存储表定义

        .MYD：MYData，存储数据

        .MYI：MYIndex，存储索引

2.4 存储引擎的选择

• InnoDB使用场景
  • 对事务完整性有高要求
  • 在并发条件下要求数据的一致性
  • 数据操作除了插入和查询之外，还包含很多的更新、删除操作
• MyISAM
  • 以读操作和写操作为主，较少的更新和删除
  • 对事务完整性要求不高
  • 对并发性要求不高

3. InnoDB 和 MyISAM 的比较

• 锁
  • InnoDB 支持行级锁、表级锁，默认是行级锁
  • MyISAM 只有表级锁
• 事务
  • InnoDB 支持事务
  • MyISAM 不支持事务
• 外键
  • InnoDB 支持外键
  • MyISAM 不支持外键
• 是否支持MVCC
  • InnoDB 支持MVCC，应对高并发事务，MVCC 比单纯的加锁更高效，MVCC 仅在读已提交和可重复读两个隔离级别下工作。MVCC 可以使用乐观锁和悲观锁来实现
  • MyISAM 不支持外键
• 索引
  • InnoDB主键索引是聚集索引，其他索引是非聚集索引（辅助索引）
  • MyISAM 仅有非聚集索引