MySql引擎

MySql服务层主要用于定义一些与存储引擎无关的东西，因为底层的存储引擎可能不同，所以不能依赖具体的存储引擎

存储引擎针对表而不是针对库，也就是一个数据库里的表存储引擎可以不同，但是这样不推荐。

//查看提供的存储引擎
mysql> show engines;
//查看默认存储引擎
mysql> show variables like '%storage_engine%';

+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

---

MyISAM

MyISAM存储引擎表由MYD（数据文件）和MYI（索引文件）组成

特性：

1. 并发性和锁级别

使用表级锁，也就是会对整张表进行加锁，进行读的时候加共享锁(S锁)，进行写的时候加排他锁(X锁)
S锁：又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
X锁：又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

2. 表损坏修复

支持对由于任意意外关闭而损坏的表的检查和修复，非事务恢复，它不支持事务

 check table tablename   //检查
 repair table tablename  //修复

3.支持索引类型

支持全文索引-待补充

4.支持数据压缩

//为每一行进行压缩
myisampack -b -f tablename//压缩
//-b代表生成备份文件，-f为强制，因为此时数据较小，强制压缩

压缩后会生成备份文件，同时表为只读

mysql> insert into myisam values(1,1);
ERROR 1036 (HY000): Table 'myisam' is read only

限制
mysql版本小于5.0时默认表大小为4G，之后为256TB

可在建表时指定 MAX_ROWS和AVG_ROW_LENGTH2个参数相乘为表能达到的最大大小

使用场景
非事务型应用
只读类应用(可以对数据文件进行压缩)
空间类应用(地理位置信息等，空间函数)

---

Innodb

MySQL5.5之后版本默认存储引擎

Innodb使用表空间进行数据存储
变量为 innodb_file_per_table
参数为ON：使用独立表空间：tablename.ibd
参数为OFF：使用系统表空间：ibdataX

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
//创建一个表
create table innodb(id int, value varchar(20)) engine='innodb';
//查看存储方式
2018/07/27  10:23             8,588 innodb.frm
2018/07/27  10:23            98,304 innodb.ibd
//变成off
mysql> set global innodb_file_per_table=off;
//重新创建一个表
create table innodb2(id int, value varchar(20)) engine='innodb';
//查看存储
2018/07/27  10:23             8,588 innodb2.frm
//ibdata在上层目录，共享，不在该数据库目录下

5.5以及之前，默认为off
比较：

系统表无法简单的收缩文件大小（在删除数据后，表文件大小不变，只是把表标志位改变，浪费空间，若要删除则必须到处数据，进行相应的删除，之后进行重建）
独立表空间可以通过optimize table命令收缩系统文件，可以对单表进行操作
系统表空间会产生IO瓶颈，因为所有表都向这一个文件写入数据，顺序进行，产生等待
独立表空间可以同时向多个文件刷新数据

特性
1.Innodb是一种事务性存储引擎

完全支持事务的ACID特性
事务使用Redo Log（已经提交的事务） Undo Log（没有提交的事务）

2.Innodb支持行级锁

提高并发程度，由存储引擎层实现

锁的粒度：行级，表级

//innodb默认行级锁，我们来尝试加表级锁，写锁
mysql> lock table innodb write;
//使用另一个连接尝试读取，被阻塞
mysql> select * from innodb;
//解锁
mysql> unlock tables;
//解锁后另一个连接已经可以正常执行了

//同样，我们可以加读锁
mysql> lock table innodb read;
//之后用另一个连接进行读取，是可以的
mysql> select id from innodb;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
//若尝试进行插入，则会被阻塞
mysql> insert into innodb values(3,3);
//加写锁也会被阻塞，但是可以加读锁

Innodb状态检查

mysql> show engine innodb status;

---

CSV

数据以文本方式存储在文件中
.csv文件存储表内容
.csm文件存储表的元数据如表的状态和数据量
.frm文件存储表结构信息，不论哪种存储引擎都有这个文件

特点
1.以CSV格式进行数据存储
2.所有列必须都是不能为NULL的
3.不支持索引，不适合大表
4.可以对数据文件直接编辑

演示

//csv不支持列为空
mysql> create table csv (id int, v1 varchar(10),v2 varchar(10)) engine=csv;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

//正确创建
mysql> create table csv (id int not null, v1 varchar(10) not null,v2 varchar(10) not null) engine=csv;

//插入内容
mysql> insert into csv values(1,1,1),(2,2,2);

最终结果

我们把他编辑，增加一行
额，文件是只读的，最好还是不要去编辑了

//尝试去建立索引，提示最多0个keys。。那就是不支持了
mysql>  create index idx_id on csv(id);
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

适用场景
适合作为数据交换的中间表，其他应用把电子表格生成为csv文件供数据库读取，或把数据写到csv供其它应用读取

---

Archive

文件系统存储特点
以zlib对表数据进行压缩，磁盘I/O更少
数据存储在ARZ为后缀的文件中

只支持insert和select操作
只支持在自增ID列上加索引

//建表操作，key(id)表示建立索引
mysql> create table archive (id int auto_increment not null, v1 varchar(10), key(id))engine=archive;
//插入数据
mysql> insert archive(v1) values(1),(2);
//查询
mysql> select * from archive;
+----+------+
| id | v1   |
+----+------+
|  1 | 1    |
|  2 | 2    |
+----+------+
//更新操作，提示不支持该操作
mysql> update archive set v1 = 2 where id = 1;
ERROR 1031 (HY000): Table storage engine for 'archive' doesn't have this option
//删除操作
mysql> delete from archive where id = 1;
ERROR 1031 (HY000): Table storage engine for 'archive' doesn't have this option
//建立索引，出错，最多支持一个key，但是这个key必须是自增id
mysql> create index idx_v1 on archive(v1);
ERROR 1069 (42000): Too many keys specified; max 1 keys allowed

使用场景
日志和数据采集类应用

---

Memory(HEAP)

数据保存在内存中
若数据库服务器重启，则数据丢失，但表结构不会丢失，因为表结构为frm文件，存在磁盘

功能特点
1.支持Hash索引和BTree索引，默认为Hash，innodb和myisam默认为BTree，不支持Hash
2.所有字段都为固定长度，例：varchar（10） = char（10）
3.不支持blob和text等大字段
4.使用表级锁
5.表的最大大小由max_heap_table_size决定，默认16M，对已经存在的表无效

//尝试建立含有text字段的表，提示不支持
mysql> create table memory(id int, v1 char(10), v2 varchar(10), v3 text)engine=memory;
ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns
//删掉这个字段，重建
mysql> create table memory(id int, v1 char(10), v2 varchar(10))engine=memory;

使用场景
1.用于查找或者是映射表，例如邮编和地区的对应表
2.用于保存数据分析中产生的中间表
3.用于缓存周期性聚合数据的结果表

---

Federated

提供了远程访问MySQL服务器上表的方法
本地不存储数据，数据全部放到远程服务器上
本地需要保存表结构和远程服务器的连接信息

默认禁止，启动需要在启动时增加federated参数
windows下，关闭mysql服务，在my.ini加上federated=1，打开mysql服务即可

//创建远程数据库
mysql> create database remote;
mysql> use remote
//创建表，默认使用innodb引擎，我是5.7版本
mysql> create table remote_fed(id int, v1 varchar(10));
//插入数据
mysql> insert remote_fed values(1,1),(2,2),(3,3);
//进行用户的授权
mysql> grant all on remote.remote_fed to [e-mail]'127.0.0.1' identified by 'user';

//创建本地数据库
mysql> create database local;
mysql> use local
//创建本地表，同时要指定使用federated引擎，指定连接，参数为用户名，密码，远程库地址，数据库，表
mysql> create table local_fed(id int, v1 varchar(10)) engine=federated connection='mysql://user:[e-mail]:3306/remote/remote_fed';

//进行查询，查询出的是远程库的数据
mysql> select * from local_fed;
+------+------+
| id   | v1   |
+------+------+
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
+------+------+
//进行删除
mysql> delete from local_fed where id = 1;
mysql> use remote;
Database changed

//在远程库中进行查询，数据已经被删掉
mysql> select * from remote_fed;
+------+------+
| id   | v1   |
+------+------+
|    2 | 2    |
|    3 | 3    |
+------+------+

使用场景
偶尔的统计分析及手工查询

---

如何选择正确的存储引擎

• 事务
• 备份
• 崩溃回复
• 存储引擎的特有特性