mysql直接通过handler读取数据
前几天在mysql文档中发现了它有个handlerhttp://dev.mysql.com/doc/refman/5.1/en/handler.html,
号称比select要快,因为省略了sql解析,查询优化等环节,也不用提供一致性读.
所以用mysqlslap测试一下

CREATE TABLE `20130122handler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `content` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `20130122handler_idx_uid` (`uid`)
) ENGINE=InnoDB

表里600w条随机数据

[mysqlslap]
query="D:/mysql-5.1.63-node1/mysqlslap-query_handler.sql"
create-schema="crap"
delimiter=";"
concurrency=1
iterations=200

mysqlslap-query_handler.sql内容为

HANDLER 20130122handler open as h;
HANDLER h read  20130122handler_idx_uid=(26010200);
HANDLER h read  20130122handler_idx_uid=(23742985);
......

一共有100个HANDLER h read
测试三次,结果如下

Benchmark
        Average number of seconds to run all queries: 0.035 seconds
        Minimum number of seconds to run all queries: 0.000 seconds
        Maximum number of seconds to run all queries: 1.032 seconds
        Number of clients running queries: 1
        Average number of queries per client: 101


Benchmark
        Average number of seconds to run all queries: 0.030 seconds
        Minimum number of seconds to run all queries: 0.000 seconds
        Maximum number of seconds to run all queries: 1.016 seconds
        Number of clients running queries: 1
        Average number of queries per client: 101


Benchmark
        Average number of seconds to run all queries: 0.025 seconds
        Minimum number of seconds to run all queries: 0.000 seconds
        Maximum number of seconds to run all queries: 1.032 seconds
        Number of clients running queries: 1
        Average number of queries per client: 101
      
换成了
select  * from 20130122handler where uid=26010200;
select  * from 20130122handler where uid=23742985;
......

结果为


Benchmark
        Average number of seconds to run all queries: 0.055 seconds
        Minimum number of seconds to run all queries: 0.015 seconds
        Maximum number of seconds to run all queries: 1.031 seconds
        Number of clients running queries: 1
        Average number of queries per client: 100


Benchmark
        Average number of seconds to run all queries: 0.050 seconds
        Minimum number of seconds to run all queries: 0.015 seconds
        Maximum number of seconds to run all queries: 1.032 seconds
        Number of clients running queries: 1
        Average number of queries per client: 100


Benchmark
        Average number of seconds to run all queries: 0.044 seconds
        Minimum number of seconds to run all queries: 0.015 seconds