MyISAM 레코드가 3억 5천만 개 이상인 25GB의 대용량 테이블을 업데이트하려면 어떻게 해야 합니까?

MyISAM 레코드가 3억 5천만 개 이상인 25GB의 대용량 테이블을 업데이트하려면 어떻게 해야 합니까?

MyISAM 레코드가 3억 5천만 개 이상인 25GB의 대용량 테이블을 업데이트하려면 어떻게 해야 합니까?다음에서 임의로 날짜를 설정해야 합니다.time모든 레코드에 사용할 수 있습니다.로드가 없는 서버에서는 다음 명령어가 실행되었습니다.

UPDATE table SET time = FROM_UNIXTIME(1451595600 + FLOOR((RAND() * 31536000)))

mysqld는 프로세서를 로드하고 RAM을 많이 사용하였습니다.아침에 서버 로드는 최소이지만 쿼리는 모두 실행되어 55시간 이상 경과하였습니다.

무슨 일이 일어나고 있는지 이해할 수 없어요!

CREATE TABLE `table` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `job_id` int(10) unsigned NOT NULL,
    `lock` mediumint(6) unsigned DEFAULT '0',
    `time` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `job_id` (`job_id`),
    KEY `lock` (`lock`),
    KEY `time` (`time`)
) ENGINE=MyISAM;

추가:

root@lw4:~ # iostat -p md1 60 5

해결:

업데이트 프로세스를 중단했습니다.

기본 인덱스를 제외한 인덱스 제거:

MariaDB [base]> ALTER TABLE `table` DROP INDEX `job_id`, DROP INDEX `lock`, DROP INDEX `time`;
Query OK, 339468609 rows affected (1 hour 3 min 28.89 sec)
Records: 339468609  Duplicates: 0  Warnings: 0

'time' 필드 업데이트:

MariaDB [base]> UPDATE `table` SET `time` = FROM_UNIXTIME(1451595600 + FLOOR((RAND() * 31539599)));
Query OK, 339468609 rows affected (16 min 8.09 sec)
Rows matched: 339468609  Changed: 339468609  Warnings: 0

인덱스 추가:

MariaDB [base]> ALTER TABLE `table` ADD INDEX (`job_id`), ADD INDEX (`lock`), ADD INDEX (`time`);
Query OK, 339468609 rows affected (2 hours 18 min 58.32 sec)
Records: 339468609  Duplicates: 0  Warnings: 0

총 갱신 시간: 3시간 38분 35.3초

먼저 해당 테이블의 인덱스를 모두 삭제합니다.또, 외부 키 제약이 있는 경우는 드롭 합니다.그런 다음 업데이트 쿼리를 실행합니다.업데이트가 완료되면 모든 인덱스와 제약조건을 다시 만듭니다.

모든 인덱스 및 제약 조건을 만드는 데 시간이 걸리지만 테이블을 직접 업데이트하는 것보다 훨씬 적게 걸립니다.

이게 무슨 일인지...

각 레코드는 처음에 13바이트였습니다.행을 업데이트하면 17바이트(MySQL의 이전 버전) 또는 18바이트(새로운 버전)가 됩니다.

즉, 갱신된 행은 단순히 오래된 행을 대체할 수 없으며 다른 장소에 배치한 후 오래된 공간을 비워야 합니다.첫 번째 업데이트는 테이블 끝으로 이동합니다.또는 이전 공간의 13~6바이트를 사용한 후 새 행의 나머지 부분에 링크를 배치할 수도 있습니다.

두 번째 행은 마찬가지로 지저분한 작업을 수행합니다.

따라서 이 작업은 테이블 내에서 수행되며 데이터가 상당히 단편화됩니다.

또, 다음의 인덱스는time(key_module을 사용하여) 재구축 중입니다."랜덤"이기 때문에 지수 BTree에 대한 업데이트는 랜덤입니다.key_buffer가 충분히 크지 않으면 그에 대한 I/O가 많이 발생합니다.이 특별한 측면은 다음과 같이 고속화할 수 있습니다.DROPping사전에 인덱스를 작성하고 나중에 다시 추가합니다.아아, 둘 다DROP그리고.ADD느리겠지만 지수의 증분 구축만큼 느리지는 않을 것이다.

InnoDB로 이행해야 합니다.InnoDB는 데이터 갱신과 인덱스 갱신을 상당히 다르게 하고 변경도 효율적으로 합니다.주의사항 하나:테이블+인덱스는 MyISAM보다 2~3배 많은 디스크 공간을 사용합니다.

4가지 인덱스가 모두 필요합니까?

、 우 、key_buffer_size경우 InnoDB의 경우innodb_buffer_pool_size.

언급URL : https://stackoverflow.com/questions/43527285/how-to-update-a-large-table-of-25-gb-more-than-350-million-records-of-myisam