MySQL的存储引擎之Archive

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

创建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表非常适合用于冷数据归档的场景。