[SQL 스터디] SQL 첫걸음 스터디 - 2

하루 30분 36강으로 배우는 완전 초보의 SQL 따라잡기

SQL 첫걸음

 2번째 SQL 스터디 내용을 정리해보겠습니다.

2차 스터디 정리

1. 데이터베이스 객체

테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것을 데이터베이스 객체라고 말합니다. 즉 데이터베이스 내의 객체를 말하는 것입니다.

데이터베이스 객체는 종류와 관계없이 이름 중복이 허용되지 않지만, 스키마가 다르다면 가능합니다. 이 데이터베이스 객체를 '스키마 객체' 라고 부르기도 합니다.

2. DELETE FROM 테이블명 VS TRUNCATE TABLE

테이블내의 레코드를 삭제하는 명령에는 DELETE 와 TRUNCATE 두 가지 방법이 있습니다. 

DELETE
DELETE 명령어를 이용하면 한 레코드씩 삭제를 진행하며 레코드 단위로 삭제했다는 로그를 트랜잭션 로그에 남깁니다. 그렇기 때문에 특정 레코드를 지우는 것이 아닌 전체 테이블의 내용을 지우거나 많은 레코드를 지워야 할 때는 TRUNCATE 명령어를 고려해보는 것도 좋습니다.
또한, DELETE 명령어는 삭제된 레코드만큼 auto increment pk 값을 초기화시키는 것이 아니라 그대로 이어갑니다. 예를 들어 아래의 이미지를 보겠습니다.

(실행전) (실행후)

첫번째 (실행전) 이미지를 보면 1번 hyeona1 데이터와 2번 hyeona2 데이터가 있었습니다. 이 상태에서
DELETE FROM table1 WHERE pk=2;
명령을 실행하면 2번 hyeona2 가 삭제되겠죠. 그 후에 hyeona3 을 insert 하면 pk 는 2가 아닌 3이 됩니다. 이것이 TRUNCATE 와의 차이입니다.
TRUNCATE
TRUNCATE 명령어는 원래 테이블 데이터가 저장되어 있던 페이지를 할당 해제하고 그 내용을 트랜잭션 로그에 남깁니다. 이것이 한 레코드씩 지우며 해당 기록을 로그로 남기는 DELETE 보다는 빠를 수 밖에 없는 이유입니다. 그러나 TRUNCATE 명령어는 외래키로 참조되어 있거나, 인덱싱 되어있는 뷰에 참여하고 있거나, 테이블 복제로 생긴 테이블에서는 사용할 수 없습니다.
또한 TRUNCATE 명령어를 실행하고 나면 auto increment 로 설정해둔 pk 정보도 남아있지 않습니다. 따라서 위의 DELETE 명령어의 예시였던 (실행전) 이미지 상태의 테이블에서
TRUNCATE TABLE table1;
를 실행한 후에 레코드를 추가하면 pk 는 다시 1부터 시작하게 됩니다.

3. 인덱스

인덱스는 검색속도를 향상하기 위해 사용하며 인덱스 또한 데이터베이스 객체입니다. 보통 인덱스는 B-Tree 알고리즘으로 구현되며 (모두 그런것은 아닙니다.) 레코드가 추가되거나 삭제될 때 B-Tree 를 최신상태로 갱신하는 시간이 소요됩니다. 따라서 무분별한 인덱스의 사용은 쿼리 처리 속도를 떨어뜨릴 수도 있습니다. 

인덱스 작성을 통해 쿼리의 성능을 향상시켰다고 생각할 수도 있는데요. 이 때, 실제로 인덱스를 사용해 검색하는지를 확인하는 방법에는 EXPLAIN 이라는 명령이 있습니다. EXPLAIN 뒤에 확인해보고 싶은 쿼리를 입력하면 어떤 상태로 실행되는지를 데이터베이스가 설명해줍니다. (모든 데이터베이스가 지원해주는 것은 아닙니다.)

인덱스에 대한 자세한 내용은 다음 스터디 글에서 정리하겠습니다.

4. 뷰

뷰는 데이터베이스 객체로 등록할 수 없는 SELECT 명령을 객체로서 이름을 붙여 관리할 수 있도록 하는 것을 말합니다. 어려운 말이지만 뷰를 생성할 때 입력한 SELECT 명령을 관리하는 객체가 바로 뷰입니다. 

뷰
뷰는 가상테이블입니다. SELECT 명령을 실행한 결과값을 테이블로 저장하고 있는게 아니라 단지 SELECT 명령을 저장하고 있는 것입니다. 따라서 뷰를 참조할 때마다 뷰에 등록되어 있는 SELECT 명령이 실행되는 것입니다. 저도 처음에는 테이블처럼 제가 실행한 SELECT 명령에 대한 결과값을 저장하고 있는 줄 알았는데 이번 스터디를 통해 깨닫게 되었습니다.
Materialized View
위에서 말씀드린 뷰의 단점을 보완하기 위해 사용하는 것이 바로 Materialized View 입니다. SELECT 명령에 해당하는 결과값이 많은 뷰를 집계처리할 때 사용한다면 처리속도가 많이 떨어질 수 밖에 없겠죠. 그러나 Materialized View 는 처음 참조되었을 때의 데이터를 저장해둔 후, 다시 참조할 때 이전에 저장해 두었던 데이터를 그대로 사용합니다. 뷰에 지정된 테이블의 데이터가 변경된 경우에는 SELECT 명령을 재실행해서 데이터를 다시 저장합니다.
따라서 뷰에 지정된 테이블의 데이터가 자주 변경되지 않는 경우라면 기본 뷰보다는 Materialized View 를 고려해보는 것도 좋을 것 같습니다. (MySQL 에는 Materialized View 가 존재하지 않습니다.)