mysql이 데이터를 느리게 삽입하는 이유-Java베이스-php.cn

mysql이 데이터를 느리게 삽입하는 이유

藏色散人

풀어 주다： 2020-11-01 14:54:25

원래의

11256명이 탐색했습니다.

mysql이 데이터를 느리게 삽입하는 이유: 1. 기본 코드, 외부 코드 및 인덱스로 인해 삽입 효율성이 감소합니다. 2. 삽입을 위해 이 메서드를 계속 실행하기 위해 for 루프를 사용하기 때문입니다. 쿼리 결과가 제때 공개될 수 없습니다.

mysql이 데이터를 느리게 삽입하는 이유

최근 프로젝트에서는 대량의 데이터를 가져와야 하며, 삽입 과정에서도 쿼리와 삽입이 동시에 필요합니다. 삽입되는 데이터의 양은 약 100만개이다. 처음에는 100만개의 데이터가 큰 용량이 아니라고 생각하여 전원을 연결하고 연결하고 식사를 하고 돌아와서 50w가 넘는 데이터를 삽입한 후에야 겨우 알 수 있었습니다. 초당 10개를 삽입합니다. . 느낌이 너무 이상해요 왜 넣을수록 점점 느려지나요? 그래서 삽입 시간 손실을 분석하기 시작했고, 다음과 같은 해결책을 생각해 냈습니다. (INNODB 엔진이 사용하는 mysql)

1. 메인 코드, 외부 코드 및 기타 코드로 인해 삽입 효율성이 저하되는지 분석합니다. index

메인코드 : 모든 테이블에 메인코드가 필요하므로 삭제할 수 없습니다. MySQL은 기본 코드에 대한 인덱스를 자동으로 생성합니다. 이 인덱스는 기본적으로 Btree 인덱스이므로 데이터를 삽입할 때마다 추가 Btree를 삽입해야 합니다. 이 추가 삽입 시간 복잡성은 log(n)에 관한 것입니다. 이 인덱스는 삭제할 수 없으므로 최적화할 수 없습니다. 그런데 삽입될 때마다 메인 코드의 제약으로 인해 메인 코드가 나타나는지 확인해야 하는데, 이는 log(n)을 필요로 합니다. 이 오버헤드를 줄일 수 있을까요? 대답은 '예'입니다. 메인 코드를 자동 증가 ID AUTO_INCREMENT로 설정할 수 있습니다. 그러면 현재 자동 증가 값이 데이터베이스에 자동으로 기록되어 중복된 메인 코드가 삽입되지 않도록 하여 메인 코드의 중복 검사를 피할 수 있습니다. 암호. '' '' ''웨이 'S'는 '' '' '' 'through' 'through' '' '' '' '' "" "" ""전반을 통해 (부터 1 ~ 1 일부터 통과)를 통해 1 ~부터 ~부터 전반을 통해 전반적으로 전반적으로 전반적으로 전반적으로 전반적으로 전달됩니다. ‐ ‐ ‐ ‐ ‐ ‐‐‐‐‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ 다운     이 제약조건은 비즈니스 로직과 관련되어 있어 임의로 삭제할 수 없습니다. 그리고 이 시간 비용은 다른 테이블의 크기에 비례하여 일정해야 하며 삽입 횟수가 늘어나도 속도가 느려져서는 안 됩니다. 그래서 제외되었습니다.

인덱스: Btree 삽입에 따른 시간 손실을 줄이기 위해

테이블 생성 시 인덱스를 생성하지 않고 먼저 모든 데이터를 삽입할 수 있습니다. 그런 다음 테이블에 인덱스를 추가합니다. 이 방법은 실제로 시간 오버헤드를 줄여줍니다.

위와 같이 던지고 테스트한 결과 속도가 조금 빨라졌다가 500,000바에 도달한 후 다시 느려지기 시작했습니다. 문제의 핵심은 여기에 있지 않은 것 같습니다. 그래서 계속 정보를 확인하다가 중요한 문제를 발견했습니다. 2. 단일 삽입을 일괄 삽입으로 변경합니다. (참조: 링크를 열려면 클릭하세요.)

Java의 ExecuteUpdate(sql) 메소드는 sql 작업을 수행하려면 SQL에서 다양한 리소스를 호출해야 합니다. for 루프를 사용하여 이 메서드를 지속적으로 실행하여 삽입하면 비용이 많이 듭니다. 따라서 MySQL은 일괄 삽입이라는 솔루션을 제공합니다. 즉, 각 SQL은 직접 제출되지 않고 먼저 배치 작업 세트에 저장됩니다. 작업 세트의 크기가 지정된 임계값에 도달하면 이러한 SQL이 함께 mysql 끝으로 전송됩니다. 100만 개의 데이터 규모에서는 임계값을 10,000으로 설정했습니다. 즉, 한 번에 10,000개의 SQL 문이 제출됩니다. 최종 결과는 꽤 좋으며 삽입 속도는 이전보다 약 20배 빠릅니다. 일괄 삽입 코드는 다음과 같습니다.

public static void insertRelease() {  
        Long begin = new Date().getTime();  
        String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)";  
        try {  
            conn.setAutoCommit(false);  
            PreparedStatement pst = conn.prepareStatement(sql);  
            for (int i = 1; i <= 100; i++) {  
                for (int k = 1; k <= 10000; k++) {  
                    pst.setLong(1, k * i);  
                    pst.setLong(2, k * i);  
                    pst.addBatch();  
                }  
                pst.executeBatch();  
                conn.commit();  
            }  
            pst.close();  
            conn.close();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        Long end = new Date().getTime();  
        System.out.println("cast : " + (end - begin) / 1000 + " ms");  
    }

로그인 후 복사

3. UPDATE 문의 VALUES 뒤에는 여러 개의 (?,?,?,?)

이 방법이 처음에는 위와 비슷하다고 생각했습니다. , 그러나 다른 사람들이 수행한 실험을 연구한 후 이 방법을 사용하여 위의 일괄 삽입을 개선하는 것이 5배 더 빠를 수 있다는 것을 발견했습니다. 나중에 나는 MySQL에서 내보낸 SQL 파일의 삽입 문도 이와 같이 작성되었음을 발견했습니다. . 이는 UPDATE table_name (a1,a2) VALUES (xx,xx),(xx,xx),(xx,xx)... 입니다. 즉, 문자열을 백그라운드에서 직접 연결해야 합니다. 문자열은 끝까지만 삽입되므로 StringBuffer를 사용하면 더 빠르게 삽입할 수 있습니다. 코드는 다음과 같습니다.

public static void insert() {  
        // 开时时间  
        Long begin = new Date().getTime();  
        // sql前缀  
        String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES ";  
        try {  
            // 保存sql后缀  
            StringBuffer suffix = new StringBuffer();  
            // 设置事务为非自动提交  
            conn.setAutoCommit(false);  
            // Statement st = conn.createStatement();  
            // 比起st，pst会更好些  
            PreparedStatement pst = conn.prepareStatement("");  
            // 外层循环，总提交事务次数  
            for (int i = 1; i <= 100; i++) {  
                // 第次提交步长  
                for (int j = 1; j <= 10000; j++) {  
                    // 构建sql后缀  
                    suffix.append("(" + j * i + ", SYSDATE(), " + i * j  
                            * Math.random() + "),");  
                }  
                // 构建完整sql  
                String sql = prefix + suffix.substring(0, suffix.length() - 1);  
                // 添加执行sql  
                pst.addBatch(sql);  
                // 执行操作  
                pst.executeBatch();  
                // 提交事务  
                conn.commit();  
                // 清空上一次添加的数据  
                suffix = new StringBuffer();  
            }  
            // 头等连接  
            pst.close();  
            conn.close();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        // 结束时间  
        Long end = new Date().getTime();  
        // 耗时  
        System.out.println("cast : " + (end - begin) / 1000 + " ms");  
    }

로그인 후 복사

做了以上的优化后，我发现了一个很蛋疼的问题。虽然一开始的插入速度的确快了几十倍，但是插入了50w条数据后，插入速度总是会一下突然变的非常慢。这种插入变慢是断崖式的突变，于是我冥思苦想，无意中打开了系统的资源管理器，一看发现：java占用的内存在不断飙升。突然脑海中想到：是不是内存溢出了？

4.及时释放查询结果

在我的数据库查询语句中，使用到了pres=con.prepareStatement(sql)来保存一个sql执行状态，使用了resultSet=pres.executeQuery来保存查询结果集。而在边查边插的过程中，我的代码一直没有把查询的结果给释放，导致其不断的占用内存空间。当我的插入执行到50w条左右时，我的内存空间占满了，于是数据库的插入开始不以内存而以磁盘为介质了，因此插入的速度就开始变得十分的低下。因此，我在每次使用完pres和resultSet后，加入了释放其空间的语句：resultSet.close(); pres.close(); 。重新进行测试，果然，内存不飙升了，插入数据到50w后速度也不降低了。原来问题的本质在这里！

위 내용은 mysql이 데이터를 느리게 삽입하는 이유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!