데이터베이스 성능 향상을 위한 주요 ySQL 스키마 검사-MySQL 튜토리얼-php.cn

데이터베이스 스키마는 테이블, 열, 관계, 인덱스, 데이터 구성 및 액세스 방식을 형성하는 제약 조건 등 데이터베이스의 논리적 구조를 정의합니다. 데이터가 저장되는 방식뿐만 아니라 쿼리, 트랜잭션 및 기타 작업과 상호 작용하는 방식도 중요합니다.

이러한 점검은 눈덩이처럼 불어나 더 큰 문제가 발생하기 전에 새로운 문제나 남아 있는 문제를 파악하는 데 도움이 될 수 있습니다. 아래에서 이러한 스키마 검사에 대해 자세히 알아보고 데이터베이스가 통과하지 못할 경우 문제를 해결하는 방법을 정확하게 알아볼 수 있습니다. 스키마를 변경하기 전에 항상 데이터를 백업하여 수정 중에 발생할 수 있는 잠재적 위험으로부터 보호하세요.

1. 기본 키 확인(기본 키 누락)

기본 키는 모든 테이블의 중요한 부분으로, 각 행을 고유하게 식별하고 효율적인 쿼리를 가능하게 합니다. 기본 키가 없으면 테이블에 성능 문제가 발생할 수 있으며 복제 및 스키마 변경 유틸리티와 같은 특정 도구가 제대로 작동하지 않을 수 있습니다.

스키마를 설계할 때 기본 키를 정의하면 피할 수 있는 몇 가지 문제가 있습니다.

기본 또는 고유 키가 지정되지 않은 경우 MySQL은 사용할 수 없는 내부 키를 생성합니다.
기본 키가 없으면 특히 행 기반 또는 혼합 복제의 경우 복제 성능이 저하될 수 있습니다.
기본 키를 사용하면 확장 가능한 데이터 보관 및 삭제가 가능합니다. pt-online-schema-change와 같은 도구에는 기본 키 또는 고유 키가 필요합니다.
기본 키는 행을 고유하게 식별하며 이는 애플리케이션 관점에서 매우 중요합니다.

예

테이블이 이미 생성된 경우 "ID" 열에 PRIMARY KEY 제약 조건을 생성하려면 다음 SQL을 사용하세요.

ALTER TABLE Persons ADD PRIMARY KEY (ID);

로그인 후 복사

여러 열에 기본 키를 정의하려면:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

로그인 후 복사

참고: ALTER TABLE 명령을 사용하는 경우 테이블이 처음 생성될 때 기본 키 열이 NULL 값을 포함하지 않도록 선언되어야 합니다.

2. 테이블 엔진 점검(더 이상 사용되지 않는 테이블 엔진)

MyISAM 스토리지 엔진은 더 이상 사용되지 않으며 이를 계속 사용하는 테이블은 InnoDB로 마이그레이션해야 합니다. InnoDB는 뛰어난 성능, 데이터 복구 기능 및 트랜잭션 지원으로 인해 대부분의 사용 사례에 기본이자 권장되는 엔진입니다. MyISAM에서 InnoDB로 마이그레이션하면 쓰기 작업이 많은 애플리케이션의 성능이 크게 향상되고 내결함성이 향상되며 전체 텍스트 검색 및 외래 키와 같은 고급 MySQL 기능을 사용할 수 있습니다.

InnoDB를 선호하는 이유:

충돌 복구 기능을 사용하면 데이터 손상 없이 데이터베이스 서버 또는 호스트 충돌로부터 자동으로 복구할 수 있습니다.
쿼리의 영향을 받은 행만 잠그므로 동시성이 높은 환경에서 훨씬 더 나은 성능을 얻을 수 있습니다.
데이터와 인덱스를 모두 메모리에 캐시하므로 읽기 작업이 많은 워크로드에 적합합니다.
완전히 ACID를 준수하여 데이터 무결성을 보장하고 거래를 지원합니다.
InnoDB 엔진은 MySQL 개발 커뮤니티의 대부분의 관심을 받아 가장 최신이고 잘 지원되는 엔진입니다.

InnoDB로 마이그레이션하는 방법

ALTER TABLE Persons ADD PRIMARY KEY (ID);

로그인 후 복사

3. 테이블 대조 확인(혼합 대조)

테이블 전체 또는 테이블 내에서 서로 다른 데이터 정렬을 사용하면 특히 문자열 비교 및 조인 중에 성능 문제가 발생할 수 있습니다. 두 문자열 열의 데이터 정렬이 다른 경우 MySQL은 런타임에 문자열을 변환해야 할 수 있으며, 이로 인해 인덱스가 사용되지 않고 쿼리 속도가 느려질 수 있습니다.

혼합 데이터 정렬 테이블을 변경하면 다음과 같은 몇 가지 문제가 나타날 수 있습니다.

데이터 정렬은 열 수준에서 다를 수 있으므로 조인의 관련 열에 일치하는 데이터 정렬이 있으면 테이블 수준의 불일치로 인해 문제가 발생하지 않습니다.
특히 문자 세트 스위치를 사용하여 테이블의 데이터 정렬을 변경하는 것이 항상 간단한 것은 아닙니다. 데이터 변환이 필요할 수 있으며, 지원되지 않는 문자는 손상된 데이터로 변할 수 있습니다.
테이블을 생성할 때 데이터 정렬이나 문자 집합을 지정하지 않으면 데이터베이스 기본값이 상속됩니다. 데이터베이스 수준에 아무것도 설정되지 않은 경우 서버 기본값이 적용됩니다. 이러한 문제를 방지하려면 특히 조인 작업에 자주 사용되는 열의 경우 전체 데이터 세트에서 데이터 정렬을 표준화하는 것이 중요합니다.

대조 설정을 변경하는 방법

데이터베이스의 데이터 정렬 설정을 변경하기 전에 비프로덕션 환경에서 접근 방식을 테스트하여 의도하지 않은 결과가 발생하지 않도록 하세요. 확실하지 않은 사항이 있으면 DBA에게 문의하는 것이 가장 좋습니다.

모든 데이터베이스에 대한 기본 문자 집합 및 데이터 정렬 검색:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

로그인 후 복사

특정 테이블의 데이터 정렬 확인:

ALTER TABLE <table_name> ENGINE=InnoDB;

로그인 후 복사

서버의 기본 문자 집합 찾기:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

로그인 후 복사

서버의 기본 데이터 정렬 찾기:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

로그인 후 복사

특정 데이터베이스의 데이터 정렬 업데이트:

SELECT @@GLOBAL.character_set_server;

로그인 후 복사

특정 테이블의 데이터 정렬 업데이트:

SELECT @@GLOBAL.collation_server;

로그인 후 복사

4. 테이블 문자 집합 확인(혼합 문자 집합)

혼합 문자 집합은 성능 및 호환성 문제를 일으킬 수 있다는 점에서 혼합 데이터 정렬과 유사합니다. 혼합 문자 집합은 서로 다른 열이나 테이블이 데이터 저장에 서로 다른 인코딩 형식을 사용하는 경우 발생합니다.

혼합 문자 집합은 인덱스 사용을 방해하거나 값 변환을 요구하여 문자열 열의 조인 성능을 저하시킬 수 있습니다.
문자 집합은 열 수준에서 정의할 수 있으며 조인과 관련된 열에 일치하는 문자 집합이 있는 한 테이블 수준의 불일치로 인해 성능이 영향을 받지 않습니다.
테이블의 문자 집합을 변경하면 데이터 변환이 필요할 수 있으며, 지원되지 않는 문자가 발견되면 데이터가 손상될 수 있습니다.
문자 집합이나 데이터 정렬을 지정하지 않으면 테이블은 데이터베이스의 기본값을 상속하고, 데이터베이스는 서버의 기본 문자 집합과 데이터 정렬을 상속합니다.

캐릭터 설정을 변경하는 방법

데이터베이스의 문자 설정을 조정하기 전에 스테이징 환경에서 변경 사항을 테스트하여 예상치 못한 문제가 발생하지 않도록 하세요. 어떤 단계에 대해 확실하지 않은 경우 DBA에게 문의하여 지침을 받으세요.

모든 데이터베이스에 대한 기본 문자 집합 및 데이터 정렬 검색:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

로그인 후 복사

열의 문자 집합 가져오기:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

로그인 후 복사

서버의 기본 문자 집합 찾기:

ALTER TABLE <table_name> ENGINE=InnoDB;

로그인 후 복사

서버의 기본 데이터 정렬 찾기:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

로그인 후 복사

테이블 구조를 보려면:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

로그인 후 복사

예제 출력:

SELECT @@GLOBAL.character_set_server;

로그인 후 복사

열 문자 집합을 변경하려면:

SELECT @@GLOBAL.collation_server;

로그인 후 복사

5. 컬럼 자동 증분 체크(자동 증분 컬럼 종류)

무한히 커질 것으로 예상되고 기본 키에 자동 증가를 사용할 것으로 예상되는 테이블의 경우 UNSIGNED BIGINT 데이터 유형으로 전환하는 것이 좋습니다. 이를 통해 열은 훨씬 더 넓은 범위의 값을 처리할 수 있으므로 나중에 최대값에 도달한 후 비용이 많이 드는 테이블 변경이 필요하지 않습니다. UNSIGNED를 지정하면 양수 값만 저장되어 데이터 유형의 범위가 사실상 두 배로 늘어납니다.

캐릭터 설정을 변경하는 방법

열 유형을 UNSIGNED BIGINT로 수정하려면:

ALTER DATABASE <db-name> COLLATE=<collation-name>;

로그인 후 복사

6. 테이블 외래키 확인(외래키 존재 여부)

외래 키는 상위 테이블과 하위 테이블 간의 관계를 유지하여 데이터 일관성을 제공하지만 데이터베이스 성능에도 영향을 미칩니다. 쓰기 작업이 발생할 때마다 관련 데이터의 무결성을 확인하기 위해 추가 조회가 필요합니다. 특히 트래픽이 많은 환경에서는 속도가 느려질 수 있습니다.

성능이 중요하다면 특히 애플리케이션 수준에서 데이터 일관성을 처리할 수 있는 시나리오에서는 외래 키 제거를 고려할 수 있습니다.

외래 키를 제거하는 방법

테이블에서 외래 키 제약 조건을 삭제하려면:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

로그인 후 복사

7. 중복지수 확인

MySQL의 중복 인덱스는 불필요한 디스크 공간을 소비하고 모든 인덱스를 업데이트해야 하므로 쓰기 작업 중에 추가 오버헤드를 생성합니다. 이는 쿼리 최적화를 복잡하게 만들어 잠재적으로 실질적인 이점을 제공하지 못한 채 비효율적인 실행 계획을 초래할 수 있습니다.

중복 인덱스를 식별하고 제거하여 쿼리 최적화를 간소화하고 오버헤드를 줄입니다. 하지만 인덱스를 제거하기 전에 해당 인덱스가 중요한 쿼리에 사용되고 있지 않은지 확인하세요.

8. 미사용 인덱스 확인

MySQL에서 사용되지 않는 인덱스는 디스크 공간을 소비하고 삽입, 업데이트, 삭제 중 처리 오버헤드를 증가시키고 전체 작업 속도를 저하시켜 데이터베이스 성능에 부정적인 영향을 미칠 수 있습니다. 인덱스는 쿼리 속도를 높이는 데 유용하지만 사용되지 않는 인덱스는 시스템에 불필요한 부담을 줄 수 있습니다.
사용되지 않거나 중복된 색인을 제거하면 다음과 같은 추가 이점이 있습니다.

인덱스 수가 적기 때문에 MySQL의 최적화 프로그램은 평가할 선택 항목이 적어서 쿼리 실행이 단순화되고 CPU/메모리 사용량이 줄어듭니다.
사용하지 않는 인덱스를 제거하면 더 중요한 데이터에 사용할 수 있는 귀중한 디스크 공간이 확보되고 I/O 효율성도 향상됩니다.
인덱스 수가 최소화되면 재구축 또는 재구성과 같은 인덱스 유지 관리 작업이 더 빨라지고 리소스 집약도가 줄어듭니다. 이는 특히 연중무휴 가동 시간이 필요한 환경에서 더욱 원활한 운영을 가능하게 합니다.

MySQL 또는 MariabDB에서 사용되지 않는 인덱스를 식별하려면 다음 SQL 문을 사용하십시오.

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

로그인 후 복사

사용되지 않거나 중복된 인덱스를 제거하는 방법

MySQL 8.0 이상에서는 인덱스를 완전히 삭제하지 않고도 인덱스가 필요한지 테스트하기 위해 인덱스를 보이지 않게 만들 수 있습니다.

ALTER TABLE <table_name> ENGINE=InnoDB;

로그인 후 복사

성능에 영향이 없으면 인덱스를 안전하게 삭제할 수 있습니다.

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

로그인 후 복사

필요한 경우 색인을 다시 표시되도록 되돌릴 수 있습니다.

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

로그인 후 복사