3) 사용자가 발생한 SQL과 그 실행계획시 라이브러리캐시(프로시저캐시)에 캐싱되어 있는지 확인
4) 캐싱되어 있다면 소프트파싱, 캐싱되어있지 않다면 하드파싱
으로 구성된다.
* 소프트파싱: SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어가는 경우
* 하드파싱: SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고나서 실행단계로 넘어가는 경우
* 라이브러리캐시는 해시구조로 엔진에서 관리된다. SQL마다 해시값에 따라 여러 해시 버킷으로 나뉘며 저장되고, SQL을 찾을 때는 SQL 문장을 해시 함수에 적용하여 반환되는 해시값을 이용해서 해시 버킷을 탐색한다.
2. 규칙기반 옵티마이저 (RBO)
실행계획을 정해진 룰에 따라 만든다.
룰은 데이터베이스 엔진마다 여러 가지가 있다.
예를 들어서 오라클의 RBO는 다음과 같다.
순위
액세스 경로
1
Single Row by Rowid
2
Single Row by Cluster Join
3
Single Row by Hash Cluster Key with Unique or Primary Key
4
Single Row by Unique or Primary Key
5
Clustered Join
6
Hash Cluster Key
7
Indexed Cluster Key
8
Composite Index
9
Single-Column Indexes
10
Bounded Range Search on Indexed Columns
11
Unbounded Range Search on Indexed Columns
12
Sort Merge Join
13
MAX or MIN of Indexed Column
14
ORDER BY on Indexed Column
15
Full Table Scan
자세한 과정은 잘 모르겠지만, 크게 보면 엑세스를 할 때에 인덱스를 이용하느냐, 전체 테이블을 스캔하느냐 등으로 나눌 수 있다. 1번부터 순서대로 맞는 경우에 진행하며, 아래로 갈수록 data가 흐트러져서 저장되기 때문에 비용이 많이 든다.
그리고 요즘에는 대부분 RBO보다 CBO를 이용한다.
3. 비용기반 옵티마이저 (CBO)
비용을 기반으로 최적화를 수행하는 방식이다. 이때 비용이란 쿼리를 수행하는데 소요되는 일의 양 또는 시간 예상치이다.
딕셔너리에서 테이블과 인덱스를 통해 레코드 개수, 블록 개수, 평균 행 길이, 칼럼 값의 수, 칼럼 값 분포, 인덱스 높이, 클러스터링 팩터 등의 통계값을 기반으로 비용을 예측하는 방식이다. 이 예측된 비용에 따라 최적의 실행 계획을 도출한다. 최근에는 추가적으로 하드웨어적 특성을 반영한 시스템 통계정보 (CPU 속도, 디스크 I/O 속도 등)까지 이용한다.
4. SQL 실행계획
실행 계획은 SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미한다. 즉, SQL을 어떤 순서로 어떻게 진행할 지 결정한다는 것이다.
실행 계획의 구성요소는 다음 다섯 가지가 있다.
조인 순서 (Join Order) : JOIN 수행에서 어떤 테이블이 먼저 조회되는가
조인 기법 (Join Method) : loop, merge, sorting 등
액세스 기법 (Access Method) : Index / Full table 등
최적화 정보 (Optimization Information) : 알고리즘
연산 (Operation) : 크다, 작다, 함수, MAX, MIN 등
5. INDEX
INDEX는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조이다.
CREATE INDEX IDX_### ON db, table (column);
ALTER TABLE db.table ADD INDEX IDX_###;
두 코드 모두 column에 대한 INDEX를 생성하는 코드이다.
ALTER TABLEdb.tableDROP INDEXIDX_###;
테이블에 있는 인덱스를 삭제하는 코드이다.
EXPLAIN SELECT * FROM db.table;
실행 계획을 확인하는 코드이다.
예제)
DESC kaggle.titanic;
EXPLAIN SELECT * FROM kaggle.titanic WHERE `Age` = 23;
>> Age가 23인 데이터를 찾기 위해 418개의 rows를 모두 검색하는 것이 플랜임을 알 수 있다.
[INDEX 생성 후]
DESCkaggle.titanic;
Age에 인덱스가 생성된 것을 확인할 수 있다.
EXPLAIN SELECT*FROMkaggle.titanicWHERE`Age` = 23;
>> IDX_AGE를 사용해서 reference를 통해서 쿼리를 실행하는 것이 플랜임을 알 수 있다.
count( ) 는 투플의 개수를 세는 함수이다. FROM - 뒤에 WHERE - 을 붙여 조건을 걸 수 있다.
정확한 표현인지는 모르겠지만..
예제에 나온 SELECT COUNT(*) FROM 'gender_submission'; 은 'gender_submission' 테이블에 있는 전체 투플 수 (rows)를 count해준다.
SELECTCOUNT(*) FROMtest A JOIN gender_submission B ON A.PassengerId = B.PassengerId ;
'test' 테이블의 'PassengerId'와 'gender_submission'테이블의 'PassengerId'가 같을 경우에, 'test'테이블과 'gender_submission'테이블을 결합한다는 의미이다. 그리고, 그 결합한 것의 투플 수 (row 수)를 세는 코드이다.
unsigned는 부호가 없다는 뜻이다. 예를 들어 tinyint의 경우, (signed) tinyint의 범위는 -128~127이며,
unsigned tinyint의 범위는 0~255이다.
NULL, NOT NULL은 NULL값이 될 수 있냐 없냐를 정해주는 것이다. 예제의 경우 학번과 이름은 NULL값이 될 수 없도록 했다.
varchar( )의 괄호 속 숫자는 최대로 들어갈 수 있는 byte 값이다.
`leave_yn`는 휴학여부 속성으로 0과 1로 구분되고, 투플값이 없을 경우 default 값은 0이 들어가도록 했다.
`last_update`는 업데이트 시각으로, 직접 적는 것이 아니라 CURRNET_TIMESTAMP를 이용하여 자동으로 저장된다.
이 테이블의 기본키는 `student_id`로, 학번만 가지고도 투플들을 구분할 수 있다는 뜻이다.
MySQL의 스토리지 엔진으로 주로 MyISAM과 InnoDB가 사용된다.
스토리지 엔진은 데이터베이스 엔진이라고도 불리며, RDBMS가 데이터베이스에 대해 데이터를 삽입, 추출, 업데이트, 삭제하는 데 사용하는 기본 소프트웨어 컴포넌트이다.
MyISAM은 항상 테이블에 ROW COUNT를 가지고 있기 때문에 SELECT 명령 시 속도가 빠르고, '풀텍스트 인덱스'를 지원하는데, 이는 자연 언어를 이용해 검색할 수 있는 특별한 인덱스로 모든 데이터 문자열의 단어를 저장한다는 것이다. 이때문에 Read only 기능이 많은 서비스일수록 효율적으로 사용할 수 있는 엔진이다. 단점으로는, row level locking을 지원하지 못해서 select, insert, update, delete 명령 시 해당 테이블 전체에 locking이 걸린다는 것이다. 그래서 갱신이 많이 필요한 경우에는 유용하지 못하다.
InnoDB는 MyISAM과 대조적인 엔진으로, 우선 row level locking이 지원된다는 장점이 있고, 트랜잭션 처리가 필요한 대용량 데이터를 다룰 때 효율적이다. 데이터의 변화가 많은 서비스에 적합하다. 또한, 유일하게 외래키를 지원하는 표준적인 스토리지 엔진이다.단점으로는, 풀텍스트 인덱스를 지원하지 않는다는 것이 있다.
주로 InnoDB를 많이 사용한다고 하고, 이번 예제에서도 InnoDB를 사용하였다.
INSERT INTO `university`.`student` (student_id, name, address, birth_date, major_id, enterance_year, leave_yn) VALUES (2016001, '정소정', '서울시 서초구 방배동 911-1', '19971101', 111, 2016, 0), (2017053,'성민경','경상북도 대구 수성구 삼익 1-1','19980412',111,2017,1), (2018111,'노규희','충청남도','19991204',130,2018,1), (2017105,'박성희','강원도','19980908',130,2017,0);
student 테이블에 투플들을 삽입하는 코드이다.
INSERT INTO로 속성을 한 번 나열하고, VALUES를 통해 각각의 개체들을 추가한다.
select * from `university`.`student`;
select from은 테이블에서 정보를 가져온다는 뜻이고, *은 테이블에 있는 모든 데이터를 가져온다는 뜻이다.
따라서 이 코드를 실행하면 university.student에 있는 4개의 투플들을 보여준다.
select * from `university`.`student` where student_id = 2016001;
where은 조건의 개념으로, 이 경우에는 학번이 2016001인 투플의 모든 데이터를 가져온다는 뜻이 된다.
select student_id, name from `university`.`student` where major_id = 111;
이 경우에는 selcet student_id, name 이므로, 전공번호가 111인 투플의 학번과 이름을 가져온다.
UPDATE `university`.`student` SET address = '부산특별시 해운대 비치333-3', leave_yn = 1 WHERE student_id = 2016001;
update는 말 그대로 테이블을 새로운 정보로 업데이트한다는 뜻이다.
이 경우, 학번이 2016001인 투플의 주소와 휴학여부 정보를 변경하게 된다.
select * from university.student where student_id = 2016001;
DELETE FROM university.student WHERE student_id = 2016001;
select * from university.student where student_id = 2016001;
select * from university.student;
select문은 마찬가지로 정보를 가져오는 코드이고,
DELETE문은 university DB의 student 테이블에서 student_id가 2016001인 투플을 삭제하는 코드이다.
CREATE TABLE `university`.`class` ( `class_id` int unsigned NOT NULL , `name` varchar(20) NOT NULL , `professor_id` int NOT NULL , `major_id` smallint NULL , `duration` smallint NULL , `type` tinyint NULL , `start_time` char(2) NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (class_id) ) ENGINE = InnoDB;
두번째로 universityDB 안에 'class'라는 테이블을 생성한 것이다.
속성으로는 class_id, name, professor_id, major_id, duratin, type, start_time, last_update가 있고 각각의 도메인이 달려있다.
INSERT INTO `university`.`registerClass` (student_id, class_id) VALUES (2017053, 10000), (2017053, 50003);
resigterClass 테이블에 투플들을 생성하는 코드들이다.
코드를 보면 student_id와 class_id 투플값이 모두 student테이블의 student_id와 class테이블의 class_id에 있는 값들인 것을 확인할 수 있다. 외래키를 사용했기 때문이다.
#INSERT INTO `university`.`registerClass` #(student_id, class_id) #VALUES #(2017053, 45003);
이처럼 만약 참조되는 테이블에 없는 값을 value로 입력하면 오류가 뜬다.
SELECTS.NAME, C.NAME, R.last_update #조회할 것 -> 결합할 것 FROM student Sjoin registerClass Ron S.student_id = R.student_id #조건 join class Con R.class_id = C.class_id where S.student_id = 2017053;
SELECT S.NAME, C.NAME, R.last_update FROM student S join registerClass R on S.student_id = R.student_id join class C on R.class_id = C.class_id where S.student_id = 2018111;
SELECT S.NAME, C.NAME, R.last_update FROM student S join registerClass R on S.student_id = R.student_id join class C on R.class_id = C.class_id where S.student_id = 2017105;
join은 릴레이션 간의 조합을 검색하는 키워드이다.
예를 들어, 첫번째 예시는 학번이 2017053인 학생의 이름(from S)과 수강한 과목(C), 수강신청시간(R)을 조회하는 코드이다.
우선 join 다음에 나오는 on은 두 테이블이 결합할 조건을 의미한다.
이 예제에서는 student테이블의 student_id와 registerClass테이블의 student_id가 같을 경우,
student테이블의 name과 class테이블의 name과 registerClass테이블의 last_update를 결합한다는 의미이다.
