8천만 개의 레코드가 있는 테이블에 인덱스를 추가하는 데 18시간 이상이 걸립니다.자 이제는 뭐죠?

8천만 개의 레코드가 있는 테이블에 인덱스를 추가하는 데 18시간 이상이 걸립니다.자 이제는 뭐죠?

무슨 일이 일어났는지 간단히 요약합니다.저는 7,100만 개의 레코드(다른 사람들이 처리하는 수십억 개의 레코드에 비해 많지 않음)를 가지고 작업하고 있습니다.다른 스레드에서 현재 클러스터 설정이 내 요구에 적합하지 않다고 제안했습니다.제 테이블 구조는 다음과 같습니다.

CREATE TABLE `IPAddresses` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `ipaddress` bigint(20) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

그리고 7천1백만 장의 기록을 추가해서 다음과 같이 했습니다.

ALTER TABLE IPAddresses ADD INDEX(ipaddress);

14시간이 지났는데도 아직도 수술이 끝나지 않았습니다.구글 검색을 해보니 이 문제를 해결하기 위한 잘 알려진 방법인 파티셔닝이 있습니다.ip address를 기준으로 지금 테이블을 분할해야 하는 것으로 알고 있는데 테이블 전체를 다시 만들지 않고 할 수 있나요?제 말은, ALTER 진술서를 통해서요?예인 경우 분할할 열이 기본 키가 되어야 한다는 요구 사항이 하나 있습니다.저는 ipaddress가 제 기본 키가 아니기 때문에 다른 테이블을 구성할 때 이 ipaddress의 id를 사용할 것입니다.이 시나리오에서 테이블을 어떻게 분할합니까?

이 문제는 단순히 테이블을 만들고 색인을 작성한 후 문제를 잊어버리는 것 이상의 것이었음이 밝혀졌습니다. :) 다른 사람이 동일한 문제에 직면할 경우를 대비하여 수행한 작업은 다음과 같습니다(IP Address의 예를 사용했지만 다른 데이터 유형에도 적용됨).

문제:테이블에는 수백만 개의 항목이 있으며 인덱스를 매우 빠르게 추가해야 합니다.

사용 사례:수백만 개의 IP 주소를 룩업 테이블에 저장하는 것을 고려해 보십시오.IP 주소를 추가하는 것은 큰 문제가 되지 않지만 IP 주소에 대한 인덱스를 만드는 데는 14시간 이상이 걸립니다.

솔루션: MySQL의 Partitioning 전략을 사용하여 테이블 파티셔닝

Case #1 : 원하는 테이블이 아직 생성되지 않은 경우

CREATE TABLE IPADDRESSES(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id, ipaddress)
) ENGINE=MYISAM
PARTITION BY HASH(ipaddress)
PARTITIONS 20;

Case #2: 원하는 테이블이 이미 생성된 경우ALTER TABLE을 사용하여 이를 해결할 수 있는 방법이 있는 것 같은데 아직 적절한 해결책을 찾지 못했습니다.대신 다음과 같은 다소 비효율적인 솔루션이 있습니다.

CREATE TABLE IPADDRESSES_TEMP(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id)
) ENGINE=MYISAM;

이 테이블에 IP 주소를 삽입합니다.그런 다음 파티션이 있는 실제 테이블을 만듭니다.

CREATE TABLE IPADDRESSES(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id, ipaddress)
) ENGINE=MYISAM
PARTITION BY HASH(ipaddress)
PARTITIONS 20;

그리고 마침내

INSERT INTO IPADDRESSES(ipaddress) SELECT ipaddress FROM IPADDRESSES_TEMP;
DROP TABLE IPADDRESSES_TEMP;
ALTER TABLE IPADDRESSES ADD INDEX(ipaddress)

그리고 여기 있네요...새 테이블의 색인은 3.2에서 약 2시간이 걸렸습니다.1GB 램이 있는 GHz 기계 :) 도움이 되길 바랍니다.

MySQL로 인덱스를 만드는 것은 느리지만 그렇게 느리지는 않습니다.7천 1백만 장의 기록을 가지고 있다면, 14시간이 아니라 2분이 걸릴 것입니다.발생 가능한 문제는 다음과 같습니다.

• 정렬 버퍼 크기 및 기타 구성 옵션을 구성하지 않았습니다.

여기를 보세요: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_sort_buffer_size

8MB 정렬 버퍼로 1GB 인덱스를 생성하려고 하면 많은 패스가 필요합니다.그러나 버퍼가 CPU 캐시보다 크면 속도가 느려집니다.따라서 무엇이 가장 효과적인지 시험해 보고 확인해야 합니다.

• 누군가 테이블에 자물쇠가 달려있습니다.
• 당신의 IO 시스템은 형편없습니다.
• 서버가 스왑 중입니다.
• 기타

평소와 같이 iostat, vmstat, 로그 등을 확인합니다.테이블에 잠금 테이블을 발행하여 누군가가 잠금 테이블을 가지고 있는지 확인합니다.

FYI는 64비트 데스크톱에서 1,000만개의 랜덤 BIGINT에 대한 인덱스를 생성하는 데 17초가 소요됩니다.

인덱스를 추가하여 쿼리 속도를 높이고자 하는 문제가 있었습니다.그 테이블은 약 300,000개의 기록만을 가지고 있었지만 그것은 또한 너무 오래 걸렸습니다.mysql 서버 프로세스를 확인해보니 최적화하려던 쿼리가 백그라운드에서 계속 실행되고 있었습니다.4번!제가 그 질문들을 없앤 후에 색인 작업이 순식간에 이루어졌습니다.아마 당신의 상황에도 같은 문제가 적용될 것입니다.

당신은 곧 감가 상각되는 My ISAM을 사용하고 있습니다.대안으로는 InnoDB가 있습니다.

"InnoDB는 사용자 데이터를 보호하기 위한 커밋, 롤백 및 크래시 복구 기능을 갖춘 MySQL용 트랜잭션 안전(ACID 호환) 스토리지 엔진입니다.InnoDB 행 수준의 잠금(엄격한 세분화 잠금으로 에스컬레이션되지 않음) 및 Oracle 스타일의 일관된 비잠금 읽기는 다중 사용자 동시성과 성능을 향상시킵니다.InnoDB는 기본 키를 기반으로 일반 쿼리에 대한 I/O를 줄이기 위해 클러스터 인덱스에 사용자 데이터를 저장합니다.InnoDB는 데이터 무결성을 유지하기 위해 FOREN KEY 참조 무결성 제약 조건도 지원합니다.InnoDB 테이블과 다른 MySQL 스토리지 엔진의 테이블을 동일한 문 내에서도 자유롭게 혼합할 수 있습니다."\

http://dev.mysql.com/doc/refman/5.0/en/innodb.html

기준:

http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html

, 유연성을 확보할 수 있는 간단한 alter 명령을 사용하여 다른 엔진 간에 전환할 수 있어야 합니다.또한 DB의 각 테이블을 독립적으로 구성할 수 있음을 명시합니다.

당신의 테이블에서. 당신은 이미 710억개의 기록을 삽입했습니다.이제 테이블의 기본 키 열에 파티션을 작성하려면 artertable 옵션을 사용할 수 있습니다.참고로 예를 들어 보겠습니다.

CREATE TABLE t1 (
    id INT,
    year_col INT
);

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;

언급URL : https://stackoverflow.com/questions/3695768/table-with-80-million-records-and-adding-an-index-takes-more-than-18-hours-or-f