java-提高建立索引表的性能

gcuhipw9  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(265)

我正在使用倒排文件方法进行全文索引,它提取文档中的所有单词,并将每个单词逐个插入mysql的表中。
到目前为止,我的程序运行得非常好,但我一直在思考如何进一步优化它,以提高插入db所需的时间。我知道倒排文件的缺点是建立索引表的时间很慢。
这是我的密码:

public class IndexTest {

    public static void main(String[] args) throws Exception {

        StopWatch stopwatch = new StopWatch();
        stopwatch.start();

        File folder = new File("D:\\PDF1");
        File[] listOfFiles = folder.listFiles();

        for (File file : listOfFiles) {
            if (file.isFile()) {
                HashSet<String> uniqueWords = new HashSet<>();
                String path = "D:\\PDF1\\" + file.getName();
                try (PDDocument document = PDDocument.load(new File(path))) {

                    if (!document.isEncrypted()) {

                        PDFTextStripper tStripper = new PDFTextStripper();
                        String pdfFileInText = tStripper.getText(document);
                        String lines[] = pdfFileInText.split("\\r?\\n");
                        for (String line : lines) {
                            String[] words = line.split(" ");

                            for (String word : words) {
                                uniqueWords.add(word);

                            }

                        }
                        // System.out.println(uniqueWords);

                    }
                } catch (IOException e) {
                    System.err.println("Exception while trying to read pdf document - " + e);
                }
                Object[] words = uniqueWords.toArray();
                String unique = uniqueWords.toString();
                //  System.out.println(words[1].toString());

                for(int i = 1 ; i <= words.length - 1 ; i++ ) {
                    MysqlAccessIndex connection = new MysqlAccessIndex();
                    connection.readDataBase(path, words[i].toString());

                }

                System.out.println("Completed");

            }
        }
       stopwatch.stop();
        long timeTaken = stopwatch.getTime();
        System.out.println(timeTaken);

mysql连接:

public class MysqlAccessIndex {
    public Connection connect = null;
    public Statement statement = null;
    public PreparedStatement preparedStatement = null;
    public ResultSet resultSet = null;

    public MysqlAccessIndex() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager
                .getConnection("jdbc:mysql://126.32.3.178/fulltext_ltat?"
                        + "user=root&password=root123");
      //  statement = connect.createStatement();
        System.out.print("Connected");
    }

    public void readDataBase(String path,String word) throws Exception {
        try {

            preparedStatement = connect
                    .prepareStatement("insert IGNORE into  fulltext_ltat.test_text values (?, ?) ");

            preparedStatement.setString(1, path);
            preparedStatement.setString(2, word);
            preparedStatement.executeUpdate();

        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }

    }

有没有可能,如果我可以使用某种多线程来说,插入三个字在三行在同一时间,以加快插入过程或某种排序?如有任何建议,我将不胜感激。

sy5wg1nm

sy5wg1nm1#

我认为解决您的问题的办法是使用批量插入。你可以尝试这样做:

public void readDataBase(String path, HashSet<String> uniqueWords) throws Exception {

    PreparedStatement preparedStatement;

    try {

        String compiledQuery = "insert IGNORE into  fulltext_ltat.test_text values (?, ?) ";
        preparedStatement = connect.prepareStatement(compiledQuery);

        for(String word : uniqueWords) {
            preparedStatement.setString(1, path);
            preparedStatement.setString(2, word);
            preparedStatement.addBatch();
        }

        long start = System.currentTimeMillis();
        int[] inserted = preparedStatement.executeBatch();

        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }
}

修改您的 readDataBase 拥有的方法 HashSet<String> uniqueWords 在参数中。
在那之后你应该加上 preparedStatement.addBatch() 在每个项之后调用以插入和执行 preparedStatement.executeBatch() 而不是 preparedStatement.executeUpdate() 最后。
我希望这会有帮助。

相关问题