[MySQL] 인덱스 생성, 조회

내장바구니 | 주문배송조회 | 내적립금

ㆍ메인페이지

ㆍ전체상품

6만원 이상 무료배송

주문하시는
총상품금액의 합계가
6만원 이상일 경우
택배비가 무료입니다.

현재위치 : 고객게시판

앨범방 사이트에 대해서 이용방법, 상품문의, 주문및배송, 사진꾸미기등 궁금한점은 여기에 질문하세요.
질문하시면 운영자가 댓글로 신속히 답변 드리겠습니다. 기타 사진, 이미지, 사진상품 관련글도 괜찮습니다.

[MySQL] 인덱스 생성, 조회

작성자: 어라 작성일: 2009-07-07 10:47 조회: 175184 댓글: 1

인덱스 만들기

1. 추가하여 만들기

    CREATE INDEX <인덱스명> ON <테이블명> ( 칼럼명1, 칼럼명2, ... );

2. 테이블 생성시 만들기

    끝에....

    INDEX <인덱스명> ( 칼럼명1, 칼럼명2 )

    UNIQUE INDEX <인덱스명> ( 칼럼명 )  --> 항상 유일해야 함.

3. 이렇게도 생성한다

    ALTER TABLE <테이블명> ADD INDEX <인덱스명> ( 칼럼명1, 칼럼명2, ... );

4. 인덱스 보기

    SHOW INDEX FROM <테이블명>;

5. 인덱스 삭제

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

=======================================================================================================

인덱스는 테이블 상에서 MUL 로 표시된다.

Mysql 서버에서 인덱스를 생성하는 방법은 3가지가 있다.

처음으로 테이블을 만들며서 생성하는 방법이 두 가지이고, 테이블을 만든 후에 사용하다가 인덱스

를 추가 할 수 있는 방법이 있다. 인덱스 파일은 "테이블명.MYI"파일로 데이터베이스 디렉토리 아래 저장된다.

1.CREATE TABLE 문에서 생성

CREATE TABLE 문에서 인덱스를 생성하는 방법으로 가장 많이 사용된다. 다음과 같이 사용 가능하고 , 인덱스명은 생략 가능하며, 임의로 idx1으로 준것이다. aaaidx로 주어도 아무 상관이 없다.

---------------------------------------------------------------------------------------

INDEX 인덱스명 (칼럼명) OR

INDEX (칼럼명) OR

KEY 인덱스명 (칼럼명)

---------------------------------------------------------------------------------------

인덱스를 만들고, desc 명령을 사용했을 때, 인덱스를 준 칼럼에 MUL 이라고 보이면 인덱스가 생성된 것이다. 임의로 만든 index1 테이블은 "주키-학번-이름"으로 구성된 테이블 이다.

BLOB과 TEXT 데이터형은 인덱스를 줄 수 없으며, 인덱스를 사용하려면 Full-text Search 를 사용해야 한다.

뒤에 자세한 설명을 참고하기 바란다.

----------------------------------------------------------------------------------------

mysql> create table index1(
    -> code int not null auto_increment primary key,
    -> hakbun int not null,
    -> name char(30) not null,
    -> index idx1(hakbun)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc index1;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| code   | int(11)  |      | PRI | NULL    | auto_increment |
| hakbun | int(11)  |      | MUL | 0       |                |
| name   | char(30) |      |     |         |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
----------------------------------------------------------------------------------------

생성한 테이블에 인덱스의 목록을 보고 싶으면 "SHOW KEYS FROM 테이블 명"명령을 사용하면 볼 수 있다. Key_name에 PRIMARY 와 idx1 으로 적힌 것을 볼 수 있을 것이다. 주키(PRIMARY)는 인덱스가 기본으로 추가된다.

mysql> show keys from index1;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| index1 |          0 | PRIMARY  |            1 | code        | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| index1 |          1 | idx1     |            1 | hakbun      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

2. CREATE INDEX 문에서 생성

테이블을 만든 후에 바로 인덱스를 추가 할 수 있는데, 이경우 CREATE INDEX 문을 사용한다.

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON tbl_name(col_name[(length)],...)

처음에 CREATE TABLE 문에서 인덱스를 주지 않고 index2 테이블을 생성한 후에, CREATE INDEX 문을 사용해서 인덱스를 추가하는 것이다.

mysql> create table index2(
    -> code int not null auto_increment primary key,
    -> hakbun int not null,
    -> name char(30) not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc index2
    -> ;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| code   | int(11)  |      | PRI | NULL    | auto_increment |
| hakbun | int(11)  |      |     | 0       |                |
| name   | char(30) |      |     |         |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> CREATE INDEX idx1 ON index2(hakbun);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc index2;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| code   | int(11)  |      | PRI | NULL    | auto_increment |
| hakbun | int(11)  |      | MUL | 0       |                |
| name   | char(30) |      |     |         |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

3. 인덱스 제거

인덱스는 한 개의 테이블에서 2개 칼럼에 주는것이 적당하다. 인덱스는 데이터베이스 용량의 3분의 1 정도를 차지하기 때문에 너무 많은 인덱스를 생성하면 용량이 커져서 퍼포먼스가 떨어질 수도 있게 된다. 인덱스를 제거하려면 DROP INDEX 문이나 ALTER TABLE 문을 사용하면 된다.

* 관련 댓글 한말씀 부탁합니다.

INDEX는 하나의 컬럼에서 특정한 값을 찾을때(즉, select작업을 할때) 주로 사용된다. MySQL에서는 어떠한 type의 컬럼이라도 인덱스될 수 있다. 하나의 table은 16개까지의 index를 가질 수 있다. index의 최대 길이는 256byte지만 이 값은 MySQL을 컴파일 할 때 변경할 수 있다. CHAR type이나 VARCHAR type의 경우에는 컬럼의 앞쪽의 일부만 인덱스화 할 수 있는데 이는 컬럼의 모든 부분을 인덱스 하는 것 보다 더 낫다. 그리고 BLOB나 TEXT type의 컬럼의 경우에는 모든 컬럼이 인덱스화 될 수 없으며 앞의 일부분만을 인덱스화 하여야 한다.

INDEX를 사용하기 위한 형식과 실제 table을 생성시킬때의 예제는 다음과 같다.

KEY index_name (column_name(length))

예제1)
Create table tablename(
        id varchar(14) primary key,
        name varchar(10) not null,
        occupation varchar(10) not null,
        address varchar(100),
        key idx_name(name),
        key idx_occupation(occupation)
)

MySQL에서는 multiple-column index도 가능하다. 하나의 index에는 최고 15개의 컬럼을 가질 수 있다.

예제2)
Create table tablename(
        id varchar(14) primary key,
        name varchar(10) not null,
        occupation varchar(10) not null,
        address varchar(100),
        key idx_name(name, occupation)
)
여기서 multiple-column index를 사용할 때 주의하여야 한다. 실제로 multiple-column index가 사용될 때, 어떠한 방식으로 사용되는지 살펴볼 필요가 있다. 다음 예제를 살펴보자.

mysql> SELECT * FROM tablename WHERE name='dinosfx' AND occupation='scan';

여기서 name 컬럼과 occupation이 위의 예제1과 같이 두개의 컬럼 각각에 index가 설정되어 있다고 하자. 이 경우 MySQL에서는 name 컬럼이나 occupation 컬럼을 살펴보고 index로 살펴보아야 할 행의 수가 적은쪽을 선택하여 검색을 한다. 만약 occupation에서 검색해야 할 행의 수가 적다면 MySQL은 occupation 컬럼을 먼저 검색한다는 이야기이다.

그리고 예제2에서처럼 두개의 컬럼이 multiple-column으로 index가 설정되어 있다고 가정하자. 이 경우 MySQL은 원하는 값을 바로 찾아내게 된다.

이렇게 multiple-column index를 사용하면 검색을 손쉽게 할 수 있다. 그러나 multiple-column index를 사용할 때에는 주의할 점이 있다. multiple-column index에서는 가장 왼쪽의 컬럼이 select의 where절에 사용되어야 한다는 것이다. 다음의 예제를 보자.
이 예제에서 index는 KEY index_name (column1, column2, column3) 과 같이 걸려 있다.

1) mysql> SELECT * FROM tablename WHERE column1 = value1
2) mysql> SELECT * FROM tablename WHERE column2 = value2;
3) mysql> SELECT * FROM tablename WHERE column2 = value2
                                                          and column3 = value3;

1)의 경우 index에서 제일 왼쪽에 설정되어 있는 column이므로 index를 사용하여 검색할 수 있다. 그러나 2)와 3)의 경우에는 column1이 검색 조건에 포함되지 않았으므로 index를 사용하지 못하고 검색이 이루어진다. 따라서 1), 2)와 3)의 세가지 경우에 대해서 모두 index를 사용하고자 한다면

key idx1(column1)
key idx2(column2, column3)

와 같이 index를 설정하여야 할 것이다.

MySQL에서는 다음과 같은 경우에 index를 사용하여 검색을 한다. (예제1 참조)

mysql> select * from tablename where name LIKE "dino%";
mysql> select * from tablename where name LIKE "di%sf%";

다음과 같은 경우에는 index가 설정이 되어 있더라도 index를 사용하지 못하고 검색을 하게 된다. (예제1 참조)

mysql> select * from tablename where name LIKE "%nos%";
mysql> select * from tablename where name LIKE id; // 다른 컬럼과의 비교

첫번째의 경우에는 wild card문자가 앞쪽에 있기 때문에 index를 사용하지 못한다. 그러므로 위의 예제에서처럼 검색어의 가운데나 끝에 wild card문자가 들어가야 한다. 두번째의 경우에는 like에서의 비교 대상이 변하지 않는 값이 아니므로(the LIKE value is not a constant.) index를 사용할 수 없다.

MySQL에서는 이 외에도 =, >, >=, <, <=, BETWEEN, LIKE 연산자를 이용하여 검색을 할 때 index를 사용한다. (물론 검색어의 첫머리에 wild card 문자가 포함되면 index를 사용하지 못한다.)

--------------------------------------------------------------------------------

간단하게나마 MySQL에서 index를 사용하여 검색 속도를 향상하는 방법을 살펴보았다. 그러나 실제로 index를 사용할 때 주의해야 할 점이 하나 있다. Index는 검색(select 작업)을 할때 큰 역할을 한다. (MySQL reference를 보면 1000개의 행에서 검색을 할때 index를 설정한 경우가 그렇지 않은 경우보다 적어도 100배 빠르다고 한다.) 하지만 빈번하게 insert나 update가 이루어지는 경우 index를 갱신하는 작업이 필요하므로 select할 때 줄인 시간만큼 insert나 update에서 더 시간이 걸릴 수도 있다. 그러므로 index의 사용은 table과 자료의 목적에 따라 적절히 사용되어야 할 것이다.

어라

2009-07-07 10:48

작성자: 비밀번호: (비밀번호는 숫자 4자리이며 본인댓글 삭제시 필요합니다.)

이용약관

개인정보취급방침