通过比较读取 MyISAM 和Memory 下的两个表的速度,评估内存引擎的性能
1,建立数据源
CREATE TABLE x_companies (
ID int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
。。。。。。。。。。。。2000 varchar
PRIMARY KEY (ID),
UNIQUE KEY idx_b2bcompanies_id (companyid,ID)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 ;
2,建立select 数据的函数,便于下一步反复执行
SET GLOBAL log_bin_trust_function_creators =1;
delimiter ; drop FUNCTION rand_company;
delimiter // CREATE FUNCTION rand_company()
RETURNS int READS SQL DATA
BEGIN
set @r = floor(rand()*1234564);
select id into @r from b2bcompanies where id = @r;
//读取MyIsam表
RETURN @r;
END ;
//
delimiter ; SELECT BENCHMARK(250000,rand_company());
delimiter ; drop FUNCTION rand_x_company;
delimiter // CREATE FUNCTION rand_x_company()
RETURNS int READS SQL DATA
BEGIN
set @r = floor(rand()*350000);
select id into @r from x_companies where id = @r; //读取memory表
RETURN @r;
END ;
//
delimiter ; 3,试验一下MYSQL的性能
SELECT BENCHMARK(2500000,rand_x_company());
1 row in set, 65535 warnings (1 min 21.82 sec) // count(*) =
0 在没有数据的情况下,需要 82 秒
4,生成模拟数据
insert into x_companies select *from b2bcompanies ;
select count(*) from x_companies;
+----------+
| count(*) |
+----------+
| 353979 | +----------+
1 row in set (0.00 sec)
(备注:内存表的大小受max_heap_table_size限制,我仅使用2132M)
5,内存表的性能?
SELECT BENCHMARK(2500000,rand_x_company());
1 row in set, 13848 warnings (1 min 39.70 sec) // count(*) =
353979
6,MYISAM 表的性能?
SELECT BENCHMARK(250000,rand_company());
+----------------------------------+
| BENCHMARK(250000,rand_company()) |
+----------------------------------+
1 row in set, 410 warnings (1 min 2.10 sec)
7,...........................
居然 MYISAM 表 更快 ,
哦,看错咯,这次少了一个0 :D
8,不敢相信,再试验几次
+-----------------------------------+
| BENCHMARK(2500000,rand_company()) |
+-----------------------------------+
1 row in set, 3924 warnings (2 min 9.4u5 sec)
1 row in set, 3963 warnings (2 min 9.53 sec)
1 row in set, 3881 warnings (2 min 9.33 sec)
1 row in set, 3914 warnings (2 min 9.83 sec)
1 row in set, 3969 warnings (2 min 11.95 sec)
多次试验 证明,MEMORY 比 MYISAM 快 一倍,也仅仅是 100% 的增长。