MySQL的存储引擎之Archive

2020年3月2日
| 没有评论
摘要：MySQL的存储引擎之Archive

Archive引擎生成特殊用途的表，这些表将大量数据存储在非常小的空间中。
MySQL 5.7中Archive引擎的特性如下

Feature Support
B-tree indexes No
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.) Yes
Cluster database support No
Clustered indexes No
Compressed data Yes
Data caches No
Encrypted data Yes (Implemented in the server via encryption functions.)
Foreign key support No
Full-text search indexes No
Geospatial data type support Yes
Geospatial indexing support No
Hash indexes No
Index caches No
Locking granularity Row
MVCC No
Replication support (Implemented in the server, rather than in the storage engine.) Yes
Storage limits None
T-tree indexes No
Transactions No
Update statistics for data dictionary Yes

创建Archive表时，MySQL在数据库目录中

  • 创建表格式文件，文件名以表名开头，扩展名为.frm。
  • 创建其它文件，均以表名开头，扩展名为.ARZ。优化操作期间可能会出现.ARN文件。
root@database-one 18:43:  [gftest]> create table testArchive(eno int,ename varchar(10),age int,sal decimal(10,2),hiredate date,deptno int) engine=archive;
Query OK, 0 rows affected (0.27 sec)

root@database-one 18:44:  [gftest]> exit
Bye
[root@database-one ~]# cd /home/mysql/gftest/
[root@database-one gftest]# ls -l testarchive*
-rw-r----- 1 mysql mysql 8806 3月   2 18:44 testarchive.ARZ
-rw-r----- 1 mysql mysql 8718 3月   2 18:44 testarchive.frm

 

大量插入数据，看看Archive表的数据压缩效果。

root@database-one 18:50:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 40 rows affected (0.01 sec)
Records: 40  Duplicates: 0  Warnings: 0

root@database-one 18:50:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 80 rows affected (0.00 sec)
Records: 80  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 160 rows affected (0.00 sec)
Records: 160  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 320 rows affected (0.11 sec)
Records: 320  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 640 rows affected (0.01 sec)
Records: 640  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 1280 rows affected (0.02 sec)
Records: 1280  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 2560 rows affected (0.03 sec)
Records: 2560  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 5120 rows affected (0.11 sec)
Records: 5120  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 10240 rows affected (0.23 sec)
Records: 10240  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> select count(*) from emp_copy;
+----------+
| count(*) |
+----------+
|    20480 |
+----------+
1 row in set (0.01 sec)

root@database-one 18:51:  [gftest]> insert into testarchive select * from emp_copy;
Query OK, 20480 rows affected (0.08 sec)
Records: 20480  Duplicates: 0  Warnings: 0

root@database-one 18:51:  [gftest]> select count(*) from testarchive;
+----------+
| count(*) |
+----------+
|    20480 |
+----------+
1 row in set (0.00 sec)

root@database-one 18:52:  [gftest]> commit;
Query OK, 0 rows affected (0.00 sec)

root@database-one 18:52:  [gftest]> exit
Bye
[root@database-one gftest]# cd /home/mysql/gftest/
[root@database-one gftest]# ls -l emp_copy* testarchive*
-rw-r----- 1 mysql mysql    8718 2月  13 22:00 emp_copy.frm
-rw-r----- 1 mysql mysql 9437184 3月   2 18:51 emp_copy.ibd
-rw-r----- 1 mysql mysql   58566 3月   2 18:52 testarchive.ARZ
-rw-r----- 1 mysql mysql    8718 3月   2 18:49 testarchive.frm

 

从上面可以看到，同样的数据量，Archive引擎使用的空间仅占InnoDB引擎的0.621%，真是天壤之别啊。

为了极致性能，Archive引擎也付出了巨大的代价，比如支持INSERT、REPLACE和SELECT，但是不支持DELETE 和UPDATE。

root@database-one 19:19:  [gftest]> delete from testarchive;
ERROR 1031 (HY000): Table storage engine for 'testarchive' doesn't have this option
root@database-one 19:19:  [gftest]> update testarchive set sal=sal+100;
ERROR 1031 (HY000): Table storage engine for 'testarchive' doesn't have this option
root@database-one 19:19:  [gftest]> select count(*) from testarchive;
+----------+
| count(*) |
+----------+
|    20480 |
+----------+
1 row in set (0.01 sec)

root@database-one 19:21:  [gftest]> insert into testarchive values(9999,'xiaowang',23,8000,'2020-3-2',10);
Query OK, 1 row affected (0.03 sec)

root@database-one 19:21:  [gftest]> select count(*) from testarchive;
+----------+
| count(*) |
+----------+
|    20481 |
+----------+
1 row in set (0.06 sec)

 

所以，Archive表非常适合用于冷数据归档的场景。

© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。

MySQL
| Tags: , ,