240524 MariaDB(7) - 인덱스/스토어드 프로시져/트리거

일일 정리

240524 MariaDB(7) - 인덱스/스토어드 프로시져/트리거

햠__ 2024. 5. 26. 19:47

(이어서..)

뷰를 생성할 때 WITH CHECK OPTION을 사용할 수 있다.

이 옵션은 서브 쿼리에 옵션이 부합하지 않으면 테이블의 내용 변경이 불가능하게 한다.

CREATE OR REPLACE VIEW v_employee
AS SELECT *
   FROM employee
   WHERE salary >= 3000000
WITH CHECK OPTION;

서브 쿼리문 바로 아래에 WITH CHECK OPTION을 써주면 된다.

-- 서동일 사원의 급여를 400만원으로 변경
-- 서브 쿼리의 조건에 부합하지 않기 때문에 변경이 불가능
UPDATE v_employee
SET salary = 4000000
WHERE emp_id = 200;

기존의 서동일 사원의 급여가 2백만원이기 때문에 뷰의 서브 쿼리 조건에 만족하지 않아서

뷰에서 서동일 사원의 정보를 확인할 수 없었다.

원래였다면 UPDATE가 가능했겠지만,

WITH CHECK OPTION 을 주었기 때문에 뷰의 서브쿼리에 조건을 만족하지 않아서 정보를 수정할 수 없었다.

인덱스(Index)

인덱스

테이블의 열 단위에 생성되는 데이터베이스의 개체로 데이터를 조회할 때 빠르게 접근하도록 도와주는 역할

클러스터형 인덱스(Clustered Index)와 보조 인덱스(Secondary Index)가 있다.

클러스터형 인덱스는 테이블당 한 개만 생성할 수 있고, 보조 인덱스는 테이블당 여러 개를 생성할 수 있다.

인덱스 생성

PRIMARY KEY 제약 조건으로 지정되는 열에 자동으로 클러스터형 인덱스가 생성된다.

UNIQUE 제약 조건으로 지정되는 열에 자동으로 보조 인덱스가 생성된다.

인덱스에서 사용하는 구조는 비트리 구조 (데이터를 검색할 때 효과적임)

클러스터형 인덱스를 가진 걸 통해서 검색하면 데이터를 하나씩 뒤져보는 것이 아니라 위치를 검색하는 것이다?

보조 인덱스는 데이터를 입력하면 입력된 데이터를 가지고 정렬하는 것이 아니라, 인덱스 자체에서 위치를 정리해서 가지고 있다.

보조 인덱스는 클러스터형 인덱스보다 검색이 느리지만 수정 등.. 은 더 빠르다 왜냐면 이미 정렬해놓고 있기 때문에

** 근데 솔직히 조금 이해가 안감 ,, 다시 공부해야 할 듯 -_-

자동으로 생성되는 인덱스 외에 직접 인덱스를 생성하려면 ALTER TABLE 또는 CREATE INDEX 구문을 사용해야 한다.

이때 직접 생성하는 인덱스는 보조 인덱스이다.

EXPLAIN SELECT * FROM employees 사용

type은 검색된 데이터의 타입, possible_keys, key_len은 길이, rows는 검색한 열 ?

-- 인덱스 생성
CREATE INDEX idx_employees_first_name
ON employees(first_name);

인덱스 생성 후 SELECT를 통해 조회해보았을 때 row의 결과가 약 20만 건에서 줄어든 것을 확인할 수 있다.

이를 통해서 속도가 더 확연히 빨라진 것을 확인할 수 있다.

* 인덱스를 사용하면 검색 속도를 빠르게 사용할 수 있다.

테이블에 지정된 인덱스 정보를 툴을 사용해서도 확인할 수 있지만,

쿼리문으로도 확인이 가능하다.

SHOW INDEX FROM (테이블명);

인덱스 삭제

인덱스를 삭제하려면 DROP INDEX 구문을 사용해야 한다.

인덱스를 모두 제거할 때는 되도록 보조 인덱스부터 삭제하도록 한다.

인덱스를 수정하려면 기존 인덱스를 삭제한 후 새로운 인덱스를 생성해야 한다.

DROP INDEX 인덱스 이름 ON 테이블 이름;
ALTER TABLE 테이블 이름 DROP INDEX 인덱스 이름;

인덱스 주의사항

WHERE절에서 자주 사용되는 열에 인덱스를 만들어야 한다.

데이터 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다.

JOIN에 자주 사용되는 열에는 인덱스를 생성해 주는 것이 좋다.

DML(INSERT, UPDATE, DELETE) 작업이 얼마나 자주 일어나는지를 고려해야 한다.

> 자주 사용하게 된다면 INDEX의 성능이 더 저하가 될 것임.

사용하지 않는 인덱스는 제거하자.

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

스토어드 프로시저

복잡한 쿼리문을 다시 입력할 필요 없이 간단하게 호출해서 실행 결과를 얻을 수 있다. (쿼리문의 집합)

MariaDB에서 제공되는 프로그래밍 기능이다.

스토어드 프로시저 생성

CREATE PROCEDURE 구문 사용

DELIMITER $$
CREATE PROCEDURE 프로시저이름
BEGIN
	-- SQL 프로그래밍 코드 작성
END $$
DELIMITER ;

CALL 프로시저이름();

DELIMITER $$부터 END $$ 까지가 프러시저 생성 ($$ 로 안 하고 다른 기호로 해도 되긴 함)

생성한다고 바로 실행되는 것이 아니라 호출을 해주어야 함.

간단한 프로시저를 만들어보자.

DELIMITER $$
CREATE PROCEDURE userProc()
BEGIN
	SELECT *
    FROM usertbl;
END $$
DELIMITER;

CALL userProc();

간단히 usertbl 테이블의 데이터를 조회하는 SQL문을 작성하였고,

생성한 프러시저를 호출한 결과 usertbl 테이블의 데이터를 조회할 수 있었다.

매개 변수의 사용

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

DELIMITER $$
CREATE PROCEDURE 프로시저 이름(
	IN|OUT 매개변수 데이터 유형,
    ...
)
BEGIN
	-- SQL 프로그래밍 코드 작성
END $$
DELIMITER;

CALL 프로시저이름([매개값, ...]);

변수 하나를 담아서 주면 내부에서 값을 담아주어 프로시저에서 사용할 수 있다.

-- 회원의 이름을 입력받아 조회하는 프로시저 생성
DELIMITER $$
CREATE OR REPLACE PROCEDURE userProc (
	IN userName VARCHAR(10)
)
BEGIN
	SELECT * FROM usertbl WHERE NAME = userName;
END $$
DELIMITER $$

CALL userProc('조관우');

'조관우' 값을 입력받아서 SQL문 쿼리 내에서 사용한다.

해당 쿼리문에 맞는 결괏값이 출력된 것을 확인할 수 있다.

OUT도 사용해보자.

********************** 여기 잘 모르겠음 다시 보기

DELIMITER $$
CREATE OR REPLACE PROCEDURE userProc (
	IN id CHAR(8),
    OUT userName CHAR(20)
)
BEGIN
	SELECT *
    FROM usertbl
    WHERE userID = id;
END $$
DELIMITER;

CALL userProc(

IF문

-- IF
DELIMITER $$
CREATE OR REPLACE PROCEDURE empProc(
	IN id CHAR(3)
)
BEGIN
	DECLARE year INT; -- 지역변수 생성 
	
	SELECT YEAR(hire_Date)
	INTO year
	FROM employee
	WHERE emp_id = id;
	
	if YEAR >= 2000 THEN 
		SELECT '2010년도에 입사하셨네요!';
	ELSEIF YEAR >= 2000 THEN
		SELECT '2000년도에 입사하셨네요!';
	ELSE
		SELECT '1990년대에 입사하셨네요??';
	END if;
END$$

CALL empProc('206');

CASE문

-- CASE 실습
DELIMITER $$
CREATE OR REPLACE PROCEDURE gradeProc(
	IN score INT
)
BEGIN
	DECLARE grade CHAR(1);
	
	CASE
		WHEN score >= 90 THEN
			SET grade = 'A';
		WHEN score >= 80 THEN
			SET grade = 'B';
		WHEN score >= 70 THEN
			SET grade = 'C';
		WHEN score >= 60 THEN
			SET grade = 'D';
		ELSE 
			SET grade = 'F';
	END CASE;
	
	SELECT score AS '점수', grade AS '등급';
END $$

CALL gradeProc(69);

CASE 문을 IF문으로, IF문을 CASE 문으로도 변경이 가능하다.

반복문 WHILE

-- 반복문
-- 1 ~ 10까지의 합계
DELIMITER $$
CREATE PROCEDURE sumProc()
BEGIN
	DECLARE i INT;
	DECLARE SUM INT;
	
	SET i = 1;
	SET SUM = 0;
	
	WHILE (i <= 10) DO
		SET SUM = SUM + i;
		SET i = i + 1;
	END WHILE;
	
	SELECT SUM;
END $$gradeProc

CALL sumProc();

오류 처리

MariaDB는 오류가 발생할 경우 직접 오류를 처리하는 방법을 제공한다.

-- 에러처리 테스트
DELIMITER $$
CREATE OR REPLACE PROCEDURE erroProc()
BEGIN
	DECLARE CONTINUE HANDLER FOR 1146 
		SELECT '테이블이 존재하지 않습니다.' AS '메세지';
	SELECT $ FROM noTable;
END $$
DELIMITER ;

CALL erroProc();

트리거 (Trigger)

트리거

트리거는 테이블에 삽입, 수정, 삭제 등의 작업이 발생할 때 자동으로 작동되는 개체이다.

트리거의 종류

AFTER 트리거 : 테이블에 삽입, 수정, 삭제 작업 후에 작동하는 트리거

BEFORE 트리거 : 테이블에 삽입, 수정, 삭제 작업 전에 작동하는 트리거

트리거 생성

CREATE TRIGGER 구문 사용

DELIMITER $$
CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE|AFTER INSERT|UPDATE|DELETE ON 테이블명
FOR EACH ROW
BEGIN
    -- SQL 프로그래밍 코드 작성
END $$
DELIMITER ;

update만 old와 new에서 접근할 수 있다.

접속 환경이 바뀌어도 mobaXterm에서 IP 변경 없이 접속하는 법

버츄얼 박스로 들어가서 포트 포워딩 설정에 들어간다.

그곳에서 ipconfig로 확인하여 입력했던 호스트 IP를 127.0.0.1로 바꾼다.

127.0.0.1은 내 PC 자기 자신을 의미한다.

매번 접속 IP가 바뀔 때마다 포트 포워딩 설정에 들어가서 IP를 변경해줬어야 했는데 이제 안 해도 된다.

또한, mobaXterm 실행하여 edit session에 들어가서 세션에 접속하는 IP도 127.0.0.1로 바꾸어 주면 된다.

'일일 정리' 카테고리의 다른 글

240528 소프트웨어 공학(1) - git/git 설치/git 태그, 브랜치, 머지/git 커밋/git 커밋 후 작업 취소 (1)	2024.06.03
240527 MariaDB(8) - 리눅스에 MariaDB 설치/NOT EQUAL JOIN 실습/조인 실습 문제 (0)	2024.05.28
240523 MariaDB(6) - 테이블 수정/뷰 (0)	2024.05.24
240522 MariaDB(5) - DB 모델링/테이블/제약 조건 (0)	2024.05.22
240521 MariaDB(4) - 조인/UNION 연산자/인라인 뷰 (0)	2024.05.21

현재글240524 MariaDB(7) - 인덱스/스토어드 프로시져/트리거

으아악

기록

Today :
Yesterday :

으아악