MySQL数据库常见存储引擎(一)

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介:

熟悉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      |       |
+ -------+----------+------+-----+---------+-------+
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: 0
 
mysql>  insert   into   csv1  values (9, 'linux' ),(8, "MYSQL" );
Query OK, 2  rows  affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql>  select   *   from  csv1;
+ ----+-------+
| id |  name   |
+ ----+-------+
|  3 | linux |
| 20 | MYSQL |
|  9 | linux |
|  8 | MYSQL |
+ ----+-------+
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: 0
 
mysql>  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: 0
 
mysql>  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: 0
 
mysql>  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: 0
 
mysql>  select  from  black ;
Empty  set  (0.00 sec)
 
多次测试发现真的这么神奇,插入什么都成功了,但就是找不到数据,很神奇的存储引擎吧?看看

    多次测试,结果就是那么神奇,插入都是成功的,但就是找不到数据,这个存储引擎神奇吧,看看这个神奇的存储引擎有哪些用途呢??

1、尽管BLACKHOLE存储引擎不会保存数据,但是启用binlog,那么执行得SQL语句还是实际上被记录,也就是说能复制到SLAVE端。如下图:



wKiom1kap4qAYX0_AAEP2RikXpc506.png

  结合复制特性中的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,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 缓存 关系型数据库
MySQL 存储引擎
MySQL 存储引擎
47 6
|
5天前
|
存储 关系型数据库 MySQL
MySQL InnoDB存储引擎的优点有哪些?
上述提到的特性和优势使得InnoDB引擎非常适合那些要求高可靠性、高性能和事务支持的场景。在使用MySQL进行数据管理时,InnoDB通常是优先考虑的存储引擎选项。
10 0
|
1月前
|
存储 关系型数据库 MySQL
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
|
1月前
|
存储 算法 关系型数据库
【MySQL技术内幕】5.7- InnoDB存储引擎中的哈希算法
【MySQL技术内幕】5.7- InnoDB存储引擎中的哈希算法
22 1
|
1月前
|
存储 SQL 关系型数据库
MYSQL--(1.存储引擎 *2.事务*)
MYSQL--(1.存储引擎 *2.事务*)
|
1月前
|
存储 关系型数据库 MySQL
【MySQL技术内幕】1-MySQL体系结构和存储引擎
【MySQL技术内幕】1-MySQL体系结构和存储引擎
26 1
|
22天前
|
存储 关系型数据库 MySQL
MySQL存储引擎
MySQL存储引擎
14 0
|
1月前
|
存储 安全 关系型数据库
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库——存储引擎(2)-存储引擎特点(InnoDB、MyISAM、Memory)、存储引擎选择
MySQL数据库——存储引擎(2)-存储引擎特点(InnoDB、MyISAM、Memory)、存储引擎选择
32 1