数据库查询性能优化之利器—索引(一)

                   数据库查询性能优化之利器—索引(一)

  最近在做基于Android的公交查询系统的过程中,遇到一个很棘手的问题:换乘算法效率低。在直达查询和一次换乘查询的时候,问题体现的还不是很明显,能够在1s之内查询出乘车方案,而当进行二次查询的时候,基本要等一两分钟才能查询出换乘方案,这对于公交查询系统是绝对无法容忍的。于是找了大量的关于公交换乘算法方面的论文和资料进行研究,发现大多治标不治本,没有从根本上解决公交换乘算法效率低下的问题。公交换乘算法主要有以下三种思路:

  1)基于数据库的查询;

  2)采用Dijkstra或者Floyd算法或者改进的Dijkstra算法;

  3)基于矩阵的运算;

  由于在做这个系统之前就想过这个问题,是采用Dijkstra算法、矩阵运算还是基于数据库的查询。仔细分析之后还是选择了基于数据库的查询,因为对于大中型城市的公交网络,Dijkstra算法和矩阵运算根本不适用,尤其还是基于Android系统的公交查询系统(离线查询系统),举个例子,像成都市的公交网络系统有将近500条公交线路,5000个公交站点,如果采用矩阵运算或者Dijkstra算法的话,就只是直达矩阵就得有5000*5000=25000000个数据,若数据采用int类型保存,则该矩阵得占用25000000*4/(1024*1024*8)=12M的内存空间,这个数据对于移动设备来说想而知,是不可取的办法。因此最后采用了基于数据库查询的方案。

  在我的数据库中有两张表:cnbusw(线路表),cnbus(站点线路表)

     cnbusw的字段:

     id:线路编号,busw:线路名称,shijian:起始站和起始时间描述;

     cnbus的字段:

  zhan:站点名,xid:对应的线路编号,pm:在该线路中的位置,kind:线路类型(去程or返程or环形路线)

  后来发现在进行一次换乘查询的时候需要直达站点信息,因此添加了一个视图direct

  direct的字段:

  start:起始站,end:终点站,route:对应的线路编号,stopcount:两站之间的站点数

  在数据库中进行测试了一下:

  1)直达方案的话,直接在direct中进行查询:

   比如下面一条sql查询语句:

select * from direct where start='磨子桥' and end='春熙路南口'

     查询耗时:119.05ms

  2)一次换乘查询方案,通过direct的自连接进行查询:

     比如下面一条sql查询语句:

select s1.start as start,s1.route as route1,s1.end as zhuan,s2.route as route2,
s2.end as end,s1.stopcount as count1,s2.stopcount as count2,(s1.stopcount+s2.stopcount) 
as stopcount from direct s1,direct s2 where s1.start='崔家店' and s2.end='磨子桥' and 
s1.end=s2.start and s1.route!=s2.route order by (s1.stopcount+s2.stopcount)

     查询耗时:415.32ms

  上述查询时间都在能够接受的范围之内,然后我很自然地按照上面的思路写出了三次换乘的sql查询语句,结果在查询时发现等了一分钟还没等出结果,并且sqlite可视化管理工具界面卡死了,这个很明显地告诉我二次换乘直接使用这样的sql语句是绝对不可行的,于是我想了一些办法,比如:两次换乘相当于是A->B->C->D,那么先查询经过站点D的线路中站点序号比D小的站点集合U,然后就相当于查询A到U集合中所有站点的一次换乘查询,这样就方便了,但是用sql语句查询了一下,一般U集合的大小都在400左右,然后要进行400次一次换乘查询,耗时400ms*400=160000ms=160s,怎么也得2分多钟,显然不可取。在陷入了这样的僵局之后,想了很久没想到办法,因为数据库里面的数据量很大,简单地对sql语句进行改进无法解决根本性问题。只有从数据库本身着手,于是想到了索引,以前上数据库课程的时候,老师提到过索引对于大量数据查询的时候效率提升的很明显,于是就尝试了一下,发现查询耗时一下减少了很多。在建立索引的过程中,原本想在视图上建立索引,发现似乎sqlite不支持在视图上创建索引,于是删除了direct视图,建立了direct直达表,在建立索引的时候,考虑是建立多列索引还是单列索引,分析一次换乘查询发现要将start,end,route这三列同时作为查询条件,并且要对stopcount进行排序,因此选择创建多列索引:

create index directindex on direct(start,end,route,stopcount)

  在创建索引之后,重新将上面的一次换乘查询语句执行了一次,耗时:5.59ms,查询速度提升了将近80倍,然后又将直达查询语句执行了一次,耗时0.49ms,也明显提升了不少。然后按照一次换乘的思路写出了二次换乘的sql语句进行执行,

select s1.start as start,s1.xid as route1,s1.end as zhuan1,s2.xid as route2 ,
s2.end as zhuan2,s3.xid as route3,s3.end as end,s1.stopcount,s2.stopcount,s3.stopcount,
(s1.stopcount+s2.stopcount+s3.stopcount) from direct s1,direct s2,direct s3 where 
s1.start='川大路黄河路口' and s3.end='春熙路南口' and s1.end=s2.start and s2.end=s3.start 
and s1.xid!=s2.xid and s2.xid!=s3.xid order by s1.stopcount+s2.stopcount+s3.stopcount

  耗时411.15ms,很好地解决查询效率低下的问题,从这里可以看出,在数据量很大的情况,建立索引和没有索引的区别简直是天壤之别,适当地建立索引能够使查询速度提升很大,关于索引的相关问题准备在后面进行进一步学习和研究。

 

posted @ 2012-08-25 00:58  Matrix海子  阅读(5734)  评论(3编辑  收藏  举报