집 >데이터 베이스 >MySQL 튜토리얼 >대용량 데이터에서 MySQL 삽입 방법의 성능 비교
일상적인 비즈니스 데이터 처리이든, 데이터베이스 가져오기 및 내보내기이든, 대량의 데이터 처리가 필요한 삽입이 발생할 수 있습니다. 삽입 방법과 데이터베이스 엔진 모두 삽입 속도에 영향을 미칩니다. 이 기사에서는 향후 응용 프로그램에서 삽입 방법을 쉽게 선택할 수 있도록 이론적, 실제적 관점에서 다양한 방법을 분석하고 비교하는 것을 목표로 합니다.
MySQL에서 레코드를 삽입하는 데 필요한 시간은 다음 요소로 구성되며, 숫자는 대략적인 비율을 나타냅니다.
연결 : (3 )
서버로 쿼리 보내기: (2)
쿼리 분석: (2)
레코드 삽입: (1x 레코드 크기)
인덱스 삽입: (1x 인덱스)
닫기: (1)
항목을 삽입할 때마다 SQL 문을 실행하면 연결 및 닫기를 제외한 모든 단계를 N 번 실행해야 합니다. 이는 여러 가지 최적화 방법이 있습니다.
각 삽입 문에 여러 행 쓰기, 일괄 삽입
모든 쿼리 문을 트랜잭션에 쓰기
Load Data를 사용하여 데이터 가져오기
각 방법의 성능은 다음과 같습니다.
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
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 = 'mysql:host=localhost;dbname=test';
$db = new PDO($dsn,'root','',array(PDO::ATTR_PERSISTENT => true));
//删除上次的插入数据
$db->query('delete from `test`');
//开始计时
$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,'tsetssdf')");
}
} elseif ($num == 2) {
// 批量插入，为了不超过max_allowed_packet，选择每10万插入一次
for ($i = 0; $i < $sum; $i++) {
if ($i == $sum - 1) { //最后一次
if ($i%100000 == 0){
$values = "($i, 'testtest')";
$db->query("insert into `test` (`id`, `name`) values $values");
} else {
$values .= ",($i, 'testtest')";
$db->query("insert into `test` (`id`, `name`) values $values");
}
break;
}
if ($i%100000 == 0) { //平常只有在这个情况下才插入
if ($i == 0){
$values = "($i, 'testtest')";
} else {
$db->query("insert into `test` (`id`, `name`) values $values");
$values = "($i, 'testtest')";
}
} else {
$values .= ",($i, 'testtest')";
}
}
} elseif ($num == 3) {
// 事务插入
$db->beginTransaction();
for($i = 0; $i < $sum; $i++){
$db->query("insert into `test` (`id`,`name`) values ($i,'tsetssdf')");
}
$db->commit();
} elseif ($num == 4) {
// 文件load data
$filename = dirname(__FILE__).'/test.sql';
$fp = fopen($filename, 'w');
for($i = 0; $i < $sum; $i++){
fputs($fp, "$i,'testtest'\r\n");
}
$db->exec("load data infile '$filename' into table test fields terminated by ','");
}
$end_time = time();
echo "总耗时", ($end_time - $start_time), "秒\n";
echo "峰值内存", round(memory_get_peak_usage()/1000), "KB\n";
?>위는 MySQL에 대용량 데이터를 삽입하기 위한 다양한 방법의 성능 분석 및 비교입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.kr)를 참고해주세요. .php.cn)!