MySQL学习笔记（一）存储引擎选择

在MySQL中有很多存储引擎，每种存储引擎大相径庭，那么又改如何选择呢？本文就将带你深入了解MySQL各种存储引擎。

MySQL 5.5以前的默认存储引擎是MyISAM, MySQL 5.5之后的默认存储引擎是InnoDB

特点	MyISAN	BDB	Memory	InnoDB	Archive
存储限制	没有	没有	有	64TB	没有
事务安全		支持		支持
锁机制	表锁	页锁	表锁	行锁	行锁
B树索引	支持	支持	支持	支持
哈希索引			支持	支持
全文索引	支持
集群索引				支持
数据缓存			支持	支持
索引缓存	支持		支持	支持
数据可压缩	支持				支持
空间使用	低	低	N/A	高	非常低
内存使用	低	低	中等	高	低
批量插入的速度	高	高	高	低	非常高
支持外键				支持

1. MyISAM

MySQL 5.5版本之前的默认存储引擎，在5.0以前最大表存储空间最大4G，5.0以后最大256TB。

Myisam存储引擎由.myd（数据）和 .myi（索引文件）组成，.frm文件存储表结构（所以存储引擎都有）

特性

• 并发性和锁级别 （对于读写混合的操作不好，为表级锁，写入和读互斥）
• 表损坏修复
• myisam表支持的索引类型（全文索引…）
• myisam支持表压缩（压缩后，此表为只读，不可以写入。使用myisampack压缩）

应用场景

• 没有事务
• 只读类应用（插入不频繁，查询非常频繁）
• 空间类应用（唯一支持空间函数的引擎）
• 做很多count 的计算

2. InnoDB

MySQL 5.5及之后版本的默认存储引擎

特性

• Innodb为事务性存储引擎
• 完全支持事物的ACID特性
• Redo log （实现事务的持久性） 和Undo log（为了实现事务的原子性，存储未完成事务log，用于回滚）
• Innodb支持行级锁
• 行级锁可以最大程度的支持并发
• 行级锁是由存储引擎层实现的

应用场景

• 可靠性要求比较高，或者要求事务
• 表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

独立表空间和系统表空间应该如何抉择呢？

两者比较

• 系统表空间：无法简单的收缩大小（这很恐怖，会导致ibdata1一直增大，即使删除了数据也不会变小）
• 独立表空间：可以通过optimize table 命令收缩系统文件
• 系统表空间：会产生I/O瓶颈（因为只有一个文件）
• 独立表空间：可以向多个文件刷新数据

总结
强烈建议：对Innodb引擎使用独立表空间（mysql5.6版本以后默认是独立表空间）

系统表转移为独立表的步骤（非常繁琐）

• 使用mysqldump导出所有数据库表数据
• 停止mysql服务，修改参数，并且删除Innodb相关文件
• 重启mysql服务，重建mysql系统表空间
• 重新导入数据

3. CSV

文件系统存储特点

• 数据以文本方式存储在文件中
• .csv文件存储表内容
• .csm文件存储表的元数据，如表状态和数据量
• .frm存储表的结构

CSV存储引擎特点

• 以CSV格式进行数据存储
• 所有列 必须都是不能为NULL
• 不支持索引
• 可以对数据文件直接编辑（其他引擎是二进制存储，不可编辑）

引用场景

• 作为数据交换的中间表

4. Archive

特性

• 以zlib对表数据进行压缩，磁盘I/O更少
• 数据存储在ARZ为后缀的文件中（表文件为a.arz，a.frm）
• 只支持insert 和 select 操作（不可以delete 和update，会提示没有这个功能）
• 只允许在自增ID列上加索引

应用场景

• 日志和数据采集类应用

5. Memory

特性

• 也称为HEAP存储引擎，所以数据保存在内存中（数据库重启后会导致数据丢失）

• 支持HASH索引（等值查找应选择HASH）和BTree索引（范围查找应选择）

• 所有字段都为固定长度，varchar(10) == char(10)
• 不支持BLOG和TEXT等大字段
• Memory存储使用表级锁（性能可能不如innodb）
• 最大大小由max_heap_table_size参数决定
• Memory存储引擎默认表大小只有16M，可以通过调整max_heap_table_size参数

应用场景

• 用于查找或是映射表，例如右边和地区的对应表
• 用于保存数据分析中产生的中间表
• 用于缓存周期性聚合数据的结果表

注意： Memory数据易丢失，所以要求数据可再生

6. Federated

特性

• 提供了访问远程mysql服务器上表的方法
• 本地不存储数据，数据全部放在远程服务器上

使用 Federated

默认是禁止的。如果需要启用，需要在启动时增加Federated参数

★思考题

如何选择存储引擎？

参考条件：

• 是否需要事务
• 是否可以热备份
• 崩溃恢复
• 存储引擎的特有特性

重要一点： 不要混合使用存储引擎
强烈推荐： Innodb

MyISAM和InnoDB引擎的区别？

区别：

• MyISAM不支持外键，而InnoDB支持
• MyISAM是非事务安全型的，而InnoDB是事务安全型的。
• MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
• MyISAM支持全文类型索引，而InnoDB不支持全文索引。
• MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
• MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
• InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

应用场景：

• MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那
  么MyISAM是更好的选择。
• InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT
  或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

为什么不建议innodb使用亿级大表

为什么不建议innodb使用亿级大表 | 峰云就她了