项目中优化查询速度案例
近期在项目中遇到的问题在本文记录一下。
问题
首先业务内容是通过ip去mysql中查询相应信息,批量导入ip进行查询。库中的数据量大约为553万条。一开始用遍历单条查询的方式查询数据非常慢,查询1.7万条数需要十几分钟(这也太慢了)网页都超时了。
解决方法
项目启动时读数据到项目中(不推荐)
最开始想到的方法就是空间换时间,先把数据查出来存到项目的List里 然后用缓存的方式查询项目中的数据,这样就能避免对数据库的频繁访问。
这种方式存在缺点:
- 拖慢项目启动速度
- 553万条数据读到 List 中,此对象会占用大概500M的内存!
其实如果用这种方法,还可以将这553万ip转为long,然后进行排序。查的时候用二分查。但是数据量稍微有点大,从库中读出来 再排序可太麻烦了!
in 查询
后来经过网上经验学习,如果查询数据库中的数据量远远大于in中的数据,采用此方式会大大加快效率。传入的是一个List,在mapper文件中写动态sql (foreach)就可以了!
SELECT * FROM table where ip in('1','2','3')
如果是库中数据小于 查询数据时候 用Exsits
关键字
当然效率提高是很不错,但是查出来的数据不是按照in
后面输入的顺序,是乱序的!解决方法就是排序oder by 咯 ,那么如何排序?其实有一个MySQL函数可以非常方便的处理此需求 ! field()
函数
field()函数如何使用?
SELECT * FROM table where ip in(1,2,3) order by field(ip,'1','2','3')
具体请看https://www.cnblogs.com/chenhaoyu/p/9771012.html
测试查询速度,
一开始没有优化查询1.7万条数据需要10几分钟。
用不推荐的空间换时间的方式1.7万条查询5分钟查完。
取消空间换时间,最后sql语句优化,8万条数据需要不到40s就查完了。