MySQL的键值存储以及与MongoDB的对比

jieforest

So, having implemented MongoDB and having gotten used to it and even starting to like it a fair bit, I also started seeing some other things.

Like, Why can't MySQL be used for this? I mean MySQL is a bit more mature than MongoDB and should be better at handling, say disk-IO. On the other hand, MongoDB is newer. One thing I already knew was that MongoDB really needs to be in-memory to a large extent, when it hits the disk, performance goes downhill fast, so here MySQL is better.

But when we DO have enough RAM (and as we use several MongoDB datastores for different purposes, and we are on Amazon, where you can get respectable amounts of RAM, this is usually the case) what happens? If I let MySQL use RAM it can get so I don't get any disk-IO for reads at all, will it be as fast as MongoDB?

Or maybe even faster. And among the MySQL Storage Engines, the one mostly targeted as a Key Value Store is NDB? How's that going to work.

jieforest

Test environment

So, to test all this, but to make sure I wasn't affected by Amazons ups-and-downs, I pulled one of our MongoDB datastores, some 105.000.000 rows of real world data. Initially, my plan was to use the full MongoDB datastore, but I had to exclude some fields as these were VARCHAR and putting them on Disk with NDB was causing just too much disk-I/O, as NDB stores disk data as fixed length (so a UTF-8 VARCHAR(256) field occupies some 768 bytes). I ended up with a table schema like this:

1. 1.CREATE TABLE `keyvalue` (
   
2. 2.`id` bigint(20) NOT NULL,
   
3. 3.`value1` int(11) DEFAULT NULL,
   
4. 4.`value2` double DEFAULT NULL,
   
5. 5.PRIMARY KEY (`id`)
   
6. 6.)

复制代码

In the case of MongoDB, the id column was used as _id, which is MongoDBs way of saying