MySQL로 배우는 데이터베이스 개론과 실습 2판: Chapter 04 SQL 고급

🔥학습목표
    1. 내장 함수의 의미를 알아보고 자주 사용되는 내장 함수 몇 가지를 직접 실습해 본다.
    2. 부속질의의 의미와 종류를 알아보고 직접 실습해 본다.
    3. 뷰의 의미를 알아보고 뷰를 직접 생성 · 수정 · 삭제해 본다. 
    4. 데이터베이스의 저장 구조와 인덱스 간 관계, 인덱스의 구조를 이해한다.
    5. MySQL 인덱스 종류를 알아보고 인덱스를 직접 생성 · 수정 · 삭제해 본다.

PREVIEW

3장에서 SQL 기초 문법에 대해 알아보았다. SQL 기초 문법은 기본적인 데이터베이스 운영에 충분히 사용할 수 있으나 업무가 복잡해지고 처리해야 할 자료가 많아지면 기초 문법이 제공하는 기능만으로는 한계가 있다. 이 장에서는 복잡한 질의를 해결할 때 사용하는 SQL 고급 문법에 대해 알아본다. 먼저 내장 함수built-in function에 대해 알아보고, 3장에서 배운 부속질의를 SQL 문 위치에 따라 세분화해 알아본다. 그리고 일종의 가상 테이블인 뷰와 데이터를 쉽고 빠르게 찾기 위해 만든 인덱스에 대해서도 알아본다.

 

01 내장함수, NULL

SQL 내장 함수

SQL 내장 함수는 상수나 속성 이름을 입력값으로 받아 단일 값을 결과로 반환한다. 모든 내장 함수는 사용될 때 유효한 입력값을 받아야 한다. 예를 들어 수학 함수의 입력값은 정수 또는 실수여야 한다. 만약 선언에 위배된 값이 입력되면 질의는 실행을 중지하고 에러 메시지를 출력한다. SQL 내장 함수는 다음과 같이 SELECT 절과 WHERE 절, UPDATE 절 등에서 모두 사용할 수 있다.

숫자 함수

SQL 문에서는 수학의 기본적인 사칙 연산자(+, -, *, /)와 나머지(%) 연산자 기호를 그대로 사용한다. 그러나 실제 수학에서는 사칙 연산자 외에도 여러 연산자가 있다. MySQL은 이러한 연산자 중 사용 빈도가 높은 것을 내장 함수 형태로 제공한다.

문자 함수

날짜/시간 함수

 

datetime 주요 인자

NULL 값 처리

NULL은 '없다', '존재하지 않다'라는 의미로, 데이터 값이 입력되지 않으면 DBMS는 NULL을 특수한 값으로 저장한다. 이 값은 공백, 0 등과는 다르다. 집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠진다. 따라서 해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되며, COUNT 함수의 결과는 0이다.

 

 NULL 값을 확인하는 방법 – IS NULL, IS NOT NULL

• NULL 값을 찾을 때는 ‘=’ 연산자가 아닌 ‘IS NULL’을 사용
• NULL이 아닌 값을 찾을 때는 ‘＜＞’ 연산자가 아닌 ‘IS NOT NULL’을 사용

NVL 함수

- NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력

- NVL(속성, 값) (-> 속성 값이 NULL이면 '값'으로 대치)

 

02 부속질의

부속질의(subquery)는 하나의 SQL 문 안에 다른 SQL 문이 중첩된 질의를 말한다. 부속질의는 주로 메인 쿼리의 조건에 따라 서브쿼리의 결과로 가져와서 메인 쿼리에서 사용하는 용도로 활용된다. 부속질의를 사용하여 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공하는 등의 작업을 수행할 수 있다. 

두 테이블을 연관실킬 때 조인을 선택할지 부속질의를 선택할지 여부는 데이터의 형태와 양에 따라 달라진다. 일반적으로 데이터가 대량일 경우 데이터를 모두 합쳐 연산하는 조인보다 필요한 데이터만 찾아서 공급해주는 부속질의의 성능이 더 좋다.

중첩질의 - WHERE 부속질의

중첩질의(nested query)는 WHERE 절에서 사용되는 부속질의를 말한다. WHERE 절은 보통 데이터를 선택하는 조건 혹은 술어(predicate)와 같이 사용된다. 그래서 중첩질의를 술어 부속질의(predicate subquery)라고도 부른다.

중첩질의는 주질의에 사용된 자료 집합 조건을 WHERE 절에 서술한다. 주질의의 자료 집합에서 한 행씩 가져와 부속질의를 수행하며, 연산 결과에 따라 WHERE 절의 조건이 참인지 거짓인지 확인하여 참일 경우 주질의의 해당 행을 출력한다.

 

비교연산자

비교 연산자 사용 시 부속질의가 반드시 단일 행, 단일 열을 반환해야 하며, 아닐 경우 질의 처리할 수 없다. 처리 과정을 보면, 주질의의 대상 열 값과 부속질의의 결과 값을 비교 연산자에 적용하여 참이면 주질의의 해당 열을 출력한다.

IN, NOT IN(집합 연산자)

IN 연산자는 주질의의 속성값이 부속질의에서 제공한 결과 집합에 있는지 확인하는 역할을 한다. IN 연산자에서 사용 가능한 부속질의는 그 결과로 다중 행, 다중 열을 반환할 수 있다. 주질의는 WHERE 절에 사용되는 속성값을 부속질의의 결과 집합과 비교해 하나라도 있으면 참이 된다. NOT IN 연산자에서는 이와 반대로 값이 존재하지 않으면 참이 된다.

 

ALL, SOME/ANY(한정 연산자)

ALL, SOME(ANY) 연산자는 비교 연산자와 함께 사용된다. ALL은 '모든', SOME과 ANY는 '어떠한(최소 하나라도)'을 뜻한다. SOME은 ISO 표준 연산자다. 

ALL, SOME(ANY) 연산자의 구문 구조는 다음과 같다. 비교 연산자를 중심으로 왼쪽에는 슼라라 값이나 열 이름 등이 위치하고 오른쪽에는 부속질의문이 위치한다. ALL이나 SOME은 부속질의의 대상 범위를 지정하는 역할을 한다.

스칼라 부속질의(scalar subquery)

스칼라 부속질의(scalar subquery)는 SELECT 절에서 사용되는 부속질의로, 부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 변환한다. 스칼라 값이란 벡터 값에 대응되는 말로 단일 값을 의미한다. 만약 결과 값이 다중 행이거나 다중 열이라면 DBMS는 그중 어떠한 행과 열을 출력해야 하는지 알 수 없어 에러를 출력한다. 또 결과가 없는 경우에는 NULL 값을 출력한다. 스칼라 부속질의는 원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에 사용할 수 있으며, 일반적으로 SELECT 문과 UPDATE SET 절에 사용된다. 주질의와 부속질의의 관계는 상관/비상관 모두 가능하다.

 

인라인 뷰 - FROM 부속질의

인라인 뷰(inline view)는 FROM 절에서 사용되는 부속질의를 말한다. 다음 절에서 설명하겠지만, 뷰(view)는 기존 테이블로부터 일시적으로 만들어진 가상의 테이블을 말한다.

SQL 문의 FROM 절에는 테이블 이름이 들어가는데, 여기에 테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있다. 부속질의의 결과 값은 다중 행, 다중 열이어도 상관없다. 단만 가상의 테이블인 뷰 형태로 제공되기 때문에 상관 부속질의로 사용될 수는 없다. 상관 부속질의란 주질의의 특정 컬럼 값을 부속질의가 상속받아 사용하는 형태다.

 

03 뷰

뷰(view)는 하나 이상의 테이블을 합하여 만든 가상의 테이블이다. 

뷰의 장점은 다음과 같다.

1. 편리성 / 재사용성
   - 여러 테이블에서 데이터를 가져와 하나의 테이블로 정의함으로써 질의 작성이 간단해진다.
   - 미리 작성된 질의를 뷰로 정의해 두면 질의 재사용할 수 있다.
2.  보안성
   - 원본 테이블에서 보안이 필요한 속성을 제외하고 새로운 테이블을 정의하여 사용자에게 제공한다.
3. 논리적 데이터 독립성
   - 뷰를 정의하여 사용하게 하면 개념스키마에 정의된 테이블 구조가 변경되어도 응용 프로그램이 변경되지 않는다.

뷰의 특징은 다음과 같다.

1. 원본 데이터 값에 따라 같이 변한다.
2. 독립적인 인덱스 생성이 어렵다.
3. 삽입, 삭제, 갱신 연산에 많은 제약이 따른다.

뷰의 생성

CREATE VIEW 뷰이름 [(열이름 [,...n])] 
AS SELECT 문

 

뷰의 수정

CREATE OR REPLACE VIEW 뷰이름 [(열이름 [,...n])]
AS SELECT 문

뷰의 삭제

DROP VIEW 뷰이름 [,...n];

04⭐️ 인덱스

DBMS는 사용자가 만든 테이블을 저장 장치에 저장하고 필요에 따라 검색하여 결과를 보여준다. 그렇다면 DBMS는 데이터를 어떻게 저장하고 검색할까? 이 절에서는 DBMS에서 테이블이 저장되는 물리적인 구조와 함께 대부분의 관계 모델 데이터베이스가 사용하는 인덱스 index의 기본 구조인 B-tree에 대해 알아본다. 또한 간단한 실습으로 MySQL에서 사용 가능한 인덱스의 종류도 알아본다.

[그림 4-7]은 DBMS의 질의 처리 과정을 나타낸 것이다. DBMS는 워드프로세서와 마찬가지로 운영체제에서 실행되는 응용 프로그램의 일종이다. 그림을 보면 SQL 작업 도구(tool) 이를 통해 SQL 문을 작성하여 실행하면 DBMS에 의해 처리 방법이 결정되고, 운영체제를 통해 각 장치에 명령이 내려져 작업이 처리된다. 저장 작업도 마찬가지 과정으로 진행되어 최종적으로 운영체제의 파일 시스템에 종속적인 데이터베이스 파일로 저장된다.

이 과정에서 주의 깊게 살펴볼 점은 실제 데이터가 저장되는 곳이 보조기억장치라는 사실이다. 보조기억장치로는 일반적으로 하드디스크, SSD, USB 메모리 등이 있는데, 그중 하드디스크를 가장 많이 사용한다. 하드디스크는 원형의 플레이트plate로 구성되어 있다. 논리적으로 보면, 플레이트는 트랙으로 나뉘며, 트랙은 다시 몇 개의 섹터로 나뉜다. 원형의 플레이트는 초당 빠른 속도로 회전하고, 회전하는 플레이트에 하드디스크의 액세스 암(arm)과 헤더(header)가 접근하여 원하는 섹터에서 데이터를 가져온다. 하드디스크에 저장된 데이터를 읽어오는 데 걸리는 시간은 모터motor에 의해서 분당 회전하는 속도(RPM, Revolutions Per Minute), 데이터를 읽을 때 액세스 암이 이동하는 시간(latency time), 주기억장치로 읽어오는 시간(transfer time)에 영향을 받는다.

이와 같은 디스크의 입출력 시간을 액세스 시간(access time)이라고 한다. 액세스 시간은 데이터의 저장 및 읽기에 많은 영향을 끼친다. 액세스 시간은 다음과 같은 식으로 표현할 수 있다.

DBMS가 하드디스크에 데이터를 저장하고 읽어올 때, 속도 문제가 발생할 수밖에 없다. 컴퓨터 시스템에서 처리되는 연산 속도는 빠르지만, 디스크의 액세스 속도는 상대적으로 느리기 때문이다. 디스크의 액세스 속도는 주기억장치보다 100배 이상 느리다.
이러한 속도 문제를 줄이기 위해 DBMS는 주기억장치에 사용하는 공간 중 일부를 버퍼 풀(Buffer Pool Memory)로 만들어 사용한다. DB는 버퍼에 자주 사용하는 데이터를 저장해 두며 LRU(Least Recently Used) 알고리즘을 이용하여 사용 빈도가 높은 데이터 위주로 저장하고 관리한다.

LRU(Least Recently Used) 알고리즘은 기억장소를 관리하는 알고리즘으로, 최근(Recently)에 가장 적게 사용된(Least Used) 순서대로 기억장소에서 할당을 제외하는 방법이다. 데이터 검색 시 DBMS는 버퍼 풀에 저장된 데이터를 우선 읽어들인 후 작업을 진행한다.

 

인덱스와 B-tree

인덱스index(색인)란 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조다. 도서관에서 책을 찾을 때 서지 목록을 보고 책의 위치를 찾는 것처럼 데이터베이스에서도 인덱스를 사용하여 원하는 데이터를 빠르게 검색할 수 있다. 데이터베이스에서 인덱스는 원하는 데이터를 빨리 찾기 위해 투플의 키 값에 대한 물리적 위치를 기록해 둔 자료구조를 말한다. 일반적인 RDBMS의 인덱스는 대부분 B-tree 구조로 되어 있다. 여기서는 먼저 B-tree의 기본 구조를 알아보고 실제 인덱스 구조에 대해 살펴본다.

B-tree(Balanced-tree)는 데이터의 검색 시간을 단축하기 위한 자료구조로, 바이어(Rudolf Bayer)가 고안하였다. [그림 4-10]은 B-tree의 구조를 보여준다. B-tree는 루트 노드(root node), 내부 노드(internal node), 리프 노드(leaf node)로 구성되며, 리프 노드가 모두 같은 레벨에 존재하는 균형(balanced) 트리다.

B-tree의 각 노드는 키 값과 포인터를 가진다. 키 값은 오름치순으로 저상되어 있으며, 키값 좌우에 있는 포인터는 각각 키 값보다 작은 값과 큰 값을 가진 다음 노드를 가리킨다. 따라서 키 값을 비교하여 다음 단계의 노드를 쉽게 찾을 수 있다. 트리에서 가장 상위에 있는 노드를 루트 노드라고 한다. 모든 검색은 루트 노드에서부터 시작하여 내부 노드를 지나 리프 노드까지 내려가면서 이루어진다. B-tree는 키 값이 새로 추가되거나 삭제될 경우 동적으로 노드를 분할하거나 통합하여 항상 균형 상태를 유지한다.

[그림 4-11]과 같이 최대 세 개의 자식을 가지는 B-tree에서 3이라는 값을 찾는다고 가정해보자. 우선 루트 노드의 값 4와 찾는 값 3을 비교한다. 3은 4보다 작으므로 왼쪽 포인터가 지시하는 노드로 이동한다. 이동한 노드에서 다시 2와 3을 비교하여 오른쪽 포인터가 지시하는 노드로 이동한다. 3이 저장된 노드와 비교하여 같은 값을 발견하면 검색을 중지한다. 삽입 작업의 경우 루트 노드에서부터 시작하여 값을 비교하면서 삽입될 위치를 찾아가고, 노드에 저장할 공간이 없으면 새로운 노드로 분할하여 값을 이동시킨 후 삽입한다. 이때 값이 새로 입력되어도 트리는 항상 균형 상태를 유지한다.

B-tree에서 검색은 루트 노드에서부터 값을 비교하여 중간 단계인 내부 노드에서 해당 노드를 찾고, 이런 단계를 거쳐 최종적으로 마지막 레벨인 리프 노드에 도달한다. 리프 노드에는 해당 데이터의 저장 위치에 대응하는 rowid(RID, Row IDentify, 테이블의 행에 대한 논리적 위치)가 있어 찾고자 하는 행을 바로 찾을 수 있다.
B-tree는 데이터를 검색할 때 특유의 트리 구조를 이용하기 때문에 한 번 검색할 때마다 검색 대상이 줄어 접근 시간이 적게 걸린다. 100만 개의 투플을 가진 데이터도 디스크 블록을 서너 번 읽으면 찾을 수 있다. 이러한 특징 때문에 주요 DBMS에서는 인덱스의 기본 구조로 B-tree를 많이 사용한다. 하지만 데이터의 변경이나 추가가 잦을 경우 B-tree의 모양을 유지하기 위해 노드의 분할 및 이동이 자주 발생한다는 문제가 있다.

 

인덱스의 특징을 정리하면 다음과 같다.

• 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성한다.
• 빠른 검색과 함께 효율적인 레코드 접근이 가능하다.
• 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지한다.
• 저장된 값들은 테이블의 부분집합이 된다.
• 일반적으로 B-tree 형태의 구조를 가진다.
• 데이터에서 수정, 삭제 등의 변경이 발생하면 인덱스를 재구성해야 한다.

MySQL 인덱스

MySQL의 인덱스는 클러스터드 인덱스(clustered index)와 보조 인덱스(secondary index)로 나뉘며, 모두 B-tree 인덱스를 기본으로 한다. 클러스터 인덱스는 연속된 키 값의 레코드를 묶어서 같은 블록에 저장하는 방법으로 테이블당 하나만 생성할 수 있으며, B-tree 인덱스의 리프 노드에서 페이지의 주소 값 대신 테이블의 열 자체가 저장되는 형태다. 보조 인덱스의 경우 속성의 값으로 B-tree 인덱스를 구성하며 리프 노드의 각 행은 해당 페이지의 주소 값을 저장한다.

 

클러스터 인덱스

[그림 4-14]는 Book 테이블에 클러스터 인덱스를 생성한 경우다. 클러스터 인덱스는 인덱스의 리프 노드들이 정렬된 상태로 저장된 테이블 자체가 된다. 예를 들어 도서번호 8번을 찾는다고 하자. 이 경우, 루트 노드의 키 8을 비교해서 3번째 행의 7보다 크고 4번째 행의 10보다는 작다는 것을 발견하게 된다. 이에 따라 페이지 3으로 이동하여 1행부터 순서대로 확인하여 데이터를 찾는다.

이러한 클러스터 인덱스는 테이블당 하나만 생성할 수 있으며, 키 값에 의한 동등 및 범위(BETWEEN) 검색 모두에 유리하다. 테이블의 데이터가 키 값에 따라 정렬된 형태로 저장되어 특정 값을 쉽게 찾을 수 있으며, 범위로 검색한다고 해도 이미 정렬되어 있으므로 손쉽게 검색할 수 있다. 또한 인덱스 페이지가 단순해져 인덱스 저장 시 차지하는 공간도 작다. 클러스터 인덱스는 테이블 생성 시 기본키(PK)를 생성하면 자동으로 생성된다.

보조 인덱스

[그림 4-13]의 Book 테이블을 보자. 서점에서 실제 업무가 이루어지면 테이블의 데이터가 갱신 또는 삭제되면서 최종적으로 그림과 같이 무작위로 저장될 것이다. 이렇게 저장된 데이터의 양이 적다면 검색 시간도 적게 걸리겠지만 수천, 수억 건 이상이라면 원하는 자료를 찾는 데 많은 시간이 들 것이다. 이 그림에서 인덱스는 마당서점 Book 테이블의 bookid 속성으로 생성한 경우다. 이렇게 생성된 인덱스의 리프 노드는 실제 데이터 값이 아닌 테이블상의 데이터 위치를 지정하는 rowid를 저장한다. rowid는 <Block 번호-Block 내의 Row가 위치한 순번>의 형태로 구성되어 있어 실제 테이블의 자료가 아무리 무작위로 저장되어 있더라도 쉽게 찾을 수 있다.

[그림 4-13]에서 도서번호 8번을 찾는 경우를 살며보자. 루트 노드를 종해 리프 노드의 두번께 페이지를 찾아간 후, 찾고자 하는 값인 8의 rowid가 3-1임을 확인한다. 3-1은 세 번째 Block의 첫 번째 행(row)를 의미하므로 테이블의 세 번째 Block에 접근하여 원하는 데이터를 가져온다.

이러한 인택스는 테이블당 여러 개를 만들 수 있다. 테이블의 컬럼(속성) 하나만 대상으로 하여 단일 킬럼 인데스를 만들 수도 있고, 여러 개의 컬럼을 복합적으로 결합하여 사용하는 인덱스도 만들 수 있다. Book 테이블의 경우 출판사 이름 컬럼과 가격 컬림을 대상으로 인덱스를 생성하여 '어떠한 출판사의 얼마짜리' 책 찾기 등과 같은 업무에 빠르게 대응할 수도 있다.
한 가지 더 알아둘 점이 있다. 인덱스를 사용하여 검색할 경우 인덱스와 데이터 파일의 구조적인 특징으로 인해 특정 키 값을 찾는 검색에서는 성능을 보장할 수 있으나, 범위 검색은 데이터가 저장된 Block 값들의 저장 순서가 일정치 않을 수 있어서 원하는 만큼의 빠른 검색 효과를 보장할 수 없다는 점이다. 그러므로 인덱스 구성 시 자료의 저장 및 질의 형태에 따라 신중하게 생성해야 한다.

 

MySQL 인덱스

클러스터 인텍스와 보조 인덱스는 보통 같이 사용된다. 예를 들어 Book 테이블에서 bookid를 클러스터 인덱스로, bookname을 보조 인데스로 사용하여 bookid와 bookname 모두 빠른 검색을 필요로 하는 경우를 살펴보자. MySQL은 bookid를 검색할 경우 클러스터 인덱스를 이용하고, bookname을 검색할 경우 보조 인덱스를 이용하여 bookid를 찾은 다음 다시 bookid에 대한 클러스터 인덱스를 사용한다. 이렇게 하는 이유는 클러스터 인덱스로 저장된 데이터의 순서를 가능한 한 유지하면서 데이터의 삽입과 삭제에 대한 인덱스 관리 비용을 줄이기 위해서다.
[그림 4-14]는 bookname으로 '야구를 부탁해" 책을 찾는 과정을 나타낸다. bookname을 사용해 보조 인덱스를 생성하였으므로 보조 인덱스에서 root node →index page 1 →클러스터 인덱스 root page → BOOK 테이블 검색 순으로 찾아나간다.

클러스터 인덱스

• 기본적인 인덱스로 테이블 생성 시 기본키를 지정하면 기본키에 대하여 클러스터 인덱스를 생성한다.
• 기본키를 지정하지 않으면 먼저 나오는 UNIQUE 속성에 대하여 클러스터 인덱스를 생성한다.
• 기본키나 UNIQUE 속성이 없는 테이블은 MySQL이 자체 생성한 행번호(row ID)를 이용하여 클러스터 인덱스를 생성한다.

보조 인덱스

• 클러스터 인덱스가 아닌 모든 인덱스는 보조 인덱스이며 보조 인덱스의 각 레코드는 보조 인덱스 속성과 기본키 속성값을 갖고 있다.
• 보조 인덱스를 검색하여 기본키 속성값을 찾은 다음 클러스터 민덱스로 가서 해당 레코드를 찾는다.

인덱스의 생성

인덱스는 데이터를 빨리 검색하기 위해 사용한다. 하지만 인덱스를 생성했다고 해서 데이터 검색이 무조건 빨라지는 것은 아니다. 데이터양이 아주 적거나 데이터 값이 및 종류 안 되어 선택도가 높을 경우 인덱스 없이 검색하는 편이 더 빠를 수 있다. 여기서 선택도(selectivity)란 '1/서로 다른 값의 개수'을 말하는 것으로, 예를 들어 100개의 행을 가진 테이블에 값이 (남, 여) 두 가지라면 선택도가 높다고 할 수 있다.

의미 없이 인덱스를 생성하면 검색 속도가 더 느려지고 저장 공간만 낭비하게 된다. 따라서 인덱스를 생성하기 전에 다음 고려사항을 충분히 살펴봐야 한다.

• 인덱스는 WHERE 절에 자주 사용되는 속성이어야 한다.
• 인덱스는 조인에 자주 사용되는 속성이어야 한다.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다(테이블당 4~5개 정도 권장).
• 속성이 가공되는 경우에는 사용하지 않는다.
• 속성의 선택도가 낮을 때 유리하다(속성의 모든 값이 다른 경우)

MySQL의 인덱스 구성과 인덱스 생성 시 고려사항에 대해 간략하게나마 살펴보았으니 이제인덱스를 생성해 보자. 인덱스를 생성할 때는 CREATE INDEX 문을 이용하며, 문법은 다음과 같다.

CREATE [UNIQUE] INDEX [인덱스이름]
ON 테이블이름 (컬럼 [ASC | DESC] [{, 컬럼[ASC | DESC]} ...])[;]

위 문법에서 [UNIQUE]는 테이블의 속성값에 대하여 중복이 없는 유일한 인덱스를 생성하는 것을 말한다. [ASC | DESC]는 컬럼 값의 정렬 방식을 의미한다.

인덱스의 재구성과 삭제

인덱스의 재구성은 ANALYZE TABLE 명령을 사용하여 수행한다. B-tree 인덱스는 테이터의 수정 · 삭제 · 삽입이 잦으면 노드의 갱신이 주기적으로 일어나 단편화(ragmentation) 현상이 나타난다. 단편화란 삭제된 레코드의 인덱스 값 자리가 비게 되는 상태들 말하는테, 이는 검색 시 성능 저하로 이어진다. 이럴 경우 ANALYZE 문법을 통해 인데스을 다시 생성해 준다.

ANALYZE TABLE 테이블이름;

하나의 테이블에 인덱스가 많으면 데이터베이스 성능에 좋지 않은 영향을 미친다. 그러므로 사용하지 않는 인덱스는 삭제해야 한다. 인덱스의 삭제는 DROP INDEX 명령을 사용하여 수행한다.