[참고] MySQL INDEX

🐕‍🦺 인덱스

• 데이터베이스의 동작 속도를 높혀준다. 
• 일부 스타트업은 백앤드 개발자가 DB를 관리하는 경우가 있다.

🐕‍🦺 인덱스 종류

• 클러스터형 인덱스

테이블에 기본키를  지정할 때 기본키 값에 따라서 해당 테이블을 잘 검색할 수 있도록 클러스터형 인덱스가 자동으로 구성된다. 이때 PRIMARY KEY값에 맞춰서 데이터 저장 순서를 재구성한다. 예를 들어서 영어사전을 보면 알파벳 순서대로 정렬하듯이, 이렇게 하면 데이터를 찾는 속도가 빨라진다. 

즉 클러스터형 인덱스는 기본키에 따라서 최대 하나가 테이블당 만들어진다. ☞ 추가/ 삭제 불가능

 

• 보조인덱스

직접 테이블마다 우리가 추가/ 삭제할 수 있다. 이 보조 인덱스는 물리적인 데이터의 저장 위치를 바꾸는 것은 아니다. 일반 책의 목차 같은 것이고, 이를 통해서 내가 원하는 데이터를 빠르게 찾아간다. ☞ 추가/ 삭제 가능

 

인덱스는 어떻게 만들어지는가??

SHOW INDEX FROM 테이블명

🐕‍🦺 테이블 변경

alter table은 보통 테이블의 구조를 변경하는데 많이 쓰인다. 테이블 구조를 변경하다보니 그 구조 중 하나가 테이블의 제약을 추가하는 그런 것도 일종의 구조 변경이라고 볼 수 있고, 그 제약을 추가하는 것도 alter table로 가능하다.

 

ALTER TABLE 테이블명 ADD CONSTRAINT 제약명 UNIQUE(제약명)

 

CONSTRAINT 뒤에 제약이 들어가는데, 각 제약들 마다 이름을 부여해야한다.

UNIQUE를 하여 중복을 방지하고 보조 인덱스가 만들어진다.

🐕‍🦺 보조 인덱스 생성

생성방법

CREATE INDEX  생성할인덱스명 ON 테이블명(컬럼)

ex) userTbl에 name을 가지고 보조 인덱스를 만들고 싶다???

CREATE INDEX idx_name ON userTbl(name);

 

근데 보조인덱스 왜 만들어??
내가 작성한 프로그램이나 내가 데이터 분석을 하는데 해당 name이라는 컬럼을 가지고 분석을 하는 경우가 너무 많다. 이 name을 보조 인덱스로 만들면 검색 속도가 빨라진다. 그래서 내가 검색하거나 많이 쓰는 컬럼에 대해서 보조 인덱스를 만들면 기존보다 빨라진다.

인덱스를 추가하는 것도 테이블 구조 변경이기 때문에 

ALTER TABLE 테이블명 ADD INDEX 생성할인덱스명(인덱스);

→ ALTER TABLE userTbl ADD INDEX idx_addr(addr);

alter 명령어로 보조 인덱스 만들기

 

이렇게 하면 addr을 가지고 보조 인덱스를 만들 수 있다.

정리 1. 인덱스 보기 SHOW INDEX FROM 테이블명 2. 보조인덱스 만들기 ALTER TABLE 테이블명 ADD CONSTRAINT 제약사항명 UNIQUE(제약명);  # 중복방지 CREATE INDEX 생성할인덱스명 ON 테이블명(컬럼); ALTER TABLE 테이블명 ADD INDEX 생성할인덱스명(인덱스);

 

🐕‍🦺 연습문제

문제1. groupName 으로 인덱스 추가하고 확인해보기

문제2. prodName 으로 인덱스 추가하고 확인해보기

 

🐕‍🦺 테이블도 생성하면서 인덱스 만들기

하지만 unique라고 쓰기 위해서라면 지정한 해당 컬럼에 unique라는 제약 조건이 걸려있어야 한다. 그럴때만 uniqe index라고 쓸 수 있다.

하지만 unique제약조건이 없는 컬럼에 대해서 보조 인덱스를 만들고 싶으면 그냥 INDEX 생성할 인덱스명(컬럼명)만 작성하면 된다.

 

※ 보통은 unique라는 제약조건이 있어야지 unique인덱스를 쓸 수 있는 것이 아니라, 어떤 식으로든 중복된 값을 허용하지 않으면 그때는 unique인덱스를 쓸 수 있다. 그래서 unique라는 제약조건이 꼭 명시적으로 표기되지 않아도 중복되는 값이 없는 컬럼이면 해당 문법을 쓸 수 있다.

 

🐕‍🦺 인덱스 삭제

ALTER TABLE 테이블명 DROP INDEX 인덱스명;

Mysql에서는 해당 독립적인 명령을 제공하지 않는다. 

prodname삭제

idx_groupname삭제

정리 SHOW INDEX FROM 테이블명;    # 인덱스보여줘 ALTER TABLE 테이블명 ADD CONSTRAINT 제약명 UNIQUE(제약명)  # 중복값이 없는 컬럼을 대상으로 하여 중복을 방지하고 보조 인덱스를 생성한다. CREATE INDEX 생성할 인덱스명 ON 테이블명(컬럼); ALTER TABLE 테이블 ADD INDEX 생성할인덱스명(컬럼);