[MariaDB] Index

📇 Index

💡 인덱스는 테이블에서 검색 속도를 향상시키기 위한 목적으로 존재한다. 인덱스는 물리적인 위치에 대한 정보를 가지고 있다.

• PK, FK 컬럼 지정 시에 인덱스가 자동으로 생성된다.
• 주로 WHERE절의 조건이나 JOIN 연산에 생성한다.

💡 인덱스가 항상 좋은 것만은 아니다.

• 데이터 변경사항에 따라 인덱스도 변경되어야 한다는 점에서 번거롭다는 점이 있다. 일부 데이터들이 삭제 되면 테이블 내부의 데이터 인덱스를 정렬한 구조가 흐트러진다. 자료구조의 데이터가 편향되고 인덱스의 의의가 변질되기 때문에 결국 효율적이지 않게 된다. 인덱스는 데이터의 변화에 따라 변경되고 재구성 해야한다.
  
  
• 인덱스는 물리적으로 저장된다는 점에서 저장공간을 차지하게 된다. 상황에 따라 인덱스가 효율적일수도 있고 풀스캔이 효율적일 수 있다. 어떤 특정 컬럼이 조건 검색에 쓰여서 풀스캔보다는 인덱스가 나을 것 같다 판단이 드는 것만 인덱스를 생성해야 한다.

Optimizer

• 파싱에 성공하면 쿼리 최적화기(optimizer)가 작동한다. 사용할 인덱스 결정, 조인 순서, 데이터를 읽는 방법 등을 포함하여 ‘실행 계획’이라는 형태로 생성된다.
• 옵티마이저는 쿼리를 실행하는 데 필요한 가장 효율적인 방법을 결정하기 위해 사용된다.
• MariaDB에서는 기본적으로 CBO를 사용하며 RBO를 제공하지 않는다.

옵티마이저의 종류

1) 비용기반 옵티마이저(Cost-Based Optimizer, CBO)

: 쿼리를 실행하는 데 필요한 비용을 추정하여 쿼리 실행 계획을 선택한다. 이 비용은 디스크
I/O, CPU 사용량 등의 자원 사용을 고려하고 데이터베이스의 통계 정보(테이블의 행 수,
인덱스의 유니크한 값 등)를 사용하여 비용을 추정한다.

데이터베이스의 실제 상황을 더 잘 반영하는 쿼리 실행 계획을 선택할 수 있다.

2) 규칙기반 옵티마이저(Rule-Based Optimizer, RBO)

: 고정된 세트의 규칙을 기반으로 쿼리 실행 계획을 결정한다. 규칙은 개발자나 DBA(Database
Administrator)가 설정할 수 있으며 특정 상황에서 특정 유형이 연산이 더 효과적이라는 경험
적인 지식에 기반해 정해진다.

실제 데이터 분포나 통계를 고려하지 않으므로 때때로 비효율적인 쿼리 실행 계획을 선택할 
수 있다.

실행 계획

• ‘EXPLAIN’ 명령어를 사용해 실행 계획을 확인할 수 있다.
• 인덱스 없이 확인하면 Extra : Using where
  • WHERE 절을 사용하여 행을 필터링했음을 나타낸다.

EXPLAIN SELECT * FROM phone WHERE phone_name = 'galaxyS23';

• 인덱스 생성 후 확인하면 Extra : Using Index condition (컬럼 데이터를 읽지 않고 인덱스를 통해 정보를 얻었음을 나타낸다. 이를 '커버링 인덱스'라고도 한다.)

Index 생성

CREATE INDEX idx_name ON phone(phone_name); 

복합 Index 생성

CREATE INDEX idx_name_price ON phone(phone_name, phone_price);

Index 조회

SHOW INDEX FROM phone;

데이터베이스(스키마)에 존재하는 인덱스 조회

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'menudb';

인덱스 최적화 (재구성)

• 인덱스에 해당하는 컬럼 값이 변화하면 인덱스도 변화해야 한다.

ALTER TABLE phone DROP INDEX idx_name;
ALTER TABLE ADD INDEX index_name(phone_name);

OPTIMIZE TABLE phone;