기본적으로 Index는 오름차순 정렬로 생성된다.
Mysql 5까지는 Index 생성 시 오름차순 정렬만 가능하지만 MySQL 8부터는 정렬순서도 혼합한 인덱스도 생성할 수 있습니다.
하지만 오름차순으로 정렬되어있다고 오름차순으로만 읽어야 하는 것은 아닙니다.
Index Scan 방향은 옵티마이저가 실시간으로 만드는 실행계획에 결정됩니다.
조건에 부합하는 파일이 Index Table의 말미에 위치하는데 정방향으로 끝까지 읽어서 찾는 파일에 접근을 하면 매우 비효율적이며 이러한 케이스에 대하여 임의로 방향을 틀어서 Index Scan을 합니다.
Index Table은 Page단위로 이루어집니다.
Leaf Node에 Key와 실질적인 Value가 이루어지며 Leaf Node는 양방향 연결리스트 방식을 가지므로
정방향, 역방향 Scan이 가능합니다.
하지만 Page끼리는 양방향이지만 Page내부의 레코드는 정방향입니다.
이러한 특징에 의하여 반대 방향으로의 Scan은 성능에 악영향을 보입니다.
다중 컬럼 인덱스의 경우
실제 서비스에서는 2개 이상의 컬럼을 포함하는 인덱스가 더 많이 사용됩니다.
다중 컬럼 인덱스는 순서가 중요합니다.
만약 3개의 컬럼이 인덱스로 설정된다면 두 번째가 인덱스가 첫 번째 인덱스에 의존해서 정렬되며 세 번째 인덱스가 두 번째 인덱스에 의존해서 정렬됩니다.
이렇게 몇개의 키가 존재하더라도 바로 앞에 있는 인덱스를 의존합니다.
그러므로 다중 컬럼 인덱스에서 순서란 매우 중요한 요소입니다.
간단한 예시
1번 케이스
Create Table card(
id int(10) not null auto_increment,
..
key idx_name(name),
key idx_address(address)
)
2번 케이스
Create Table card(
id int(10) not null auto_increment,
..
key idx_index(name, address)
)
select * from card where name = "지아" and address="대구";1번 케이스는 name컬럼과 address컬럼에서 더 빠르게 검색되는 컬럼을 우선으로 탐색하고 그 후에 다음 컬럼을 탐색합니다.
2번 케이스는 한개의 Index Table에 name과 address가 함께 저장됩니다.
한번의 인덱스 스캔으로 원하는 레코드를 찾습니다.
