From mysql to MongoDB（MongoDB2011北京交流会）

from MySQL

to

mongoDB
nightsailer
March 3, 2011 / Mongo Beijing

About.me

@nightsailer
nightsailer # gmail.com
http://nightsailer.com/
http://github.com/nightsailer

Mysql data type
char / varchar
Int/BigInt
Float/Double/Decimal
Blob/Text
.....

BSON [bee · sahn]

Byte Object Id
Int32/Int64 Array
Double Boolean
Javascript code
Embed document
.....

http://bsonspec.org/

BSON

Object Id

0 1 2 3 4 5 6 7 8 9 10 11
utc time machine pid inc/random

Enforce collection unique: _id
Should global/cluster unique

BSON

Date / Timestamp

Perl: DateTime / Mongo::Timestamp
PHP: MongoDate / MongoDatetime

* Date Int64

BSON

Binary Data
Perl: string
my $default_avatar = "xFFxFExFF";
$collection->insert({"avatar_file" => $string});

PHP:MongoBinData

* 4Mb/8Mb(1.8+)

Create Database .... No!
Create Table .... No!
Alter Table .... No!

Table => Collection
Row => Document
Embed document

Embed document : Array/List/Hash

_id
Collection

Array/List

Object Id => perl: MongoDB::OID php: MongoId

Insert/Save
perl:
$db->user->create({name=>’ns’});
$db->user->save({_id=>5,name=>’ns’});

SQL Injection .

Update
>db.user.update({_id:5},{name:’ns’,‘email’:‘xxx’});

mysql

Upsert

>db.user.update({_id:5},{_id:5,name:’ns’,email:‘xxx’},true)

_id,

In-place Updates
$inc $push $pushAll $pull $pullAll $addToSet
$set
....
Atomic
modiﬁer
Array/Hash $set)

Upsert & modifier
$modifier _id

>db.art_track.update({ art_id:25,d:20110303 },
{ $addToSet:{follower:234},$inc:{views:1}},true);

> db.art_track.find()
{ "_id" : ObjectId("4d6df20cb7fc9b3c1329c917"), "art_id" : 25, "d" : 20110303, "follower" :
[ 234 ], "views" : 2 }

Select => Find
>db.user.find({city:‘beijing’})
.sort({created_on:-1})
.limit(20);

Cursor
when ﬁnd & run command
cursor->next

OP_QEURY OP_GETMORE

cursor

Order by => Sort
Sort Limit Cursor

Query Modiﬁer
:
$ne, $in, $nin, $mod, $all, $size, $exists, $type, ..
$lt, $lte, $gt, $gte, $ne,
...

Command
:
count,map/reduce, group,...
>db.$cmd.findOne(cmd_query_obj);

db.$cmd.findOne(‘user’) == db.user.count()

FindAndModify
db.runCommand( { findAndModify : <collection>,
query: { filter },
sort : { },
update: {},
field: {},
new/remove: true/false
});

FindAndModify
#perl
my $job = db_find_and_modify {
query => { state => 0 },
update => { '$set' => { state => 1, ts =>
time } },
sort => { _id => 1 }
};
# Mysql Auto increment
<?php
$db->command(array(
‘findandmodify’=>‘sequences’,
‘query’ => array(‘_id’=>‘user_id’),
‘update’ => array(‘$inc’=>array(‘val’=>1)),
‘new’ => true,
‘upsert’ => true,
));
?>
*update/sort, !

List commands
> db.runCommand({listCommands:1})

Quick reference card
http://www.10gen.com/reference

Index
db.foo.ensureIndex({a:1})
db.foo.ensureIndex({a:1,b:-1})

collection 64 index
query 1 index

Multikeys
Array/Object

>db.art.ensureIndex({tags:1});
>db.art.find({tags:’ ’}).sort({created_on:-1}).limit(10);

>db.user.ensureIndex({‘profile.age’:1});
>db.user.find({"profile.age":{$gte:18}});

Special index / 1.8+
Sparse Indexes
>db.user.ensureIndex({sina_account:1},{sparse:true});
>db.user.findOne({sina_account:‘xxx’});

Covered Indexes
>db.user.ensureIndex({_id:1,passport:1,state:1});
>db.user.findOne({passport:‘xxx’},{_id:0,passport:
1,state:1});

Like %mongoDB% ?

> db.count({title:/mongodb/i});

Regex => index

collection
db.fulltext.save({
index_name: [ word1,word2]
{ }
...
})

index_name
db.fullext.find({index_name:{$all:
[w1,w2,w3]}})
.sort({updated_on:-1})

Sum/Group by => ?
Group command + db
Map/Reduce: good, sharding

Alternate approach
Live: counter ﬁeld =>$inc
Backend service:
=> Gearman: workers

ORM,easy.
PHP: Zend/Symfony/CI ...
Ruby:Mongoid

Perl: Mongoose MongoDBx

<?php
// model
class Lgk_Model_Art extends Lgk_Core_Model_Base {
protected $collection = "art";
protected $schema = array(
'category_id' => 0,
'tags' => array(),
'fav_tags' => array(),
....
);
protected $required_fields = array('user_id','name');
protected $int_fields = array('user_id','category_id','deleted','published','approved','private');
protected $created_timestamp_fields = array('created_on','updated_on');
protected $joins = array(
'user' => array('user_id' => 'Lgk_Core_Model_User'),
'assets' => array('assets' => 'Lgk_Core_Model_Asset'),
'category' => array('category_id' => 'Lgk_Core_Model_Category'),
'thumbnail_asset' => array('thumb_asset_id' => 'Lgk_Core_Model_Asset'),
);
protected function extra_extend_model_row(&$row) {
}
?>
//
public function load_joins($row) {
...
foreach ($this->joins as $attribute => $definition) {
list($pk_name,$model_class) = each($definition);
if (isset($row[$pk_name])) {
if (is_array($row[$pk_name])) {
$row[$attribute] = &DoggyX_Model_Mapper::load_model_list($row[$pk_name],$model_class);
}
else {
$row[$attribute] = &DoggyX_Model_Mapper::load_model($row[$pk_name],$model_class);
}
}
}
return $row;
}
}

GridFS
Just speciﬁcation.
Not best but good.

Write-once, read many
db.assets:{ db.fs.file: {
_id:ObjectId, _id: ObjectId,
file_id: ObjectId, r: (ref counter)
thumb_id:ObjectId, hash:‘xxxxx’
... }
}

db.fs.files 1
r
files file_id

Plack/Twiggy AnyEvent

Nginx Todo Node.js

Varnish/Squid proxy store

Plack/Starman /

ETag: ﬁle _id

Replication
local.system.replset
local.oplog.rs => oplog/capped

* local.usr.xxx => collection

collection

mySQL: mmm

ReplicaSet
1 Primary + 2 Secondary + 1
Arbiter

* Master/Slave

“SlaveOk”
<?php
$con =new Mongo(‘mongodb://s1:27017’,array
(‘replicaSet’=>true);
$con->setSlaveOkay(true);
?>
#perl
my $con = MongoDB::Connection->new(host=>‘mongodb://
localhost’,w =>2,find_master => 1);
$MongoDB::Cursor::slave_okay = 1;
my $cursor = $con->user->find;
$cursor->slave_okay = 1;

Driver

Auto Shard
: : db

: mongos
shard_key: ,
counting

VPN

=> VPN

& =>

oplog /replay,

db.repairDatabase,

: primary, !
oplog

kill -9 or = Crash!

Durability?
--syncdelay ? (60=>5)
‘w’
db.runCommand( { getlasterror : 1 , w : 2 } )

ReplicaSet 2 secondary
-dur upgrade to 1.8!

Type: string vs int

“2” != 2

<php? $user_id = (int) $user_id; ?>

OOM Killer

> db.serverStatus()

Swap > Memory

Ordered hash

Perl: IxHash
({a=>1, b=>-1}) => IxHash->new

Ruby (<1.9): BSON::OrderedHash
PHP: Array is OK

Cursor
last_row_id = ObjectId(‘....’);
db.activity_stream->find({_id:{$lt: last_row_id },
user_id:20 } ).sort( {_id:-1} ).limit(10);

PHP/5.2.x: front-end
Perl 5.2.10+ / Plack / Gearman:
Daemon, large-file upload, data
process, job queue service ...

MongoDB :
session store
business data
GridFS: Media files (user uploaded
files, thumbnails ..)

use MongoX (host=>‘mongodb://127.0.0.1’,db=>‘test’);
use_collection 'task_queue';
db_update { state => 1, queue => 'sina_tweet' }, {
'$set' => { state => 0 },
'$inc' => { tries => 1 },
},{ multiple => 1 };
db_remove { state => 1, queue => 'sina_tweet', 'tries' =>
{ '$gt' => 3 } };
db_inc {}

//
my $cnt = 0;
for_connections {
for_dbs {
for_collections {
$cnt += db_count;
} ‘art’
} ‘db1’,‘db2’
} ‘c_1’ ,‘c_2’,‘c_arch1’,‘c_arch2’;

http://github.com/nightsailer/mongo-x/

:
http://czone.chinavisual.com/
2009-6 rebuild on mongodb
< 1.0

http://cvurl.cn/
:
http://tu.chinavisual.com/

mongoDB:
: GridFS: hight resolution
http://down.chinavisual.com/
images,photos/medium
size ﬁles(60mb~500mb)

PHP 开发师
job-php@chinavisual.com
Modern Perl 开发师
job-perl@chinavisual.com

mongoDB / Git / Catalyst /
Ubuntu / Xiapian / Nginx / Plack /
Moose / Node.js / Titanium / Redis

From mysql to MongoDB（MongoDB2011北京交流会）

More Related Content

What's hot

Viewers also liked

Similar to From mysql to MongoDB（MongoDB2011北京交流会）

Recently uploaded

From mysql to MongoDB（MongoDB2011北京交流会）