대용량 데이터에서 MySQL 삽입 방법의 성능 비교

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

삽입 분석

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

• 연결 : (3 )

• 서버로 쿼리 보내기: (2)

• 쿼리 분석: (2)

• 레코드 삽입: (1x 레코드 크기)

• 인덱스 삽입: (1x 인덱스)

• 닫기: (1)

항목을 삽입할 때마다 SQL 문을 실행하면 연결 및 닫기를 제외한 모든 단계를 N 번 실행해야 합니다. 이는 여러 가지 최적화 방법이 있습니다.

1. 각 삽입 문에 여러 행 쓰기, 일괄 삽입

2. 모든 쿼리 문을 트랜잭션에 쓰기

3. Load Data를 사용하여 데이터 가져오기

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

Innodb 엔진

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

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

테스트 환경

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

총 100만개 데이터

삽입 후 데이터베이스 크기 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

총 100만개 데이터

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

• 인덱스 없는 단일 삽입에 대한 총 시간: 82초 최대 메모리: 246KB

• 인덱스가 있는 단일 삽입에 걸린 총 시간: 86초 최대 메모리: 246KB

• 인덱스가 없는 일괄 삽입에 걸린 총 시간: 3초 최대 메모리: 8643KB

• 인덱스가 있는 일괄 삽입 총 소요 시간: 7초 최대 메모리: 8643KB

• 인덱스 없이 로드 데이터 삽입에 소요된 총 시간: 6초 최대 메모리: 246KB

• 인덱스로 로드 총 데이터 삽입 시간: 8초 최대 메모리: 246KB

요약

테스트한 데이터 양은 그리 많지 않지만 대략적으로는 이러한 삽입 방법의 속도를 이해하려면 가장 빠른 방법은 데이터 로드 방법이어야 합니다. 이 방법은 파일을 쓰기 때문에 상대적으로 번거롭지만 메모리와 속도를 모두 고려할 수 있습니다.

테스트 코드

<?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";

?>

위는 MySQL에 대용량 데이터를 삽입하기 위한 다양한 방법의 성능 분석 및 비교입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.kr)를 참고해주세요. .php.cn)!