TokuDB和InnoDB的读写分析与比较
- - CSDN博客数据库推荐文章我们知道,在MySQL单机版本里面最流行的也是唯一支持全事务的引擎为INNODB. 其特点是数据本身是用B-TREE来组织,数据本身即是庞大的根据主键聚簇的B-TREE索引. 所以在这点上,写入速度就会有些降低,因为要每次写入要用一次IO来做索引树的重排. 特别是当数据量本身比内存大很多的情况下,CPU本身被磁盘IO纠缠的做不了其他事情了.
1. INNODB. 对应的参数: innodb_buffer_pool_size=32M bulk_insert_buffer_size=20M query_cache_size = 0 导入性能:(InnoDB在这里慢在CPU一直忙于IO置换。) mysql> load data infile '/tmp/t3_push.csv' into table t3_push; Query OK, 955527 rows affected (30 min 44.03 sec) Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0 读性能:(读的性能还是很好的,这里用到5.6的ICP以及MRR特性。) mysql> select count(*) from t3_push where rank1 < 20 and rank2 < 30; +----------+ | count(*) | +----------+ | 49 | +----------+ 1 row in set (0.06 sec) 调大 innodb_buffer_pool=128M mysql> load data infile '/tmp/t3_push.csv' into table t3_push; Query OK, 955527 rows affected (38.72 sec) Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0 调大后,其实导入性能还是不错的。
2. TokuDB. (5.5.30-tokudb-7.1.0-e-log TokuDB Enterprise Server (GPL) ) 对应的参数: tokudb_cache_size=32M tokudb_loader_memory_size=20M query_cache_size = 0 写性能:(这里IO次数很少,所以导入速度很快。) mysql> load data infile '/tmp/t3_push.csv' into table t3_push; Query OK, 955527 rows affected (19.73 sec) Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0 读性能:(读的速度比INNODB稍微慢了些。) mysql> select count(*) from t3_push where rank1 < 20 and rank2 < 30; +----------+ | count(*) | +----------+ | 49 | +----------+ 1 row in set (0.54 sec) mysql> select count(*) from t3_push where rank1 < 200 and rank2 < 300; +----------+ | count(*) | +----------+ | 5759 | +----------+ 1 row in set (4.13 sec) 但是TokuDB可以给二级索引变聚簇,所以这点上如果只读的话,还是会比InnoDB快。 给列rank2 加聚簇索引, mysql> alter table t3_push add clustering index idx_rank2(rank2); Query OK, 0 rows affected (6.79 sec) Records: 0 Duplicates: 0 Warnings: 0 现在所有的基于索引idx_rank2 的查询都是瞬间的。 mysql> select count(*) from t3_push where rank1 < 20 and rank2 < 30; +----------+ | count(*) | +----------+ | 49 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from t3_push where rank1 < 200 and rank2 < 300; +----------+ | count(*) | +----------+ | 5759 | +----------+ 1 row in set (0.01 sec)