文章目录
- 1、提出问题
- 1.1 问题测试
- 2、解决问题(三种方案)
- 2.1、方案一:查询的时候,只返回主键 ID
- 2.2、方案二:查询的时候,通过主键 ID 过滤
- 2.3、方案三:采用 elasticSearch 作为搜索引擎
- 3、总结
1、提出问题
在某车辆监控项目的软件系统开发过程中,记录每台车的行驶轨迹表数据会随着时间的推移,单表的数据量会越来越大(每台车辆10秒记录一条)。
每台车的数据体量:
车辆 | 记录方式 | 天/按8个小时计算 | 月 | 年 |
---|---|---|---|---|
每台车辆 | 10秒一条记录 | 2880 | 86400 | 1036800 |
可见,车辆轨迹数据的查询不会像最初那样简单快速,如果查询关键字段没有走索引,会直接影响到用户体验,甚至会影响到服务是否能正常运行!
下面就某月车辆行驶轨迹表为例,数据库是 Mysql,数据体量在 900 万以上,详细介绍分页查询下,不同阶段的查询效率情况。
1.1 问题测试
下面我们一起来测试一下,每次查询车辆行驶轨迹表时最多返回 100 条数据,不同的起始下,数据库查询性能的差异。
查询sql语句 | 起点位置 | 查询数量 | 耗时(秒) |
---|---|---|---|
SELECT * FROM t_location_log ORDER BY id LIMIT 0,100 | 0 | 100 | 0.830s |
SELECT * FROM t_location_log ORDER BY id LIMIT 10000,100 | 0 | 100 | 0.876s |
SELECT * FROM t_location_log ORDER BY id LIMIT 1000000,100 | 0 | 100 | 1.038s |
SELECT * FROM t_location_log ORDER BY id LIMIT 2000000,100 | 0 | 100 | 1.248s |
SELECT * FROM t_location_log ORDER BY id LIMIT 3000000,100 | 0 | 100 | 1.326s |
SELECT * FROM t_location_log ORDER BY id LIMIT 4000000,100 | 0 | 100 | 1.526s |
SELECT * FROM t_location_log ORDER BY id LIMIT 5000000,100 | 0 | 100 | 1.906s |
可以看出,随着起点位置越大,分页查询效率下降明显,一般查询耗时超过 1 秒的 SQL 都被称为慢 SQL,事实上,这还只是数据库层面的耗时,还没有算后端服务的处理链路时间,以及返回给前端的数据渲染时间,以百万级的单表查询为例,如果数据库查询耗时 1 秒,再经过后端的数据封装处理,前端的数据渲染处理,以及网络传输时间,没有异常的情况下,差不多在 3~4 秒之间,必须在限定的时间内尽快优化,不然可能会影响服务的正常运行和用户体验。
对于千万级的单表数据查询,我也测试了一下,查询耗时结果:43 秒!
据互联网软件用户体验报告:
- b当平均请求耗时在1秒之内,用户体验是最佳的,此时的软件也是用户留存度最高的;
- 2 秒之内,还勉强过的去,用户能接受;
- 当超过 3 秒,体验会稍差;超过 5 秒,基本上会卸载当前软件。
2、解决问题(三种方案)
2.1、方案一:查询的时候,只返回主键 ID
我们继续回到上文给大家介绍的客户表查询,将select *改成select id,简化返回的字段,我们再来观察一下查询耗时。
查询sql语句 | 起点位置 | 查询数量 | 耗时(秒) |
---|---|---|---|
SELECT id FROM t_location_log ORDER BY id LIMIT 0,100 | 0 | 100 | 0.649s |
SELECT id FROM t_location_log ORDER BY id LIMIT 10000,100 | 0 | 100 | 0.713s |
SELECT id FROM t_location_log ORDER BY id LIMIT 1000000,100 | 0 | 100 | 0.883s |
SELECT id FROM t_location_log ORDER BY id LIMIT 2000000,100 | 0 | 100 | 1.107s |
SELECT id FROM t_location_log ORDER BY id LIMIT 3000000,100 | 0 | 100 | 1.272s |
SELECT id FROM t_location_log ORDER BY id LIMIT 4000000,100 | 0 | 100 | 1.452s |
SELECT id FROM t_location_log ORDER BY id LIMIT 5000000,100 | 0 | 100 | 1.753s |
通过对比发现,通过简化返回的字段,可以提升查询效率。
实际的操作思路就是先通过分页查询满足条件的主键 ID,然后通过主键 ID 查询部分数据,可以显著提升查询效果。
-- 先分页查询满足条件的主键ID
select id from t_location_log order by id limit 100000,10;-- 再通过分页查询返回的ID,批量查询数据
select * from t_location_log where id in (1,2,3,4,.....);
2.2、方案二:查询的时候,通过主键 ID 过滤
这种方案有一个要求就是主键ID,必须是数字类型,实践的思路就是取上一次查询结果的 ID 最大值,作为过滤条件,而且排序字段必须是主键 ID,不然分页排序顺序会错乱。
查询sql语句 | 耗时(秒) |
---|---|
SELECT id FROM t_location_log WHERE id > 100000 ORDER BY id LIMIT 100 | 0.636s |
SELECT id FROM t_location_log WHERE id > 500000 ORDER BY id LIMIT 100 | 0.669s |
SELECT id FROM t_location_log WHERE id > 1000000 ORDER BY id LIMIT 100 | 0.738s |
带上主键 ID 作为过滤条件,查询性能非常的稳定,基本上在0.69 s内可以返回。
这种方案还是非常可行的,如果当前业务对排序要求不多,可以采用这种方案,性能也非常杠!但是如果当前业务对排序有要求,比如通过客户最后修改时间、客户最后下单时间、客户最后下单金额等字段来排序,那么上面介绍的【方案一】,比【方案二】查询效率更高!
2.3、方案三:采用 elasticSearch 作为搜索引擎
当数据量越来越大的时候,尤其是出现分库分表的数据库,以上通过主键 ID 进行过滤查询,效果可能会不尽人意,还有另一种比较好的解决办法就是将数据存储到 elasticSearch 中,通过 elasticSearch 实现快速分页和搜索,效果提升也是非常明显。
3、总结
上文中介绍的表主键 ID 都是数值类型的,之所以采用数字类型作为主键,是因为数字类型的字段能很好的进行排序。但如果当前表的主键 ID 是字符串类型,比如 uuid 这种,就没办法实现这种排序特性,而且搜索性能也非常差,因此不建议大家采用 uuid 作为主键ID,具体的数值类型主键 ID 的生成方案有很多种,比如自增、雪花算法等等,都能很好的满足我们的需求。
希望本文中的一些sql查询技巧给你实际工作中帮到你。
人生从来没有真正的绝境。只要一个人的心中还怀着一粒信念的种子,那么总有一天,他就能走出困境,让生命重新开花结果。