MySQL - 뷰, 인덱스, 스토어드 프로시저

뷰(VIEW) : 가상 테이블

1. 복잡한 쿼리를 가상 테이블인 뷰로 만들면 사용이 간단해진다
2. 보안에 도움 (사용자의 레벨에 따라 접근 레벨을 분류할 수 있음)
3. 데이터의 독립성을 유지

※ 뷰를 이용한 쓰기 작업은 하지 않는 것이 좋다 (본 테이블에 데이터가 누락될 수 있음)

#뷰 생성하기
#똑같은 이름의 뷰가 있을 경우 replace를 써서 기존 뷰를 날리고 새로운 뷰 생성

create [or replace] view 뷰이름
as 서브쿼리

예)
create or replace view view_80
as
select employee_id as emp_id, last_name as name, salary
from employees
where department_id=80;

　

인덱스(Index)

빠른 검색을 제공하기 위해 지정된 컬럼값을 “B-트리”로 구성

• unique 나 primary key를 설정하면 자동으로 인덱스가 생성된다.
• 인덱스는 질의 성능을 높이기 위한 목적으로 사용되지만, 너무 많은 컬럼을 지정하거나 아무 컬럼을 인덱스로 지정하면 오히려 성능이 떨어질 수 있다.
  • 장점
    1. 검색 속도가 무척 빨라질 수 있다. (단, 항상 그런 것은 아니다)
    2. 위 결과로 쿼리의 부하가 줄어들어서 시스템 전체의 성능이 향상된다.
  • 단점
    1. 인덴스가 데이터베이스 공간을 차지해서 추가 공간이 필요한데, 대략 데이터베이스의 10% 정도 추가 공간이 필요하다.
    2. 처음 인덱스 생성에 시간이 많이 소요될 수 있다.
    3. 데이터 변경작업 (insert, update, delete)가 자주 일어나는 경우 오히려 성능이 나빠진다.
  • 인덱스가 없을 경우 데이터를 찾으려면 테이블 전체를 검색하는 Full Table Scan을 할 수 밖에 없고, 데이터가 많을 경우 시스템에 과부하를 초래한다.
• 인덱스 설정 기준
  1. 인덱스는 자주 수정 및 삭제가 되지 않는 컬럼을 기준으로 만드는 것이 좋다.
  2. where 절로 검색했을 때 검색 결과로 나오는 행이 몇줄 안되는 컬럼

　

스토어드 프로시져 (Stored Procedure)

MySQL에서 제공해주는 프로그래밍 기능으로, SQL문을 하나로 묶어서 편리하게 사용하거나 프로그래밍 언어와 같은 기능을 담당할 수도 있다.

⇒ 간단하게 말해서, 파이썬에서의 함수와 같은 기능을 한다고 보면 된다.

특징:

• MySQL의 성능을 향상시킬 수 있다. → 쿼리 텍스트 길이를 줄여주기 때문에 네트워크의 부하를 줄일 수 있다.
• 유지관리가 간편하다.
• 모듈식 프로그래밍이 가능하다.
• 보안을 강화할 수 있다.

DELIMITER $$
CREATE PROCEDURE 프로시저명(파라미터 리스트)
BEGIN
	실행문
END $$
DELIMITER ;

CALL 프로시저명()

⇒ stored procedure 목록에서 우클릭, ‘create stored procedure’를 사용하면 DELIMITER 사용없이 편리하게 생성가능

변수선언

SET @변수이름 = 값;
SELECT @변수이름;

• 스토어드 프로시저 안에서는 DECLARE 문으로 ‘@’없이 변수명만으로 선언해서 사용.

  → @변수는 전역변수, DECLARE 변수는 지역변수처럼 생각하면 된다.