java - 加速多个 JDBC SQL 查询？

Question

我正在使用 mysql 数据库研究 java 中的最短路径 a* 算法。我在程序中执行了大约 300 次以下 SQL 查询，以从包含 10,000 个公交连接的数据库中查找路线连接。执行查询 300 次大约需要 6-7 秒。关于如何加快速度的任何建议或关于我可以使用的不同方法的任何想法？谢谢

private HashMap<Coordinate,Node> closedNodes;
private PriorityQueue<Node> openNodes;

..
private List<Coordinate> calculatePath() 
{
    //While there are nodes in the open list
    while (!openNodes.isEmpty()) 
    {
        //Get the node with the lowest gVal+hVal
        Node node = openNodes.poll();
        //Add it to the closed list
        closedNodes.put(node);
        //If it is not the goal node
        if (!node.equals(goal)) 
        {   
            //Get all the neighbours and Create neighbour node
            List<Node> neighbours = helper.getNeighbours(node, goal);
            //For each neighbour
            for (Node neighbourNode : neighbours) 
            {
                //Check if the neighbour is in the list of open nodes
                boolean isInOpen = checkOpenNodes(neighbourNode);
                //If it is not in the open nodes and not in the closed nodes
                if ((!closedNodes.containsKey(neighbourNode))&& (!isInOpen)) 
                {
                    //Add it to the list of open nodes
                    openNodes.add(neighbourNode);
                }
            }
        }
        else 
        {
            // We found the path
            path = backTrackPath(node);
            break;              
        }
    }
    return path;

/**
 * Gets the list of valid Nodes that are possible to travel to from <b>Node</b>
 * @param stopNode Node to find neighbours for
 * @param goal End Node
 * @return list of neighbour Nodes
 */
public ArrayList<Node> getNeighbours(Node stopNode, Node goal) 
{
    ArrayList<Node> neighbours = new ArrayList<Node>(); 
    Node neighbourNode;     
    //get neighbours connected to stop  
        try {
            ResultSet rs = stmt.executeQuery("select To_Station_id, To_Station_routeID, To_Station_stopID," +
                    "To_Station_lat, To_Station_lng, Time from connections  where Connections.From_Station_stopID ="
                    +stopNode.getCoord().getStopID()+" ORDER BY Connections.Time");

            rs = stmt.getResultSet();
            while (rs.next()) {
                int id = rs.getInt("To_Station_id");
                String routeID = rs.getString("To_Station_routeID");
                String stopID = rs.getString("To_Station_stopID");
                String stopName = rs.getString("To_Station_stopName");
                Double lat = rs.getDouble("To_Station_lat");
                Double lng = rs.getDouble("To_Station_lng");
                int time = rs.getInt("Time");
                neighbourNode = new Node(id, routeID, stopID, stopName, lat, lng);
                neighbourNode.prev = stopNode;
                neighbourNode.gVal = stopNode.gVal + time;
                neighbourNode.hVal = heuristic.calculateHeuristic(neighbourNode, goal);
                neighbours.add(neighbourNode);
            }
        }
    catch (SQLException e) {
        e.printStackTrace();
    }
    return neighbours;
}

Answer 1

1. 确保您在 connections.From_Station_stopID 上有索引
2. 而不是 SELECT *，只选择您需要的列
3. 如果只有 From_Station_stopID 的 WHERE 子句中的常量每次都更改，请使用参数化的、准备好的查询，这样数据库就不必解析查询和每次构建执行路径，或使用 WHERE From_Station_stopID IN (value1, value2, ...)
将查询合并为一个查询
4. 如果您经常重复相同的查询，请确保 MySQL 使用查询缓存

如果您向我们展示了其余代码，其中循环调用查询 300 次，我们可能能够提供进一步的帮助。

一般来说，如果你每次都计算最短路径，那么你应该构建一个像网格一样工作的表，为每个停靠点预先计算路线距离，甚至从每个停靠点预先计算整条路线停止。