啥也没写
分类： Mysql/postgreSQL
2011-04-11 13:04:12
喜欢Mysql，不仅仅是因为他简单的使用，更深一层次的是因为他的开源、他的插入式引擎及更优秀的plugin！从队列式存储引擎Q4M()到memcache 的UDF(http://hi.baidu.com/ytjwt/blog/item/5fc8303f226c542f71cf6c3c.html) 到本文要说到的NoSQL for MySQL，去年的某一天，一朋友跟我说，nosql性能有多好多好时，我说，如果提取像k/v形式的数据，假设：用主键查询一个数据，你觉得性能会怎样 呢？其实当时我也知道，肯定是比不过memcache之类的cache，因为，mysql有一系列的认证，一系列的语法、词法分析！
前段一个偶然的时间里，下载percona-server突然发现一个名叫HandlerSocket的东东，一时好奇，google一把，发现这不就是我一直想要的在mysql里实现nosql的东西吗?兴奋之余，也动手安装及使用了一把，下面把我的步骤列出来!
主要参考：http://whitesock.javaeye.com/blog/811339
1：安装
HandlerSocket老简单了，只需要5.1版本以上的source 安装版本跟二进制版本，因为5.5GA了，所以我选择了5.5.8版本安装
安装HandlerSocket
$ ./autogen.sh
$ ./configure --with-mysql-source=../mysql-5.5.8/ --with-mysql-bindir=/usr/local/mysql55/bin/ --with-mysql-plugindir=/usr/local/mysql55/lib/plugin/
$ make
$ make install
2：配置
在正式使用前，我们必须在mysql配置文件添加如下配置
[mysqld]
loose_handlersocket_port = 9998
# the port number to bind to (for read requests)
loose_handlersocket_port_wr = 9999
# the port number to bind to (for write requests)
loose_handlersocket_threads = 16
# the number of worker threads (for read requests)
loose_handlersocket_threads_wr = 1
# the number of worker threads (for write requests)
open_files_limit = 65535
# to allow handlersocket accept many concurrent connections, make open_files_limit as large as possible.
重启mysql后，登录mysql，执行
install plugin handlersocket soname 'handlersocket.so';
这个时候，我们就能看到两个新端口了
netstat -lnp|grep 999
tcp 0 0 0.0.0.0:9998 0.0.0.0:* LISTEN 32010/mysqld
tcp 0 0 0.0.0.0:9999 0.0.0.0:* LISTEN 32010/mysqld
mysql> show plugins;
| handlersocket | ACTIVE | DAEMON | handlersocket.so | BSD |
+-----------------------+----------+--------------------+------------------+---------+
21 rows in set (0.00 sec)
mysql> show processlist;
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| 2 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL |
| 3 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL
这样就说明已经正常使用了！
3：使用
我使用perl对handlersocket进行测试使用，表也用网上的表（^-^方便）
CREATE TABLE `user` (
`user_id` int(10) unsigned NOT NULL,
`user_name` varchar(50) DEFAULT NULL,
`user_email` varchar(255) DEFAULT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`),
KEY `INDEX_01` (`user_name`)
) ENGINE=InnoDB;
手动insert 几条语句insert into user values(4, "yangting", "", CURRENT_TIMESTAMP);
1）SELECT
如下我通过handlersocket对表进行查询
#!/usr/bin/perl
use strict;
use warnings;
use Net::HandlerSocket;
#1. establishing a connection
my $args = { host => 'localhost', port => 9998 };
my $hs = new Net::HandlerSocket($args);
#2. initializing an index so that we can use in main logics.
# MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, 'test', 'user', 'PRIMARY', 'user_name,user_email,created');
#这个为查询的列数，这里为三列，分别为：user_name,user_email,created
die $hs->get_error() if $res != 0;
#3. main logic
#fetching rows by id
#execute_single (index id, cond, cond value, max rows, offset)
# 下面这个语句的意思是：查询主键大于或等于1，总行数为2行的数据
$res = $hs->execute_single(0, '>=', [ 1 ], 2, 0);
# 这里我们要注意：execute_single方法的返回值类型为arrayref，其第一个元素为error code：如果为0，则为正常，否则不正常，数组从第二元素开始即为返回的值，存储格式为 后面一行紧跟前面一行！
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
#下面为分行打印这个数组的所有值
for (my $row = 0; $row <8 ; ++$row) {
my $user_name= $res->[$row*3 + 0];
my $user_email= $res->[$row*3 + 1];
my $created= $res->[$row*3 + 2];
if ($user_name || $user_email || $created)
{
print "$user_name\t$user_email\t$created\n";
}
else
{ last;
}
}
#4. closing the connection
$hs->close();
2）INSERT
my $args = { host => 'localhost', port => 9999 };
my $hs = new Net::HandlerSocket($args);
my $res = $hs->open_index(3, 'test', 'user', 'PRIMARY', 'user_id,user_name,user_email,created');
die $hs->get_error() if $res != 0;
#INSERT
$res = $hs->execute_single(3,'+', [5, 'zhongguo', 13:51:33' ],1,0);
die $hs->get_error() if $res->[0] != 0;
$hs->close();
3）UPDATE
my $args = { host => 'localhost', port => 9999 };
my $hs = new Net::HandlerSocket($args);
my $res = $hs->open_index(3, 'test', 'user', 'PRIMARY', 'user_name');
die $hs->get_error() if $res != 0;
#当user_id=5，更新'user_name'为woaini
$res = $hs->execute_single(3,'=', [5],1,0,'U',['woaini']);
die $hs->get_error() if $res->[0] != 0;
$hs->close();
4) DELETE
my $args = { host => 'localhost', port => 9999 };
my $hs = new Net::HandlerSocket($args);
my $res = $hs->open_index(3, 'test', 'user', 'PRIMARY', 'user_name');
die $hs->get_error() if $res != 0;
#DELETE user_id=4 的数据
$res = $hs->execute_single(3,'=', [4],1,0,'D');
print $res;
die $hs->get_error() if $res != 0;
$hs->close();
这里我只关注了使用，对于性能，有时间，还是需要测试一把，不过，HandlerSocket作者自己就测试了
参照：http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
