• NoSQL for MySQL [HandlerSocket] 之安装与使用


    喜欢Mysql,不仅仅是因为他简单的使用,更深一层次的是因为他的开源、他的插入式引擎及更优秀的plugin!从队列式存储引擎Q4M(http://q4m.github.com/)到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", "D@test.com", 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作者自己就测试了

    参照:http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

  • 相关阅读:
    数据处理之求和语句,retain语句
    Day1 Excel基本知识
    Day6 数据清洗(2)
    Day5 数据的清洗
    Day5 快速输入数据的方法(2)
    Day5 快速输入数据的方法
    Day4 利用小技巧进行快速数据处理
    Day3 Excel与数据分析之小技巧
    Day2 Excel与数据处理之定位条件、选择性粘贴及查找功能
    day5:python学习之集合
  • 原文地址:https://www.cnblogs.com/zcy_soft/p/2010626.html
Copyright © 2020-2023  润新知