Nemo的轨迹

work hard, be persistent, and good luck

0%

Mysql笔记:存储引擎对比-Innodb 和 Myisam

1
2
3
查看 Mysql 支持的引擎

show engines;

Myisam 在mysql5.5之前是默认存储引擎, 5.5之后默认是Innodb

Myisam 在查询和插入大量数据非常快(适合web 服务器日志数据); Innodb 适合 更新密集, 事务要求高, 有灾难恢复要求, 或者需要有外键约束;

Myisam 不是事务安全,Innodb 是事务安全. (事务是一种高级的处理方式,提供出错回滚的能力)

Myisam 默认是表锁.Innodb 默认是行锁,但当一条 sql 不能确定要执行的范围,Innodb 同样会锁住全表.

Myisam 的auto_increment 是保存在磁盘; Innodb 的auto_increment 是在表启动的时候把数值载入内存.

InnoDB是唯一支持外键的存储引擎,其他存储引擎在 create table 可以创建外键,但不会约束

Myisam 表结构,数据文件(table.MYD),索引文件(table.MYI) 是分离的; Innodb 索引文件和数据文件是一体的.表大小受限于操作系统的大小.

  • select count(*) table ; Myisam 可以在不便利数据返回准确条数,InnoDB 需要扫描全表

  • Myisam 键值是分离的(索引先载入内存,数据载入依赖系统); InnoDB键值一起保存(索引和数据一起载入InnoDB缓冲池)

Myisam的索引使用B+树来存储,B+树的叶子节点存储的是一个指针,指向数据实际的物理地址.Innodb 索引文件的叶子节点存储的是数据实际的物理地址.

Myisam 支持全文索引,压缩索引,Innodb 不支持

Myisam 辅助索引和主键索引相差无几(索引叶⼦节点保存对应数据⾏地址);InnoDB主键节点同时保存数据⾏,其他辅助索引保存的是主键索引的值

Innodb 通过辅助索引查找数据,需要先通过辅助索引找到对应的主键,再通过主键找到实际的数据.

  • 使用主键索引,避免数据冗余,修改数据的时候只需要修改辅助索引值

  • 但多了一次查询.得先找到主键才能获取的实际的数据.

聚集索引: 键值的逻辑顺序决定了表中相应⾏的物理顺序. Innodb 是通过主键聚集数据的.

optimize 命令: 减少数据库的碎片化.Myisam 支持, InnoDB 不支持

Repair optimize 需要有⾜够的硬盘空间,否则可能会破坏表,导致不能操作,那就要⽤上repair

表结构的更新与维护:

在千万级别量级面前, 增/删字段,增/删索引,增/删大量数据,都要认为是一个棘手问题.

更多的存储引擎

Memory: 把数据都放在内存了.适合小数据.同时支持散列索引和 B 树索引.

1
2

可以通过参数 max_heap_table_size 控制 Memory 表大小

MERGE: 一组 Myisam 表的聚合器,目的是提供一个全局视角(例如把按时间相关的表聚合在一起),MERGE表不存放实际数据

ARCHIVE: 优点是节省空间,但只支持基本插入和查询功能. 5.5版本后,ARCHIVE支持索引.

更多索引方式

B 树索引优于散列索引: 可以使用部分查询,通配查询,范围查询; 散列索引在进行”相等比较非常快”,但范围查询很慢