一、MySQL 存储引擎

MySQL存储引擎

---

一、MyISAM

1.概要

1.MySQL5.5 之前版本默认存储引擎

2.多用于系统表和临时表（在排序、分组等操作中，当数量超过一定的大小后，由查询优化器建立的临时表）

3.存储

  MYD：保存表的数据

  MYI：保存表的索引文件

2.特性

a).并发性与锁级别：使用表级锁而非行级锁，读取和写入互斥，读写并发性能比较弱

b).不支持事物、不具备AICD特性：原子性、一致性、分离性、永久性

c).只会缓存索引：通过key_buffer_size来设置缓存索引，提高访问性能减少磁盘IO的压力，但是只缓存索引，不缓存数据

d).不支持外键约束，只支持全文检索

3.表损坏修复

检查：check table tablename

修复：repair table tablename

通过命令行工具：myisamchk ,使用时需要停止服务，否则将造成更大的损坏

4.索引类型

1.支持全文索引

2.MySQL 5.7 之前版本唯一原生就支持全文索引的官方存储引擎

3.支持text、blog 类型字段建立前 500 个字符的前缀索引

4.支持数据压缩，使用命令行：myisampack 进行独立压缩，读取单行数据时，不必对整张表进行解压。
压缩后只能读取，不能写入

5.限制

1.MySQL5.0 版本之前默认表大小为4G，如存储大表则要修改MAX_Rows 和 AVG_ROW_LENGTH

2.MYSQL5.0 版本之后默认支持256TB，足够使用

6.使用场景

1.非事务型应用：数据仓库、报表

2.只读类应用

3.空间类应用：GPS

---

二、InnoDB

1.概要

1.MySQL5.5 及之后版本默认存储引擎

2.事务型存储引擎，支持ACID特性，适用于大量小事务

3.使用表空间进行数据存储

  `innodb_file_per_table`

      ON : 独立表空间:tablename.ibd

      OFF : 系统表空间:ibdataX（X为数字，从1开始）

2.特性

a).InnoDB 是一种事务型存储引擎

b).完全支持ACID特性

c).Redo Log：存储已提交的事务，持久性的体现；Undo Log：存储未提交的事务

d).支持行级锁：可以最大程度的支持并发，由存储引擎层实现的

• 锁？

  锁的主要作用是管理共享资源的并发访问，实现事务的隔离性

• 锁的类型？

共享锁（读锁）：相互不会阻塞，多个线程可以同时读取

独占锁（写锁）：会阻塞其他的

• 兼容性？

	写锁	读锁
写锁	不兼容	不兼容
读锁	不兼容	兼容

• 粒度（锁定对象的最小单位）？

行级锁

表级锁

d).状态检查
show engine innodb status:两次使用间隔 30S 以上，以保证采样的丰富性

2.表空间的选择

a).比较

• 系统表空间无法简单的收缩文件大小
• 独立表空间可以通过optimize table命令收缩系统文件
• 系统表空间会产生IO瓶颈
• 独立表空间可以同时向多个文件刷新数据

b).建议

• 对InnoDB 使用独立表空间（MySQL5.6之后独立表空间为默认配置）

3.表转移的步骤

把原来存在于系统表空间中的表转移到独立的表空间中的方法

步骤：

1.使用mysqldump导出所有数据库表数据

2.停止MySQL服务，修改参数，并删除InnoDB 相关文件（若主从服务架构，先停止从服务器）

3.重启MySQL服务，重建InnoDB系统表空间

4.重新导入数据

4.使用场景

适用于大多数应用：全文索引、空间函数

---

三、存储引擎的选择（首选InnoDB）

参考条件

• 事务：需要事务InnoDB ，不需要事务MyISAM
• 备份：InnoDB 支持免费热备方案
• 崩溃恢复：MyISAM损坏概率高于InnoDB，恢复速度MyISAM比InnoDB更慢
• 存储引擎的特有特性：聚集索引优化-InnoDB；地理空间搜索：5.7之前的版本MyISAM，5.7之后InnoDB

除非万不得已，否则不要混合使用多种存储引擎

例如：

1.MyISAM 和InnoDB混合使用时，当事务出现回滚时，只有InnoDB的表中数据可回滚，MyISAM则无法回滚，导致逻辑不完整

2.混合使用多种引擎，无法在线热备