mysql直接通过handler读取数据

前几天在mysql文档中发现了它有个handler[url]http://dev.mysql.com/doc/refman/5.1/en/handler.html[/url],
号称比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
看来还是有一定的性能的提升.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值