Mysql存储引擎MyIsam和Innodb的特点和适用场景

一、MyIsam
特性
建表会生成frm MYD（特有） MYI（特有）三个文件
并发性及锁级别
表级锁并发性差
表损坏修复：不支持事务
可以使用check table tabklename和repair table tablename 来进行myisam表的检查和修复
还有mysql的工具myisamchk –help 来进行修复
MyISAM 支持数据压缩 myisampack *.MYI
对于已经压缩的表只能进行读操作

使用场景：
1.非事务型应用
2.只读类应用
3.空间类应用
二、innodb(mysql5.5及以后版本默认存储引擎):
show variables like ‘innodb_file_per_table’;查看表空间类型
show variables like ‘innodb_log_buffer_size’; 查看redo 缓冲区大小
show variables like ‘innodb_log_file_in_group’; 查看log file数量
事务型存储引擎，支持ACID
使用表空间进行数据存储
使用 show variables like ‘innodb_file_per_table’;查看表空间类型
on：独立表空间：tablename.ibd
off：系统表空间：ibdataX
修改这个参数使用set global innodb_file_per_table=on(off);来完成
两者之间的比较：
1.系统表空间无法简单的收缩文件大小，会造成较大的空间浪费（即删除无效数据之后不会改变文件的大小）
独立表空间可以通过optimize table命令收缩系统文件，这种方法不会影响数据表的正常使用
2.系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据（频繁写入的表）
建议：对innodb使用独立表空间

表转移的步骤
把原来存在与系统表空间中的表转移到独立表空间中的方法
步骤：
1.使用mysqldump导出所有数据库表数据
2.停止MySQL服务，修改参数，并删除Innodb相关文件
3.重启MySQL服务，重建Innodb系统表空间
4.重新导入数据

Innodb特性
Innodb是一种事务型存储引擎
完全支持事务的ACID特性
即原子性、一致性、隔离性、持久性
使用Redo Log和Undo Log来完成一致性
show variables like ‘innodb_log_buffer_size’; 查看redo 缓冲区大小
show variables like ‘innodb_log_file_in_group’; 查看log file数量

innodb支持行级锁
行级锁可以最大程度的支持并发
行级锁是由存储引擎层实现的

什么是锁
锁对主要作用是管理共享资源的并发访问
用于实现事务的隔离性
锁的类型
共享锁（读锁）
独占锁（写锁）
MySQL锁的粒度
表级锁（开销小，并发性低），通常在服务器层实现
行级锁（开销大，并发性高），只会在存储引擎层面进行实现
阻塞和死锁

innodb状态检查
show engine innodb status

适用场景：
mysql5.7之后已经支持全文索引以及空间函数
适用于大多数OLTP应用（On-Line Transaction Processing联机事务处理过程(OLTP)也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。）

MySQL 常见的两种存储引擎：MyISAM与InnoDB的理解

关于二者的对比与总结:

count运算上的区别：因为MyISAM缓存有表meta-data（行数等），因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。

是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACIDcompliant))型表。

是否支持外键： MyISAM不支持，而InnoDB支持。

MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。 在数据库做主从分离的情况下，经常选择MyISAM作为主库的存储引擎。 一般来说，如果需要事务支持，并且有较高的并发读取频率(MyISAM的表锁的粒度太大，所以当该表写并发量较高时，要等待的查询就会很多了)，InnoDB是不错的选择。如果你的数据量很大（MyISAM支持压缩特性可以减少磁盘的空间占用），而且不需要支持事务时，MyISAM是最好的选择。