MySQL常用存储引擎之MyISAM
MySQL5.5之前版本默认存储引擎
临时表
在排序、分组等操作中，当数量超过一定的大小之后，由查询优化器建立的临时表。
MyISAM存储引擎表由MYD和MYI组成
CREATE TABLE `myIsam`(
`id` int(11) DEFAULT NULL,
`c1` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
ls -l
myIsam.frm
myIsam.MYD
myIsam.MYI
特性
- 并发性与锁级别（表级锁）
- 表损坏修复
- check table tablename
- repair table tablename
- MyISAM表支持的索引类型
- MyISAM表支持数据压缩
命令行：myisampack
限制
- 版本<MySQL5.0时默认表大小为4G
如存储大表则修改MAX_Rows和AVG_ROW_LENGTH - 版本>MySQL5.0时默认支持为256TB
适用场景：
- 非事务型应用
- 只读类应用
- 空间类应用
MySQL常用存储引擎之Innodb
MySQL5.5及之后版本默认存储引擎
Innodb使用表空间进行 数据存储
innodb_file_per_table
ON：独立表空间：tablename.ibd
OFF：系统表空间：ibdataX
show variables like 'innodb_file_per_table';
set global innodb_file_per_table=off;
系统表空间和独立表空间要如何选择
比较：
- 系统表空间无法简单的收缩文件大小
- 独立表空间可以通过optimize table命令收缩系统文件
- 系统表空间会产生IO瓶颈
- 独立表空间可以同时向多个文件刷新数据
建议：
- 对Innodb使用独立表空间（MySQL5.6之后默认采用对表空间）
表转移的步骤
把原来存在于系统表空间中的表转移到独立表空间中的方法
步骤：
1、使用mysqldump导出所有数据库表数据
2、停止MySQL服务，修改参数，并删除Innodb相关文件
3、重启MySQL服务，重建Innodb系统表空间
4、重新导入数据
Innodb存储引擎的特性（1）
系统表空间和独立表空间要如何选择
- Innodb 数据字典信息
- Undo 回滚段
Innodb存储引擎的特性
- Innodb是一种事务性存储引擎
- 完全支持事务的ACID特性
- Redo Log 和 Undo Log
show variables like 'innodb_log_buffer_size';
show variables like 'innodb_log_files_in_group';
- Innodb支持行级锁
- 行级锁可以最大程度的支持并发
- 行级锁是由存储引擎层实现的
什么是锁
- 锁对主要作用是管理共享资源的并发访问
- 锁用于实现事务的隔离性
锁的类型
- 共享锁（也称读锁）
- 独占锁（也称写锁）
| 写锁 | 读锁 | |
|---|---|---|
| 写锁 | 不兼容 | 不兼容 |
| 读锁 | 不兼容 | 兼容 |
锁的粒度
- 表级锁
lock table tablename write;
unlock tables;
- 行级锁
阻塞和死锁
- 什么是阻塞
不同锁的兼容性的关系，在有些时刻，一个事务中的锁需要等到另一个事务中的锁释放他所占用的资源，这就形成了阻塞。 - 什么是死锁
死锁是指两个或两个以上的事务在执行过程中，相互占用着对方等待的资源而产生的异常。
Innodb状态检查
show engine innodb status;
适用场景
- Innodb适合于大多数OLTP应用
MySQL常用存储引擎之CSV
文件系统存储特点
数据以文本方式存储在文件中
.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息
特点
- 以CSV格式进行数据存储
- 所有列必须都是不能为NULL的
- 不支持索引
不适合大表，不适合在线处理 - 可以对数据文件直接编辑
保存文本文件内容
适用场景
适合作为数据交换的中间表
电子表格->CSV文件->MySQL数据目录
数据->CSV文件->其他Web程序
MySQL常用存储引擎之Archive
文件系统存储特点
- 以zlib对表数据进行压缩，磁盘I/O更少
- 数据存储在ARZ为后缀的文件中
Archive存储引擎的特点
- 只支持insert 和select 操作
- 只允许在自增ID列上加索引
使用场景
日志和数据采集器应用
MySQL常用存储引擎之Memory
文件系统存储特点
也称HEAP存储引擎，所以数据保存在内存中
功能特点
- 支持HASH索引和BTree索引
- 所有字段都为固定长度 varchar(10)=char(10)
- 不支持BLOG和TEXT等大字段
- Memory存储引擎使用表级锁
- 最大大小由max_heap_table_size参数决定
容易混淆的概念
Memory存储引擎
VS
临时表
- 系统使用临时表
- 超过限制使用Myisam临时表
-未超限制使用Memory表
- 超过限制使用Myisam临时表
- create temporary table 建立的临时表
使用场景
- 用于查找或者是映射表，例如邮编和地区的对应表
- 用于保存数据分析中产生的中间表
- 用于缓存周期性聚合数据的结果表
Memory数据易丢失，所以要求数据可再生
MySQL常用存储引擎之Federated
特点
- 提供了访问远程MySQL服务器上表的方法
- 本地不存储数据，数据全部放到远程服务器上
- 本地需要保存表结构和远程服务器的连接信息
如何使用
默认禁止，启用需要在启动时增加federated参数
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
show engines;
federated=1
create database local;
create database remote;
use remote;
create table remote_fed(id int auto_increment not null, c1 varchar(10) not null default '', c2 char(10) not null default '',primery key(id))engine=innodb;
show create table remote_fed\G
insert into remote_fed(c1,c2) values('aaa','bbb'),('ccc','ddd'),('ddd','fff');
grant select,update,insert,delete on remote.remote_fed to fred_link@'127.0.0.1' identified by '123456';
use local;
create table remote_fed(id int(11) not null auto_increment, c1 varchar(10) not null default '', c2 char(10) not null default '', primary key(id))engine=federated connection='mysql://fred_link:123456@127.0.0.1:3306/remote/remote_fed';
rename table remote_fed to local_fed;
select * from local.local_fed;
delete from local.local_fed where id=2;
use remote;
select * fro remote_fed;
使用场景
偶尔的统计分析及手工查询
如何选择存储引擎
参考条件
- 事务
- 备份
- 崩溃恢复
- 存储引擎的特有特性
不要混合使用存储引擎
