집 > 기사 > 데이터 베이스 > 대량의 데이터를 삽입하거나 수정하기 위한 여러 MySQL 방법 비교

대량의 데이터를 삽입하거나 수정하기 위한 여러 MySQL 방법 비교

小云云원래의: 2017-11-27 09:57:054832검색

일상적인 비즈니스 데이터 처리든, 데이터베이스 가져오기 및 내보내기든, 대량의 데이터를 삽입하거나 수정해야 하는 경우가 발생할 수 있습니다. 삽입 또는 수정 방법과 데이터베이스 엔진은 모두 삽입 속도에 영향을 미칩니다. 이 기사에서는 향후 응용 프로그램에서 삽입 방법을 쉽게 선택할 수 있도록 이론적, 실제적 관점에서 다양한 방법을 분석하고 비교하는 것을 목표로 합니다.

삽입 분석

MySQL에 레코드를 삽입하는 데 필요한 시간은 다음 요소로 구성되며, 여기서 숫자는 대략적인 비율을 나타냅니다.

연결: (3)
서버에 쿼리 보내기: (2)
분석 쿼리: (2)
레코드 삽입: (1x 레코드 크기)
삽입 인덱스: (1x 인덱스)
닫기: (1)

삽입마다 하나의 SQL 문을 실행하면 연결 및 N 닫기를 제외한 모든 단계를 수행해야 합니다. 시간이 많이 소요됩니다. 최적화하는 방법에는 여러 가지가 있습니다.

(1) 각 삽입 문에 여러 행을 작성하고 일괄 삽입

(2) 모든 쿼리 문을 트랜잭션에 작성

( 3) 사용 Load Data to import data

각 방법의 성능은 다음과 같습니다.

Innodb 엔진

InnoDB는 MySQL에 트랜잭션(커밋), 롤백(롤백) 및 충돌 복구 기능(충돌 복구 기능)을 갖춘 트랜잭션 안전(ACID 호환) 테이블을 제공합니다. InnoDB는 행 잠금(행 수준 잠금) 및 외래 키 제약 조건(FOREIGN KEY 제약 조건)을 제공합니다.

InnoDB는 대용량 데이터베이스 시스템을 처리하도록 설계되었으며 CPU 활용도는 다른 디스크 기반 관계형 데이터베이스 엔진과 비교할 수 없습니다. 기술적으로 InnoDB는 MySQL 배경에 배치된 완전한 데이터베이스 시스템입니다. InnoDB는 데이터 및 인덱스 캐싱을 위해 메인 메모리에 전용 버퍼 풀을 구축합니다.

테스트 환경

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

총 1백만 개의 데이터

삽입 후 데이터베이스 크기는 38.6MB(인덱스 제외), 46.8(인덱스 포함)

총계 인덱스 없는 단일 삽입에 소요된 시간: 229초 최대 메모리: 246KB
인덱스가 있는 단일 삽입에 소요된 총 시간: 242초 최대 메모리: 246KB
인덱스가 없는 일괄 삽입에 소요된 총 시간: 10초 최대 메모리: 8643KB
인덱스가 있는 일괄 삽입에 소요된 총 시간 인덱스: 16초 최대 메모리: 8643KB
인덱스 없이 트랜잭션 삽입에 소요된 총 시간: 78초 최대 메모리: 246KB
인덱스가 있는 트랜잭션 삽입에 소요된 총 시간: 82초 최대 메모리: 246KB
인덱스 없이 로드 데이터 삽입에 소요된 총 시간: 12초 최대 메모리 : 246KB
인덱스가 포함된 데이터 로드를 삽입하는 데 소요된 총 시간 소비 시간: 11초 최대 메모리: 246KB

MyIASM 엔진

MyISAM은 MySQL의 기본 스토리지 엔진입니다. 디자인이 간단하고 전체 텍스트 검색을 지원합니다.

테스트 환경

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

총 1백만 개의 데이터

삽입 후 데이터베이스 크기는 19.1MB(인덱스 제외), 38.6(인덱스 포함)

총계 인덱스 없는 단일 삽입 시간 소모: 82초 최대 메모리: 246KB
인덱스가 있는 단일 삽입의 총 시간: 86초 최대 메모리: 246KB
인덱스가 없는 일괄 삽입의 총 시간: 3초 최대 메모리: 8643KB
인덱스가 있는 일괄 삽입의 총 시간: 7초 최대 메모리: 8643KB
인덱스 없이 로드 데이터 삽입에 걸린 총 시간: 6초 최대 메모리: 246KB
인덱스가 있는 로드 데이터 삽입에 소요된 총 시간: 8초 최대 메모리: 246KB

요약

테스트한 데이터 양은 그리 많지 않습니다. 크지만 대략적인 아이디어를 얻을 수 있습니다. 세 가지 삽입 방법이 속도에 미치는 영향 중 가장 빠른 것은 Load Data 방법이어야 합니다. 이 방법은 파일을 쓰기 때문에 상대적으로 번거롭지만 메모리와 속도의 균형을 맞출 수 있습니다.

테스트 코드

<?php
$dsn = &#39;mysql:host=localhost;dbname=test&#39;;
$db = new PDO($dsn,&#39;root&#39;,&#39;&#39;,array(PDO::ATTR_PERSISTENT => true));
//删除上次的插入数据
$db->query(&#39;delete from `test`&#39;);
//开始计时
$start_time = time();
$sum = 1000000;
// 测试选项
$num = 1;
if ($num == 1){
    // 单条插入
    for($i = 0; $i < $sum; $i++){
        $db->query("insert into `test` (`id`,`name`) values ($i,&#39;tsetssdf&#39;)");
    }
} elseif ($num == 2) {
    // 批量插入，为了不超过max_allowed_packet，选择每10万插入一次
    for ($i = 0; $i < $sum; $i++) {
        if ($i == $sum - 1) { //最后一次
            if ($i%100000 == 0){
                $values = "($i, &#39;testtest&#39;)";
                $db->query("insert into `test` (`id`, `name`) values $values");
            } else {
                $values .= ",($i, &#39;testtest&#39;)";
                $db->query("insert into `test` (`id`, `name`) values $values");
            }
            break;
        }
        if ($i%100000 == 0) { //平常只有在这个情况下才插入
            if ($i == 0){
                $values = "($i, &#39;testtest&#39;)";
            } else {
                $db->query("insert into `test` (`id`, `name`) values $values");
                $values = "($i, &#39;testtest&#39;)";
            }
        } else {
            $values .= ",($i, &#39;testtest&#39;)";    
        }
    }
} elseif ($num == 3) {
    // 事务插入
    $db->beginTransaction(); 
    for($i = 0; $i < $sum; $i++){
        $db->query("insert into `test` (`id`,`name`) values ($i,&#39;tsetssdf&#39;)");
    }
    $db->commit();
} elseif ($num == 4) {
    // 文件load data
    $filename = dirname(__FILE__).&#39;/test.sql&#39;;
    $fp = fopen($filename, &#39;w&#39;);
    for($i = 0; $i < $sum; $i++){
        fputs($fp, "$i,&#39;testtest&#39;\r\n");    
    }
    $db->exec("load data infile &#39;$filename&#39; into table test fields terminated by &#39;,&#39;");
}
$end_time = time();
echo "总耗时", ($end_time - $start_time), "秒\n";
echo "峰值内存", round(memory_get_peak_usage()/1000), "KB\n";

다음은 간단한 SQL 문 삽입 및 수정 문 연결 형식입니다.

(1) 삽입 문 연결:

INSERT INTO user (id, name) VALUES (1, 'Zhang San'); INSERT INTO user (id, name) VALUES (2, 'lee思');
INSERT INTO user (id, name) VALUES (3, '王五');

다음으로 연결될 수 있습니다:

INSERT INTO user ( id, name) VALUES

(1, '张三'),
(2, '이사'),
(3, '王五');

(2) 업데이트 문 연결:

update user set name ='Zhang San' where id='1';

update user set name='lee思' where id='2';
update user set name='Wang Wu' where id='3';

연결 가능 대상:

사용자 업데이트

설정 이름 = CASE ID
when 1 then '张三'
when 2 then '李思'
when 3 then '王五'
end
where id IN (1,2, 3);

위는 많은 양의 데이터를 삽입하거나 수정하는 여러 가지 MySQL 방법을 비교한 것입니다.

대량의 데이터를 삽입하거나 수정하기 위한 여러 MySQL 방법 비교

관련 기사