SQL 튜닝 사례 (부재:Table Charset 표준 위배 사례)

SQL 튜닝 사례 (부재:Table Charset 표준 위배 사례)

데이터베이스 운영하다 보면, 루틴한 작업으로 표준이라는 단어가 무감각해 지고, 무시하거나 잊고 지나가는 경우가
종종 있다.

 

아래의 사례는 테이블 생성 표준이라는 지극히 일반적인이고 쉽게 잊어 버릴수 있는 케이스라고 할 수 있을 것 같다.테이블간 조인 구문을 작성할 경우,

오라클 같은 경우는 인덱스 컬럼, 컬럼 타입, 컬럼 가공 등을 유의하면 작성하면 일반적으로 큰 문제가 생기지 않는다.

하지만, MySQL 같은 경우는 테이블 charset, 컬럼 charset 등 추가적인 고려 사항이 있을 수 있다.

 

아래의 경우는, 해당 사이트에서 정의한 테이블 생성 표준을 준수하지 않아서 발생한 경우라고 볼 수 있다.

 

하나의 테이블은 utf8 이며, 다른 하나의 테이블은 utf8mb4 로 생성된 경우인데, 기존에는 모든 테이블들이 해당 사이트 표준인 utf8 로 생성 되어 운영 중이 였지만, 특정 테이블에 4bytes 문자로 입력 요건으로 인하여 utf8mb4로 변경하여 발생한 성능 이슈 사례이다.

 

테이블 정보 및 SQL 정보 :

 - TB_DATACOLLECT : 약 300만건 / UTF8MB4
 - TB_FIELD_MAPPING : 약 8,000건 / UTF8

 

SELECT I.*       FROM TB_DATACOLLECT I     WHERE CONCAT(I.COL1,'_',I.COL2)) IN          (SELECT MAP.FIELD_NAME             FROM TB_FIELD_MAPPING MAP            WHERE MAP.FIELD_NAME=:1)

 

1) 테이블간 Charset 이 상이한 경우

    - 두 테이블간 charset 이 다르기 때문에 내부적으로 convert 로 인하여 성능 저하 이슈가 발생한 케이스이다.

    - 해당 케이스는 convert로 인한 index를 정상적으로 활용하지 못한 케이스이며, 실행 계획 역시 비효율적으로 생성 된 케이스이다.

    - 이 케이스는 NL 조인에서 가장 좋지 않은 방식으로 수행된 케이스이다, NL 조인에서는 Inner Table 에 대해서 인덱스를 활용 할수 없다면,

      Inner Table에 집합이 작은 테이블을 선택하는 것이 효율적이다.

    - 응답 시간 : 16분 51초

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	MAP	ref	PRIMARY	PRIMARY	152	const	496	Using where; Using index; Start temporary
1	SIMPLE	I	ALL					2408059	Using where; End temporary; Using join buffer (Block Nested Loop)

 

2) 테이블간 동일한 Charset 인 경우

    - MySQL 5.6에서 기능 향상된 subquery meterialization 활용하여 실행계획이 생성되었다.

    - 응답 시간 : 7.7 초

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	I	ALL					2418647	Using where
1	SIMPLE	<subquery2>	eq_ref	<auto_key>	<auto_key>	302	func	1	Using where
2	MATERIALIZED	MAP	ref	PRIMARY	PRIMARY	152	const	496	Using where; Using index

 

 

요약하면, 상이한 charset을 가진 두 테이블 조인시 내부 convert로 인하여 인덱스 활용 및 MySQL 5.6에서 향상된 기능을 활용할 수 없기 때문에 SQL 성능

저하되었다. 만약 테이블 생성 또는 변경시 표준을 고려하여 생성 변경 하였다면 발생하지 않을 문제 였다. 사이트 마다 데이터베이스 관리자들이 항상 표준에 대해서 강조하고 운영 관리를 하고 있지만, 운영하다보면 표준이라는 단어가 희미해지기 마련이다. 그러므로 다시 한번 더 표준을 검토하고 운영관리할 수 있도록 마음을 다져 보자.

 

참고 사이트 : https://www.percona.com/blog/2008/10/21/join-performance-charsets/