VIEW

Explain

  • 가상 테이블로 일부분만 데이터로 보고자 할때 사용합니다.

  • 실제 데이터를 저장하고 있지는 않습니다.

  • 한마디로 특정 컬럼의 데이터를 보여주는 역할만 합니다.

  • 뷰를 사용함으로 쿼리를 더 단순하게 만들수 있습니다.

  • 한번 생성된 뷰는 수정이 불가능 하며 인덱스설정이 불가능 합니다.

Syntax

1
CREATE VIEW <뷰이름> AS (QUERY)

Example

  • 국가코드와 국가이름이 있는 뷰 

    1
    2
    3
    CREATE VIEW code_name AS
    SELECT code, name
    FROM country

  • city 테이블에 국가 이름 추가

    1
    2
    3
    4
    SELECT *
    FROM city
    JOIN code_name
    ON city.countrycode = code_name.code

INDEX

Explain

  • 정의 : 테이블에서 데이터를 검색할때 빠르게 찾을수 있도록 해주는 기능입니다.

  • 장점 : 검색속도가 빨라짐

  • 단점 : 저장공간을 10% 정도 더 많이 차지 INSERT, DELETE, UPDATE 할때 속도가 느려짐

  • 사용법 : SELECT시 WHERE 절에 들어가는 컬럼을 Index로 설정하면 좋다.

  • 내부 작동 원리 (B-Tree)

    • 루트노드와 리프노드의 계층적 구조로 루트노드를 이용하여 리프노드에서의 데이터를 빠르게 찾을수 있는 자료구조 알고리즘.

Syntax

1
CREATE INDEX <인덱스 이름> AS <테이블 이름(필드 이름)>

Example

  • employees 데이터 베이스에서 실행

  • 실행계획을 확인하여 인덱스를 사용하는지 확인

    1
    2
    3
    4
    5
    6
    7
    8
    9
    EXPLAIN
    SELECT *
    FROM salaries
    WHERE from_date < "1986-01-01"

    EXPLAIN
    SELECT *
    FROM salaries
    WHERE to_date < "1986-01-01"

  • 인덱스 확인

    1
    SHOW INDEX FROM salaries;

  • 인덱스 생성

    1
    2
    3
    4
    5
    CREATE INDEX fdate
    ON salaries (from_date)

    CREATE INDEX tdate
    ON salaries (to_date)

  • 여러개의 컬럼을 가지는 인덱스 생성도 가능

    1
    2
    CREATE INDEX ftdate
    ON salaries (from_date, to_date)

  • 인덱스 삭제

    1
    2
    3
    4
    5
    6
    7
    8
    DROP INDEX fdate
    ON salaries

    DROP INDEX tdate
    ON salaries

    DROP INDEX ftdate
    ON salaries

  • 여러개의 컬럼을 조건으로 WHERE절에 사용하는 경우 인덱스 확인

  • 인덱스가 하나의 컬럼에 있을때 보다 둘다 있을때가 더 빠름

    1
    2
    3
    4
    EXPLAIN
    SELECT *
    FROM salaries
    WHERE from_date < "1986-01-01" AND to_date < "1986-01-01"

TRIGGER

정의 : 특정 테이블을 감시하고 있다가 설정된 조건의 쿼리가 실행되면 미리 지정한 쿼리가 자동으로 실행되도록하는 방법

Syntax

1
2
3
4
5
6
create trigger {trigger name}
{before | after} {insert | update |delete}
on <table name> for each row
begin
    <excute query>
end

Example

  • database 생성

    1
    2
    3
    4
    5
    6
    7
    8
    9
    create table data1 (
        id varchar(20),
        msg varchar(50) not null
    );

    create table data_backup (
        id varchar(20),
        msg varchar(50) not null
    );

  • Trigger 생성

    1
    2
    3
    4
    5
    6
    7
    create trigger backup
    before delete on data1
    for each row 
    begin
        insert into data_backup(id, msg)
        values(old.id, old.msg);
    end