MySQL5.6 기본 MySQL 스토리지 엔진으로 InnoDB

14.1.1 기본 MySQL 스토리지 엔진으로 InnoDB

MySQL은 사용하기 쉽고 높은 성능과 확장 성을 제공한다는 평가를 유지할 수있게되었습니다. MySQL 5.5 이전에서는 MyISAM 이 디폴트 스토리지 엔진이었습니다. 우리의 경험상 대부분의 사용자는 기본 설정을 변경하지 않았습니다. MySQL 5.5 이상에서는 InnoDB 가 기본 스토리지 엔진입니다. 역시 대부분의 사용자가 기본 설정을 변경하지 않을 것으로 예상됩니다. 그러나 InnoDB 를 사용하면 기본 설정에서 사용자가 RDBMS에서 기대하는 이점 ( ACID 트랜잭션, 참조 무결성 및 충돌 복구 )를 얻을 수 있습니다. InnoDB 테이블을 사용하여 MySQL 사용자, DBA 또는 개발자로서의 삶을 개선하는 방법을 살펴 보자.

스토리지 엔진의 사용 경향

MySQL의 성장기 1 년차에는 초기의 Web 기반의 응용 프로그램에서 병렬 및 가용성의 한계가 넓혀지는 것은 없었습니다. 최근에는 하드 드라이브 및 메모리 용량 및 가격 대비 성능이 모두 향상하고 있습니다. MySQL의 성능 한계를 극복하는 사용자는 신뢰성과 충돌 복구에 많은 관심을 가지고 있습니다. MySQL 데이터베이스는 크고 높은 부하에서 견고하고 분산에서 중요합니다.

InnoDB 는 이러한 사용자의 최우선 처리합니다. 스토리지 엔진의 사용 경향은보다 확장 가능한 InnoDB 쪽으로 이동하고 있습니다. 따라서 MySQL 5.5은 InnoDB 를 기본 스토리지 엔진에 대한 논리 전이 출시했다.

MySQL은 이전에 MyISAM 테이블이 필요했던 사례에 대한 대처에 노력하고 있습니다. MySQL 5.6 이상에는 다음과 같은 특성이 있습니다.

InnoDB 는 FULLTEXT 인덱스 유형을 사용하여 전체 텍스트 검색을 할 수 있습니다. 자세한 내용은 섹션 14.2.13.3 "FULLTEXT 인덱스" 를 참조하십시오.
InnoDB 는 읽기 전용 또는 읽기가 대부분의 워크로드에서 더 잘 작동하도록했습니다. 자동 커밋 모드에서는 InnoDB 쿼리에 자동으로 최적화가 적용되며, START TRANSACTION READ ONLY 구문을 사용하면 읽기 전용으로 트랜잭션을 명시 적으로 표시 할 수 있습니다. 자세한 내용은 섹션 14.13.14 "InnoDB의 읽기 전용 트랜잭션 최적화" 를 참조하십시오.
읽기 전용 미디어에 배포 된 응용 프로그램은 InnoDB 테이블이 사용되게되었습니다. 자세한 내용은 섹션 14.3.1 "읽기 전용 작업에 대한 InnoDB의 구성" 을 참조하십시오.

기본 MySQL 스토리지 엔진으로 InnoDB의 중요성

MySQL 5.5.5 이후 새로운 테이블의 기본 스토리지 엔진은 InnoDB 입니다. 이 변경은 새로 생성 된 테이블에서 ENGINE=MyISAM 등의 어구를 사용하여 스토리지 엔진이 지정되지 않은 경우에 적용됩니다. 이렇게 기본 동작을 변경하면 MySQL 5.5은 MyISAM 이 사용되는 테이블이 InnoDB 로 전환시 이점을 얻을 수 있는지를 평가하기위한 논리 포인트가 될 수 있습니다.

MySQL 내부 사양의 일부가 구현되어있는 mysql 및 information_schema 데이터베이스는 계속 MyISAM 이 사용됩니다. 특히 부여 테이블을 전환해도 InnoDB 를 사용할 수 없습니다.

InnoDB 테이블의 장점

MyISAM 테이블을 사용하고 있지만, 기술적 인 이유로 그들에 관여하지 않는 경우, InnoDB 테이블을 사용하면 더욱 편리한 점이 많이있을 것입니다.

하드웨어 또는 소프트웨어의 문제로 인해 서버가 충돌 한 경우에도 그 시점에서 데이터베이스에 무슨 일이 발생했는지에 관계없이 데이터베이스를 다시 시작한 후 특별한 것은 아무것도 할 필요가 없습니다. InnoDB 의 충돌 복구 를 사용하면 자동으로 충돌시 이전에 커밋 된 변경 사항은 완료 처리 중이던 커밋되지 않은 변경은 취소됩니다. 단순히 다시 시작하고 종료 한 장소에서 계속하면됩니다. 이 프로세스는 MySQL 5.1 이전보다 훨씬 빨라졌습니다.
테이블 및 인덱스의 데이터에 액세스하면 데이터는 InnoDB 의 버퍼 풀 에 캐시됩니다. 자주 사용되는 데이터는 직접 메모리에서 처리됩니다. 이 캐시는 매우 많은 종류의 정보에 적용되며, 이로 인해 처리 속도가 크게 오릅니다. 그 결과 전용 데이터베이스 서버에서는 최대 물리적 메모리의 80 %가 InnoDB 버퍼 풀에 할당됩니다.
관련 데이터를 다양한 테이블로 분할하면 강제로 참조 무결성 이 적용되는 외부 키 를 설정할 수 있습니다. 데이터를 업데이트하거나 삭제하면 다른 테이블의 관련 데이터도 자동으로 업데이트 또는 삭제됩니다. 기본 테이블에 해당 데이터가 존재하지 않는 보조 테이블에 데이터를 삽입하려고하면 자동으로 잘못된 데이터가 제외됩니다.
디스크 또는 메모리의 데이터가 손상된 경우 가짜 데이터를 사용하기 전에 체크섬 메커니즘에 의해 경고가 발행됩니다.
테이블마다 적절한 기본 키 컬럼을 가진 데이터베이스를 설계 할 때 이러한 열이 관여하는 작업을 자동으로 최적화됩니다. WHERE 절, ORDER BY 절, GROUP BY 절 및 결합 작업은 프라이 머리 키 컬럼에 대한 참조가 매우 빠릅니다.
삽입, 업데이트 및 삭제는 변경 버퍼링 라는 자동화 메커니즘에 의해 최적화됩니다. InnoDB 는 같은 테이블에 병렬 읽기 및 쓰기 액세스가 허용되는뿐만 아니라 디스크 I / O가 효율화되도록 변경된 데이터가 캐시됩니다.
성능의 장점은 장시간 실행되는 쿼리를 포함한 거대한 테이블에만 국한되지 않습니다. 같은 행이 하나의 테이블에서 여러 번 사용되면 적응 형 해시 인덱스 라는 기능에 인계되어 해시 테이블에서 읽은 것처럼 이러한 검색 속도가 빨라집니다.

InnoDB 테이블의 모범 사례

장기간 InnoDB 를 사용하고 있으면 이미 트랜잭션과 외래 키 등의 기능을 이해할 수 있습니다. 그렇지 않은 경우는이 장 전체에서 이러한 내용 참조하십시오. 요컨대 다음과 같습니다.

가장 자주 쿼리가 실행되는 컬럼 (여러 적용될 수 있음)을 사용하고있는 모든 테이블에 기본 키 를 지정합니다. 명시적인 기본 키가 존재하지 않는 경우는 자동 증가 값을 지정합니다.
여러 테이블에 동일한 ID 값에 따라 그 테이블에서 데이터를 추출하는 경우, 결합 의 개념을 도입합니다. 조인 성능을 빠르게하려면 결합 컬럼에 외부 키 를 정의하고 각 테이블에서 그 열을 동일한 데이터 형식으로 선언합니다. 또한 외부 키를 사용하면 영향을받는 모든 테이블에 삭제 또는 업데이트가 반영되어 부모 테이블에 해당하는 ID가 존재하지 않는 경우는 자식 테이블의 데이터 삽입이 방지됩니다.
자동 커밋 을 해제합니다. 초당 수백 번 커밋하면 성능에 제한이 설정됩니다 (이것은 저장 장치의 쓰기 속도로 제한됩니다).
관련 DML 작업 집합을 START TRANSACTION 과 COMMIT 문 안에함으로써 트랜잭션 으로 그룹화합니다. 자주 커밋하고 싶지 않은 반면, 커밋없이 몇 시간 동안 실행되는 INSERT , UPDATE 또는 DELETE 문의 거대한 배치 발생시키고 싶지 않습니다.
LOCK TABLE 문 사용을 중지합니다. InnoDB 는 한 번에 동일한 테이블에 대한 모든 읽기 및 쓰기를함으로써 신뢰성과 높은 성능을 희생하지 않고 여러 세션을 처리 할 수 있습니다. 행 세트에 독점 쓰기 권한을 취득하려면 SELECT ... FOR UPDATE 구문을 사용하여 갱신 대상의 행만을 잠급니다.
innodb_file_per_table 옵션을 사용하여 하나의 거대한 시스템 테이블 스페이스 의 대신 별도의 파일로 각 테이블의 데이터 및 인덱스를 배치합니다. 이 설정은 테이블의 압축 및 빠른 잘림 등의 기타 기능의 일부를 사용할 때 필요합니다.
사용중인 데이터 및 액세스 패턴은 CREATE TABLE 문에서 새로운 InnoDB 테이블의 압축 기능 ( ROW_FORMAT=COMPRESSED )에서 이득을 얻을 수 있는지를 평가합니다. 읽기 및 쓰기 기능을 희생하지 않고 InnoDB 테이블을 압축 할 수 있습니다.
옵션 --sql_mode=NO_ENGINE_SUBSTITUTION 으로 서버를 실행하여 CREATE TABLE 의 ENGINE= 절에 지정된 스토리지 엔진에 문제가 발생했을 경우 다른 스토리지 엔진을 사용하여 테이블을 생성하지 않도록합니다 .

InnoDB 테이블에 대한 최근의 개선점

테이블 및 관련된 인덱스를 압축 할 수 있습니다.
이전보다 훨씬 작은 성능 및 가용성에 미치는 영향에 인덱스를 생성 및 삭제할 수 있습니다.
테이블 잘라 내기가 상당히 빨라지고 InnoDB 에서만 재사용 될 수있는 시스템 테이블 스페이스의 공간을 확보하는 것이 아니라 운영 체제에서 다시 사용되는 디스크 공간을 확보 할 수 있습니다.
DYNAMIC 행 형식을 사용하여 테이블 데이터 스토리지 레이아웃이 BLOB 및 긴 텍스트 필드에서보다 효율적으로되었습니다.
INFORMATION_SCHEMA 테이블에서 쿼리를 실행하여 스토리지 엔진의 내부 동작을 모니터 할 수 있습니다.
performance_schema 테이블에서 쿼리를 실행하여 스토리지 엔진의 성능을 상세하게 모니터 할 수 있습니다.
성능에 관해서는 많은 개선점이 있습니다. 특히 충돌 복구, 즉 데이터베이스가 재시작 할 때 모든 데이터를 자동으로 매칭시키는 처리 속도 및 안정성이 향상되었습니다 ( InnoDB 사용자가 기존 경험 해왔다 속도보다 훨씬 빠릅니다). 데이터베이스가 클수록 크게 속도가 향상됩니다.

대부분의 새로운 성능 기능은 자동입니다. 그렇지 않은 경우에도 필요한 것은 많아도 구성 옵션 값을 설정하면됩니다. 자세한 내용은 섹션 14.13 "InnoDB 성능" 을 참조하십시오. 응용 프로그램 코드에 적용 할 수있는 InnoDB 고유의 튜닝 기술 내용은 섹션 8.5 "InnoDB 테이블 최적화" 를 참조하십시오. 고급 사용자는 섹션 14.12 "InnoDB 부팅 옵션 및 시스템 변수" 를 다시 확인하십시오.

기본 스토리지 엔진으로 InnoDB를 사용하여 테스트 및 벤치 마크

MySQL 5.1 이전에서 MySQL 5.5 이상으로 업그레이드가 열리기 전에 데이터베이스 서버 또는 응용 프로그램에서 기본 스토리지 엔진으로 InnoDB 가 제대로 작동하는지 여부를 미리 볼 수 있습니다. 이전의 MySQL 릴리스에서 기본 스토리지 엔진으로 InnoDB 를 설정하려면 명령 줄에서 --default-storage-engine=InnoDB 를 지정하거나 my.cnf 파일의 [mysqld] 섹션에 default-storage-engine=innodb 를 추가하고 서버를 다시 시작합니다.

기본 스토리지 엔진을 변경해도 새로 만들어진 테이블 밖에 영향을받지 않기 때문에 응용 프로그램의 설치 및 구성 단계를 수행하여 모든 것이 제대로 설치되어 있는지 확인합니다. 다음 모든 응용 프로그램 기능을 수행하여 데이터의로드, 편집 및 조회 기능이 모두 작동하는지 확인합니다. 테이블이 일부 MyISAM 고유의 기능에 의존하는 경우 오류가 수신됩니다. 오류를 방지하려면 ENGINE=MyISAM 절을 CREATE TABLE 문에 추가합니다 (예를 들어, 전체 텍스트 검색에 의존하는 테이블은 InnoDB 테이블이 아닌 MyISAM 테이블에해야합니다).

스토리지 엔진에 대해 신중한 결정을하지 않은 경우에 특정 테이블이 InnoDB 에서 작성된 때 어떻게 작동 하는지를 미리 각 테이블에 ALTER TABLE table_name ENGINE=InnoDB; 명령을 실행 합니다. 또한 원래의 테이블을 배포하지 않고 테스트 쿼리 및 기타 문을 실행하려면 다음과 같은 복사본을 만듭니다.

 CREATE TABLE InnoDB_Table (...) ENGINE = InnoDB AS SELECT * FROM MyISAM_Table;

MySQL 5.5 이상으로 매우 많은 InnoDB 의 성능 향상으로 인해 현실적인 워크로드에서 전체 응용 프로그램을 사용할 때의 성능에 대한 정확한 고찰을 위해서는 최신의 MySQL 서버를 설치 라고, 벤치 마크를 실행하십시오.

전체 애플리케이션 라이프 사이클 (설치에서 자주 사용까지) 및 서버 재부팅을 테스트합니다. 정전 시뮬레이션을위한 데이터베이스의 부하가 높을 때 서버 프로세스를 강제 종료하고 서버를 다시 시작할 때 데이터가 성공적으로 복구되는지 여부를 확인합니다.

특히 마스터 및 슬레이브에서 다양한 MySQL 버전이나 옵션을 사용하는 경우는 복제 구성을 테스트합니다.

InnoDB가 기본 스토리지 엔진인지 여부의 확인

이전 MySQL을 사용하여 what-if 테스트를 할 것인지, 최신 MySQL을 사용하여 포괄적 인 테스트를 실시하는지에 관계없이 InnoDB 의 상태를 확인하는 방법은 다음과 같습니다.

SHOW ENGINES; 명령을 실행하여 다양한 MySQL 스토리지 엔진을 모두 표시합니다. InnoDB 행에 DEFAULT 를 찾습니다.
InnoDB 가 전혀 존재하지 않는 경우, InnoDB 의 지원없이 컴파일 된 mysqld 바이너리가 있기 때문에 다른 바이너리를 입수해야합니다.
InnoDB 는 존재하지만, 사용할 수있는 경우, 부팅 옵션 및 구성 파일까지 다시 모든 skip-innodb 옵션을 삭제합니다.