MySQL(MariaDB)에서 큰 테이블에 인덱스를 만드는 데 시간이 매우 오래 걸립니다.

MySQL(MariaDB)에서 큰 테이블에 인덱스를 만드는 데 시간이 매우 오래 걸립니다.

저는 몇 십억 행의 데이터가 있는 테이블을 가지고 있고 그 테이블 위에 5개의 인덱스를 한 번에 구축하려고 합니다.테이블 형식은 공간 절약을 위한 My ISAM입니다.인덱스를 작성하면 정적 테이블이 됩니다. 읽기만 하면 됩니다.

다음 명령을 사용하여 인덱스를 만들었습니다.

변경 가능한 링크 8 인덱스 추가(uid, tid), 인덱스 추가(날짜), 인덱스 추가(tid), 인덱스 추가(userid), 인덱스 추가(userid, userid, date);

명령은 45일 이상 실행되고 있습니다.당신이 제대로 읽었네요 45일.임시 파일들이 아직도 접근되고 있는 것을 알 수 있습니다. 죽은 쿼리가 아닙니다.

내 질문은: wtf?몇 십억 행이라도 인덱스를 정렬하고 구축하는 데는 많아야 몇 시간이 걸릴 것으로 보입니다.

정적 테이블이 있는데, 사용하기에 적합한 다른 저장 엔진이 있습니까?Innodb는 공간을 너무 많이 차지합니다.

45일은 맞지 않는 것 같습니다. 왜냐하면 그 시간 안에 MySQL은 무언가를 할 수밖에 없고, RAM이나 스토리지를 소비할 가능성이 높기 때문입니다. 즉, 어느 시점에서 RAM이나 스토리지가 부족해졌어야 한다는 것입니다.RAM이라고 생각합니다. 일반적으로 RAM은 희소성이 있기 때문입니다.)

자, 당신 말이 전적으로 옳습니다. 기억 속의 몇 십억 개의 가치를 분류하는 데는 시간이 걸리지 않아야 합니다.인몇의값렬억값nscgdta(updated,uid,tid,userid,date)RAM에서는 발생하지 않을 가능성이 높습니다.에를 가정하여updated그리고.datedatetime를 사용합니다; , 8 됩니다 uid,tid,userid일반적으로 32비트 int이지만 테이블에 2**32개 이상의 항목이 있기 때문에 고유 ID의 길이도 8바이트가 됩니다. 유형의의값값efe의의oe서ef값(updated,uid,tid,userid,date)길이가 40B일 것입니다.

이제 이 중 50억 개를 입력해 보겠습니다. 인덱스를 생성하기 위해 정렬해야 하는 200GB의 순수 행 데이터를 얻을 수 있습니다.어떤 거대한 기계에서 이런 작업을 하는 것이 아니라고 가정할 때, 이러한 값의 일부를 디스크로 바꿀 필요가 있습니다. 임시 파일이 나타나는 것을 볼 수 있기 때문에, 제 추측으로는 이런 일이 일어나고 있고, MySQL도 적극적으로 그 자체를 하고 있습니다.행의 일부에서 반복적으로 작동하는 정렬 알고리즘은 훨씬 느립니다. 먼저 모든 부분을 정렬한 다음 이전보다 더 나은 방식으로 부품을 혼합하기 때문입니다. 데이터를 다시 파티션화하는 것보다 부품을 정렬하고 디스크에서 저장하고 로드하는 것을 사이에 두고 말이죠.

참고로, 45일간 지속되는 메모리 작업은 수정 조치가 취해지지 않으면 메모리 비트 오류가 발생하기 쉽습니다(기본적으로 이러한 작업에 ECC를 사용하거나 색인화되고 손상된 데이터를 사용하게 됩니다).

128비트(==16바이트) MD5 해시 정렬이 5*8Byte == 40*8비트 == 320비트 길이의 복합 행 정렬보다 쉬울 수 있으므로 MySQL 자체에서 검색 튜플의 해시를 가져와 찾는 특별한 MD5 인덱스를 구축하는 것이 좋습니다.

더 나은 해결책을 찾았습니다.

인덱스가 이미 있는 새 테이블을 만든 다음 한 테이블에서 다른 테이블로 삽입을 실행했습니다.이 방법은 MYD(raw data file)를 채우고 그 후 인덱스를 생성하는 것입니다.일단 인덱스를 만들기 시작하면 쿼리를 삭제했습니다.그런 다음 파일 시스템에서 수동으로 테이블을 수리하기 위해 isamchk를 사용했습니다.

그 명령은 다음과 같습니다.

myisamchk --force --fast --update-state --key_buffer_size=2000M --sort_buffer_size=2000M --sort_buffer_size=2000M --read_buffer_size=10M --write_buffer_size1010M TABLE.MYI

그리고 이 모든 것이 12시간도 걸리지 않았고 데이터도 좋아 보입니다!

업데이트:

여기에 요약된 흐름이 있습니다.

인덱스를 사용하여 표 1과 동일한 표 2를 만듭니다.

표 2에 삽입합니다. 표 1에서 *를 선택합니다.

MYD 파일이 가득 차면 MYI 파일에서 시작하면 쿼리가 중지됩니다.

그런 다음 mysql을 종료하고 myisamchk 쿼리를 실행하고 mysql을 다시 시작합니다.

오어

표 2를 복사합니다.MYD와 표 2.MYI부터 3번 테이블.MYD와 표 3.MYI, 그런 다음 myisamchk를 실행한 다음 table2.frm을 table3.frm으로 복사하고 권한을 변경하면 mysql을 다시 시작하지 않고 table3에 액세스할 수 있습니다.

언급URL : https://stackoverflow.com/questions/28029218/creating-indexes-on-large-tables-in-mysql-mariadb-takes-a-verrry-looong-time