[MySQL] 인덱스(색인) index 생성, 추가, 삭제

인덱스(색인) index

: 테이블의 조회 속도를 높여주는 자료구조

• 추가/삭제/변경이 없어 거의 변동이 없는 칼럼에 이용 
• 단점 : update, insert, delete 사용시 조회 속도 저하

인덱스 index 특징

• primary키 = 자동 인덱스 설정
• MYSQL INDEX(MYI) 파일에 저장된다
• 인덱스는 하나 또는 여러개의 칼럼에 대해 설정이 가능
  • (여러칼럼을 묶어 하나의 인덱스로도 설정이 가능)
• where 절 뒤에 사용될 경우, order by,  group by에 사용되는 경우 -> index도 영향을 미침

인덱스 index를 사용할 수 없는 경우

1.  복수의 키에 대해 order by하는 경우
2.  연속하지 않는 칼럼에 대해 order by하는 경우
3.  group by 칼럼, order by 칼럼이 다를 경우
4. order by 칼럼을 변형 시켜 사용할 경우

다중 칼럼 인덱스 사용

: 단일 칼럼 인덱스보다 더 비효율적

- insert, updete, delete에서 더욱 비효율적

- 잘 사용 하지 않음

- key 인덱스명(칼럼, 칼럼) 

 

---

 

인덱스 index 생성

   1.  테이블 생성하며 인덱스 설정

create table 테이블명 (
칼럼 속성1,
칼럼 속성2,
...
primary key(칼럼),
key 인덱스명(칼럼) ) ; // 여러 인덱스인 경우 계속 key 인덱스명(칼럼)로 추가
                                   // key 인덱스명(칼럼, 칼럼) = 다중 칼럼 인덱스

create table table(
id int not null,
name varchar(10),
address varchar(20),
primary ket(id),
key idx_name(name));

   2.  기존 테이블에 인덱스 설정 추가

1. 인덱스 생성 하는 방법
create index 인덱스명 on 테이블명(칼럼명) ; 

2. 테이블에 추가하는 방법
alter table 테이블명 add index 인덱스명 (칼럼명) ;

mysql> create index idx_name on student(st_name);

 

 

인덱스 보기 

show index from 테이블명; 

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY  |            1 | st_num      | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_name |            1 | st_name     | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

 

 

인덱스 삭제 

alter table 테이블명 drop index 인덱스명;

mysql> alter table student drop index idx_name;