성능을 고려한 설계

최소 사이즈로 PK 필수 생성

• PK는 되도록 반드시 생성하고 최대한 작게 생성한다.

MariaDB는 PK를 기준으로 Clustering 되므로, PK가 없으면 성능 하락이 발생할 수 있고 테이블 전체에 Lock이 잡히는 이슈가 발생할 수도 있다. (단순 Purge 작업 중 발생하는 경우 확인됨)

Secondary Index를 생성하면 내부적으로 PK값을 모두 갖고 있게 되므로 PK의 사이즈를 최대한 작게 유지하는 것이 좋다. PK 사이즈 계산을 하기 어렵다면 PK를 구성하는 칼럼 수가 3개를 넘지 않도록 하는 것이 좋다.

• PK에 AUTO_INCREMENT 설정하고 PK 데이터는 되도록 수정하지 않는 것이 좋다.

MariaDB는 PK를 기준으로 Clustering 되므로 순차적인 값을 PK로 설정하는 것이 좋다. 따라서 PK는 AUTO_INCREMENT 설정된 칼럼을 사용하는 것을 권장한다.

만약 PK 칼럼의 값을 수정하면 PK를 기준으로 Clustering을 위해서 데이터 위치 이동이 있을 수 있으므로 되도록 PK 칼럼의 값을 수정하지 않는 것이 좋다.

인덱스 설계

• NULL 인덱스

Oracle DB는 NULL을 인덱스에 포함하지 않지만 MariaDB는 NULL값을 인덱스에 포함한다.

# ename 칼럼에 인덱스가 있다면 해당 쿼리는 인덱스를 사용하여 처리된다.
SELECT * FROM emp WHERE ename IS NULL;

• 중복 인덱스 방지

MariaDB는 동일한 인덱스를 중복 생성해도 에러를 발생하지 않으므로 인덱스가 중복되지 않도록 주의한다.

• Prefix Index

가변 데이터 타입에서 데이터 선두의 일부만 인덱싱하는 것을 Prefix Index라고 한다.

VARCHAR, VARBINARY, BLOB, TEXT와 같이 가변 길이를 가질 수 있는 모든 타입은 인덱스에 포함될 때 Prefix가 적용된다.

TEXT 타입과 BLOB 타입에 인덱스를 만들 떄는 명시적으로 prefix length 를 지정해야 한다.

VARCHAR 타입과 VARBINARY 타입을 인덱스에 포함시킬 경우, prefix length를 명시하지 않으면 지정된 칼럼 길이를 prefix length로 설정한다. (utf8 캐릭터셋 사용할 경우 varchar타입은 255자리를 넘어가면 prefix length 제한으로 인해 인덱스 생성이 안 된다)

VARCHAR 타입과 VARBINARY 타입도 TEXT 타입과 BLOB 타입처럼 prefix length를 명시할 수도 있다. InnoDB 테이블에서 Prefix가 적용되는 칼럼에서 사용할 수 있는 prefix length는 최대 767 bytes인데 특정 요건을 충족하면 3072 bytes까지 늘일 수 있다.

• InnoDB 테이블에서 prefix length를 3072 bytes로 늘이기 위한 조건

innodb_large_prefix = ON
innodb_file_per_table = ON
innodb_file_format = barracuda
테이블의 ROW_FORMAT: DYNAMIC 혹은 COMPRESSED

• InnoDB 파일 포멧: 변수 innodb_file_format