MySQL 테이블에서 원하는 데이터 쉽고 빠르게 찾기(INDEX) feat.Workbench

🟨 목 차 🟨

1. INDEX 란 ?

    1-1. INDEX

    1-2. INDEX 정보보기

    1-3. 중복값을 허용하지 않는 UNIQUE INDEX 생성

    1-4. INDEX 정렬

    1-5. INDEX 추가

           1-5-1. 기본 INDEX 추가

          1-5-2. UNIQUE INDEX 추가

          1-5-3. FULLTEXT INDEX 추가

     1-6. INDEX 삭제

           1-6-1. ALTER문 INDEX 삭제

           1-6-2. DROP문 INDEX 삭제

      1-7. 효과적인 INDEX 설계방법

 

 

 

저번 시간에는 쿼리의 각 부분을 명확하게 구분할 수 있게 해주는 다른 쿼리 내부에 포함되어 있는 SELECT 문인 서브쿼리에 대해서 배웠습니다.

이번에는 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용되는 INDEX(인덱스)에 대해 알아봅시다 :)

---

◼️ 1. INDEX 란 ? ◼️

INDEX(인덱스)는 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용된다. 이러한 인덱스는 자주 사용되는 필드 값으로 만들어진 원본 테이블의 사본이라고 생각 할 수 있다.

 

MySQL은 데이터를 검색할 때 첫번째 필드부터 차례대로 테이블 전체를 검색한다.

==> 따라서 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어나게 된다.

 

하지만 INDEX(인덱스)를 사용하게 되면 테이블 전체를 읽지 않아도 되므로, 검색과 질의에 대한 처리가 빠르게 이루어진다.

[ 이러한 인덱스는 사용자가 직접 접근할 수 없으며, 검색과 질의에 대한 처리에서만 사용된다. ]

 

인덱스가 설정된 필드 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정이 되어야 한다.

==> 따라서 인덱스가 설정된 테이블의 처리 속도가 느려질 수 있다.

그러므로 인덱스는 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 더 좋다(고정된 값이 있는 경우)

---

◼️ 1-1. INDEX 생성

CREATE 문을 사용하여 인덱스를 생성 할 수 있다.

 

[문법]

CREATE INDEX 인덱스이름

ON 테이블이름 (필드이름1, 필드이름2, ...)

 

==> 쉼표(,)를 이용하여 여러 필드를 가지는 인덱스를 생성할 수도 있다.

 

 

 

[Reservation 테이블의 Name 필드에 Nameldx라는 인덱스를 설정하는 예제]

CREATE INDEX NameIdx

On Reservation (Name);

실행결과

---

◼️ 1-2. INDEX 정보보기

 

[문법]

SHOW INDEX

FROM 테이블이름

 

[반화되는 인덱스 정보의 필드 값]

• Table : 테이블의 이름을 표시함.
• Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함.
• Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.
• Seq_in_index : 인덱스에서의 해당 필드의 순서를 표시함.
• Column_name : 해당 필드의 이름을 표시함.
• Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시함.
• Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시함.
• Sub_part : 인덱스 접두어를 표시함.
• Packed : 키가 압축되는(packed) 방법을 표시함.
• Null : 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 ''를 표시함.
• Index_type : 인덱스에 사용되는 메소드(method)를 표시함.
• Comment : 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시함.
• Index_comment : 인덱스에 관한 모든 기타 정보를 표시함.

---

◼️ 1-3. 중복값을 허용하지 않는 UNIQUE INDEX 생성

UNIQUE INDEX는 중복값을 허용하지 않는 인덱스이다.

 

[문법]

CREATE UNIQUE INDEX 인덱스이름

ON 테이블이름 (필드이름1, 필드이름2, ...)

==> 쉼표(,)를 이용하여 여러 필드를 가지는 UNQUE INDEX를 설정할 수도 있다.

 

 

[Reservation 테이블의 ID 필드에 IDldx라는 UNQUE INDEX를 설정하는 예제]

CREATE UNIQUE INDEX IdIdx

On Reservation (ID);

실행결과

---

◼️ 1-4. INDEX 정렬

INDEX를 생성할 때 인덱스에 포함되는 필드의 정렬 방식을 설정할 수 있다.

[ DESC 키워드를 사용하면 내림차순 정렬, ASC 키워드를 사용하면 오른차순 정렬 ]

 

[문법]

1. CREATE INDEX 인덱스이름

   ON 테이블이름 (필드이름 DESC)

2. CREATE INDEX 인덱스이름

   ON 테이블이름 (필드이름 ASC)

 

[Reservation 테이블의 Name 필드(내림차순)에 NameDescldx라는 인덱스를 설정하는 예제]

CREATE INDEX NameDescIdx

On Reservation (Name DESC);

실행결과

 

---

◼️ 1-5. INDEX 추가

ALTER문을 사용하여 테이블에 인덱스를 추가할 수 있다.

 

추가할 수 있는 인덱스 타입

• 기본 인덱스
• UNIQUE INDEX
• FULLTEXT INDEX

◼️ 1-5-1. 기본 INDEX 추가

기본 인덱스에서 필드의 값은 같은 값이 여러번 저장될 수 있고, NULL 값을 가질  수도 있다.

 

[문법]

ALTER TABLE 테이블이름

ADD INDEX 인덱스이름 (필드이름)

 

[Reservation 테이블의 Name 필드에 기본 인덱스를 추가하는 예제]

ALTER TABLE Reservation

ADD INDEX NameIdx (Name);

실행결과

 

 

◼️ 1-5-2. UNIQUE INDEX 추가

UNIQUE INDEX에서 필드의 값은 중복될 수 없으나, NULL 값을 가질 수 있다.

 

[문법]

ALTER TABLE 테이블이름

ADD UNIQUE 인덱스이름 (필드이름)

 

[Reservation 테이블의 ID 필드에 UNIQUE INDEX를 추가하는 예제]

ALTER TABLE Reservation

ADD UNIQUE IdIdx (ID);

실행결과

 

◼️ 1-5-3. FULLTEXT INDEX 추가

FULLTEXT INDEX는 일반적인 인덱스와는 달리 매우 빠르게 테이블의 모든 텍스트 필드를 검색한다.

FULLTEXT INDEX는 검색 엔진과 유사한 방법으로 자연어를 이용하여 데이터를 검색할 수 있도록 모든 데이터의 문자열 단어를 저장한다.

 

[문법]

ALTER TABLE 테이블이름

ADD FULLTEXT INDEX이름 (필드이름)

 

[Reservation 테이블의 Name 필드에 FULLTEXT INDEX를 추가하는 예제]

ALTER TABLE Reservation

ADD FULLTEXT NameFtIdx (Name);

실행결과

==> 위의 사진처럼 FULLTEXT 인덱스의 인덱스 타입은 언제나 FULLTEXT 이다.

 

---

◼️ 1-6. INDEX 삭제

ALTER, DROP문을 사용하여 테이블에 추가된 인덱스를 삭제할 수 있다.

 

◼️ 1-6-1. ALTER문 INDEX 삭제

[문법]

ALTER TABLE 테이블이름

DROP INDEX 인덱스이름

 

[Reservation 테이블에서 Nameldx라는 이름의 인덱스를 삭제하는 예제]

ALTER TABLE Reservation

DROP INDEX NameIdx;

 

 

◼️ 1-6-2. DROP문 INDEX 삭제

[문법]

DROP INDEX 인덱스이름

ON 테이블이름

 

[Reservation 테이블에서 Idldx라는 이름의 인덱스를 삭제하는 예제]

DROP INDEX IdIdx

ON Reservation;

---

◼️ 1-7. 효과적인 INDEX 설계방법

• 조회시 자주 사용하는 컬럼 위주 설계
• 무조건 많이 설정하지 않는다. (한 테이블당 3~5개가 적당 목적에 따라 상이)
• 고유한 값 위주로 설계
• 카디널리티가 높을 수록 좋다 (= 한 컬럼이 갖고 있는 중복의 정도가 낮을 수록 좋다.)
• INDEX 키의 크기는 되도록 작게 설계
• PK(기본키), JOIN의 연결고리가 되는 컬럼
• 단일 인덱스 여러 개 보다 다중 컬럼 INDEX 생성 고려
• UPDATE가 빈번하지 않은 컬럼
• JOIN시 자주 사용하는 컬럼
• INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)

---

 

 

 

 

출처

 

---

 

[MySQL] 인덱스(INDEX) 정리(효과적인 INDEX 설계)