Index

주제

내용

인덱스

색인 - 위키백과

책 속의 낱말이나 구절, 또 이에 관련한 지시자를 찾아보기 쉽도록 일정한 순서로 나열한 목록을 가리킨다.  인덱스 라고도 한다.

• 인덱스는 데이터를 빠르게 조회할 수 있도록 도와주는 자료구조 입니다.

• 데이터는 내부적으로 힙영역(Heap)영역에 저장이 됩니다. ⇒ 저장 공간이 필요하다.

만약에 인덱스가 없다면 ?

• 테이블 전체를 풀 테이블 스캔하기 때문에 비효율적

• 풀 테이블 스캔 ?
• 데이터가 1억건이 있다면 1억번의 값을 비교

cluster index

Cluster : 무리, 무리를 이루다

• 인덱스 생성 시 데이터 페이지 전체를 다시 정렬
• 해당 인덱스를 기준으로 정렬이 필요하다.
• 테이블 구조에 영향을 미친다.

• 성능이 매우 빠르다.

• PK , 테이블 당 1개

• 클러스터 인덱스를 잡는 우선순위
• pk → 클러스터 인덱스
• unique index로 정의된 컬럼 중 하나
• 보이지 않는 컬럼을 내부적으로 추가하여 사용한다. ⇒ 인조 식별자



• 테코톡 - 찰리의 인덱싱 : https://www.youtube.com/watch?v=P5SZaTQnVCA

non-cluster index

• 인덱스 키 값에는 해당 데이터에 대한 포인터가 존재
• 정렬과는 상관이 없음, 주소 값으로 찾아갈거니까
• 인덱스의 구조는 데이터 행과 독립적
• 한 테이블에 여러 개 생성 가능

• 리프 페이지가 데이터 페이지를 갖고 있음



• 테코톡 - 찰리의 인덱싱 : https://www.youtube.com/watch?v=P5SZaTQnVCA

WHERE

• 인덱스는 WHERE 절에서 효과가 있다

• WHERE 절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는 것은 성능에 향상에 영향이 없다.

고려할 점

• 조회속도는 빨라지지만 UPDATE, INSERT, DELETE 의 속도는 저하된다는 단점이 있다.
• INSERT의 경우, Table의 index 색인 정보를 갱신하는 추가적인 비용 소요
• DELETE의 경우, 실제 데이터를 삭제하는 것이 아닌 Index 내부에서 사용하지 않는다고 설정을 해주기 때문에 공간이 낭비
• UPDATE의 경우, 인덱스에는 UPDATE라는 개념이 없어서 인덱스에서는 DELETE를 하고 INSERT를 해줌

• 조건절(WHERE)에서 많이 사용되는 컬럼을 인덱스로 생성하는 것이 좋다.

• 동일한 데이터가 적은 컬럼혹은 JOIN을 할 때 사용되는 컬럼을 인덱스로 선언하자

인덱스 효율적으로 설정하기

카디널리티 : 높아야한다. (상대적으로)

성별(남, 녀) 카디널리티 : 2
주민등록번호 : 여러개 => 왜냐하면 모두 다 unique하니까

• 카디널리티 = 총 레코드 수 X 선택도

• 특정 데이터 집합의 유니크(Unique)한 값의 개수이다.

• 중복이 적으면 카디널리티가 높고, 중복이 많으면 카디널리티가 낮다.

선택도 : 낮아야한다.

• 전체 레코드 중에서 조건절에 의해 선택될 것으로 예상되는 레코드의 비율

• 선택도 = 카디널리티 / 총 레코드 수

• 선택도는 0~1사이의 값이며, 1일 때 모든 값이 유니크하다.

• 선택도가 높은 컬럼에 인덱스를 걸면 인덱스가 특정 레코드를 잘 골라내어 인덱스 효율이 높아진다.

활용도

• 해당 컬럼이 실제 작업에서 얼마나 활용되는지

사용하기

// 단일 인덱스
CREATE INDEX 인덱스 이름 ON 테이블 이름(필드 이름1)

// 다중 컬럼 인덱스
CREATE INDEX 인덱스 이름 ON 테이블 이름(필드 이름1, 필드 이름2, ...) 

// 해당 테이블의 인덱스 조회
SHOW INDEX FROM 테이블 이름

// UNIQUE 인덱스 생성 (중복 값을 허용하지 않는 인덱스) 
// 단일 인덱스
CREATE UNIQUE INDEX 인덱스 이름 ON 테이블 이름(필드 이름1)
// 다중 컬럼 인덱스
CREATE UNIQUE INDEX 인덱스 이름 ON 테이블 이름(필드 이름1, 필드 이름2, ...)

//인덱스 정렬 (인덱스 생성 시점에 필드의 정렬방식 설정)
CREATE INDEX 인덱스 이름 ON 테이블 이름 (필드 이름 DESC)
CREATE INDEX 인덱스 이름 ON 테이블 이름 (필드 이름 ASC)

//인덱스 삭제
ALTER TABLE 테이블 이름 DROP INDEX 인덱스 이름

//인덱스 추가 
ALTER TABLE 테이블 이름 ADD (UNIQUE)INDEX 인덱스 이름(컬럼명1, 컬럼명2, ...) 

주의 !

인덱스가 컬럼에 지정되어 있고, select가 있고, where을 사용하지만 인덱스 사용이 안되는 경우

인덱스 컬럼 좌측을 수정한 WHERE 문

// BAD!
SELECT * FROM ANIMAL WHERE SUBSTR(NAME, 1, 2) = 'A';

// GOOD 
SELECT * FROM ANIMAL WHERE NAME LIKE 'A%';

//BAD!
SELECT * FROM ACCOUNT WHERE TOTAL * 1000 = 5000;

// GOOD
SELECT * FROM ACCOUNT WHERE TOTAL = 5000 / 1000;

NULL

// null은 인덱스가 걸리지 않음 
SELECT * FROM CUSTOMERS WHERE PHONE_NUMBER IS NULL;

멀티 컬럼 인덱스

• 인덱스를 걸어주는 순서가 중요하다.
• 첫 번째 컬럼에 뒤가 의존된다.
• 즉, where에 첫 번째 컬럼이 있을 때 인덱스를 타게 된다.

인덱스 키 값의 크기

• InnoDB는 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스도 페이지 단위로 관리된다.
• 페이지는 16KB로 크기가 고정

• 만약 자식 노드의 주소가 담긴 크기가 12Byte 정도라면 16 * 1024 / (16 + 12) = 585 로 인해 하나의 페이지에는 585개가 저장될 수 있다.

[mysql] 인덱스 정리 및 팁

MySQL 인덱스에 관해 정리를 하였습니다. MySQL을 잘 알아서 정리를 한것이 아니라, 잘 알고 싶어서 정리한 것이라 오류가 있을수도 있습니다. 인덱스 == 정렬 인덱스는 결국 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것입니다. insert, update, delete (Command)의 성능을 희생하고 대신 select (Query)의 성능을 향상시킵니다.

https://jojoldu.tistory.com/243

[10분 테코톡] 🍫 찰리의 인덱싱

🙋‍♀️ 우아한테크코스의 크루들이 진행하는 10분 테크토크입니다. 🙋‍♂️ '10분 테코톡'이란 우아한테크코스 과정을 진행하며 크루(수강생)들이 동료들과 학습한 내용을 공유하고 이야기하는 시간입니다. 서로가 성장하기 위해 지식을 나누고 대화하며 생각해보는 시간으로 자기 주도적인 성장을 지향하는 우아한테크코스의 문화 중 하나입니다. 🌕우아한테크코스란 🌕 우아한테크코스는 일반 사용자용 서비스를 개발하는 회사가 필요로 하는 역량을 가진 프로그래머를 양성하기 위한 교육입니다.

https://www.youtube.com/watch?v=P5SZaTQnVCA