加速大数据集休眠查询的数据库设计

Question

我目前有下表代表在休眠中映射的公交网络，可从基于 Spring MVC 的公交路线规划器访问 我试图让我的路线规划器应用程序执行得更快，我将上述所有表格加载到列表中以执行路线规划器逻辑。

如果有人对如何提高我的表现有任何想法，我将不胜感激 或者有任何其他方法来解决处理大量数据的问题

    Coordinate Connections Table (INT,INT,INT)( Containing 50,000 Coordinate Connections)
    ID, FROMCOORDID, TOCOORDID
    1   1               2       
    2   1               17      
    3   1               63      
    4   1               64      
    5   1               65      
    6   1               95      

    Coordinate Table (INT,DECIMAL, DECIMAL) (Containing 4700 Coordinates)
    ID ,  LAT,       LNG
    0   59.352669   -7.264341
    1   59.352669   -7.264341
    2   59.350012   -7.260653
    3   59.337585   -7.189798
    4   59.339221   -7.193582
    5   59.341408   -7.205888

    Bus Stop Table (INT, INT, INT)(Containing 15000 Stops)
    StopID    RouteID COORDINATEID
    1000100001  100     17  
    1000100002  100     18  
    1000100003  100     19  
    1000100004  100     20  
    1000100005  100     21  
    1000100006  100     22  
    1000100007  100     23  

这是从每个表加载所有数据所需的时间：

    stop.findAll  = 148ms,  stops.size: 15670
    Hibernate: select coordinate0_.COORDINATEID as COORDINA1_2_, coordinate0_.LAT as LAT2_, coordinate0_.LNG as LNG2_ from COORDINATES coordinate0_
    coord.findAll =  51ms , coordinates.size: 4704
    Hibernate: select coordconne0_.COORDCONNECTIONID as COORDCON1_3_, coordconne0_.DISTANCE as DISTANCE3_, coordconne0_.FROMCOORDID as FROMCOOR3_3_, coordconne0_.TOCOORDID as TOCOORDID3_ from COORDCONNECTIONS coordconne0_
    coordinateConnectionDao.findAll  = 238ms ; coordConnectioninates.size:48132

Hibernate Annotations

    @Entity
    @Table(name = "STOPS")
    public class Stop implements Serializable {

        @Id
        @GeneratedValue
        @Column(name = "COORDINATEID")
        private Integer CoordinateID;


        @Column(name = "LAT")
        private double latitude;


        @Column(name = "LNG")
        private double longitude;

    }




    @Table(name = "COORDINATES")
    public class Coordinate {

        @Id
        @GeneratedValue
        @Column(name = "COORDINATEID")
        private Integer CoordinateID;


        @Column(name = "LAT")
        private double latitude;


        @Column(name = "LNG")
        private double longitude;
    }

    @Entity
    @Table(name = "COORDCONNECTIONS")
    public class CoordConnection {

        @Id
        @GeneratedValue
        @Column(name = "COORDCONNECTIONID")
        private Integer CoordinateID;

        /**
         * From Coordinate_id value
         */
        @Column(name = "FROMCOORDID", nullable = false)
        private int fromCoordID;

        /**
         * To Coordinate_id value
         */
        @Column(name = "TOCOORDID", nullable = false)
        private int toCoordID;
        //private Coordinate toCoordID;
    }

HashMap -> CoodinateID 到坐标代码

private void setupCoordinateIDToCoordinate() {
    HashMap<Integer, Coordinate> coordinateIDToCoordinate = new HashMap<Integer, Coordinate>();
    List<Coordinate> coordinates = coordinateDao.findAll();
    Iterator <Coordinate> itr = coordinates.iterator();
    Coordinate c;
    while(itr.hasNext()) {
        c = itr.next();
        coordinateIDToCoordinate.put(c.getCoordinateID(),c);
    }
}

Answer 1

看看如何使用 mysql 中的解释命令，让您了解最好将索引放在哪里。

下面的链接很好地解释了这一点。

http://forums.spry.com/howtos/1345-使用-mysqls-explain-command.html

Answer 2

使用 Hibernate 实现 EhCache 帮助我提高了性能