[MySQL] prefix index로 조회속도 최적화

[문제식별]

입력받은 사용자 개인정보를 통해 존재하는 회원인지 확인하고 화면을 분기하는 과정의 유스케이스가 있었습니다.

이때, 아무런 장치가 없다면 Mysql은 Full Table Scan이 발생합니다. O(N)

 

Full Table Scan은 테이블의 모든 데이터를 순차적으로 읽으며 테이블의 모든 행을 탐색합니다.

그렇게 되면 해당 로직이 가입자 수에 비례하게 처리시간이 늘어납니다.

[해결방안]

사용자를 조회될 때 사용되는 컬럼에 index를 생성하면  정렬된 B-tree 구조의 구조가 생성되기 때문에 O(log N)의 시간복잡도로 조회할 수 있습니다.

[해결과정]

1. 테이블 생성

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    encrypted_info CHAR(64)
);

 

필요한 컬럼만 추가하여 테스트 테이블을 생성해 줍니다.

 

2. 더미생성 프로시저 생성

-- DELETE PROCEDURE IF EXISTS generate_dummy_data;

DELIMITER $$

CREATE PROCEDURE generate_dummy_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 1000000 DO
        SET @encrypted_info = SHA2(UUID(), 256);
        INSERT INTO users (encrypted_info) VALUES (@encrypted_info);
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

CALL generate_dummy_data();

 

생성된 테이블에 100만 더미 데이터를 삽입하는 프로시저를 생성하고 실행해 줍니다.

 

실제로는 Springboot 기준 Spring Security의 PasswordEncoder를 이용하고 있지만, 예제 간략화를 위해 SHA2함수를 이용해 암호화해 줍니다.

 

3. 더미생성 확인

 

count 쿼리를 통해 확인한 결과 100만 개의 더미 데이터가 정상적으로 삽입되었습니다.

 

 

4. 인덱스 없이 조회

아무런 장치 없이 사용되는 조회 쿼리를 실행해 봅니다.

SELECT * FROM prefix_index_test.datas WHERE encrypted_info = "065c74364c2664d0f8ef8e5a65a74f223610271dde63a93fbf486d7de56eda18"

평균 0.2165 sec

결과는 평균 0.2165 sec의 응답시간을 가지게 됩니다.

 

5. 전체길이 유니크 인덱스 적용 후 조회

다음은 해당필드에 UNIQUE INDEX를 생성하여 조회합니다.

CREATE UNIQUE INDEX idx_encrypted_info_full_unique ON users (encrypted_info);

EXPLAIN

0.00088

평균 0.00088 sec의 응답속도로 매우 빠른 응답속도를 가지게 되었습니다.

 

실행계획 역시 사용된 인덱스는 "idx_encrypted_info_full_unique"이며,  Extra에서 "Using index"가 표시된 것은 이 쿼리가 인덱스만을 이용해 데이터를 읽었다는 뜻으로 의도대로 동작됨을 의미합니다.

 

단 계정을 인덱스를 설정한 컬럼이 암호화되어 관리되고 있었기 때문에 60byte의 값을 Index로 사용하게 되었습니다.

Mysql InnoDB는 기본 페이지단위가 16kb임으로 인덱스 길이가 길수록 여러 페이지를 읽거나 버퍼풀이 가득 차게 되면서 캐시미스가 발생할 가능성이 높아지기 때문에 성능저하가 발생할 수 있습니다.

[출처 : https://icarus8050.tistory.com/42] [MySQL] 인덱스 정리

인덱스 키 값의 크기
 InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 기본 단위를 Page 또는 Block이라고 하며, 디스크의 모든 읽기 및 쓰기의 최소 작업 단위가 됩니다. 또한 Page는 InnoDB 스토리지 엔진의 버퍼풀에서 데이터를 버퍼링 하는 기본 단위이기도 합니다. 인덱스도 결국 Page 단위로 관리되며, B-Tree의 Root, Branch, Leaf를 구분하는 기준이 바로 Page 단위입니다. InnoDB의 모든 페이지 크기는 16KB로 크기가 고정되어 있습니다.
 
 만약 설정된 인덱스 키의 크기가 16 Byte이고, 자식 노드의 주소가 담긴 크기가 12 Byte라면, 16 * 1024 / (16 + 12) = 585 이므로 하나의 페이지에는 585개가 저장될 수 있습니다. 여기서 인덱스 키의 크기가 32 Byte로 커지면 16 * 1024 / (32 + 12) = 372가 되어 한 페이지에 372개가 저장됩니다. 즉, 조회 결과로 500개의 row를 읽을 때, 인덱스 크기가 16 Byte이면 1개의 페이지에서 모두 조회가 가능하지만 인덱스의 크기가 32 Byte이면 2개의 페이지를 읽어야 하므로 성능 저하가 발생하게 됩니다. 따라서 인덱스의 키는 길면 길수록 성능상 이슈가 발생합니다.

 

그래서 전체 부분을 index로 사용하지 않고 앞부분을 잘라서 인덱싱하는 prefix index을 도입하기로 결정했습니다.

 

6. Prefix 유니크 인덱스 적용 후 조회

DROP INDEX idx_encrypted_info_full_unique ON users;
CREATE UNIQUE INDEX idx_encrypted_info_prefix_unique ON datas (encrypted_info(16));

 

기존 인덱스를 제거하고 앞 16자리를 이용해서 인덱스를 생성해 줍니다.

 

이때 한 가지 고려할 점이 암호화된 prefix 16자리에 대한 중복가능성인데 사용하는 암호화 알고리즘마다 조금 차이가 있습니.

예를 들어 BCryptPasswordEncoder의 경우 앞에 7자리가 각각 작업비용과 알고리즘 식별자로 고정된 값이어서 prefix 16의 경우 9자로만 중복가능성이 결정됩니다. 때문에 BCrypt로 암호화된 데이터라면 Salt값 까지 포함한 29자리로 설정해야 할 것 같습니다.

출처: https://velog.io/@kellyb9/bcrypt-%EC%95%94%ED%98%B8%ED%99%94

 

 

prefix index를 설정한 후 조회 결과는 다음과 같습니다.

 

평균 0.00127 sec

EXPLAIN

 

평균 0.00127 sec으로 네트워크 상황을 고려한다면 느려졌다고 판단하기는 어렵지만 성능이 개선되지는 못한 것 같습니다.

 

조회를 한건씩 테스트를 해서는 버퍼풀이나 캐시미스가 발생하지 않기 때문이라고 판단되어 짧은 시간 안에 많은 조회쿼리를 실행해 보기로 결정했습니다.

-- DROP PROCEDURE IF EXISTS prefix_indexing_test;
DELIMITER $$

CREATE PROCEDURE prefix_indexing_test()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE dummy_var INT DEFAULT 0;
    
    WHILE i < 900000 DO  -- 반복 횟수 설정
        -- 실제로 존재하는 프로시저를 호출
       SET @random_encrypted_info = (SELECT encrypted_info FROM datas  WHERE id = i);
        
        -- dummy_var에 저장함을로써 select 결과 출력X
        SET dummy_var = (SELECT id FROM users WHERE encrypted_info = @random_encrypted_info);
        SET i = i + 1;
    END WHILE;
    
END$$

DELIMITER ;

 

90만 데이터 각각에 대한 조회를 while문을 통해 짧은 시간 안에 실행하는 프로시저를 구성합니다.

 

 

 

prefix인덱스로 세 번의 테스트는 약 23 sec 이 소요되었고 다시 전체길이 index로 변경한 결과 26 sec이 걸리는 것을 확인했습니다.