안녕하세요, 멀티컬럼인덱스 개념 후반부 내용이 이해가 가지 않아 질문 드립니다.

답변 감사합니다. 늦지 않게 답변 주셔서 괜찮아요!

그동안 제가 알게된 점과 함께 조금 더 풀어서..설명을 해보겠습니다. 질문 의도가 전해지지 않았던 것 같아요

제가 위와 같은 실무 상황이라면 two_field의 단일 인덱스를 설정했을 거예요.

그러나, 질문드리고 싶은 점은 mysql에서 실행 계획을 정할 때

인덱스를 활용했을 때 더 효율적임에도 불구하고 풀스캔을 하는 이유가 있는지입니다.

제가 했던 테스트를 예로 들어보겠습니다.

위 테이블을 응용해서 컬럼 개수가 많고, row당 데이터가 큰 테이블을 생성했습니다.

CREATE TABLE test1 (
	seq INT PRIMARY KEY AUTO_INCREMENT,
	one_field BIGINT,
	two_field BIGINT,
	hash_email1 VARCHAR(700) not null default "",
    hash_email2 VARCHAR(700) not null default "",
    hash_email3 VARCHAR(700) not null default "",
    hash_email4 VARCHAR(700) not null default "",
    hash_email5 VARCHAR(700) not null default "",
    hash_email6 VARCHAR(700) not null default "",
    hash_email7 VARCHAR(700) not null default "",
    hash_email8 VARCHAR(700) not null default "",
    hash_email9 VARCHAR(700) not null default "",
    hash_email10 VARCHAR(700) not null default "",
	INDEX idx_hash_email (hash_email), -- 해시 인덱스,
	INDEX idx_col1_col2 (one_field, two_field) -- 복합 인덱스
);

위 테이블에 작성해주신 재귀문을 통해 약 300만 개의 데이터를 넣고, two_field = 2인 데이터를 딱 하나 삽입하였습니다.

SET @@cte_max_recursion_depth = 100000;

-- 30번 수행
INSERT INTO test1 (one_field, two_field)

WITH RECURSIVE my_cte AS (

	SELECT 1 AS n, CAST(1 AS DOUBLE) AS abc, CAST(3 AS DOUBLE) AS se

    UNION ALL

    SELECT 1+n, CAST(1+n AS DOUBLE), CAST(3+n AS DOUBLE) FROM my_cte WHERE n < 100000

)

SELECT abc, se FROM my_cte;

-- 1번 수행
INSERT INTO test1 (one_field, two_field) value (1, 2);

해당 테이블은 300만 + 1개의 데이터가 들어가 있고, 약 300mb의 크기를 갖게 되었습니다.

이 상태에서 조회문을 날려보았습니다.

-- 1. explain 결과 : type = INDEX (인덱스 풀스캔)
SELECT seq, one_field, two_field FROM test1 WHERE two_field = 2;

-- 2. explain 결과 : type = ALL (풀스캔)
SELECT * FROM test1 WHERE two_field = 2;

위 2번 쿼리는 여전히 풀스캔이 발생하였는데요,

제 생각엔 (비록 two_field가 선행이 아닐지라도) 1번 쿼리처럼 idx_col1_col2 인덱스를 타서 two_field = 2인 데이터의 seq(primary key) 값을 찾아낸 후, 그를 통해 실제 데이터를 조회하면 풀스캔에 비해 시간을 대폭 줄일 수 있을 것 같았습니다.

실제로 위 2번 쿼리는 제 환경에서 약 2초의 수행 시간이 걸렸는데요,

-- 3. explain 결과 : 서브쿼리 type = INDEX, 그 이후 메인쿼리 type = CONST
SELECT * FROM test1 WHERE seq = (SELECT seq FROM test6 WHERE two_field = 2);

위와 같이 명시적으로 본 질문 의도와 같이 작성한 쿼리를 날려보면 약 0.5초로 수행 시간이 많이 줄어든 것을 확인할 수 있었습니다.

처음 질문했던 의도는 3번 쿼리와 같이 수행하면 더 효율적임에도 불구하고 풀스캔을 하는 이유가 따로 있는지 여쭤본 것이었어요.

좀 더 찾아봐야겠지만 질문하면서 든 생각은, 이 정도 튜닝은 mysql에서 지원하는 영역이 아닌 개발자가 해야하는 영역이라는 생각이 드네요

답변 주신 것처럼 애초에 two_field를 선행으로 갖는 인덱스를 생성하면 되는 일이기도 하니까요

안녕하세요. 사실 이 복합인덱스에 대해서는 실무에서 적용도 하기 꽤나 까다롭고 복잡한 부분이 많습니다. 생각보다 제약사항도 꽤나 많고요.

데이터를 가져오는데에는 무리가 없지만 얼마나 효율적으로 얼마나 오버헤드 없이 잘 가져 올 수 있냐의 차이로 보시면 될 꺼같아요.

질문자님이 말씀하신 것처럼 데이터 자체를 가져오는 것에는 문제는 없지만 풀스캔이 동작 할 수 있는 쿼리 입니다.

다음과 같은 테이블을 기준으로 설명을 드려 볼게요.

CREATE TABLE test1 (
	seq INT PRIMARY KEY AUTO_INCREMENT,
	one_field BIGINT,
    two_field BIGINT,
    hash_email VARCHAR(500),
    INDEX idx_hash_email (hash_email), -- 해시 인덱스
    INDEX idx_col1_col2 (one_field, two_field) -- 복합 인덱스
);

강의에서 다루는 테이블이며 idx_col1_col2 를 기준으로 사용이 될 것입니다.

질문자님이 주셨던 질문의 쿼리를 전송을 하게 되면, type All 로 좋은 쿼리로써 동작을 할 수는 없어요.

왜냐하면, 해당 복합 인덱스의 순서는 보장이 되어야 하기 떄문이에요.

그래서 다음과 같은 쿼리는 효율적으로 동작을 하게 될 거에요.

EXPLAIN SELECT hash_email, one_field, two_field FROM test1 WHERE one_field = 2 AND two_field = 3;

이전과의 차이는 인덱스가 지정된 필드를 모두 넣어 두었다는 차이가 있어요.
그리고 다음과 같은 쿼리도 문제가 없을 거에요.

EXPLAIN SELECT hash_email, one_field, two_field FROM test1 WHERE one_field = 2;

이 두 쿼리와 강의자분이 주셨던 쿼리의 차이는 one_field가 선행이 되었다는 차이가 있어요.

그러니 만약 two_field가 선행이 되어야 한다면, 그에 맞는 인덱스를 설정해보시면 어떨까 싶어요.

어느정도 질문에 대한 정답이 되었을까요?? 확인이 늦어서 답변을 늦게 드린거 같은데 죄송합니다 ㅠ