>source

다음과 같은 테이블이 하나 있습니다.

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| name        | varchar(255) | NO   | PRI | NULL    |       |
| timestamp1  | int          | NO   |     | NULL    |       |
| timestamp2  | int          | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

이 테이블에는 약 2억 5천만 개의 행이 있습니다. 나는 하나의 이름 열에 대한 약 2억 2,500만 행을 포함하는 csv를 하루에 한 번 얻습니다. 내가 매일 얻는 csv에 있는 이름의 99%는 이미 데이터베이스에 있습니다. 그래서 내가 하고 싶은 것은 이미 거기에 있는 모든 사람들을 위해 업데이트하는 것입니다.타임스탬프1UNIX_TIMESTAMP(지금()). 그런 다음 원본 테이블에 없지만 csv에 있는 모든 이름을 원본 테이블에 추가합니다. 지금은 이렇게 하고 있습니다.

DROP TEMPORARY TABLE IF EXISTS tmp_import;
CREATE TEMPORARY TABLE tmp_import (name VARCHAR(255), primary_key(name));
LOAD DATA LOCAL INFILE 'path.csv' INTO TABLE tmp_import LINES TERMINATED BY '\n';
UPDATE og_table tb SET timestamp1= UNIX_TIMESTAMP(NOW()) WHERE og.name IN (SELECT tmp.name FROM tmp_import tmp);
DELETE FROM tmp_import WHERE name in (SELECT og.name FROM og_table og);
INSERT INTO og_table SELECT name, UNIX_TIMESTAMP(NOW()) AS timestamp1, UNIX_TIMESTAMP(NOW()) AS timestamp2 FROM tmp_import;

누군가는 업데이트 라인이 6시간 이상 오래 걸리거나 오류가 발생한다고 추측할 수 있습니다. 데이터를 읽는 데 40분 이상이 걸립니다. 나는 이것이 주로 인덱스를 생성하기 때문이라는 것을 알고 있습니다.이름기본 키로 설정하지 않으면 데이터를 읽는 데 9분밖에 걸리지 않지만 인덱스가 있으면 작업 속도가 빨라질 것이라고 생각했습니다. 여러 가지 방법으로 업데이트를 시도했습니다. 내가 가진 것과 다음 사항:

UPDATE og_table og SET timestamp1= UNIX_TIMESTAMP(NOW()) WHERE EXISTS (SELECT tmp.name FROM tmp_import tmp where tmp.name= og.name);
UPDATE og_table og inner join tmp_import tmp on og.name=tmp.name SET og.timestamp1= UNIX_TIMESTAMP(NOW());

두 가지 시도 모두 실패했습니다. 일반적으로 몇 시간이 걸리고 다음과 같이 끝납니다.

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

이 테이블에 innodb를 사용하고 있지만 필요한 외래 키가 없으며 해당 엔진의 이점이 반드시 필요한 것은 아니므로 다른 스토리지 엔진을 사용해 볼 수 있습니다.

나는 많은 게시물을 살펴보았지만 아직 내 상황에 도움이 되는 것을 찾지 못했습니다. 글을 놓쳤다면 사과드립니다.

JOIN이 일반적으로 가장 좋은 방법입니다.

Barmar2021-10-29 09:24:13

2억 2,500만 행을 업데이트하는 데는 시간이 걸리며 피할 수 없습니다.

Barmar2021-10-29 09:24:13

데이터를 로드한 후 인덱스를 추가하면 데이터를 더 빠르게 가져올 수 있습니다.

Barmar2021-10-29 09:24:13

csv 파일 소스가 궁금합니다. 데이터베이스에서도 내보내나요? 그렇다면 'csv로 내보내기' 프로세스를 대신 개선하는 것이 좋지 않을까요? 내 말은 왜 매일 데이터 중복성의 99%를 내보내야 합니까?

FanoFN2021-10-29 09:24:13

얼마나 많은 RAM이 있습니까? innodb_buffer_pool_size의 값은 무엇입니까?

Rick James2021-10-28 19:21:39
  • 이전 Typescript+webpack: typescript가 index.d.ts에 대한 출력을 내보내지 않았습니다.
  • 다음 Vim 키맵에 "\"가 포함되어 있으면 Lua에서 오류가 발생합니다.