php HandlerSocket的使用

shell> makeshell> make install

接着需要配置一下HandlerSocket，编辑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.

此外，InnoDB的innodb_buffer_pool_size，或MyISAM的key_buffy_size等关系到缓存索引的选项尽可能设置大一些，这样才能发挥HandlerSocket的潜力。

注：apt包管理下的配置文件一般是/etc/mysql/my.cnf，否则一般是/etc/my.cnf

最后登陆MySQL并激活HandlerSocket插件：

mysql> INSTALL PLUGIN handlersocket soname ''handlersocket.so'';

重启一下MySQL服务，如果没有问题，就能在MySQL里看到HandlerSocket的线程了：

mysql> SHOW PROCESSLIST;

也可以通过查询刚配置的端口是否已经被MySQL占用来确认是否安装成功：

shell> lsof -i :9998shell> lsof -i :9999

完活儿！现在你的MySQL已经具备NoSQL的能力了！

实战

首先创建一个测试用的表：

CREATE TABLE IF NOT EXISTS `test`.`t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(10) NOT NULL, `b` varchar(10) NOT NULL, PRIMARY KEY (`id`), KEY `a_b` (`a`,`b`)) ENGINE=InnoDB;

注：理论上HandlerSocket支持MyISAM，InnoDB等各种引擎，不过推荐使用InnoDB。

HandlerSocket的协议非常简单，指令通过TAB分割，一行就是一个请求。本文用到了：

• 打开索引：P <索引标识> <数据库> <表> <索引> <字段>
• 插入数据：<索引标识> ‘+'' <参数个数> <参数1> … <参数N>
• 读取数据：<索引标识> <操作> <参数个数> <参数1> … <参数N> <条数> <偏移>

SQL原型：INSERT INTO test.t (id, a, b) VALUES (1, ‘a1′, ‘b1′), (2, ‘a2′, ‘b2′)

shell> telnet localhost 9999Trying 127.0.0.1...Connected to localhost.Escape character is ''^]''.P    1    test  t    PRIMARY id,a,b0    11    +    3    1    a1   b10    1    01    +    3    2    a2   b20    1    0

注：使用HandlerSocket时，因为没有实际运行SQL，所以Binlog记录的是Row格式。

SQL原型：SELECT id, a, b FROM test.t WHERE id = 1 LIMIT 1

shell> telnet localhost 9999Trying 127.0.0.1...Connected to localhost.Escape character is ''^]''.P    1    test  t    PRIMARY id,a,b0    11    =    1    1    1    00    3    1    a1   b1

SQL原型：SELECT id, a, b FROM test.t WHERE id >=1 LIMIT 2

shell> telnet localhost 9999Trying 127.0.0.1...Connected to localhost.Escape character is ''^]''.P    1    test  t    PRIMARY id,a,b0    11    >=   1    1    2    00    3    1    a1   b1   2    a2   b2

SQL原型：SELECT id, a, b FROM test.t WHERE a = ‘a1′ AND b = ‘b1′ LIMIT 1

shell> telnet localhost 9999Trying 127.0.0.1...Connected to localhost.Escape character is ''^]''.P    1    test  t    a_b   id,a,b0    11    =    2    a1   b1   1    00    3    1    a1   b1

对HandlerSocket一个常见的