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

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

권장: "mysql tutorial"

MySQL InnoDB에서 일부 성능 테스트를 마친 후 참조용 삽입 효율성을 향상시킬 수 있는 몇 가지 방법을 발견했습니다.

1. 하나의 SQL 문으로 여러 데이터 조각을 삽입합니다.

일반적으로 사용되는 삽입 문:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>

은 다음과 같이 수정됩니다.

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0), (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>

수정된 삽입 작업은 프로그램의 삽입 효율성을 향상시킬 수 있습니다. 여기서 두 번째 SQL 실행 효율이 높은 주된 이유는 병합 후 로그(MySQL의 binlog와 innodb의 트랜잭션 로그) 양이 줄어들어 로그 플러싱의 양과 빈도가 줄어들어 효율성이 향상되기 때문이다. SQL 문을 병합하면 SQL 문을 구문 분석하는 횟수도 줄어들고 네트워크 전송 IO도 줄일 수 있습니다.

다음은 단일 데이터를 가져와서 가져오기 위해 SQL 문으로 변환하고 각각 100, 1,000, 10,000개의 데이터 레코드를 테스트하는 테스트 비교 데이터입니다.

2. 트랜잭션에서 삽입 처리를 수행합니다.

삽입을 다음으로 변경:

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>...
COMMIT;

트랜잭션을 사용하면 데이터 삽입의 효율성을 높일 수 있습니다. 이는 INSERT 작업이 수행되면 MySQL 내부적으로 트랜잭션이 설정되고 실제 삽입 처리 작업은 내부에서 수행되기 때문입니다. 거래. 트랜잭션을 사용하면 트랜잭션 생성 비용을 줄일 수 있습니다. 모든 삽입은 커밋 전에 실행됩니다.

여기에서도 트랜잭션을 사용하지 않는 경우와 레코드 개수가 100, 1000, 10000일 때 트랜잭션을 사용하는 테스트 비교도 제공됩니다.

3. 데이터가 순서대로 삽입됩니다.

순서대로 데이터를 삽입한다는 것은 삽입된 레코드가 기본 키에 순서대로 정렬된다는 의미입니다. 예를 들어 datetime은 레코드의 기본 키입니다.

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;2&#39;, &#39;userid_2&#39;, &#39;content_2&#39;,2);<br/>

수정:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('2', 'userid_2', 'content_2',2);

인덱스 데이터를 유지해야 하기 때문입니다. 데이터베이스에 삽입할 때 정렬되지 않은 레코드는 인덱스 유지 비용을 증가시킵니다. InnoDB에서 사용하는 B+트리 인덱스를 참조할 수 있습니다. 삽입된 각 레코드가 인덱스 끝에 있으면 인덱스 위치 지정 효율성이 매우 높고 삽입된 레코드가 인덱스 중간에 있으면 인덱스 조정이 적습니다. , B+트리가 필요합니다. 분할, 병합 등의 프로세스는 더 많은 컴퓨팅 리소스를 소비하며, 삽입된 레코드의 인덱스 위치 지정 효율성이 떨어지게 됩니다.

아래는 랜덤 데이터와 순차 데이터의 성능 비교를 제공하며 각각 100, 1000, 10000, 100000 및 100만으로 기록됩니다.

테스트 결과에 따르면 이 최적화 방법의 성능이 향상되었지만 개선 효과가 그다지 뚜렷하지 않습니다.

4. 종합 성능 테스트

위의 세 가지 방법을 동시에 사용하여 INSERT 효율성을 최적화하는 테스트입니다.

데이터의 양이 적을 때(1,000만 개 이상) 데이터 + 트랜잭션 병합 방식의 성능 향상이 확연히 나타나는 것을 테스트 결과에서 알 수 있습니다. 이는 이때 데이터의 양이 innodb_buffer의 용량을 초과하기 때문입니다. 각 인덱스 위치 지정에는 더 많은 디스크 읽기 및 쓰기 작업이 포함되어 성능이 급격히 저하됩니다. 병합된 데이터 + 트랜잭션 + 정렬된 데이터를 사용하는 방법은 데이터 볼륨이 수천만에 도달하는 경우에도 여전히 잘 수행됩니다. 데이터 볼륨이 큰 경우 정렬된 데이터 인덱스 위치 지정이 더 편리하고 디스크에서 빈번한 읽기 및 쓰기 작업이 필요하지 않습니다. 따라서 높은 성능을 유지할 수 있습니다.

주의 사항:

1. 동일한 SQL에서 데이터를 병합할 때 SQL 길이 제한은 max_allowed_packet 구성을 통해 수정될 수 있습니다. 테스트 중에는 8M까지.

2. 트랜잭션 크기를 제어해야 합니다. 트랜잭션이 너무 크면 실행 효율성에 영향을 미칠 수 있습니다. MySQL에는 innodb_log_buffer_size 구성 항목이 있는데, 이 값을 초과하면 innodb 데이터가 디스크로 플러시되므로 효율성이 떨어집니다. 따라서 더 나은 접근 방식은 데이터가 이 값에 도달하기 전에 트랜잭션을 커밋하는 것입니다.

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