mysql存储引擎测试

首先安装mysql的实例数据库，employees

mysql> select table_name,data_length/1024/1024 from information_schema.tables where table_name like 'sa%';

+------------+-----------------------+
| table_name | data_length/1024/1024 |
+------------+-----------------------+
| salaries   |           95.62500000 |
+------------+-----------------------+

1 row in set (0.00 sec)

从这里可以看到表原始salaries占用的空间为95.62500000M

然后分别用不同的存储引擎来创建表，语句如下：

mysql> use test;
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> create table mytest_myisam engine=myisam as select * from employees.salaries;
Query OK, 2844047 rows affected (14.35 sec)
Records: 2844047  Duplicates: 0  Warnings: 0

mysql> create table mytest_innodb engine=innodb as select * from employees.salaries;
Query OK, 2844047 rows affected (20.97 sec)
Records: 2844047  Duplicates: 0  Warnings: 0

mysql> create table mytest_archive engine=archive as select * from employees.salaries;            
Query OK, 2844047 rows affected (9.51 sec)
Records: 2844047  Duplicates: 0  Warnings: 0

然后查看每个表现在所占用的空间大小

mysql> select table_name,data_length/1024/1024 from information_schema.tables where table_name like 'mytest_%';
+----------------+-----------------------+
| table_name     | data_length/1024/1024 |
+----------------+-----------------------+
| mytest_archive |           20.24855614 |
| mytest_innodb  |          113.64062500 |
| mytest_myisam  |           40.68441868 |
+----------------+-----------------------+
3 rows in set (0.00 sec)

从上述结果可以看到 archive存储引擎的表占用空间仅有20.24855614M,myisam引擎表占用了40.68441868M,而innodb引擎占用的空间达到113M以上