mysql stored procedure 내에서 NAME_CONST() 함수 사용으로 성능저하 문제

원래대로라면 15초 내에 종료되었어야 할 프로시저가 1시간이 지나도 끝나지 않고

관련 테이블의 LOCK 까지 발생하여
확인해보니 name_const()로 변환되는 부분에서 문제가 있는 것을 확인하였다.

 

해결과정까지 가는 건 많은 시간이 소요되었지만 결국 답을 찾았고 관련 기록을 정리한다.

 

DB ver: mysql 5.7.1x

1. 장애프로시저 확인

 

CREATE PROCEDURE `xxxx`.`SP_xxxx_LIST`(
in VAR_DATE VARCHAR(20)
)
BEGIN
	DECLARE varMonth varchar(6);
   
	set varMonth = DATE_FORMAT(date_sub(now(),interval +1 MONTH),'%Y%m');
	

	update TB_TEMP_TABLE
	SET RCP_DT = CDP_DT 
	where TG_YYMM = varMonth
	and CAST_CD in ('xxx','xxx')
	and LENGTH(RCP_DT) !=8;

........

2.  원인분석

 

mysql > show full processlist;

/* 프로시저 중간에 이 부분에서 지연이 발생함을 확인함 */

| 591165 | xxxxadm      | 192.168.141.202:49131 | xxxx | Query   |  3638 | Updating                 | update TB_TEMP_TABLE
	SET RCP_DT = CDP_DT 
	where TG_YYMM = NAME_CONST('varMonth',_utf8mb4'202212' COLLATE 'utf8mb4_unicode_ci') 
	and CAST_CD in ('xxx','xxx')
	and LENGTH(RCP_DT) !=8 |    0.000 |

#1. 실행계획 확인

explain
select * from TB_TEMP_TABLE
	where TG_YYMM = NAME_CONST('varMonth',_utf8mb4'202212' COLLATE 'utf8mb4_unicode_ci') 
	and CAST_CD in ('xxx','xxx')
	and LENGTH(RCP_DT) !=8;


#result)
1	SIMPLE	TB_TEMP_TABLE	range	IDX_TEMP_TABLE_06	IDX_TEMP_TABLE_06	18		99890358	Using where

 

IDX_TEMP_TABLE_06은 CAST_CD의 인덱스이다. (TG_YYMM의 인덱스는 IDX_TEMP_TABLE_04)

TB_TEMP_TABLE 테이블은 대용량 파티션 테이블로 약 80GB 정도의 용량을 갖고 있다.
TG_YYMM = '202212' 값이 
내부 함수인 NAME_CONST('varMonth',_utf8mb4'202212' COLLATE 'utf8mb4_unicode_ci')에 
의해 자동으로 묶였는데 이 부분에서 인덱스 사용을 못하고 있다.

#2. 인덱스가 깨졌는지 확인

 

explain
select * from TB_TEMP_TABLE 
where TG_YYMM = '202212' 
and CAST_CD in ('xxx','xxx')
and LENGTH(RCP_DT) !=8;


#result)
1	SIMPLE	TB_TEMP_TABLE	ref	IDX_TEMP_TABLE_04,IDX_TEMP_TABLE_06	IDX_TEMP_TABLE_04	21	const	4752697	Using where

직접 값을 입력했을시 정상적으로 TG_YYMM의 ref 인덱스를 사용하는 것을 확인함, 인덱스는 정상이다.

#3. 실행엔진이 간혹 잘못된 PLAN을 세울수도 있기때문에 인덱스를 강제로 할당테스트

 

explain
select * from TB_TEMP_TABLE force index (IDX_TEMP_TABLE_04)
where TG_YYMM = NAME_CONST('varMonth',_utf8mb4'202212' COLLATE 'utf8mb4_unicode_ci') 
and CAST_CD in ('xxx','xxx')
and LENGTH(RCP_DT) !=8;

#result)
1	SIMPLE	TB_TEMP_TABLE	ALL					198699556	Using where

실행엔진이 문제인가 싶어 force 인덱스로 강제로 주었는데 아에 full scan해서 쿼리가 끝나지 않음

실행엔진이 멍청한 것도 아니다!

 

#4. 확인결과

인덱스는 문제없고 procedure내에서 선언한 varchar 타입인 '202212' 값이 
NAME_CONST() 로 내부함수로 자동변환해서 읽히는데 
이 부분에서 왜 인덱스가 안 타는지 좀 더 확인필요!

 

3. 해결과정

 

일단 name_const() 함수에 대해 알아본다.

#1) name_const 함수란?

NAME_CONST( name , value )
지정된 값을 반환합니다. 결과 집합의 열을 생성 할 때 NAME_CONST() 을 사용하면 지정된 이름이 컬럼에 지정됩니다.
결과 집합 열을 생성하는데 사용하면 NAME_CONST()열이 지정된 이름을 갖게 됩니다. 
인수는 상수여야 합니다.

 

#ex)
mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)

같은 의미로 쿼리 안에서 AS 로 컬럼의 닉네임을 거는 것과 동일하다.
mysql> SELECT 14 AS myname;
+--------+
| myname |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)

 

이 함수는 저장 프로시저를 복제할 때 내부적으로 사용됩니다. 
SQL 문에서 명시적으로 사용하는 것은 거의 의미가 없으며 그렇게 사용해서는 안됩니다.

ex)
SELECT NAME_CONST('myname', 14) 
--> name_const() 함수는 mysql 내부에서 사용하는 것으로 user가 굳이 사용할 필요없다는 것


#name_const() 에서 name은 알겠는데 value는 왜 아래처럼 들어가는 것인가?

_utf8mb4'202212' COLLATE 'utf8mb4_unicode_ci'

#이것도 공식 reference에서 확인가능
https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

#9.1.1 문자열 리터럴

A character string literal may have an optional character set introducer and COLLATE clause, 
to designate it as a string that uses a particular character set and collation:

문자열 리터럴 COLLATE에는 특정 문자 집합과 데이터 정렬을 사용하는 문자열로 지정하기 위해 선택적인 문자 집합 소개자와 절이 있을 수 있습니다.

[_charset_name]'string' [COLLATE collation_name]


ex)
SELECT _latin1'string';
SELECT _binary'string';
SELECT _utf8mb4'string' COLLATE utf8mb4_danish_ci;

-> mysql에서 사용하는 default form 이 _utf8mb4'some text' COLLATE 인 것을 확인함


#2) DBA 관련 카페 문의
https://cafe.naver.com/prodba
-> name_const() 함수로 인해 성능저하가 있었는지 경험많은 DBA분에게 문의하여

여기서 character set 관련 문제로 이슈를 좁힐 수 있었다.


#3) 구글링

비슷한 상황의 질의를 찾았고 해결가능한 힌트를 찾게됨

#MySQL Character Set & Select Query Performance in stored procedure
https://stackoverflow.com/questions/71771201/mysql-character-set-select-query-performance-in-stored-procedure

 

#question)
Recently I noticed few queries are taking very long time in execution, 
checked further and found that MySQL Optimizer is trying to use COLLATE in Where clause 
and that's causing performance issue, if I run below query without COLLATE then getting quick response from database:

최근 몇 가지 쿼리가 실행에 매우 오랜 시간이 걸리는 것을 확인하고 추가로 확인한 결과 
MySQL Optimizer가 Where절에서 COLLATE를 사용하려고 시도하고 있으며 
COLLATE 없이 쿼리 아래에서 실행하면 데이터베이스에서 빠른 응답을 받으면 성능 문제가 

발생한다는 것을 발견했습니다.

SELECT notification_id FROM notification  
WHERE ref_table = 2 
AND ref_id =  NAME_CONST('v_wall_detail_id',_utf8mb4'c37e32fc-b3b5-11ec-befc-02447a44a47c' COLLATE 'utf8mb4_unicode_ci')

 

 

..

Any suggestion, what improvements are needed to make my queries faster?
쿼리 속도를 높이려면 어떤 개선이 필요합니까?

 

#answer)
The table's character set is utf8, so I guess its collation is one of utf8_general_ci or utf8_unicode_ci. 
테이블의 문자 집합이 utf8이므로 데이터 정렬이 utf8_general_ci 또는 utf8_unicode_ci 중 하나인 것 같습니다.

You can check this way:
다음과 같이 확인할 수 있습니다.

 

SELECT collation_name from INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = '...your schema...' AND table_name = 'notification'
AND column_name = 'ref_id';

You are forcing it to compare to a string with a utf8mb4 charset and collation. 

An index is a sorted data structure, and the sort order depends on the collation of the column. 
Using that index means taking advantage of the sort order to look up values rapidly, without examining every row.

When you compared the column to a string with a different collation, MySQL cannot infer that the sort order or string equivalence of your UUID constant is compatible. 
So it must do string comparison the hard way, row by row.

This is not a bug, this is the intended way for collations to work. 
To take advantage of the index, you must compare to a string with a compatible collation. 

utf8mb4 문자 세트 및 데이터 정렬이 있는 문자열과 비교하도록 강제하고 있습니다. 
인덱스는 정렬된 데이터 구조이며 정렬 순서는 열의 데이터 정렬에 따라 다릅니다. 
해당 인덱스를 사용한다는 것은 정렬 순서를 활용하여 모든 행을 검사하지 않고 빠르게 값을 조회한다는 의미입니다.

열을 데이터 정렬이 다른 문자열과 비교할 때 MySQL은 정렬 순서 또는 UUID 상수의 문자열 등가가 호환 가능하다고 추론할 수 없습니다. 
따라서 문자열 비교를 row 단위의 어려운 방식으로 수행해야 합니다.

이것은 버그가 아니며 데이터 정렬이 작동하도록 의도된 방식입니다. 
인덱스를 활용하려면 호환되는 데이터 정렬이 있는 문자열과 비교해야 합니다.

 

 

# 1) mysql 의 default character set , cllation 확인 (my.cnf 설정)

 

mysql> show global variables like '%coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)


mysql> show global variables like '%char%';
+--------------------------+----------------------------------------------------------+
| Variable_name            | Value                                                    |
+--------------------------+----------------------------------------------------------+
| character_set_client     | utf8mb4                                                  |
| character_set_connection | utf8mb4                                                  |
| character_set_database   | utf8mb4                                                  |
| character_set_filesystem | binary                                                   |
| character_set_results    | utf8mb4                                                  |
| character_set_server     | utf8mb4                                                  |
| character_set_system     | utf8                                                     ||
+--------------------------+----------------------------------------------------------+
8 rows in set (0.00 sec)

 

 

#2) 컬럼 타입 확인

 

SELECT
CHARACTER_SET_NAME
,COLLATION_NAME 
FROM INFORMATION_SCHEMA.`COLUMNS`  
WHERE TABLE_NAME = 'TB_TEMP_TABLE'
and COLUMN_NAME = 'TG_YYMM';


----------------------------
utf8  |   utf8_general_ci  |
----------------------------

 

 

빙고!

 

4. 해결

 

원인은 TG_YYMM 컬럼은 character set  = utf8이고

mysql 의 my.cnf / default character set = utf8mb4 이 차이가 있어서 발생하였다.

프로시저 내에서 선언한 varMonth varchar(6)은 당연히 mysql default 케릭터셋인 utf8mb4 를 쓰고 있다.

where 절에서

utf8 = utf8mb4 를 비교하니 

mysql에서 

야! varMonth  변수 이거 utf8 아니고 utf8mb4 이야 ."라고 

name_const() 함수를 통해 알려주고 있었고 

 

"이러면 인덱스 못쓴다.!" 라고 

show processlist를 통해 한번 더 알려주고 있었는데

의미를 제대로 알지 못함..^^;




#수정방법은 2가지고 성능은 1번이 조금 더 좋다.

(내부 함수라도 함수를 한번 덜써서 그런것으로 보임)

1) varMonth 선언할 때 TG_YYMM 컬럼과 동일한 character set 으로 변경

 

/* DECLARE varMonth varchar(6) 를 아래처럼 utf8로 명시적으로 선언*/
DECLARE varMonth varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci; 


#log)

mysql> show full processlist;

update TB_TEMP_TABLE
SET RCP_DT = CDP_DT 
where TG_YYMM =  NAME_CONST('varMonth',_utf8'202212' COLLATE 'utf8_general_ci')
and CAST_CD in ('xxx','xxx')
and LENGTH(RCP_DT) !=8;

 

 

#실행계획

-> NAME_CONST()를 쓰지만 동일한 utf8이기 때문에 ref 인덱스 스캔을 한다.



2) query 문 안에서 수정

 

update TB_TEMP_TABLE
SET RCP_DT = CDP_DT 
where TG_YYMM = CONVERT(varMonth USING utf8)
and CAST_CD in ('xxx','xxx')
and LENGTH(RCP_DT) !=8;


#log)

mysql> show full processlist;

update TB_TEMP_TABLE
SET RCP_DT = CDP_DT 
where TG_YYMM = CONVERT( NAME_CONST('varMonth',_utf8mb4'202212' COLLATE 'utf8mb4_unicode_ci') USING utf8)
and CAST_CD in ('xxx','xxx')
and LENGTH(RCP_DT) !=8

 

 

#실행계획

-> NAME_CONST( _utf8mb4 를 쓰지만 다시 convert 함수로 utf8로 변경해서 결국 ref 인덱스 스캔이 된다.

 

끝.