한 동안 일에 치여 살다가,

오랜만에 취미로다가-

Swipe Bricks 라는 게임을 즐겨하던 중,

따라 만들기에 크게 어렵지 않겠는데? 라는 생각으로 시작해서

이틀 꼬박 걸린 것 같은?

순수 Javascript로만 개발했는데-

내가 막 그렇게 꼼꼼한 사람도 아니고, 완벽주의 이런 거 따윈 없는지라-

소소하게 버그가 존재하는 듯하지만.. 

요런 게임 하나씩 개발해서 모아보는 것도 좋을 것 같음,

Excel Export 기능은 자주는 아니지만 간간히 필요할 때가 있다-

자주가 아니기 때문에 쓸 때마다 검색에 의존하고.. 

점점 쉬운 방법을 찾아간다-

이전에 POI를 이용한 Excel 관련 포스팅을 한 기억이 있는데- 이 방법이 훨씬 직관적이고 쉬운 것 같다-

아, 물론 단점이 존재한다-

글자 크기라던가- 뭐.. 여튼 이런저런 서식을 전혀 이용하지 못한다-

(내가 검색을 잘 못해서 그런 걸 수도 있고-)

각설하고-

코드 들어간다-

<script type="text/javascript" src="/js/excel/xlsx.core.min.js"></script>
<script type="text/javascript" src="/js/excel/Blob.js"></script>
<script type="text/javascript" src="/js/excel/FileSaver.js"></script>

요 위에 세개의 js 파일을 넣고- 밑에 처럼..

var data = new Array();
var row = new Array();
 
row.push('val1');
row.push('val2');
row.push('val3');
 
data.push(row);
 
var ws_name = 'Work Sheet1';
var wb = new Workbook();
var ws = sheet_from_array_of_arrays(data);
 
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
 
var wb_out = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type:'binary'});
 
saveAs(new Blob([s2ab(wb_out)], {type:'application/octet-strean'}, 'File_Name.xlsx');

굉장히 간단하다-

아마 코드만 봐도 다 이해가 될 듯-

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값도 비교