mysql引擎测试_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以上