关于mysql archive存储引擎(下面SQL测试待研究)
政府还有一个让数据库专家摊上更多事情的职能,就是安全控制和数据审计。那些管理着海量数据仓库的企业官员常常得回答诸如“何人何时修改了什么”或者“何人何时查看了什么”这样的提问。那些拥有数以千计的员工,开展着不计其数的业务的企业,每天都会产生出大量的日志记录数据,而且必须将其好好保存。为了帮助数据库专家应对数据爆炸的挑战,MySQL5.0引入了一种新的数据存储引擎,叫做Archive。这个先进的数据管理工具,让MySQL的专家们拥有了处理和管理海量数据的新式武器。
Archive引擎作用:为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案,区别于InnoDB、MyISAM提供压缩功能,没有索引。
关于Archive存储引擎的介绍和性能测试的文章:http://dev.mysql.com/tech-resources/articles/storage-engine.html
不喜欢英文的童鞋可以看这篇翻译过来的文章(推荐,翻译的不错):http://guangxin.name/2009/04/mysql50-archive-1.html
根据英文的测试结论来看,Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。当数据量非常大的时候Archive的插入性能表现会较MyISAM为佳。
Archive表的性能是否可能超过MyISAM?答案是肯定的。根据MySQL工程师的资料,当表内的数据达到1.5GB这个量级,CPU又比较快的时候,Archive表的执行性能就会超越MyISAM表。因为这个时候,CPU会取代I/O子系统成为性能瓶颈。别忘了Archive表比其他任何类型的表执行的物理I/O操作都要少。
较小的空间占用也能在你移植MySQL数据的时候发挥作用。当你需要把数据从一台MySQL服务器转移到另一台的时候,Archive表可以方便地移植到新的MySQL环境,你只需将保存Archive表的底层文件复制过去就可以了。
本着怀疑一切的精神,本人进行了如下的测试:
①建立一个iplog的表:
mysql> create table iplog(id int auto_increment not null primary key,userid int,ip char(15),visit_time datetime) engine=innodb;
②使用python脚本插入50w数据:
#!/usr/bin/mysql
import MySQLdb
conn = MySQLdb.connect(host="localhost",user="root",passwd="asdf",db="test",unix_socket="/data/mysql_3306/mysql.sock")
cursor = conn.cursor()
for i in range(0,500000):
sql = "insert into iplog(userid,ip,visit_time) values(%s,'127.0.0.1',now())"%i
cursor.execute(sql)
cursor.close()
conn.commit()
conn.close()
③分别创建iplog对应的archive、InnoDB、MyISAM对应表格并插入数据
mysql> create table iplog_archive engine=archive as select * from iplog;
Query OK, 500000 rows affected (2.73 sec)
Records: 500000 Duplicates: 0 Warnings: 0
mysql> create table iplog_myisam engine=myisam as select * from iplog;
Query OK, 500000 rows affected (1.39 sec)
Records: 500000 Duplicates: 0 Warnings: 0
mysql> create table iplog_innodb engine=innodb as select * from iplog;
Query OK, 500000 rows affected (4.78 sec)
Records: 500000 Duplicates: 0 Warnings: 0
④比较它们的大小
mysql> select table_name,engine,ROUND(data_length/1024/1024,2) total_size_mb,table_rows from information_schema.tables
-> where table_schema = 'test' and table_name like 'iplog_%';
+---------------+---------+---------------+------------+
| table_name | engine | total_size_mb | table_rows |
+---------------+---------+---------------+------------+
| iplog_archive | ARCHIVE | 2.10 | 500000 |
| iplog_innodb | InnoDB | 30.56 | 500289 |
| iplog_myisam | MyISAM | 29.56 | 500000 |
+---------------+---------+---------------+------------+
3 rows in set (0.01 sec)
⑤测试select性能:
mysql> select * from iplog_archive where userid=250000;
+--------+--------+-----------+---------------------+
| id | userid | ip | visit_time |
+--------+--------+-----------+---------------------+
| 750001 | 250000 | 127.0.0.1 | 2010-02-01 10:54:20 |
+--------+--------+-----------+---------------------+
1 row in set (0.31 sec)
mysql> select * from iplog_innodb where userid=250000;
+--------+--------+-----------+---------------------+
| id | userid | ip | visit_time |
+--------+--------+-----------+---------------------+
| 750001 | 250000 | 127.0.0.1 | 2010-02-01 10:54:20 |
+--------+--------+-----------+---------------------+
1 row in set (0.48 sec)
mysql> select * from iplog_myisam where userid=250000;
+--------+--------+-----------+---------------------+
| id | userid | ip | visit_time |
+--------+--------+-----------+---------------------+
| 750001 | 250000 | 127.0.0.1 | 2010-02-01 10:54:20 |
+--------+--------+-----------+---------------------+
1 row in set (0.10 sec)
⑥测试insert性能():
使用python脚本再插入50w数据,查看插入性能,脚本如下,没有写的很复杂,测试InnoDB或者MyISAM要修改代码
#!/usr/bin/mysql
import MySQLdb
conn = MySQLdb.connect(host="localhost",user="root",passwd="asdf",db="test",unix_socket="/data/mysql_3306/mysql.sock")
cursor = conn.cursor()
for i in range(500001,1000000):
sql = "insert into iplog_archive(userid,ip,visit_time) values(%s,'127.0.0.1',now())"%i
cursor.execute(sql)
cursor.close()
conn.commit()
conn.close()
archive
real 1m30.467s
user 0m22.270s
sys 0m12.670s
InnoDB
real 0m48.622s
user 0m18.722s
sys 0m9.322s
MyISAM
real 1m32.129s
user 0m13.183s
sys 0m5.624s
测试结果是archive可以大规模的减少空间减少%93(这个与表有关系),select性能介于MyISAM和InnoDB之间,大规模insert时效率比MyISAM和InnoDB高,至于原因“因为这个时候,CPU会取代I/O子系统成为性能瓶颈。别忘了Archive表比其他任何类型的表执行的物理I/O操作都要少。”
欢迎大家共同探讨,包括测试用例以及任何想法。