MariaDB SQL INDEX - INDEX가 적용되지 않는 이유들

WHERE 절에서 Column 값을 변경하여 비교하면 INDEX가 적용되지 않습니다.

 

WHERE 절에서 지정된 문자열로 시작하는 Column를 찾을 경우 SUBSTR() 대신 LIKE%를 사용합니다.

LIKE%는 INDEX가 적용됩니다.

%LIKE는 INDEX 적용되지 않아 가급적 제한된 조건에서 사용해야 합니다.

-- 단일 INDEX 생성
CREATE INDEX IDX_MBR_ID ON MBR_ACCOUNT_TB (MBR_ID);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE SUBSTR(MBR_ID, 1, 2) = 'te';
--> type : ALL, key : NULL, rows : 2, extra : Using where
--> INDEX로 검색되지 않고 WHERE 절 조건으로 처리

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID LIKE 'te%';
--> type : range, key : IDX_MBR_MULTI, rows : 2, extra : Using index condition
--> INDEX로 검색

-- INDEX 제거
ALTER TABLE MBR_ACCOUNT_TB DROP INDEX IDX_MBR_ID;

 

WHERE 절에서 UPPER()나 LOWER()는 별도 Column으로 생성하여 처리해야 합니다.

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE UPPER(MBR_ID) = UPPER('testid1');
--> type : ALL, key : NULL, rows : 2, extra : Using where
--> INDEX로 검색되지 않고 WHERE 절 조건으로 처리

-- UPPER Column 추가 (처음부터 CREATE TABLE에서 Column를 추가해서 처리하시기 바랍니다.)
ALTER TABLE MBR_ACCOUNT_TB ADD IF NOT EXISTS MBR_ID_UPPER VARCHAR(100) AS (UPPER(MBR_ID));

-- 단일 INDEX 생성
CREATE INDEX IDX_MBR_ID_UPPER ON MBR_ACCOUNT_TB (MBR_ID_UPPER);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID_UPPER = UPPER('testid1');
--> type : ref, key : IDX_MBR_ID_UPPER, rows : 1, extra : Using where
--> INDEX로 검색

-- INDEX 제거
ALTER TABLE INT_BOARD_TB DROP INDEX IDX_MBR_ID_UPPER;

-- UPPER Column 삭제
ALTER TABLE MBR_ACCOUNT_TB DROP IF EXISTS MBR_ID_UPPER;

 

WHERE 절에서 DATETIME은 DATE_FORMAT() 대신 조건문을 사용해야 합니다.

-- 단일 INDEX 생성
CREATE INDEX IDX_REG_DTM ON MBR_ACCOUNT_TB (REG_DTM);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE DATE_FORMAT(REG_DTM, '%Y') = DATE_FORMAT(NOW(), '%Y');
--> type : ALL, key : NULL, rows : 2, extra : Using where
--> INDEX로 검색되지 않고 WHERE 절 조건으로 처리

-- 비교문으로 처리
EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE REG_DTM >= '2022-01-01' AND REG_DTM < '2023-01-01';
--> type : range, key : IDX_REG_DTM, rows : 2, extra : Using index condition
--> INDEX로 검색

-- INDEX 제거
ALTER TABLE MBR_ACCOUNT_TB DROP INDEX IDX_REG_DTM;

 

WHERE 절에서 연산은 Column에서 하지 않아야 합니다.

-- 단일 INDEX 생성
CREATE INDEX IDX_MBR_BIRTHYEAR ON MBR_ACCOUNT_TB (MBR_BIRTHYEAR);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE (2022 - MBR_BIRTHYEAR + 1) = 35;
--> type : ALL, key : NULL, rows : 2, extra : Using where
--> INDEX로 검색되지 않고 WHERE 절 조건으로 처리

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_BIRTHYEAR = (DATE_FORMAT(NOW(), '%Y')  - 34);
--> type : ref, key : IDX_MBR_BIRTHYEAR, rows : 1, extra : Using index condition
--> INDEX로 검색

-- INDEX 제거
ALTER TABLE MBR_ACCOUNT_TB DROP INDEX IDX_MBR_BIRTHYEAR;

 

WHERE 절에서 Column 값이 NULL이면 조건으로 제거하면 INDEX에 효율적입니다.

SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_NICKNAME LIKE '홍%';

SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_NICKNAME LIKE '홍%' AND MBR_NICKNAME IS NOT NULL;