일상적인 비즈니스 데이터 처리든, 데이터베이스 가져오기 및 내보내기든, 대량의 데이터를 삽입하거나 수정해야 하는 경우가 발생할 수 있습니다. 삽입 또는 수정 방법과 데이터베이스 엔진은 모두 삽입 속도에 영향을 미칩니다. 이 기사에서는 향후 응용 프로그램에서 삽입 방법을 쉽게 선택할 수 있도록 이론적, 실제적 관점에서 다양한 방법을 분석하고 비교하는 것을 목표로 합니다.
삽입 분석
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 = '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";다음은 간단한 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, '王五');
(1, '张三'),
(2, '이사'),
(3, '王五');
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
해결책: 데이터베이스에 데이터를 삽입할 수 없습니다. 해결 방법
위 내용은 대량의 데이터를 삽입하거나 수정하기 위한 여러 MySQL 방법 비교의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
