MySQL5.6 MyISAM에서 InnoDB의 테이블 변환

14.6.4 MyISAM에서 InnoDB의 테이블 변환

신뢰성 및 확장 성을 개선하기 위해 기존의 테이블을 사용하는 응용 프로그램을 InnoDB 로 변환하려면 다음 지침과 팁을 사용합니다. 이 섹션에서는 이러한 테이블 대부분이 처음에는 이전의 기본 MyISAM 였던 것이 전제가되어 있습니다.

MyISAM의 메모리 사용량의 감소, InnoDB의 메모리 사용량 증가

MyISAM 테이블에서 마이그레이션 할 때 결과를 캐시하는 데 필요하지 않게 된 메모리가 해제되도록 key_buffer_size 구성 옵션 값을 줄입니다. InnoDB 테이블의 캐시 메모리 할당과 같은 역할을 innodb_buffer_pool_size 구성 옵션의 값을 늘립니다. InnoDB 의 버퍼 풀 에는 테이블 데이터와 인덱스 데이터가 모두 캐시되므로 쿼리의 검색 속도를 높일 수 및 재사용하기 위해 쿼리 결과를 메모리에 유지하는 두 가지 역할이 있습니다.

이 옵션은 가능한 한 많은 메모리 (많은 경우 최대 서버의 실제 메모리의 80 %까지)을 할당합니다.
운영 체제에서 다른 프로세스의 메모리가 부족하고 스왑이 발생하기 시작하면 innodb_buffer_pool_size 값을 줄입니다. 스왑은 캐시 메모리의 이점이 크게 줄어들 같은 값 비싼 작업입니다.
innodb_buffer_pool_size 값이 몇 기가 바이트 이상인 경우는 innodb_buffer_pool_instances 의 값을 크게하는 것을 고려하십시오. 이렇게하면 동시에 여러 연결이 캐시에 데이터를로드 고부하의 서버에 도움이됩니다.
고부하의 서버에서는 쿼리 캐시를 해제하고 벤치 마크를 실행합니다. InnoDB 의 버퍼 풀에서도 같은 이점을 얻을 수 있기 때문에 쿼리 캐시를 사용하면 불필요하게 메모리가 중지 될 수 있습니다.

너무 오래 또는 너무 짧은 트랜잭션 모니터링

MyISAM 테이블은 트랜잭션 을 지원하지 않기 때문에, autocommit 구성 옵션과 COMMIT 및 ROLLBACK 문에 많은 관심을 기울이고 있지 않을 가능성이 있습니다. 이러한 키워드는 여러 세션이 병렬로 InnoDB 테이블의 읽기 및 쓰기를 할 수 있도록하는 데 중요합니다. 그러면 쓰기 집약적 워크로드에 충분한 확장 성 이점이 있습니다.

트랜잭션이 열려있는 동안은 트랜잭션의 시작시에 볼 수있는 데이터의 스냅 샷을 시스템에서 유지됩니다. 이는 트랜젝션이 제대로 작동하지 않는 동안 시스템에서 수백만 개의 행 삽입, 업데이트 및 삭제가 이루어지면 상당한 오버 헤드가 발생할 수 있습니다. 따라서 동작 시간이 너무 오래 트랜잭션은 발생하지 않도록주의하십시오.

인터랙티브 실험에서 mysql 세션을 사용하는 경우, 완료 후 반드시 (변경을 완료시키는 경우) COMMIT 또는 (변경을 취소하려면) ROLLBACK 을 실행합니다. 잘못 트랜잭션이 오랫동안 개방되는 것을 방지하려면 대화 형 세션을 오랫동안 개방하지 않고 닫습니다.
응용 프로그램의 모든 에러 핸들러에서도 불완전한 변경 ROLLBACK 이 실행되거나 완료된 변경 COMMIT 이 실행되는 것을 확인합니다.
INSERT , UPDATE 및 DELETE 작업은 대부분의 변경은 성공적으로 커밋 된 롤백은 거의 발생하지 않는다는 전망으로, COMMIT 보다 전에 InnoDB 테이블에 기록되므로 ROLLBACK 은 비교적 부하가 높은 작업 입니다. 대량의 데이터를 사용하여 실험 할 때는 많은 행을 변경 후 그 변경 사항을 롤백하는 것은 피하십시오.
일련의 INSERT 문을 사용하여 대량의 데이터를로드 할 때 트랜잭션이 몇 시간 살아나는 것을 방지하기 위해 정기적으로 결과 COMMIT 을 실행합니다. 데이터웨어 하우스에서 일반로드 작업은 문제가 발생했을 경우에 사용자는 ROLLBACK 을 할 것이 아니라, TRUNCATE TABLE 을 실행하고 처음부터 다시 시작합니다.

위의 팁을 사용하면 너무 긴 트랜잭션 중에 낭비 될 수있는 메모리 및 디스크 공간을 절약 할 수 있습니다. 트랜잭션이 본래보다 짧은 경우 과도한 I / O가 문제가됩니다. MySQL에서는 COMMIT 가 수행 될 때마다 각 변경이 안전하게 디스크에 기록되어있는 것이 확인됩니다. 여기에는 약간의 I / O가 포함됩니다.

InnoDB 테이블에서 대부분의 작업은 autocommit=0 설정을 사용하도록하십시오. 효율성의 관점에서 보면, 이는 다수의 연속적인 INSERT , UPDATE 또는 DELETE 문을 실행할 때 불필요한 I / O가 해결됩니다. 안전성의 관점에서 보면 이는 mysql 명령 행 또는 응용 프로그램의 예외 핸들러에 오류가있는 경우 ROLLBACK 문을 발행하여 손실 된 데이터 나 문자 손상된 데이터를 복구 할 수 있습니다.
InnoDB 테이블에 autocommit=1 을 설정하는 것이 적합 상황 보고서를 생성하거나 통계 분석을 수행하는 일련의 쿼리를 실행할 때입니다. 이러한 상황에서는 COMMIT 또는 ROLLBACK 관련 I / O 페널티가 발생하지 않고, InnoDB 는 자동으로 읽기 전용 워크로드를 최적화 할 수 있습니다 .
관련된 일련의 변경을 할 경우는 마지막에 1 회 COMMIT 을 실행하여 이러한 모든 변경 사항을 한 번에 완료됩니다. 예를 들어, 정보의 관련 부분을 여러 테이블에 삽입하는 경우, 모든 변경을 행한 뒤에, COMMIT 을 1 회 실행합니다. 또한 연속 다수의 INSERT 문을 실행하는 경우 모든 데이터가로드 된 후, COMMIT 을 1 회 실행합니다. 수백만 INSERT 문을 실행하려면 일만 또는 천 레코드마다 COMMIT 를 발행하여 거대한 트랜잭션을 나눌 수 있습니다.
SELECT 문에서 트랜잭션이 열리기 때문에, 쌍방향 mysql 세션에서 일부 보고서를 실행하거나 쿼리를 디버깅하거나 한 후에는 COMMIT 를 발행하거나 mysql 세션을 닫습니다.

교착 상태를 걱정 불과 수

MySQL의 오류 로그 또는 SHOW ENGINE INNODB STATUS 출력에 "교착 상태"에 언급하는 경고 메시지가 나타날 수 있습니다. 교착 상태 는 무서운 들리는 이름에도 불구하고, InnoDB 테이블에게는 중요한 문제가 아니라 시정 조치는 아무것도 필요하지 않습니다. 2 개의 트랜잭션이 여러 테이블을 변경하고 테이블에 별도의 순서로 액세스하기 시작하면 각 트랜잭션이 서로 대기 있고, 모두 처리 할 수없는 상태에 도달 할 수 있습니다. 곧 MySQL에 의해이 상황이 감지되고 "더 작은"트랜잭션이 취소 ( 롤백 됩니다) 나머지는 처리 할 수 있습니다.

응용 프로그램이 같이 강제로 취소 된 트랜잭션을 재개하기 위해 오류 처리 로직이 필요합니다. 이전과 같은 SQL 문을 다시 발행하면 원래의 타이밍 문제에는 적용되지 않습니다. 다른 트랜잭션이 이미 완료 되었기 때문에 한쪽을 처리 할 수 있는지, 다른 트랜잭션이 아직 처리 중이며, 이것이 완료 될 때까지 일방이 대기하고있는 것 중 하나입니다.

교착 상태의 경고가 항상 발생하는 경우 응용 프로그램 코드를 다시 확인하여 일관된 방식으로 SQL 작업을 다시 지시하거나 트랜잭션을 단축 할 수 있습니다. innodb_print_all_deadlocks 옵션을 사용하여 테스트하면 SHOW ENGINE INNODB STATUS 출력의 마지막 경고뿐만 아니라 MySQL의 오류 로그에 모든 교착 상태 경고를 볼 수 있습니다.

스토리지 레이아웃 계획

InnoDB 테이블에서 최상의 성능을 얻기 위해서는 스토리지 레이아웃에 대한 다양한 매개 변수를 조정할 수 있습니다.

자주 사용되는 중요한 데이터가 유지되는 큰 MyISAM 테이블을 변환 할 때 innodb_file_per_table , innodb_file_format , innodb_page_size 구성 옵션 및 CREATE TABLE 문 ROW_FORMAT 과 KEY_BLOCK_SIZE 절 을 조사 및 검토하십시오.

초기 실험시에 가장 중요하다 설정은 innodb_file_per_table 입니다. 새로운 InnoDB 테이블을 만들기 전에이 옵션을 활성화하면 InnoDB 의 시스템 테이블 스페이스 파일을 사용하여 모든 InnoDB 데이터의 디스크 공간이 영구적으로 할당되지 않습니다. innodb_file_per_table 를 활성화하면 DROP TABLE 및 TRUNCATE TABLE 을 발행하여 요구대로 디스크 공간이 해제됩니다.

기존 테이블 변환

InnoDB 를 사용하도록 InnoDB 이외의 테이블을 변환하려면 ALTER TABLE 을 사용합니다.

 ALTER TABLE table_name ENGINE = InnoDB;

중요

mysql 데이터베이스의 MySQL 시스템 테이블 ( user 와 host 등)을 InnoDB 형식으로 변환하지 마십시오. 이것은 지원되지 않는 작업입니다. 시스템 테이블의 형태는 반드시 MyISAM 할 필요가 있습니다.

테이블 구조 복제

오래된 테이블과 새로운 테이블을 전환하기 전에 병렬로 테스트 할 때 ALTER TABLE 변환을 할 것이 아니라, MyISAM 테이블의 복제품이다 InnoDB 테이블을 만들 수 있습니다.

같은 컬럼과 인덱스의 정의를 가진 하늘의 InnoDB 테이블을 만듭니다. show create table table_name \G 를 사용하여 사용되는 완전한 CREATE TABLE 문을 표시합니다. ENGINE 절을 ENGINE=INNODB 로 변경합니다.

기존 데이터의 전송

이전 섹션에서 나타난 바와 같이 생성 된 빈 InnoDB 테이블에 대량의 데이터를 전송하려면 INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns 를 사용하여 행을 삽입합니다.

데이터를 삽입 한 후, InnoDB 테이블에 대한 인덱스를 만들 수 있습니다. 기존 새로운 보조 인덱스를 작성하는 것은 InnoDB에게 느린 동작 이었지만 현재는 인덱싱 단계에서 비교적 작은 오버 헤드로 데이터가로드 된 후에 인덱스를 생성 할 수있게되었습니다 .

부 키에 UNIQUE 제약이있는 경우 가져 오기 작업 중에 고유성 검사를 일시적으로 해제하여 테이블 임포트 속도를 높일 수 있습니다.

 SET unique_checks = 0;
... import operation ...
 SET unique_checks = 1;

큰 테이블의 경우, InnoDB 는 삽입 버퍼 를 사용하여 일괄 적으로 보조 인덱스 레코드를 기록 할 수 있기 때문에이를 통해 대량의 디스크 I / O가 절약됩니다. 데이터 중복 키가 포함되지 않도록합니다. unique_checks 는 스토리지 엔진이 중복 키를 무시하는 것이 허용되어 있지만 필수 사항은 아닙니다.

삽입 과정을보다 효율적으로 제어하기 위해 큰 테이블을 분할하여 삽입 할 수 있습니다.

 INSERT INTO newtable SELECT * FROM oldtable
    WHERE yourkey> something AND yourkey <= somethingelse ;

모든 레코드가 삽입 된 후에 테이블의 이름을 변경할 수 있습니다.

디스크 I / O를 줄이기 위해 큰 테이블 변환시 최대 물리적 메모리의 80 %까지 InnoDB 버퍼 풀의 크기를 크게합니다. InnoDB 로그 파일의 크기를 크게 할 수 있습니다.

스토리지 요구 사항

이미 설명했듯이,이 시점에서 이미 innodb_file_per_table 옵션을 활성화해야합니다. 이는 InnoDB 테이블에 데이터의 여러 사본을 일시적으로 작성하는 경우는 나중에 불필요한 테이블을 삭제하여 디스크 공간을 복구 할 수 있습니다.

MyISAM 테이블을 직접 변환할지, 클론의 InnoDB 테이블을 생성하는지에 관계없이 프로세스 중에 오래된 테이블과 새로운 테이블을 모두 유지하는 데 충분한 디스크 공간이 있는지 확인합니다. InnoDB 테이블은 MyISAM 테이블보다 많은 디스크 공간이 필요합니다. ALTER TABLE 조작에 의해 영역이 모두 소진되면 롤백이 시작되지만 디스크 바운드의 경우는 몇 시간이 걸릴 수 있습니다. 삽입의 경우, InnoDB 는 배치 내의 인덱스 보조 인덱스 레코드를 병합 할 때 삽입 버퍼를 사용합니다. 이렇게하면 대량의 디스크 I / O가 절약됩니다. 롤백에서는 이러한 메커니즘은 사용되지 않습니다. 롤백은 삽입보다 30 배 긴 시간이 걸릴 수 있습니다.

런 어웨이 롤백의 경우 데이터베이스에 귀중한 데이터가 없으면, 수백만의 디스크 I / O 작업이 완료 될 때까지 대기하는 것이 아니라 데이터베이스 프로세스를 강제 종료하는 것이 좋습니다. 전체 지침은 섹션 14.19.2 "InnoDB 복구 강제 실행" 을 참조하십시오.

테이블 당 기본 키의 신중한 선택

PRIMARY KEY 절은 MySQL 쿼리의 성능이나 테이블 및 인덱스를위한 공간 사용량에 영향을 미치는 중요한 요소입니다. 아마도 금융 기관에 전화를 걸어 계좌 번호를 요구했던 경험이있을 것입니다. 그 번호를 가지고 있지 않은 경우는 자신을 "고유하게 식별하는"위해 다양한 정보가 요구됩니다. 기본 키는 테이블의 정보를 조회하거나 수정할 때 바로 본론위한 고유 계좌 번호 같은 것입니다. 테이블의 모든 행이 기본 키 값을 가질 필요가 있으며, 2 개의 행이 동일한 기본 키 값을 가질 수 없습니다.

다음 기본 키에 대한 몇 가지 지침을 따르고 더 자세한 설명을 보여줍니다.

테이블마다 PRIMARY KEY 를 선언합니다. 일반적으로 단일 행을 검색 할 때 참조하는 WHERE 절의 컬럼에서 가장 중요한 것입니다.
나중에 ALTER TABLE 문을 사용하여 추가하는 것이 아니라 원래 CREATE TABLE 문에서 PRIMARY KEY 절을 선언합니다.
컬럼 데이터 유형은 신중하게 선택하십시오. 문자 또는 문자열 컬럼보다 숫자 컬럼을 우선합니다.
다른 안정되어 있고, 고유 비 NULL로 숫자 열을 사용할 수없는 경우 자동 증가 컬럼을 사용하는 것을 고려하십시오.
프라이 머리 키 컬럼의 값이 변경되었는지 여부가 의심스러운 경우에도 자동 증가는 적절한 선택입니다. 프라이 머리 키 컬럼의 값을 변경하는 것은 비용이 많이 드는 작업이며, 테이블 및 각 보조 인덱스에서 데이터 재구성이 수반 될 수 있습니다.

기본 키 가 아직 존재하지 않는 테이블에 추가하는 것을 고려하십시오. 계획 된 테이블의 최대 크기에 따라 현실적인 최소 숫자 형식을 사용합니다. 이렇게하면 각 행을 약간 압축 할 수 있으며, 큰 테이블에 상당한 공간을 절약 할 수 있습니다. 기본 키 값은 보조 인덱스가 입력 될 때마다 반복되기 때문에 테이블이 모든 보조 인덱스 를 가지고 있다면, 공간 절약도 두배합니다. 작은 기본 키를 사용하면 디스크의 데이터 크기가 감소 될뿐만 아니라, 더 많은 데이터를 버퍼 풀 에 수용 할 수 있기 때문에 모든 종류의 작업 속도를 향상 동시성이 향상됩니다 .

이미 테이블의 다소 긴 컬럼 ( VARCHAR 등)에 기본 키가 있으면 그 열이 쿼리에서 참조되지 않아도 새로운 부호없는 AUTO_INCREMENT 컬럼을 추가하고 기본 키 컬럼로 전환하는 것을 고려하십시오. 이러한 설계 변경으로 보조 인덱스의 상당한 공간을 절약 할 수 있습니다. 이전의 프라이 머리 키 컬럼을 UNIQUE NOT NULL 로 지정하면 PRIMARY KEY 절과 같은 제한을 강제로 적용 할 수 있습니다 (즉, 이러한 모든 컬럼에 걸쳐 중복 값이나 NULL 값을 피할 수 있습니다.)

관련 정보를 여러 테이블에 분산시키는 경우는 일반적으로 각 테이블에서 기본 키에 같은 컬럼이 사용됩니다. 예를 들어, 인사 부서의 데이터베이스에는 여러 테이블이 포함 된 각 테이블에는 직원 번호 기본 키가 포함되어있는 경우가 있습니다. 영업부 데이터베이스에는 고객 번호의 기본 키를 포함하는 테이블과 주문 번호의 기본 키를 포함하는 테이블이 포함되어있을 수 있습니다. 기본 키를 사용하여 검색이 매우 빠르기 때문에 이러한 테이블에는 효율적인 결합 쿼리를 작성할 수 있습니다.

PRIMARY KEY 절을 완전히 제거하면 MySQL에 의해 자동으로 숨겨진 기본 키가 생성됩니다. 이것은 필요 이상으로 길어질 수있는 6 바이트의 값이기 때문에 공간이 낭비됩니다. 이것은 숨길이기 때문에 쿼리에서 참조 할 수 없습니다.

응용 프로그램의 성능 고려 사항

InnoDB 의 추가 안정성 및 확장 성 기능을 사용하려면 동일한 MyISAM 테이블보다 많은 디스크 스토리지가 필요합니다. 공간 활용을 개선하고 결과 세트를 처리 할 때 I / O 및 메모리 소비를 줄이고 인덱스 검색을 효율적으로 사용하는 쿼리 최적화 계획을 개선하기 위해 컬럼 및 인덱스의 정의 를 약간 변경 될 수 있습니다.

기본 키에 숫자 ID 컬럼을 설정하는 경우 (특히 결합 쿼리의 경우)는 그 값을 사용하여 다른 모든 테이블에서 관련 값과 상호 참조합니다. 예를 들어, 입력으로 국명을 받아 같은 이름을 검색하는 쿼리를 실행하는 것이 아니라 국가 ID를 확인하기 위해 검색을 한 번 실행하고 여러 테이블에 걸쳐 관련 정보를 검색하기 위해 다른 쿼리 (또는 1 회의 결합 쿼리)를 실행합니다. 고객 번호 또는 카탈로그 항목 번호를 숫자의 문자열로 저장하면 몇 바이트를 부족할 수 있기 때문에 대신, 저장 및 쿼리에 대한 숫자 ID로 변환합니다. 4 바이트 부호없는 INT 컬럼에서는 40 억 이상의 항목 (미국에서 billion의 의미 : 10 억)에 인덱스를 지정할 수 있습니다. 다양한 정수형의 범위에 대해서는 섹션 11.2.1 "정수형 (정확한 숫자) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" 를 참조하십시오.

InnoDB 테이블에 연결된 파일의 이해

InnoDB 파일을 사용하는 경우, MyISAM 파일보다 더 많은주의와 계획이 필요합니다.

InnoDB 의 시스템 테이블 공간 을 나타내는 ibdata 파일 은 삭제하지 마십시오.
한 서버에서 다른 서버로 InnoDB 테이블을 복사하려면 먼저 FLUSH TABLES ... FOR EXPORT 문을 실행 한 후 table_name .ibd 파일과 함께 table_name .cfg 파일을 복사해야합니다.