[SQL] 인덱스, 스토어드 프로시저
Chapter 06 인덱스
1. 개념
2. 내부 작동
3. 실제 사용
1. 개념
1.1 인덱스의 개념
1.2 인덱스의 종류
1.1 인덱스의 개념
인덱스의 장점과 단점
①인덱스의 장점
- SELECT문으로 검색하는 속도가 매우 빨라짐
- →컴퓨터의 부담 감소→전체 시스템 성능 향상
②인덱스의 단점
- 데이터베이스에 추가 공간 필요(테이블 크기의 약 10% 정도)
- 처음에 인덱스를 만들 때 시간이 오래 걸릴 수 있음
- 인덱스가 있는 컬럼의 데이터 변경작업이 잦은 경우 오히려 성능이 나빠질 수 있음
1.2 인덱스의 종류
1.2.1 클러스터형 인덱스
1)PK 지정시 자동으로 클러스터형 인덱스 생성
인덱스 정보 확인: SHOW INDEX
create table table1(
col1 int primary key,
col2 int,
col3 int
);
show index from table1;
Key_name이 PRIMARY로 표시되어있다.
Non_unique: 중복 허용(0 = false)
2)클러스터형 인덱스가 있는 컬럼은 자동정렬
(1)기본키가 없는 상태
create table member(
mem_id char(8),
mem_name varchar(10),
mem_number int,
addr char(2)
);
insert into member values('TWC', '트와이스', 9, '서울');
insert into member values('BLK', '블랙핑크', 4, '경남');
insert into member values('WMN', '여자친구', 6, '경기');
insert into member values('OMY', '오마이걸', 7, '서울');
select * from member;
(2)기본키를 지정한 상태
alter table member
add constraint
primary key(mem_id);
select * from member;
기본키인 mem_id를 기준으로 알파벳순으로 정렬된다.
(3) 기본키 변경
alter table member drop primary key; -- 기본키 제거
alter table member
add constraint
primary key(mem_name);
select * from member;
insert into member values('GRL', '소녀시대', 8, '서울');
select * from member;
1.2.2 보조 인덱스
컬럼을 고유 키로 지정하면 인덱스가 자동으로 생성된다. 고유 키 컬럼이 여럿 있을 수 있듯이 보조 인덱스도 여럿 있을 수 있다.
create table table2(
col1 int primary key,
col2 int unique,
col3 int unique
);
show index from table2;
2) 보조 인덱스는 정렬 기능 x
create table member(
mem_id char(8),
mem_name varchar(10),
mem_number int,
addr char(2)
);
insert into member values('TWC', '트와이스', 9, '서울');
insert into member values('BLK', '블랙핑크', 4, '경남');
insert into member values('WMN', '여자친구', 6, '경기');
insert into member values('OMY', '오마이걸', 7, '서울');
select * from member;
alter table member
add constraint
unique(mem_id);
select * from member;unique 지정 전이나 후나 select의 결과는 같다.
보조 인덱스는 책의 뒷장에 색인 페이지가 추가되는 것과 같다. 본 테이블은 정렬되지 않는다.
2. 내부 작동
균형 트리(Balanced tree, B-tree): 범용적으로 사용되는 데이터의 구조
뿌리(루트): 최상단
줄기(중간)
잎(리프)
2.1 내부 작동 원리
노드: 균형 트리 구조에서 데이터가 저장되는 공간. 개념적인 설명에서 사용하는 용어
페이지: MySQL에서 노드를 부르는 말
2.2 인덱스의 구조
2.2.1 클러스터 인덱스의 경우
클러스터 인덱스는 정렬이 되어 있고, 그림과 같이 루트-중간-리프(데이터 양에 따라 더 복잡화)의 구조로 이루어져있다. 데이터 중 MMM을 찾고자 할 경우 먼저 루트 페이지로 간다. MMM은 LLL 다음으로 나오기 때문에 세 번째 리프 페이지로 이동한다. 그곳에서 MMM을 찾을 수 있다.
페이지 분할
인덱스가 있으면 데이터 변경 작업(INSERT, UPDATE, DELETE)를 실행할 때 성능이 나빠진다. 따라서 클러스터 인덱스가 만들어지는 PK는 변경이 자주 일어나지 않는 값으로 지정해야 한다.
III를 삽입할 경우, JJJ가 원래 있던 자리에서 밀려난다.
여기에 GGG를 삽입하면 III와 JJJ가 또 밀려나게 되고, 페이지에 자리가 부족해진다. 이럴 경우 새로운 페이지가 만들어진다. 이것을 페이지 분할이라고 한다.
또한 새로운 리프 페이지가 만들어졌기 때문에 루트 페이지에도 등록되어야 한다.
이런 식으로 삽입이 계속되다보면 루트 페이지도 분할된다. 그렇게 되면 루트 페이지는 더 이상 루트 페이지가 아니게 되고, 중간 페이지가 되어 상위에 새로운 루트 페이지가 생기게 된다. 때문에 클러스터 인덱스가 있는 컬럼의 INSERT는 속도가 느려진다.
2.2.2 인덱스의 구조
클러스터형 인덱스는 데이터 페이지도 인덱스에 포함된다. 보조 인덱스는 데이터 페이지에 변경이 일어나지 않고, 별도의 장소에 인덱스 페이지가 생성된다. 그리고 데이터의 위치가 '페이지 번호+#위치'로 기록된다.
클러스터형 인덱스에서 데이터를 검색하면 ①루트 페이지를 찾고, ②해당 데이터가 있는 리프 페이지를 찾아가 읽고, ③검색할 컬럼을 찾는다. 보조 인덱스에서 데이터를 검색하면 인덱스 페이지의 루트 페이지, 리프 페이지를 읽고 데이터 페이지를 읽는다. 읽어야 할 페이지가 더 적기 때문에 클러스터형 인덱스로 검색하면 속도가 더 빠르다.
3. 실제 사용
PRIMARY KEY와 UNIQUE에서 자동으로 생성되는 인덱스 외에 추가적으로 인덱스를 생성할 일은 잘 없다. 데이터베이스에 대한 이해도가 아주 높아야 인덱스를 수동으로 만들었을 때 효과적으로 활용할 수 있기 때문이다.
인덱스 생성
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명 (컬럼명) [ASC|DESC]
인덱스 제거
DROP INDEX 인덱스명 ON 테이블명기본 키와 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거되지 않는다.
인덱스의 효과적인 사용법 정리
1. 인덱스는 열 단위에 생성된다.
2. WHERE절에서 사용되는 열에 인덱스를 만들어야 한다.
3. WHERE절에 사용되더라도 자주 사용해야 한다.
4. 데이터의 중복이 적어야 한다.
5. 클러스터형 인덱스는 테이블당 하나만 생성된다.
6. 사용하지 않는 인덱스는 제거한다.
Chapter 7 스토어드 프로시저
1. 사용 방법
2. 스토어드 함수와 커서
3. 트리거
1. 사용 방법
스토어드 프로시저
쿼리문의 집합(단위작업에 사용)
1.1 스토어드 프로시저 형식
1.2 출력 매개변수의 활용
1.1 스토어드 프로시저 형식
delimiter $$
create procedure 스토어드프로시저명(IN|OUT 매개변수)
begin
-- 코드
end $$
delimiter ;
파라미터 사용★
delimiter $$
create procedure user_proc1(in username varchar(10))
begin
select * from member where mem_name = username;
end $$
delimiter ;
call user_proc1('에이핑크');
파라미터 복수 사용
delimiter $$
create procedure user_proc2(
in usernumber int,
in userheight int)
begin
select * from member
where mem_number > usernumber and height > userheight;
end $$
delimiter ;
call user_proc2(6, 165);
1.2 출력 매개변수의 활용★
IN: 매개변수 입력
OUT: 리턴 변수 입력
delimiter $$
create procedure user_proc3(
in txtvalue char(10),
out outvalue int
)
begin
insert into notable values(null, txtvalue);
select max(id) into outvalue from notable;
end $$
delimiter ;
-- 프로시저에서 사용할 테이블이 존재하지 않아도 프로시저는 생성됨
create table if not exists notable(
id int auto_increment primary key,
txt char(10)
);
call user_proc3('테스트1', @myvalue); -- 리턴값(outvalue)를 myvalue에 담음
select concat('입력된 id값: ', @myvalue);
1.3 SQL 프로그래밍의 활용
if-else문
delimiter $$
create procedure ifelse_proc(in memname varchar(10))
begin
declare debutyear int;
select year(debut_date) into debutyear from member where mem_name = memname;
if(debutyear >= 2015) then
select '신인 가수' as '메시지';
else
select '고참 가수' as '메시지';
end if;
end $$
delimiter ;
call ifelse_proc('오마이걸');
2. 스토어드 함수와 커서
스토어드 함수
2.1 스토어드 함수의 개념과 형식
스토어드 함수와 스토어드 프로시저의 차이점
①스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식 지정, 본문 안에서는 RETURN문으로 값 반환
②스토어드 함수의 매개변수는 모두 입력 매개변수, IN 없음
③스토어드 프로시저는 CALL로 호출, 스토어드 함수는 SELECT문 안에서 호출
④스토어드 프로시저 안에서는 SELECT문 사용 가능, 스토어드 함수 안에서는 사용 불가능
스토어드 함수의 형식
delimiter $$
create function 스토어드함수명(매개변수)
RETURNS 반환형식
begin
-- 코드
RETURN 반환값;
end $$
delimiter ;
2.2 스토어드 함수의 사용
스토어드 함수 생성 권한 허용(MySQL에서 한 번만 설정)
set global log_bin_trust_function_creators = 1;
숫자 2개의 합계를 계산하는 스토어드 함수
delimiter $$
create function sumfunc(number1 int, number2 int)
returns int
begin
return number1 + number2;
end$$
delimiter ;
select sumfunc(100, 200) as '합계';
데뷔 연도를 입력하면 활동 기간을 출력하는 함수
delimiter $$
create function calcyearfunc(dyear int) -- 매개변수 받음
returns int -- 리턴할 값
begin
declare runyear int; -- 변수 선언
set runyear = year(curdate()) - dyear; -- 계산해서 변수에 값 입력
return runyear; -- 값 리턴
end $$
delimiter ;
select calcyearfunc(2010) as '활동 햇수';
함수 리턴값 변수에 저장하기
select calcyearfunc(2007) into @debut2007;
select calcyearfunc(2013) into @debut2013;
select @debut2007-@debut2013 as '2007과 2013 차이'; -- 6
함수 제거
drop function calcyearfunc;
커서
2.3 커서의 기본 개념
2.4 커서의 단계별 실습
2.5 커서의 통합 코드
2.3 커서의 기본 개념
테이블에서 한 행씩 처리하기 위한 방식
첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리(≒iterator, Enumeration)
2.4 커서의 단계별 실습
1)사용할 변수 준비
declare memnumber int;
declare cnt int default 0;
declare totnumber int default 0;
declare endofrow boolean default false; -- 행의 끝 파악
2)커서 선언
declare membercursor cursor for
select mem_number from member;
3)반복 조건 선언
declare continue handler
FOR NOT FOUND SET endofrow = TRUE;
4)커서 열기
OPEN membercursor;
5)행 반복
cursor_loop: LOOP
-- 반복할 코드
END LOOP cursor_loop
LEAVE: 반복문 빠져나감
IF endofrow THEN
LEAVE cursor_loop;
END IF;
FETCH: 한 행씩 읽어옴
cursor_loop: LOOP
FETCH membercursor INTO memnumber;
IF endofrow THEN
LEAVE cursor_loop;
END IF;
SET cnt = cnt + 1;
SET totnumber = totnumber + memnumber;
END LOOP cursor_loop;
6)커서 닫기
CLOSE membercursor;
2.5 커서의 통합 코드
drop procedure if exists cursor_proc;
delimiter $$
create procedure cursor_proc()
begin
-- 변수 선언
declare memnumber int;
declare cnt int default 0;
declare totnumber int default 0;
declare endofrow boolean default false;
-- 커서 선언
declare membercursor cursor for
select mem_number from member;
-- 반복 조건 준비
declare continue handler
FOR NOT FOUND SET endofrow = TRUE;
-- 커서 열기
OPEN membercursor;
cursor_loop: LOOP
FETCH membercursor INTO memnumber;
IF endofrow THEN
LEAVE cursor_loop;
END IF;
SET cnt = cnt + 1;
SET totnumber = totnumber + memnumber;
END LOOP cursor_loop;
SELECT(totnumber/cnt) AS '회원의 평균 인원 수';
CLOSE membercursor;
end $$
delimiter ;
call cursor_proc();
3. 트리거
3.1. 트리거 기본
3.2. 트리거 활용★
3.1. 트리거 기본
테이블에 DML문의 이벤트가 발생하면 실행되는 코드
예시 코드
1)테이블 생성
create table if not exists trigger_table (id int, txt varchar(10));
insert into trigger_table values(1, '레드벨벳');
insert into trigger_table values(2, '잇지');
insert into trigger_table values(3, '블랙핑크');
2)트리거 부착: delimiter, create trigger
delimiter $$
create trigger mytrigger
after delete
on trigger_table
for each row
begin
set @msg = '가수 그룹 삭제됨' ;
end $$
delimiter ;AFTER DELETE: DELETE가 발생하면 그 후에
ON ~: 부착할 테이블
FOR EACH ROW: 각 행마다. 트리거 생성시 써야 함
set @msg = '';
insert into trigger_table values(4, '마마무');
select @msg;
update trigger_table set txt = '블핑' where id = 3;
select @msg;
delete from trigger_table where id = 4;
select @msg;DELETE 후에만 트리거가 실행된 것을 확인할 수 있다.
3.2. 트리거 활용★
트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용할 수 있다.
1) 테이블과 백업 테이블 생성
create table singer (select mem_id, mem_name, mem_number, addr from member);
create table backup_singer(
mem_id char(8) not null,
mem_name varchar(10) not null,
mem_number int not null,
addr char(2) not null,
modtype char(2), -- 변경된 타입(수정/삭제)
moddate date, -- 변경된 날짜
moduser varchar(30) -- 변경한 사용자
);
2)트리거 작성
-- update trigger
drop trigger if exists singer_updateTrg;
delimiter $$
create trigger singer_updateTrg
after update
on singer
for each row
begin
insert into backup_singer values(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_USER());
end $$
delimiter ;
-- delete trigger
drop trigger if exists singer_deleteTrg;
delimiter $$
create trigger singer_deleteTrg
after delete
on singer
for each row
begin
insert into backup_singer values(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '삭제', CURDATE(), CURRENT_USER());
end $$
delimiter ;
3)DML문 실행
update singer set addr = '영국' where mem_id = 'BLK';
delete from singer where mem_number >= 7;
select * from backup_singer;
3.3 임시테이블
NEW/OLD: DML 작업이 수행되면 임시로 사용되는 시스템 테이블
INSERT가 실행되면 새 값은 테이블에 들어가기 전에 NEW 테이블에 잠깐 들어가 있는다. DELETE가 실행될 때는 예전 값이 OLD 테이블에 들어가있다가 삭제된다. 때문에 트리거에서 NEW.컬럼 또는 OLD.컬럼 식으로 값에 접근할 수 있다. UPDATE는 NEW와 OLD를 둘 다 사용한다.