`summary` varchar(255) default '',
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
插入数据
说明:每次都是空表插入数据
插入10000 Record
Heap engine insert 10000 record used time: 3.5008587837219
MyISAM engine insert 10000 record used time: 4.5881390571594
50000 Record
Heap engine insert 50000 record used time: 19.895354986191
MyISAM engine insert 50000 record used time: 33.866044998169
100000 Record
Heap engine insert 100000 record used time: 36.200875997543
MyISAM engine insert 100000 record used time: 68.34194111824
200000 Record
Heap engine insert 200000 record used time: 68.00207901001
MyISAM engine insert 200000 record used time: 125.26263713837
查询数据
表里分表有:200000条记录,两个表数据一致
直接select,10000次,每次取100条记录
Heap engine select 10000 times, 100 record used time: 12.122506141663
MyISAM engine select 10000 times, 100 record used time: 19.512896060944
直接select,1000次,每次取10000条记录
Heap engine select 1000 times, 10000 record used time: 111.54126811028
MyISAM engine select 1000 record used time: 116.79438710213
增加where条件,1000次,每次取10000条记录
Heap engine select 1000 times, 10000 record used time: 111.52102303505
MyISAM engine select 1000 times, 10000 record used time: 117.68481087685
where条件,10000次,每次从1000条起,取1000条记录
Heap engine select 10000 times, 1000 record used time: 124.28988695145
MyISAM engine select 10000 times, 1000 record used time: 139.82107305527
where条件增加like,10000次,每次从1000条起,取1000条记录
Heap engine select 10000 times, 1000 record used time: 145.43780493736
MyISAM engine select 10000 times, 1000 record used time: 163.56296992302
where条件增加索引,10000次,每次从1000条起,取1000条记录
-- 建立索引 (在SQLyob下执行)
ALTER TABLE tbl_heap ADD INDEX idx_name (name);
ALTER TABLE tbl_isam ADD INDEX idx_name (name);
Heap engine alter table add index used time: 2.078
MyISAM engine alter table add index used time: 13.516
Heap engine select 10000 times, 1000 record used time: 153.48922395706
MyISAM engine select 10000 times, 1000 record used time: 239.86818814278
PS:不合适的索引还不如不要。
文章来源于领测软件测试网 https://www.ltesting.net/