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

发表于 | 分类于 | | 阅读次数: |
字数统计: 3,721 字 | 阅读时长 ≈ 17 分钟
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存储引擎的特点

  • 只支持insert和select操作（支持行级所和缓冲区，可以实现高并发的插入）
  • 只允许在自增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)
  • 不支持BLOG和TEXT等大字段
  • 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，不要使用混合引擎
  • 崩溃恢复
  • 存储引擎的特有特性
知识就是财富
如果您觉得文章对您有帮助, 欢迎请我喝杯水！