[혼자공부하는SQL] Summary3

15강

1. 인덱스의 개념

• SELECT를 사용해서 테이블을 조회할 때 결과를 빠르게 도출하도록 도와주는 기능이다.

• 인덱스가 반드시 필요한 것은 아니지만, 실무에서는 데이터의 양이 엄청 많기 때문에 인덱스가 필요하다

1) 인덱스의 장점과 단점

(1) 장점

• SELECT문으로 검색하는 속도가 매우 빨라진다.

• 그 결과 컴퓨터의 부담이 줄어들면서 결국 전체 시스템의 성능이 향상된다.

(2) 단점

• 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요하다.

  • 대략 테이블 크기의 10% 정도의 공간이 추가로 필요하다.

• 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.

  • 찾아보기가 없는 책에 새로 찾아보기를 만드는 것과 마찬가지로 작업 시간이 필요하다.

• SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 나빠질 수 있다.

2. 인덱스의 종류

1) 클러스터형 인덱스

• PRIMARY KEY로 지정하면, 생성된다.

• SELECT 검색 시, ‘자동으로’ 오름차순 되어 결과로 보여진다.

2) 보조 인덱스

• UNIQUE로 지정하면, 생성된다.

• SELECT 검색 시, 결과에 영향을 주지 않는다. (정렬되지 않는다.)

1. 기타

• 테이블의 인덱스 정보 보는 함수

SHOW INDEX FROM table2;

16강

1. 균형 트리(Balanced tree, B-tree)

1) 의미

• 나무를 거꾸로 표현한 자료 구조로, 트리에서 제일 상단의 뿌리를 루트, 줄기를 중간, 끝에 달린 잎을 리프라고 부른다.

• 균형 트리에서 데이터가 저장되는 공간을 노드(Node)라고 하며, 페이지(page)라고도 한다.

2) 종류

• 루트 노드(root node) : 노드의 가장 상위 노드

  • 모든 출발은 루트 노드에서 시작된다.

• 리프 노드(leaf node) : 제일 마지막에 존재하는 노드

  • 데이터가 많다면 3단계나 그 이상이 될 수 있다.

• 중간 노드(internal node) : 루트 노드와 리프 노드의 중간에 끼인 노드들

3) 특징

(1) 전체 테이블 검색으로 진행한 경우

: 인덱스가 없으므로, 처음부터 끝까지 다 검색해야 한다.

(2) 균형 테이블 검색으로 진행한 경우

: 인덱스를 참고하여, 처음부터 끝까지 다 검색하지 않아도 된다.

4) 페이지 분할

: 새로운 페이지를 준비해서 데이터를 나누는 작업

• 페이지 분할이 일어나면, MySQL이 느려지고, 너무 자주 일어나면 성능에 큰 영향을 준다.

2. 인덱스의 구조

1) 클러스터형 인덱스

• 루트 페이지 / 리프 페이지로 구성된다.

• 리프 페이지 = 데이터 페이지

2) 보조 인덱스

• 루트 페이지 / 리프 페이지 / 데이터 페이지로 구성된다.

• 리프 페이지 != 데이터 페이지

3. 인덱스에서 데이터 검색하기

1) 클러스터형 인덱스에서 데이터 찾기

• 루트 페이지를 읽고 MMU 페이지를 찾는다.

• MMU 페이지를 읽고 SPC를 찾는다.

• SPC의 이름 우주소녀를 검색한다.

→ 총 2페이지를 읽고, 원하는 결과를 탐색

2) 보조 인덱스에서 데이터 찾기

• 루트 페이지를 읽고 OMY 페이지를 찾는다.

• OMY 페이지를 읽고 SPC 페이지를 찾는다.

• SPC 페이지를 읽고 SPC를 찾는다.

• SPC의 이름 우주소녀를 검색한다.

→ 총 3페이지를 읽고, 원하는 결과를 탐색

3) 결론

• 인덱스 검색(Index Scan)을 통해 클러스터형 인덱스는 2페이지만 읽어서 원하는 결과를 검색했고, 보조 인덱스는 3페이지를 읽어서 원하는 결과를 검색했다.

• 두 인덱스 모두 검색이 빠르기는 하지만, 클러스터형 인덱스가 조금 더 빠르다.

17강

1. 인덱스 생성 문법

1) 문법

CREATE [UNIQUE] INDEX 인덱스_이름
    ON 테이블_이름 (열_이름) [ASC | DESC]

2) 특징

• Primary Key 문법을 사용하면 클러스터형 인덱스가, Unique 문법을 사용하면 보조 인덱스가 자동으로 생성된다.

• 위의 방식으로 생성하면 보조 인덱스로 생성된다.

3) UNIQUE INDEX

• UNIQUE로 인덱스를 생성하려면, 기존에 입력된 값들에 중복이 있으면 안 된다.

• 또한, 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니, 신중해야 한다.

4) 기타

(1) 테이블 상태 정보를 조회하기

SHOW TABLE STATUS LIKE 'member'

• 설명

  • SHOW TABLE STATUS : 현재 데이터베이스에 있는 모든 테이블의 상태와 정보를 출력한다.

  • LIKE 'member' : 특정 테이블(member)만 필터링하여 해당 테이블의 상태 정보를 조회한다.

(2) 테이블의 인덱스 정보 조회하기

SHOW INDEX FROM member;

(3) 테이블의 통계 정보를 업데이트

ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';

2. 인덱스 제거 문법

1) 문법

DROP INDEX 인덱스_이름 ON 테이블_이름

2) 특징

• 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.

• ALTER TABLE 문으로 기본 키나 고유 키를 제거하면, 자동으로 생성된 인덱스도 제거할 수 있다.

3) 특정 데이터베이스(market_db)의 외래 키(Foreign Key) 제약 조건을 조회

SELECT table_name, constraint_name
    FROM information_schema.referential_constraints
    WHERE constraint_schema = 'market_db';

• information_schema.referential_constraints : MySQL의 시스템 테이블로, 데이터베이스의 외래 키 정보를 저장한다.

• constraint_schema = 'market_db' : 'market_db' 데이터베이스에서 설정된 외래 키 제약 조건만 필터링한다.

• table_name : 외래 키가 설정된 테이블 이름을 가져온다.

• constraint_name : 해당 테이블의 외래 키 제약 조건 이름을 가져온다.

3. 인덱스 사용하기

1) 예시

(1) 전부 다 검색하는 경우에는 인덱스를 사용하지 않는다. → 인덱스 사용 X

SELECT * FROM member;

(2) WHERE 절에서 인덱스를 사용해야 한다. → 인덱스 사용 O

SELECT mem_id, mem_name, addr 
    FROM member 
    WHERE mem_name = '에이핑크';

(3) 인덱스를 실제로 사용한 경우

-- 새로운 인덱스 생성
CREATE INDEX idx_member_mem_number
    ON member (mem_number);

 -- 인덱스 적용
ANALYZE TABLE member;

-- 인덱스 사용하여 SELECT
SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 7;

(4) 인덱스를 사용하지 않는 경우 1 : 많은 건수를 처리해야 하는 경우

-- 문제 : 인덱스가 있더라도 많은 건수가 발생하면 MySQL이 인덱스를 사용하지 않는다.
SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 1;

(5) 인덱스를 사용하지 않는 경우 2 : 인덱스에 가공한 경우

-- 문제 : 인덱스에 가공을 하면, 인덱스를 사용하지 않는다.
SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number*2 >= 14;     

-- 해결책 : 인덱스를 가공하지 않지만, 인덱스를 사용하는 경우
SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 14/2;

18강

1. 스토어드 프로시저 기본

1) 스토어드 프로시저의 개념과 형식

• 스토어드 프로시저는 쿼리 문의 집합으로도 볼 수 있다.

• 어떠한 동작을 일괄 처리하기 위한 용도로도 사용한다.

• 자주 사용하는 일반적인 쿼리를 반복하는 것보다는, 스토어드 프로시저로 묶어 놓고, 필요할 때마다 간단히 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있다.

(1) 스토어드 프로시저 생성

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름 (IN 또는 OUT 매개변수)
BEGIN
-- 이 부분에 SQL 프로그래밍을 코드를 작성
END %%
DELIMITER;

• $$는 $를 1개만 사용해도 되지만, 명확하게 표시하기 위해 2개를 사용하며, ##, &&, %%, // 등으로 바꿔도 된다.

• 이름은 마음대로 지어도 되지만, 가능하면 이름만으로 스토어드 프로시저라는것을 알 수 있도록 표현하는 것이 좋다.

• CREATE PROCEDURE는 스토어드 프로시저를 만든 것뿐이며, 아직 실행(호출)한 것은 아니다.

(2) 스토어드 프로시저 호출

CALL 스토어드_프로시저_이름();

2. 스토어드 프로시저 실습

1) 매개변수의 사용

• 스토어드 프로시저에서는 실행 시, 입력 매개변수를 지정할 수 있다.

(1) 종류

• 입력 매개변수

• 출력 매개변수

2) 입력 매개변수

(1) 형식

IN 입력_매개변수_이름 데이터_형식

(2) 사용

CALL 프로시즈_이름(전달_값);

3) 출력 매개변수

(1) 형식

OUT 출력_매개변수_이름 데이터_형식

(2) 사용

CALL 프로시저_이름(@변수명);
SELECT @변수명;

19강

1. 스토어드 함수

1) 스토어드 함수의 개념과 형식

• MySQL에서 제공하는 함수 외에, 사용자가 원하는 함수를 직접 만들 수 있다.

• 이를 스토어드 함수라고 한다.

(1) 생성 형식

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
    RETURNS 반환형식
BEGIN

    - 이 부분에 프로그래밍 코딩
    RETURN 반환값;

END $$
DELIMITER ;

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

• RETURNS 문으로 반환할 값의 데이터 형식을 지정한다.

• 본문 안에서는 RETURN 문으로 하나의 값을 반환해야 한다.

• 스토어드 함수의 매개변수는 모두 입력 매개변수이며, IN을 붙이지 않는다.

• 스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT 문 안에서 호출된다.

• 스토어드 프로시저 안에서는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT를 사용할 수 없다.

• 스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용한다.

2) 스토어드 함수의 사용

• 스토어드 함수를 사용하기 위해서는 먼저 SQL로 스토어드 함수 생성 권한을 허용해야 한다.

SET GLOBAL log_bin_trust_function_creators = 1;

2. 커서로 한 행씩 처리하기

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

1) 커서의 기본 개념

(1) 개념

• 커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.

• 처음에는 커서가 행의 시작을 가리킨 후에 한 행씩 차례대로 접근한다.

• Ex) 커서를 통해 테이블의 전체 인원 수를 알고 싶은 경우

(2) 커서의 작동 과정

• 커서는 대부분 스토어드 프로시저와 함께 사용된다.

2) 커서의 단계별 실습

(1) 사용할 변수 준비하기

DECLARE memNumber INT; -- 회원의 인원수
DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
DECLARE toNumber INT DEFAULT 0; -- 전체 인원의 합계

-- 행의 끝을 파악하기 위한 변수
DECLARE endOfRow BOOLEAN DEFAULT FALSE;

(2) 커서 선언하기

DECLARE memberCursor CURSOR FOR
    SELECT mem_number FROM member;

(3) 반복 조건 선언하기

: 행의 끝에 다다르면, 앞에서 선언한 endOfRow 변수를 TRUE로 설정한다.

DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET endOfRow = TRUE;

(4) 커서 열기

OPEN memberCursor;

(5) 행 반복하기

cursor_loop : LOOP
    이 부분을 반복
END LOOP cursor_loop

-- 반복문을 빠져나갈 조건
IF endOfRow THEN
    LEAVE cursor_loop;
END IF;

• LEAVE는 반복할 이름을 빠져나간다.

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_lopp;

SELECT (totNumber / cnt) AS '회원의 평균 인원 수';

(6) 커서 닫기

CLOSE memberCursor;

20강

1. 트리거의 기본

1) 트리거의 개요

• 트리거란? 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생하면 실행되는 코드이다.

• 트리거는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다.

• ex) 해당 데이터가 삭제 되기 전에 다른 곳으로 자동으로 저장해주는 기능이 있다면, 사용자는 더 이상 행 데이터를 삭제하기 전에 다른 곳에 저장해야 하는 부담에서 벗어나게 될 뿐 아니라, 모든 것이 자동으로 처리되므로 삭제된 모든 사용자 정보는 완벽하게 별도의 장소에 저장될 것이다.

2) 트리거의 기본 작동

(1) 설명

: 트리거는 테이블에서 DML(Data Manipulation Language)문인 INSERT, UPDATE, DELETE 등의 이벤트가 발생될 때 작동한다.

• 테이블에 미리 부착(attach)되는 프로그램 코드라고 생각하면 된다.

• 이 책에서 언급하는 트리거는 AFTER트리커이다. BEFORE 트리거는 작동 방식이 조금 다르다.

(2) 특징

• 트리거는 스토어드 프로시저와 문법이 비슷하지만, CALL 문으로 직접 실행시킬 수는 없고, 오직 테이블에 INSERT, UPDATE, DELETE 등의 이벤트가 발생할 경우에만 자동으로 실행된다.

• 또한, 스토어드 프로시저와 달린 트리거에는 IN, OUT 매개변수를 사용할 수 없다.

(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 = '';

-- 트리거 발생 : X (INSERT, UPDATE)
INSERT INTO trigger_table VALUES(4, '마마무');
SELECT @msg;
UPDATE trigger_table SET txt = '블핑' WHERE id = 3;
SELECT @msg;

-- 트리거 발생 : O (DELETE)
DELETE FROM trigger_table WHERE id = 4;
SELECT @msg;