GPS定位数据库表设计
2013-05-16 17:06 GPS视频平台产品经理 阅读(5038) 评论(3) 编辑 收藏 举报GPS定位数据库表设计
在开发工业系统的数据采集功能相关的系统时，由于数据都是定时上传的，如每20秒上传一次的时间序列数据，这些数据在经过处理和计算后，变成了与时间轴有关的历史数据（与股票数据相似，如下图的车辆行驶过程中的油量曲线、历史轨迹数据等）。当采集点多的时候，如上万个采集终端，再加上时间的累积，数据表会变得越来越大。数据库会越来越难以维护。
对历史数据操作的功能有历史数据的查询、统计图表曲线显示等等。而这些基于组合条件的历史查询功能和图表显示功能对数据库的压力可不小，如果听之任之，则海量的数据查询、统计计算会非常慢，用户体验很差。所以对于这种类型的表结构设计的时候，要制定好策略，避免以后再进行变动就麻烦了。
常规的策略就是分表。减少单一数据表的容量，通过增加合并查询和计算的逻辑的复杂度来换取减轻查询对数据库的压力。
- 减少冗余数据的存储
- 数据写入时进行预运算
- 通过降低记录量提高系统的响应能力
- 降低磁盘的读操作
GPS轨迹日志数据表保存原始的定位信息数据，索引表为日志数据表的索引信息内容，预处理模块完成新数据的预处理运算功能，保持索引表的信息与日志数据表的信息一致。
预处理模块建议为数据库内部的存储过程，保证对数据表操作的效率性能，通讯服务器以访问存储过程的方式，完成数据保存的事务处理。
日志数据表最多保存四个月的定位信息数据，最高的记录为246亿条数据，数据量为2.3T（2012年9月-12月）。
索引表为日志数据表的索引信息，保留三个月零一天的车辆定位信息，每车每天为一条记录，最高数据量为432万，每个车辆每天的摘要信息保存在二进制的字段里。
摘要的数据内容包含定位时间、速度、里程、经度、纬度、角度状态信息，摘要在索引表里按规定的报文方式保存在索引表的二进制字段里，每个车辆一天产生的定位数据量为4320条，速度为0的定位信息所占比例为40%-60%左右，因此摘要里只记录有速度的定位数据，每车每天的实际摘要的数据量为2000左右。每个摘要信息量为30Bytes，因此每车每天产生的摘要为58K Bytes左右。
注：从统计角度考虑，摘要信息里也许会包括速度为0的定位信息。
索引表根据业务操作的需要分为“最新定位索引表”、“三个月定位索引表”及“相关的统计索引表”。由于索引表里的记录量在百万级别内，可以实现毫秒级别的响应能力，所有的定位摘要数据保存在一个BLOB的二进制字段里，一次性快速读取出58K Bytes左右的数据，即可获得全天的车辆定位数据，避免数据库在磁盘上随机检索定位数据读而引起I/O性能瓶颈。
每车每天的索引信息的更新，通过预处理模块完成，如果索引表里已经存在该车该天的记录，则进行二进制字段的更新，否则自动添加新的记录。新的车辆定位摘要信息通过规定好的报文协议直接追加到二进制字段后面。
每天通过后台作业的方式，定时删除索引表里最早一天的数据。一次删除的最大记录量为4.9万条（2012年12月31日）。
读取车辆在某时间段内的定位数据，会从索引表里获得到相应天数的记录数，通过对BLOB字段进行数据协议的处理，获得定位摘要的记录集。
协议的解析工作可以由预处理模块解析，然后反转为表形式返回给web应用，也可以数据库直接交付给web应用服务器二进制数据，由web应用服务器完成数据的解析操作。
索引表里二进制数据摘要的状态可以支持8种不同的状态，每个定位数据可以同时具有8种不同的状态，比如超速、偏移路线等，可以直接通过索引表进行统计，快速实时的获取最新的报表数据。
以下数据表的结构内容未必完整，为关键字段的描述。
|
项目
|
类型
|
长度(Byes)
|
说明
|
|
号牌信息
|
Varchar2
|
8
|
粤A4811B格式
|
|
卡号信息
|
Number
|
7
|
如：13828838124
|
|
定位时间
|
Date
|
8
|
YYYYMMDDHH24MISS
|
|
记录时间
|
Date
|
8
|
YYYYMMDDHH24MISS
|
|
速度
|
Number
|
3
|
0≤SPEED≤999
|
|
里程
|
Number
|
4
|
0.00≤ MILEAGE≤9999.99
|
|
经度
|
Number
|
5
|
0.0000≤LONGITUDE≤180.0000，如果考虑境外定位，需要增加1位，可以支持最小-999.999
|
|
纬度
|
Number
|
5
|
-90.0000≤LATITUDE≤90.000
|
|
角度
|
Number
|
3
|
0≤ANGLE≤999
|
|
状态
|
CHAR
|
2
|
十六进制，支持8种状态，为2n，1≤n≤8， 1≤STATUS≤255
|
|
项目
|
类型
|
长度(Byes)
|
说明
|
|||
|
号牌信息
|
Varchar2
|
8
|
粤A4811B格式
|
|||
|
卡号信息
|
Number
|
7
|
如：13828838124
|
|||
|
日期
|
Number
|
5
|
20090531格式
|
|||
|
摘要信息
|
BLOB
|
≈58K
|
二进制格式，记录速度≥0的定位信息
|
|||
|
静止摘要信息
|
BLOB
|
≈58K
|
二进制格式，记录速度=0的定位信息，待定字段
|
|||
|
项目
|
类型
|
长度(Byes)
|
说明
|
|
序号
|
number
|
10
|
格式为YYYYMMDDHH24+8位序号，共18位，每车每小时产生的记录数为180，8位序号可以支持10万车辆的定位服务
|
|
号牌信息
|
Varchar2
|
8
|
粤A4811B格式
|
|
卡号信息
|
Number
|
7
|
如：13828838124
|
|
定位时间
|
Date
|
8
|
YYYYMMDDHH24MISS
|
|
记录时间
|
Date
|
8
|
YYYYMMDDHH24MISS
|
|
速度
|
Number
|
3
|
0≤SPEED≤999
|
|
里程
|
Number
|
4
|
0.00≤ MILEAGE≤9999.99
|
|
经度
|
Number
|
5
|
0.0000≤LONGITUDE≤180.0000，如果考虑境外定位，需要增加1位，可以支持最小-999.999
|
|
纬度
|
Number
|
5
|
-90.0000≤LATITUDE≤90.000
|
|
角度
|
Number
|
3
|
0≤ANGLE≤999
|
|
状态
|
CHAR
|
2
|
十六进制，支持8种状态，为2n，1≤n≤8， 1≤STATUS≤255
|
|
合计
|
|
63
|
|
