9장. 인덱스
책의 뒷부분에 있는 찾아보기와 비슷한 개념이다. 지금 우리가 하고있는 예제에는 데이터가 작기때문에 없어도 별 차이가 없지만, 대량의 데이터에는 인덱스가 있어야만 데이터를 빠른 시간에 검색할 수 있다.
인덱스의 장점
1. 검색하는 속도가 매우 빨라질 수 있다(반드시 그런것은 아니다 왜냐면 잘못된 인덱스를 생성하면 성능이 떨어진다)
2. 그 결과 시스템의 부하가 줄어들어서, 결국 시스템 전체의 성능이 향상된다.
인덱스의 단점
1. 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간이 필요해진다.(대략 DB의 10% 내외의 공간이 추가로 필요하다)
2. 인덱스를 생성하는데 시간이 많이 소요될 수 있다.(우리 실습은 몇 초 안걸리는데 자료가 많으면 분단위까지 될 수 있다.)
3. 데이터의 변경작업(Insert, Update, Delete)이 자주 일어날 경우에는 성능이 많이 나빠질 수도 있다.
<-> Select와의 반대개념
인덱스 종류
1. 클러스터형 인덱스 : 영어사전과 비슷한 개념
2. 비클러스터형 인덱스 : 일반 책의 '찾아보기'와 같은 개념
특징
- 클러스터형 인덱스는 테이블당 1개만 생성
- 비클러스터형 인덱스는 테이블당 여러 개 생성
- 클러스터형 인덱스는 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.
- 제약조건 없이 테이블 생성시에 인덱스를 만들 수 없다.
- 인덱스가 자동생성되기 위한 열의 제약조건은 Primary Key과 Unique 뿐이다.
이미지의 새 인덱스 창에서, '고유'는 유니크값을 지정해주는건데 체크하면 난리난다.(예를들어 동명이인이 있을 수도 있기때문에 체크를 하지말자)
인덱스의 내부작동
B-Tree(Balanced Tree, 균형 트리)
1. 범용적으로 사용되는 데이터 구조이다.
2. 인덱스를 표현할 때 많이 사용됨.
3. 데이터의 검색(Select)시에 뛰어난 성능을 보일 수 있음
4. 데이터의 변경(Insert, Update, Delete)시에 성능이 나빠짐
클러스터형 인덱스의 특징
1. 클러스터형 인덱스의 생성시에는 데이터페이지 전체를 다시 정렬하게된다. 그러므로 클러스터형 인덱스를 생성은 심각한 시스템 부하를 줄 수 있다.
2. 클러스터형 인덱스는 인덱스 자체의 리프 페이지가 곧 데이터이다
3. 비 클러스터형보다 검색속도는 빠르다. 하지만, 데이터의 입력.수정/삭제는 더 느리다
4. 클러스터 인덱스는 성능이 좋지만, 테이블에 한 개밖에 생성하지 못한다. 그러므로, 어느 열에 클러스터형 인덱스를 생성하느냐에 따라서 시스템의 성능이 달라 질 수 있다.
비클러스터형 인덱스의 특징
1. 비클러스터형 생성시에는 데이터 페이지는 그냥 둔 사앹에서 별도의 페이지에 인덱스를 구성한다.
2. 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)다. 클러스터형보다 검색 속도는 더 느리지만, 데이터의 입력/수정/삭제는 덜 느리다.
3. 비클러스터형 인덱스는 여러 개 생성할 수가 있다. 하지만, 함부로 남용할 경우에는 오히려 시스템 성능을 떨어뜨리는 결과를 초래할 수 있으므로, 꼭 필요한 열에만 생성하는 것이 좋다.
인덱스를 만들었는데 잘 작동하는지 볼 수도 있고, 다시 재정렬을 해야할 필요가있다.
결론 : 인덱스를 생성해야하는 경우와 그렇지 않은 경우
- 인덱스는 열 단위에 생성된다.
- Where 절에서 사용되는 컬럼을 인덱스로 만든다.
- Where 절에 사용되더라도 자주 사용해야 가치가 있다.
- 데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효용이없다.
- 외래키가 사용되는 열에는 인덱스를 되도록 생성해 주는 것이 좋다.
- JOIN에 자주 사용되는 열에는 인덱스를 생성해 주는 것이 좋다.
- INSERT/UPDATE/DELETE가 얼마나 자주 일어나는 지를 고려한다.(PK가 클러스터형으로 걸리니까 나둬도된다)
- 클러스터형 인덱스는 하나만 생성할 수 있다.
- 클러스터형 인덱스는 테이블에 아예 없는 것이 좋은 경우도 있다.(이런 경우는 잘 없다고함)
- 사용하지 않는 인덱스는 제거하자.(만들때마다 HDD에 자리를 차지하고 성능에 좋은게 하나도없다)
계산열에도 인덱스를 활용할 수 있다.
10장. 트랜잭션
데이터 베이스는 1개인데 저장된 폴더에는 2개가 있다.
.mdf
_log.ldf
데이터베이스의 기본 구조와 SQL 작동방식
데이터베이스의 물리적 실체
데이터베이스는 물리적으로 파일임
기본적으로 .mdf, .ldf 두 파일이 생김
데이터 파일
.mdf 또는 .ndf로 생성됨
이 파일에는 데이터베이스 개체(테이블, 인덱스 등)와 그 행 데이터가 저장됨
트랜잭션 로그파일
"*.ldf"로 생성됨
정전 등의 응급상황에서 입력된 데이터가 완전하도록 함
전부 되거나 전부 안되거나(All or Nothing)을 지원함.
SQL문이 동작하는 데이터베이스의 간단한 구조도
SELECT 문 1->2->4
UPDATE문 1->3->2->4
트랜잭션 개념과 작동방식
하나의 논리적 작업단위로 수행되는 일련의 작업
SQL문(SELECT/INSERT/UPDATE/DELETE)의 묶음
트랜잭션 처리과정 1이 완료된 후, 정전이 되고 다시 전원이 들어온 경우
한 단위의 트랜잭션은 모두 처리되거나, 모두 처리 되지 않도록 DBMS가 관리해 준다.
구문형식)
BEGIN TRANSACTION(또는 BEGIN TRAN)
SQL문장들
COMMIT TRANSACTION(또는 COMMIT TRAN 또는 COMMOT WORK)
자동으로 COMMIT을 해주기위해 옵션을 조정해보자
이때 상사님께서 num = 10에 price = 100, amount = 5로 바꾸라고 명령했을때를 생각해보자
알고보니 바꾸려고 의도했던 num값이 num = 10이 아니고 num = 9이었다고 한다.
그럼 당황하지 말고 BEGIN TRAN을 실행한 다음 롤백을 실행시켜서 num=10의 값을 되돌릴 수 있다.
이제 num = 9의 값을 price = 100, amount = 5로 바꿔보자
BEGIN TRAN을 실행한 다음에 COMMIT을 실행해주면 다시 BEGIN TRAN, ROLLBACK을 실행해줘도 값이 변하지 않는다.
트랜잭션의 특성
원자성(Atomicity)
트랜잭션은 분리할 수 없는 하나의 단위이다
일관성(Consistency)
트랜잭션에서 사용되는 모든 데이터는 일관되어야 한다
격리성(Isolation)
현재 트랜잭션이 접근하고 있는 데이터는 다른 트랜잭션에서 격리되어야 한다는 것을 의미한다
영속성(Durability)
트랜잭션이 정상적으로 종료된다면 그 결과는 시스템 오류가 발생하더라도 시스템에 영구적으로 적용된다.
트랜잭션의 문법과 종류
BEGIN TRANSACTION(또는 BEGIN TRAN)
SQL문장들
COMMIT TRANSACTION(또는 COMMIT TRAN 또는 COMMOT WORK)COMMIT TRAN과 COMMIT WORK는 동일하게 사용되며, ROLLBACK TRAN과 ROLLBACK WORK도 마찬가지다.
자동 커밋 트랜잭션(Autocommit Transaction)
각 쿼리마다 자동적으로 BEGIN TRAN과 COMMIT TRAN이 붙여진다. SQL Server가 디폴트로 사용한다.
사용 예시)
UPDATE 문
GO
INSERT 문
-- 밑으로 변형
BEGIN TRAN
UPDATE 문
COMMIT TRAN
Go
BEGIN TRAN
INSERT 문
COMMIT TRAN참고로 지금까지 사용해온 대부분의 쿼리도 자동 커밋 트랜잭션이 작동해왔던 것이다.
명시적 트랜잭션(xplicit Transaction)
직접 BEGIN TRAN과 COMMIT TRAN을 써주는 것을 말한다. 대개는 BEGIN TRAN ... END TRAN만 붙여주면 된다.
암시적 트랜잭션(Implicit Transaction)
생략..
4장. 데이터베이스 모델링
프로젝트 진행 단계
프로젝트 : '현실세계의 업무를 컴퓨터 시스템으로 옮겨놓는 일련의 과정' 또는 '대규모 프로그램을 작성하기 위한 전체 과정'이다. 소프트 웨어 분야의 몇몇 개발자에 의존하는 고질적인 문제 때문에 '소프트웨어 개발 방법론'이 대두됨. 이러한 분야를 '소프트웨어 공학'이라 부름.
폭포수 모델
각 단계가 명확하게 구분되는 장점이있지만, 문제점 발생시 앞 단계로 돌아가기가 어렵다는 단점이있다. 대규모 프로젝트 일수록, 업무 분석과 시스템 설계에 최소 50% 이상을 할당하는 것이 좋다.
정보 공학적 방법론
1980년대에 정보 시스템이 단순 업무 지원 뿐 아니라 경영 전략을 창출하는 경영정보 시스템(MIS, Managemonet Information Systeam)으로 진화하여 방법론이 경영 전략을 잘 수용하기 위함'에 맞춰 발전하게 되었다.
따라서, 정보 공학 방법론은 데이터를 우선적으로 개발하고, 문제 영역을 세분화해서 Top-Down 방식으로 전개하여 기존보다 빠르게 결과물을 낼 수 있다는 특징을 가지고 있다.(폭포수 모델이랑 차이는 없다)
객체 지향 방법론
개발 단계에서 반복과 점증적(Iterativve and Incremental) 모델을 사용하여 사용자의 요구사항을 반영하고, 모든 단계를 유기적으로 협력시켜 전체 프로세스를 효율적이게 사용할 수 있다.
CBD(Component Based Development)분석 방법론
CBD(Component Based Development) 분석 방법론은 문제를 조각으로 나누어 각각 컴포넌트를 생성한 후, 다시 조합하는 재사용성에 초점을 둔 방식이다.
아무리 복잡한 시스템이어도 단계별로 나누어 생각하기 대문에, 전체 시스템이나 프로그램에 영향을 주지 않고 빠르게 문제를 해결할 수 있다는 특징이 있다.
애자일 방법론
지금 가장 많이 쓰이는 방법론이며, 이전 방식들은 개발 과정에서 과도한 문서화나 형식적인 절차에 많은 비용을 치르게되면서 최근에 애자일(Agile) 방법론이라는 적응적(Adaptive) 방법론이 새롭게 떠올랐다.
애자일 방법론은 고객과의 협력을 중시하고, 프로세스나 도구에 국한되지 않는 자기 적응적(self-adaptive) 방식을 사용하는데, 일정한 주기를 가지고 프로토 타입(Prototype)을 만들어 내기 때문에 고객의 요구사항을 반영하기 쉽고, 변화에도 빠르게 대응할 수 있다는 장점이 있다.
방법론 이미지와 설명 출처 : 잼코딩학원
데이터베이스 모델링
데이터베이스 다이어그램 우클릭 -> 새 데이터베이스 다이어그램을 클릭해서 빈창에 우클릭 -> 새 테이블을 만들어보자
두 테이블을 외래 키 관계를 맺어주자
INSERT 및 UPDATE 사양에 삭제 규칙이나 업데이트 규칙을 동작한다고 체크해놓으면 부모창이 바뀌면, 자식창도 같이 바뀌거나, 부모창이 삭제하면 자식도 삭제되기때문에 함부로 건들면 위험하다.
사용자를 추가했을때 어떤 일이 벌어질까?
보안적인 문제를 해결할 수 있고 정해진 서버의 데이터만 확인할 수 있게 해준다.
11장. 저장 프로시저와 사용자 정의 함수
저장 프로시저란 SQL Server에서 제공 되는 프로그래밍 기능이다. 한마디로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용한다.
helpindex 쿼리문의 집합처럼 우리도 개발해보자
-- ================================================
/*
유저 테이블을 조회하는 사용자 저장 프로시저
created date : 2020.06.11. 15:35
caeated user : SDW
company : HangaramIT
*/
--===============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <SDW>
-- Create date: <2020.06.11 15:35>
-- Description: <userTBL에서 사용자 조회하는 SP>
-- =============================================
CREATE OR ALTER PROCEDURE usp_User
-- CREATE OR ALTER usp_User가 없으면 생성, usp_User가 있으면 수정
AS
BEGIN
SELECT * FROm userTBL
END
GO
-- 다시 실행하면 이미 있기때문에 오류가뜬다
-- ================================================
/*
유저 테이블을 조회하는 사용자 저장 프로시저
created date : 2020.06.11. 15:35
caeated user : SDW
company : HangaramIT
*/
--===============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <SDW>
-- Create date: <2020.06.11 15:35>
-- Description: <userTBL에서 사용자 조회하는 SP>
-- =============================================
CREATE OR ALTER PROCEDURE usp_User
-- CREATE OR ALTER usp_User가 없으면 생성, usp_User가 있으면 수정
@userID VARCHAR(8) = 'SSK',
@userHeight INT = 186,
@outValue INT OUTPUT -- 위의 두 줄은 IN, 이 구문은 OUTPUT
AS -- 실행결과는 다른 애들한테 넘겨줄때 쓰는 매개변수
-- C#의 return에 들어간다고 생각하면됨
BEGIN
SELECT @outValue = birthYear FROM userTBL
WHERE userID = @userID
AND height >= @userHeight; -- 입력한 키가 테이블의 키보다 작으면 출력
END
GO
-- 다시 실행하면 이미 있기때문에 오류가뜬다
이름을 입력하면 나이에 따라서 젊은지 안젊은지 출력하는 코드를 작성해보자
CREATE OR ALTER PROC usp_ages -- 프로시저를 PROC로 줄여서씀
@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
CASE문 연습예제(저장 프로시저를 새로 만들어서 작성해보자)
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 '말'
WHEN (@Year%12 = 11) THEN '양'
END
PRINT @userName + '의 띠 ==> ' + @Zodiac + '띠.';
END
GO
저장 프로시저의 특징
SQL Server의 성능을 향상시킬 수 있다.
동일한 저장 프로시저가 자주 사용될 경우에는 일반 쿼리를 반복해서 실행하는 것보다 SQL Server의 성능이 크게 향상 될 수 있다.
모듈식 프로그래밍이 가능하다
저장프로시저를 생성해 놓으면, 언제든지 실행이 가능하다
보안을 강화할 수 있다.
사용자 별로 테이블에 접근 권한을 주지 않고, 저장 프로시저에 접근 권한을 줌으로써 좀 더 보안을 강화한다.
네트워크 전송량을 감소시킨다.
저장 프로시저의 이름 및 매개변수 등 몇 글자의 텍스트만 전송하면 되므로 네트워크의 부하를 줄일 수 있다.
저장 프로시저의 종류
사용자 정의 저장 프로시저
1. T-SQL 저장 프로시저
사용자가 직접 CRETE PROCEDUER 문을 이용해서 생성한 프로시저
2. CLR 저장 프로시저
닷넷 프레임워크 어셈블리의 클래스에 공용의 정적 메소드로 구현
확장 저장 프로시저
C 언어 등을 이용하여 데이터베이스에서 구현하기 어려운 것들을 구현한 저장 프로시저
시스템 저장 프로시저
1. 시스템을 관리하기 위해서 SQL Server가 제공해주는 저장 프로시저로, SQL 서버의 관리와 관련된 작업을 위해서 주로 사용한다.
2. 주로 sp_'접두어로 작성되어 있다. 그러므로, 사용자가 생성한 프로시저는 'sp_' 접두어를 사용하지 않아야 시스템 저장 프로시저와 혼란을 방지할 수 있다.
저장 프로시저의 작동
일반 T-SQL의 작동
아래의 왼쪽그림은 일반적인 T-SQL 문을 처음으로 실행했을 경우의 순서이다.
저장 프로시저 작동방식
위드 리컴파일
생략
사용자 정의 함수
저장프로시저와 조금 비슷해 보이지만, 일반적인 프로그래밍 언어에서 사용되는 함수와 같이 복잡한 프로그래밍이 가능하다. 함수는 RETURN 문에 의해서 특정 값을 되돌려준다. 저장 프로시저는 'EXEC"에 의해서 실행되지만, 함수는 주로 'SELECT'문에 포함되어 실행된다.(예외도 있음)
사용자 정의 함수의 생성/수정/삭제
사용자 정의 함수 중에서 스칼라 함수를 정의하는 방법
출생년도를 입력하면 나이가 출력되는 함수를 생성해보자.
CREATE OR ALTER FUNCTION ufn_getAge
(
@bYear INT
)
RETURNS INT -- 리턴값은 정수형
AS
BEGIN
DECLARE @age INT
SELECT @age = YEAR(GETDATE()) - @bYear;
RETURN @age
END
GO
-- 현재의 연도 YEAR(GETDATE())에서 입력된 출생년도를 뺀 값(즉, 본인의 나이)을 돌려주는 함수
사용자 정의 테이블 반환 함수
리턴하는 값이 테이블인 함수이다. 인 라인 테이블 반환 함수(Inline Table-valued Function)와 다중 문 테이블 반환 함수(Multistatement Table-valued Function) 두 가지가 있다.
인라인 테이블 반환 함수
간단히 테이블을 돌려주는 함수로 뷰와 비슷한 역할을 한다. 인라인 테이블 반환 함수에는 별도의 내용이 없으며 단지 SELECT 문만 사용되어 그 결과 집합을 돌려줄 뿐이다.
사용예제)
입력한 값보다 키가 큰 사용자들을 돌려주는 함수를 정의해보자
CREATE FUNCTION ufn_getUser
(
@height INT
)
RETURNS TABLE
AS
RETURN
(
SELECT userID as'아이디',
name AS' 이름',
height AS '키'
FROM userTBL
WHERE height >= @height -- 입력한 @height값보다 height값이 큰 사람을 출력
)
GO
스키마 바운드 함수
함수에서 참조하는 테이블, 뷰 등이 수정되지 못하도록 설정한 함수이다.
스키마 바운드 함수의 생성은 옵션에 'WITH SCHEMABINDING'을 사용한다
테이블 변수
일반적인 변수의 선언처럼 테이블 변수도 선언해서 사용한다
테이블 변수의 용도는 주로 임시테이블의 용도와 비슷하게 사용된다.
사용자정의 함수의 제약사항
사용자 정의함수 내부에 TRY--CATCH 문을 사용할 수 없다.
사용자 정의 함수 내부에 CREATE/ALTER/DROP 문을 사용할 수 없다.
오류가 발생하면 즉시 함수의 실행이 멈추고 값을 반환하지 않는다.
다음 시간에는 커서부터 시간되면 XML까지 달려보자,
'개발자과정준비 > 데이터베이스(DB)' 카테고리의 다른 글
| [DB] 6. 커서(Cursor), 트리거(trigger), 전체 텍스트 검색(Full text Search Service) (0) | 2020.06.13 |
|---|---|
| [DB] 4. JOIN, UNION, SQL프로그래밍, 테이블, 뷰 (0) | 2020.06.10 |
| [DB] 3. ROLLUP(), WITH절, INSERT문, 데이터형식의 종류, JSON 데이터, PIVOT (0) | 2020.06.09 |
| [DB] 2. 운영 실습, SELECT 문 (0) | 2020.06.08 |
| [DB] 1. DBMS 개요, DB 생성 실습 (0) | 2020.06.08 |
