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

这是之前公司的一个业务的调研测试报告，测试数据量大约是一天8640万条数据。

定位系统数据库设计方案

——历史轨迹表

作者：Dreamingodd

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.4月份分区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

之前的系统Track（轨迹）表的设计是由员工ID号生成，也就是说一千个人，一千个数据表。这使得查询位置的时候，需要从一千个不同的表中插入和查询数据。故尝试重新设计。

仅涉及原系统的历史轨迹查询和位置查询两个功能，以及Track表。

原方案的表结构：

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。

Engine：Archive，简称：员工分表。设计了一千个员工，id范围（0-1000）。得到一千张表。

Engine：Archive，用于测试时的比较。所有的数据在一张表中，没有其他处理。

Engine：Archive，一张表，四个分区，按照时间。

Engine：Archive，一张表，12个分区，按照时间。

Engine：Archive，一张表，按照员工ID使用HASH分区法分为100个分区。

Engine：Archive，先按员工数分为1000张表，再按照月份将每个表分为12个分区。1000*12=12000各分区。

Engine：Archive，一张表，先按月份分为12个分区，再按照员工ID号将每个月份分区分为80个次分区，形成共960个分区。

Engine：InnoDB，一张表，按照员工ID使用HASH分区法分为500个分区，为id和tracktime加上复合索引，因为，两种查询都会用到这两列值。

    将一亿条数据分别插入8种方案，以供查询。数据时间范围1年、任意天数、任意小时数、20分钟、0秒。即并不完全是随机时间。这样做是希望一亿条数据能基本覆盖所有可能出现的时间值。每人1亿/1000=10万条，时间可能数：365*24*20=17万。这个方案并不合理，但最开始的时候造出的数据只能沿用了。否则再插一遍几亿的数据太浪费时间。

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

查询语句：

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

查询一定人群在特定时间点的位置。

查询语句：

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

由于方案3（季节分区）和方案4（月份分区）在历史轨迹的第一次测试中就超过3分钟所以在接下来的测试中被放弃，方案6（员工分表+月份分区，1000*12=12000个分区）的方案在插入数据时在第500分区处崩溃，即使换成员工分表+季节分区（1000*4=4000个分区）也在第1500分区处崩溃，所以这个方案也被放弃。方案7员工分区的表现还可以，但几乎每个测试所用的时间都在原方案的5-10倍，所以一样放弃。

由于Archive引擎不支持索引，所以最后一个方案的引擎改用InnoDB——员工分区加索引，这个方案在测试中表现良好，在与原方案的比较中基本完胜。尤其是在所有人的位置查询中比原方案快近500倍，但是加索引之后的插入效率会成为问题。所以接下来做了这个方案的插入速度测试。

原方案的插入是向1000张表中定时插入数据，而且是一条插一次，并且不停地换表，所以插入效率是很低的，以上任意方案（除分表+分区），效率都应该是要高过原方案的。但大数据量的表加索引时插入效率也很低。所以还是做了这个测试。

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

拼接sql语句的效率在反复单独的十倍上下，而以3000人为基准的数据一次性插入的速度在0.5秒左右，效率上也可以接受。

目前数据库方案是按天建表，原因是考虑到一个人全天在线（如养老院），一秒钟一条数据一天就是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

插入测试的结果证明MyISAM引擎的插入速度要好于InnoDB，但InnoDB的效率也不是不能接受，因为在项目中一次性缓存插入数据库2000条左右，1秒钟还过得去。

查询效率的问题几乎宣告了方案的失败，查人员分布理应是全部1000人的查询，由于效率太低才使用100人分批查找，但是100人4秒的效率根本无法接受。

还需再想办法。

在华为应先生的建议下，将人员分布和历史轨迹两个业务分开，人员分布的表定位每人一分钟一次数据，这样一天的数据量为3000*24*60=432万。

结果：16ms

显而易见，可以接受的结果。

问题被解决。

定位模糊时JAVA端对历史轨迹插入数据库的数据进行的筛选工作，主要作用在于减少数据量、减少对硬盘存储空间的要求、减轻数据库查询的压力。它可以限定一个人的移动达到一定距离才算做移动，而不算做移动的数据大部分不插入数据库，但同时在JAVA业务超时的范围内，插入一次停留的数据。具体见如下“参数描述”的超时时间。

移动超过多少像素算作是移动一次，移动的数据是必须插入数据库的，这个距离应该是测试不会出现穿墙等异常情况后的最大距离。

超时时间是停留数据插入间隔的最小范围，如超时时间为30秒，那么在超时时间内没有移动事件的话，将在30秒内插入一次停留的数据。

超时时间30秒，模拟移动的工具每次移动20px，以监狱版本业务的全天在线和很少移动为模型，设置最小移动距离为100px。1000人在线测试：

数据量减少了24倍，当然这是比较理想的情况，1000人一年的数据量292GB。

经过测试和综合平定方案8——员工分区加索引的方案查询效率最佳，但是插入数据最好在缓存中存储list，拼接成sql语句后一次性插入多条数据，才能使插入效率达到可接受的程度。

备注：可能查询时mac放在前面有助于提高查询效率。