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
Maximum number of seconds to run all queries: 1.032 seconds
Number of clients running queries: 1
Average number of queries per client: 100
看来还是有一定的性能的提升.