Create GUIDs online 全球唯一主键 作者:QQ 14588019 WonderfulLife
本范例中不能使用guid编号作为明细的id,如果这么干,明细将无法查询,分页就更别谈啦
https://www.guidgen.com/
百度拾取坐标系统
http://api.map.baidu.com/lbsapi/getpoint/index.html
站长工具 时间转 unix时间戳
http://tool.chinaz.com/Tools/unixtime.aspx
假如有这样一个主从表,记录全国每个人一生当中移动的轨迹,每1秒记录一次经纬度坐标,
这样我们就可以在此基础数据上分析用户的生活习惯
T95次 武昌-深圳 火车时刻表
114.324393,30.534197 武昌火车站 17:40
114.295448,29.885403 咸宁火车站 18:28
113.936372,29.727576 赤壁火车站 18:56
113.019455,28.200103 长沙火车站 21:17
113.520746,24.756814 韶关东火车站 02:11
113.262337,23.153768 广州火车站 04:32
114.123611,22.537961 深圳火车站 06:13
杨帆乘车时间2018-04-25 17:40:00 在1天的时间内定位表locations会生成86400笔记录,
明细表1个月会生成2592000笔记录(差不多260万笔记录),1年会生成946080000(差不多9.5亿笔记录)
所以这样的主从表,就是个 一对超级多 的数据模型啦
假如这个记录1个人一生的移动轨迹系统从杨帆18岁乘坐T95次武昌-深圳火车开始记录他的移动路线,
跳过18年前的流水号946080000 x 18 = 17029440000 (个),那么系统分配给他的第1个流水号
就应该是1017029440001,这个位数的整数再长命的人也够用啦
给杨帆一个湖南邵阳县的身份证编号430523198612060001
users表(主表)
userid username mobile
430523198612060001 杨帆 13500135000
430523198612060002 田野 13800138000
locations表(从表)
unixtime longitude latitude userid id
1524649200 114.324393 30.534197 430523198612060001 430523198612060001:1017029440001
1524652080 114.295448 29.885403 430523198612060001 430523198612060001:1017029442881
1524653760 113.936372 29.727576 430523198612060001 430523198612060001:1017029444561
1524662220 113.019455 28.200103 430523198612060001 430523198612060001:1017029453021
1524679860 113.520746 24.756814 430523198612060001 430523198612060001:1017029470661
1524688320 113.262337 23.153768 430523198612060001 430523198612060001:1017029479121
1524694380 114.123611 22.537961 430523198612060001 430523198612060001:1017029485181
特别提醒一下主表记录太多啦生老病死,每10万个身份证编号作为1组存储到队列里或者有序集合里,
这样性能一级棒,分页需要身份证集合编号扩容能力也很大很大,也能分页查询啦,例如:
第1组身份证编号集合队列名称的键可命名为user:list:100000 430523198612060001
第2组身份证编号集合队列名称的键可命名为user:list:200000 420921198612069999
MULTI
INCR user:count
# 根据用户数决定当前用户的身份证编号应该存储在那一组队列里,
# 新增用户已经达到503649个,那么它应该存储在键名称为user:list:600000的队列里了
GET user:count
SET user:430523198612060001 "{\"username\":\"杨帆\",\"mobile\":\"13500135000\"}"
LPUSH user:list:100000 430523198612060001 # 队列中保存的身份证编号集合用来分页
EXEC
========================================================================================================
新增第1条明细
SET user:430523198612060001:start 1017029440001 # 系统开始记录的第1条轨迹起始点
MULTI
SET user:430523198612060001:1017029440001 "{\"unixtime\":1524649200,\"longitude\":114.324393,\"latitude\":30.534197}"
SET user:430523198612060001:current 1017029440001 # 系统实时分配的id编号
EXEC
---------------------------------------------------------------------------------------------------
... ... ... ...
... ... ... ...
... ... ... ...
---------------------------------------------------------------------------------------------------
新增第2880条明细
MULTI
SET user:430523198612060001:1017029442881 "{\"unixtime\":1524652080,\"longitude\":114.295448,\"latitude\":29.885403}"
SET user:430523198612060001:current 1017029442881 # 系统实时分配的id编号
EXEC
--------------------------------------------------------------------------------------------------
... ... ... ...
... ... ... ...
... ... ... ...
--------------------------------------------------------------------------------------------------
新增第4560条明细
MULTI
SET user:430523198612060001:1017029444561 "{\"unixtime\":1524653760,\"longitude\":113.936372,\"latitude\":29.727576}"
SET user:430523198612060001:current 1017029444561 # 系统实时分配的id编号
EXEC
--------------------------------------------------------------------------------------------------
... ... ... ...
... ... ... ...
... ... ... ...
--------------------------------------------------------------------------------------------------
新增第13020条明细
MULTI
SET user:430523198612060001:1017029453021 "{\"unixtime\":1524662220,\"longitude\":113.019455,\"latitude\":28.200103}"
SET user:430523198612060001:current 1017029453021 # 系统实时分配的id编号
EXEC
--------------------------------------------------------------------------------------------------
... ... ... ...
... ... ... ...
... ... ... ...
---------------------------------------------------------------------------------------------------
新增第30660条明细
MULTI
SET user:430523198612060001:1017029470661 "{\"unixtime\":1524679860,\"longitude\":113.520746,\"latitude\":24.756814}"
SET user:430523198612060001:current 1017029470661 # 系统实时分配的id编号
EXEC
--------------------------------------------------------------------------------------------------
... ... ... ...
... ... ... ...
... ... ... ...
--------------------------------------------------------------------------------------------------
新增第39120条明细
MULTI
SET user:430523198612060001:1017029479121 "{\"unixtime\":1524688320,\"longitude\":113.262337,\"latitude\":23.153768}"
SET user:430523198612060001:current 1017029479121 # 系统实时分配的id编号
EXEC
--------------------------------------------------------------------------------------------------
... ... ... ...
... ... ... ...
... ... ... ...
-------------------------------------------------------------------------------------------------
新增第45180条明细
MULTI
SET user:430523198612060001:1017029485181 "{\"unixtime\":1524694380,\"longitude\":114.123611,\"latitude\":22.537961}"
SET user:430523198612060001:current 1017029485181 # 系统实时分配的id编号
EXEC
*******************************************************************************************************************
主表分页查询
LLEN user:list:100000 # 返回记录总数
LRANGE user:list:100000 0 20 # 这是第1页的20条记录主键
LRANGE user:list:100000 21 40 # 这是第2页的20条记录主键
LRANGE user:list:100000 41 60 # 这是第3页的20条记录主键
LRANGE user:list:200000 41 60 # 这是第50003页的20条记录主键
LRANGE user:list:200000 61 80 # 这是第50004页的20条记录主键
明细查询
GET user:430523198612060001:start # 首先获得第1条活动轨迹主键后缀流水号,返回值为1017029440001
很容推测出连续的20条明细记录主键编号是以下这些:
user:430523198612060001:1017029440001
user:430523198612060001:1017029440002
user:430523198612060001:1017029440003
user:430523198612060001:1017029440004
user:430523198612060001:1017029440005
user:430523198612060001:1017029440006
user:430523198612060001:1017029440007
user:430523198612060001:1017029440008
user:430523198612060001:1017029440009
user:430523198612060001:1017029440010
user:430523198612060001:1017029440011
user:430523198612060001:1017029440012
user:430523198612060001:1017029440013
user:430523198612060001:1017029440014
user:430523198612060001:1017029440015
user:430523198612060001:1017029440016
user:430523198612060001:1017029440017
user:430523198612060001:1017029440018
user:430523198612060001:1017029440019
user:430523198612060001:1017029440020
这就是杨帆第1页活动轨迹经纬度坐标的主键集合
根据这个推理,我可以选择任一1个小时,或者3个小时,或者10分钟的活动轨迹来单独查询,只不过需要写个简单的算法来获得查询结果罢了
本篇讲解完毕 未经许可,不得商用出版发行!