首先安装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以上