16.1 임의(랜덤) 정렬

• 임의의 순서대로 나열
• 그럼 order by 만 없으면 되는거 아님?
• order by 없어도 옵티마이저가 결정한 작업 순서대로 레코드 가져옴

16.1.1 지금까지의 구현

• RAND() 를 사용해서 정렬
• 문제점 : 대용량 테이블에서where 조건이 없다거나 where 조건으로도 정렬대상 건수를 많이 줄이지 못하면 성능 폭망

16.1.2 인덱스를 이용한 임의 정렬

• 테이블 생성할 때 새로운 칼럼을 추가하고 임의의 값을 미리 각 레코드에 부여
• 쿼리에서는 임의의 값이 저장된 칼럼을 기준으로 정렬해 레코드를 가져옴

16.2 페이징 쿼리

6.2.1 지금까지의 방법

• n 값은 높은 페이지로 이동할 때마다 증가
• n + m 개의 레코드를 읽고 n개를 버리고 m개의 레코드를 반환

16.2.2 불필요한 접근을 제거하기 위한 페이징

• 165는 이전 페이지의 마지막 article_id
• 참고
• MyISAM 테이블에서는 프라이머리 키가 보조 인덱스에 자동으로 추가 되지 않아서 board_id와 article_id 모두 포함하는 인덱스 생성
• InnoDB 테이블에서는 프라이머리 키를 자동으로 인덱스의 마지막 칼럼으로 추가

16.3 MySQL 에서 시퀀스 구현

• AUTO_INCREMENT기능은 테이블의 일부라서 관리하기가 용이하지만, 여러 테이블에 걸쳐 유일한 일련번호를 만들어 낼 수 없다는 단점
• Sequence? 오라클에서 제공하는 기능으로 특정 테이블에 의존적이지 않고독립적으로 일련번호 발급

16.3.1 시퀀스용 테이블 준비

MySQL에서 시퀀스 구현하기

• 테이블의 스토리지 엔진을 MyISAM 으로 한 이유
• InnoDB로 시퀀스 테이블을 만들면 시퀀스 테이블의 잠금 때문에 성능 저하를 유발할 가능성

16.3.2 시퀀스를 위한 스토어드 함수

• mysql_sequences 테이블에 레코드를 INSERT해서 프라이머리 키에 중복이 발생하면 seq_currval 값만 1만큼 증가시켜서 mysql_sequencse 테이블에 업데이트
• 이 내용을 스토어드 함수로 캡슐화

위의 내용을 조금더 개선하면 …

on duplicate key로 값을 조회하는것도 좋지만 위의 예제처럼 스토어드 함수로 캡슐화하면 실수를 조금 더 줄일 수 있어

16.3.3 여러 시퀀스 처리하기

• 위의 스토어드 함수에서 조금만 변형하면 여러 시퀀스를 처리할 수 있다

16.3.4 시퀀스 사용 시 주의사항

• InnoDB와 같이 트랜잭션을 지원하는 스토리지 엔진 사용하면 안됨
• 시퀀스 테이블은 많은 클라이언트로부터 동시에 시퀀스 번호를 읽혀야 할 수도 있기 때문
• 시퀀스를 위해 만든 스토어드 함수는 INSERT에서 사용 X
• 트랜잭션을 사용하지 않기 때문에 잘못된 값을 가지게 될 가능성있어
• 아래와 같이 일련번호를 가져오는 작업과 sequence 값을 insert 하는 작업을 별도로 분리 해야함

16.4 큰 문자열 칼럼의 인덱스(해시)

• MyISAM 이나 InnoDB 테이블의 인덱스는 하나의 레코드가 767 바이트 이상을 넘길 수 없음
• 이때 칼럼의 앞부분 767 바이트만 잘라서 p or u key 로 생성할수 있는데 이를 prefix index 라고 함
• 근데 앞 767 바이트가 중복되서 unique index(ui) 나 primary key 를 못쓸경우가 생겨     
• 이럴땐 해시값으로 p key 나 ui를 생성하는 방법을 사용 하면 됨         
• 해시값을 만들려면? SHA 나 MD5로 encrypt 하면 됨

16.5 테이블 파티션

• 정보/ 처리해야 할 데이터가 많을때 빠른 처리를 위한 방법중 하나로 MySQL 에서는 Partition 을 지원하고 있음
• Partition은 저장할 데이터를 분할하여 관리함으로써 보다 빠른 데이터 처리를 도와줌

• MySQL 에서는 해시 파티션의 일종으로 키 파티션 기능을 제공
• 테이블의 프라이머리 키나 유니크 키 칼럼을 이용해 파티션 하는 방법
• 파티션 적용시 PARTITION BY KEY() 와 같이 파티션 키를 명시하지 않으면 프라이머리키를 구성하는 모든 칼럼이 파티션 키가 됨

16.6 SNS 의 타임라인 구현

• facebook 이나 twitter 의 타임라인 기능은 RDBMS 에 적합하지 않은 데이터
• RDBMS 는 쓰기는 느리지만 읽기는 빠르게 처리하는게 장점. 근데 타임라인은 읽기보다 쓰기의 비중이 더 많아
• RDBMS 의 정렬은 인덱스를 이용하지 못하면 정렬대상 건수에 비례해 느려짐. 근데 타임라인의 정렬은 인덱스를 이용할 수 없어

16.6.1 예제 시나리오

• SELECT * FROM tb_article WHERE 작성자 IN (‘김세형’, ‘임현석’) ORDER BY 작성일
• 범위조건이라 인덱스를 이용할 수없어
• 사용자가 게시물이 많아지면 MySQL 서버 폭발

16.6.2 인덱스 테이블 사용

• 단순한 해결책 : 사용자별 타임라인 테이블(인덱스 테이블)을 미리 만들고 칼럼으로 인덱스 생성
• 문제점1 : 인덱스 테이블이 회원 테이블의 레코드 수보다 훨씬 빠른 속도로 불어남
• 문제점2 : 새로운 게시물이 게시 또는 삭제 될때 모든 회원의 타임라인을 INSERT / DELETE 해야함 -> 서버 과부하

16.6.3 Try & Fail 쿼리

• 실시간으로 처리할 수 있는 만큼만 조금씩 잘라서 정렬하고, 필요한 만큼의 레코드가 조회될때까지 여러번 SELECT 쿼리를 실행하자는것이 기본 원리

• 적당한 시간 범위 자르기
•  

• 보여줘야할 게시물 가운데 최근에 작성된 일부 게시물만 조회하고 정렬을 수행하므로 인덱스를 효율적으로 이용가능 and 소량의 레코드만 처리하면 됨
• 필요한 만큼의 레코드를 못찾았으면 다시 다음 시간 범위의 쿼리 실행
• 쿼리 여러번 실행되는건 신경 안써도 돼 -> 쿼리가 데이터를 가져오지 못했다는건 쿼리가 한일이 아무것도 없단 뜻
• 부가적인 비용을 줄이는 방법 중 하나로 Try & Fail 쿼리를 스토어드 프로시저로 구현하는걸 생각해볼 수 있음.

• Try & Fail 쿼리를 스토어드 프로시저로 구현
• 흐름

1. 필요할 로컬 변수 정의 및 초기화
2. 게시물의 타임라인 조회를 위해 반복 실행할 쿼리의 프리페어 스테이트먼트 준비
3. 반복 루프를 돌면서 SELECT 쿼리의 대상 시간 범위를 변수에 설정하고 프리페어 스테이트먼트를 실행
4. 필요한 레코드 20건이 채워지면 반복 루프를 종료하고 프리페어 스테이트먼트를 해제

• 주의 및 개선 사항
• 게시물의 누락
• 만약 장성 일시가 동일한 게시물이 있을 때 누락의 가능성
• 작성 일시와 게시물 번호를 함께 범위로 사용할 것 추천
• 게시물 일련번호를 부여하는 방법도 있음
• 반대로 타임라인의 최근 게시물 가져오기
• 최근 타임라인을 가져오고 싶을 때는 기준 일시에 DATE_ADD()함수를 적용
• 작성 일시를 ASC로 정렬해서 가져와야 할 것
• 회원별 게시물 작성의 불규칙성
• 실시간 통계 테이블을 별도로 생성해서 스토어드 프로시저가 참조할 수 있게 구현하는 방법
• Try & Fail 쿼리의 실행 횟수를 평균해서 저장해두는 것 -> 프로시저가 호출되면 그 회원의 평균 실행 횟수를 참조해 시간 간격을 그에 맞춰서 가변적으로 적용

16.7 MySQL 표준 설정

16.7.1 MySQL 표준 설정의 필요성

• MySQL 서버의 특성상 기본 확장 방식은 하드웨어의 성능을 업그레이드하는 방법(스케일 업)이 아니라 똑같은 성능의 하드웨어를 장착한 서버의 대수를 늘리는 형태(스케일 아웃)
• MySQL 서버가 필요할 때 기준이 되는 설정 파일을 준비한다면, 설정의 누락 없이 빠르게 서비스 환경 구축 가능

16.7.2 표준설정의 예시(p.948 참조)

• 일반적으로 고정적으로 사용하는 설정
• OS 유저나 디렉터리, 이벤트 스케줄러나 “skip-name-resolve”, “sysdate-is-now”와 같은 성능상의 이슈나 실수를 만들어 내기 쉬운 부분은 거의 고정적으로 통일해서 사용
• 복제 용도별로 사용하는 설정
• 복제 용도별로 사용할 수도 있고 사용하지 않을 수도 있는 설정은 모두 주석으로 처리
• 필요할 때만 활성화해서 사용
• 디렉터리나 파일명을 명확하게
• 하드웨어의 특성이나 서비스의 특성에 맞게 변경하는 설정
• MyISAM의 키 캐시, InnoDB의 InnoDB 버퍼 풀의 크기는 성능에 큰 영향
• 별도의 주석으로 표기해두고, 잊지 않도록 할 것

16.8 복제를 사용하지 않는 MySQL의 설정

• 복제를 사용하려면 바이너리 로그 파일이 활성화돼야
• 고비용의 디스크 I/O 필요
• 내부적으로 갭 락과 넥스트 키 락을 사용
• MySQL 서버 전체적인 성능과 동시성까지 저하시키는 효과
• 결론(복제를 사용하지 않을 땐)
• 바이너리 로그 비활성화
• InnoDB가 갭락과 넥스트 키 락을 사용하지 않게 트랜잭션 격리 수준을 READ-COMMITTED로 사용

16.9 MySQL 복제 구축

16.9.1 MySQL 복제의 형태

• 하나의 슬레이브 MySQL이 둘 이상의 마스터 MySQL을 가질 수 없다
• 그 외에는 어떤 형태로든 구성 가능
• 1:M 복제
• 마스터 서버에 2개 이상의 슬레이브를 연결시키는 복제 형태
• 쿼리 요청수를 적절히 분산해서 실행 가능, 백업이나 통계, 배치 프로그램 용도로도 사용
• 1:M:M 복제
• 1:M 구조에서 슬레이브 서버가 너무 많아서 마스터 서버의 성능에 악영향이 예상될 때는 p.953 그림 처럼 1:M:M 구조의 복제 고려
• 1차 복제 그룹은 변경이 빠르게 적용될 것이므로 웹 서비스와 같은 OLTP 서비스 용도, 2차 복제 그룹은 통계나 배치 그리고 백업 용도로 사용 가능
• 업그레이드하거나 장비를 일괄 교체할 때도 사용 가능

16.9.2 확장(스케일 아웃)

• MySQL의 복제는 읽기를 확장하는 방법이지 쓰기를 확장하는 방법 X
• 마스터에 쓰기 작업이 집중
• 최대한 읽기 작업을 슬레이브로 옮겨야 쓰기 작업의 병목으로 인한 서비스 장애를 줄일 수 있다

16.9.3 가용성

• 하드웨어 문제로 MySQL 서버에 문제가 발생하면 슬레이브 서버를 마스터로 승격시켜서 서비스를 멈추지 않게 해야함
• 자동으로 슬레이브를 마스터로 승격하는 MMM(Multi-Master replication Manager) 도구

16.9.4 복제가 구축된 MySQL 에서의 작업

• 사용자가 동시 다발적으로 실행한 SQL이 슬레이브 서버에서는 하나의 스레드에 의해 직렬화되어 하나씩 순차적으로 실행
• 시간이 오래 걸리는 인덱스나 칼럼 추가와 같은 작업은 마스터 서버에서만 실행하는 것보다 마스터와 슬레이브 각각의 서버에서 별도로 실행할 것
• 주의점
• 마스터 서버에서 실행되는 인덱스나 칼럼 추가 명령이 슬레이브로 넘어가지 않게 해야함

16.10 SQL 작성 표준

16.10.1 조인 조건은 항상 ON 절에 기재

• LEFT JOIN 키워드를 사용해 아우터 조인을 수행하면서 WHERE 절에 조건을 표기하는 실수
• 이런 상황에서 MySQL 옵티마이저는 INNER JOIN으로 고쳐서 실행
• LEFT JOIN 뿐 아니라 INNER JOIN에서도 가능하면 ON절에서 명시하는 습괍 추천

16.10.2 테이블 별칭(Alias) 사용 및 칼럼 명에 테이블 별칭 포함

• first_name은 employees 에만 존재하기 때문에 문제없이 실행
• 어느날 dept_manager에 first_name 칼럼이 추가된다면 에러 발생
• 가능하다면 조인 여부와 상관없이 짧은 이름으로 테이블의 별칭을 부여하고, 모든 칼럼의 이름 앞에는 테이블의 별칭을 붙이는 습관 추천

16.10.4 FULL GROUP BY 사용

• FULL GROUP BY : GROUP BY절에 명시된 칼럼 이외의 모든 칼럼은 집합 함수를 통해서만 조회 가능 제약
• FULL GROUP BY를 사용하지 않는 쿼리는 가독성을 떨어뜨리고 사용자의 실수 유발 가능성

16.10.5 DELETE, UPDATE 쿼리에서 ORDER BY.. LIMIT.. 사용 자제

• MySQL 5.0 이하는 DELETE나 UPDATE쿼리에 ORDER BY … LIMIT … 형태의 쿼리를 실행해도 아무 문제 X
• 복제가 구축된 5.1 버전부터는 이러한 쿼리가 마스터와 슬레이브의 데이터를 달라지게 만들 가능성
• 그래서 이러한 쿼리는 경고 메시지를 출력하고, 때로는 에러를 발생
• 경고 메시지때문에 쌓인 에러 로그 때문에 디스크의 여유 공간 문제 발생 가능성

16.10.6 문자열 리터럴 표기는 홑따옴표만 사용

• MySQL 에서는 홑따옴표와 쌍따옴표 모두 사용 가능
• 하지만 두 따옴표의 이스케이프 방식이 달라 혼란을 초래할 수 있고, 잘못된 데이터가 저장될 가능성
• 가능하다면 SQL 표준인 홑따옴표를 사용할 것을 권장

16.10.7 서브쿼리는 조인으로 변경

• MySQL은 서브 쿼리 최적화 능력이 상당히 부족
• FROM 절에 사용된 괄호의 개수만큼 임시 테이블을 만들어 처리한다고 가정하면 될 정도로 취약
• 불필요한 FROM절의 서브 쿼리는 제거를 추천

16.10.8 UNION [ALL] 은 사용 자제

• UNION은 항상 내부적으로 임시 테이블을 만들어 버퍼링한 다음에 사용자에게 결과를 반환
• 여러 집합의 중복된 레코드를 제거해야 할 때는 우회 방법이 없지만, 중복 제거가 없는 경우는 두 개의 쿼리문으로 분리해서 실행하는 편이 더 효율적으로 처리

16.10.9 스토어드 함수는 가능하면 DETERMINISTIC 으로 정의

• DETERMINISTIC : 스토어드 함수나 프로시저에서 입력 값이 똑같으면 출력 값도 같다는 것을 옵티마이저에게 알려주는 명령

16.10.10 스토어드 프로그램에서는 예외 처리 코드를 작성

• 스토어드 프로시저나 함수는 디버깅이 어려움
• 항상 예외 핸들러 코드를 포함시켜야

16.10.11 UPDATE, DELETE 쿼리와 적용 건수(Affected row counts) 체크

• INSERT 쿼리는 성공하면 1, 실패하면 0으로 처리된 레코드 건수가 에러 여부에 따라 명확
• UPDATE나 DELETE 문장은 쿼리의 성공적인 실행 여부를 업무적인 정상 처리 여부로 판단하기에는 부족
• 반드시 적용된 건수를 체크해보고 COMMIT 할 것 추천

16.10.12 숫자 값은 반드시 숫자 타입의 칼럼으로 정의

• 문자열 컬럼과 숫자(상수)를 비교하게 되면, 옵티마이저는 문자열 칼럼을 모두 숫자로 변환해서 비교 수행
• 풀 테이블 수행 OR 인덱스 풀 스캔 수행

16.13 스키마 검토

• 서비스의 기능을 업그레이드하거나 수정하다 보면 조인을 하는 칼럼끼리 타입 불일치가 발생하거나, 문자열 타입의 문자집합이나 콜레이션 등의 문제가 있어 쿼리의 성능이나 데이터의 일관성이 손상될 수 있다
• ERD를 직접 하나씩 점검하는 것이 가장 좋겠지만, 시간적인 문제로 어렵다면 MySQL 딕셔너리 정보를 확인해 보는 방법도 있다

15.1 문자열

15.1.1 저장 공간

• CHAR : 저장공간의 크기가 고정적 - 실제 저장된 값의 유효 크기가 얼마인지 별도로 저장 필요X -> 추가 공간 필요 X ( 문자열의 길이가 항상 일정한 경우 사용)
• VARCHAR : 값의 크기 별도로 저장 O -> 추가 공간 필요 O(문자열 길이가 가변적일때 사용)

15.1.2 비교 방식

• CHAR 랑 VARCHAR 비교시 뒤에 빈공간은 없는걸로 처리 앞에 빈공간은 유효한걸로 처리됨 EX)
• “abc” = “abc   “ // “abc” ”    abc”
• 예외적으로 LIKE에서는 앞뒤 공백 모두 유효문자로 처리됨 -> 와일드카드(%) 사용하자
• “abc” ”    abc” //  “abc” ” abc     ” // abc LIKE “ %abc%” good

15.1.3 문자 집합(캐릭터 셋)

• MySQL 서버에서 각 테이블의 칼럼은 모두 서로 다른 문자집합을 사용해 문자열 저장 가능

• CHAR, VARCHAR, TEXT 에서만 설정가능
• MySQL 서버에서 사용가능한 문자집합 확인 명령 : SHOW CHARACTER SET;
• 클라이언트로부터 쿼리를 요청했을때의 문자 집합 변환
• 인트로듀서 : SQL 문장에서 별도로 문자집합을 설정하는 지정자
• SELECT emp_no, first_name FROM employees WHERE first_name=_latin1'Smith';
• 처리 결과를 클라이언트로 전송할때의 문자집합 변환
• character_set_connection 에 정의된 문자집합으로 변환해 SQL 실행 후, 쿼리의 결과를 character_set_results 에 설정된 문자집하으로 변환해 클라이언트에 전송

15.1.4 콜레이션

• 문자열 칼럼의 값에 대한 비교나 정렬 순서를 위한 규칙 - 비교나 정렬 작업에서 영문 대소문자를 같은것으로 처리할지 아니면 더 크거나 작은것으로 판단할지에 대한 규칙을 정의
• 서버에서 사용가능한 콜레이션 목록 확인 : SHOW COLLATION;

15.1.5 문자열 이스케이프 처리

• MySQL 에서 SQL 문장에 사용하는 문자열은 “\” 를 사용해 이스케이프 처리해주는것 가능 ex) \0, \’, \”, \b. \n, \n ...

15.2 숫자

• 참값(Exact value data type) - 소수점 유무에 상관없이 정확히 그값을 유지 ex) INTEGER, DECIMAL
• 근사값 - 부동 소수점. 저장값과조회값이 정확히 일치안함 ex) float, double

15.2.1 정수

• 그냥 읽어보자

15.2.2 부동 소수점

• 부동 소수점 저장하기 위해 float, double 사용
• 동등비교 사용 불가 - 근사값을 저장하기 때문
• 그럼 부동 소수점 왜 사용하는거야
• 옛날 메모리가 부족했을 당시에 float형 자료를 담을 메모리 공간은 한정된 반면 소수점은 0.00000000000001 과 같이 무한으로 늘어나기 때문에 이를 정확하게 메모리에 담는 방법이 존재하지 않아 사용하게 된 것 - by someone

15.2.3 DECIMAL

• 정확한 수치가 필요할때 사용  ex) 은행 잔고
• NUMERIC 과 DECIMAL 은 내부적으로 같은 방식으로 처리됨

15.2.4 정수 타입의 칼럼을 생성할 때의 주의사항

• ZEROFILL 옵션을 사용하면 자동으로 그 칼럼의 타입은 양의 숫자만 저장할 수 있는 UNSIGNED 타입이 되버림
• 사용 예 CREATE TABLE tb_bigint(fd1 BIGINT(10), ZEROFILL);

15.2.5 자동 증가 옵션 사용

• auto_increment_offset : 속성의 칼럼 초기 값 정의
• auto_increment_increment : 얼마씩 증가할건지 결정

15.3 날짜와 시간

• DATE, DATETIME 이 가장 많이 사용됨
• 현재 DBMS 커넥션의 타임존에 관계없이 클라이언트로 부터 입력된 값 그대로 저장하고 조회할때도 변환없이 그대로 출력
• Timestamp 는 항상 UTC 타임존으로 저장되므로 타임존이 달라져도 값이 자동 보정됨

15.3.1 TIMESTAMP 타입의 옵션

• timestamp 는 레코드가 update 되거나 insert 될때 자동으로 현재시간으로 변경됨

15.3.2 타임존 등록 및 사용

• mysql_tzinfo_to_sql 이라는 유틸리티 이용해서 MySQL 의 타임존으로 등록 가능
• 그냥 한 번 읽어보자

15.4 ENUM과 SET

• 문자열 값을 MySQL 내부적으로 숫자 값으로 맵핑해서 관리하는 타입

15.4.1 ENUM

• Enum 타입은 반드시 하나의 값만 저장 가능
• 용도 : 코드화된 값을 관리
• 빈 문자열은 항상 0으로 맵핑 됨
• 단점 : 칼럼에 저장되는 문자열 값이 테이블의 구조가 되면서 기존의 enum 타입에 새로운 값을 추가해야 한다면 테이블의 구조를 변경해야 한다는 점

15.4.2 SET

• 문자열 값을 정수 값으로 맵핑해서 저장하는 방식
• ENUM 과의 차이 : SET 은 하나의 칼럼에 하나 이상의 값을 저장 가능

15.5 TEXT, BLOB

• MySQL 에서 대량의 데이터를 저장할때 TEXT, BLOB 사용      
• TEXT 와 BLOB 의 차이
• TEXT 타입은 문자열을 저장하는 대용량 칼럼이라 문자집합이나 콜레이션을 가짐
• BLOB 타입은 이진 데이터 타입이라 별도의 문자집합이나 콜레이션을 가지지 않음
• 칼럼하나에 저장되는 문자열이나 이진 값을 길이가 예측할 수 없이 클때 TEXT 나 BLOB 사용하자
• TIP : 만약 select 를 통해 가져오려는 blob 나 text 타입의 칼럼이 크지 않거나 일부만 조회해야 한다면 cast 나 substring 함수를 이용해 강제로 char 나 varchar 로 변환해서 조회하면 필요한 임시테이블이 메모리에 생성되도록 유도 가능
• 참고 : CLOB : Oracle 에서만 존재하는 필드타입
• 사용방법 http://ggari.tistory.com/175

15.6 공간(Spatial) 데이터 타입

• 공간 데이터 : GPS 를 이용한 위치정보를 포함해 모든 좌표 형식의 데이터를 관리 할 수 있는 개념

15.6.1 POINT 타입

• 공간 데이터 타입 중 가장 기본적인 데이터 타입으로 x 와 y 의 좌표만으로 구성된 하나의 점정보를 저장할 수 있는 데이터 타입
• POINT(x,y), GeomFromText(‘Point(x,y)’) 로 데이터 생성 가능
• p.908 예제 한번 보자

15.6.2 LINESTRING 타입

• 하나의 직선 뿐 아니라 여러개의 꺾임이 있는 연결된 선도 모두 저장 가능
• LINESTRING(), LineStringFromText() 로 데이터 생성 가능

15.6.3 POLYGON 타입

• 다각형(시작과 끝지점 이 일치하는 닫힌 도형)을 저장할 수 있는 데이터 타입  
• p.909 예제 한번 보자

15.6.4 GEOMETRY 타입

• 위의 모든 데이터 타입을 모두 포함하는 슈퍼 타입. GIS 처럼 다양한 공간 정보를 저장하는 칼럼이 필요할때는 점 이나 라인 뿐 아니라 복잡하고 다양한 다각형 데이터가 담기는데 이 때 GEOMETRY 타입을 이용하면 됨

14.1 논리 모델링

14.1.1 모델링 용어

• ERD 상에 표현되는 오브젝트는 논리모델링 단계에서 사용하는 이름, 물리단계에서 사용하는 이름, 이렇게 이름을 두개씩 가지고 있음
• 논리 모델            물리 모델
• 엔티티                          테이블
• 속성,어트리뷰트    칼럼
• 키 그룹              인덱스 ….

14.1.2 용어집(p.825)

• 시스템이나 업무를 잘 모르는 관련자의 이해도를 높이는데 사용
• 시스템이 다루는 업무의 범위를 정의하는 가장 기본적 문서

14.1.3 엔터티

• = 객체지향 개발 언어의 클래스
• Entity 의 종류와 표현 방법
• key entity - 관리대상 데이터중 가장 최상휘에 존재하는 entity, (각 진 사각형)
• main entity-  action entity 중 중요한 역할을 하는 entity
• action entity - key entity 간의 작용으로 만들어지는 entity ( 원형 사각형)

( p. 828 참고)

• Entity 의 작명
• 복수형 표현 사용 x ex) 리스트, 목록
• 수식어로 범위를 제한하지 말자 ex) 직원용상품, 고객용 상품 (x) -> 상품 (o)
• 범위가 애매모호한 단어 x ex) 정보

14.1.4 어트리뷰트 (속성)

• 더는 분리 될 수 없는 최소의 데이터 보관 단위
• 어트리뷰트의 원자성
• 하나의 어트리뷰트는 해당 업무 요건에 맞게 최소 단위의 값 하나만 가져야 함
• 하나의 어트리뷰트에 복수형으로 값을 저장해서는 아니됨
• 어트리뷰트의 작명
• ERD 의 가독성을 위해속성 이름 신중하게 짓자
• 너무 간략하게 하면 나중에 어떤 의미로 지었는지 혼동 될 수 있음
• 엔티티와 어트리뷰트의 구분
• 엔터티를 만들고 해당 엔터티에 포함된 어트리뷰트를 나열해서 어트리뷰트의 기본적인 조건을 만족하는지 꼭 검토해볼 것 ( p.833 참고) - 하나의 값만 가져야 되는데 여러개의 값을 가질수 있어 -고객주소, 로그인 시간 therefore, 부적합

14.1.5 식별자 (Primary Key)

• 하나의 엔터티에서 개별 레코드를 식별할수  있는 어트리뷰트의 조합
• 여러개의 집합체를 담고 있는 하나의 통에서 각각을 구분할 수 있는 논리적인 이름이 있어야 하는데 이 구분자가 식별자

14.1.6 관계(릴레이션)

• 식별 관계와 비식별 관계
• 식별 관계
• 부모의 식별자가 자식의 엔터티의 레코드를 식별하는 데 꼭 필요한 관계
• 비식별 관계
• 부모 엔터티의 식별자가 없어도 자식 엔터티의 레코드가 생성될 수 있는 관계
• 관계의 기수성
• 부모 엔터티의 레코드 하나에 대해 자식 엔터티의 레코드가 얼마나 만들어질 수 있는지를 의미
• 0, 1, 1개 이상(N, M) 으로 표시
• 관계의 형태
• 계층 관계
• 부모와 자식 간의 직선적인 관계가 연속되는 형태
• 적절한 수준에서 자식 엔터티의 식별자를 인조 키로 식별자를 대체할 것
• 순환 관계
• 하나의 엔터티가 부모임과 동시에 자식 엔터티가 되는 재귀적인 형태
• 순환 관계는 절대 식별 관계가 될 수 없다
• M:M 관계
• 논리 모델에서만 가능, 물리 모델에서는 다른 방법으로 해결돼야 함
• M:M 관계의 엔터티를 두 개의 1:M 관계로 풀어줘야 함 : “M:M관계 해소”
• BOM 관계
• 부품을 결합해서 하나의 또 다른 부품을 만들고, 만들어진 새로운 부품은 또 다시 다른 부품의 조립 시에 사용되는 형태의 업무를 표현하는 데 주로 사용
• Bill Of Material의 약자
• M:M 관계 + 순환 관계, M:M관계 해소가 필요
• p.840 ~ 그림 참조
• 배타 관계
• 하나의 엔터티에 두 개 이상의 관계가 동시에 존재할 수 없는 형태
• Arc 관계
• 자식 엔터티는 부모 엔터티를 구분하기 위해 “구분” 어트리뷰트를 가짐

14.1.7 엔터티의 통합

• 엔터티 통합을 고려해야 할 경우
• 엔터티를 구성하는 어트리뷰트와 관계가 비슷한 엔터티
• 어트리뷰트의 차이가 있더라도 개별적인 어트리뷰트가 많지 않을 때

• 서비스에서 어떤 형태로 엔터티나 어트리뷰트에 접근하게 될 것인지 고려하여 적절하게 분리 또는 통합을 선택

14.1.8 관계의 통합

• 부모와 자식 간에 관계가 여러 번 나타날 때 하나의 관계로 통합
• 관계의 수가 일정하지 않고, 늘거나 줄 수 있을 때 유용
• 관계를 통합하면 조인해야 하거나 저장해야 하는 테이블이 하나 더 늘어남
  but, 업무 요건의 변화에 유연하게 대응 가능

14.1.9 모델 정규화

• 제 1 정규화(No Repeating Group)
• 모든 속성은 반드시 하나의 값을 가져야 한다
• 하나의 어트리뷰트에 여러 개의 값을 저장하거나, 하나의 엔터티에서 똑같은 성격의 어트리뷰트가 여러 번 나열되는 것 X

• 제 2 정규화(Whole Key Dependent)
• 식별자 일부에 종속되는 어트리뷰트는 제거해야 한다
• 나중에 반정규화를 하더라도 정규화를 수행하기 전에 미리 반정규화를 해두는 방법은 혼란을 초래

• 제 3 정규화(Non-Key Independent)
• 식별자 이외의 속성간에 종속 관계가 존재하면 안 된다
• p. 849 ~ 그림 참조

14.2 물리 모델링

14.2.1 프라이머리 키 선택

• 복합 칼럼으로 구성할 때는 너무 많은 칼럼이 프라이머리 키로 참여하지 않게
• 자식 테이블 중에서도 관계를 많이 가지는 테이블에서는 부모와의 프라이머리 키 상속을 끊고 새로운 프라이머리 키를 갖게 -> Auto-Increment 같은 인조 키 부여

14.2.2 데이터 타입 선정

• 데이터 타입
• 문자, 숫자, 날짜, 이진 데이터 등
• 데이터의 성격에 맞는 데이터 타입을 선정해야
• 칼럼의 길이
• 어떤 특성을 가지느냐에 따라 칼럼의 길이 결정
• 인터넷 익스플로러 웹 브라우저는 최대 2038 바이트까지 URL로 사용 가능, 아파치 웹 서버는 대략 8000 바이트까지, 프락시 서버는 255바이트까지 지원
  URL 데이터 타입은 몇 바이트로?
• 문자집합
• 여러 문자집합 사용으로 인해 혼동이 예상된다면, 하나의 문자 집합만을 선택
• 최적의 데이터 타입을 선정하고 칼럼의 길이를 줄이고 최적의 문자집합을 선정하는 노력을 해야

• NULL과 NOT NULL
• 저장 방법
• MyISAM

• NULL이나 빈문자열을 저장하나 디스크 공간의 차이 X
• InnoDB
• NULL이 저장되는 칼럼은 디스크 공간 사용 X
• 디스크 공간 절약의 문제가 아니라 옵티마이저가 얼마나 쿼리를 더 최적화할 수 있게 환경을 만드느냐가 관건
• 검색 조건으로 사용되는 칼럼에서는 NOT NULL로 선택
• 도메인
• 같은 정보가 저장되는 칼럼을 한 테이블에서는 INTEGER로 다른 테이블에서는 VARCHAR로 저장돼 있는 경우가 빈번히 발생
• 데이터베이스의 칼럼 타입을 일관성 있게 통일해서 사용할 것

14.2.3 반정규화

• 정의
• GROUP BY나 COUNT(*)와 같이 많은 레코드를 대상으로 하는 작업을 빠르게 조회하기 위해 미리 건수를 집계해서 별도의 테이블이나 칼럼으로 저장해두는 것
• 칼럼 복사
• 조인을 제거하기 위한 용도
• 게시물 테이블을 조회할 때 회원 테이블과 조인하지 않기 위해 회원의 이름을 게시물 테이블에 복사해두는 형태
• 최근에는 조인을 제거하기 위한 용도로는 거의 사용하지 않음 - 회원명 칼럼이 자주 변경된다면 효율성이 떨어지고, 데이터 정합성 문제
• 정렬을 위한 용도

• 이 때, ORDER BY 처리는 Filesort 과정을 거쳐야
• 하지만 게시물 테이블에 작성회원명을 복사해두고, “게시판번호 + 작성회원명“으로 인덱스를 생성한다면 인덱스 처리
  (회원.작성회원명 -> 게시물.작성회원명)

• 요약 칼럼
• 필요할 때
• 게시판의 목록을 조회할 때 게시물 수를 함께 출력해야 할 때
• 특정 게시판의 상세 내용을 출력할 때 게시물 수를 함께 출력해야 할 때
• 이러한  기능이 자주 호출 되고 게시물 건수가 많을 때
  (자주 실행되지 않는다면, 반정규화X)
• 칼럼의 값 유지 방법
• 원본 데이터가 변경될 때마다 실시간으로 요약 칼럼의 값을 증가

• 변경과 동시에 게시물수 칼럼의 값을 증가
• 사용자의 대기 시간 길어짐
• 게시물 테이블과 게시판 테이블의 레코드 모두 잠금
• 백 그라운드 프로세스로 요약 칼럼의 값을 증가
• 사용자의 응답 시간 단축
• 실행해야 하는 쿼리는 첫 번째 방법과 동일
• 2~30분 단위로 모아서 배치 형태로 요약 칼럼의 값을 증가
• 웹 서버나 별도의 큐를 이용해 작성된 게시물의 수를 누적했다가 한 번에 증가
• 잠금 경합이나 쓰기 부하 측면에서 성능 향상
• 해시 인덱스
• 해시 인덱스는 동등 비교를 가장 빠르게 처리할 수 있는 인덱싱 방식
• MyISAM, InnoDB 스토리지 엔진은 해시 인덱스를 지원 X
• 해시 인덱스를 흉내 내는 T-Tree 인덱스
• referrer_url_hash 칼럼에 Referrer URL의 해시 값 저장

• referrer_url_hash 칼럼에 인덱스를 생성하고 검색

• 해시 값 충돌 발생을 예방하기 위해 referrer_url값도 비교

12장 쿼리 종류별 잠금

12.1 InnoDB의 기본 잠금 방식

12.1.1 SELECT

• 레코드를 잠그는 방법

• 다른 커넥션의 트랜잭션에서 변경하지 못하게 막는 역할
• LOCK IN SHARE MODE는 읽기 잠금만 걸기 때문에 잠금을 획득한 트랜잭션에서도 변경하려면 쓰기 잠금 다시 획득 필요
• 읽기 잠금을 가진 상태에서 다시 쓰기 잠금을 획득하는 과정은 데드락을 유발하는 가장 일반적인 형태
• 잠금 해제
• COMMIT이나 ROLLBACK이 실행되면 잠금 해제
• 하나의 작업 단위가 여러 번의 사용자 요청으로 완료되는 프로그램에서는 읽기 잠금을 사용해서는 안됨
• TRY ~ FINALLY 구문으로 작성하여 FINALLY 구문에서 COMMIT 이나 ROLLBACK을 사용해 트랜잭션 종료가 보장되는 프로그램 작성 필요

12.1.2 INSERT, UPDATE, DELETE

• 기본적으로 쓰기 잠금을 사용, 필요 시에는 읽기 잠금 사용 가능
• AutoCommit 모드 : 자동으로 트랜잭션이 시작되고 종료
• AutoCommit 비활성 모드 : 실행과 함께 자동 트랜잭션 시작, 종료는 반드시 COMMIT이나 ROLLBACK 명령을 사용해 수동으로 종료
• AutoCommit, BEGIN이나 START TRANSACTION 명령으로 명시적 트랜잭션 : COMMIT이나 ROLLBACK 명령을 이용해 수동으로 트랜잭션을 종료
• InnoDB의 잠금 방식
• 인덱스를 사용할 수 있는 조건만 일치하면 모두 잠금 -> 잠금 대상 레코드가 변경 대상 레코드보다 범위가 크거나 같다
• 사용가능한 인덱스가 없다면, 테이블의 모든 레코드를 잠금

12.2 문장별로 사용하는 잠금

12.2.1 SELECT 쿼리의 잠금

• SELECT … FROM …
• 기본 형태의 SELECT 쿼리는 별도의 잠금 사용X
• 다른 트랜잭션에 의해 변경되거나 삭제되는 중이라면 언두 로그를 이용해 레코드 읽음
• DDL 문장으로 테이블 구조가 변경되는 중에도 처리 가능
• 트랜잭션의 격리 수준이 SERIALIZABLE인 경우에는 LOCK IN SHARE MODE 옵션이 자동으로 덧붙혀서 실행
• SELECT … FROM … LOCK IN SHARE MODE
• 검색을 위해 접근한 모든 레코드에 대해 공유 넥스트 키 락을 필요로 함
• 다른 트랜잭션에 의해 쓰기 잠금이 걸려 있다면 그 잠금이 해제될 때까지 대기
• 다른 트랜잭션에 의해 읽기 잠금이 걸려 있을 땐 대기 없이 잠금 획득 가능
• SELECT … FROM … FOR UPDATE
• 접근한 모든 레코드에 대해 배타적 넥스트 키 락
• 대상 레코드가 다른 트랜잭션에 의해 읽기 잠금이나 쓰기 잠금으로 사용되고 있다면 해제될 때까지 대기

12.2.2 INSERT 쿼리의 잠금

• 배타적 레코드 잠금 사용
• 해당 테이블에 p or u key 가 존재한다면 중복체크를 위해 공유 레코드 잠금을 먼저 획득 해야함
• 여러 트랜잭션이 동시에 인서트 인텐션 락(일종의 갭락) 획득 가능
• 참고 : Insert Intention Lock  Intention Lock
• Intention Lock : InnoDB 에서 MyISAM 테이블 락과의 호환을 위해 가지고 있는 잠금 / InnoDB 에서 사용되는 테이블 레벨의 잠금
• Insert Intention Lock :  동시 처리 가능하게 해주는 락? 밑에 설명 참고

CREATE TABLE tb_test123 (

fdpk INT NOT NULL,

PRIMARY KEY(fdpk)

);

INSERT INTO tb_test123 VALUES (1), (6),(8),(9);

BEGIN;  //  트랜잭션 시작

INSERT INTO tb_test123 VALUES(5);

BEGIN;

INSERT INTO tb_test123 VALUES (3);

BEGIN;

INSERT INTO tb_test123 VALUES (4);

• 인서트 인텐션 락이 없다면?
• 순차적으로 실행됨
• 1과 6사이의 간격을 배타적 갭락으로 잠금
• 새로운 p 값 345 삽입
• 새로운 p 값 345에 배타적 레코드 잠금
• 동시에 실행되지 못하고 순차적으로 실행
• 인서트 인텐션 락 사용하면?
• 1 6사이의 간격에 대한 인서트 인텐션 락을 동시에 획득
• 충돌하는 값 집어넣지 않으면 동시 처리 가능

주의 : 공유잠금을 가지고 있는 상태에서 다시 배타적 잠금을 걸게 되면 데드락 발생 할 수 도

[참고]  배타적 잠금 : 해당 트랜잭션에서 레코드나 간격을 변형하기 위해 획득하는 잠금

          공유 잠금 : 레코드나 간격을 읽을때 다른 트랜잭션이 변경하지 못하게 하는 용도의 잠금

12.2.3 UPDATE 쿼리의 잠금

• UPDATE … WHERE …
• 참조한 모든 레코드에 배타적 넥스트 키 락
• 다른 트랜잭션에 의해 처리 범위의 레코드가 영향을 받지 않게 하기 위해
• UPDATE tb_test1 a, tb_test2 b ON … SET a.column = b.column
• UPDATE 되는 칼럼이 포함된 모든 테이블의 레코드에는 배타적 넥스트 키 락
• 단순 참조 테이블에는 공유 넥스트 키 락

12.2.4 DELETE 쿼리의 잠금

• DELETE FROM … WHERE …
• 참조한 모든 레코드에 배타적 넥스트 키 락
• 복제에서 마스터와 슬레이브의 동기화를 유지하기 위해
• 일반적으로 넥스트 키 락을 설정하는 이유는 이 처리가 수행되는 동안 다른 트랜잭션에 의해 처리 범위의 레코드가 변경되지 않게 하는 데
• DELETE a FROM tb_test1 a, tb_test2 b …
• 최종적으로 DELETE 되는 레코드는 배타적 넥스트 키 락
• 단순 참조용으로만 사용되는 테이블에는 공유 넥스트 키 락

12.2.5 DDL 문장의 잠금

• CREATE TABLE tb_new … SELECT … FROM tb_old …
• 읽어오는 테이블은 읽기 잠금
• INSERT되는 레코드는 배타적 레코드 락
• DDL 명령은 쿼리가 완료됨과 동시에 트랜잭션도 자동으로 COMMIT
• RENAME TABLE tb_test TO tb_backup, tb_swap TO tb_test, …
• RENAME TABLE절에 명시된 모든 테이블에 대해 네임 락
• 네임 락은 테이블 수준의 잠금
• 테이블의 이름을 변경하는 동안 해당 테이블의 레코드를 읽거나 쓰는 트랜잭션은 모두 대기

12.2.6 InnoDB에서 여러 쿼리 패턴 간의 잠금 대기

CREATE TABLE empl (

emp_no INT NOT NULL,

last_name VARCHAR(16) DEFAULT NULL,

PRIMARY KEY (emp_no)

) ENGINE= INNODB;

INSERT INTO empl VALUES (120, 'Hello'), (130,'Hi'), (140, 'Bye');

12.3 InnoDB 에서 데드락 만들기

12.3.1 패턴 1 (상호 거래 관련)

• A 사용자가 B 사용자에게 10 포인트 전달하고, B 사용자도 A 사용자에게 10포인트 전달하는 시나리오
• 트랜잭션 1, 2번이 각각 user_id A, B 에 대해 배타적 잠금 -> 데드락
• p.727 표
• 해결책 : 업무 순서가 아니라 테이블의 프라이머리 키 기준으로 처리

12.3.2 패턴 2 (유니크 인덱스 관련)

• 시나리오
• 세 트랜잭션이 각각 프라이머리 키나 유니크 키를 가진 테이블에 같은 값으로 INSERT를 시도
• 첫 번째 트랜잭션이 그 값에 대해 배타적 잠금을 가짐
• 두, 세 번째 트랜잭션은 공유 레코드 잠금을 획득하기 위해 대기
• 첫 번째 트랜잭션이 ROLLBACK 실행 -> 배타적 잠금 해제
• 두, 세 번째 트랜잭션은 동시에 그 값에 대해 공유 잠금 획득 & 배타적 잠금 시도
• 공유 잠금이 걸려있기 때문에 어느 트랜잭션도 배타적 잠금 획득 불가
• 데드락
• 해결책
• 이 데드락이 발생한 프로그램이 배치 프로그램이라면 실행 시간을 변경
• 웹 서비스와 같은 OLTP 환경의 프로그램이라면 프로그램 코드에 데드락에 대한 핸들링 코드 추가
• try ~ catch문으로 SQLException을 이용해 데드락 발생 여부 조사

12.3.3 패턴 3 (외래키 관련)

• 시나리오
• 부모 테이블과 부모 테이블을 참조하는 자식 테이블 존재
• 두 트랜잭션이 각각 자식 테이블에 값을 입력하고, 부모 테이블의 같은 컬럼에 업데이트를 시도
• 자식 테이블에 값을 입력할 때, 참조된 부모 테이블의 공유 잠금을 획득
• 부모 테이블을 업데이트 하기위해 배타적 잠금을 요청하게 되고, 패턴2와 같이 공유 잠금이 걸린 테이블에 배타적 잠금 요청을 하며 데드락에 걸림
• 해결책
• INSERT와 UPDATE 순서를 반대로 실행해주면 해결
• 사용량이 많은 서비스에서는 UPDATE하는 쿼리가 너무 빈번하게 실행되기 때문에 잠금 경합이 상당히 높아질 가능성
• UPDATE 하는 쿼리는 일정 주기로 모아서 실행하는 것 추천

12.3.4 패턴 4 (서로 다른 인덱스를 통한 잠금)

• 시나리오
• 인덱스가 있는 테이블에 하나의 트랜잭션은 인덱스를 이용하여 UPDATE를 시도
• 다른 하나의 트랜잭션은 프라이머리 키를 이용하여 같은 값에 UPDATE 시도
• 첫 번째 트랜잭션은 인덱스의 레코드 잠금
• 두 번째 트랜잭션은 프라이머리 키의 레코드 잠금
• 서로 서로의 잠금을 대기 -> 데드락
• 결론
• 이런 패턴의 데드락은 발생 빈도는 낮지만 쿼리 하나씩만 실행하는 과정에도 데드락 발생 가능성 존재
• 절대 데드락을 유발하지 않으리라 보장하기는 불가능
• 에러 코드를 감지해서 재처리하도록 프로그래밍하는 것 추천

7.2 매뉴얼의 SQL 문법 표기를 읽는 방법

• MySQL 매뉴얼의 SQL 문법 표기

7.3 MySQL 연산자와 내장 함수

7.3.1 리터럴 표기법

• 문자열
• 숫자
• 날짜
• 불리언

7.3.2 MySQL 연산자

• 동등 비교 (=, <=>)
• 부정 비교 (<>, !=)
• NOT 연산자 (!)
• AND(&&)와 OR(||) 연산자
• 나누기(/, DIV)와 나머지(%, MOD) 연산자
• REGEXP 연산자
• LIKE 연산자
• BETWEEN 연산자
• IN 연산자

7.3.3 MySQL 내장 함수

• NULL 값 비교 및 대체(IFNULL, ISNULL)
• IFNULL
• 표현식의 값이 NULL인지 비교하고, NULL이면 다른 값으로 대체
• ex. SELECT IFNULL(NULL, 1);
• ISNULL
• 표현식이나 칼럼의 값이 NULL인지 아닌지 비교 (1: TRUE, 0: FALSE)
• ex. SELECT ISNULL(0);
• 현재 시각 조회(NOW, SYSDATE)
• NOW : 하나의 SQL에서 같은 값
• SYSDATE : 호출 시점에 따라 결과 값이 달라짐
• 슬레이브에서 안정적으로 복제되지 못함
• 인덱스를 효율적으로 사용하지 못함(p. 399 예제)
• 날짜와 시간의 포맷(DATE_FORMAT, STR_TO_DATE)
• SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%s’) AS current_dttm;
• SELECT STR_TO_DATE(‘2011-04-30 15:13:25’, ‘%Y-%m-%d %H:%i:%s’)
• 날짜와 시간의 연산(DATE_ADD, DATE_SUB)
• SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow;
• INTERVAL n [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, …]
• 타임 스탬프 연산(UNIX_TIMESTAMP, FROM_UNIXTIME)
• TIMESTAMP : 4바이트 숫자 타입으로 저장
• SELECT UNIX_TIMESTAMP(‘2005-03-27 03:00:00);
• 문자열 처리(RPAD, LPAD / RTRIM, LTRIM, TRIM)
• SELECT RPAD(‘Cloee’, 10, ‘0’); -> Cloee00000
• SELECT TRIM(‘           Cloee           ‘); -> Cloee
• 문자열 결합(CONCAT)
• SELECT CONCAT(‘Georgi’, ‘Chrisian’) -> GeorgiChristian
• 인자의 개수는 제한이 없다.
• SELECT CONCAT_WS(‘,’, ‘Georgi’, ‘Christian’); -> Georgi,Christian
• 첫 번째 인자로 구분자
• GROUP BY 문자열 결합(GROUP_CONCAT)
• 그룹함수 : GROUP BY 와 함께 사용하며, GROUP BY가 없는 경우엔 단 하나의 결과 값만 도출
• SELECT GROUP_CONCAT(dept_no) FROM departments;
• 메모리 버퍼 공간을 사용
• 시스템 변수에 지정된 크기를 초과하면 경고
• 값의 비교와 대체(CASE WHEN … THEN … END)
• SQL 구문

• 타입의 변환(CAST, CONVERT)
• CONVERT 가 문자열의 문자집합을 변환하는 용도를 가졌다는 것과 인자 사용 규칙만 다르고 같은 역할을 한다
• SELECT CAST(‘1234’ AS SIGNED INTEGER);
• SELECT CONVERT(‘1234’, INTEGER);
• SELECT CONVERT(‘ABC’ USING ‘utf8’);
• 이진값과 16진수(Hex String) 문자열 변환(HEX, UNHEX)
• INSET INTO tab_binary VALUES(UNHEX(MD5(‘abc’)), UNHEX(SHA(‘abc’)));
• SELECT HEX(col_md5), HEX(col_sha) FROM tab_binary;
• 암호화 및 해시 함수(MD5, SHA)
• SHA()
• SHA-1 암호화 알고리즘 사용
• 결과로 160비트(20바이트)의 해시 값을 반환
• MD5()
• 메시지 다이제스트 알고리즘 사용
• 128비트(16바이트)의 해시 값을 반환
• 두 함수의 출력 값은 16진수로 표시되기 때문에 저장 공간이 두 배로 필요
• 저장 공간을 줄이고 싶다면 BINARY 타입으로 정의하고 UNHEX와 HEX 이용
• 처리 대기(SLEEP)
• 개발이나 디버깅 용도
• SELECT SLEEP(1) FROM employees;
• 벤치마크(BENCHMARK)
• 디버깅이나 간단한 함수의 성능 테스트용
• 반복할 횟수, 실행할 표현식을 인자로 받음
• SELECT BENCHMARK(100, (SELECT COUNT(*) FROM employees));
• IP 주소 변환(INET_ATON, INET_NTOA)
• IP 주소는 4바이트의 부호 없는 정수
• VARCHAR(15) 타입으로 저장하면 저장 공간을 훨씬 많이 필요
• INSERT INTO tab_accesslog VALUES(NOW(), INET_ATON(‘127.0.0.1’));
• SELECT access_dttm, INET_NTOA(ip_addr) FROM tab_accesslog;
• MySQL 전용 암호화(PASSWORD, OLD_PASSWORD)
• 암호화 함수
• 4.0 이하 버전에서 쓰이던 PASSWORD 가 OLD_PASSWORD로 수정됨
• 이후에도 업그레이드 가능성이 있으니 사용에 주의
• MD5(), SHA() 추천
• VALUES()
• INSERT INTO … ON DUPLICATE KEY UPDATE … 구문에서만 사용 가능

• COUNT()
• COUNT(*) 형태로 사용, 여기서 *은 모든 칼럼이 아닌 레코드 자체를 의미
• 인덱스를 제대로 사용한 COUNT()는 SELECT 쿼리보다 빠르다 (반대로 인덱스를 제대로 사용하지 못한 경우엔 페이징해서 데이터를 가져오는 SELECT 쿼리보다 훨씬 느리게 수행 될 수도 있다)

7.3.4 SQL 주석

• SQL 표준에서 정의한 주석 표기 방식
• -- : 한 라인만 주석
• /* … */ : 여러 라인을 주석
• MySQL
• # : 한 라인만 주석
• /*! … */ : 선택적인 처리나 힌트

7.4 SELECT

7.4.1 SELECT 각 절의 처리 순서

• 쿼리 구분
• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT
• 쿼리 절의 실행 순서

1. WHERE 적용 및 조인 실행
2. GROUP BY 적용(레코드 그룹핑)
3. DISTINCT 적용
4. HAVING 조건 필터링
5. ORDER BY 정렬
6. LIMIT 적용

• 예외적 경우(주로 GROUP BY 절이 없이 ORDER BY만 사용된 쿼리에서 사용)

1. 드라이빙 테이블
2. ORDER BY 정렬
3. WHERE 적용 및 조인 실행
4. LIMIT 적용

7.4.2 WHERE 절과 GROUP BY 절, 그리고 ORDER BY 절의 인덱스 사용

• 인덱스를 사용하기 위한 기본 규칙
• 인덱스된 칼럼의 값 자체를 변환하지 않고 그대로 사용
• 연사자 양쪽의 비교 대상 값은 데이터 타입이 일치
• WHERE 절의 인덱스 사용
• 인덱스 사용 방법
• 범위제한 조건

• 동등 비교 조건이나 IN으로 구성된 조건이 인덱스를 구성하는 칼럼과 얼마나 좌측부터 일치하는가에 따라
• 체크 조건(p.424 그림 설명)
• OR 연산자 사용시
• OR로 연결된 각 조건이 모두 각 인덱스를 가진다면, index_merge 접근 방법 실행
• 그 이외의 경우에는 풀 테이블 스캔
• GROUP BY 절의 인덱스 사용
• 규칙
• GROUP BY절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야
• 인덱스의 앞쪽에 있는 칼럼은 GROUP BY 절에 반드시 명시되야
• GROUP BY 절에 명시된 칼럼이 하나라도 인덱스에 없으면 인덱스를 이용하지 못한다.
• WHERE 조건절에 앞쪽에 있는 칼럼이 동등 비교 조건으로 사용된다면, 그 칼럼이 있는 것과 같은 효과
• ORDER BY 절의 인덱스 사용
• GROUP BY와 조건 동일 + 모든 칼럼이 오름차순이거나 내림차순일 때만
• WHERE 조건과 ORDER BY(또는 GROUP BY)절의 인덱스 사용
• WHERE절과 ORDER BY절이 동시에 같은 인덱스 이용
• WHERE 절의 비교 조건에서 사용하는 칼럼과 ORDER BY절의 정렬 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함
• 가장 빠른 성능
• WHERE절만 인덱스 이용
• 인덱스를 통해 검색된 결과 레코드를 정렬 처리 과정을 거쳐서 정렬(Filesort)
• WHERE 절의 조건에 일치하는 레코드의 건수가 적을 때
• ORDER BY절만 인덱스 이용
• ORDER BY 절의 순서대로 인덱스를 읽으면서, 레코드 한 건씩을 WHERE 절의 조건에 일치하는지 비교해 버리는 형태로 처리
• 아주 많은 레코드를 조회해서 정렬해야 할 때
• p.429 그림 설명
• GROUP BY절과 ORDER BY 절의 인덱스 사용
• GROUP BY 조건과 ORDER BY 조건을 모두 만족할 때만 사용 가능
• WHERE 조건과 ORDER BY 절, 그리고 GROUP BY 절의 인덱스 사용
• WHERE 조건과 ORDER BY 절, 그리고 GROUP BY 절의 인덱스 사용 여부
• WHERE 절이 인덱스를 사용할 수 있는가?
• GROUP BY 절이 인덱스를 사용할 수 있는가?
• GROUP BY절과 ORDER BY절이 동시에 인덱스를 사용할 수 있는가?
• p.432 그림 설명

7.4.3 WHERE 절의 비교 조건 사용 시 주의사항

• NULL 비교
• SQL 표준 NULL의 정의 : 비교할 수 없는 값
• IS NULL 연산자 외에는 칼럼의 값이 NULL인지 알 수 있는 방법X
• 주의점

• 문자열이나 숫자 비교
• 문자열이나 숫자 칼럼을 비교할 때는 반드시 그 타입에 맞춰서 상수를 사용할 것을 권장

• 날짜 비교
• DATE나 DATETIME과 문자열 비교
• MySQL이 내부적으로 변환
• STR_TO_DATE()함수로 형변환을 한 것과 동일하게 처리
• But, 형변환을 한다면 칼럼을 변경하지 말고, 상수를 변경할 것!
• DATE와 DATETIME의 비교
• DATE(date_time) : DATETIME값을 DATE 타입으로 변환해서 비교
• DATE 타입을 DATETIME으로 변환하면 시간은 00:00:00으로
• DATETIME과 DATE의 타입 변환은 인덱스 사용 여부에 영향X
• DATETIME과 TIMESTAMP의 비교
• UNIX_TIMESTAMP()함수의 결과 값은 내부적으로는 단순 숫자 값
• FROM_UNIXTIME() 이나 UNIX_TIMESTAMP() 함수로 변환해서 비교

7.4.4 DISTINCT

• SELECT DISTINCT …
• DISTINCT와 GROUP BY는 정렬의 차이만 있을 뿐 같은 방식으로 처리
• DISTINCT는 SELECT 되는 레코드를 유니크하게 SELECT하는 것이지 칼럼을 유니크하게 조회하는 것X -> 칼럼 일부만 유니크하게 조회하는 방법은 없다!
• 집합 함수와 함께 사용된 DISTINCT
• 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼 값들 중에 중복을 제거하고 남은 값만 가져온다.

• Extra 칼럼에는 출력되지 않지만 내부적으로 임시 테이블을 사용
• salary 칼럼의 값을 위한 임시 테이블을 만들고 유니크 인덱스가 생성되기 때문에 상당히 느려질 수 있는 형태의 쿼리
• 인덱스된 칼럼에 대한 DISTINCT를 사용해 효율적으로 처리 가능

7.4.5 LIMIT n

• LIMIT가 사용된 예제

• employees 테이블에서 WHERE 절의 검색 조건에 일치하는 레코드를 전부 읽어
• 읽은 레코드를 first_name 칼럼 값에 따라 정렬
• 정렬된 결과에서 상위 5건만 사용자에게 반환
• 특징
• 정렬이나 그룹핑 또는 DISTINCT가 없는 쿼리에선 성능 향상이 큼
• GROUP BY나 ORDER BY와 같은 전체 범위 작업은 LIMIT보다 먼저 실행되기 때문에 작업 내용을 크게 줄여주진 못하지만 성능 향상에 도움
• DISTINCT는 정렬에 대한 요건이 없이 유니크한 그룹만 만들어 내면 되어 레코드를 읽음과 동시에 중복 제거 작업을 진행하는데, LIMIT에 지정한 건수만 작업하면 되기 때문에 성능 향상이 큼
• 만약 인덱스를 이용한다면, GROUP BY, ORDER BY, DISTINCT 절이 포함되었다고 하더라도 필요한 만큼의 레코드만 읽도록 만들어주기 때문에 쿼리의 작업량을 상당히 줄여 줌
• 제한 사항
• LIMIT의 인자로 표현식이나 별도의 사브 쿼리는 불가

7.4.6 JOIN

• JOIN의 순서와 인덱스
• 인덱스 레인지 스캔으로 레코드를 읽는 작업 정리

1. 인덱스에서 조건을 만족하는 값이 저장된 위치 탐색 : 인덱스 탐색
2. 탐색된 위치부터 필요한 만큼 인덱스 스캔 : 인덱스 스캔
3. 읽어드린 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어옴

• 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 한 번만 수행하고, 스캔 But 드리븐 테이블은 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복 -> 옵티마이저는 드리븐 테이블을 최적으로 읽는 실행 계획 수립
• 옵티마이저 선택

• 두 칼럼 모두 각각 인덱스가 있는 경우

• 어느 테이블을 드라이빙으로 선택하든 검색 작업을 빠르게 처리
• 통계 정보를 이용해 적절히 드라이빙 테이블을 선택
• 옵티마이저가 선택하는 방법이 최적일 때가 다수
• 한 쪽에만 인덱스가 있는 경우
• 인덱스가 있는 테이블을 드리븐 테이블로 선택
• 인덱스가 있는 테이블을 드라이빙 테이블로 선택하는 것이 더 효율적이더라도 옵티마이저는 드리븐 테이블로 선택할 가능성이 높다  
• 두 칼럼 모두 인덱스가 없는 경우
• 옵티마이저가 적절히 드라이빙 테이블을 선택
• 레코드 건수가 적은 테이블을 드리븐 테이블로 선택하는 것이 훨씬 효율적

• JOIN 칼럼의 데이터 타입
• 조인 칼럼 간의 비교에서 각 칼럼의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 이용 불가능
• CHAR / VARCHAR, INT / BIGINT, DATE/DATETIME 사이는 타입 불일치 발생X
• 문제가 될 가능성이 높은 경우
• CHAR타입과 INT타입의 비교와 같이 데이터 타입의 종류가 다른 경우
• 같은 CHAR 타입이더라도 문자집합이나 콜레이션이 다른 경우
• 같은 INT 타입이더라도 부호가 있는지 여부가 다른 경우
• OUTER JOIN의 주의사항
• OUTER JOIN의 조건은 ON절에 명시
• WHERE 절에 명시하면 옵티마이저가 INNER JOIN과 같은 방법으로 처리

• OUTER JOIN과 COUNT(*)
• 일반적으로 페이징 처리는 테이블의 레코드를 가져오는 쿼리와 단순히 레코드 건수만 가져오는 쿼리가 쌍으로 사용
• 조인에서 불필요한 테이블을 제거하면 같은 결과를 더 빠르게 가져올 수
• OUTER JOIN 제거 가능 조건
• 드라이빙 테이블과 드리븐 테이블의 관계가 1:1 혹은 M:1
• 드리븐 테이블에 조인 조건 이외의 별도 조건X

• OUTER JOIN을 이용한 ANTI JOIN
• 한쪽 테이블에는 있지만 다른 쪽 테이블에는 없는 레코드를 검색할 때
• 보통 NOT IN이나 NOT EXISTS로 처리(레코드 건수가 많으면 성능상의 문제)
• ANTI JOIN

• INNER JOIN과 OUTER JOIN의 선택
• OUTER JOIN과 INNER JOIN은 성능을 고려해서 선택할 것이 아니라 업무 요건에 따라 선택하는 것
• FULL OUTER JOIN 구현
• MySQL에서는 FULL OUTER JOIN 기능 제공 X
• FULL OUTER JOIN 구현하는 방법

• UNION은 두 집합의 결과에서 중복 제거가 필요하기 때문에 UNION LL을 사용하는 것이 더 빠르게 처리
• JOIN과 FOREIGN KEY
• FOREIGN KEY는 데이터의 무결성을 보장하기 위해
• 지연된 조인
• MySQL 서버는 모든 조인이 실행하고 난 다음 GROUP BY나 ORDER BY 처리
• 조인이 실행되기 이전에 GROUP BY나 ORDER BY를 처리하는 방식

7.4.7 GROUP BY

• GROUP BY 사용 시 주의사항
• GROUP BY 절에 명시된 칼럼이 아닌 칼럼은 일반적으로 집합 함수를 감싸서 사용해야

• 오라클 같은 DBMS 에서는 에러
• 쿼리의 가독성 떨어뜨림
• GROUP BY … ORDER BY NULL
• MySQL의 GROUP BY는 그룹핑 칼럼 순서대로 정렬까지 수행

• ORDER BY NULL 을 추가하여 정렬을 막을 수 있다

• GROUP BY col1 ASC col2 DESC
• GROUP BY 절 칼럼에 순서 명시 가능
• ORDER BY와 같은 수행을 하므로 정렬을 혼용하면 인덱스 사용 불가
• GROUP BY … WITH ROLLUP
• 그룹핑된 그룹별로 소계를 가져올 수 있는 기능

• 제한사항
• ORDER BY와 함께 사용 불가
• LIMIT 와 함께 사용되는 경우에는 결과가 혼란스러울 가능성

• GROUP BY … WITH ROLLUP 처리 후 LIMIT 수행
• 레코드를 칼럼으로 변환해서 조회
• 레코드를 칼럼으로 변환
• GROUP BY 결과를 SUM(CASE WHEN …) 기능을 이용해 한번 더 변환
• p. 466 코드
• 하나의 칼럼을 여러 칼럼으로 분리
• 특정 조건으로 소그룹을 나눠서 카운팅 가능

7.4.8 ORDER BY

• ORDER BY 절이 사용되지 않을 때의 정렬
• 인덱스를 사용한 경우에는 인덱스의 정렬 순서
• 풀 테이블 스캔의 경우엔 테이블에 저장된 순서(MyISAM, INSERT 순서X)
• InnoDB 경우엔 프라이머리 키로 크러스터링돼 있기 때문에 프라이머리 키 순서
• 처리 절차에 따라 어떠한 정렬도 보장하지 않는다.
• ORDER BY 사용법 및 주의사항
• 1개 이상의 칼럼으로 정렬 수행 가능
• 정렬 순서는 각 칼럼별로 다르게 명시 가능
• SELECT 되는 칼럼의 순번으로 명시 가능 (ex. ORDER BY 2)
• 숫자 값이 아닌 문자열 상수 사용 시엔 ORDER BY 절 자체를 무시
• ORDER BY RAND()
• 추첨 또는 임의의 사용자 조회를 처리할 때 사용
• RAND() 함수로 발생되는 임의의 값을 각 레코드 별로 부여, 그 임의값으로 정렬
• 문제점 및 해결책
• 인덱스 이용 불가
• 임의 값을 별도의 칼럼으로 생성해두고, 그 칼럼에 인덱스를 생성해 임의 정렬 방법 추천
• 여러 방향으로 동시 정렬
• 정렬 순서가 혼용되면 인덱스 이용 불가
• 칼럼 값을 변형시켜 저장하는 것이 유일한 해결책
• 숫자 타입 :  반대 부호로 변환
• 날짜 타입 : 타임스탬프로 변환하여 음수로 저장
• NULL 값은 항상 최소의 값으로 간주하고 정렬
• 칼럼 값을 변형시키기 어려운 경우(문자열)
• 어플리케이션에서 쿼리를 나눠서 정렬(p.472 코드)

• 정렬해야 할 레코드 건수가 너무 많아서 디스크를 이용할 경우
• 첫 번째 정렬 칼럼에 중복 값이 많아서 두 번째 쿼리의 반복 실행 횟수가 적은 경우
• 함수나 표현식을 이용한 정렬
• 표현식의 결과 순서가 칼럼의 원본 값 순서와 동일할 때(ex. emp_no + 10)
• 변형되지 않은 칼럼을 그대로 사용해줄 것
• 표현식의 정렬 순서가 칼럼의 원본 값과 다른 경우
• 연산의 결과가 칼럼의 값에만 의존적인 경우(ex. ABS(member_age - 30))
• 연산의 결과를 저장하는 칼럼을 만들고, 그 칼럼에 인덱스 생성하여 수행
• 칼럼 이외의 값에 의존적인 경우(ex. ABS(member_age - ?))
• ORDER BY 자체를 튜닝하기는 어려워
• WHERE 절의 조건을 최적화해서 정렬해야 할 레코드 수를 최대한 줄이는 형태로 튜닝

7.4.9 서브 쿼리

• 서브 쿼리 종류
• 상관 서브 쿼리
• 외부에서 정의된 테이블의 칼럼을 참조해서 검색을 수행
• 외부 쿼리가 실행된 후 서브 쿼리 실행
• 독립 서브 쿼리
• 서브 쿼리에서 정의된 칼럼만 참조
• 외부 쿼리보다 먼저 실행되어 외부 쿼리의 검색을 위한 상수로 사용되는 것이 일반적
• 서브 쿼리의 제약 사항
• LIMIT 절과 LOAD DATA INFILE의 파일명에는 사용 X
• IN 연산자와 함께 사용할 땐 비효율적
• IN 연산자 안에서 사용하는 서브 쿼리에는 ORDER BY와 LIMIT를 동시에 사용X
• FROM 절에 사용하는 서브 쿼리는 상관 서브 쿼리 형태 X
• 서브 쿼리를 이용해 하나의 테이블에 대해 읽고 쓰기 동시 작업 X
• SELECT 절에서 사용된 서브 쿼리
• 임시테이블 생성X, 쿼리 비효율적 실행X
• 적절히 인데스를 사용할 수 있게만
• 주의점
• 서브 쿼리의 결과가 NULL이거나 1건만 존재해야
• 조인으로 처리 가능한지 확인 (조인으로 처리가 훨씬 성능이 좋기 때문)
• WHERE 절에 단순 비교를 위해 사용된 서브 쿼리
• WHERE 절에선 상관 서브 쿼리는 범위 제한 조건으로 사용되지 못함
• 독립 서브 쿼리 경우는 서브 쿼리를 먼저 실행 후 상수로 변환하고, 그 조건을 범위 제한 조건으로 사용하는 것이 일반적 RDBMS
• MySQL에서 독립 서브 쿼리 처리 방법

• MySQL 5.1 이하

• 풀 테이블 스캔으로 레코드 한 건씩 읽으면서, 서브 쿼리를 매번 실행해서 서브 쿼리가 포함된 조건이 참인지 비교
• MySQL 5.5
• dept_emp 테이블을 읽기 위해 인덱스를 사용해서 레인지 스캔
• 서브 쿼리가 먼저 실행되어 그 결과를 외부 쿼리 비교 조건의 입력으로 전달했음을 의미
• WHERE 절에 IN과 함께 사용된 서브 쿼리
• WHERE절에 IN 연산자를 상수와 함께 사용할 때는 동등 비교와 똑같이 처리
• MySQL 옵티마이저는 IN(subquery)부분을 EXISTS(subquery)형태로 변환
• 상당히 비효율적이기 때문에 개선 필요
• 바깥 쪽 테이블과 서브 쿼리 테이블의 관계가 1:1 혹은 M:1
• 조인으로 풀어서 성능 개선 가능
• 바깥 쪽 테이블과 서브 쿼리 테이블의 관계가 M:1
• 최종 결과의 건수가 달라질 수 있기 때문에 단순히 변경 불가
• GROUP BY를 추가해 강제로 중복 제거
• 서브 쿼리를 분리시켜서 2개의 쿼리 실행(p.482 코드)
• WHERE 절에 NOT IN과 함께 사용된 서브 쿼리
• IN보다 더 비효율적 처리 (NOT IN -> NOT EXISTS)
• IN 왼쪽값이 NULL 가능성을 가졌기 때문
• 왼쪽 값이 NULL이 아닐 땐 NOT EXISTS 처리
• NULL일 경우엔 NOT EXISTS로 처리 불가
• subquery 결과 확인

• 서브 쿼리가 결과 레코드를 가진다면 NULL
• 서브 쿼리가 결과 레코드를 가지지 않는다면 FALSE
• 이런 경우엔 인덱스 사용 불가 -> 풀 테이블 스캔
• 개선 방안
• 칼럼에 NOT NULL 옵션 혹은 WHERE 절에 IS NOT NULL 명시
• LEFT JOIN으로 개선(p.484)
• FROM절에 사용된 서브 쿼리
• FROM절에 사용된 서브 쿼리는 항상 임시 테이블 사용 -> 비효율적
• 서브 쿼리가 만들어 내는 데이터가 작을 경우엔 메모리에 임시 테이블이 생성돼 큰 문제는 없나, 아닐 경우엔 디스크에 생성되어 디스크의 읽고 쓰기 작업이 병목 지점이 될 가능성
• FROM 절의 서브 쿼리가 꼭 필요한 경우가 아니라면 풀어서 사용하는 것 추천

7.4.10 집합 연산

• UNION(DISTINCT, ALL)
• UNION ALL
• 두 개의 집합에서 중복된 레코드에 대해 별도의 처리 과정을 거치지 않고 반환
• UNION DISTINCT
• 두 개의 집합에서 중복된 레코드를 제거한 후 합집합을 사용자에게 반환
• 성능 비교
• 두 연산 모두 버퍼 역할을 하는 임시 테이블을 사용
• UNION DISTINCT는 모든 칼럼을 이용해 UNIQUE 인덱스를 생성
• 두 집합이 중복된 레코드가 없는 것이 확실한 경우엔 UNION ALL 명령을 사용(UNION만 사용하면 DISTINCT가 실행)
• INTERSECT
• 두 개의 집합에서 교집합 부분만 반환
• INNER JOIN과 동일
• MySQL은 INTERSECT 집합 연산을 제공 X
• MINUS
• 첫 번째 결과 집합에서 두 번째 결과 집합의 내용을 뺀 결과 반환
• NOT IN 이나 NOT EXISTS로 구현 가능
• 레코드 건수가 많아지면 많아질수록 LEFT JOIN을 이용한 ANTI-JOIN 형태가 더 빠른 성능

7.4.11 LOCK IN SHARE MODE와 FOR UPDATE

• InnoDB 테이블에선 SELECT할 때 잠금X
• 잠금이 필요할 때 쓰는 명령
• LOCK IN SHARE MODE
• SELECT된 레코드에 대해 읽기 잠금을 설정
• 다른 세션에서 해당 레코드 변경 금지
• 잠금이 걸린 레코드를 읽는 것은 가능
• FOR UPDATE
• 쓰기 잠금 설정
• 다른 트랜잭션에서 변경, 읽기 모두 금지
• 예시

• 주의점
• 잠금 경합 유발, 데드락 발생 가능성 -> 모니터링 필수

7.4.12 SELECT INTO OUTFILE

• 파일로 저장하는 명령어
• 주의점
• MySQL 서버가 기동 중인 장비의 디스크로 저장
• 결과를 저장할 파일, 디렉터리는 MySQL 서버를 기동 중인 운영체제의 계정이 쓰기 권한 필요
• 이미 동일 디렉터리에 동일 이름이 존재할 땐 에러를 발생하고 종료
• 예시

7.5 INSERT

7.5.1 INSERT와 AUTO_INCREMENT (p.495)

• AUTO_IMCREMENT 제약 및 특성 ( p.495)
• 강제 저장한 값이 현재 값보다 작으면 AUTO_INCREMENT 의 현재 값이 변하지 않음
• MyISAM 의 경우 프라이머리 키 값이 뒤에 있으면 앞 칼럼에 의존해서 증가

CREATE TABLE tb_innodb (

           fd1 CHAR,

           fd2 INT AUTO_INCREMENT,

           PRIMARY KEY(fd1, fd2),

           UNIQUE KEY(fd2)

)ENGINE = INNODB;

CREATE TABLE tb_myisam (

           fd1 CHAR,

           fd2 INT AUTO_INCREMENT,

           PRIMARY KEY(fd1, fd2)

)ENGINE = MYISAM;

INSERT INTO tb_innodb VALUES ('A', NULL), ('A', NULL), ('B', NULL),('B', NULL);

INSERT INTO tb_myisam VALUES ('A', NULL), ('A', NULL), ('B', NULL),('B', NULL);

• auto_increment_offset : 속성의 칼럼 초기 값 정의
• auto_increment_increment : 얼마씩 증가할건지 결정

• AUTO_INCREMENT(AI) 잠금 (p.498)
• 여러커넥션에서 AI 동시에 사용할때 AutoIncrement 잠금이라는 테이블 단위의 잠금 사용
• AutoIncrement 잠금 : AI 현재값 가져올때만 잠금 걸렸다가 즉시 해제
• AI 가 insert 문장으로 한번 증가하면 트랜잭션이 rollback 되더라도 원래 값으로 돌아가지 않아
• innodb_autoinc_mode
• 0 : AutoIncrement 잠금 걸고 한번에 1씩 증가
• 1 : 뮤텍스 이용해 처리

• AUTO_INCREMENT(AI) 증가 값 가져오기 (p.498)
• 가장 최근 저장된 AI 값 가져오려면? select max(member_id) from… 사용...NO
• 현재 커넥션 뿐 아니라 다른 커넥션에서 증가된 AI 값도 가져옴
• last_insert_id() 사용하자
• 현재 커넥션에서 마지막에 가져온 값 반환

CREATE TABLE tb_autoincrement(

           seq_no INT NOT NULL AUTO_INCREMENT,

           NAME VARCHAR(30),

           PRIMARY KEY(seq_no)

);

INSERT INTO tb_autoincrement VALUES(NULL, 'hyeonseok lim');

SELECT LAST_INSERT_ID();

• last_insert_id() 는 쿼리를 실행 해서 시간 잡아 먹어
• Statement.getGeneratedKeys() 사용하면 추가적인 네트워크 통신 필요없어 ( p.499쪽 코드 참고)

7.5.2 INSERT IGNORE (p.501)

• Insert 문장에 Ignore 추가하면 primary key 나 unique key 로 인한 중복 에러가 발생해도 무시하고 작업 진행

7.5.3 REPLACE (p.502)

• replace 는 insert 와 update 기능을 묶은 쿼리와 같은 기능 수행
• 저장하려는 레코드가 중복이면 update 아니면 insert 실행
• update 의 경우 해당 레코드 delete 후 insert 실행
• 중복된 레코드를 찾을때는 체이블의 모든 유니크 값에서 동일 값이 존재하는지 비교
• 테이블에 p 키 와 u 키가 있다면 p 와 u 모두 중복된 값이 존재하는지 비교

CREATE TABLE tb_replace (

           fd1 INT NOT NULL,

           fd2 INT NOT NULL,

           PRIMARY KEY(fd1),

           UNIQUE INDEX ux_fd2 (fd2)

);

INSERT INTO tb_replace(fd1, fd2) VALUES (1,1), (2,2), (3,3), (4,4), (5,5);

mysql> select * from tb_replace;

+-----+-----+

| fd1  | fd2  |

+-----+-----+

|   1   |   1   |

|   2   |   2   |

|   3   |   3   |

|   4   |   4   |

|   5   |   5   |

+-----+-----+

replace into tb_replace(fd1, fd2) values (5,3); -- (5,3 ) 존재 하지 않는 값

mysql> select * from tb_replace;

+-----+-----+

| fd1  | fd2  |

+-----+-----+

|   1   |   1   |

|   2   |   2   |

|   5   |   3   |

|   4   |   4   |

+-----+-----+

(3,3) (5,5) 삭제 되고 (5,3) 투입

7.5.4 INSERT INTO ... ON DUPLICATE KEY UPDATE … (IU) (p.504)

• replace 문과 흡사하게 작동하는 insert 쿼리
• 차이점 replace : 중복된 레코드 선 삭제 후 삽입
• IU : 중복된 레코드 delete 하지 않고 업데이트

CREATE TABLE tb_insertondup (

           fd1 INT NOT NULL,

           fd2 INT NOT NULL,

           PRIMARY KEY(fd1)

);

INSERT INTO tb_insertondup VALUES (1,1), (2,2);

mysql> select * from tb_insertondup;

+-----+-----+

| fd1 | fd2   |

+-----+-----+

|   1   |   1   |

|   2   |   2   |

+-----+-----+

INSERT INTO  tb_insertondup (fd1, fd2) VALUES (1, 100) ON DUPLICATE KEY UPDATE fd2= fd2 +100;

mysql> select * from tb_insertondup;

+-----+-----+

| fd1  | fd2  |

+-----+-----+

|   1   | 101 |

|   2   |   2   |

+-----+-----+

7.5.5 INSERT … SELECT … (p.506)

• 레코드를 읽어 그 결과를 insert 하는 법
• 주의 : 이 쿼리 사용할려면 읽는 테이블 대상 레코드에 읽기 잠금 필요

INSERT INTO temp_employees SELECT * FROM employees LIMIT 10;

7.5.6 LOAD DATA(LOCAL) INFILE …

• CSV 파일 포맷 또는 일정한 규칙을 지닌 구분자로 구분된 데이터 파일을 읽어 MySQL 서버의 테이블로 적재

• CSV 파일 포맷
• 10001, “1943-04-23”, “HYEONSEOK”, “LIM”, “M”, “2015-02-09”
• 10001, “1943-04-23”, “SEHYEONG”, “KIM”, “F”, “2015-02-09”
• …

7.6 UPDATE

7.6.1 UPDATE … ORDER BY … LIMIT n (p. 516)

• MySQL 에서는 update 문장에order by 절과 limit 절을 동시에 사용해 특정값으로 정렬해 그 중 상위 몇건만 업데이트 하는게 가능

UPDATE salaries

           SET salary=salary * 1.1

ORDER BY salary DESC LIMIT 10;

사용자 변수 이용하면…

SET @ranking := 0;

UPDATE salaries

           SET ranking = (@ranking := @ranking + 1)

ORDER BY salary DESC;

이런식으로 아주 유용하게 만들수 잇음 -> 자세한 얘기는 챕터 9에서...to be continued...

• 주의 : LIMIT 절은 있지만 ORDER BY 절이 없는 update 문장은 복제에서 마스터 역할을 하는 MySQL 서버에서 사용하지 않는 편이 좋아
• where  조건절에 일치하는 레코드 가운데 일부를 변경하지만 레코드의 정렬 규칙이 없기 때문에 업데이트 대상으로 선정되 레코드가 마스터와 슬레이브에서 100% 달라질수 밖에 없기 때문

7.6.2 JOIN UPDATE (p.517)

• 두 개 이상의 테이블을 조인해 조인된 결과 레코드를 업데이트 하는 쿼리
• 주의 :  join update 문장이 웹서비스 와 같은 OLAP 환경에서는 데드락을 유발할 가능성이 높으므로 사용하지 않는 것이 좋음
• 배치 프로그램이나 통계용 update 문장에는 유용하게 사용됨

CREATE TABLE tb_test1 (emp_no INT, first_name VARCHAR(14), PRIMARY KEY(emp_no));

INSERT INTO tb_test1

VALUES (10001, NULL), (10002, NULL), (10003, NULL), (10004, NULL);

UPDATE tb_test1 t1, employees e

SET t1.first_name= e.first_name

WHERE e.emp_no = t1.emp_no;

// 쿼리를 우선조인 하기 때문에 테이블의 조인 순서에 따라 update 문장의 성능이 달라짐

// MySQL 에서는 update 에 대해서는 실행 계획을 확인 할 수 없음

// 이럴때는 select 쿼리로 변경해 실행 계획을 확인 해봐야돼

SELECT * FROM tb_test1 t1, employees e

WHERE e.emp_no = t1.emp_no;

• 주의 : JOIN UPDATE 문장에는 group by 나 order by 절을 사용 할 수 없음

UPDATE departments d, dept_emp de

SET d.emp_count = COUNT(*)

WHERE de.dept_no = d.dept_no

GROUP BY de.dept_no;    // 이러지마 에러

UPDATE departments d,

           (SELECT de.dept_no, COUNT(*) AS emp_count FROM dept_emp de GROUP BY de.dept_no) dc

SET d.emp_count = dc.emp_count

WHERE dc.dept_no = d.dept_no;

• STRAIGHT JOIN : 조인의 순서를 결정 (왼쪽: 드라이빙 / 오른쪽 : 드리븐)

7.7 DELETE

• 조인을 이용해 두 개 이상의 테이블에서 동시에 레코드 삭제 가능 / 정렬해서 상위 몇건만 삭제도 가능

7.7.1 DELETE … ORDER BY … LIMIT n (p.520)

• update 문장이랑 유사

DELETE FROM employees ORDER BY first_name LIMIT 10;

7.7.2 JOIN DELETE (p. 520)

• 여러 테이블을 조인해서 레코드를 삭제
• 하나의 테이블에서 레코드 삭제

DELETE e FROM employees e, dept_emp de, departments d

WHERE e.emp_no = de.emp_no AND de.dept_no = d.dept_no

AND d.dept_no = 'd001';

• 3개의 테이블 모두 삭제

DELETE e, de, d

FROM employees e,  dept_emp de, departments d

WHERE e.emp_no  = de.emp_no AND de.dept_no = d.dept_no

AND d.dept_no = 'd001';

7.8 스키마 조작 (DDL)

• 데이터베이스의 구조 및 정의를 생성하거나 변경하는 쿼리

7.8.1 데이터베이스 (p. 522)

• 데이터베이스 생성 (p.522)
• IF NOT EXISTS : 이미 있다면 해당 문장 무시

CREATE DATABASE IF NOT EXISTS employees;

• 데이터베이스 목록(p.523)
• 데이터베이스 목록 나열

SHOW DATABASES;

• 데이터베이스 선택 (p.523)
• 기본 데이터베이스 선택

USE employees;

• 데이터 베이스 속성 변경 (p.523)
• 생성할 때 지정한 문자 집합이나 콜레이션 변경

ALTER DATABASES employees CHARACTER SET=euckr COLLATE= euckr_korean_ci;

• 데이터베이스 삭제 (p.523)
• IF EXISTS : 해당 DB 존재하면 삭제 아니면 무시

DROP DATABSE IF EXISTS employees;

7.8.2 테이블

• 테이블 구조 조회 (p.525)

SHOW CREATE TABLE employees ; // 칼럭의 목록과 인덱스, 외래키 정보를 동시에 보여줌

CREATE TABLE `employees` (

 `emp_no` INT(11) NOT NULL,

 `birth_date` DATE NOT NULL,

 `first_name` VARCHAR(14) NOT NULL,

 `last_name` VARCHAR(16) NOT NULL,

 `gender` ENUM('M','F') NOT NULL,

 `hire_date` DATE NOT NULL,

 PRIMARY KEY (`emp_no`),

 KEY `idx_first_name` (`first_name`)

) ENGINE=INNODB DEFAULT CHARSET=latin1

DESC employees; // 인덱스, 외래키 테이블 자체의 속성을 보여주지는 않음

• 테이블 구조 변경 (p.526)
• ALTER TABLE : 테이블 자체의 속성을 변경 뿐 아니라 인덱스의 추가 삭제, 칼럼 추가 삭제 하는 용도로 사용

ALTER TABLE employees CHARACTER SET ‘euckr’; // 기존 문자열을 변경하는게 아니고 앞으로 추가 될 문자열 변경

• RENAME TABLE (p.528)
• 테이블의 이름 변경

CREATE TABLE temp_emp_stat(hire_year INT NOT NULL, emp_count INT);

RENAME TABLE emp_stat TO backup_emp_stat, temp_emp_stat TO emp_stat;

// 하나의 rename table 명령으로 두 테이블의 이름 변경 작업을 처리

           MySQL 은 네임 락이라는 잠금 이용해 rename table 명령에 있는 테이블에 잠금 걸고

           작업 진행. 테이블을 바꾸는 동안 잠시 테이블이 없어질수 있지만 select 쿼리시

테이블이 없다는 에러를 받는게 아니고 rename table 이 완료될때 까지 기다림

• 테이블의 DB 변경
• 테이블 이름 변경 뿐 아니라 A db 에서 B db 로 옮길때도 사용

RENAME TABLE db1.employees TO db2.employees;

• 테이블의 상태 조회 (p.530)
• 어떤 스토리지 엔진 사용하는지 데이터 포맷을 뭘 사용하는지 등등을 조회 가능

SHOW TABLES status LIKE ‘employees’\G;

\G : 레코드의 칼럼을 라인당 하나씩 표현하게 하는 옵션

• 테이블 구조 복사
• CREATE TABLE LIKE 이용하면 구조 같은 테이블 생성 가느

CREATE TABLE temp_employees LIKE employees;

• 테이블 구조 및 데이터 복사
• CREATE TABLE … AS SELECT 명령 이용해서 다른 테이블로 부터 SELECT 된 결과를 이용해 새로운 테이블 생성 가능

CREATE TABLE temp_empolyees (

           birth_date DATE not null…

           ...

) ENGINE = INNODB

AS

SELECT emp_no, birth_date, first name.. from employees LIMIT 10;

CREATE TABLE AS SELECT 명령은 SELECT 되는 칼럼과 저장할 칼럼의 이름이 일치해야 함

• 테이블 삭제
• 레코드 건수가 많은 테이블을 삭제하는 작업은 상당히 부하가 큰 작업 -> 서비스 도중에 삭제 작업(DROP TABLE )은 수해하지 않는게 좋음

7.8.3 칼럼 변경

• 칼럼 추가
• ALTER TABLE employees ADD emp_telno varchar(20);
• 칼럼 삭제
• ALTER TABLE employees DROP [COLUMN] emp_telno;
• 칼럼 이름이나 칼럼 타입 변경
• 칼럼을 변경하는 경우
• ALTER TABLE employees CHANGE [COLUMN] first_name name VARCHAR(14) NOT NULL;
• 실제 테이블의 데이터는 변경하지 않고 테이블의 구조 정보(메타 정보)만 변경
• 칼럼명 이외의 타입이나 NULL 여부를 변경하는 경우
• ALTER TABLE employees MODIFY COLUMN first_name VARCHAR(200) NOT NULL;
• 칼럼 타입이나 null 여부 변경 할때는 change column 대신 modify column  사용
• 칼럼 변경을 위한 ALTER TABLE 진행 상황
• MySQL 서버 상태 값을 보면 현재 어느정도 ALTER TABLE 이 진행됐고 얼마나 더 기다려야 할지 대략적으로 예측 가능
• ALTER TABLE 명령을 이용해 테이블의 칼럼을 추가/삭제 할때는 MySQL 서버에 두개의 커넥션을 만들어 하나에는 ALTER TABLE 명령을 실행하고 다른 한쪽에는 SHOW GLOBAL STATUS LIKE ‘Handler%’ 명령을 실행하면서 진행상황을 확인 하면 됨

7.8.4 인덱스 변경

• 인덱스 조회 : SHOW INDEX FROM employees;
• 인덱스 삭제 : ALTER TABLE employees DROP INDEX ix_emptelno;
• 칼럼 및 인덱스 변경을 모아서 실행
• ALTER TABLE employees
• DROP INDEX ix_fristname,
• ADD INDEX ix_new_firstname(first_name),
• ADD COLUMN emp_telno VARCHAR(15);

7.8.5 프로세스 조회

• SHOW PROCESSLIST 로 서버에 접속된 사용자의 목록이나 각 클라이언트 사용자가 어떤 쿼리를 실행하고 있는지 확인 할 수 있음

7.8.6 프로세스 강제 종료

• SHOW PROCESSLIST 에서 Id 는 접속된 커넥션의 요청을 처리하는 전용 스레드 번호를 의미함  만약 특정 스레드에서 실행중인 쿼리나 커넥션 자체를 강제 종료 하려면….
• KILL QUERY 4228;    // 4228 번 스레드 종료
• KILL 4228;
•  

7.8.7 시스템 변수 조회 및 변경

• 시스템 변수는 SHOW VARIABLES 라는 명령으로 조회 가능    
• SHOW GLOBAL VARIABLES;
• SHOW GLOBAL VARIABLES LIKE ‘%timeout%’;

7.8.8 경고나 에러 조회

• 쿼리 실행중 에러가 발생하면 쿼리 실행을 중지하고, 에러 메세지를 화면에 자동으로 표시 : SHOW WARNINGS…
• INSERT INTO departments(dept_no, dept_name) VALUES ('12345', 'TestDepartments');
• SHOW WARNINGS; // 워닝의 경우
• 에러메세지가 표시되지 않는 경우 “SHOW ERRORS” 명령으로 조회 가능

7.8.9 권한 조회 (p.546)

• 사용자에게 부여할 수 있는 모든 권한의 이름과 간단한 설명이 표시됨

• SHOW PRIVILEGES;

7.9 SQL 힌트

• 성능 개선 위해 사용됨

7.9.1 힌트의 사용법

• 잘못 사용하면 오류 발생 - 주의 하자

SELECT * FROM employees USE INDEX (PRIMARY) WHERE emp_no = 10001;

SELECT * FROM employees /*! USE INDEX (PRIMARY) */ WHERE emp_no = 10001;

이런식으로 /* 뒤에 바로 ! 표시해서 힌트가 기술 될거란걸 SQL 서버에 알려줌

주석이 SQL 의 일부로 해석됨

7.9.2 STRAIGHT_JOIN

• 조인의 순서를 고정하는 역할

SELECT *

FROM employees e, dept_emp de, departments d

WHERE  e.emp_no = de.emp_no AND d.dept_no = de.dept_no;

이 쿼리의 경우 어느테이블이 드라이빙이 되고 드리븐이 될지 알수 없음

SELECT /*! STRAIGHT_JOIN */ e.frist_name, e.last_name, d.dept_name

FROM employees e, dept_emp de, departments d

WHERE e.emp_no = de.emp_no AND d.dept_no = de.dept_no;

employees -> dept_emp -> departments 순으로 조인이 수행됨

힌트가 있으면 옵티마이저는 힌트를 맹신하고 그 힌트에 맞게 쿼리를 실행 함

7.9.3 USE INDEX / FORCE INDEX / IGNORE INDEX

• USE INDEX : 옵티마이저에게 특정테이블의 인덱스를 사용하도록 권장
• FORE INDEX : USE INDEX 보다 옵티마이저에게 미치는 영향이 더 큰 힌트
• IGNORE INDEX : USE INDEX 나 FORCE INDEX 와는 반대로 특정 인덱스를 사용하지 못하게 하는 용도로 사용
• USE INDEX FOR JOIN : 테이블 간의 조인 뿐 아니라 레코드를 검색하는 용도까지 포함
• USE INDEX FOR ORDER BY : 명시된 인덱스를 ORDER BY 용도로만 사용하도록 제한
• USE INDEX FOR GROUP BY : 명시된 인덱스를 GROUP BY 용도로만 사용하도록 제한

7.9.4 SQL_CACHE / SQL_NO_CACHE

• SELECT 쿼리의 결과를 쿼리 캐시에 담아 둘지 여부를 쿼리에서 직접 선택하는데 이때 사용하는 힌트가 SQL_CACHE , SQL_NO_CACHE
• SQL_NO_CACHE 는 성능을 비교하거나 분석하는데 사용
• SELECT COUNT(*) FROM employees WHERE last_name = “faf”;
• 두번째부터는 쿼리 사용해서 소요시간 확인 애매해.. 이럴때는...
• SELECT /*! SQL_NO_CACHE */ COUNT(*) FROM employees WHERE last_name = “faf”;
• 쿼리 캐시 결과는 사용하지 않고 MySQL 서버가 쿼리를 실행해서 그 결과를 가져오는 전체 과정에서 소요된 시간 확인 할 수 있음

7.9.5 SQL_CALC_FOUND_ROWS

• SQL_CALC_FOUND_ROWS 힌트가 사용된 쿼리를 실행한 다음  FOUND_ROWS() 라는 함수를 이용해 LIMIT 절과 상관 없이 조건에 일치하는 레코드가 몇건이었는지 가져 올 수 있음

SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;

// 처음엔 5개만

SELECT FOUND_ROWS() ;

// 두번째엔 limit 와 관계없이 처음 select 쿼리 만족했던 모든 레코드 건수를 화면에 출력

7.9.6 기타 힌트

• 시간이 아주 남아돌면 매뉴얼을 토대로 천천히 공부해 보기 바람 by 저자

7.10 쿼리 성능 테스트

7.10.2 쿼리의 성능 테스트

• SELECT SQL_NO_CACHE STRAIGHT_JOIN
• e.first_name, d.dept_name
• FROM employees e, dept_emp de, departments d
• WHERE e.emp_no = de.emp_no AND d.dept_no = de.dept_no;
• 출력할때까지 엄청난 시간이 걸림 이런거보단
• SELECT SQL_NO_CACHE COUNT(*) FROM
• ( SELECT STRAIGHT_JOIN
• e.first_name, d.dept_name
• FROM employees e, dept_emp de, departments d
• WHERE e.emp_no = de.emp_no AND d.dept_no = de.`dept_no`) dt;
• 임시테이블을 사용하게 되면서 속도가 엄청 빨리짐, but 조금의 오차는 생길수 잇어

7.10.3 쿼리 프로파일링

• MySQL 에서 쿼리가 처리되는 동안 각 단계별 작업에 시간이 얼마나 걸렸는지 확인 할 수 잇으면 쿼리의 성능을 예측하거나 개선하는데 많은 도움이 될것. 이를 위해 MySQL 은 쿼리 프로파일링 기능을 제공

• 프로파일링을 활성화 하는 방법 : SET PROFILING
• SHOW VARIABLES LIKE ‘PROFILING’;
• 프로파일링 활성화 set profiling = 1;
• 저장된 프로파일링 정보는 한꺼번에 모든것을 볼수 있고 각 쿼리별로 시스템 자원의 영역별로 구분해서 조회하는 것도 가능
  
  
  

SELECT * FROM employees WHERE emp_no=10001;

SHOW PROFILES;

6장 실행 계획

6.1 개요

• 실행 계획전 알고 있어야 할 부분들 살펴보자

6.1.1 쿼리 실행 절차 (p.263)

• 쿼리가 실행되는 과정

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리 - SQL Parsing
2. SQL 의 파싱정보를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택 - 최적화 및 실행 계획 수립 단계(옵티마이저에서 처리)
3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴

6.1.2 옵티마이저의 종류 (p.265)

• 데이터 베이스에서 두뇌와 같은 역할을 담당
• 비용 기반 최적화(Cost-based optimizer, CBO) - 대부분의RDBMS 가 채택

• 쿼리를 처리하기 위한 여러가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획 별 비용을 산출
• 규칙 기반 최적화(Rule-based optimizer, RBO)

• 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립

6.1.3 통계 정보 (p.265)

• 비용 기반 최적화에서 “통계 정보” 는 매우 중요

• 통계정보가 부정확하면 엉뚱한 방향으로 쿼리를 실행해버리기 때문
• MySQL 에서 통계 정보는 사용자가 알아차리지 못하는 순간 순간 자동으로 변경되기 때문에 상당히 동적인 편 - but 레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많으므로 ANALYZE 명령을 이용해 통계 정보를 갱신해야 할 때도 있음
• 통계정보를 갱신하려면…
• ANALYZE TABLE tb_test;

• 파티션을 사용하지 않는 일반 테이블
• ALTER TABLE tb_test ANALYZE PARTITION p3;
• 파티션 사용하는 테이블에서 특정 파티션의 통계 정보 수집

6.2 실행 계획 분석

• EXPLAIN : 쿼리의 실행 계획 확인

6.2.1 id 칼럼

• 단위 SELECT 쿼리별로 부여되는 식별자 값
• SELECT 문장 하나에 여러 개의 테이블이 조인되는 경우 id 값이 증가하지 않고 같은 id 부여

EXPLAIN

SELECT e.emp_no, e.first_name, s.from_date, s.salary

FROM employees e, salaries s

WHERE e.emp_no = s.emp_no

LIMIT 10;

EXPLAIN

SELECT

((SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM departments)) AS total_count;

// id check

6.2.2 select_type 칼럼

• SIMPLE
• UNION이나 서브 쿼리를 사용하지 않은 단순한 SELECT 쿼리
• PRIMARY
• UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 가장 바깥쪽에 있는 단위 쿼리
• UNION
• UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리

EXPLAIN

SELECT * FROM (

(SELECT emp_no FROM employees e1 LIMIT 10)

UNION ALL

(SELECT emp_no FROM employees e2 LIMIT 10)

UNION ALL

(SELECT emp_no FROM employees e3 LIMIT 10)

)tb;

• DEPENDENT UNION
• 쿼리에 UNION이나 UNION ALL로 집합을 결합하는 쿼리
• DEPENDENT는 단위 쿼리가 외부의 영향에 의해 영향을 받는 것을 의미
• DEPENDENT 키워드가 포함된 서브 쿼리는 외부 쿼리에 의존적이므로 비효율적인 경우가 다수

EXPLAIN

SELECT

e.first_name,

(SELECT CONCAT('Salary change count : ', COUNT(*)) AS message

FROM salaries s WHERE s.emp_no-e.emp_no

UNION

SELECT CONCAT('Department change count : ', COUNT(*)) AS message

FROM dept_emp de WHERE de.emp_no = .e.emp_no

)AS message

FROM employees e

WHERE e.emp_no=10001;

• UNION RESULT
• UNION 결과를 담아두는 임시 테이블을 의미
• 단위 쿼리가 아니기 때문에 별도로 id 값은 부여X

EXPLAIN

SELECT emp_no FROM salaries WHERE salary > 100000

UNION ALL

SELECT emp_no FROM dept_emp WHERE from_date > '2001-01-01';

• SUBQUERY
• FROM절 이외에서 사용되는 서브 쿼리를 의미

EXPLAIN

SELECT

e.first_name,

(SELECT COUNT(*) FROM dept_emp de, dept_manager dm WHERE dm.dept_no = de.dept_no) AS cnt

FROM employees e

WHERE e.emp_no=10001;

• DEPENDENT SUBQUERY
• 서브 쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우
• 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브 쿼리보다 처리 속도가 느릴 때가 다수

EXPLAIN

SELECT e.first_name,

(SELECT COUNT(*)

FROM dept_emp de, dept_manager dm

WHERE dm.dept_no=de.dept_no AND de.emp_no=e.emp_no)AS cnt

FROM employees e

WHERE e.emp_no=10001;

• DERIVED
• 서브 쿼리가 FROM 절에 사용된 경우
• 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미
• DERIVED인 경우 생성되는 임시 테이블을 파생 테이블이라고 하는데 이 테이블에는 인덱스가 없어 다른 테이블과 조인할 때 성능상 불리
• 가능하다면 조인으로 해결할 수 있게 변환

EXPLAIN

SELECT *

FROM

(SELECT de.emp_no FROM dept_emp de) tb,

employees e

WHERE e.emp_no=tb.emp_no;

• UNCACHEABLE SUBQUERY (p.276 그림 참고)
• SUBQUERY 중 캐시를 사용하지 못하는 SUBQUERY를 의미
• 캐시를 사용하지 못하는 경우
• 사용자 변수가 서브 쿼리에 사용
• NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브 쿼리 내에서 사용
• UUID()나 RAND()와 같이 결과값이 호출할 때마다 달라지는 함수 사용

EXPLAIN

SELECT *

FROM emplo0yees e

WHERE e.emp_no = (

SELECT @status FROM dept_emp de WHERE de.dept_no="d005");

사용자 변수 (@status) : 다른 커넥션에서는 공유 못하고 해당 커넥션에서만 사용 가능

• UNCACHEABLE UNION
• UNION 중 캐시를 사용하지 못하는 UNION을 의미   

6.2.3 table 칼럼 (p.278 그림 참고)

• 실행계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시
• 테이블의 별칭이 부여된 경우 별칭 표시
• 별도의 테이블을 사용하지 않은 경우엔 NULL 표시
• <>로 둘러싸인 이름이 명시되는 경우는 임시 테이블을 의미

6.2.4 type 칼럼

• 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미
• 성능 순으로 정리
• system
• 레코드가 1건 이하만 존재하는 테이블을 참조하는 쿼리
• const
• 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지며, 반드시 1건을 반환하는 쿼리
• UNIQUE INDEX SCAN 이라고도 함

EXPLAIN SELECT * FROM employees WHERE emp_no=10005;

EXPLAIN SELECT * FROM dept_emp WHERE dept_no='d005';

//type check

• eq_ref
• 여러 테이블이 조인되는 쿼리의 실행 계획에만 표시
• 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장(프라이머리 키나 NOT NULL인 유니크 키 사용)

EXPLAIN

SELECT * FROM dept_emp de, employees e

WHERE e.emp_no=de.emp_no AND de.dept_no='d005';

• ref
• 조인의 순서와 인덱스의 종류에 관계없이 동등 조건으로 검색

EXPLAIN

SELECT * FROM dept_emp WHERE dept_no='d005';

• fulltext
• 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미
• “MATCH … AGAINST ... “ 구문을 사용해서 실행
• 반드시 해당 테이블에 전문 검색용 인덱스가 준비돼 있어야만 가능

EXPLAIN

SELECT *

FROM employee_name

WHERE emp_no=10001

AND emp_no BETWEEN 10001 AND 10005

AND MATCH(first_name, list_name) AGAINST ('Facello' IN BOOLEAN MODE);

• ref_or_null (존재감 없는 접근방식)
• ref 접근 방식에 NULL 비교가 추가된 형태

EXPLAIN SELECT * FROM titles WHERE to_date='1985-03-01' OR to_date IS NULL;

• unique_subquery
• WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리
• 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때

EXPLAIN SELECT * FROM departments WHERE dept_no IN(SELECT dept_no FROM dept_emp WHERE emp_no=10001);

• index_subquery
• IN(subquery)에서 subquery가 중복된 값을 반환할 수 있지만 중복된 값을 인덱스를 이용해 제거할 수 있을 때

EXPLAIN

SELECT * FROM departments WHERE dept_no IN(

SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003');

• range
• 인덱스 레인지 스캔
• 인덱스를 하나의 값이 아니라 범위로 검색하는 경우 (<, >, IS NULL, BETWEEN, IN, LIKE 등)

EXPLAIN

SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003';

• index_merge
• 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식
• 특징
• 여러 인덱스를 읽어야 하므로 일반적으로 range보다 효율성 낮음
• AND와 OR 연산이 연결된 쿼리에서는 최적화 못할 때 다수
• 전문 검색 인덱스를 사용하는 경우 적용X
• 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업 필요

EXPLAIN

SELECT * FROM employees

WHERE emp_no BETWEEN 10001 AND 11000

OR first_name='smith';

• index
• 인덱스 풀 스캔

EXPLAIN

SELECT * FROM departments ORDER BY dept_name DESC LIMIT 10;

• ALL
• 풀 테이블 스캔
• 가장 비효율적 방법

6.2.5 possible_keys

• 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 인덱스

6.2.6 key

• possible_keys 중 최종 선택된 인덱스(최종 선택된 실행 계획에서 사용하는 인덱스)

6.2.7 key_len

• 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값

EXPLAIN

SELECT * FROM dept_emp WHERE dept_no='d005';

6.2.8 ref

• 접근 방법이 ref 방식이면 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 보여주는 값
• 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐을 때 func라고 표시되는데, 가능하다면 이런 변환을 하지 않도록 하는 편이 좋다

EXPLAIN

SELECT *

FROM employees e, dept_emp de

WHERE e.emp_no=de.emp_no;

6.2.9 rows

• 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수
• 예상 값

6.2.10 Extra

• Const row not found
• const 접근 방식으로 테이블을 읽었지만 레코드가 존재하지 않을 때 Extra 칼럼에 표시
• Distinct : 테이블에 있는 값을 중복없이 유니크하게 가져옴

EXPLAIN

SELECT DISTINCT d.dept_no

FROM departments d, dept_emp de WHERE de.dept_no=d.dept_no;

• Full Scan on NULL key :  col1  IN(SELECT col2 FROM…) 같은 쿼리에서 자주 발생
• MySQL이 쿼리를 실행하는 중 col1 이 NULL 을 만나면 예비책으로 풀 테이블 스캔을 사용할거라는 사실을 알려주는 키워드

EXPLAIN

SELECT d.dept_no, NULL IN(SELECT id.dept_name FROM departments id)

FROM departments d;

• Impossible HAVING(since MySQL 5.1) : having 절을 만족하는 레코드가 없을때 Extra 칼럼에 표시
• 이 메세지가 떴다면 쿼리를 제대로 작성하지 못한 경우가 대부분이니 쿼리 다시 점검

EXPLAIN

SELECT e.emp_no, COUNT(*) AS cnt

FROM employees e

WHERE e.emp_no=10001

GROUP BY e.`emp_no`HAVING e.emp_no IS NULL;

• no matching row in const table(since MySQL 5.1)
• 조인에 사용된 테이블에서 const 방식으로 접근할 때, 일치하는 레코드가 없을 경우 나타나는 메시지

EXPLAIN

SELECT *

FROM dept_emp de,

(SELECT emp_no FROM employees WHERE emp_no=1) tb1

WHERE tb1.emp_no=de.emp_no AND de.dept_no='d005';

• No tables used
• From 절이 없는 쿼리 문장이나 “FROM DUAL” 형태의 쿼리 실행 계획에서 출력 되는 메시지

EXPLAIN SELECT 1;

• Not exists
• A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 할때 not in 이나 not exist 를 사용 - anti-join 이라고 함
• NOT IN이나  NOT EXIST를 사용하지 않고, 아우터 조인을 이용해 안티-조인을 수행하는 쿼리에 표시

EXPLAIN

SELECT *

FROM dept_emp de

LEFT JOIN departments d ON de.dept_no=d.dept_no

WHERE d.dept_no IS NULL;

• Range checked for each record(index map :N)
• 매 레코드마다 인덱스 레인지 스캔을 체크(p.307 그림 6-6 참고)

EXPLAIN

SELECT *

FROM employees e1, employees e2

WHERE e2.emp_no >= e1.emp_no;

• Scanned N databases(since MySQL 5.1)
• 메타 정보를 검색할 경우 표시
• N 은 0, 1 or all 의 값을 가짐

• 0 : 특정 테이블 정보만 요청되어 DB 전체의 메타 정보를 읽지 않음
• 1: 특정 db 내의 모든 스키마 정보가 요청되어 해당 db 의 모든 스키마 정보를 읽음
• all : MySQL 서버 내의 모든 스키마 정보를 다 읽음

EXPLAIN

SELECT table_name

FROM information_schema.`TABLES`WHERE table_schema = 'employees' AND table_name = 'employees';

• Select tables optimized away
• MIN() 또는 MAX()만 SELECT 절에 사용되거나 또는 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을 때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 표시

EXPLAIN SELECT MAX(emp_no), MIN(emp_no) FROM employees;

• Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table
• INFORMATION_SCHEMA DB의 메타 정보를 조회하는 SELECT 쿼리의 실행 계획에서만 표시

• Skip_open_table : 메타 정보가 저장된 파일을 별도로 읽을 필요X
• Open_frm_only : 메타 정보가 저장된 파일(*.FRM)만 열어서 읽음
• Open_trigger_only : 트리거 정보가 저장된 파일(*.TRG)만 열어서 읽음
• Open_full_table : 최적화되지 못해 테이블 메타 정보 파일과 데이터 및 인덱스 파일까지 모두 읽음
• unique row not found
• 두 개의 테이블이 각각 유니크 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 표시

• Using filesort
• ORDER BY 처리가 인덱스를 사용하지 못 할 때만 표시
• 조회 된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행
• 많은 부하를 일으키므로 가능하다면 튜닝하거나 인덱스를 생성

EXPLAIN SELECT * FROM employees ORDER BY last_name DESC;

• Using index
• 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 표시
• 커버링 인덱스
• 인덱스 레인지 스캔을 사용하지만 쿼리의 성능이 만족스럽지 못하다면 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경해 큰 성능 향상 가능
• Using index for group-by
• GROUP BY 처리가 인덱스를 이용할 때 표시
• 루스 인덱스 스캔

• 타이트 인덱스 스캔 : AVG(), SUM(), COUNT(*)와 같이 모든 인덱스를 다 읽어야 할 때
• 루스 인덱스 스캔 : MIN(), MAX()와 같이 조회하는 값이 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리
• Using join buffer
• 읽은 레코드를 임시로 보관해두는 메모리 공간을 “조인 버퍼”라고 하며, 조인 버퍼가 사용되는 실행 계획에 표시

EXPLAIN

SELECT *

FROM dept_emp de, employees e

WHERE de.from_date > '2005-01-01' AND e.emp_no < 10904;

• Using sort_union(...), Using union(...), Using intersect(...)
• Using intersect(...) : 각각의 인덱스를 사용할 수 있는 조건이 AND로 연결
• Using union(...)  : 각각의 인덱스를 사용할 수 있는 조건이 OR로 연결
• Using sort_uion(...) : OR로 연결된 상대적으로 대량의 range조건들이 있어서 프라이머리 키만 먼저 읽어 정렬하고 병합한 후에야 비로서 레코드를 읽어서 반환할 수 있을 때
• Using temporary
• 임시 테이블을 사용할 때 표시
• 이때 사용된 임시 테이블이 메모리에 생성됐었는지 디스크에 생성됐었는지는 알 수 없다.
• 인덱스를 사용하지 못하는 GROUP BY 쿼리가 대표적인 형태

EXPLAIN

SELECT * FROM employees GROUP BY gender ORDER BY MIN(emp_no);

• Using where
• MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리하는 경우에 표시

EXPLAIN

SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 10100 AND gender='F';

• Using where with pushed condition
• NDB 클러스터 스토리지 엔진을 사용하는 테이블에서만 표시
• “Condition push down”이 적용됐음을 의미

6.2.11 EXPLAIN EXTENDED(Filtered 칼럼)

• EXPLAIN EXTENDED 명령
• MySQL 엔진에 의해 필터링되어 제거된 레코드는 제외하고 최종적으로 레코드가 얼마나 남았는 지의 비율

EXPLAIN EXTENDED

SELECT * FROM employees

WHERE emp_no BETWEEN 10001 AND 10100 AND gender='F';

6.2.12 EXPLAIN EXTENDED(추가 옵티마이저 정보)

• EXPLAIN EXTENDED 명령 이후, SHOW WARNING 명령
• 옵티마이저가 쿼리를 어떻게 해석했고, 어떻게 쿼리를 변환했으며, 어떤 특수한 처리가 수행됐는 지 등을 판단 가능

6.2.13 EXPLAIN PARTITIONS(Partitions 칼럼)

• EXPLAIN PARTITONS 명령
• 파티션 테이블의 생행 계획 정보를 확인 가능

6.3 MySQL의 주요 처리 방식

• 성능에 미치는 영향이 큰 실행 계획과 연관 있는 단위 작업 알아보자

6.3.1 풀 테이블 스캔 (p.330)

• 풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업. 아래와 같은 경우 풀 테이블 스캔 사용함
• 테이블의 레코드 건수가 너무 작을 때
• where 절이나 on 절에 인덱스 이용할 적절한 조건이 없는 경우
• 조건 일치 레코드 건수가 너무 많은 경우
• max_seeks_for_key 변수를 특정 값으로 설정하면, 옵티마이저는 그 만큼만 읽으면 된다고 판단. 작게 설정할 수록 인덱스를 더 사용하도록 유도

6.3.2 ORDER BY 처리(using filesort) (p.331)

• 정렬 방법
• 인덱스를 이용하는 방법

• 인덱스가 정렬되어 있어 매우 빠름
• INSERT, UPDATE, DELETE 작업 시 인덱스 추가 삭제 작업이 필요
• filesort를 이용하는 방법
• 인덱스를 생성 안해서 인덱스의 단점이 장점으로 바뀜
• 정렬해야할 레코드가 많지 않으면 메모리에서 filesort 가 처리되므로 빨라
• 인덱스를 사용할 수 없는 경우

• 정렬 기준이 너무 많을 때
• GROUP BY의 결과 또는 DISTINCT와 같은 처리의 결과를 정렬
• UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬
• 랜덤하게 결과를 가져와야 하는 경우
• Sort Buffer( 소트 버퍼)
• MySQL이 정렬을 수행하기 위한 별도의 메모리 공간
• 정렬이 필요한 경우에만 메모리가 할당되고 쿼리의 실행이 완료되면 즉시 시스템으로 반납
• 정렬 알고리즘
• 싱글 패스(Single Pass) 알고리즘
• 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 되는 칼럼 전부를 담아 정렬을 수행하는 방법
• 투 패스(Two Pass) 알고리즘
• 정렬대상 칼럼과 프라이머리 키 값만을 소트 버퍼에 담아 정렬을 수행하고, 다시 프라이머리 키로 테이블을 읽어서 SELECT 할 칼럼을 가져오는 알고리즘
• 장단점
• 싱글 패스는 테이블을 한 번만 읽어도 되는 반면 더 많은 소트 버퍼의 공간이 필요
• 정렬 대상 레코드의 크기나 건수가 작은 경우 싱글 패스, 레코드의 크기나 건수가 클 경우엔 투 패스가 효율적(기준 : max_length_for_sort_data 값  혹은 BLOB이나 TEXT 타입의 칼럼이 있을 땐 투 패스)
• 정렬의 처리 방식 ( 밑으로 갈수록 느려져)
• 인덱스를 사용한 정렬
• 반드시 ORDER BY 에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY 의 순서대로 생성된 인덱스가 있어야 함

SELECT * FROM employees e, salaries s

WHERE s.emp_no=e.emp_no

AND e.emp_no BETWEEN 100002 AND 100020;

• 드라이빙 테이블(첫번째 읽히는 테이블)만 정렬(조인 없는 경우 포함)
• 선 정렬 후 조인. 이 방법은 조인에서 첫번째 읽히는 데이블 칼럼 만으로 ORDER BY 절이 작성되어야 함

SELECT * FROM employees e, salaries s

WHERE s.emp_no=e.emp_no AND e.emp_no BETWEEN 100002 AND 100010

ORDER BY e.last_name;