喜欢Mysql，不仅仅是因为他简单的使用，更深一层次的是因为他的开源、他的插入式引擎及更优秀的plugin！从队列式存储引擎Q4M()到memcache 的UDF() 到本文要说到的NoSQL for MySQL，去年的某一天，一朋友跟我说，nosql性能有多好多好时，我说，如果提取像k/v形式的数据，假设：用主键查询一个数据，你觉得性能会怎样 呢？其实当时我也知道，肯定是比不过memcache之类的cache，因为，mysql有一系列的认证，一系列的语法、词法分析！
前段一个偶然的时间里，下载percona-server突然发现一个名叫HandlerSocket的东东，一时好奇，google一把，发现这不就是我一直想要的在mysql里实现nosql的东西吗?兴奋之余，也动手安装及使用了一把，下面把我的步骤列出来!
主要参考：
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 install2：配置
在正式使用前，我们必须在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/mysqldmysql> 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', 'zhogonguo@email.com','2011-01-08 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作者自己就测试了
参照：