mysql性能简单测试

测试环境：

环境1：

操作系统： Ubuntu 15.10 (GNU/Linux 4.2.0-16-generic x86_64)

硬盘: 20G

内存: 1G

cpu:单核

mysql版本：5.6.30-0ubuntu0.15.10.1

环境2：

操作系统： win7

硬盘: 1T

内存: 16G

cpu:4核 i5

mysql版本： 14.14 Distrib 5.5.28, for Win64 (x86)

测试数据:gps定位数据

查询类型： 单表查询

group对应的字段已添加索引

select gps_no,count(gps_no) from car_gps_info_h_10XXXwan group by gps_no;

环境1 结果:

	表引擎	是否分区	是否分表	创建所需时间	修改表引擎为myisam所需时间	按id range分区（30万）所需时间	mere 创建所需时间	count(1)	min/max	count 并且 group
10万数据表	innodb	未分区	未分表	1.767s				0.794s	0.747s	0.761s
100万数据	innodb	未分区	未分表	17.372 s				3.279s	3.222s	3.736s
1000万数据	innodb	未分区	未分表	169s （差不多3分钟）				27.655s	28.295s	33.700s
10万数据表	myisam	未分区	未分表		0.305s			0.001s	0.265s	0.294s
100万数据	myisam	未分区	未分表		3.727s			0.001s	2.280s	2.985s
1000万数据	myisam	未分区	未分表		46.552s			0.001s	18.747s	22.562s
100万数据	myisam	分区	未分表			1.943s		0.002s	1.034s	1.146s
1000万数据	myisam	分区	未分表			41.203s		0.001s	19.903s	23.848s
1000万数据	myisam	未分区	分表					0.001s	19.903s	23.848s

环境2 结果:

	表引擎	是否分区	是否分表	创建所需时间	修改表引擎为myisam所需时间	按id range分区（30万）所需时间	mere 创建所需时间	count(1)	min/max	count 并且 group
10万数据表	innodb	未分区	未分表	未测试				0.209s	0.199s	0.247s
100万数据	innodb	未分区	未分表	未测试				1.705s	1.612s	1.712s
1000万数据	innodb	未分区	未分表	未测试				14.844s	14.442s	18.369s
10万数据表	myisam	未分区	未分表		未测试			0.001s	0.070s	0.130s
100万数据	myisam	未分区	未分表		未测试			0.001s	0.434s	1.238s
1000万数据	myisam	未分区	未分表		未测试			0.001s	3.957s	11.451s
100万数据	myisam	分区	未分表			1.646s		0.001s	0.429s	1.189s
1000万数据	myisam	分区	未分表			14.770s		0.001s	4.036s	11.528s
1000万数据	myisam	未分区	分表	0.038s			0.038s	0.002s	4.011s	11.503s

总结：

1.好的硬件设备对性能提高显著

2.分区在硬件支持的情况下才能生效

3.myisam查询比innodb快

4.分区比不分区速度快

5.分表和分区在测试环境下表现基本差不多。

6.mysql 5.5.28不支持磁盘分区选项(DIRECTORY)

执行命令记录

/** 创建表 */ create table car_gps_info_h_10wan as select * from car_gps_info_h_bak limit 0,100000; create table car_gps_info_h_10wan_myisam as select * from car_gps_info_h_bak limit 0,100000; create table car_gps_info_h_1000wan as select * from car_gps_info_h_bak limit 0,10000000; create table car_gps_info_h_100wan_myisam as select * from car_gps_info_h_bak limit 0,1000000; create table car_gps_info_h_1000wan as select * from car_gps_info_h_1000wan; create table car_gps_info_h_1000wan_myisam as select * from car_gps_info_h_1000wan; /*修改表引擎*/ alter table car_gps_info_h_10wan_myisam engine ='myisam'; alter table car_gps_info_h_100wan_myisam engine ='myisam'; alter table car_gps_info_h_1000wan_myisam engine ='myisam'; /*查询汇总*/ ----------------- select max(time) from car_gps_info_h_10wan; select max(time) from car_gps_info_h_100wan; select max(time) from car_gps_info_h_1000wan; select max(time) from car_gps_info_h_10wan_myisam; select max(time) from car_gps_info_h_100wan_myisam; select max(time) from car_gps_info_h_1000wan_myisam; ------------------------- select count(1) from car_gps_info_h_10wan; select count(1) from car_gps_info_h_100wan; select count(1) from car_gps_info_h_1000wan; select count(1) from car_gps_info_h_10wan_myisam; select count(1) from car_gps_info_h_100wan_myisam; select count(1) from car_gps_info_h_1000wan_myisam; ------------------------------ select gps_no,count(gps_no) from car_gps_info_h_10wan group by gps_no; select gps_no,count(gps_no) from car_gps_info_h_100wan group by gps_no; select gps_no,count(gps_no) from car_gps_info_h_1000wan group by gps_no; select gps_no,count(gps_no) from car_gps_info_h_10wan_myisam group by gps_no; select gps_no,count(gps_no) from car_gps_info_h_100wan_myisam group by gps_no; select gps_no,count(gps_no) from car_gps_info_h_1000wan_myisam group by gps_no; /*分区脚本*/ /*30万分组 100万*/ alter table car_gps_info_h_100wan_myisam partition by range (id) ( partition f1 values less than(300000), partition f2 values less than(600000), partition f3 values less than(900000), partition f4 values less than(1200000) ); /*30万分组 1000万*/ alter table car_gps_info_h_1000wan_myisam partition by range (id) ( partition f1 values less than (300000), partition f2 values less than (600000), partition f3 values less than (900000), partition f4 values less than (1200000), partition f5 values less than (1500000), partition f6 values less than (1800000), partition f7 values less than (2100000), partition f8 values less than (2400000), partition f9 values less than (2700000), partition f10 values less than (3000000), partition f11 values less than (3300000), partition f12 values less than (3600000), partition f13 values less than (3900000), partition f14 values less than (4200000), partition f15 values less than (4500000), partition f16 values less than (4800000), partition f17 values less than (5100000), partition f18 values less than (5400000), partition f19 values less than (5700000), partition f20 values less than (6000000), partition f21 values less than (6300000), partition f22 values less than (6600000), partition f23 values less than (6900000), partition f24 values less than (7200000), partition f25 values less than (7500000), partition f26 values less than (7800000), partition f27 values less than (8100000), partition f28 values less than (8400000), partition f29 values less than (8700000), partition f30 values less than (9000000), partition f31 values less than (9300000), partition f32 values less than (9600000), partition f33 values less than (9900000), partition f34 values less than (12000000) );