MySQL数据库架构--什么影响了MySQL性能-下

MySQL存储引擎之CSV

CSV存储引擎可以将csv文件作为mysql的表进行处理。存储格式就是普通的csv文件。

如果把数据存储在myisam和Innodb中,存储的文件是不能直接查看的,这两种存储引擎都是以二进制文件存储的,而csv是以文本方式存储的,csv不支持索引的,查找的时候要进行全表扫描。

数据以文本方式存储在文件中(Innodb则是二进制),会有三类文件生成

1
2
3
.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息

文件系统存储特点

  • 以CSV格式进行数据存储(逗号隔开,引号)
  • 所有的列必须都是不能为NULL的
  • 不支持索引(不适合大表,不适合在线处理)
  • 可以对数据文件直接编辑(保存文本文件内容)

演示一下不能为空:

1
2
3
# 我们新建一个csv文件,但是不指定not null 
MySQL [test]> create table mycsv(id int,c1 varchar(10),c2 char(20)) engine=csv;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

增加不为空的约束

1
2
MySQL [test]> create table  mycsv(id  int not null ,c1 varchar(10) not null,c2 char(20) not null) engine=csv;
Query OK, 0 rows affected (0.02 sec)

向表中插入数据

1
2
3
4
5
6
7
8
9
10
11
12
MySQL [test]> insert into mycsv values ( 1,'aaa','bbb'),(2,'ccc','ddd') ;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

MySQL [test]> select * from mycsv;
+----+-----+-----+
| id | c1 | c2 |
+----+-----+-----+
| 1 | aaa | bbb |
| 2 | ccc | ddd |
+----+-----+-----+
2 rows in set (0.01 sec)

我们查看文件系统下的文件

1
2
3
4
5
[root@hongshaorou ~]# cd /usr/local/mysql/var/test 
[root@hongshaorou test]# ls -lh mycsv*
-rw-r----- 1 mysql mysql 35 Mar 17 10:36 mycsv.CSM
-rw-r----- 1 mysql mysql 28 Mar 17 10:36 mycsv.CSV
-rw-r----- 1 mysql mysql 8.5K Mar 17 10:35 mycsv.frm

查看数据文件内容

1
2
3
[root@wangerxiao test]# cat mycsv.CSV
1,"aaa","bbb"
2,"ccc","ddd"

我们对文本文件进行编辑

1
2
3
1 1,"aaa","bbb"
2 2,"ccc","ddd"
3 3,"eee","fff"

然后在数据库中查看表
首先我们刷新数据表

1
2
MySQL [test]> flush tables;
Query OK, 0 rows affected (0.05 sec)

查看数据表

1
2
3
4
5
6
7
8
9
MySQL [test]> select * from  mycsv;
+----+-----+-----+
| id | c1 | c2 |
+----+-----+-----+
| 1 | aaa | bbb |
| 2 | ccc | ddd |
| 3 | eee | fff |
+----+-----+-----+
3 rows in set (0.00 sec)

我们发现在文件中修改的数据已经添加到了数据库

我们看看如果增加索引会发生什么

1
2
MySQL [test]> create index idx_id on mycsv(id);
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

最多只支持0个keys 证明不支持索引

适用场景
适合做为数据交换的中间表(能够在服务器运行的时候,拷贝和拷出文件,可以将电子表格存储为CSV文件再拷贝到MySQL数据目录下,就能够在数据库中打开和使用。同样,如果将数据写入到CSV文件数据表中,其它web程序也可以迅速读取到数据。

MySQL存储引擎之Archive

文件系统存储特点:

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

Archiv存储引擎的特点

  • 只支持insertselect操作(支持行级所和缓冲区,可以实现高并发的插入)
  • 只允许在自增ID列上加索引

Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。当数据量非常大的时候Archive的插入性能表现会较MyISAM为佳。

Archive表的性能是否可能超过MyISAM?答案是肯定的。根据MySQL工程师的资料,当表内的数据达到1.5GB这个量级,CPU又比较快的时候,Archive表的执行性能就会超越MyISAM表。因为这个时候,CPU会取代I/O子系统成为性能瓶颈。别忘了Archive表比其他任何类型的表执行的物理I/O操作都要少。

较小的空间占用也能在你移植MySQL数据的时候发挥作用。当你需要把数据从一台MySQL服务器转移到另一台的时候,Archive表可以方便地移植到新的MySQL环境,你只需将保存Archive表的底层文件复制过去就可以了。

演示实例:
建立一张表

1
2
MySQL [test]> create  table myarchive( id int auto_increment not null , c1  varchar(10),c2  char(10), key(id)) engine = archive;
Query OK, 0 rows affected (0.06 sec)

查看文件系统

1
2
3
[root@hongshaorou test]# ls  -lh  myarchi*
-rw-r----- 1 mysql mysql 8.5K Mar 17 14:47 myarchive.ARZ
-rw-r----- 1 mysql mysql 8.5K Mar 17 14:47 myarchive.frm

ARZ文件存储表内容,frm文件存储表结构(MySQL服务器层)。

我们往数据表里插入一些数据

1
2
3
4
5
6
7
8
9
10
11
12
MySQL [test]> insert into myarchive (c1,c2) values ('aa','bb'),('cc','dd');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

MySQL [test]> select * from myarchive;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | aa | bb |
| 2 | cc | dd |
+----+------+------+
2 rows in set (0.01 sec)

我们看到可以进行查询操作,现在看是否可以进行删除操作。

1
2
MySQL [test]> delete from myarchive where id = 1;
ERROR 1031 (HY000): Table storage engine for 'myarchive' doesn't have this option

更新操作

1
2
MySQL [test]> update myarchive  set c1='aaaa' where id =1;
ERROR 1031 (HY000): Table storage engine for 'myarchive' doesn't have this option

我们接着查看是否可以在非自增键上创建索引

1
2
MySQL [test]> create index idx_c1 on myarchive(c1); 
ERROR 1069 (42000): Too many keys specified; max 1 keys allowed

使用场景

日志和数据采集类应用(不支持OLTP)

MySQL存储引擎之Memory

文件系统存储特点
称HEAP存储引擎,所以数据保存在内存中(服务器重启则表的数据丢失,但是表结构是保留的,表结构保存在磁盘文件中,而表的内容是存储在内存中)

功能特点

  • 支持HASH索引(等值查询)和BTree索引(范围查找)(默认HASH)
  • 所有字段都为固定长度 varchar(10) = char(10)
  • 不支持BLOGTEXT等大字段
  • Memory存储引擎使用表级锁
  • 表的最大大小由max_heap_table_size参数决定(默认16M,对存在的表修改是无效的)

演示实例

首先创建表

1
2
3
4
MySQL [test]> create table mymemory (id int,c1 varchar(10),c2 char(10), c3 text ) engine = memory;
ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns
MySQL [test]> create table mymemory (id int,c1 varchar(10),c2 char(10) ) engine = memory;
Query OK, 0 rows affected (0.02 sec)

我们能够看到,表是不支持TEXT字段的

我们再看下文件系统

1
2
[root@wangerxiao test]# ls -lh  mymemo* 
-rw-r----- 1 mysql mysql 8.5K Mar 17 15:25 mymemory.frm

只有一个保存表结构的文件,不存在存储数据的文件。

下面我们再看下表的索引
新建两个索引

1
2
3
4
5
6
7
8
MySQL [test]> create index idx_c1 on mymemory(c1);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 创建索引的时候 指定索引类型
MySQL [test]> create index idx_c2 using btree on mymemory(c2);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

我们查看当前索引类型

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
MySQL [test]> show index from mymemory \G
*************************** 1. row ***************************
Table: mymemory
Non_unique: 1
Key_name: idx_c1
Seq_in_index: 1
Column_name: c1
Collation: NULL
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: HASH
Comment:
Index_comment:
*************************** 2. row ***************************
Table: mymemory
Non_unique: 1
Key_name: idx_c2
Seq_in_index: 1
Column_name: c2
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

存在两个索引,一个为默认的,一个是指定的BTree。

接下来我们查看表的状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MySQL [test]> show table status like 'mymemory'\G
*************************** 1. row ***************************
Name: mymemory
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 86
Data_length: 0
Max_data_length: 7799082
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-03-17 15:30:16
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

Memory存储引擎表和临时表的区别

临时表分两类:

  1. 系统使用临时表(查询优化器创建)

  2. create temporary table 建立的临时表

无论哪种表,只有当前session是可见的。

系统使用临时表又分为两类:超过限制使用Myisam临时表,未超过限制使用Memory表。

而Memory表是所有线程都可以使用的。

使用场景

  • 用于查找或者是映射表,例如邮编和地区的对应表

  • 用于保存数据分析中产生的中间表

  • 用于缓存周期性聚合数据的结果表

注意一点是:Memory数据易丢失,所以要求数据可再生

memory存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表,而且所有数据也放在内存中。这些特性都与InnoDB,MyISAM存储引擎不同。
OK,这里我们讲解一些memory存储引擎的文件存储形式,索引类型,存储周期和优缺点。

每个基于memory存储引擎的表实际对应一个磁盘文件,该文件的文件名与表名相同,类型为frm类型。该文件只存储表的结构,而其数据文件,都是存储在内存中的,这样有利于对数据的快速的处理,提高整个表的处理效率。

值得注意的是:服务器需要有足够的内存来维持memory存储引擎的表的使用。如果不需要了,可以释放这些内存,甚至可以删除不需要的表。

Memory存储引擎默认使用哈希(HASH)索引,其速度比使用B型树(BTREE)索引快。如果我们需要使用B型树索引,可以在创建索引时选择使用。

这里来整理一个小的技巧:

Memory存储引擎通常很少用到,至少我是没有用到过。因为Memory表的所有数据都是存储在内存上的,如果内存出现异常会影响到数据的完整性。

如果重启机器或者关机,表中的所有数据都将消失,因此,基于Memory存储引擎的表的生命周期都比较短,一般都是一次性的。

Memory表的大小是受到限制的,表的大小主要取决于2个参数,分别是max_rows和max_heap_table_size。其中,max_rows可以在创建表时指定,max_heap_table_size的大小默认为16MB,可以按需要进行扩大。

因此,其基于内存中的特性,这类表的处理速度会非常快,但是,其数据易丢失,生命周期短。基于其这个缺陷,选择Memory存储引擎时需要特别小心。

MySQL存储引擎之Federated

Federated可以不使用复制技术,直接远程服务器主机的数据表。

实现原理
通过创建存储引擎为Federated 的表来实现远程共享服务器表数据。
Federated:能够将多个分离(不在同一台服务器上的机器)的MySQL服务器链接起来,从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

特点

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

如何使用
默认禁止,启用需要在启动时增加federated参数

1
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

演示实例:在本地模拟远程本地
查看是否支持federated

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MySQL [test]> show  engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

不支持federated引擎

编辑配置文件 使其支持

1
2
3
4
[root@hongshaorou ~]# vim /etc/my.cnf
[root@hongshaorou ~]# more /etc/my.cnf
[mysqld]
federated

重启服务

1
[root@hongshaorou ~]#  systemctl restart  mysqld

再次查看会有federated引擎.

image-20190114173829476

演示示例

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
mysql> create database local;
Query OK, 1 row affected (0.00 sec)

mysql> create database remote;
Query OK, 1 row affected (0.00 sec)

mysql> use remote;
Database changed

mysql> create table remote_fed(id int auto_increment not null, c1 varchar(10) not null default '', c2 char(10) not null default '', primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> show create table remote_fed\G
*************************** 1. row ***************************
Table: remote_fed
Create Table: 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=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> insert into remote_fed (c1,c2) values ('aaa','bbb'), ('ccc', 'ddd');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from remote_fed;
+----+-----+-----+
| id | c1 | c2 |
+----+-----+-----+
| 1 | aaa | bbb |
| 2 | ccc | ddd |
+----+-----+-----+
2 rows in set (0.00 sec)

# 授权
mysql> grant select,update,insert,delete on remote.remote_fed to fred_link@'127.0.0.1' identified by '@#loveLOVE520';
Query OK, 0 rows affected, 1 warning (0.00 sec)


# 在 local 库 创建 federated 引擎的数据表
mysql> use local;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> create table remote_fed(id int auto_increment not null, c1 varchar(10) not null default '', c2 char(10) not null default '', primary key(id)) engine=federated connection='mysql://fred_link:#loveLOVE520@127.0.0.1:3306/remote/remote_fed';
ERROR 1050 (42S01): Table 'remote_fed' already exists
mysql> rename table remote_fed to local_fed;
Query OK, 0 rows affected (0.00 sec)

# 查询数据库
mysql> select * from local_fed;
+----+-----+-----+
| id | c1 | c2 |
+----+-----+-----+
| 1 | aaa | bbb |
| 2 | ccc | ddd |
+----+-----+-----+
2 rows in set (0.02 sec)

# 删除一条记录
mysql> delete from local.local_fed where id = 2;
Query OK, 1 row affected (0.02 sec)

# 切换到远程库
mysql> use remote;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from remote_fed;
+----+-----+-----+
| id | c1 | c2 |
+----+-----+-----+
| 1 | aaa | bbb |
+----+-----+-----+
1 row in set (0.00 sec)

我们在local库中可以直接使用remote库。

1
2
3
4
5
[root@hongshaorou ~]# cd /var/lib/mysql/local/
[root@hongshaorou local]# ll
total 16
-rw-r----- 1 mysql mysql 65 Jan 14 17:41 db.opt
-rw-r----- 1 mysql mysql 8608 Jan 14 20:23 local_fed.frm

在本地只保留了远程数据库的数据结构。

推荐阅读文章Mysql基于FEDERATED存储引擎的远程表使用

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

如何选择正确的存储引擎

参考条件:

  • 首先我们要考虑应用是否需要事务支持
  • 在线热备方案 Innodb,不要使用混合引擎
  • 崩溃恢复
  • 存储引擎的特有特性
知识就是财富
如果您觉得文章对您有帮助, 欢迎请我喝杯水!