单机大数据量表的查询与插入优化方案分享

那是后面集团的一个政工的调研测试报告,测试数据量大致是一天8640万条数据。

前言

眼下自我还只是读书到Mecanima的初级阶段,看完了阿赵的日志《Unity3D
4.0新功效:Mecanim动画系统基础教程》
,对Mecanima的了然更透彻了一些,谢谢他的分享。

定位系统数据库设计方案

存在的题材

——历史轨迹表

Unity版本

Unity 4.0

作者:Dreamingodd

ab不可以动态修改controller

在正式项目中如若是索要从互联网中下载模型,存在那样的一个标题Animator中的Controller不能够改改,所以只要选拔assetBundle动态加载,那么是不行的。

如若单机演示,那么就足以选拔此新动画系统

2012.9.5

Version:2.2.0

目录

1.引言4

1.1目标 4

1.2范围 4

2.方案描述4

2.1表结构 4

2.2不等方案的牵线 5

2.2.1原方案5

2.2.2一张表5

2.2.3季节分区5

2.2.十一月份分区5

2.2.5员工分区5

2.2.6职工分表和月份分区5

2.2.7员工分区和月份分区5

2.2.8员工分区加索引6

3.询问速度测试6

3.1数量描述 6

3.2历史轨迹查询 6

3.2.1测试方法描述6

3.2.2测试结果(所有测试结果单位为秒)6

3.3岗位查询 7

3.3.1测试方法描述7

3.3.2测试结果(所有测试结果单位为秒)7

3.4结实分析 7

4.插入速度测试7

4.1概述 7

4.2测试方法描述 8

4.3测试结果(所有测试结果单位为秒)
8

4.4结出分析 8

5.人士分布测试(Ed.2.1)9

5.1 人员分布测试方案描述 9

5.2 插入速度测试 10

5.3 查询速度测试 10

5.4 结果分析 10

5.5 解决方案 10

5.6查询速度测试2 10

5.7 结果分析2 11

6.稳住模糊对DB的震慑(Ed.2.2)12

6.1 方案描述 12

6.2 影响结果的参数描述 12

6.2.1活动最小距离12

6.2.2 超时时间12

6.2.3 测试所选用的数据解析12

6.3 结果分析 12

7.总结13

 

1.引言

1.1目标

事先的系列Track(轨迹)表的宏图是由职工ID号生成,也就是说一千个人,一千个数据表。那使得查询职位的时候,需求从一千个不等的表中插入和查询数据。故尝试重新规划。

1.2范围

仅提到原系统的历史轨迹查询和岗位查询三个效益,以及Track表。

2.方案描述

2.1表结构

原方案的表结构:

CREATE TABLE `track_1094` (

  `track_id` bigint(20) NOT NULL AUTO_INCREMENT,

  `trackTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `x` double NOT NULL,

  `y` double NOT NULL,

  `z` double NOT NULL,

  `area` double NOT NULL,

  PRIMARY KEY (`track_id`),

  KEY `trackTime` (`trackTime`)

) ENGINE=InnoDB AUTO_INCREMENT=549207 DEFAULT CHARSET=utf8;

 

新方案的表结构:

CREATE TABLE prison_all (

  track_id bigint(20) NOT NULL,

  trackTime timestamp NOT NULL default CURRENT_TIMESTAMP,

  id int NOT NULL,

  x double default NULL,

  y double default NULL,

  z double default NULL,

  area double default NULL

)engine=archive DEFAULT CHARSET=utf8;

 

 

鉴于新方案紧要尝试用一张表解决问题,所以加了职工的ID,相当于原方案中的MAC地址(标签),engine一伊始选拔的是archive,最终换回了InnoDB。

2.2不等方案的牵线

2.2.1原方案

Engine:Archive,简称:员工分表。设计了一千个员工,id范围(0-1000)。得到一千张表。

2.2.2一张表

Engine:Archive,用于测试时的可比。所有的数量在一张表中,没有其他处理。

2.2.3季节分区

Engine:Archive,一张表,多个分区,依据时间。

2.2.十一月份分区

Engine:Archive,一张表,12个分区,根据时间。

2.2.5员工分区

Engine:Archive,一张表,依据员工ID使用HASH分区法分为100个分区。

2.2.6职工分表和月份分区

Engine:Archive,先按职工数分为1000张表,再依照月份将每个表分为12个分区。1000*12=12000各分区。

2.2.7员工分区和月份分区

Engine:Archive,一张表,先按月度分为12个分区,再根据职工ID号将每个月份分区分为80个次分区,形成共960个分区。

 

2.2.8员工分区加索引

Engine:InnoDB,一张表,根据员工ID使用HASH分区法分为500个分区,为id和tracktime加上复合索引,因为,三种查询都会用到那两列值。

3.询问速度测试

3.1数码描述

    将一亿条数据分别插入8种方案,以供查询。数据时间范围1年、任意天数、任意小时数、20分钟、0秒。即并不完全是任意时间。那样做是可望一亿条数据能基本覆盖所有可能出现的年月值。每人1亿/1000=10万条,时间或许数:365*24*20=17万。那个方案并不创立,但最开始的时候造出的数额只可以沿用了。否则再插两次几亿的多少太浪费时间。

3.2历史轨迹查询

3.2.1测试方法描述

查询一定时间段内某名员工的track数据。

查询语句:

Select * from table_name where id = ?  and trackTime < ?  and
trackTime > ? ;

3.2.2测试结果(所有测试结果单位为秒)

亿级别历史轨迹查询(s)

1时

1天

5天

10天

1月

2月

1

员工分表(原方案)

0.4

0.4

0.5

0.6

0.7

2.9

2

就一张表

280

没测

没测

没测

没测

没测

3

时令分区

250

没测

没测

没测

没测

没测

4

月份分区

225

没测

没测

没测

没测

没测

5

职工分区

2.1

2.3

2.4

2.4

2.5

2.7

6

员工分表+月份分区

Fail

Fail

Fail

Fail

Fail

Fail

7

员工+月份分区

530

没测

没测

没测

没测

没测

8

职工分区加索引

0.02

0.05

0.16

0.2

0.7

0.9

 

3.3义务查询

3.3.1测试方法描述

查询一定人群在一定时间点的任务。

查询语句:

Select  * from table_name where id < ? and trackTime = ? ;

3.3.2测试结果(所有测试结果单位为秒)

亿级别岗位查询

澳门美高梅手机网站,1个人

10人

100人

所有人

1

员工分表(原方案)

0.3

4

45

477

5

员工分区

3.3

291

291

295

7

职工+月份分区

0.5

38

没测

没测

8

职工分区加索引

0.02

9

9.5

9

3.4结出分析

出于方案3(季节分区)和方案4(月份分区)在历史轨迹的率先次测试中就跨越3分钟所以在接下去的测试中被放弃,方案6(员工分表+月份分区,1000*12=12000个分区)的方案在插入数据时在第500分区处崩溃,固然换成员工分表+季节分区(1000*4=4000个分区)也在第1500分区处崩溃,所以那么些方案也被甩掉。方案7员工分区的展现还足以,但差一些每个测试所用的年月都在原方案的5-10倍,所以同样废弃。

是因为Archive引擎不匡助索引,所以最后一个方案的引擎改用InnoDB——员工分区加索引,这一个方案在测试中表现完美,在与原方案的可比中基本完胜。尤其是在所有人的职位查询中比原方案快近500倍,可是加索引之后的插入作用会成为难点。所以接下去做了这一个方案的插入速度测试。

4.插入速度测试

4.1概述

原方案的插入是向1000张表中定时插入数据,而且是一条插四回,并且不停地换表,所以插入功用是很低的,以上任意方案(除分表+分区),功用都应该是要高过原方案的。但大数据量的表加索引时插入效能也很低。所以如故做了那么些测试。

4.2测试方法描述

前提:表中已有7500万条数据,随机生成定量的Track对象,保存在List中,向方案8——员工分区加索引的表中插入数据。插入方法三种:一种是一条一条地插入,另一种是拼接一千个Track对象的数码变成一条sql语句三回性插入表中。

4.3测试结果(所有测试结果单位为秒)

员工分区加索引插入测试

1000

3000

5000

10000

批量插入

0.25

0.5

1

4

单独插入

1.9

5

9

16

4.4结出分析

拼接sql语句的效能在延续单独的十倍左右,而以3000人为准绳的数量一遍性插入的快慢在0.5秒左右,效能上也足以接受。

 

5.人员分布测试(Ed.2.1)

5.1 人员分布测试方案描述

眼下数据库方案是按天建表,原因是考虑到一个人全天在线(如养老院),一分钟一条数据一天就是86400条,1000个人就有8千万,3000人有2亿5千万——那一个数字就不自然罩得住了。当然也有肯定的接纳事件流和任务模糊处理收缩数据量,可是那一个主意或者没有通过系统的测试,也远非多少支撑。

本次的测试模拟5千万的测试数据,1000人15钟头为5千4百万条的数据量,分二种存储引擎,共三种方案:InnoDB、MyISAM100分区和MyISAM200分区三种。

 

表结构:

create table track_InnoDB(

tag_id bigint,

time timestamp,

x double,

y double,

z double,

area int,

status int(1),

alarm int(2),

index(tag_id,time)

)engine=InnoDB DEFAULT CHARSET=utf8

partition by hash(tag_id)

partitions 100;

 

测试目标为:两种方案的人口分布查询速度。

查询语句:

一分钟:

select max(time),x,y,tag_id from track_2012_12_13  

where tag_id in (?,?,?…) and time>=’2013-01-01 11:00:00′

and time<‘2012-01-01 11:01:00’ group by tag_id

一小时:

select max(time),x,y,tag_id from track_2012_12_13  

where tag_id in (?,?,?…) and time>=’2013-01-01 11:00:00′

and time<‘2012-01-01 12:00:00’ group by tag_id

5.2 插入速度测试

插入速度测试

5400万

1000计算

1亿计算

1亿大小

MyISAM引擎100分区

26分钟

0.029秒

48分钟

 

MyISAM引擎200分区

30分钟

0.033秒

55分钟

 

InnoDB100分区

79分钟

0.079秒

146分钟

 

 

5.3 查询速度测试

人员分布查询速度测试
(单位秒)

1分钟的60条数据筛选100人的100条数据

1分钟的60条数据筛选50人的50条数据

1分钟的60条数据筛选25人的25条数据

一小时的3600条数据筛选100人的100条数据

一小时的3600条数据筛选50人的50条数据

一小时的3600条数据筛选25人的25条数据

MyISAM引擎100分区

4.1

1.8

0.5

4.4

3.4

1.6

MyISAM引擎200分区

2.6

1.3

0.9

3.2

1.8

8.4

InnoDB100分区

4.8

1

0.5

5.4

3

1.1

 

5.4 结果分析

插入测试的结果证实MyISAM引擎的插入速度要好于InnoDB,但InnoDB的频率也不是不可以承受,因为在档次中四次性缓存插入数据库2000条左右,1分钟还过得去。

询问作用的标题大致宣布了方案的挫败,查人士分布理应是任何1000人的询问,由于作用太低才使用100人分批查找,可是100人4秒的效用根本不可以接受。

还需再想办法。

5.5 解决方案

在BlackBerry应先生的提议下,将人口分布和历史轨迹三个业务分别,人士分布的表定位每人一分钟一回数据,那样一天的数据量为3000*24*60=432万。

 

5.6查询速度测试2

结果:16ms

5.7 结果分析2

肯定,可以接受的结果。

标题被解决。

 

6.定点模糊对DB的熏陶(Ed.2.2)

6.1 方案描述

原则性模糊时JAVA端对历史轨迹插入数据库的数据开展的筛选工作,主要功能在于缩短数据量、减弱对硬盘存储空间的渴求、减轻数据库查询的下压力。它能够限制一个人的运动达成一定距离才算做活动,而不算做运动的多少超过一半不插入数据库,但还要在JAVA业务超时的范围内,插入一次停留的数据。具体见如下“参数描述”的超时时间。

6.2 影响结果的参数描述

6.2.1运动最小距离

挪动领先多少像素算作是运动四遍,移动的数额是必须插入数据库的,那几个距离应该是测试不会产出穿墙等极度意况后的最大距离。

6.2.2 超时时间

过期时间是停留数据插入间隔的纤维范围,如超时时间为30秒,那么在逾期时间内没有挪动事件的话,将在30秒内插入四次停留的数量。

6.2.3 测试所拔取的数额解析

过期时间30秒,模拟移动的工具每一回活动20px,以监狱版本业务的全天在线和很少运动为模型,设置最小移动距离为100px。1000人在线测试:

6.3 结果分析

 

 

一天的数据量

一天数据大小

一年数据大小

没有应用定位模糊的结果

8640万条

8G

3TB以上

应用了定位模糊的结果

350万条

388MB

292GB

 

数据量缩小了24倍,当然这是相比赏心悦目的意况,1000人一年的数据量292GB。

 

7.总结

通过测试和归咎平定方案8——员工分区加索引的方案查询效用至上,可是插入数据最好在缓存中存储list,拼接成sql语句后两遍性插入多条数据,才能使插入效用达到可接受的水平。

 

备注:可能查询时mac放在眼前有助于增高查询功用。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注