HBase VS MySQL 性能测试

HBase 1.0.0
MySQL 5.6

存储情况

MYISAM存储空间最小、其次InnoDB、HBase启用压缩单副本大小略大

66.8 M  200.4 M  /hbase/data/test/content
34.9 M  104.8 M  /hbase/data/test/content_compress

MYISAM具有索引缓存,可通过以下方法禁用:

echo 3 > /proc/sys/vm/drop_caches
flush tables;
SELECT SQL_NO_CACHE * FROM cailiao WHERE id = 100000;  

百万级别主键查询

MySQL 20-30ms,Hbase 20-200ms, MySQL快半个数量级

千万级别主键查询

Hbase 性能更好,该对比是在MySQL关闭索引缓存的情况下,MySQL的索引缓存非常牛逼,一旦索引已被缓存,查询一般是几ms:

/*[10:42:09][ 563 ms]*/ SELECT SQL_NO_CACHE * FROM tableinfo WHERE id = 1543 LIMIT 0, 1000;
/*[10:42:40][ 640 ms]*/ SELECT SQL_NO_CACHE * FROM tableinfo WHERE id = 1000000 LIMIT 0, 1000;
/*[10:43:15][ 438 ms]*/ SELECT SQL_NO_CACHE * FROM tableinfo WHERE id = 5000000 LIMIT 0, 1000;
/*[10:43:46][ 485 ms]*/ SELECT SQL_NO_CACHE * FROM tableinfo WHERE id = 10000000 LIMIT 0, 1000;
scan 'tableinfo', {COLUMNS => 'data', LIMIT => 1, STARTROW => '1543' ,CACHE_BLOCKS=>false}
1 row(s) in 0.1360 seconds
scan 'tableinfo', {COLUMNS => 'data', LIMIT => 1, STARTROW => '1000000' ,CACHE_BLOCKS=>false}
1 row(s) in 0.1160 seconds
scan 'tableinfo', {COLUMNS => 'data', LIMIT => 1, STARTROW => '5000000' ,CACHE_BLOCKS=>false}
1 row(s) in 0.0790 seconds
scan 'tableinfo', {COLUMNS => 'data', LIMIT => 1, STARTROW => '10000000' ,CACHE_BLOCKS=>false}
1 row(s) in 0.0870 seconds
tableinfo
get 'tableinfo', '1543'id
25 row(s) in 0.2690 seconds
get 'tableinfo', '1000000'
25 row(s) in 0.0490 seconds
get 'tableinfo', '5000000'
25 row(s) in 0.0490 seconds
get 'tableinfo', '10000000'
25 row(s) in 0.0390 seconds

千万级别插入:MySQL慢一个数量级

/*[11:04:12][ 125 ms]*/ INSERT INTO `test`.`tableinfo`(`id`,`InfoUrl`,`ApplyNo`,`InternationCat`,`ApplyDate`,`ApplyNameC`,`ApplyAddC`,`ApplyNameE`,`ApplyAddE`,`ImageAddress`,`Service`,`Same`,`FirstDate`,`RegisterDate`,`CheckDate`,`NoticeDate`,`SpecialDate`,`LastDate`,`Internation`,`Defanced`,`FriendName`,`Color`,`Type`,`Common`,`Status`,`Workflow`) VALUES ( '1','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0');
/*[11:04:30][ 125 ms]*/ INSERT INTO `test`.`tableinfo`(`id`,`InfoUrl`,`ApplyNo`,`InternationCat`,`ApplyDate`,`ApplyNameC`,`ApplyAddC`,`ApplyNameE`,`ApplyAddE`,`ImageAddress`,`Service`,`Same`,`FirstDate`,`RegisterDate`,`CheckDate`,`NoticeDate`,`SpecialDate`,`LastDate`,`Internation`,`Defanced`,`FriendName`,`Color`,`Type`,`Common`,`Status`,`Workflow`) VALUES ( '100000000','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0');
/*[11:05:01][  94 ms]*/ INSERT INTO `test`.`tableinfo`(`id`,`InfoUrl`,`ApplyNo`,`InternationCat`,`ApplyDate`,`ApplyNameC`,`ApplyAddC`,`ApplyNameE`,`ApplyAddE`,`ImageAddress`,`Service`,`Same`,`FirstDate`,`RegisterDate`,`CheckDate`,`NoticeDate`,`SpecialDate`,`LastDate`,`Internation`,`Defanced`,`FriendName`,`Color`,`Type`,`Common`,`Status`,`Workflow`) VALUES ( '100000001','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0');
/*[11:09:46][ 110 ms]*/ INSERT INTO `test`.`tableinfo`(`id`,`InfoUrl`,`ApplyNo`,`InternationCat`,`ApplyDate`,`ApplyNameC`,`ApplyAddC`,`ApplyNameE`,`ApplyAddE`,`ImageAddress`,`Service`,`Same`,`FirstDate`,`RegisterDate`,`CheckDate`,`NoticeDate`,`SpecialDate`,`LastDate`,`Internation`,`Defanced`,`FriendName`,`Color`,`Type`,`Common`,`Status`,`Workflow`) VALUES ( NULL,'0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0');

put 'tableinfo', '100000001', 'data:Color', '6'
0 row(s) in 0.0060 seconds
put 'tableinfo', '110000001', 'data:Color', '6'
0 row(s) in 0.0100 seconds
put 'tableinfo', '100000001', 'data:ApplyNo', '6'
0 row(s) in 0.0080 seconds

亿级别查询