MySQL 배치 SQL 삽입 성능 최적화에 대한 자세한 설명

                                                       

mysql tutorial 열에 일괄 SQL 삽입 도입

권장(무료): mysql tutorial

문제 데이터베이스에 직면하면 쿼리 효율성이 낮고 데이터 저장 시간이 깁니다. 특히 보고 시스템의 경우 데이터 가져오기에 소요되는 시간은 매일 몇 시간에서 10시간 이상 지속될 수 있습니다. 따라서 데이터베이스 삽입 성능을 최적화하는 것이 합리적입니다.

하나의 SQL 문으로 여러 개의 데이터를 삽입합니다

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('1', 'userid_1', 'content_1', 1);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

• 두 번째 SQL 실행 효율성이 높은 주된 이유는 [mysql의 binlog와 InnoDB의 트랜잭션 로그]를 병합한 후의 로그 양이 줄어들기 때문입니다. 로그 플러시 디스크 데이터 양과 빈도를 줄여 효율성을 향상시킵니다.
• SQL 문을 병합하면 SQL 문을 구문 분석하는 횟수도 줄어들고 네트워크 전송 IO도 줄일 수 있습니다.

테스트 및 비교 데이터에는 단일 데이터를 가져오고 이를 가져오기 위해 SQL 문으로 변환하는 작업이 포함됩니다.

트랜잭션에서의 삽입 처리

START TRANSACTION;INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('1', 'userid_1', 'content_1', 1);...COMMIT;

• 트랜잭션을 사용하면 데이터 삽입의 효율성을 높일 수 있습니다. 이는 삽입 작업을 수행할 때 MySQL 내부에서 트랜잭션이 설정되고 실제 삽입이 이루어지기 때문입니다. 트랜잭션 처리 작업 내에서 수행됩니다.
• 트랜잭션을 사용하여 트랜잭션 생성 소비를 줄입니다.

데이터를 테스트하고 비교합니다.

순서 있는 데이터 삽입입니다. 기본 키에 삽입된 레코드 순서 정렬

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('1', 'userid_1', 'content_1', 1);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('2', 'userid_2', 'content_2',2);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('1', 'userid_1', 'content_1', 1);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('2', 'userid_2', 'content_2',2);

데이터베이스는 삽입 시 인덱스 데이터를 유지해야 하기 때문에 불필요한 레코드는 인덱스 유지 비용을 증가시킵니다.

InnoDB에서 사용하는 B+트리 인덱스를 참조하세요. 삽입된 각 레코드가 인덱스 끝에 있으면 인덱스 위치 지정 효율성이 매우 높고 삽입된 레코드가 중간에 있으면 인덱스 조정이 적습니다. 인덱스의 경우 B+가 필요합니다. 트리를 분할하고 병합하면 컴퓨팅 리소스가 많이 소모되고, 삽입된 레코드의 인덱스 위치 지정 효율성이 떨어지게 되며, 데이터 양이 많아지면 디스크 작업이 자주 발생하게 됩니다. west 테스트 비교 데이터, 랜덤 데이터의 성능 비교 및 ​​순차 데이터

삽입 먼저, 삽입이 완료된 후 인덱스를 재건축합니다.

데이터 병합 + 트랜잭션 방식은 데이터 양이 적을 때 성능이 확연히 향상되는데, 이는 데이터 양이 이때 innodb_buffer의 용량을 초과하기 때문입니다. 각 위치 지정 인덱스에는 더 많은 디스크 읽기 및 쓰기가 포함되며 성능이 빠르게 저하됩니다.

병합된 데이터 + 트랜잭션 + 정렬된 방법은 데이터 볼륨이 수천만에 도달할 때에도 여전히 잘 작동합니다. 데이터 볼륨이 큰 경우 정렬된 데이터 인덱스 위치 지정이 더 편리하고 디스크에서 빈번한 읽기 및 쓰기 작업이 필요하지 않습니다. 더 높게 유지할 수 있습니다

Notes

SQL 문에는 길이 제한이 있습니다. 데이터 병합 시 동일한 SQL에서 SQL 길이 제한을 초과해서는 안 됩니다. 를 통해 수정할 수 있습니다. max_allowed_packet 구성입니다. 기본값은 1M이며 테스트 중에 8M로 수정될 수 있습니다. 트랜잭션의 크기를 제어해야 합니다. 너무 큰 것은 실행 효율성에 영향을 줄 수 있습니다. MySQL에는 innodb_log_buffer_size 구성 항목이 있는데, 이 값을 초과하면 innodb 데이터가 디스크로 플러시되므로 효율성이 떨어집니다. 따라서 더 나은 접근 방식은 데이터가 이 값에 도달하기 전에 트랜잭션 커밋을 수행하는 것입니다.

위 내용은 MySQL 배치 SQL 삽입 성능 최적화에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!