테이블 외에 다른 중요한 데이터베이스 개체로는 인덱스, 저장 프로시저, 함수, 트리거, 커서 등이 있다,
1. 인덱스
1.1 인덱스란?
인덱스(Index)란 첵에 있는 찾아보기(혹은 색인)와 같은 개념이다. 책의 내용 중 특정 단어를 찾고 싶으면 첫 장부터 마지막 장까지 한 쪽씩 전부 찾아보기보다는, 책 뒤쪽의 찾아보기를 펼쳐서 나와 있는 쪽수를 확인해 찾아가는 것이 훨씬 빠를 것이다.
데이터베이스 튜닝에서 가장 중요한 부분 중 하나가 인덱스이다. 즉 인덱스를 적절히 활용하고 있느냐에 따라 시스템의 성능이 몇 배, 심하게는 몇십 배 이상 차이가 날 수 있다.
인덱스는 테이블의 열 단위에 생성된다.(복합 인덱스도 있긴함)
테이블의 컬럼을 기본키로 설정하면 인덱스가 자동으로 생성된다.
이전 글에서 생성한 테이블을 활용한다.
select * from product_tb where productName = '세탁기'
현재는 제품 테이블에 행이 3개가 있기 때문에 세탁기에 해당하는 행을 찾으려면 3개의 행을 읽어서 데이터를 가져온다. 그런데 만약 제품이 100만개라면 100만개 행을 읽어서 그 중에 해당하는 1개 행을 가져와야 하며, 이는 엄청나게 부하가 걸리는 일이 된다. 이때 해결을 위한 것이 인덱스이다.
-- AdventureWorks2022.Production.Product 의 데이터를 복사해 index_tb 테이블에 입력한다. SELECT Name, ProductNumber, ListPrice, Size INTO index_tb FROM AdventureWorks2022.Production.Product; GO SELECT * FROM index_tb;
샘플 데이터를 복사한다. 이 데이터는 약 500개의 행이 존재한다.
SELECT * FROM index_tb WHERE Name = 'Minipump'
index_tb 에서 ‘minipump’ 를 검색 했을 때 실행 계획을 선택해서 쿼리가 어떻게 작동했는지 확인해본다.
위의 쿼리 결과는 테이블 스캔이다.
테이블 스캔은 인덱스를 사용하지 않고 테이블 전체를 스캔했다는 의미이다. 현재는 500건 정도의 데이터밖에 스캔하지 않지만, 수십만~수억 건의 데이터라면 시스템에 과부화가 생겼을 것이다.
1.2 인덱스 생성
CREATE INDEX idx_index_tb_Name ON index_tb(Name)
위의 쿼리를 실행해 index_tb 에 Name 컬럼을 인덱스로 설정한다.
SELECT * FROM index_tb WHERE Name = 'Minipump'
인덱스를 설정 후 다시 쿼리를 실행해 실행 결과를 확인해본다.
결과는 Index Seek를 사용했다. 이는 인덱스를 사용해 결과를 찾았다는 의미이다.
2. 뷰
뷰(View)란 가상의 테이블일고 생각하면 된다. 즉 사용자 입장에서는 테이블과 같게 보이지만, 뷰는 실제 행 데이터를 가지고 있지 않다. 실체는 없으며, 진짜 테이블에 링크된 개념이라고 생각하면 된다. 그래서 뷰를 SELECT 하면 진짜 테이블을 조회하는 것과 동일한 결과를 얻게 된다.
뷰는 사용자가 데이터베이스를 조회하기 위해 접근할 때 보안을 위해 사용하며, 재사용할 수 있는 장점이 있다.
-- VIEW 정의 CREATE VIEW uv_member_tb AS SELECT memberName,memberAddress FROM member_tb go SELECT * FROM uv_member_tb
뷰를 사용하면 관리자가 아닌 다른 사용자가 데이터에 접근할 때 전체 데이터를 보여줄 필요없어 보안에 효과적이다.
3. 저장 프로시저
저장 프로시저(Stored Procedure)는 SQL Server에서 제공하는 프로그래밍 기능이다. 즉 SQL문을 하나로 묶어 사용하는 기능이다. 실무에서는 SQL문을 하나하나 수행하기 보다 하나의 프로시저로 만들어 놓은 후 프로시저를 호출하는 방식을 사용한다.
SELECT * FROM member_tb WHERE memberName = '당탕이' SELECT * FROM product_tb WHERE productName = '냉장고'
만약 사진과 같은 두 값을 원한다면 각각의 쿼리를 실행해야 한다.
이 두 쿼리를 하나의 프로시저로 묶을 수 있다.
-- 프로시저 생성 CREATE PROCEDURE myProc AS SELECT * FROM member_tb WHERE memberName = '당탕이' SELECT * FROM product_tb WHERE productName = '냉장고' GO
CREATE 문을 활용해 프로시저를 생성한다.
EXEC myProc
EXEC (혹은 EXECUTE) 를 사용해 프로시저를 호출하면 같은 결과가 조회된다.
4. 트리거
트리거(Trigger)는 테이블에 부착되어서, 테이블에 INSERT, UPDATE, DELETE 작업이 발생하면 실행되는 코드를 의미한다.
만약 회원 탈퇴가 발생할 때, 단순히 회원 테이블에서 정보를 삭제한다면 탈퇴한 회원의 정보를 알 수가 없을 것이다. 이 때 데이터가 삭제되기 전에 다른 곳에 먼저 복사를 해두면 될 것이다. 이때 트리거를 생성하면 자동으로 삭제 전 데이터 백업을 실행할 수 있다.
INSERT INTO member_tb values('Figure','연아', '경기도 군포시 당정동')
INSERT 쿼리를 실행해 회원 테이블에 데이터를 추가한다.
이제 트리거를 만들어 회원 테이블에 삭제가 일어날 때 백업이 되도록 만들어보자.
우선 백업이 될 수 있는 테이블을 생성한다.
CREATE TABLE delete_Member_tb( memberID char(8), memberName nchar(5), memberAddrerss nchar(20), deletedDate date )
그리고 트리거를 생성한다.
CREATE TRIGGER trg_delete_member_tb -- 트리거 이름 ON member_tb -- 트리거에 적용될 테이블 AFTER DELETE -- 삭제 후 작동 AS INSERT INTO delete_member_tb SELECT memberID, memberName,memberAddress,GETDATE() FROM deleted
member_tb 에서 삭제가 일어난 후 delete_member_tb에 INSERT 되는 트리거이다.
DELETE member_tb WHERE memberName = '당탕이'
member_tb 을 실행하면 두 개의 행이 적용된 것을 확인할 수 있다.
SELECT * FROM member_tb SELECT * FROM delete_member_tb
member_tb 에서 삭제된 데이터가 delete_member_tb에 백업된 것을 확인할 수 있다.
Share article