인덱스(Index).MySQL

인덱스(index)란?

• 책의 <찾아보기> 의 개념과 비슷
• 데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구

 

---

인덱스의 장단점

장점

• 검색속도가 무척 빨라 질수 있습니다 (항상 그런것은 아님)
• 쿼리의 부하가 줄어들어 시스템 전체의 성능을 향상 합니다.

단점

• 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간을 필요로 합니다.
  • 대략 데이터베이스 크기의 10% 정도의 추가 공간이 필요합니다.
• 처음 인덱스 생성하는데 시간이 소요됩니다
• 데이터의 변경 작업(Insert, Update, Delete)이 자주 일어나는 경우 성능이 나빠질 수 있습니다.

 

---

인덱스의 종류

• 클러스터형 인덱스 (Clustered Index)
  • '영어 사전'과 같은 책
  • 테이블 당 한 개만 지정 가능
  • 행 데이터를 인덱스로 지정한 열에 맞춰 자동 정렬
• 보조 인덱스 (Secondary Index)
  
  • 책 뒤에 <찾아보기>가 있는 일반 책
  • 테이블당 여러 개도 생성 가능

---

use sqldb;

select * from usertbl;

-- 기본키 변경 : 1. buytbl 외래키 설정 제거, 2. usertbl 기본키 제거, 3. usertbl name컬럼을 기본키로 지정
alter table usertbl
	drop primary key;
    
alter table buytbl
	drop foreign key buytbl_ibfk_1;

alter table usertbl
	add constraint pk_name primary key(name);
    
select * from usertbl;

-- 테이블 생성 시에 제약조건 Primary key 또는 unique를 사용하면 자동으로 인덱스가 생성됩니다.
use sqldb;

create table tbl1
(
	a int primary key,
    b int,
    c int
);

show index from tbl1;

create table tbl2
(
	a int primary key,
    b int unique,
    c int unique,
    d int
);
show index from tbl2;

-- unique index : 중복 값이 없는 인덱스 : 클러스터 인덱스 primary key, unique
-- nonunique index : 중복 값이 있는 인덱스 : non_unique - 1 : unique x, unique - 0 : unique.

create table tbl3
(
	a int unique,
    b int unique,
    c int unique,
    d int
);

show index from tbl3;

-- unique not null
create table tbl4
(
	a int unique not null,  -- 클러스터 인덱스 : 자동정렬
    b int unique,			-- 보조 인덱스 : 정렬 X
    c int unique,
    d int
);

show index from tbl4;

create table tbl5
(
	a int unique not null,  -- 클러스터 인덱스 : 자동정렬
    b int unique,			-- 보조 인덱스 : 정렬 X
    c int unique,
    d int primary key		-- 클러스터 인덱스 : 자동정렬
);

show index from tbl5;
-- 기본 값이 먼저 우선적으로 자동 정렬

-- primary key로 지정한 열은 클러스터형 인덱스가 생성
-- unique not null로 지정한 열은 클러스터형 인덱스가 생성
-- unique 또는 unique null 로 지정한 열은 보조 인덱스가 생성됩니다.
-- primary key와 unique not null이 있으면 primary key로 지정한 열을 우선해서 클러스터형 인덱스가 생성됩니다.
-- primary key로 지정한 열로 데이터가 오름차순 정렬 됩니다.

create database if not exists testdb;

use testdb;

drop table if exists usertbl;

create table usertbl
(
	userid char(8) not null primary key,
    name varchar(10) not null,
    addr nchar(8) not null
);

alter table usertbl
	add `birthYear` int not null after name;
    
insert into usertbl values ('LSG', '이승기', 1987, '서울');
insert into usertbl values ('KBS', '김범수', 1979, '경남');
insert into usertbl values ('KKH', '김경호', 1971, '전남');
insert into usertbl values ('JYP', '조용필', 1950, '경기');
insert into usertbl values ('SSK', '성시경', 1979, '서울');

show index from usertbl;