Mysql에서 4가지 삽입 방법의 효율성 비교

이 기사에서는 항목별 삽입, 트랜잭션 기반 일괄 삽입, 한 번에 여러 데이터 세트를 삽입하는 단일 명령문, 가져오기 등 4가지 방법을 포함한 Mysql의 여러 삽입 방법의 효율성 비교를 예제를 통해 소개합니다. 데이터 파일을 비교해서 기사에 예제코드를 통해 자세히 소개되어 있으니 필요한 친구들은 내려와서 보시면 됩니다.

서문

최근 업무상 1천만개 정도의 대용량 데이터를 MySQL에 삽입해야 하게 되었고, 시각적으로도 검사에 더 많은 시간이 소요됩니다. 이제 어떤 방법으로 데이터를 삽입하는 것이 더 빠르고 효율적인지 테스트하는 것과 같습니다.

다음은 각 방법에 대해 서로 다른 데이터 양에서 삽입 효율성을 테스트합니다.

테스트 데이터베이스의 기본 및 작동은 다음과 같습니다.

mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql> create table mytable(id int primary key auto_increment ,value varchar(50));
Query OK, 0 rows affected (0.35 sec)
mysql> desc mytable;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | varchar(50) | YES | | NULL |  |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

테스트를 용이하게 하기 위해 여기에 두 개의 필드가 있는 테이블이 생성됩니다. id이고 다른 하나는 문자열입니다. 내용을 나타냅니다.

테스트할 때마다 mysql> truncate mytable 각 실험 후에 기존 테이블을 지워야 합니다.

방법 1:

을 하나씩 삽입한다. 테스트 코드: (중간에 1000개의 insert 문이 있다. vim으로 복사해서 붙여넣는 게 더 편리해요. 작성 후 a.sql에 저장한 뒤 mysql 프롬프트에 source a.sql을 입력하세요)

set @start=(select current_timestamp(6));
insert into mytable values(null,"value");
......
insert into mytable values(null,"value");
set @end=(select current_timestamp(6));
select @start;
select @end;

출력 결과:

Query OK, 1 row affected (0.03 sec)
......
Query OK, 1 row affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
+----------------------------+
| @start   |
+----------------------------+
| 2016-05-05 23:06:51.267029 |
+----------------------------+
1 row in set (0.00 sec)
+----------------------------+
| @end   |
+----------------------------+
| 2016-05-05 23:07:22.831889 |
+----------------------------+
1 row in set (0.00 sec)

걸렸습니다. 총 31.56486초, 실제로 거의 모든 문 소요 시간은 기본적으로 30ms로 거의 동일합니다.

이런 식으로 1000w의 데이터를 얻으려면 87시간이 걸립니다.

데이터 용량이 큰 경우에는 이 방법을 사용하지 않는 것이 좋습니다.

방법 2: 트랜잭션 기반 일괄 삽입

사실 하나의 트랜잭션에 정말 많은 쿼리가 들어갑니다. 실제로 방법 1의 모든 문은 트랜잭션을 열기 때문에 특히 느립니다.

테스트 코드: (기본적으로 방법 1과 유사하며 주로 두 줄을 추가합니다. 더 빠르기 때문에 여기에서 다양한 데이터 볼륨을 테스트합니다.)

set @start=(select current_timestamp(6));
start transaction;
insert into mytable values(null,"value");
......
insert into mytable values(null,"value");
commit;
set @end=(select current_timestamp(6));
select @start;
select @end;

테스트 결과:

数据量 时间(s)
1k  0.1458
1w  1.0793
10w 5.546006
100w 38.930997

기본적으로 로그 시간이며 효율이 상대적으로 높다는 것을 알 수 있다.

방법 3: 단일 문으로 여러 데이터 세트를 한 번에 삽입합니다.

은 한 번의 삽입으로 여러 값을 한 번에 삽입한다는 의미입니다. 일회.

테스트 코드:

insert into mytable values (null,"value"),
    (null,"value"),
    ......
    (null,"value");

테스트 결과:

数据量 时间(s)
1k  0.15
1w  0.80
10w 2.14
100w *

역시 로그 시간처럼 보이며 방법 2보다 약간 빠릅니다. 그러나 문제는 단일 SQL 문에 대한 버퍼 크기 제한이 있다는 것입니다. 구성을 수정하여 더 크게 만들 수는 있지만 너무 클 수는 없습니다. 따라서 많은 양의 데이터를 삽입할 때에는 사용할 수 없습니다.

방법 4: 데이터 파일 가져오기

수치 데이터를 데이터 파일에 쓰고 직접 가져옵니다(이전 참조 부분).

데이터 파일(a.dat):

null value
null value
.....
null value
null value

테스트 코드:

mysql> load data local infile "a.dat" into table mytable;

테스트 결과:

数据量 时间(s)
1k  0.13
1w  0.75
10w 1.97
100w 6.75
1000w 58.18

시간이 가장 빠른 사람입니다. . . .

위 내용은 Mysql에서 4가지 삽입 방법의 효율성 비교의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!