인덱스를 어떻게 써야 잘쓰는건지 (그리고 AUTO INCREMENT를 써야하는지에 대한 고민)

injoon2019 commented 2 years ago

주제

인덱스는 어떨 때 쓸 수 없는지
선택도(기수성)

선정 이유

인덱스를 만들고 유지하는 것은 비용이다. 책에선 인덱스가 있는 테이블에 레코르를 하나 추가하는 것은 그냥 추가하는 것보다 1.5의 추가 비용이 든다고 했다. 예를 들어 테이블에 인덱스가 3개 걸려있는 경우 데이터를 추가하면 데이터 추가 비용은 (1 + 1.5 *3)이 된다. 그럼 어떤 상황에서 인덱스가 유용하게 쓰이고 어떤 상황에서는 인덱스를 쓰지 못할까?
인덱스에서 선택도가 인덱스나 쿼리의 효율성에 큰 영향을 미친다고 하여 알아보고 싶었다.

해당 텍스트

인덱스는 꼭 CRUD에서 R에서만 쓰이는 것은 아니다. SELECT 뿐만 아니라 UPDATE, DELETE 등 레코드를 검색해야하는 경우면 쓰일 수 있다. 다만 B-Tree 인덱스를 쓸때 100% 일치 또는 앞부분부터 일치해야 사용할 수 있다. 즉 `LIKE '%토르' 이런 쿼리에서는 인덱스를 사용할 수 없다. 또한 SUBSTRING 같은 것을 이용해서 키 값에 변형이 가해져도 이용할 수 없다.
인덱스에서 선택도(기수성)은 유니크한 값의 수를 의미한다. 어떤 크기의 테이블에서 선택도가 낮다는 말은 중복된 데이터가 많다는 뜻이고 선택도가 높다는 말은 중복된 데이터가 적다는 뜻이다. 중복된 데이터가 많은 테이블은, 중복된 데이터들을 찾은 후 일일이 실제 조건에 맞는 데이터를 찾아야 하기 때문에 인덱스의 효율이 좋지 않다.
비슷해보이는 인덱스, 다른 성능
```
SELECT * FROM dept_emp
WHERE dept_no = 'd002' AND emp_no >= 10114;
```
케이스 A: INDEX(dept_no, emp_no) 케이스 B: INDEX(emp_no, dept_no)

케이스A 인덱스는 'd002'이면서 emp_no >= 10114인 데이터를 찾고 d002가 아닐때까지 쭉 읽는다. 효율적인 인덱스다. 케이스B 인덱스는 emp_no >= 10114이며 dept_no = d002인 데이터를 찾고 그 이후 모든 데이터에서 d002를 체크한다.

케이스 A에서 emp_no는 검사해야할 데이터 자체를 줄여주지만, 케이스 B에서 dept_no는 범위를 줄여주는게 아니라 그냥 검사하는 용도다.

프라이머리 키 InnoDB에서는 기본적으로 프라이머리키를 기준으로 클러스터 인덱스를 생성한다. 또한 PK가 없으면 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 인덱스로 만들고, 내부적으로 유니크한 값을 가지는 칼럼을 추가해서라도 만든다. 클러스터 인덱스는 매우 좋은 성능을 낼 수 있으나 하나밖에 만들지 못한다. 그래서 프라이머리키를 잘 정하는게 중요하다. 그래서 책의 저자는 업무적으로 해당 레코드를 대표하는 칼럼을 프라이머리 키로 설정하라고 한다.

이는 김영한님이 가능한 자연키보다는 AUTO_INCREMENT를 쓰라는 것과 대치된다.

woowacourse-study / 2022-Real-MySQL

인덱스를 어떻게 써야 잘쓰는건지 (그리고 AUTO INCREMENT를 써야하는지에 대한 고민) #17

주제

선정 이유

해당 텍스트

관련 페이지