인덱스란
인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위해 데이터와 데이터의 위치를 포함한 자료구조이다.
// 인덱스 생성
CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);
ALTER TABLE 테이블명 ADD INDEX 인덱스명 (컬럼명);
// 인덱스 삭제 (수정 시에는 DROP 후 재생성)
ALTER TABLE 테이블명 DROP INDEX 인덱스명;인덱스를 쓰면 좋은 점과 안 좋은 점
- 좋은 점: 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있고, 전반적인 시스템의 부하를 줄일 수 있다.
- 안 좋은 점: 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하고, 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
INSERT는 새로운 데이터에 대한 인덱스를 추가해야하고, DELETE는 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행해야하고, UPDATE는 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가해야한다.
인덱스를 효율적으로 설정하기 위해 고려해야 하는 사항
1) 한 컬럼이 갖고 있는 값의 중복 정도가 낮을 수록 좋다.
2) INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼일 수록 좋다.
3) JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼일 수록 좋다.
4) 인덱스 키의 크기는 되도록 작게 설계되어야 성능에 유리하다.
5) 분포도가 좁은 범위, 기본키, 조인과 연결 고리가 되는 컬럼을 인덱스로 구성한다.
6) 단일 인덱스 여러 개 보다 다중 컬럼 인덱스를 구성하는 게 좋다.
7) 되도록 동등 비교를 사용하는 것이 좋다.
8) 인덱스를 많이 생성하면 INSERT/DELETE/UPDATE의 성능 저하 원인이 될 수 있다.
9) 인덱스 생성 시 가장 효율적인 자료형은 정수형 타입이다. 가변적인 크기와 정규화 할 수 없는 데이터는 인덱스 생성 시 비효율적으로 동작한다.
인덱스를 구현한 자료구조
1) 해시 테이블
해시 테이블은 (Key, Value)로 데이터를 저장하는 자료구조 중 하나로 빠른 데이터 검색이 필요할 때 유용하지만(O(1)), 등호(=) 연산에만 특화되어 있다. 해시 함수는 값이 1이라도 달라지면 완전히 다른 해시 값을 생성하는데, 이러한 특성에 의해 부등호 연산(>, <)이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않다. B+Tree가 주로 사용된다.
2) B+Tree
DB의 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조이다. 리프노드만 인덱스와 함께 데이터를 가지고 있고, 나머지 노드들은 데이터를 위한 인덱스만을 갖는다. 부등호를 이용한 순차 검색 연산이 자주 발생될 수 있어서 B-Tree의 리프노드들을 LinkedList로 연결하여 순차검색을 용이하게 하는 등 B-Tree를 인덱스에 맞게 최적화하였다.
인덱스 타입
- index type
-유니크하지 않은 중복값을 허용한다. 제약을 줄 순 없고 접근에만 사용한다. - unique type
-유니크한 값을 참조하는 타입이다. 행마다 중복 값은 허용되지 않으나 null 값은 허용된다.
-데이터에 빠르게 접근할 수 있고, 제약을 줄 수도 있다. - primary type
-인덱스 이름이 primary인 것과 하나의 테이블에 하나의 primary 타입의 인덱스가 존재하기를 강제한다.
-중복 값도 허용하지 않고, null도 허용하지 않는다. - fulltext type
-match 또는 against 절에 사용되며 한 컬럼에 많은 형태의 데이터가 담겨 있을 때 효율적으로 데이터를 조회하기 위해 사용하는 타입이다. (ex) "등산복을 연관 상품 추천을 통해 구매했다.")
-검색 서비스를 구현할 때 활용한다.
select *from product
where match(product_description) against ("편한 최고의 등산복!!!") 다중 컬럼 인덱스
두 개 이상의 필드를 조합하여 생성한 인덱스이다. MYSQL은 하나의 인덱스에 최대 15개 컬럼으로 구성할 수 있다. 단일 컬럼 인덱스보다 INSERT/DELETE/UPDATE가 비효율적이기 때문에 변경이 없는 컬럼 값을 선정하는 것이 중요하다.
SELECT *FROM User_Info
WHERE name = 'hong' and address = 'seoul'name, address가 단일 인덱스로 각각 있는 경우에는 두 컬럼 중 빠른 쪽을 먼저 검색하고 그 다음에 다른 컬럼을 검색한다.
다중 컬럼 인덱스의 경우는 인덱스 저장 시 name, address를 같이 저장하기 때문에 원하는 값을 바로 찾을 수 있다.
SELECT *FROM User_Info
WHERE address = 'seoul'이 쿼리에서는 name이 없으므로 다중 컬럼 인덱스의 경우는 인덱스의 효과를 보지 못한다. 다중 컬럼 인덱스는 제일 왼쪽 컬럼이 where 절에서 사용되어야 한다.
*참고 자료
https://mangkyu.tistory.com/96
https://yurimkoo.github.io/db/2020/03/14/db-index.html
https://code-factory.tistory.com/24
