３、性能测试

简便起见，这里使用perl进行测试。

首先需要编译安装HandlerSocket perl客户端，进入HandlerSocket源码目录：

    # cd perl-Net-HandlerSocket/

    # perl Makefile.PL

    # make && make install

MySQL数据库中创建测试对象：

    mysql> use jssdb;

    Database changed

    mysql> CREATE TABLE user (

    -> user_id INT(10) UNSIGNED NOT NULL auto_increment,

    -> user_name VARCHAR(50) DEFAULT NULL,

    -> user_email VARCHAR(255) DEFAULT NULL,

    -> created DATETIME DEFAULT NULL,

    -> PRIMARY KEY (user_id),

    -> KEY ind_user_name (user_name))

    -> engine=Innodb,auto_increment=1;

    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into user select null,¨junsansi¨,¨junsansi#sina.com¨,current_timestamp from information_schema.columns;

    Query OK, 10029 rows affected (10.83 sec)

    Records: 10029 Duplicates: 0 Warnings: 0

    mysql> insert into user select null,¨junsansi¨,¨junsansi#sina.com¨,current_timestamp from user;

    Query OK, 10029 rows affected (0.16 sec)

    Records: 10029 Duplicates: 0 Warnings: 0

    mysql> insert into user select null,¨junsansi¨,¨junsansi#sina.com¨,current_timestamp from user;

    Query OK, 20058 rows affected (0.32 sec)

    Records: 20058 Duplicates: 0 Warnings: 0

    mysql> insert into user select null,¨junsansi¨,¨junsansi#sina.com¨,current_timestamp from user;

    Query OK, 40116 rows affected (0.49 sec)

    Records: 40116 Duplicates: 0 Warnings: 0

    mysql> insert into user select null,¨junsansi¨,¨junsansi#sina.com¨,current_timestamp from user;

    Query OK, 80232 rows affected (1.08 sec)

    Records: 80232 Duplicates: 0 Warnings: 0

    mysql> insert into user select null,¨junsansi¨,¨junsansi#sina.com¨,current_timestamp from user;

    Query OK, 160464 rows affected (1.31 sec)

    Records: 160464 Duplicates: 0 Warnings: 0

    mysql> insert into user select null,¨junsansi¨,¨junsansi#sina.com¨,current_timestamp from user;

    Query OK, 320928 rows affected (3.58 sec)

    Records: 320928 Duplicates: 0 Warnings: 0

    mysql> update user set user_name=concat(user_name,user_id),created=created+user_id;

    Query OK, 641856 rows affected, 65535 warnings (40.28 sec)

    Rows matched: 641856 Changed: 641856 Warnings: 623530

创建测试用的perl脚本，首先创建使用handlersocket查询的方式queryhs.pl：

    #!/usr/bin/perl

    use strict;

    use warnings;

    use Net::HandlerSocket;

    use POSIX qw(strftime);

    print strftime("%Y-%m-%d %H:%M:%S\n", localtime(time));

    #1. establishing a connection

    my $args = { host => ¨192.168.10.112¨, 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, ¨jssdb¨, ¨user¨, ¨ind_user_name¨, ¨user_name,user_email,created¨);

    die $hs->get_error() if $res != 0;

    my $randnum;

    my $username;

    #3. main logic

    #fetching rows by id

    #execute_single (index id, cond, cond value, max rows, offset)

    for (my $row = 0; $row < 100000; ++$row) {

    $randnum= int(rand(640000));

    $username = "junsansi$randnum";

    $res = $hs->execute_single(0, ¨=¨, [ $username ], 1, 0);

    }

    #4. closing the connection

    $hs->close();

    print strftime("%Y-%m-%d %H:%M:%S\n", localtime(time));

Perl中调用HandlerSocket的语法参考官方文档：

https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/perl-client.en.txt

创建使用标准select查询的方式querysql.pl(perl连接mysql需要先安装DBI)：

    #!/usr/bin/perl -w

    use strict;

    use DBI;

    use POSIX qw(strftime);

    print strftime("%Y-%m-%d %H:%M:%S\n", localtime(time));

    # Variables

    my $username = ¨jssdb¨; # set your MySQL username

    my $password = ¨jssdb¨; # set your MySQL password

    my $database = ¨jssdb¨; # set your MySQL database name

    my $server = ¨192.168.10.112¨; # set your server hostname (probably localhost)

    my $randnum;

    my $query;

    my $sth;

    # Get the rows from database

    my $dbh = DBI->connect("DBI:mysql:$database;host=$server", $username, $password)

    || die "Could not connect to database: $DBI::errstr";

    for (my $row = 0; $row < 100000; ++$row) {

    $randnum= int(rand(640000));

    $query = "select user_name from user where user_name=¨junsansi$randnum¨";

    $sth = $dbh->prepare($query);

    $sth->execute();

    }

    # Disconnect

    $sth->finish;

    $dbh->disconnect;

    print strftime("%Y-%m-%d %H:%M:%S\n", localtime(time));

接下来进行测试，先执行标准的mysql select获取数据：

    [root@mysqldb33 ~]# ./querysql.pl

    2012-03-30 16:29:24

    2012-03-30 16:29:58

    [root@mysqldb33 ~]# ./querysql.pl

    2012-03-30 16:30:05

    2012-03-30 16:30:38

尝试使用handlersocket获取数据：

    [root@mysqldb33 ~]# ./queryhs.pl

    2012-03-30 16:31:57

    2012-03-30 16:32:03

    [root@mysqldb33 ~]# ./queryhs.pl

    2012-03-30 16:32:11

    2012-03-30 16:32:19

粗略的测试可以看出，效率相差5倍左右，这只是单线程的调用示例，如果是多线程并发执行，HandlerSocket会表现的更优秀。

HandlerSocket的优势在于从内存中读取数据的效率比较高，比如是数据未被加载至内存，那么使HandlerSocket的效率也就高不到哪去，基本与传统的sql查询是相同的了，尝试重启MySQL服务使数据变冷：

    $ /data/mysqldata/script/mysql_db_shutdown.sh 3306

    $ /data/mysqldata/script/mysql_db_startup.sh 3306

这次先执行queryhs.pl：

    [root@mysqldb33 ~]# ./queryhs.pl

    2012-03-30 16:38:27

    2012-03-30 16:38:53

耗费的时间不小，不过再次调用就快多了：

    [root@mysqldb33 ~]# ./queryhs.pl

    2012-03-30 16:38:56

    2012-03-30 16:39:04

对于传统的SQL查询方式，即使数据已基本在内存里，效率仍然慢多了，甚至比初次加载的handlersocket还慢：

    [root@mysqldb33 ~]# ./querysql.pl

    2012-03-30 16:40:01

    2012-03-30 16:40:35

常见错误：

在5.1及之前版本的Mysql中安装插件时可能会碰到提示：

    mysql> install plugin handlersoket soname ¨handlersocket.so¨;

    ERROR 1289 (HY000): The ¨plugin¨ feature is disabled; you need MySQL built with ¨HAVE_DLOPEN¨ to have it working

这说明源码编译mysql时指定了静态编译的参数，可以将静态编译的参数去掉，并重新编译MySQL运行环境。

或者在编译MySQL前执行：

    # export CFLAGS="-O2 -DHAVE_DLOPEN=1"

    # export CXXFLAGS="-O2 -DHAVE_DLOPEN=1"