分享程序员开发的那些事...
更新时间：2022-09-16 14:34:29
熟悉mysql数据库的朋友，肯定会喜欢mysql强大的插件式存储引擎，能够支持太多存储引擎，当目前的存储引擎不能满足你的需求时，你可以根据自己的需求选择合适的引擎，将相关的文件拷贝到相关路径，甚至不需要重启数据库，就可以使用。真的很强大。
1 常见存储引擎
memory存储引擎
硬盘上存储表结构信息，格式为.frm，数据存储在内存中
不支持blob text等格式
创建表结构，
支持表锁
支持B树索引和哈希索引
支持数据缓存 数据 缓存
插入速度快
分配给memory引擎表的内存不会释放，由该表持有，删除数据也不会被回收，会被新插入数据使用
CSV存储引擎
所有列必须制定为Not NULL
CSV 引擎不支持索引 不支持分区
文件格式 .frm 表结构信息
.CSV 则是数据文件 是实际的数据
.CSM 报错表的状态和表中的数据
可以直接更改.csv文件 更改数据， check table 检查 repair table （注：在手动更改.csv文件后 可以使用 repair table 加载数据）
例如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
#创建表结构 存储引擎为CSV
create table csv2 (id int not null,
name char(20) not null default "ZN")
engine=csv charset utf8;
#检查表结构：
mysql> desc csv1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(20) | NO | | ZN | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
#插入数据mysql> insert into csv1 values(3,'linux'),(20,"MYSQL");
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> insert into csv1 values(9,'linux'),(8,"MYSQL");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from csv1;
+----+-------+
| id | name |
+----+-------+
| 3 | linux || 20 | MYSQL || 9 | linux || 8 | MYSQL |+----+-------+
4 rows in set (0.00 sec)
#手动更改文件 vim /var/lib/mysql/test/csv1.CSV #（RPM包安装路径 其他路径根据自己安装情况）
8,"MYSQL"
9,"linux"
99,"docker"
200,"baidu"
44,"openstack"
155,"facebook"
121,"ansible"
#检查表mysql> check table csv1;
+-----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.csv1 | check | error | Corrupt |
+-----------+-------+----------+----------+
1 row in set (0.03 sec)
#修复表mysql> repair table csv1;
+-----------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| test.csv1 | repair | status | OK |+-----------+--------+----------+----------+
1 row in set (0.05 sec)
#检查修复mysql> check table csv1;
+-----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.csv1 | check | status | OK |
+-----------+-------+----------+----------+
1 row in set (0.03 sec)
#检查数据 更改生效mysql> select * from csv1;
+-----+-----------+
| id | name |
+-----+-----------+
| 9 | linux || 99 | docker || 200 | baidu || 44 | openstack || 155 | facebook || 121 | ansible |+-----+-----------+
|
注意事项：check语句会检查CSV文件的分隔符是否正确，数据列和定义的表结构是否相同，发现不合法的行会抛出异常，在使用修复时，会尝试从当前的CSV文件中复制合法数据，清楚不合法数据，但是需要注意 修复时发现文件中有损坏的记录行，那么后面的数据全部丢失，不管是否合法。
ARCHIVE 存储引擎
适用场景 归档
支持大量数据压缩 插入的列会被压缩，ARCHIVE 引擎使用Zlib无损数据压缩算法
还可以使用optimze table 分析表并打包成更小的格式
仅支持insert、update语句而不支持delete replace update truncate 等语句 能支持order by操作 blob列类型
支持行级锁 但是不支持索引
archive 引擎表文件.frm定义文件 .arz的数据文件，执行优化操作时可能还会还会出现一个扩展名的.arn文件。
简单测试：
先创建一个myisam存储引擎的表，插入数据，然后创建ARCHIVE 存储引擎的表插入数据，检查其存储空间的大小。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
#创建测试表和相关的数据mysql> create table archive2 engine=myisam as select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from information_schema.columns;
Query OK, 3362 rows affected (0.10 sec)
Records: 3362 Duplicates: 0 Warnings: 0mysql> select count(*) from archive2;
+----------+
| count(*) |
+----------+
| 3362 |+----------+
1 row in set (0.00 sec)
#继续再插入数据（执行多次）mysql> insert into archive2 select * from archive2;
Query OK, 107584 rows affected (0.23 sec)
#检查数据量mysql> select count(*) from archive2;
+----------+
| count(*) |
+----------+
| 860672 |+----------+
1 row in set (0.00 sec)
#检查数据大小mysql> show table status like "archive2"\G;
*************************** 1. row *************************** Name: archive2
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 860672
Avg_row_length: 53
Data_length: 45790208
Max_data_length: 281474976710655 Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2017-05-16 13:35:26
Update_time: 2017-05-16 13:38:14
Check_time: NULL
Collation: gbk_chinese_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR: No query specified
#数据大小45790208新创建存储引擎为archive类型的表mysql> create table archive3 engine=archive as select * from archive2;
Query OK, 860672 rows affected (2.69 sec)
Records: 860672 Duplicates: 0 Warnings: 0mysql> select count(*) from archive3;
+----------+
| count(*) |
+----------+
| 860672 |+----------+
1 row in set (0.11 sec)
检查大小mysql> show table status like "archive3"\G;
*************************** 1. row *************************** Name: archive3
Engine: ARCHIVE
Version: 10
Row_format: Compressed
Rows: 860672
Avg_row_length: 6
Data_length: 5801647
Max_data_length: 0 Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: 2017-05-16 13:42:35
Check_time: NULL
Collation: gbk_chinese_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
大小：5801647 |
对比结果相差8倍的存储值，差距还是很大。
BLACKGOLE存储引擎
是一个比较特殊的存储引擎，只管写入，但不管存储，尽管能像其他存储引擎一样接受数据，但是所有数据都不会保存，BLACKGOLE存储引擎永远为空，有点类似Linux下的/dev/null。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
#创建表试试mysql> create table black engine=blackhole as select * from archive2;
Query OK, 860672 rows affected (0.65 sec)
Records: 860672 Duplicates: 0 Warnings: 0mysql> select * from black ;
Empty set (0.00 sec)
mysql> insert into black select * from archive2;
Query OK, 860672 rows affected (0.62 sec)
Records: 860672 Duplicates: 0 Warnings: 0mysql> select * from black ;
Empty set (0.00 sec)
多次测试发现真的这么神奇，插入什么都成功了，但就是找不到数据，很神奇的存储引擎吧？看看 |
多次测试，结果就是那么神奇，插入都是成功的，但就是找不到数据，这个存储引擎神奇吧，看看这个神奇的存储引擎有哪些用途呢？？
1、尽管BLACKHOLE存储引擎不会保存数据，但是启用binlog，那么执行得SQL语句还是实际上被记录，也就是说能复制到SLAVE端。如下图：
结合复制特性中的replicete-do-* 或者reolicate-ignore-*规则，可以实现对日志的过滤，通过这一巧妙的设计，就可以实现相同的写入，但是主从间的数据不一致。
BLACKHOLE对象中的insert触发器会按照标准触发，不过由于BLACKHOLE对象是空，那么UPdate和delete绝对不可能触发，对于触发器中FOR EACH RAW语句并不会有任何影响。
其他应用情形：
其他应用：
验证dump文件语法
通过对比启动一级禁用二进制日志文件时的性能，来评估二进制日志对负载的影响。
BLACKHOLE存储引擎 支持事务，提交事务会写入二进制日志 但回滚则不会
BLACKHOLE存储引擎与自增列
BLACKHOLE引擎是no-op无操作引擎，所有在BLACKHOLE对象上的操作是没有效果的，那么久需要考虑主见自增列的行为，该引擎不会自动增加自增列值，实际上也不会保存自增字段的状态，对于复制来说，这一点很重要。
考虑以下复制场景
1、Master端BLACKHOLE表拥有一个自增的主键列
2、Slave端表存储引擎为Myisam
3、Master端对该表对象的插入操作没有明确知道自增列的列值
该场景下 Slave端就会出现主键列的重复键错误，再给予语句的复制（SBR）模式下，每次插入事件的INSERT_ID都是相同的，因此复制就会触发插入重复键的错误。
在基于行的复制模式下，该引擎返回的列值总是相同的，那么在Slave端就会出现尝试插入相同值的错误。
MySQL的插件式存储引擎是功能很丰富的，同样也是适用于不用的应用情景，当你深入了解其原理后，才能发挥出MySQL更好的性能。
本文转自 tianya1993 51CTO博客，原文链接：http://blog.51cto.com/dreamlinux/1926304，如需转载请自行联系原作者
