MySQL 存储引擎归纳总结

MyISAM

特点

• 不支持事务
• 加锁和并发
  锁：表锁
  并发：共享锁S锁和排他锁X锁
• 有特殊的计数器记录当前的记录数
• 数据文件和索引文件分开存储[堆表形式 所有记录无序存储]
• 列索引
  可以给BLOB和TEXT类型的列的前500个字符创建索引
• 主键索引和二级索引完全一样都是B+树的数据结构，只有是否唯一的区别（主键和唯一索引有唯一属性，其他普通索引没有唯一属性。B+树叶子节点存储的都是指向行记录的row pointer）
• 延迟更新索引
  默认开启DELAY_KEY_WRITE
  查询结束后不会把索引的改变数据写入磁盘 而是改变内存的索引数据 只有清理缓存区或者关闭表的时候才会把数据写入磁盘
• 修复表
  使用check table tablename 查看表状态 使用repair table tablename修复表

适用场景

• 不需要事务的支持
• 并发相对较低[因为是表锁]
• 数据修改较少 以读为主[读写阻塞]
• 数据一致性要求不高

实际应用

• 尽量使用索引[有索引缓存机制]
• 调整读写的优先级
  默认写优先级高于读
• 启用延迟插入改善大批写入性能
• 尽量顺序操作使得在表尾查入 降低单个操作的阻塞时间
• 降低并发数
• 静态数据使用query cache查询缓存
• count只有在全表扫描的时候性能比较高 其他条件都需要访问具体的数据

InnoDB

特性

• 较好的事务支持
  四个事务隔离级别 支持多版本读MVCC
• 行级锁定
  使用索引检索的时候是行锁否则都是表锁
  全表扫描是时候是表锁
  注意还存在间隙锁的影响
• 读写阻塞和事务隔离级别有关
• 高效的缓存特性
  既缓存索引 又缓存数据
• 整个表和主键使用cluster聚簇的方式存储 组成一颗平衡树
• 辅助索引[Secondary Index 非主键索引]会保存主键信息
  因此主键最好不要太长
• 支持外键
• 数据文件和索引文件存储在同一个表空间中
• MySQL5.6之后支持全文索引
• 主键和二级索引数据结构一样都是B+树，但叶子节点存储的键值不一样（主键的叶子节点存储整行数据，因此也称为聚集索引；而二级索引的叶子节点存储的是主键的键值）
• 支持崩溃恢复
• 相同数据量的Innodb表的空间是M表的1.5-2倍

适用场景

• 1.需要事务支持
• 2.行级锁对高并发具有很好的适应能力 确保查询是通过索引完成 [使用索引才是行锁 否则都是表锁]
• 3.数据更新频繁
• 4.数据一致性要求高
• 5.硬件设备内存大 使用InnoDB的缓存能力可以提高内存利用率 减少磁盘IO

实际应用

• 1.主键尽可能小 避免给辅助索引带来空间负担
• 2.避免全表索引 会使用表锁
• 3.尽可能缓存所有的索引和数据 提高相应速度
• 4.大批量小插入的时候最好自己控制事务
• 5.合理设置innodb_flush_log_at_trx_commit参数值
• 6.避免主键更新 会带来大量的参数移动

NDBCluster

特性

• 1.分布式
  分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
• 2.支持事务
  和Innodb一样，支持事务
• 3.可与mysqld不在一台主机
  可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互
• 4.内存需求量巨大
  新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

适用场景

• 1.具有非常高的并发需求
• 2.对单个请求的响应并不是非常的critical
• 3.查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding

实际应用

• 1.尽可能让查询简单，避免数据的跨节点传输
• 2.尽可能满足SQL节点的计算性能，大一点的集群SQL节点会明显多余Data节点
• 3.在各节点之间尽可能使用万兆网络环境互联，以减少数据在网络层传输过程中的延时

---