Mysql Procedure Index 문제

문제점

 - 세션에서 직접 쿼리를 요청하면 Index를 탄다. 그런데 Procedure에서 IN으로 매개변수를 넘겼을 경우. 타입까지 동일 시 했는데 인덱스를 무시하는 경우가 생겨버림.

 - 데이터가 100만건 이상이라. 오랜 시간이 걸려서 query interrupt로 중지가 됨.

 

일단 해결은 했는데, 원인을 찾기 위해서 여러 방면으로 알아봤다.

 

아래는 비슷한 케이스. 물론 답변은 없는게 함정..

 

www.gurubee.net/article/79401

mysql 프로시저 인덱스 관련

 

다음은 해결하기 전에 확인해야 하는 사항이다.

SELECT @@version

10.1.37-MariaDB-1~xenial

#테이블 character set을 알아온다.
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = '스키마 이름'
  AND T.table_name = '테이블 이름';

#스키마 character set을 알아온다.
SELECT default_character_set_name, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA
WHERE schema_name = '스키마 이름';

 

내 경우 테이블 character set은 utf8이었고, 스키마는 utf8mb4이었다.

다음은 테스트 했던 과정과 결과다.

 

create definer = user@`%` procedure change_naturalization_step1_TEST(IN $old_id varchar(36))
proc_label:BEGIN
    
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- default exception handler
        ROLLBACK;
        #SET @@SQL_SAFE_UPDATES=1;
        RESIGNAL;
    END;

START TRANSACTION;

    EXPLAIN
    SELECT *
    FROM
    (
    	#Using where; Using index; Using temporary
        SELECT DISTINCT user_id
        FROM t_user_temp USE INDEX(t_user_id_index) #FORCE INDEX(t_user_id_index)
        WHERE user_id = $old_id
        LIMIT 1
    ) AS A
    INNER JOIN t_user_temp AS B
    ON A.user_id = B.user_id;

    #Using where
    EXPLAIN
    SELECT * FROM t_user_temp WHERE user_id = $old_id;

    #Using index condition
    EXPLAIN
    SELECT * FROM t_user_temp WHERE user_id = CAST($old_id AS CHAR CHARSET utf8);

    #Using index condition
    EXPLAIN
    SELECT * FROM t_user_temp WHERE user_id = 'cda293be-4d54-4337-8d6e-a29bb210fc8e';

    SET @test_user_id = 'cda293be-4d54-4337-8d6e-a29bb210fc8e';
    #Using where
    EXPLAIN
    SELECT * FROM t_user_temp WHERE user_id = @test_user_id;

    SELECT id
    INTO @test_user_id2
    FROM t_user
    WHERE id = 'cda293be-4d54-4337-8d6e-a29bb210fc8e';

    #Using index condition
    EXPLAIN
    SELECT * FROM t_user_temp WHERE user_id = @test_user_id2;

COMMIT;

SELECT 0;
	
END;

 

Procedure를 실행 할 때 매개변수가 utf8mb4로 넘어가는 듯 하다.

저런 경우 테이블에 맞는 CHARSET으로 변경 후 조회를 하면 인덱스를 태운다.

아니면 이름이 절대 중복될 수 없도록 세션 변수를 만들어 맨 아래처럼 처리해도 되긴 한다.

 

이거 처리로 30분 + 이유 찾느라 2시간 약간 넘게 걸린 듯...

다른 사람은 시간 아끼라고 올린다.

그런데 보통 이렇게 CHARSET이 꼬이는 경우는 99% 없을 듯. ㅋㅋㅋ