mysql直连通过handler读取数据

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 

欢迎大家阅读《mysql直连通过handler读取数据》,跪求各位点评，by 搞代码

表里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
看来还是有一定的性能的提升.

喜欢 (0)赏

[搞代码]

分享 (0)