MySQL Chapter6-Chapter7[13-4]

                                                         인덱스

인덱스의 개념

인덱스는 데이터를 빠르게 찾을 수 있도록 도와주는 도구로써 실무에서는 현실적으로 인덱스 없이 데이터 베이스의 운영이 어렵습니다.

인덱스의 개념

책을 예로 들면 1000페이지가 넘는 책의 내용 중에 "UNIQUE"에 대해 찾아보고 싶다면 찾아보기란에는 ABC 또는 가나다 순으로 이미 정렬되어 있기 때문에 "U"부분을 살펴본다면 "UNIQUE"단어를 쉽게 찾을 수가 있습니다. 단어 옆에 본문의 페이지 번호가 적혀 있어서 원하는 내용으로 이동할 수 있습니다.

실무에서 운영하는 테이블에서는 인덱스의 사용 여부에 따라서 성능 차이가 날 수가 있습니다. 대용량의 테이블일 경우에는 더욱 그러하며, 이것이 인덱스를 사용하는 이유입니다.

*데이터를 찾을 때에 인덱스의 사용 여부에 따른 결과값의 차이는 없습니다. 즉, 시간이 많이 걸릴 뿐이지 책의 찾아보기가 없다고 글자를 못 찾는 것은 아닙니다. 

인덱스의 문제점
:인덱스를 사용시 주의할 점으로는 인덱스를 제대로 이해하지 못하고 남용할 경우입니다. 필요없는 인덱스를 만드는 바람에 데이터 베이스가 차지하는 공간만 더 늘어나고, 인덱스를 이용해서 데이터를 찾는 것이 전체 테이블을 찾는 것보다 느려집니다.

*데이터베이스에 인덱스를 생성해 놓아도, 인덱스를 사용해 검색하는 것이 빠를지 아니면 전체 테이블을 검색하는 것이 빠를지 MySQL이 알아서 판단을 합니다. 만약에 인덱스를 사용하지 않는다면 사용하지도 않는 찾아보기를 만드는 것이므로 쓸데없이 공간으 낭비한 셈입니다.

인덱스의 장점과 단점
:인덱스는 select 에서 즉각적인 효과를 내는 빠른 방법 중에 한 가지입니다. 즉, 적절한 인덱스를 생성하고 인덱스를 사용하는 SQL을 만든다면 기존보다 아주 빠른 응답 속도를 얻을 수가 있습니다. 

컴퓨터입장에서는 적은 처리량으로 요청한 결과를 빨리 얻을 수가 있으니 여유도 생기고 추가로 더 많은 일을 할 수 있게 됩니다. 결과적으로 전체 시스템의 성능이 향상되는 효과도 얻게 됩니다.

인덱스의 종류

자동으로 생성되는 인덱스
:인덱스는 테이블의 열 단위에 생성이 되고, 하나의 열에는 하나의 인덱스를 생성할 수가 있습니다.

-- index
-- 클러스터 형 인덱스
use market_db;
create table table1
(
	col1 int primary key, -- 기본 키로 지정.
    col2 int,
    col3 int
);
show index from table1;​

자동으로 정렬되는 클러스터형 인덱스
: 클러스터형 인덱스는 기본 키로 지정하면 자동 생성된다는 것을 알았는데, 클러스터형 인덱스가 생성이 된다면  어떤 열을 기본 키로 지정을 할 때 그 열을 기준으로 자동 정렬이 됩니다. 예를 들어서 영어 단어와 뜻을 열심히 필기한 노트를 영어 사전으로 만든다고 가정할 때 단어의 알파벳 순서로 정렬되어야 합니다.

use market_db;
drop table if exists buy, member;
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
    primary key(mem_id);
select * from member;​

-- mem_name을 지정하고, 소녀시대를 넣고 다시 정렬을 할 떄 들어가는 순서(정렬)
-- 기본 키 제거
alter table member drop primary key;
alter table member
	add constraint
    -- 클러스터형 인덱스 생성
    primary key(mem_name);
select * from member;

insert into member values('GRL', '소녀시대', 8, '서울');
-- name순서로 블랙핑크 다음으로 소녀시대가 온다.
select * from member;​

정렬되지 않는 인덱스
:데이터의 순서에 변화가 없습니다. 즉, 보조 인덱스를 생성을 한다 해도 데이터의 순서는 변경되지 않고 별도로 인덱스를 만드는 것입니다.

-- 정렬되지 않는 보조 인덱스(유니크)
drop table if exists member;
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);
-- mem_id가 정렬되지 않는 것을 알 수 있다.
select * from member;​

                                              스토어드 프로시저

스토어드 프로시저 

 

스토어드 프로시저 기본

스토어드 프로시저의 완전한 형식은 어렵게 느낄 수도 있지만, 실제로 사용하는 형식은 간단합니다. 기본 형식을 먼저 익히고 추가로 완전히 형식을 학습 하도록 하는 것이 좋습니다.

스토어드 프로시저의 개념과 형식
:스토어드 프로시저(저장 프로시저)란 mysql에서 제공하는 프로그래밍 기능입니다. mySQL 내부에서 사용할 때 적절한 프로그래밍 기능을 제공해줍니다.

또한 스토어드 프로시저는 쿼리문의 집합(단위작업)으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로도 사용을 합니다. 자주 사용하는 일반적인 쿼리를 반복하는 것보다는 스토어드 프로시저로 묶어 놓고, 필요할 때마다 간단히 호출만 하면 훨씬 편리하게 MySQL을 운영할 수가 있습니다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름( IN 또는 OUT 매개변수)
BEGIN

     이 부분에 SQL 프로그래밍을 코드로 작성

END $$
DELIMITER ;

데이터 준비

drop table member;
create table member(
   mem_id varchar(8) not null primary key,
    mem_name varchar(10) not null,
    mem_number int not null,
    addr varchar(2) not null,
    phone1 varchar(3),
    phone2 varchar(8),
    height int,
    debut_date date
);
ALTER TABLE member CHARSET=UTF8;
show create table member;

insert into member values('TWC','트와이스',9,'서울','02','11111111',167,'2015.10.19');
insert into member values('BLK','블랙핑크',4,'경남','055','22222222',163,'2016.08.08');
insert into member values('WMN','여자친구',6,'경기','031','33333333',166,'2015.01.15');
insert into member values('OMY','오마이걸',7,'서울','','',160,'2015.04.21');
insert into member values('GRL','소녀시대',8,'서울','02','44444444',168,'2007.08.02');
insert into member values('ITZ','잇지',5,'경남','','',167,'2019.02.12');
insert into member values('RED','레드벨벳',4,'경북','054','55555555',161,'2014.08.01');
insert into member values('APN','에이핑크',6,'경기','031','77777777',164,'2011.02.10');
insert into member values('SPC','우주소녀',13,'서울','02','88888888',162,'2016.02.25');
insert into member values('MMU','마마무',4,'전남','061','99999999',165,'2014.06.19');​

입력 매개변수의 활용

-- 스토어드 프로시저 
-- 입력 매개변수의 활용
use market_db;
drop procedure if exists user_proc1;
delimiter $$
create procedure user_proc1(in username varchar(10))
begin
	select * from member where mem_name = username;
end $$
delimiter ; 

call user_proc1('에이핑크');



drop procedure if exists user_proc2;
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);​

출력 매개변수 활용

-- 출력 매개변수의 활용
drop procedure if exists user_proc3;
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 ; 


desc notable;

create table if not exists notable
(
	id int auto_increment primary key,
    txt char(10)
);

desc notable;

call user_proc3 ('테스트1', @myvalue);
select concat('입력된 id 값 ==> ', @myvalue);

SQL프로그래밍의 활용

-- SQL 프로그래밍 활용
drop procedure if exists ifelse_proc;
delimiter $$
create procedure ifelse_proc
	(in memname char(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('오마이걸');

스토어드 함수와 커서

스토어드 함수는 MySQL 에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공합니다. 즉, MySQL이 제공하는 함수를 그대로 사용할 수 없는 경우가 발생을 한다면 직접 스토어드 함수를 작성해서 사용할 수가 있습니다.

스토어드 함수는 스토어드 프로시저와 모양이 비슷하지만, 세부적으로는 다릅니다. 특히 용도가 다르고, returns 예약어를 통해서 하나의 값을 반환해야 한다는 특징을 갖습니다. 

스토어드 함수

MySQL은 다양함 함수(sum,cast,concat,current_date 등)를 제공합니다. 하지만 사용자가 원하는 모든 함수를 제공하지 않기 때문에 사용자가 직접 만들어서 사용을 해야하는 경우도 존재합니다. 이렇게 만들어서 사용하는 함수를 스토어드 함수라고 부릅니다.

형식

delimiter $$
create function 스토어드_함수_이름(매개변수)
returns 반환형식 
begin

이 부분에 프로그래밍 코딩
return 반환값

end $$
delimiter ;

select 스토어드_함수_이름();

• returns문으로 반환할 값의 데이터 형식 지정. 본문 안에서는 return문으로 하나의 값을 반환.
• 스토어드 함수의 매개변수는 모두 입력 매개변수. in을 붙이지 않습니다.
• 스토어드 프로시저는 call로 호출하지만 스토어드 함수는 select문 안에서 호출이 됩니다.
• 스토어드 프로시저 안에서는 select를 사용할 수 있지만, 스토어드 함수 안에서는 select를 사용할 수가 없습니다.
• 스토어드 프로시저는 여러 SQL문이나 숫자 계산 등의 다양한 용도로 사용을 하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용합니다.

스토어드 함수 사용

-- 스토어드 함수의 사용

set global log_bin_trust_function_creators = 1;

use market_db;
drop function if exists sumfunc;
delimiter $$
create function sumfunc
	(number1 int, number2 int)
    returns int
begin
	return number1 + number2;
end $$
delimiter ; 

select sumfunc(100,200) as '합계';

drop function if exists calcyearfunc;
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 ~ into ~
:select 한 것을 변수에 저장하고 싶을 경우에 사용.

-- select ~ into ~
select calcyearfunc(2007) into @debut2007;
select calcyearfunc(2013) into @debut2013;
select @debut2007-@debut2013 as '2007과 2013의 차이';

year()함수

-- year() 함수
select mem_id, mem_name, calcyearfunc(year(debut_date)) as '활동 햇수'
	from member;

커서로 한 행씩 처리하기

커서는 테이블에서 한 행씩 처리하기 위한 방식입니다.

커서의 기본 개념
: 커서는 첫 번째 항을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리합니다. 

1.커서 선언하기
2.반복 조건 선언하기
3.커서 열기
4. 데이터 가져오기 -> 데이터 처리하기  // 이 부분을 반복.
5.커서 닫기.

커서는 대부분 스토어드 프로시저와 함께 사용됩니다. 커서의 세부 문법을 외우기보다는 커서를 사용하는 전반적인 흐름에 초점을 맞춰서 진행을 하면 됩니다.

use market_db;
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();​

자동 실행되는 트리거

트리거는 자동으로 수행해 사용자가 추가 작업을 잊어버리는 실수를 방지해줍니다.  트리거를 사용하면 데이터에 오류가 발생한느 것을 막을 수가 있습니다. 이것을 데이터의 무결성이라고 부릅니다.

트리거의 기본

트리거는 사전적 의미로 방아쇠를 뜻합니다. 총의 방아쇠를 당기면 자동으로 총알이 나가듯이, 트리거는 테이블에 무슨일이 일어난다면 자동으로 실행이 됩니다.

트리거의 기본 작동

트리거는 테이블에서 DML문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동합니다.
테이블에 미리 부탁(attach)되는 프로그램 코드라고 생각을 하면 됩니다.

트리거는 스토어드 프로시저와 문법이 비슷하지만, call문으로 직접 실행할 수도 없고, 오직 테이블에 insert, update, delete 등의 이벤트가 발생하였을 경우에 자동으로 실행이 됩니다. 

또한, 스토어드 프로시저와 달리 트리거에는 in, out 매개변수를 사용할 수 없습니다.

-- 7-3 트리거의 기본 작동
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,'블랙핑크');

drop trigger if exists mytrigger;
delimiter $$
create trigger mytrigger
	after delete
    on trigger_table
    for each row
begin
	set @msg = '가수 그룹이 삭제됨' ;
end $$
delimiter ;

set @msg = '';
insert into trigger_table values(4, '마마무');
select @mgs;
update trigger_table set txt = '블핑' where id = 3;
select @msg;

delete from trigger_table where id =4;
select @msg;​

트리거의 활용

-- 트리거 활용

use market_db;
-- select으로 만들기 *단 기본기 정의는 복사되지 않는다.
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)
);

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 ;

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 ;


update singer set addr = '영국' where mem_id = 'BLK';
delete from singer where mem_number >= 7;

select * from backup_singer;​