数据库学习----MySQL 存储引擎

MySQL存储引擎

MySQL常用的存储引擎

引擎名称	事务	说明
MYISAM	N	MySQL5.6之前的默认引擎，最常用
CSV	N	以CSV格式存储的非事务型存储引擎
Archive	N	只允许查询和新增数据而不允许修改的非实物型存储引擎
Memory	N	是一种存储在内存中的非事务型的存储引擎
INNODB	Y	最常用的事务型存储引擎
NDB	Y	MySQL集群使用的内存型存储引擎

存储引擎特点

MyISAM

• 非事务性存储引擎
  • 不能用在事务处理的场景
• 以堆表方式存储
  • 没有特定顺序
  • 所有叶子节点指向内存地址，避免二分查找，查询性能高
• 使用表级锁
  • 查询时会对表加共享锁，修改时加排他锁，读写操作会互斥
  • 不适合高并发的场景
• 支持Btree索引，空间索引，全文索引

MyISAM出现问题时，比如索引文件损坏，我们可以使用以下操作对表进行修复

check tablemyisam表名检查表的状态

repair tablemyisam表名修复表

myisampack -b -fmyisam表名压缩表[-b]生成OLD文件备份[-f]强制压缩

使用场景

• 读操作远远大于写操作的场景(查询性能很好)
• 不需要使用事务的场景

CSV

• 非事务型存储引擎
• 数据以CSV格式存储
  • 每列数据以逗号分割
  • 可以直接编辑和查看文件数据
  • 编辑文件后在MYSQL中是可以直接查看的
• 所有列不能为NULL
  • 创建表的每个列都需要指定not null
• 不支持索引
  • 不能频繁的查询和更新

使用场景

• 做为数据交换的中间表使用

Archive引擎

• 非事务型存储引擎
• 表数据使用zlib压缩
• 只支持insert和select操作
• 只允许在自增ID上建立索引

使用场景

• 日志和数据采集类应用
• 数据归档存储

Memory

• 非事务存储引擎
• 数据保存在内存中
  • 所有数据要是可以重新生成的
• 所有字段长度固定
  • 字段不能用text，blob那种大的数据类型
• 支持Btree和Hash索引

使用场景

• 用于缓存字典映射表
• 缓存周期性分析数据

Innodb引擎

• 事务型存储引擎支持ACID
• 数据按主键聚集存储
• 支持行级锁及MVCC
  • 加强数据并发能力
  • MVCC避免数据阻塞
• 支持Btree和自适应Hash索引
• 5.6支持全文索引 ，5.7支持空间索引

使用场景

• 大多数OLTP场景

NDB

需要安装特定版本的NDB MySQL数据库，NDB集群

• 事务型存储引擎
• 数据存储在内存中
  • 与Memory不同的是，他会把数据存在磁盘中
  • 显示的时候会把数据存储在内存中，对服务器内存大小有很大的要求
• 支持行级锁
• 支持高可用集群
• 支持Ttree索引
  • 跨设备存储

使用场景

• 需要数据完全同步的高可用场景

InnoDB

Innodb不支持在线修改表结构的场景

操作	语法
加全文索引	CREATE FULL TEXT INDEX name ON table(column);
加空间索引	ALTER TABLE geom ADD SPATIAL INDEX(g);
删除主键	ALTER TABLE tbl_name DROP PRIMARY KEY;
增加自增列	ALTER TABLE t ADD column id int AUTO_INCREMENT NOT NULL PRIMARY KEY;
修改列类型	ALTER TABLE tbl_name CHANGE c1 c1 NEW_TYPE
该表字符集	ALTER TABLE tbl_name CHARACTER SET = charset_name

在线DDL存在的问题

• 有部分语句不支持在线DDL
• 长时间的DDL操作会引起严重的主从延迟
• 无法对DDL操作进行资源限制

如何更安全的执行DDL

工具 pt-online-schema-change [OPTIONS] DSN

• 建立一个要被修改的表的一个新表，这个新表的结构是原表修改后的结构
• 对新表的数据分批次拷贝到新表中
• 数据导入之后，对原表和新表进行重命名就完成了

重命名时会对表进行暂时的锁定

使用pt-online-schema-change工具

所有操作需要在Linux的shell中运行

修改操作示例alter table nsl_tb add column modified_time timestamp

pt-online-schema-change --alter “add column modified_time timestamp” --execute D=stock,t=stock,u=dba,p=123456

D :执行的数据库

t：执行的表

u：执行的用户

p：用户的密码

事务

特征	说明
原子性（A）	一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束中间某个环节
一致性（C）	在事务开始之前和事务结束以后，数据库的完整性没有被破坏
隔离性（I）	事务的隔离性要求每个读写事务的对象与其他事务的操作对象能互相分离，即该事务提交钱对其他事务都不可见。
持久性（D）	事务一旦提交了，其结果就是永久性的，就算发生宕机等事故，数据库也能将数据恢复。

实现方式

特征	INNODB实现方式
原子性（A）	回滚日志（Undo Log）:用于记录数据修改前的状态
一致性（C）	重作日志（Redo Log）：用于记录数据修改后的状态
隔离性（I）	锁：用于隔离资源，分为共享锁和排他锁
持久性（D）	Redo Log & Undo Log

MVCC

MVCC（多版本并发控制）