06
11
스마트팩토리/데이터베이스

5. 인덱스, 트랜잭션, 데이터베이스 모델링, 저장 프로시저, 사용자 정의 함수

1) 인덱스

인덱스는 지정한 컬럼들을 기준으로 일종의 목차를 생성하는 것이다. 대용량의 테이블일수록 인덱스는 데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구가 된다. 인덱스의 재정렬이 필요한 경우 다시 작성을 누르면 된다.

 

장점

  • 검색 속도가 무척 빨라질 수 있다.(항상 그런 것은 아니다.)
  • 그 결과 해당 쿼리의 부하가 줄어들어서 시스템 전체의 성능이 향상된다.

단점

  • 인덱스가 데이터베이스 공간을 차지해서 데이터베이스의 크기의 10% 정도의 추가 공간이 필요하다.
  • 처음 인덱스를 생성하는 데 시간이 많이 소요될 수 있다.
  • 데이터의 변경 작업(Insert, Update, Delete)이 자주 일어날 경우에는 오히려 성능이 많이 나빠질 수도 있다.

1.1) 인덱스의 종류

인덱스의 종류는 크게 클러스터형 인덱스(Clustered Index)와 비클러스터형 인덱스(Nonclustered Index)로 나뉜다.

비클러스터형 인덱스는 앞에서 설명했던 데이터베이스 이론 책과 같이 <찾아보기>가 별도로 있고, <찾아보기>를 찾은 후에 그 옆에 표시된 페이지로 가야 실제 찾는 내용이 있는 것을 말한다. 테이블당 여러 개를 생성할 수 있다.

클러스터형 인덱스는 책의 내용 자체가 순서대로 정렬이 되어 있어서 인덱스 자체가 책의 내용과 같은 것을 말한다. 테이블당 한 개만 생성할 수 있으며 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.

 

테이블 생성 시에 자동으로 생성되는 인덱스는 반드시 제약 조건을 사용해야 하며, 인덱스가 만들어 지는 제약 조건은 Primary Key 또는 Unique 뿐이다.

 

인덱스 추가 과정과 생성된 인덱스

 

생성된 인덱스 확인

 

클러스터형 인덱스 특징

  • 클러스터형 인덱스의 생성 시에는 데이터 페이지 전체가 다시 정렬된다. 이미 대용량의 데이터가 입력된 상태라면 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템 부하를 줄 수 있으므로 신중하게 생각해야 한다.
  • 인덱스 자체의 리프 페이지가 곧 데이터다. 인덱스 자체에 데이터가 포함이 되어있다고 볼 수 있다.
  • 비클러스터형보다 검색 속도는 더 빠르다. 하지만, 데이터의 입력/수정/삭제는 더 느리다.
  • 클러스터 인덱스는 성능이 좋지만 테이블에 한 개만 생성할 수 있다. 그러므로 어느 열에 클러스터형 인덱스를 생성하느냐에 따라서 시스템의 성능이 달라질 수 있다.

 

비클러스터형 인덱스 특징

  • 비클러스터형 인덱스의 생성 시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
  • 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)다. 클러스터형보다 검색 속도는 더 느리지만, 데이터의 입력/수정/삭제는 덜 느리다.
  • 비클러스터형 인덱스는 여러 개 생성할 수 있다. 하지만, 함부로 남용할 경우에는 오히려 시스템 성능을 떨어뜨리는 결과를 초래할 수 있으므로, 꼭 필요한 열에만 생성하는 것이 좋다.

 

1.2) B-Tree(Balanced Tree)

B-Tree는 범용적으로 사용되는 데이터의 구조다. 인덱스를 표현할 때 많이 사용된다. B-Tree 구조는 데이터를 검색할 때(SELECT 구문을 사용할 때) 아주 뛰어난 성능을 발휘한다. 인덱스를 구성하면 데이터를 변경(INSERT, UPDATE, DELETE)할 때 성능이 나빠질 수 있다.

 

3차 B-트리 구조

 

1.3) 인덱스의 특징

  • 인덱스는 열 단위에 생성된다.
  • WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
  • WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
  • 데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효용이 없다.
  • 외래 키가 사용되는 열에는 인덱스를 되도록 생성해주는 것이 좋다.
  • JOIN에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.
  • INSERT/UPDATE/DELETE가 얼마나 자주 일어나는지를 고려해야 한다.
  • 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
  • 클러스터형 인덱스가 테이블에 아예 없는 경우도 있다.
  • 사용하지 않는 인덱스는 제거하는 것이 좋다.
  • 계산 열에도 인덱스를 활용할 수 있다.

 

2) 트랜잭션(Transaction)

2.1) 데이터베이스의 기본 구조와 SQL 작동 방식

데이터베이스 생성 시 실체는 최소 두 개의 파일로 존재하게 된다. 하나는 확장명이 *.mdf인 데이터 파일이다. 이 파일에는 실제 데이터(테이블, 인덱스 등)와 그 행 데이터가 저장된다. 다른 하나는 확장명이 *.ldf인 트랜잭션 로그 파일(tRANSACTION Log File)이다. 이 트랜잭션 로그 파일은 DBMS에서 매우 중요한 역할을 하는데, 정전 등의 응급상황이 발생했을 때도 입력된 데이터가 완전하도록 해준다. 완전은 데이터베이스에서 전부 되거나 전부 안 되는 것(All or Nothing)을 말한다.

 

데이터베이스의 간단한 구조

 

위 그림에서 SELECT 문은 ① → ③ → ④의 과정을 거치고 UPDATE 문은 ① → ③ → ② → ④의 과정을 거친다.

 

2.2) 트랜잭션의 개념과 작동 방식

트랜잭션을 정의하면 하나의 논리적 작업 단위로 수행되는 일련의 작업이다. 단순히 말하면 SQL의 묶음이라 할 수 있으며 여기서 SQL의 묶음은 SELECT/INSERT/UPDATE/DELETE가 주로 해당된다. 데이터의 무결성을 지키기 위해 하나의 트랜잭션이 전부 적용되거나 또는 전부 적용이 안되어야 한다.

BEGIN TRANSACTION(또는 BEGIN TRAN)
	SQL 문장들
COMMIT TRANSATION(또는 COMMIT, COMMIT TRAN, COMMIT WORK)
ROLLBACK TRANSACTION(또는 ROLLBACK, ROLLBACK TRAN)

커밋(COMMIT)은 현재까지의 임시 작업 내용을 확정짓는 것이고, 롤백(ROLLBACK)은 현재까지의 임시 작업을 취소시키는 것이다.

 

ex)

BEGIN TRAN;

SELECT * FROM buyTBL
 WHERE num = 9;

 UPDATE buyTBL
    SET price = 100, amount = 5
  WHERE num = 9;

COMMIT;
ROLLBACK;

트랜잭션 처리 과정

 

트랜잭션 처리 과정

 

트랜잭션 처리과정 1이 완료된 후, 정전이 되고 다시 전원이 들어온 경우

 

2.3) 트랜잭션의 특성

  • 원자성(Atomicity) : 트랜잭션은 분리할 수 없는 하나의 단위로써, 작업이 모두 수행되거나 하나도 수행되지 않아야 한다.
  • 일관성(Consistency) : 트랜잭션에서 사용되는 모든 데이터는 일관되어야 한다.
  • 격리성(Isolation) : 현재 트랜잭션이 접근하고 있는 데이터는 다른 트랜잭션에서 격리되어야 한다.
  • 영속성(Durability) : 트랜잭션이 정상적으로 종료된다면 그 결과는 시스템 오류가 발생하더라도 시스템에 영구적으로 적용된다.

 

2.4) 트랜잭션의 종류

자동 커밋 트랜잭션(Autocommit Transaction)

자동 커밋 트랜잭션은 각 쿼리마다 자동으로 BEGIN TRAN과 COMMIT TRAN이 붙여지는 것을 말한다. SQL Server는 디폴트로 자동 커밋 트랜잭션을 사용한다.

 

명시적 트랜잭션(Explicit Transaction)

명시적 트랜잭션은 직접 BEGIN TRAN 문과 COMMIT TRAN 또는 ROLLBACK TRAN 문을 써 주는 것을 말한다.

 

암시적 트랜잭션(Implicit Transaction)

암시적 트랜잭션은 오라클 등의 데이터베이스와 호환을 위해서 사용될 수 있다. 어떤 쿼리가 나오면 자동으로 내부적으로 BEGIN TRAN을 붙여주지만, COMMIT TRAN 또는 ROLLBACK TRAN은 직접 써줘야 한다.

 

암시적 트랜잭션 설정 과정

 

3) 데이터베이스 모델링

3.1) 프로젝트의 진행 단계

프로젝트(Project)란 현실세계의 업무를 컴퓨터 시스템으로 옮겨놓는 일련의 과정 또는 대규모의 프로그램을 작성하기 위한 과정을 말한다. 프로젝트의 여러 문제점을 해결하기 위해 다른 분야의 것을 소프트웨어 분야에 가져와 적합하게 수정한 분야를 소프트웨어 공학이라고 부른다. 소프트웨어 공학에서 제시하는 소프트웨어 개발 모델 중 가장 오래되고 전통적으로 사용되는 것은 폭포수 모델(Waterfall Model)이다.

 

폭포수 모델

 

장점 : 각 단계가 명확히 구분되어서 프로젝트의 진행 단계가 명확하다.

단점 : 문제점이 발생도리 경우 다시 앞 단계로 거슬러 올라가기가 어렵다.

 

3.2) 데이터베이스 모델링

데이터베이스 모델링(또는 데이터 모델링)은 현 세계에서 사용되는 작업이나 사물을 DBMS의 데이터베이스 개체로 옮기는 과정이다.

 

데이터베이스 모델링의 개념

 

데이터베이스 다이어그램으로 만든 데이터베이스 모델링

 

cf. 권한부여

 

현재 서버에 로그인할 수 있는 사용자

 

신규 사용자 로그인 속성 설정 과정

 

권한이 없는 데이터베이스에 접근한 경우

 

 

권한이 없는 테이블에 접근한 경우

 

사용자에게 sqlDB 읽기/쓰기 권한 부여

 

권한이 부여된 사용자의 테이블 접근 성공

 

4) 저장 프로시저(Stored Procedure)

저장 프로시저란 SQL Server에서 제공되는 프로그래밍 기능으로 쿼리 문의 집합이다. 어떤 동작을 일괄 처리할 때 사용한다.  수정은 ALTER, 삭제는 DROP을 사용한다.

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [; number]
   [ { @parameter [ type_schema_name. ] data_type }
       [ VARYING ] [ = default ] [ OUT | OUTPUT] [READONLY]
   ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN | sql_statement [;] [ ,...n ] [ END ] }
[;]

 

매개변수를 지정하는 형식

@입력_매개_변수_이름 데이터_형식 [ = 디폴트값]

 

매개변수가 있는 저장 프로시저 실행

EXEC(EXECUTE) 프로시저_이름 [전달 값]

 

출력 매개변수 지정

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

 

출력 매개변수가 있는 저장 프로시저 실행

EXEC 프로시저_이름 @변수명 OUTPUT

 

ex)

-- =============================================
-- Author:		KHJ
-- Create date: 2020.06.11. 15:35
-- Description:	유저 테이블을 조회하는 사용자 저정 프로시저
-- =============================================
CREATE OR ALTER PROCEDURE usp_User
AS
BEGIN
	SELECT * FROM userTBL;
END
GO

EXEC usp_user;

 

예제 실행 화면

 

-- =============================================
-- Author:		KHJ
-- Create date: 2020.06.11. 15:35
-- Description:	유저 테이블을 조회하는 사용자 저정 프로시저
-- =============================================
CREATE OR ALTER PROCEDURE usp_User
	@userID VARCHAR(8)
AS
BEGIN
	SELECT * FROM userTBL WHERE userID = @userID;
END

EXEC usp_User;
EXEC usp_User 'BBK';

 

예제 실행 화면

 

예제 실행 화면

 

-- =============================================
-- Author:		KHJ
-- Create date: 2020.06.11. 15:35
-- Description:	유저 테이블을 조회하는 사용자 저정 프로시저
-- =============================================
CREATE OR ALTER PROCEDURE usp_User
	@userID VARCHAR(8),
	@userHeight INT
AS
BEGIN
	SELECT * FROM userTBL 
	 WHERE userID = @userID
	   AND height >= @userHeight;
END

EXEC usp_User 'BBK', 170;

 

예제 실행 화면

 

-- =============================================
-- Author:		KHJ
-- Create date: 2020.06.11. 15:35
-- Description:	유저 테이블을 조회하는 사용자 저정 프로시저
-- =============================================
CREATE OR ALTER PROCEDURE usp_User
	@userID VARCHAR(8) = 'SSK',
	@userHeight INT = 186
AS
BEGIN
	SELECT * FROM userTBL 
	 WHERE userID = @userID
	   AND height >= @userHeight;
END

EXEC usp_User;

 

예제 실행 화면

 

-- =============================================
-- Author:		KHJ
-- Create date: 2020.06.11. 15:35
-- Description:	유저 테이블을 조회하는 사용자 저정 프로시저
-- =============================================
CREATE OR ALTER PROCEDURE usp_User
	@userID VARCHAR(8) = 'SSK',
	@userHeight INT = 186,
	@outValue INT OUTPUT
AS
BEGIN
	SELECT @outValue = birthYear FROM userTBL 
	 WHERE userID = @userID
	   AND height >= @userHeight;
END

DECLARE @curBirth INT;
EXEC usp_User 'JKW', 160, @curBirth OUTPUT;
PRINT '현재 사용자의 출생년도는 ==>' + CAST(@curBirth AS CHAR(4));

 

예제 실행 화면

 

CREATE OR ALTER PROC usp_ages
	@userName NVARCHAR(10)
AS
BEGIN
	DECLARE @mYear INT; -- 출생년도 저장 변수
	SELECT @mYear = birthYear FROM userTBL
	 WHERE name = @userName;

	IF (@mYear >= 1980)
	BEGIN
		SELECT '아직 젊군요';
	END
	ELSE
	BEGIN
		SELECT '다 됐습니다.';
	END
END
GO
EXEC usp_ages '이승기';
EXEC usp_ages '조용필';

 

예제 실행 화면

 

CREATE OR ALTER PROCEDURE usp_Case 
	@userName NVARCHAR(10)
AS
BEGIN
	DECLARE @Year INT
	DECLARE @Zodiac NVARCHAR(3)
	SELECT @Year = birthYear FROM userTBL
	 WHERE name = @userName;

	SET @Zodiac =
		CASE
		    WHEN (@Year % 12 = 0) THEN '원숭이'
			WHEN (@Year % 12 = 1) THEN '닭'
			WHEN (@Year % 12 = 2) THEN '개'
			WHEN (@Year % 12 = 3) THEN '돼지'
			WHEN (@Year % 12 = 4) THEN '쥐'
			WHEN (@Year % 12 = 5) THEN '소'
			WHEN (@Year % 12 = 6) THEN '호랑이'
			WHEN (@Year % 12 = 7) THEN '토끼'
			WHEN (@Year % 12 = 8) THEN '용'
			WHEN (@Year % 12 = 9) THEN '뱀'
			WHEN (@Year % 12 = 10) THEN '말'
			ELSE '양'
		END
	PRINT @userName + '의 띠 ==>' + @Zodiac;
END
GO

EXEC usp_Case '김한진';

 

예제 실행 화면

 

4.1) 저장 프로시저의 특징

  • SQL Server의 성능을 향상할 수 있다 : 동일한 저장 프로시저가 자주 사용될 경우에는 일반 쿼리를 반복해서 실행하는 것보다 SQL Server의 성능이 향상될 수 있다.
  • 유지관리가 간편하다
  • 모듈식 프로그래밍이 가능하다 : 한 번 저장 프로시저를 생성해 놓으면, 언제든지 실행이 가능하다.
  • 보안을 강화할 수 있다 : 사용자별로 테이블에 접근 권한을 주지 않고, 저장 프로시저에만 접근 권한을 줌으로써 좀 더 보안을 강화할 수 있다.
  • 네트워크 전송량의 감소 : 저장 프로시저 이름 및 매개변수 등 몇 글자의 텍스트만 전송하면 되므로 네트워크의 부하를 크게 줄일 수 있다.

4.2) 저장 프로시저의 종류

사용자 정의 저장 프로시저

  • T-SQL 저장 프로시저 : 사용자가 직접 CREATE PROCEDURE 문을 이용해서 생성한 프로시저
  • CLR 저장 프로시저 : .NET Framework 어셈블리의 클래스에 공용의 정적 메서드로 구현

확장 저장 프로시저

C 언어 등을 이용하여 데이터베이스에서 구현하기 어려운 것들을 구현한 저장 프로시저

 

시스템 저장 프로시저

시스템을 관리하기 위해서 SQL Server가 제공해주는 저장 프로시저로, SQL Server의 관리와 관련된 작업을 위해서 주로 사용된다. 시스템 저장 프로시저는 주로 'sp_' 접두어로 작성되어 있다. 그러므로 사용자가 생성한 프로시저는 'sp_' 접두어를 사용하지 않아야 시스템 저장 프로시저와 혼란을 방지할 수 있다.

 

4.3) 일반 T-SQL의 작동 방식

 

두 번째 실행 시라도 완전하게 똑같은 SQL 문의 실행 계획이 메모리에 없다면 왼쪽 그림의 과정을 다시 한다.

 

4.4) 저장 프로시저의 작동 방식

 

 

5) 사용자 정의 함수(Function)

함수는 저장 프로시저와 조금 비슷해 보이지만, 일반적인 프로그래밍 언어에서 사용되는 함수와 같이 복잡한 프로그래밍이 가능하도록 지원해준다. RETURN 문에 의해서 특정값을 돌려주는 기능을 한다. 저장 프로시저는 EXECUTE 또는 EXEC에 의해서 실행되지만 함수는 주로 SELECT 문에 포함되어서 실행(호출)된다.

 

CREATE OR ALTER FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
    [ =  default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END

 

5.1) 사용자 정의 스칼라 함수

RETURN 문에 의해서 하나의 단일 값을 돌려주는 함수를 스칼라 함수라고 한다.

 

ex)

CREATE OR ALTER FUNCTION ufn_getAge
(
	@bYear INT
)
RETURNS INT
AS
BEGIN
	DECLARE @age INT

	SET @age = YEAR(GETDATE()) - @bYear;

	RETURN @age

END
GO

SELECT name, birthYear, 
       dbo.ufn_getAge(birthYear) AS '나이',
       addr 
  FROM userTBL;

 

예제 실행 화면

 

SELECT dbo.ufn_getAge(1988);

 

예제 실행 화면

 

5.2) 사용자 정의 테이블 반환 함수

반환하는 값(RETURN 값)이 하나의 값이 아닌 테이블인 함수를 말한다. 인 라인 테이블 반환 함수(Inline Table-valued Function)와 다중 문 테이블 반환 함수(Multistatement Table-valued Function) 두 가지가 있다.

CREATE FUNCTION 함수이름(매개 변수)
       RETURNS TABLE
AS
       RETURN (
                단일 SELECT 문장;
       )
GO

 

ex)

CREATE FUNCTION ufn_getUser 
(	
	@height INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT userID AS '아이디',
	       name AS '이름',
		   height AS '키'
	  FROM userTBL
	 WHERE height >= @height
)
GO

SELECT * FROM dbo.ufn_getUser(170);

 

예제 실행 화면

 

저작자표시 (새창열림)

'스마트팩토리 > 데이터베이스' 카테고리의 다른 글

6. 커서, 트리거, 전체 텍스트 검색  (0) 2020.06.12
4. 조인, UNION, SQL 프로그래밍, 테이블, 뷰  (0) 2020.06.10
3. GROUP BY, WITH, INSERT, UPDATE, DELETE, 데이터 형식, 변수, 시스템 함수, JSON 데이터  (0) 2020.06.09
2. 운영 실습, SSMS, SELECT 문  (0) 2020.06.08
1. DBMS 개요, SQL Server 설치, 요구사항 분석과 설계 및 모델링, DB 생성 실습  (0) 2020.06.05
COMMENT
 

티스토리툴바