반성문

요약

MySql 대소문자를 구분하면서 인덱스를 타봅시다.

• 1. 칼럼의 collation을 utf8-bin 으로 설정합니다. - 추천
• 2. 칼럼 자료형을 VARBINARY 로 변경하고 인덱스를 설정합니다.
• 3. WHERE LOGIN_ID = BINARY 'value' 검색 값을 캐스팅해줍니다.

1. 대소문자 구분

MySQL의 varchar 자료형은 기본적으로 대소문자 구분이 없습니다..

SELECT *
FROM USER
WHERE LOGIN_ID = 'aaaa';

2. BINARY

1) 정의

BINARY는 문자열을 바이너리로 캐스팅하고 바이트를 기준으로 비교합니다.

2) 이슈

BINARY로 칼럼을 캐스팅하면 기존에 설정된 인덱스가 적용되지 않고 풀스캔으로 동작합니다.

WHERE BINARY LOGIN_ID = 'aaaa';

조금 좋은 방법은 값을 바이너리로 캐스팅합니다.

WHERE LOGIN_ID = BINARY 'aaaa';

제일 좋은 방법은 칼럼을 VARBINARY 로 변경하고 인덱스를 설정해줍니다.

테스트를 통해 알아봅시다.

3. 테스트

1) 테스트 케이스

길이4의 대소문자 구분된 문자열을 생성했습니다.

생성한 코드는 다음과 같습니다.

52^4으로 약 700만 건이 생성되어야하지만, 10만 건 넣는것도 한시간 넘게 걸려서 10만건으로 테스트합니다.

const lower = Array.from(Array(26).keys()).map(x => String.fromCharCode('a'.charCodeAt(0) + x)).join("")
const upper = lower.toUpperCase()

const alphabet = lower + upper

const loginIdList = []

const makeLoginId = (val, cnt) => {
    if(cnt > 3) {
        loginIdList.push(val)
        return
    }

    for(let i=0; i<52; i++) {
        makeLoginId(val + alphabet.charAt(i), cnt + 1)
    }
}

makeLoginId('', 0)

console.log(loginIdList)

2) 칼럼을 바이너리로 캐스팅

쿼리비용 10644

실행규칙에 'index'라고 표시되지만 실제로는 FULL INDEX SCAN입니다. 모든 로우를 조회합니다.

EXPLAIN
SELECT ID, LOGIN_ID
FROM USER
WHERE BINARY LOGIN_ID = 'aaaa';

3) 값을 바이너리로 캐스팅

쿼리비용 2.83

'aaaa' ~ 'AAAA' 인것 모두 찾은 다음에 거기서 값을 비교합니다.

EXPLAIN
SELECT ID, LOGIN_ID
FROM USER
WHERE LOGIN_ID = BINARY 'aaaa';

4) 칼럼은 VARBINARY로 변경하고, 인덱스 설정

쿼리비용 0.45

5) 비교

쿼리 비용의 차이는 9300 : 2 : 0.45 입니다.

지금 10만건의 테스트 데이터로 진행했을때 수치입니다. 만약 실제 상황에서 정말 많은 데이터가 있고 조인을 한다면, 인덱스가 걸리지 않았을 경우 10초는 그냥 넘어가버립니다.

4. 정리

collation 잘 잡아줍시다.