 카페 > 하준용 in Net / likeasky님 http://cafe.naver.com/likeasky/78 | |
http://www.phpschool.com/bbs/view.html?id=2590&code=tnt&start=0 http://tunelinux.pe.kr/bbs/read.php?table=linuxinfo&no=18&o[at]=s&o[sc]=t&o[ss]=mrtg&o[st]=a http://www.wowlinux.co.kr/download/specialview.html?id=55&view=1 http://tunelinux.pe.kr/bbs/read.php?table=linuxinfo&no=72
mrtg설치시 gd가 필요하고 gd는 zlib랑 모가 필요하다는데 요즘 배포판에 모두 설치되어 있는 것들임.. 그냥 ./configure 하면 됨.. snmpd 떠 있으면 ./cfgmaker 가 mrtg.cfg 화일 제대로 만들어줌.. 수작업으로 mrtg.cfg 화일 편집해야함(snmpd.conf도 함께.. 리부팅 필요)
Target[traffic]: 2:public@ns2.myfolder.net: ################################################################################ Target[http]: `/usr/local/mrtg-2.9.10/web_count.sh` ################################################################################ Target[mysql]: `/usr/local/mrtg-2.9.10/mysql_count.sh`
Target[load]: .1.3.6.1.4.1.2021.10.1.3.2&.1.3.6.1.4.1.2021.10.1.3.3:public@localhost * 100 ################################################################################ Target[memory]: .1.3.6.1.4.1.2021.4.6.0&.1.3.6.1.4.1.2021.4.15.0:public@localhost * 1024 ################################################################################
Target[disk2]: .1.3.6.1.4.1.2021.9.1.8.3&.1.3.6.1.4.1.2021.9.1.8.4:public@localhost indexmaker mrtg.cfg > index.html 하면 여러페이지를 하나의 인덱스 페이지로 만들어줌.. 크론에 등록해야 함.. |
| 블로그 > 홍이얌 http://blog.naver.com/meetjava/100006369952 | |
[ myisamchk 란 ? ] - DB 테이블에대한 오류 검사 및 오류 복구 유틸리티 - 버전 3.22.x : isamchk 유틸리티 사용 3.23.x : myisamchk 유틸리티 사용 [ myisamchk 사용전 주의사항 ] - mysql 데몬을 stop 시킨후 이 유틸리티를 사용해야한다. - mysql 데몬을 중지시킬수 없는 사항이라면 검사할 테이블에대한 rock을 걸고 검사를 수행하여야만 검사도중에 발생할수있는 오류를 막을수있다. - 모든작업이 그렇듯이 항상 백업을 한후 작성을 수행하는것이 좋을것이다. [ myisamchk 사용법 및 옵션 ] - 해당 테이블이있는 디렉토리로 이동 ( 일반적으로 /usr/local/mysql/var 밑에 위치함 ) 1.일반적인 검사 [root@angelsoma var]myisamchk [table 명] Checking MYISAM file: [table 명] Data records: 271 Deleted blocks: 0 - check file-size - check delete-chain - check index reference - check record links 에러메시지가 없으면 테이블에 오류가 없다는것이다. 2.Global 옵션 [root@angelsoma var]myisamchk -s,--silent [table 명] 에러만 출력한다. [root@angelsoma var]myisamchk -v,--verbose [table 명] -s 옵션보다 많은 정보를 출력한다. [root@angelsoma var]myisamchk -V myisamchk 버젼을 표시한다. 3.Check 옵션 [root@angelsoma var]myisamchk -c,--check [table 명] 테이블의 에러를 check 한다. [root@angelsoma var]myisamchk -e,--extend-check [table 명] 테이블을 좀더 세밀하게 check 한다. 일반적인 방법으로 error를 찾을수없 경우 사용하는 옵션이다. [root@angelsoma var]myisamchk -F,--fast [table 명] 빠른게 테이블 check 한다.정교한 체크는 하지않느다. [root@angelsoma var]myisamchk -C,--check-only-changed [table 명] 테이블을 check 하고,테이블을 check 이후의 상태로 변경한다. [root@angelsoma var]myisamchk -f,--force [table 명] 테이블에 error에 있을경우 강재로 check 한다. [root@angelsoma var]myisamchk -i,--information [table 명] check한 결과의 정보를 통계화하여 보여준다. Checking MyISAM file: insertdb Data records: 8962 Deleted blocks: 0 - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 2 Total: Keyblocks used: 97% Packed: 0% - check record links Records: 8962 M.recordlength: 241 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 8962 Delete blocks: 0 Record data: 2166962 Deleted data: 0 Lost space: 6796 Linkdata: 33634 User time 0.21, System time 0.02 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 92, Physical pagefaults 198, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 [root@angelsoma var]myisamchk -m,--medium-check [table 명] extend-check 옵션보다 check 속도가빠르며,99.9 % 의 에러을 찾을수있다. 4.Repair 옵션 [root@angelsoma var]myisamchk -o -B,--backup [table 명] - recovering (with sort) MyISAM-table 'insertdb.MYI' Data records: 8962 - Fixing index 1 MYD파일을 백업한다. 형식은 [filename-time.BAK]의 파일이 생긴다. [root@angelsoma var]myisamchk -e,--extend-check [table 명] 세부적인 파일까지 복구를해준다.일반적으로 아주 하찮은 에러까지 찾을수 있다.하지만 자포자기의 상태가 아니고서는 이옵션을 사용하지 않는게 좋다. [root@angelsoma var]myisamchk -f,-force [table 명] 이전것의 temporary file을 덥어쒸운다. [root@angelsoma var]myisamchk -l,--no-symlinks [table 명] 심복릭 링크를 따르지않겠다는 옵션이다. 일반적으로 myisamchk 는symlink points를 복구한다. [root@angelsoma var]myisamchk -r,--recover [table 명] unique key를 제외한 대부분를 복구한다. [root@angelsoma var]myisamchk -n,--sort-recover [table 명] sorting하면서 테이블을 복구한다. 심지어 temporary 파일과 같은 아주 큰 파일역시 sorting하면서 복구한다. [root@angelsoma var]myisamchk -o,--safe-recover [table 명] -r 옵션보다 느리게 복구한다.그러나 좀더 섬세한 복구를 지원한다. [root@angelsoma var]myisamchk -q,--quick [table 명] 테이터 파일의 수정없이 복구한다. 5.기타 옵션 [root@angelsoma var]myisamchk -a,--analyze [table 명] key의 distribution 을 분석한다. 만약, distribution 을 산출하고 싶을경우에는 --verbose 나 --describe 라 는 옵션과 동행해서 확인할수있다. [root@angelsoma var]myisamchk -d,--description [table 명] 테이블에 대한 정보를 출력한다. [root@angelsoma var]myisamchk -S,--sort-index [table 명] index 블록을 sort한다. [root@angelsoma var]myisamchk -R[index번호],--sort-records [table 명] index 번호를 기준으로 인덱스를 정렬해준다. 6.검사중 아래의 메시지가출력되면 해당테이블을 사용중이라는 의미이므로 테 이블에 LOCK을 걸든가 데몬을 죽이고 나서 검사 및 복구를해야함. myisamchk: warning: 1 clients is using or hasn't closed the table properly 7.LOCK 걸기 myisamchk 는 테이블에대한 read 만 할수있으면 되기때문데 read 를 제외한 모든것에 lock을 걸면된다. mysql> lock tables [table 명] READ ; mysql> flush tables ; flush tables 는 mysql이 테이블의 내용을 메모리에만 보관하고 실제 테이 블파일에 기록을하지 않았을경우 실제 테이블파일에 기록하라는 의미이다 8.LOCK 풀기 mysql> unlock talbe; 9.Myisamchk 로 복구를 위한 LOCK 걸기 서비스를 죽이지않고 복구를 해야할경우는 write lock를 걸어주면된다. 복구는 write 를 해야하기때문에 write lock를 걸어줘야한다. mysql>lock tables [table명] write; mysql>flush tables; 10.LOCK 풀기 mysql> unlock table; |
| 블로그 > dohyah님의 블로그 http://blog.naver.com/dohyah/140009231142 | |
| 1.반드시 컴파일 하라! 10-30% 속도 향상 ! 소스를 가지고 컴파일 하세요. MySQL 메뉴얼에 따르면 10-30% 속도가 빠르다고 합니다. RPM 이나 바이너리 설치를 하지 마세요 ! 1-2.최신 버전을 사용하라 최신 버전이 좋은 점은 자동 튜닝 하는 것 입니다. 버그를 수정 하구요. 되도록 이면 최신 버전을 사용하세요 !! 2. HEAP 테이블이 가장 빠르다! 일반적으로 가장 많이 쓰이는 테이블 타입은 MyISAM 타입 입니다. MyISAM 타입은 무자게 빠르며, 대용량에도 강합니다. 그러나 트랜잭션은 지원되지 않습니다. 이노디비(InnoDB) 는 트랜잭션이 지원 됩니다. 쇼핑몰에서는 반드시 사용해야 합니다 ^^ HEAP 테이블 타입은 가장 빠르며, 단점은 메모리에 있기 때문에, MySQL에 중지 될 경우 모두 날아 갑니다. 검색을 하고 재검색을 다시 하는 경우, 임시 검색 테이블을 만들어 놓는 것도 좋은 방법입니다. HEAP 테이블 메뉴얼 ! http://www.mysql.com/doc/H/E/HEAP.html HEAP 테이블 만들기 ! mysql>CREATE TABLE email_addresses TYPE=HEAP ( ->email char(55) NOT NULL, ->name char (30) NOT NULL, ->PRIMARY KEY(email) ); 3.mysql 서버 top 보기 mysql 서버의 메모리 상황을 보여 주는 프로그램 입니다. 리눅스나 유닉스의 top 기능을 mysql 에서 가능하게 한것 입니다 . top 정보는 튜닝의 기본 이기 땜시 자주 자주 보아야 합니다. ^^ http://public.yahoo.com/~jzawodn/mytop/ PHP 소스 자료실에 파일 다운 로드 하시면 됩니다. 4.mysql_connect Vs mysql_pconnect 서버 메모리가 최소 2G 이상일 경우 mysql_pconnect 를 추천 함다 ! 연결을 계속 하지 않기 땜시 빠릅니다. ! 그러나 메모리가 2G 이하 일 경우는 mysql_connect 사용하세요 ! 5.int,smallint,tinyint 데이터형 ! int 는 굉장히 큰수 입니다. 4바이트를 차지 하구요. tinyint 는 몇백 까지만 됩니다. 1바이트 구요. 쓸데 없이 int 를 사용하지 마세용 !! 4바이트와 1바이트는 4배 차이 입니다.조그만것 1개 1개가 모여 서버 부하를 일으 킵니다.!! 데이터 량이 얼마만큼 들어가는지 체크 하고 데이터형을 선택 하세요 ^^ 만약 쓸데없는 데이터 형이다 싶으면 alter table 로 데이터 형을 바꾸세요 ! 6.인덱스의 사용 인덱스는 반드시 필요한 곳에만 넣으세요 ! 인덱스를 줄 경우 하드 용량을 더 차지 하기 때문에 속도를 떨어 뜨릴 수 있습니다. 모든 칼럼에 인덱스를 주는 것은 절대 추천 하지 않습니다. 1개의 테이블에 주키외에 2-3 개 이상의 인덱스는 주지 마세요! 주키는 당근 인덱스 입니다. ^^ CREATE TABLE albums ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(80)NOT NULL, INDEX title_idx (title) ); ☞Alter Table 로 인덱스 추가 ALTER TABLE albums ADD INDEX title_idx (title) 결합 인덱스의 경우 너무 많은 인덱스를 사용할 경우 CPU 오버헤드나 하드 오버헤드를 불러 일으 킵니다. 적당히 사용하세요 ^^ http://www.mysql.com/doc/I/n/Indexes.html http://www.mysql.com/doc/M/u/Multiple-column_indexes.html http://www.mysql.com/doc/O/R/ORDER_BY_optimisation.html 6-1. 바보 같은 인덱스의 사용 ? 인덱스는 %$search% 가 먹지 않습니다. 그런디 게시판 제목(Subject) 에 인덱스 걸어 놓고 , 검색을 %$search% 이렇게 하면 될까요? 인덱스 거나 안거나 똑같습니다. !! $search% 이렇게 사용하세요. 그런디.. $search% 사용하면 제목 처음 단어 밖에 검색이 안됩니다. 그렇다면 ? 다른 검색 방법은 ? 7.UDF의 사용 MySQL은 스토어드 프로시져 같은 개념이 존재 하지 않습니다. 그대신 C 언어로 만든 함수를 사용할 수 있습니다. 조금더 빠른 쿼리를 원한다면 UDF 를 사용해보세요 ! UDF 함수 보기 http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.mysql.com/doc/A/d/Adding_functions.html 스토어드 프로시져가 먼뎅? 스토어드 프로시져는 쉽게 말해 MS-SQL 함수 입니다. 오라클에도 아마 있을검당..^^ 그러니까 게시판에서 내용을 넘길때나 불러 올때 mysql 쿼리가 3-4 번 정도 이루어 집니다. 또는 ms- sql 쿼리가 이루어지죵.. 3-4 번 정도 쿼리가 되면..그만큼 디비 접속이 잦아 지기 때문에.. 속도가 느려 집니다. 많게는 10번 정도의 insert into 와 update 가 이루어집니다. 그래서 ms - sql 자체 내에 인서트 함수 나 목록 보기 함수를 만들어 놓습니다. 글구 1번의 ms-sql 접속만 해서 인서트 함수를 불러서 처리하는 것입니다. 그렇기 때문에 2-3 번의 쿼리가 절약 되서 빠르다는 것이죵..ㅋㅋㅋ 또는 10번의 쿼리 할것을 MS-SQL 스토어드 프로시져를 1번만 호출 함으로 해서 디비 접속이 절약이 되죵..ㅌㅌ UDF 를 꼭 사용해야 하는가? 안해도 됩니다.만... 사용하면 좋은점 많습니다. 새로운 함수를 추가 할 수 있으므로 ^^ MS-SQL의 스토어드 프로시져 기능 비스므리 하게 사용할 수 도 있구요... UDF 나 MS-SQL 스토어드 프로시져의 사용법을 익히기 보다는 캐슁을 연구하세용 ^^ 동적인 PHP 를 정적인 HTML 로 만드는 방법을요... 또는 UDF 에서 MS-SQL 스토어드 프로시져 처럼 사용이 가능 합니다. 그 부분을 연구하세요. www.zdnet.co.kr 이나 www.zdnet.com 가시면 기사 파일이 1000,29920,2892.html 파일 이란것을 보게 됩 니다. 어키 구현된것일까요? zdnet 게네 들은 강좌를 원래 부터 HTML 로 만들어서 올리는 것일까용?? HTML 로 만드는 부분도 많이 생각 해야 합니다. 강좌가 1만개 라면, 1만개의 파일이 생성 됩니다. zdnet 의 경우는 조회수가 10만-20만을 넘는 초대형 사이트 이기 때문에 HTML 로 만드는 것이 퍼포먼스가 좋습니 다. UDF 배워 두면..남주지 않습니다. 8.조인보다는 쿼리를 나누어라! 조인(Join)하는 것보다 쿼리를 2개로 나누는 것이 속도가 빠릅니다. 조인을 생각 하기 이전에 쿼리를 나누는 것을 생각 하세요 ^^ 어쩔 수 없는 경우는 당근 조인 해야죠. 글고 서브쿼리는 아직 지원 안됩니다. Ms-SQL이나 오라클에서 서브쿼리 보다는 서브쿼리를 하지 않는 방향의 데이터 정규화를 하세요 ^^ 조인 보다 서브쿼리가 느리다. 서버 쿼리 보다는 조인을 사용하세요 ^^ 9.full text index와 search 3.23.23 부터 mysql 에서는 full text index 를 지원 합니다. 자세한 사항은 아래 ! http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search http://www.mysql.com/doc/F/u/Fulltext_Fine-tuning.html 10. SELECT * FROM sometable SELECT * FROM sometable 에서 * 모든을 사용하는 것은 무식한 방법 입니다. 모든 칼럼을 불러오는 경우는 드물거든요. SELECT code,tadate,see FROM sometable 사용할 것만 불러 오세요 ^^ 11.데이터베이스 정규화 테이블을 아무렇게나 만들면 안됩니다. 데이터베이스 정규화 원칙에 의거, 테이블을 나눌것은 나누고 만드시는 것이 좋습니다. 제1 정규화, 제2 정규화 정도는 사용하셔야 합니다. 게시판을 만들때 아직도 테이블 1개에 만드시나요? 온라인 폴 만들때 , 테이블 1개에 만드시나요? 12.REPLACE INTO문 사용하기 REPLACE INTO albums VALUES (6, 'tood.net') insert 문대신에 replace 문을 사용해보세요. 메뉴얼 보시고 연구하세요 ^^ 주키일 경우 사용하시면 됩니다. 13. explain 사용하기 explain 를 사용하여 테이블의 키 값이 얼마나 잘 활용 되는지 알 수 있습니다. EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS, SHOW PROCESSLIST http://www.mysql.com/doc/E/X/EXPLAIN.html 17.BLOB과 TEXT는 분리하라 BLOB과 TEXT 칼럼은 테이블을 분리 하는 것이 좋다. 다른 칼럼의 내용 보다 크기 때문이다 ! OPTIMIZE TABLE 명령을 자주 사용해라 ! Not null 로 지정 하는 것이 빠르다. varchar 보다 char 이 훨빠르다. |
| 블로그 > 『해킹.... 속임수의 예술....』 http://blog.naver.com/zsup1343/60003542238 | |
| mysql 을 엑셀로...... 우선 mysql 에 접속하여 데이타를 저장합니다... 아마도 load data infile 라는 명령을 아시는 분은 아시겠죠.. ^^ 이 명령은 텍스트 파일을 데이타 베이스로 저장하기 위한 명령입니다.. 이 명령의 반대가.. 데이타를 텍스트로 저장..즉 select * into outfile '파일명' from 테이블 하시면.. 테이블의 자료가 텍스트 형식으로 저장이 됩니다.. 옵션으로는 fields terminated by '|' 구분자를 지정하여 주실수 있읍니다. 그렇게 되면...형식이 데이타|테이타|데이타|데이타 이런 형식으로 저장이 되겠죠... 옵션이 없으면 tab 형식으로 저장이 되고요.. 그럼.. 이 저장된 파일을 윈도우 쪽으로 옮긴후 엑셀을 열고.. 이 파일을 불러오기 합니다.. 그럼.. 엑셀에서 이 파일을 어떤 방식으로 불러올지 물어보더군요.... 기본 형식으로 저장하면.. 저장된 데이타들을 보실수 있으실 것입니다... 설명보다는 직접 해 보시면.. 쉽게 이해하실수 있으실 것입니다. 그럼..좋은 결과 있으시길 바라며... |
| 블로그 > 박군의 블로그 http://blog.naver.com/webmaster23/6332122 | |
정규화
데이터베이스의 설계에서 가장 중요한 것은 현실을 제대로 반영하는 것이며, 이를 어떻게 논리적으로 구성하는가를 결정하는 것입니다. 특히 관계형 데이터 모델에서는 데이터 값들이 2차원의 평면 테이블 형태로 표현하므로 어떤 릴레이션들이 필요하고, 어떤 애트리뷰트가 필요한가를 결정하는 것이 중요합니다.
이젠 앞서서 직관적으로 바라보았던 것들을 '정규화'라는 원리를 도입할 것입니다. 정규화는 관계형 데이터 모델에서 아주 중요한 역할을 하고 있습니다. 학자마다 정규화는 튜닝의 도구 또는 설계 검증의 도구이다라고 의견이 약간씩은 다르지만 결국은 같은 의미를 가지고 있습니다. 현실을 제대로 반영하는 것은 튜닝과 검증이라는 것을 모두 포함하고 있기 때문입니다.
데이터베이스 설계를 할 때 우리는 단계적인 사고방식을 가져야한다고 했습니다. 단계적인 사고 방식에서 윗 단계를 생각해 봅시다. 대부분은 설계의 초기단계에서 복잡한 생각을 하지 않는다고 나중에 누락되지는 않을까 하고 생각하는 분들이 많습니다. 그러나 이러한 것들은 정규화 과정이나 앞의 단계를 거치면서 데이터 모델링은 멋있게 틀을 잡아갑니다. 이렇게 틀을 잡아가는 것중 정규화는 그야말로 아주 큰 역할을 하는 것입니다. 앞에서 행했던 설계를 검증하고, 데이터의 중복을 없앤다 것 자체가 정보의 질을 높이고, 설계의 튜닝을 하는 것이니까요.
정규화란 속성이 제위치에 제대로 찾아가게끔 하는 것입니다. 정규화의 목적은 당연히 데이터의 중복의 최소화와 여러가지 이상(Anomaly)들을 제거함에 있습니다. 데이터가 중복되어 있으면 여러 문제를 일으킬 수 있습니다. 삽입, 삭제, 변경에서 나타는 이상들이 개발자를 괴롭히는 것입니다. 이것은 결국 속성이 제자리에 있지 않기 때문에 발생하는 중복의 문제점 때문이라고 할 수 있습니다.
정규화 과정은 속성간에 관계성, 데이터 종속성, 성능, 데이터베이스의일관성 유지등을 고려해야합니다. 정규화를 검증도구라고 하는 것도 설계가 잘못되면 일어날 수 있는 여러가지 문제점을 예방하는 차원이기 때문입니다. 또한 데이터의 중복을 없앤다는 자체가 엄청난 튜닝의 효과를 가지는 것입니다. 일단 데이터의 중복이 많은 설계는 먼가가 문제가 있는 설계입니다. 데이터베이스의 정의에서도 언급했듯이 데이터베이스는 중복의 최소화로 기존의 파일처리방식이나 수작업 방식에서 오는 정보의 질을 떨어뜨리는 문제점을 없애는 것입니다. 이러한 문제점을 좋은 정보의 질을 유지하기 위한 하나의 정형화된 도구 즉, 정규화를 통해 해결을 하게 되는 것입니다.
정규화는 데이터의 중복으로 인한 문제를 해결하기 위해서 속성들간에 종속성(Dependency)을 분석해서 기본적으로 하나의 릴레이션(테이블)에 표현하도록 분해를 하는 것입니다. 어떻게 보면 테이블을 무작정 쪼개는 것으로 보일 수도 있습니다. 그러나 테이블을 쪼개는 기준은 "함수적 종속"이란 개념으로 쪼개는 것입니다. 즉, 함수적 종속성을 파악한 다음 그 함수적 종속을 기본으로 해서 속성들을 하나의 테이블로 그룹짓는 것입니다.
함수적 종속
그럼 "함수적 종속" 이란 것이 무엇인가 살펴보도록 하겠습니다. 일단 함수라 하면 다음의 그림과 같이 나타낼 수 있습니다.
이것을 테이블로 표현한다면 다음과 같겠지요.
감이 약간 오시나요?? 일단 함수적 종속이란 말에서 함수라는 것에 대해서 살펴보도록 하겠습니다. 함수가 무엇인가요? 다름의 그림을 보고서 설명을 하도록 하겠습니다.
그림처럼 왼쪽은 2라는 원소가 두개가 들어 있습니다. 2를 어떤 함수에 집어 넣어야지 4가 될까요? 일반적으로 생각해 보면 2의 제곱을 구하는 함수에 값을 집어 넣는다면 4가 되겠지요. 그러나 역으로 4를 집어 넣는다면 16이 되버리는 것입니다. 즉, 2와 2는 4가 되기 위해서 함수적으로 종속되어 있는 것입니다. 결국 4는 2와 2가 4가 되기 위한 함수에 종속적이기 때문에 2의 제곱이라는 함수에 종속적이게 만드는 4는 "결정자"라고 부릅니다. 또한 2와 2를 "종속자"라고 합니다.
사실 이렇게 수학적으로 함수적 종속을 설명하였으나 정규화는 시스템을 구축하고자하는 조직내의 의미에 함수적 종속을 설명해야 합니다. 즉, 정규화는 관계형 모델에서 표현하고자 하는 주제가 동일한 속성들끼리 뭉쳐져 있는 것입니다. 그러므로 설계의 초기부터 나타내고 자하는 정보를 의미론적으로 묶는다면 정규화라는 과정의 설계의 검증도구가 되는 것입니다.
위의 예에서 2를 Y로 하고 4를 X라 한다면 함수적 종속의 표현은 "X Y" 로 표기합니다. 이러한 표현을 실제의 예를 들어서 살펴보겠습니다.
학생 릴레이션에서 ...
학번 이름 학번 학과 학번 학년
위에서 보는 바와 같이 학번은 이름, 학과, 학년을 결정하고 있습니다. 즉, 학생 릴레이션에서 각각의 학생을 유일하게 구분지을 수 있는 속성은 학번으로 학번은 기본키의 역할을 하는 속성입니다. 즉, 이름만 가지고는 각각의 학생을 알 수 없다는 것입니다. 제가 옛날에 휴학을 하려고 했는데 시간이 없어서 조교님께 휴학 신청을 대신해달라고 한 적이 있었습니다. 그런데 저와 같은 이름을 가진 다른 사람이 있었는데 그 사람으로 휴학을 한적이 있어서 상당히 난감했던 적이 있습니다. 즉, 저의 이름인 "이재학" 만 가지고는 정확성이 있는 정보가 될 수 없던 것입니다. 만약 교수님께서 "이재학"을 불러오라 라고 한다면 이미 과에 "이재학"이란 이름을 가진 사람이 2명이라는 것을 아는 사람은 학번을 교수님께 되물을 것입니다. 이렇듯 이름은 학번에 종속되어 있다는 것입니다. 이름이 이재학이고, 학과가 정보통신공학과이고, 4학년인 학생은 저 말고도 한명이 더 있습니다. 즉, 이것들은 학번에 종속적이라는 것입니다.
테이블로 표현한 것을 가지고 좀더 살펴보도록 하겠습니다.
9555023, 이재학, 4학년, 정보통신공학과 라는 것은 한명의 학생에 대햔 데이터입니다. 학번, 이름, 학년, 학과중에 대표성을 띄고 있는 것은 학번이고, [9555023, 이재학], [9555023, 4학년], [9555023, 정보통신] 이라고 해야지만 정확한 정보를 표현할 수가 있는 것이죠. 앞에서 언급했듯이 [이재학, 4학년, 정보통신]이라고 해서 정확한 정보가 되는 것일까요? 당연히 학번이 9555023인 학생과 학번이 9839011인 학생을 구별하지 못하는데 문제가 있습니다.
이렇듯 학번은 이름, 학년, 학과를 결정하고, 이름, 학년, 학과는 학번에 종속적입니다. 이렇게 데이터에 대한 의미를 표현한 것을 함수적 종속이라고 합니다.
<참고> 함수 종속에 대한 증명된 추론 규칙 (R 릴레이션) (데이터베이스시스템, 이석호, 정익사)
R1: (반사규칙) A B 이면 A B 이다. R2: (첨가규칙) A B 이면 AC BC 이고 AC B 이다. R3: (이행규칙) A B 이고 B C 이면 A C 이다. R4: (분해규칙) A BC 이면 A B 이다. R5: (결합규칙) A B 이고 A C 이면 A BC 이다.
정규화는 이러한 "함수적 종속"을 기본 원칙으로 하나의 의미를 가지는 집합으로 나누는 작업입니다. 정규화는 1차 정규화, 2차 정규화, 3차 정규화, 보이스/코드 정규화, 4차 정규화, 5차 정규화, 도메인/키 정규화가 있습니다. 이 순서가 높아지는 단계의 정규화일수록 무결성은 강화되나 과도하게 테이블이 쪼개지므로 쓸 때 없는 부하가 걸릴 수 있습니다. 그러므로 현실을 감안해서 어느 정도 수준의 정규화까지 행해야 하는가를 결정해야 합니다. 보통 실무에서는 3차 정규화와 보이스/코드 정규화까지 합니다. 기본으로 3차 정규화까지는 해야 하며, 3차 정규화 과정을 마치고도 사용자의 요구사항에 의해서 여러가지 이상들이 발생할 요지가 있다면 더 높은 차원의 정규화를 해야 합니다. 이제 각 단계별 정규화에 대해서 설명하도록 하겠습니다.
1차 정규화
하나의 릴레이션은 어떤 도메인의 집합입니다. 각각의 속성은 해당 도메인에 속하는 단지 하나의 값을 가져야 합니다. 이것이 1차 정규화 이며, 실제로 1차 정규화도 거치지 않은 테이블이 많이 존재합니다. 예를 들면 다중값 속성들이 그대로 표현될 때 정규화된 테이블이 아닌 즉, 비정규화 테이블인 것입니다. 사원테이블에 사번, 이름, 보유기술, 월급의 속성이 있는데 보유기술은 여러 개를 가질 수 있다는 현실을 생각해 보도록 하겠습니다. 일반적으로 문서를 만들면 다음과 같은 문서가 나올 수 있습니다. 보유기술에서 하나의 속성값에 여러 개의 값이 들어간 것을 볼 수 있습니다. 이러한 표현은 비정규화 된 테이블입니다. 여기서 기본키는 사원번호입니다.
그렇다고 다음과 같이 보유기술을 옆으로 펼친다고 해서 달라지는 것은 없습니다. 이도 역시 1차 정규화된 테이블이 아니라고 볼 수 있습니다. 의미상으로 보유기술1, 보유기술2, 보유기술3는 그냥 보유기술의 종류일 뿐입니다. 그러니 하나의 속성에 여러 개의 값을 다른 표현으로 한 것 뿐입니다.
어떤 독자분은 보유기술 속성에 속성값을 넣는데 콤마로 구분하면 어떻겠냐? 라는 생각을 가질 수도 있습니다. 만약 사원번호 1111 인 사원의 보유기술을 하나더 추가하려면 기본키가 "사원번호" 이므로 "사원번호"가 1111 인 사원이 추가되지 못하는 것을 알 수 있습니다. 즉, 다음과 같은 그림이 되어 삽입을 할 수 없는 형태가 되는 것입니다.
이제 본격적인 정규화에 대해서 알아보겠습니다. 아래의 테이블은 각각의 학생에 대해서 각각의 속성마다 단일값을 가지고 있으므로 1차 정규화된 테이블이 입니다. 여기서 독자분들은 함수적 종속관계를 찾아보아야 할 것입니다. 여러분은 찾아낸 함수적 종속성을 바탕으로 데이터가 많이 중복되어 있고, 그 데이터의 중복으로 인한 여러가지 문제점(이상)들을 찾아내어야 합니다.
이 테이블의 함수적 종속 다이어그램의 다음과 같습니다.
함수적 종속 다이어그램에서 보는 바와 같이 학번은 학생명과 학년을 결정하고, 학번은 학과를 결정합니다. 또한 수강코드는 담당교수와 과목명을 결정합니다. 각각의 학생은 자신이 수강신청한 과목의 성적등급을 알기 위해서 학번과 수강코드가 필요합니다. 담당교수는 학과를 결정합니다. 여기서 자칫 잘못하면 학과가 담당교수를 결정할 수 있다고 볼 수 있는데 하나의 학과에 소속된 교수는 여러명인 것이 보입니다. 즉, 지도교수가 학과를 결정하는 것이지 학과가 교수를 결정하는 것이 아닙니다.
이제 1차정규화된 테이블을 가지고 나타나는 문제점을 살펴보도록 하겠습니다. 이러한 문제점은 함수적 종속과 관련하여 찾아보아야 합니다.
이 테이블은 학번만 가지고는 어떤 과목의 성적의 등급이 얼마인지를 모릅니다. 그러므로 이 테이블의 기본키는 학번 + 수강코드입니다. 여기서 주의할 것은 수강코드라는 속성의 이름이 수강을 해야만 하는 코드가 아니라는 사실입니다. 즉, 수강할때 그 과목을 나타내는 과목의 고유번호응 나타내는 것입니다.
2차 정규화
앞서서 삽입, 삭제, 갱신 이상들이 일어 날 수 있다는 것을 보았습니다. 1차 정규화된 테이블에서 이러한 문제점이 일어나는 원인이 무엇일까요? 원인은 바로 기본키가 아닌 각각의 속성들이 기본키에 종속적이지 않고, 부분적으로 함수 종속이 되기 때문입니다. 즉, 기본키를 제외한 모든 속성이 기본키에 함수적 종속이 아니기 때문입니다. 이러한 문제를 해결하기 위해서는 기본키에 함수 종속을 시킨 것 끼리 따로 테이블을 만들어야 합니다. 2차 정규화된 테이블은 다음과 같습니다. ( 테이블 밑은 점(...)은 생각치 않도록 하겠습니다. )
2차 정규화의 결과로 위와 같은 3개의 테이블이 나왔습니다. 학생과 수강과목은 다:다의 관계를 맺고 있기 때문에 학생의 학번과 수강과목의 수강코드가 합쳐진 것을 기본키로 하여 학생 테이블과 수강과목 테이블을 연관지어 주고 있습니다. 사실 데이터 모델링의 초기단계부터 차근 차근 진행해 오면서 엔티티만 제대로 도출해 냈다면 이와 같은 2차 정규화 작업은 필요가 없습니다. 그러나 이와 같은 검증된 원리를 알고 있다면 애매모호 함이 없어지고, 좀더 정확한 정보를 산출하는 정보시스템이 만들어 질 수 있는 것입니다.
학생 테이블과 수강과목 테이블에 학과라는 속성이 중복되어 존재하는 것이 보입니다. 이것은 어떤 다른 엔티티 집합이 더 존재한다는 것을 의미합니다. 즉, "학과" 엔티티가 "학생"과 "수강과목"과 관계를 맺고 있다는 것입니다. 위의 테이블을 볼 때 학과와 관련된 엔티티와 학생, 수강과목은 1:다의 관계를 맺고 있음을 알 수 있습니다. 즉,
학과 : 학생 1 : 다 학과 : 수강과목 1 : 다
의 관계입니다. (사실 더많은 객관적인 업무규칙이 파악되어야 정확히 알수 있습니다.) 그러므로 속성의 이름은 학과보다는 "학과명"이라고 하는 것이 더 명확하겠지요. 그러나 이 단원에서는 다른 엔티티 집합은 생각치 않고, 이에 대한 고려는 다음 단원에서 하겠습니다. 단지 이것은 정규화를 위한 예제라는 것을 염두해 두시기 바랍니다.
이제는 어느정도 속성들이 자신이 있어야 할 곳에 배치된 것으로 보입니다. 그러나 이러한 2차 정규화 테이블에도 이상들이 존재합니다. 이러한이상들을 살펴보도록 하겠습니다.
2차 정규화된 테이블에서 이상이 일어나는 이유는 기본키가 아닌 다른 속성들 간에 함수적 종속이 일어나기 때문입니다. 이렇게기본키가 아닌 속성들끼리의 종속성을 가지는 것은 이행 종속(Transitive Function Dependency) 라고 합니다. 이러한 개념으로 위의 테이블에서 일어날 수 있는 문제점을 찾아보록 하겠습니다. 먼저 문제의 이행 종속이 일어나고 있는 테이블을 추려내야 합니다. 앞서서 그려본 함수적 종속 다이어그램에서와 같이 이행 종속이 일어나고 있는 테이블은 수강과목 테이블입니다.
삽입이상: 각 과목을 담당하는 교수가 특정 학과에 속한다는 사실을 삽입하려 할 때 과목이 존재하지 않는다면 이 사실을 삽입할 수 없습니다. 즉, 각각의 행을 구분할 수 있는 대표성을 지닌 속성(기본키)가 존재하지 않으므로 삽입이 불가능하다는 것입니다. 기본키는 수강과목 테이블에서 반드시 들어가야만 하는 것인데 이를 무시한채 다른 속성값을 삽입하려 한 것이 문제입니다.
삭제이상: 만약 해당 학과의 커리큘럼이 바뀌어 오상훈 교수가 담당하고 있는 과목인 "자료구조"라는 과목이 없어진다면 오상훈 교수가 정보통신공학과에 소속된다는 사실도 없어지게 됩니다. 오상훈 교수가 정보통신공학과에 소속된다는 사실은 이행종속이 일어나고 있다는 것이며, 역시 2차 정규화된 테이블에서 일어나는 삭제이상도 이행 종속때문이라는 것을 알 수 있습니다.
갱신이상: 만약 박덕규 교수의 소속 학과가 정보통신에서 다른 학과로 변경된다면 박덕규 교수에 해당되는 학과에 대한 속성값을 모두 변경시켜 주어야 합니다. 역시 이행종속이 일어나서 일어나는 이상현상입니다.
3차 정규화
2차 정규화된 테이블에서 이행 종속에 의해서 여러 가지 이상현상이 발생되는 것을 보았습니다. 이러한 여러 이상현상을 제거하려면 어떻게 해야 할까요? 당연히 이상현상의 원인이 되었던 이행종속을 없애면 됩니다. 어떻게 없애야 할까요? 당연히 이행종속을 일으키는 속성들을 묶어서 그룹지어 주면 문제는 해결됩니다.
이렇게 이행 종속성을 제거한 테이블은 3차 정규화된 테이블입니다. 만약 독자분들 중에 여기까지 대충 읽어 보신분들은 아마도 정규화란 것이 테이블을 쪼개는 것이구나 라고 생각하시는 분들도 계실겁니다. 그러나 테이블을 쪼갠다는 개념을 갖지 말고, 좀더 세부적으로 관련성이 많은 것끼리 새로운 그룹을 만드는 개념으로 정규화를 생각하셔야 합니다.
<쉬어가기>
자존심... 여러분은 느껴보셨는지요? 공대인이 마음이 닫혀있다는 것을... 얼마전 웹상에서 알게된 친구가 저에게 메신저로 호출을 하더군요. 그래서 왜 그러느냐고 했더니 지금 큰일났다고 하더군요. 다시 왜 그러냐고 물었더니 지금 자기가 하고 있는 프로젝트가 원래 D사 의 프로젝트인데 하청으로 받아서 하고 있는데, D사 사람이 와서 DB설계 개판(?)으로 했다고 하면서 엄청 깨졌다고 하더군요. 그러더니이거 DB 설계 다시해서 그쪽 사람과 협상해야 한다고 하소연을 했습니다. 그래서 한번 보자고 했습니다. ㅡㅡ;;
저는 그 DB설계 해논 것을 보고 이게 도대체 머냐? 그럴만 하다 라고 했습니다. 그랬더니 자기자신도 모르는 상태에서 했기 때문에 그럴만 하다라는 것을 인정하면서도 너무나도 화가 난다고 하는 것이 였습니다. 왜 화가 나는 것일까요? 자신이 해논 것에 대한 쓸 때 없는 자존심 때문일까요?
이상스럽게 이쪽 분야에서 일하는 사람이나 공부하는 사람들은 지는 것을 싫어합니다. 사실 게임도 아닌데 많은 사람들이 자신이 제시한 솔루션이 가장 옳다라고 우겨서(?) 이기고자하는 경향이 있습니다.(물론 필자도 그런 경향이 매우 짙게 나타납니다. 모르는 것도 전에 알던 지식을 이용해서 논리적으로 엮어서 아는 척하는 합니다. ㅡㅡ;;) 정보기술분야는 너무나도 빨리 발전하고 있습니다. 거의 대부분의 사람이 따라가기 바쁘지요. 물론 필자도 따라가기가 너무 힘듭니다. 그래서 매일 꾀죄죄한 모습으로 학교에서 중국음식에 길들여지면서 고생하는 이유일지도 모르겠습니다. 다음 그림을
여러분은 마음을 열고 다른 사람의 것을 받아들일 수 있어야합니다. 너무나도 빨리 발전하고 있는 기술을 따라가는데 가장 빠른 지름길은 사람과 사람이 나누는 Communication 일것입니다.
보이스/코드 정규화
3차 정규화도 여러가지 이상이 존재합니다. 그렇다면 이상이 발생하지 않는 정규화 과정은 어떤거냐고 의문을 가지는 분도 있을 겁니다. 이상이 발생하지 않는 정규화는 키/도메인 정규화입니다. 이것은 증명은 되었으나, 키/도메인 정규화 테이블을 만드는 구체적인 방법을 발견하지 못했기 때문에 실무에서 직관적으로 사용되는 방법이기도 합니다. 그러나 보통 실무에서는 3차 정규화과정이나 다음에 할 보이스/코드 정규화까지 합니다. 그 이유는 일반적으로 4차 정규화나 5차 정규화 과정을 거쳐야 하는 상황은 거의 발생하지 않기 때문입니다. 이 책에서는 보이스/코드 정규화 과정까지만 언급하겠습니다. 만약 보이스/코드 정규화 과정을 거쳤으나 사용자가 원하는 작업을 수행할 때 이상이 발생한다면 4차 정규화 과정을 거쳐야 할 것입니다. 4차, 5차 정규화는 다른 책을 참고하셔야 할 것입니다.
이제 위의 3차 정규화를 거친 테이블에 대한 이상현상이 발생하는 원인을 분석하고 보이스/코드 정규화에 대해서 언급하도록 하겠습니다.
3차 정규화 과정을 거치 테이블에서 이상현상을 발생시키는 원인은 후보키들이 중첩되어 있다는 것 때문입니다. 후보키는 기본키가 될 수 있는 자격이 있는 속성 또는 속성들입니다. 즉, 하나의 릴레이션에 여러 개의 후보키가 존재하는데 하나 또는 여러 개의 속성이 중첩되어서 후보키될 때 이상현상이 발생할 수 있다는 것입니다. 보이스/코드 정규화 과정은 바로 이러한 문제점을 해결하는 것입니다. 이러한 의미에서 볼 때 보이스/코드 정규형은 엄격한 3차 정규형이라고도 합니다.
보이스/코드 정규형은 릴레이션의 모든 결정자가 후보키이면 보이스/코드 정규형이라고 보는 것입니다. 결정자라는 개념은 어떤 속성을 함수적으로 완전히 종속시키는 속성을 의미합니다. 만약 다음의 업무 규칙이 존재하는 테이블이 있다고 가정 한다면
-. 하나의 과목을 여러 교수가 담당할 수 있다. -. 각 교수는 하나의 과목만을 담당한다. -. 각각의 학생은 같은 과목명을 가진 다른 과목을 수강하지 못한다.
앞서서 언급한 3차 정규화의 문제점인 후보키의 일부가 되는 속성인 "학번"이 중첩되어 있는 것이 보입니다. 즉, 수강_교수 릴레이션의 후보키는 "학번 + 과목명" , "학번 + 담당교수" 입니다. 이 후보키중 "학번 + 과목명"을 기본키라고 가정하겠습니다. 함수 종속 다이어그램에서 보는 바와 같이 "학번 + 과목명"은 "담당교수"를 결정하고, "담당교수"는 "과목명"을 결정합니다. 이런 구조를 가지고 있는 릴레이션의 문제점을 파악해 보도록 하겠습니다.
삽입이상: 만약 이현태 교수도 자료구조를 담당하게 되었다면 수강신청을 한 학생이 있어야만 이와 같은 사실을 입력할 수 있습니다. 만약 "담당교수"의 의마가 해당 과목을 담당하고, 또한 그 학생에 대한 생활지도 등의 "지도"를 할 수 있다면(여기서는 담당과목을 수강하지 않은 학생도 지도할 수 있다는 가정), 과목을 수강하지 않은 학생은 지도교수가 누구인지 결정을 할 수 없게 됩니다.
삭제이상: 학번이 "9655032" 인 학생이 자료구조의 수강 취소를 한다면 오용선 교수가 자료구조를 담당하고 있다는 사실도 함께 삭제됩니다. 이 뿐만 아니라 다른 과목들도 마찬가지로 수강하는 학생이 수강을 취소한다면 과목에 대한 담당교수도 같이 삭제되므로 이상현상이 일어납니다. 만약 다른 수강 신청자가 있다면 이와 같은 사실은 같이 삭제되지 않으나 현재 상황으로 볼 때 어떤 교수가 어떤 과목을 담당하고 있는지를 나타내는 것이 한 개의 투플(행)뿐이기 때문에 이러한 문제를 해결되어야 합니다.
갱신이상: 만약 이현태 교수가 "DB" 에서 "네트웍 프로그래밍"으로 담당과목이 바뀌었다면 3개의 투플(행)을 모두 변경해주어야 합니다.
이러한 문제점은 보이스/코드 정규화 과정을 거치면 해결되는 문제입니다. 즉, "모든 결정자가 후보키" 가 되게 하면 되는 것입니다. 다음은 보이스/코드 정규화의 결과입니다.
이제 여러분은 1차 정규화에서 3차 정규화 까지를 종합적으로 살펴볼 필요가 있습니다. 즉, 이러한 원리만 알고 있다면 바로 3차 정규화 또는 보이스/코드 정규화까지 직접 도출이 가능합니다. 직접 도출하는 예를 들어 보겠습니다. 다음과 같은 스키마가 존재하다고 가정하겠습니다.
대출 (대출번호, 고객명, 지점명, 지점위치, 자산, 대출합계)
이 스카마는 어떤 은행은 대출에 관련된 스키마입니다. 이 스키마를 가지고 함수적 종속만 파악한다면 나머지 보이스/코드 정규형을 도출하는 과정은 간단합니다. 다음은 이 스키마에 대한 함수적 종속을 나타내는 것입니다.
<함수적 종속> 지점명 자산 지정명 지점위치 대출번호 대출합계 대출번호 지점명
도출한 R1, R2, R3, R4, R5는 모두 보이스/코드 정규형을 만족합니다. 각각의릴레이션의 모든 결정자가 후보키입니다. 그러나 이렇게 너무 불필요한 정규화는 결과적으로 성능을 떨어뜨릴 수 있습니다. 그러므로 다음과 같은 통합작업을 거쳐야 합니다.
결과적으로 R1(지점명, 자산), R2(지점명, 지점위치), R3(대출번호, 대출합계), R4(대출번호, 지점명), R5(대출번호, 고객명)으로 일단은 테이블을 최대한 분해하였습니다. 그러나 R1과 R2는 기본키가 같으므로 통합할 수 있습니다. 그러므로 R1_2 (지점명, 자산, 지점위치) 로 통합되고, R3와 R4, R5가 기본키가 같으나 R3, R4와 R5는 은행(R3, R4)과 고객(R5)으로 서로 다른 것을 나타내므로 R3와 R4는 통합되고, R5는 독립적으로 존재하게 됩니다. 즉, (R3, R4)와 R5는 표현하려는 정보가 틀리기 때문에 통합이 불가능합니다. 마지막에 나온 R5는 원래 정규화되기 전의 원래 테이블의 기본키가 됩니다. 결과적으로 다음과 같이 보이스/코드 정규화가 이루어졌습니다.
R1_2 (지점명, 자산, 지점위치) R3_4 (대출번호, 지점명, 대출합계) R5 (대출번호, 고객명)
결과적으로 정규화라는 과정은 함수적 종속이라는 하나의 원칙으로 관련성으로 속성들을 묶어서 데이터의 중복을 없애고, 데이터의 중복에 의한 여러가지 이상현상을 없애는 유용한 도구입니다. 데이터의 중복이 최소화되는 자체는 시스템이 가장 가벼운 데이터를 가지고 처리하기 때문에 전체적인 시스템의 성능이 높아지기도 하는 것입니다.
키/도메인 정규화
'정규화란것은 '함수적 종속'관계를 파악하는 것입니다. 이 종속관계를 파악하여 속성이 원래 갈 자리에 가게 하는 것입니다. 즉, 주제에 맞는 한 객체가 관련된 업무에 관한 속성들이 있어야 할 곳에 있게 하는 것입니다. 우리는 1차 정규화에서 보이스/코드 정규화까지 알아보았습니다. 보통 실무에서는 3차 정규화나 보이스/코드 정규화 때에 따라서는 아주 가끔씩 4차정규화를 행합니다.
그러나 검증은 되었으나 그 방법이 찾아지지 않은 키/도메인 정규화를 이 글에서 이야기하고자 합니다. 키(key)라는 것은 객체를 유일하게 구별할 수 있는 속성중에 가장 관련된 대표적인 것을 이야기 합니다. 이 키에 함수적으로 모두 종속되고, 속성의 도메인이 맞다면 즉, 모든 제약이 키와 도메인의 정의에 따른 논리적인 결과인 것은 모두 키/도메인 정규화인 것입니다. 이것은 완벽한 정규화입니다. 3차 정규형은 기본키에 모두 함수적 종속적인 것들로 테이블을 분리하고, 기본키가 아닌 속성들끼리의 종속성 즉, 이행종속을 일으키는 속성들을 다른 테이블로 옮기고 그 테이블에 기본키를 정의할 수 있으면 됩니다. 이와 같이 분리된 테이블은 기본키를 가지게 됩니다. 이러한 개념으로 테이블을 봤을때 데이타가 중복되어 나타나는 것들 잘 살펴보면, 왜 중복이 일어났는지 알수 있을 것입니다.
정규화의 해법들이 키/도메인 정규화 빼고는 모두 나와있습니다. 그러나 직관적으로 바라본다면 해법이 나와 있지는 않지만 키/도메인 정규화가 더 쉽습니다. 또한 초기에 엔티티를 선정할때 우리가 시스템화 하고자하는 관련된 것들끼리 모인 즉, 엔티티를 잘 선정한다면 직관적인 관점에서 3차정규화는 충분히 할 수 있으리라 생각합니다. 그렇다고 정규화 과정을 무시해서는 안됩니다. 이렇게 직관적으로 설계를 하면서 나갈때는 정규화는 검증도구가 되는 것입니다. 학자에 따라서 정규화는 검증도구다 또는 튜닝도구다라고 하는 의견들이 분분합니다. 그러나 정규화는 반드시 필요한 것이 틀림이 없습니다.
도메인/키 정규화에서 중요한 단어는 제약, 키, 도메인입니다. 한가지 주의할 것은 제약에 시간의 개념을 뺏다는 것입니다. 엑기스만 뽑는다면 키와 도메인에 대한 제약을 준수시켰을때 모든 제약이 준수되는 릴레이션은 키/도메인 정규형입니다.
다시 핵심단어 키, 도메인, 제약 이 세가지의 관점에서 살펴보겠습니다. 키라는 것은 객체들을 유일하게 구분지어 주는 속성입니다. 즉, 속성들중 대표하는 것을 말합니다. 이 속성들이 가질수 있는 값들의 범위를 정의한 것이 바로 도메인입니다. 독자들중에 아시는 분이 별로 없으시겠지만 푸리에변환 같은 것을 보면 시간 도메인에서 주파수도메인으로 주파수 도메인에서 시간 도메인으로 변환을 할 수 있습니다. 시간 도메인에서 본다면 이 값들은 절대로 변환과정을 거치지 않고는 시간이라는 단위밖에 가지지 못하는 것입니다. 실제로 주민번호를 본다면 생년월일 담에 오는 1이란 숫자는 남자밖에 가지지 못하는 숫자입니다. 이 도메인을 벗어난다면 현실에 맞지 않게 되는 것입니다. 누누히 얘기하지만 데이타베이스는 현실을 최대한 반영하는 것입니다.
정리하자면 키라는 것은 "unique + not null + 대표성" 입니다. 도메인은 앞에서 얘기한 것처럼 속성이 가질수 있는 값의 범위이고 가질수 있는 꼭 그것을 가져야만 하는 의미입니다. 이것은 현실의 제약이라고 볼수 있으며, 이러한 제약이 지켜진다면 이것은 완벽한 이상이 없는 정규형입니다. 정규화의 정리
이제 앞에서 살펴보았던 정규화에 대해서 의미로만 따져도록 하겠습니다. 필자의 경우는 정규화 과정은 검증도구로 사용하는 편입니다. 사실 함수적 종속이란 것이 조직의 범위내에서 통용되는 의미에 따라 틀려집니다. 그러므로 모델링을 하기전 단계에서부터 정보시스템을 구축하려는 조직에서 사용되는 정보의 의미를 파악하는 것이 더 중요하다고 합니다.
3차 정규화된 테이블을 보면 테이블마다 어떤 정보들을 나타내기 위해서 데이터 들이 뭉쳐있습니다. 즉, 각각의 속성들이 뭉쳐서 어떠한 하나의 정보(의미)를 만들어 내기 때문에 그 의미만 잘 파악한다면 앞에서 행했던 것처럼 바로 보이스/코드 정규화까지 직접 도출이 가능한 것입니다. 그냥 어떤 "의미"를 나타내기 위해서 그룹짓는 과정이라고 하기엔 너무 애매모호 합니다. 그래서 함수적 종속이란 개념을 도입하여 누구나 고개를 끄덕이게 만든 것이 정규화입니다.
독자가 파악해야 할 것은 사용자의 정보가 무엇인지 정확하게 판단하여야 할 것입니다. 만약 사용자의 요구사항이 정확하게 파악되면, 그 요구사항을 정확히 반영하기 위해서 엔티티를 도출하고, 각각의 속성들을 배치해야 합니다. 엔티티는 속성의 집합이기도 합니다. 하나의 엔티티가 다른 엔티티와 관계를 맺고, 어떠한 정보를 만들어 낼 수 있습니다.
일반적으로 데이터 모델링은 하향식(Top-Down)의 방식으로 설계를 하고, 정규화를 통한 하향식(Bottom-up) 방식으로 검증을 하는 방법론을 사용합니다. 이제 속성을 가지고 다음 그림을 살펴보도록 하겠습니다.
제일 먼저 파악해야 할 것은 "관련성" 입니다. 릴레이션이란 것이 속성들이 어떠한 관련성에 묶여서 있는 모습입니다. 그러니 관련성이라는 의미는 매우 중요한 것입니다. 이것은 기본키와의 관련성입니다. 하나의 개체( 학생으로 하였을 경우 학번이 9555023인 학생 하나는 인스턴스입니다.)를 대표하는 것이 기본키이기 때문입니다.
두번째는 파악해야 할 것은 속성의 도메인입니다. 각각의 속성은 가질 수 있는 값의 범위 즉, 도메인을 가지고 있습니다. 이 도메인에서 표현할 수 있는 속성값들을 대표할 수 있는 것들을 찾는 것입니다. 이 도메인도 관련된 엔티티의 범위를 가지기 때문에 관련성이라는 것은 매우 중요합니다.
의미상으로 볼 때 학번과 학생명이 같은 객체의 다른 표현인 것을 알 수 있습니다. 그러나 한 학년에 속하는 학생이 여러명인 것을 알 수 있지만, 학년자체는 독립적으로 존재할 수 있는 즉, 엔티티가 아니라 속성이라는 것입니다. 결과적으로 이 릴레이션은 "학생" 엔티티 집합이 포함된 것입니다.
이러한 방식으로 속성값을 살펴보면 위의 그림이 나올 수 있습니다. 그림에서 학과명과 관련된 것을 살펴보면, 일단 학번이 이름과 학년을 결정하는 것은 앞에서 체크었으므로 이 두 속성은 제외하고 학번으로만 생각해보도록 하겠습니다. 학번이 학과명을 결정하나요? 이 부분은 상당한 혼돈의 여지가 있습니다. 그러나 좀더 원천적으로 생각하면 데이터베이스 시스템을 개발하려는 도메인이 무엇인가요? 바로 "학교"입니다. 즉, 학교에는 기본적으로 "학생"과 "학과"가 존재해야 "학교"가 존재할 수 있는 것입니다. 즉, 학과와 학생은 기본엔티티 집합인 것입니다. 이렇게 "닭이 먼저냐? 달걀이 먼저냐?" 라고 따지는 상황이 온다면 이것은 기본엔티티 집합입니다. 그러므로 학과명은 "학과" 엔티티 집합의 속성입니다. 그러므로 이것은 외부키인 것입니다. 그렇다면다른 속성은 어떨까요? 당연히 다른 속성들도 따져볼 것이 못되는 것입니다. 만약 관련이 있다면 그것은 외부키로 의 기능을 하는 속성입니다.
결과적으로 이 테이블에서 도출할 수 있는 엔티티 집합은 "학생", "수강(또는 과목)", "학과", "교수" 입니다. "학생" 과 "수강" 은 다:다의 관계를 맺고 있으므로 "수강코드, 학번, 등급" 은 이 다:다의 관계를 해소한 것이 되는 것입니다.
이렇게 엔티티와 속성과 관계를 도?하는 것은 데이터 모델리의 핵심입니다. 이와 같은 기본적인 것만 확실히 파악이 된다면 정규화는 데이터 모델링의 검증의 도구와 튜닝의 도구로 써 훌륭한 역할을 할 것입니다. |
| 카페 > 디자인같은 프로그램 강좌 / 디플타임님 http://cafe.naver.com/dptime/24 | |
정규화(NORMALIZATION) 테이블은 행(row)들과 열(column)들로 구성된 하나의 실체이며 데이터의 컬렉션인 엔티티(entity)이다. 하나의 테이블은 다른 테이블들과의 관계(relationship)를 맺을 수 있다. 이 관계 구성의 논리화가 바로 정규화로써 이루어지게 된다. 설계의 출발은 데이터 저장 매체인 엔티티(테이블)들을 구성하는 것이다. 일단은 서비스 흐름에서 찾아지는 기본적인 엔티티를 찾아서 그 엔티티로부터 정규화를 거치는 예를 들어 보도록 한다. 전자제품 대리점에서 고객이 물건을 주문하면 그 고객의 주문 정보가 생성이 된다. 바로 첫번째 엔티티가 생성이 되는 것이다. 그러면 ‘주문’이라는 엔티티에는 어떠한 항목들(필드 설정)이 들어가야하는지 다음의 테이블로 구현을 했다.
1차 정규형식(1NF, first normal form) 중복된 데이터를 찾아보기 위해 ‘주문’테이블에 다음처럼 발생 가능한 데이터를 입력해 보자. 이 테이블에는 하나의 주문에 대한 모든 정보가 다 들어 있다. 고로, 중복된 데이터가 있을수 있는 것이 된다.
이 테이블은 주문에 대한 자세한 정보가 들어가 있다고 했다. 그러면 왜 이테이블을 분할해야 하는가? 그 이유는 다음과 같다. ㄱ. 데이터를 여러 번 저장하는 것은 공간 낭비 ㄴ. 반복적인 데이터가 존재한다는 것은 데이터의 이동량이 더 크게 된다. 따라서 데이터 버스나 네트워크 대역폭에 더 많은 부담이 생긴다. 이는 전반적인 성능에 상당한 악영향을 미친다. ㄷ. 반복 데이터들 간의 서로 모순된 데이터를 저장할 수 있다. 이것은 데이터 무결성이 깨져버린 것이다.
분리시키는 과정을 거쳐야 하기에 속도가 떨어지게 된다.
1차 정규화를 시작하자. 이 테이블에서의 문제는 다음과 같다. 된다. ㄱ. 고객 정보 부분을 다른 테이블로 분할을 할 때 생각을 해야할 것이 있다. 주문 테이블에서 떼어 낸 후 연결을 할 매개체를 지정해야 하는 것이다. 그 연결은 ‘고객번호’필드로써 설정을 했다. 그러므로 주문 테이블에서 고객번호 필드만을 남겨두고 고객 정보 모두를 제거 한다.
고객 정보는 다음과 같이 테이블을 구성한다. 이렇게 테이블을 분할함으로써 왕관의 데이터가 한번만 존재하게 되었다. 이것이 바로 중복 데이터의 제거이다. 이러므로 공간 절약과 중복 값들 사이의 모순을 방지할 수 있게 된 것이다.
결과 테이블을 보면 상품의 분할이 이루어져 원소성을 유지함을 알 수 있다. 그런데 기본 키 필드인 ‘주문번호’가 기본 키의 유일성이 깨져있다. 결과를 위한 각각의 행들은 고유한 데이터로써 분할은 했으나 기본 키 설정이 부적절하게 되어 데이터 식별이 어렵다. 이를 해결하기 위해서는 기본 키 추가 필드를 하나 더 만들도록 하자(다른 방법이 없는 것은 아니지만 지금으로써는 최선이라고 본다). 바로 복합 키를 설정하겠다는 뜻이다. 고객이 상품 주문 순서에 따른 필드를 하나 추가 하여 주문번호 필드와의 복합으로 기본 키 설정을 해 보면 다음의 테이블과 같이된다.
위 결과와 같이 각 주문에 대한 순서를 정하여 두개의 필드를 기본 키 설정으로 하게 되면 각 행들을 고유하게 식별할 수 있게된다.
ㄱ. 2NF의 규칙 만족해야 한다. [주문상세] 테이블을 보면 기본 키가 아닌 상품번호에 의존하는 필드들이 있다. 바로 상품명과 단가 필드들인데 이 필드들은 다른 테이블로 분할을 해야 한다.
이렇게 해서 3차 정규형식중 ‘키가 아닌 필드에 의존하는 필드가 없어야 한다’를 만족 시켰고 나머지 하나인 필드연산에 의한 결과값을 가지는 필드는 아예 만들질 않았다. 그러면 결과값 필드 라는 것이 무엇일까? 다음의 예제 테이블에서 이것을 알아보자
이 테이블들을 관계 형성을 하는 다이어그램으로 나타내 보자 실습을 위해서 EM에서 ‘shop’ 이라는 데이터베이스를 생성을 한다(3M). 다이어그램을 실행후 다음의 테이블들을 생성하고 관계설정을 한다.
 |
| 블로그 > Greate Teacher Onizuka http://blog.naver.com/semi7623/100005610936 | |
쿼리 결과를 파일로 저장하기 위해서는 into outfile를 사용한다. mysql>select * into outfile 'filename' from table_name; |
| 블로그 > loves0508님의 블로그 http://blog.naver.com/loves0508/2941304 | |
| MySQL이 지원하는 컬럼타입 이 글은 MySQL사이트에서 제공하는 매뉴얼 중에서 컬럼관련 부분을 기초로 번역 및 정리한 내용입니다. 더욱 자세하고 내용을 원하시면 mysql의 document를 참고하십시요. 자 그럼 시작하겠습니다. 1. Column types MySQL은 numeric types, date and time types, 그리고 string (character) types의 총 세가지 타입을 지원합니다. 아래에는 MySQL이 지원하는 컬럼 타입을 적었는데 바로 밑의 것은 컬럼타입을 설명하는데 사용한 문자에 대한 설명입니다. M 최대 표시 크기를 나타냅니다. 최대크기는 255입니다. D 부동소주점 타입에 적용되는 것으로 소수점아래의 자릿수를 나타냅니다. ZEROFILL 입력한 값의 자릿수에 여유가 있으면 그 부분을 모두 0으로 채웁니다. UNSIGNED 0과 양수만 사용합니다. Square brackets (`[' 과 `]')은 옵션으로 특별한 사항을 나타내는데 사용합니다. A. NUMERIC TYPES 주의! : 만일 컬럼에 ZEROFILL를 명기하면, MySQL은 자동적으로 UNSIGNED 특성을 컬럼에 적용합니다. TINYINT[(M)] [UNSIGNED] [ZEROFILL] 매우 작은 정수. 사용범위 : -128 to 127. unsigned로 정의하면 0 to 255. SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 매우 작은 정수. 사용범위 : -32768 to 32767. unsigned로 정의하면 0 to 65535. MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 중간 크기의 정수. 사용범위 : -8388608 to 8388607. unsigned로 정의하면 0 to 16777215. INT[(M)] [UNSIGNED] [ZEROFILL] 보통 크기의 정수. 사용범위 : -2147483648 to 2147483647. unsigned로 정의하면 0 to 4294967295. INTEGER[(M)] [UNSIGNED] [ZEROFILL] INT와 같음. BIGINT[(M)] [UNSIGNED] [ZEROFILL] 큰 정수. 사용범위 : -9223372036854775808 to 9223372036854775807. unsigned로 정의하면 0 to 18446744073709551615. Note! 모든 계산은 부호가 있는 BIGINT 또는 DOUBLE 타입의 값으로 이루어집니다. 그러므로 bit 함수를 제외한 9223372036854775807보다 큰 정수는 사용하지 마십시오. 연산 대상 둘 다 INTEGER값이면, -,+와 *는 BIGINT 연산을 사용할 것입니다. 이 의미는 만일 두 개의 BIGINT를 당신이 곱할 경우 (또는 함수의 계산 결과가 정수를 반환할 경우), 그 결과가 9223372036854775807보다 클 경우에는 원하지 않는 결과를 얻게 된다는 것입니다. FLOAT(precision) [ZEROFILL] 부동소수점 수(일반적으로 말하는 소수). unsigned는 사용할 수 없습니다. precision(정밀도)은 4 또는 8만 사용가능합니다. FLOAT(4)은 single-precision의 수를 나타내고, FLOAT(8)은 double-precision의 수를 나타냅니다. 이런 타입은 타입과 바로 밑에서 기술하는 FLOAT와 DOUBLE 타입과 같습니다. FLOAT(4)와 FLOAT(8) 은 FLOAT 과 DOUBLE 타입과 같습니다. 그라나 소수부분의 표시 크기나 수는 정의되지 않습니다. MySQL 3.23에서, 이것은 true floating point value입니다. 이보다 이전 버전에서는, FLOAT(precision)은 항상 2 decimals이었습니다. 이 구문은 ODBC 호환성을 위해 미리 제공되는 것입니다. FLOAT[(M,D)] [ZEROFILL] 매우 작은(single-precision) 부동소수점 수. unsigned 값은 사용할 수 없습니다. 사용범위 : -3.402823466E+38부터 -1.175494351E-38까지와 1.175494351E-38부터 3.402823466E+38까지, 그리고 0. DOUBLE[(M,D)] [ZEROFILL] 보통 크기의(double-precision) 부동소수점 수. unsigned 사용할 수 없음. 사용 범위 : -1.7976931348623157E+308부터 -2.2250738585072014E-308까지와 2.2250738585072014E-308부터 1.7976931348623157E+308까지 그리고 0. DOUBLE PRECISION[(M,D)] [ZEROFILL] REAL[(M,D)] [ZEROFILL] 이것은 DOUBLE과 동일합니다. DECIMAL(M,D) [ZEROFILL] unpacked 소동소수점 수. unsigned은 사용할 수 없음. CHAR 컬럼 같이 행동합니다. ``unpacked'' 은 수를 문자처럼 저장한다는 의미입니다. 소수점, 음의 부호(-) 등을 포함하여 수 한자리를 저장하는데 각각 digit의 값을 사용하여 저장합니다. 만일 D가 0이면 소수점 또는 소수부분이 없는 값으로 지정하는 것입니다. 최대 사용 범위는 DOUBLE과 같습니다만, 실제적인 사용범위는 M 과 D의 선택 사항에 의해 결정됩니다. MySQL 3.23에서 M은 부호나 소수점을 포함하지 않는다. (이것은 ANSI SQL에 따른 것이다.) NUMERIC(M,D) [ZEROFILL] DECIMAL과 같다. B. DATE and TIME TYPES DATE 날짜. 지원 범위는 '1000-01-01'부터 '9999-12-31'까지입니다. MySQL 은 'YYYY-MM-DD' 형식으로 값을 표시하지만, string 또는 number 모두를 사용하여 DATE 컬럼에 값을 할당할 수 있다. 이 내용은 date and time 타입에 거의 모두 적용되므로 앞으로는 기술하지 않습니다. DATETIME date와 time을 조합한 타입읻아. 이 타입은 '1000-01-01 00:00:00'부터 '9999-12-31 23:59:59'까지를 사용할 수 있다. MySQL은 'YYYY-MM-DD HH:MM:SS' 형식으로 나타냅니다. TIMESTAMP[(M)] 타임스탬프(편지.문서의 발송.접수. 날짜.시간을 기록). 이 범위는 '1970-01-01 00:00:00' 로부터 때때로 2037년까지 사용된다. MySQL은 TIMESTAMP값으로 YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD 또는 YYMMDD 형식으로 나타낸다. M이 값이 14, 12, 8 또는 6인지에 따라 달라진다. 이 컬럼타입도 마찬가지로 string과 number를 이용하여 값을 할당할 수 있다. 시간과 날짜를 insert, update하는데 유용하다. TIME 시간. 지원 범위는 '-838:59:59' to '838:59:59'. MySQL은 'HH:MM:SS'형식으로 time을 나타낸다. YEAR 연도. 1901년부터 2155까지 그리고 0000년을 사용할 수 있다. MySQL은 YEAR컬럼에 YYYY 형식으로 나타낸다. YEAR 타입은 MySQL 3.22.에서 새로 지원하는 타입이다. C. STRING TYPES CHAR(M) [BINARY] 길이가 정해진 문자열. 저장할 때 길이보다 문자열의 길이가 작으면 그 길이 만큼 오른쪽에 공백문자(SPACE)를 더하여 저장한다. 값이 retrieve될 때는 덧붙혀진 공백은 제거된다. CHAR값은 BINARY 키워드로 지정되지 않는다면 case-insensitive(대소문자 구분없이) 형태로 저장되고 비교된다. VARCHAR(M) [BINARY] 가변길이의 문자열. 뒤에 덧붙어있는 공백문자는 저장될 때 제거된다. (이것은 ANSI SQL specification과 다르다). M은 1부터 255문자까지 사용가능하다. VARCHAR 값도 BINARY키워드로 설정되지 않으면 case-insensitive 형태로 저장되고 비교된다. TINYBLOB 최대 길이가 255 (2^8 - 1)인 BLOB 컬럼. BLOB는 그림이나 바이너리 파일 등을 저장하는데 사용된다. TINYTEXT 최대 길이가 255 (2^8 - 1)인 TEXT 컬럼. BLOB 최대 길이가 65535 (2^16 - 1)인 BLOB 컬럼. TEXT 최대 길이가 65535 (2^16 - 1)인 TEXT 컬럼. MEDIUMBLOB 최대 길이가 16777215 (2^24 - 1)인 BLOB 컬럼. MEDIUMTEXT 최대 길이가 16777215 (2^24 - 1)인 TEXT 컬럼. LONGBLOB 최대 길이가 4294967295 (2^32 - 1)인 BLOB 컬럼. LONGTEXT 최대 길이가 4294967295 (2^32 - 1)인 TEXT 컬럼. ENUM('value1','value2',...) 목록 컬럼. 목록에서 선택된 하나의 값 또는 NULL만을 가질 수 있는 문자열 객체이다. ENUM은 구성요소의 총 문자열 길이가 최대 65535까지의 서로 다른 값만을 사용할 수 있다. 예를 들어 test란 테이블에 test란 컬럼명으로 ENUM("one", "two", "three")을 설정한 컬럼이 있다면 다음과 같이 각각에 인덱스가 붙는다. : Value Index NULL NULL "" 0 "one" 1 "two" 2 "three" 3 커리에서 where절을 사용하여 one을 선택한 경우를 select하려면 다음과 같이 한다. mysql> select * from test where test=1; SET('value1','value2',...) A set. zero 또는 리스트에서 선택한 각각의 값을 가질 수 있는 문자열 객체. 최대 64개의 member를 가질 수 있다. 예를 들어 SET("one", "two") NOT NULL 과 같이 정의된 컬럼이 있다면 다음과 같은 값을 목록에 가지고 있다.: "" "one" "two" "one,two" MySQL은 수의 형태로 값(bit값)을 저장한다. 만약 SET("a","b","c","d")으로 정의한 컬럼이 있다면 다음과 같은 bit값을 가진다. SET member Decimal value Binary value a 1 0001 b 2 0010 c 4 0100 d 8 1000 만일 9 즉, 바이너리로 1001인 값을 저장했다면, "a" 와 "d"를 선택한 것으로 결과 값은 "a,d"이다. 이런 형식으로 저장하기에 "a,d"나 "d,a" 또는 "d,a,a,d,d" 등은 모두 "a,d"로 저장되고 보여진다. NULL값은 non-NULL값으로 셋팅된 것보다 전에 sort된다. 보통, SELECT 또는 FIND_IN_SET() 함수를 set으로 정의한 컬럼에 like 연산자를 이용하여 사용한다. 다음과 같은 경우를 예로 들 수 있다. mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%'; mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0; 그러나 이런 경우는 다음과 같이 실행된다. : mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; mysql> SELECT * FROM tbl_name WHERE set_col & 1; 위 문장들 중 첫 번째는 확실히 일치하는 값을 찾는다. 그리고 두 번째는 첫 번째 멤버를 포함한 값을 찾는다. 2.Column type의 요구()사항 아래의 A,B,C는 각 category별로 컬럼 타입마다 저장에 필요한 공간(크기)을 적은 것입니다. A. Numeric types Column type Storage required TINYINT 1 byte SMALLINT 2 bytes MEDIUMINT 3 bytes INT 4 bytes INTEGER 4 bytes BIGINT 8 bytes FLOAT(4) 4 bytes FLOAT(8) 8 bytes FLOAT 4 bytes DOUBLE 8 bytes DOUBLE PRECISION 8 bytes REAL 8 bytes DECIMAL(M,D) M bytes (D+2, if M < D) NUMERIC(M,D) M bytes (D+2, if M < D) B. Date and time types Column type Storage required DATETIME 8 bytes DATE 3 bytes TIMESTAMP 4 bytes TIME 3 bytes YEAR 1 byte C. String types Column type Storage required CHAR(M) M bytes, 1 <= M <= 255 VARCHAR(M) L+1 bytes, where L <= M and 1 <= M <= 255 TINYBLOB, TINYTEXT L+1 bytes, where L < 2^8 BLOB, TEXT L+2 bytes, where L < 2^16 MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L < 2^24 LONGBLOB, LONGTEXT L+4 bytes, where L < 2^32 ENUM('value1','value2',...) 1 or 2 bytes, 목록 값의 개수에 달려 있다. (최대 길이 65535) SET('value1','value2',...) 1, 2, 3, 4 or 8 bytes, 셋 멤버의 개수에 달려있다. (최대 64개의 멤버) VARCHAR과 BLOB and TEXT types은 가변길이를 가진다. 그러므로 저장공간은 저장하는 값의 실제 길이에 따라 정해진다. BLOB and TEXT types은 컬럼에 값을 저장하기 위해 1, 2, 3 또는 4 바이트가 필요하다. 3. Column indexes MySQL은 BLOB and TEXT type을 제외한 모든 column types을 색인할(indexed) 수 있습니다. 컬럼에 관계된 인덱스의 사용은 select 연산에서의 속도를 개선하는데 최선의 방법이고, 하나의 테이블은 16개의 인덱스를 사용할 수 있습니다. 인덱스의 최대길이는 256바이트이고, 이것은 MySQL을 컴파일할 때 변경할 수 있습니다. 그러나 인덱스에는 null을 사용할 수 없습니다. 그러므로 인덱스로 사용할 컬럼은 NOT NULL로 선언되어야 합니다. char나 varchar컬럼을 인덱스할 때 전부가 아닌 일부분을 할 수도 있습니다. 이것은 하드의 공간이나 검색속도에서 좋습니다. 다음과 같이 키(인덱스키)를 정의하면 됩니다. KEY index_name (col_name(length)) 예로 name컬럼의 첫 10글자만 인덱스로 지정하여 테이블을 만드는 것을 보이면 다음과 같습니다. : mysql> CREATE TABLE test ( name CHAR(200) NOT NULL, KEY index_name (name(10))); MySQL이 지원하는 컬럼타입과 인덱스에 대해 알아보았습니다. |
| 블로그 > loves0508님의 블로그 http://blog.naver.com/loves0508/2941553 | |
| mysql의 최대 성능 향상 방법 10.1 버퍼 크기 조정 mysqld 서버가 사용하는 기본 버퍼 크기는 다음의 명령으로 알 수 있다. shell> mysqld --help 이 명령은 모든 mysqld 옵션의 목록과 설정 변수를 보여준다. 출력되는 내용은 기본값을 포함하고 있으며 다음과 비슷하다. Possible variables for option --set-variable (-O) are: back_log current value: 5 connect_timeout current value: 5 join_buffer current value: 131072 key_buffer current value: 1048540 long_query_time current value: 10 max_allowed_packet current value: 1048576 max_connections current value: 90 max_connect_errors current value: 10 max_join_size current value: 4294967295 max_sort_length current value: 1024 net_buffer_length current value: 16384 record_buffer current value: 131072 sort_buffer current value: 2097116 table_cache current value: 64 tmp_table_size current value: 1048576 thread_stack current value: 131072 wait_timeout current value: 28800 mysqld 서버가 현재 가동중이면 다음의 명령을 통해 실제 변수값을 볼 수 있다. shell> mysqladmin variables 각 옵션은 밑에서 설명한다. 버퍼 크기, 길이, 스택 크기는 바이트이다. 'K'(킬로바이트) 나 'M'(메가바이트)를 앞에 붙여 값을 지정할 수 있다. 예를 들면 16M는 16 메가바이트를 가리킨다. 대소문자는 구별하지 않는다. 16M 와 16m은 같다. -back_log mysql이 가질 수 있는 최대 연결 요청의 수. 이것은 main mysql 스레드가 매우 짧은 시간 동안 매우 많은 연결 요청을 받을 때 기능을 한다. 이때 메인 스레드가 연결을 체크하고 새 로운 스레드를 시작하는데는 약간의 시간이 걸린다.(그러나 아주 짧은 시간임) back_log 값 은 mysql이 순간적으로 새로운 요청에 답하는 것을 멈추기전에 이 짧은 시간동안 얼마나 많은 요청을 쌓아두고 있는지를 지정한다. 매우 짧은 시간동안 매우 많은 연결이 예상될때 만 이 값을 증가시켜야 한다. 다른 말로 이 값은 tcp/ip 연결을 받는 listen queue의 크기이다. 각 운영체제마다 이러한 큐 의 크기에 한계가 있다. Unix system call listen(2) 매뉴얼페이지에 자세한 정보가 있다. ba ck_log값의 한계는 운영체제 문서를 확인해봐라. back_log를 최대값보다 더 높여도 효과가 없다. -connect_timeout Bad handshake에 반응하기 전에 연결 패킷을 mysql 서버에서 기다리는 시간.(초) -join_buffer (인덱스를 사용하지 않는 조인의) full-join에서 사용하는 버퍼의 크기. 버퍼는 두 테이블 사 이에서 각 full-join마다 한번 할당이 된다. 인덱싱을 추가하지 못할 때 조인 버퍼를 증가시 키면 full join의 속도를 향상시킬 수 있다. (일반적으로 빠르게 조인을 하는 가장 좋은 방법 은인덱스를 추가하는 것이다) -key_buffer 인덱스 블락은 버퍼링되고 모든 스레드에서 공유한다. 키 버퍼는 인덱스 블락에서 사용하는 버퍼의 크기이다. 인덱스가 많은 테이블에서 delete나 insert 작업을 많이 하면 키 버퍼값을 증가시키는 것이 좋다. 더 빠른 속도를 내려면 LOCK TABLES를 사용하자. [Lock Tables] 참고. -max_allowed_packet 한 패킷의 최대 크기. 메시지 버퍼는 net_buffer_length 바이트로 초기화되지만 필요하면 최 대 허용 패킷 바이트를 증가시킬 수 있다.기본값은 큰 패킷을 잡기에는 작다. 거대 BLOB 컬럼을 사용한다면 값을 증가시켜야 한다. 사용자가 원하는 최대 blob만큼 크게 해야 한다. -max_connections 동시 클라이언트 숫자. mysqld가 필요로하는 파일 지시자(descriptor)의 숫자만큼 값을 늘려 야 한다. 밑에서 파일 디스크립터 제한에 대한 내용을 참고하자. -max_connect_errors 호스트에서 최대 연결 에러이상의 interrupted 연결이 있으면 더 많은 연결을 위해 호스트는 block화된다. FLUSH HOSTS 명령으로 호스트의 block을 해제할 수 있다. -max_join_size 최대 조인 크기이상으로 레크도를 읽는 조인을 하면 에러가 난다. 만약 사용자가 where 문 을 사용하지 않고 시간이 많이 걸리면서 몇백만개의 레코드를 읽는 조인을 수행하려 하면 이 값을 설정한다. -max_sort_length BLOB나 TEXT 값으로 정열할때 사용하는 바이트의 숫자. (각 값중 오직 첫번째 max_sort _length 바이트만 사용된다. 나머지는 무시된다) -net_buffer_length 질의에서 통신 버퍼가 초기화되는 크기. 일반적으로 바뀌지 않지만 매우 적은 메모리를 가 지고 있을 때 예상되는 질의에 맞게 세팅할 수 있다. (이것은 클라이언트에 가는 예상된 sql 문의 길이이다. 질의문이 이 크기를 넘으면 버퍼는 자동으로 max_allowed_packet 바이트까 지 증가한다) -record_buffer 순차적인 검색을 하는 각 스레드에서 각 검색 테이블에 할당하는 버퍼 크기. 순차적인 검색 을 많이 하면 이 값을 증가시켜야 한다. -sort_buffer 정렬이 필요한 각 스레드에서 할당하는 버퍼 크기. order by 나 group by 오퍼레이션을 빠 르게 하려면 이 값을 증가시킨다. 16.4 [임시 파일] 참고. -table_cache 모든 스레드에서 열 수 있는 테이블의 숫자. mysqld가 필요로 하는 파일 디스크립터의 숫 자만큼 이 값을 증가시켜라. mysql은 각 유일한 오픈 테이블에서 두개의 파일 디스크립터가 필요하다. 파일 디스크립터 제한을 참고한다. 테이블 캐쉬가 어떻게 작동하는지는 10.6 [테 이블 캐쉬]를 참고한다. -tmp_table_size 임시 테이블이 이 값을 넘으면 mysql은 "The Table tbl_name is full"이라는 에러 메시지를 낸다. 매우 많은 group by 질의를 사용하면 이 값을 증가시켜야 한다. -thread_stack 각 스레드의 스택 사이즈. creash-me test(**역자주 : 데이터베이스의 벤치마킹을 하는 테스 트입니다. 말그대로 데이터베이스를 죽여주지요) 에서 잡히는 많은 제한은 이 값에 달려있 다. 기본값은 일반적으로 충분히 크다. 11장의 [벤치마크] 참조 -wait_timeout 연결을 끊기전에 연결 활동(activity)을 서버에서 기다리는 시간(초). table_cache 와 max_connections는 서버가 열 수 있는 최대 파일 갯수에 영향을 미친다. 이 값을 증가시키면 운영시스템에서 오픈 파일 디스크립터의 per-process 숫자의 한계까지 올 릴 수 있다. (** ... imposed by your operating system on the per-process number of open file descriptors. 번역이 이상하므로 영문 참고) 그러나 많은 시스템에서 이 한계를 증가시킬수 있다. 이렇게 하려면 각 시스템에서 이 한계 를 변화시키는 방법이 매우 다양하므로 운영체제 문서를 참고해야 한다. table_cache 는 max_connections 와 관계가 있다. 예를 들면 200개의 연결이 있으면 최소 2 00 * n 의 테이블 캐쉬를 가져야 한다. 여기서 n은 조인에서 테이블의 최대 숫자이다. mysql은 매우 유용한 알고리즘을 사용하기 때문에 일반적으로는 매우 적은 메모리로 사용 할 수 있으며 메모리가 많을 수록 성능이 더 많이 향상된다. 많은 메모리와 많은 테이블을 가졌고 중간정도 숫자의클라이언트에서 최대의 성능을 원한다 면 다음과 같이 사용한다. shell> safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M & 메모리가 적고 연결이 많으면 다음과 같이 사용한다. shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k & 또는: shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 -O record_buffer=8k -O net_buffer=1K & 매우 많은 연결이 있을 때 mysqld가 각 연결마다 최소한의 메모리를 사용하도록 설정하지 않았다면 "swapping problems" 문제가 생길 것이다. mysqld에서 옵션을 바꾸었으면 그것은 서버의 해당하는 인스턴스에만 영향을 미친다는 것 을 기억하자. 옵션을 바꾸었을때의 효과를 보기 위해 다음과 같이 해보자. shell> mysqld -O key_buffer=32m --help 마지막에 --help 옵션이 들어간 것을 기억하자. 그렇지 않으면 커맨드 라인에서 사용한 옵 션의 효력은 출력에는 반영되지 않을 것이다. 10.2 메모리 사용 방법 <메모리 최적화> 아래에서 설명하는 목록은 mysqld 서버가 메모리를 사용하는 방법에 대해서 나타내고 있 다. 메모리 사용과 관련된 서버의 변수 이름이 주어진다. - 키 버퍼(변수 key_buffer)는 모든 스레드에서 공유한다. 서버에서 사용하는 다른 버퍼는 필요한대로 할당이 된다. - 각 연결은 각 스레드마다의 특정한 공간을 사용한다. 스택(64k, 변수 thread_stack) , 연결 버퍼(변수 net_buffer_length), result 버퍼 (변수 net_buffer_length) 등. 연결 버퍼와 result 버퍼는 필요할때 max_allowed_packet 까지 동적으로 증가된다. 질의가 수행될 때 현재의 질의문의 복사문이 또한 할당이 된다. (** When a query is running a copy of the current query string is also alloced.) - 모든 스레드는 같은 기본 메모리를 공유한다. - 메모리 맵은 아직 지원이 안된다. (압축 테이블을 제외하고. 그러나 이것은 다른 이야기이 다) 왜냐하면 4GB의 32비트 메모리 공간은 대부분의 대형 테이블에서 충분히 크기가 않기 때문이다. 우리가 64비트 주소 공간을 가진 시스템을 가지게 될 때 우리는 메모리 맵핑을 위한 일반적인 지원을 추가할 것이다. - 테이블에서 순차적인 검색을 하는 각 요청은 read 버퍼에 할당이 된다. (변수 record_buff er) - 모든 조인은 한번에 수행이 되며 대부분의 조인은 임시 테이블을 생성하지 않고 수행이 된다. 대부분의 테이블은 메모리 기반(HEAP) 테이블이다. 거대 길이의 레코드를 가졌거나 BLOB 컬럼을 포함한 임시 테이블은 디스크에 저장이 된다. 현재의 문제는 메모리 기반 테 이블이 tmp_table_size를 초과했을때 "The table tbl_name is full"이라는 에러가 생기는 것 이다. 가까운 시일안에 필요할때 자동적으로 메모리 기반(HEAP) 테이블을 디스크 기반(NI SAM) 테이블로 바꾸도록 고칠 것이다. 이 문제를 해결하기 위해서 mysqld의 tmp_table_size 옵션을 설정하여 임시 테이블 크기를 늘이거나 클라이언트 프로그램에서 SQL_BIG_TABLES라는 sql 옵션을 설정하여야 한다. 7. 24 SET OPTION 을 참고하자. mysql 3.20에서 임시 테이블의 최대 크기는 record_buffer*16이다. 3.20 버전을 사용하고 있 다면 record_buffer의 값을 증가시켜야 한다. 또한 mysqld를 시작할 때 --big-tables 옵션을 사용하여 항상 임시 테이블을 디스크에 저장할 수 있지만 질의 속도에 영향을 미친다. - 정열을 하는 대부분의 요청은 정렬 버퍼와 하나나 두개의 임시 파일을 할당한다. 16.4의 [임시 파일]을 참고한다. - 대부분의 파징(parsing)과 계산은 지역 메모리에서 이루어진다. 작은 아이템에는 메모리 o verhead가 필요없고 일반적인 느린 메모리 할당(slow memory allocation)과 freeing(메모리 해제)는 무시된다. 메모리는 오직 예상지 못한 거대 문자열에서 할당이 된다.( mallloc() 과 free() 사용) - 각 인덱스 파일은 한번에 열리며 각 병행수행되는 스레드에서 데이터 파일은 한번에 열 린다. 각 병행수행 스레드마다 테이블 구조, 각 컬럼의 컬럼 구조, 3 * n 의 버퍼 크기가 할 당된다. ( n은 최대 레코드 길이이며 BLOB 컬럼은 해당하지 않는다) BLOB는 BLOB 데이 터의 길이에 5에서 8 바이트를 더한 값을 사용한다. - BLOB 컬럼을 가진 각 테이블에서 버퍼는 거대 BLOB 값을 읽을 수 있도록 동적으로 커 진다. 테이블을 검색하면 버퍼는 최대 BLOB의 값만큼 버퍼가 할당이 된다. - 모든 사용중인 테이블의 테이블 핸들러는 캐쉬에 저장되며 FIFO로 관리가 된다. 일반적 으로 캐쉬는 64 엔트리를 갖는다. 동시에 두개의 실행 스레드에서 테이블을 사용하면 캐쉬 는 테이블의 두 엔트리를 포함한다. 10.6 [테이블 캐쉬]를 참고한다. - mysqladmin flush-tables 명령은 사용하지 않는 모든 테이블을 닫고 현재 실행되는 스레 드가 끝날 때 모든 사용중인 테이블을 닫는다고 표시한다. 이것은 효과적으로 사용중인 메 모리를 해제한다. ps 와 다른 시스템 상황 프로그램은 mysqld가 많은 메모리를 사용하고 있다고 보고할 것이 다. 이것은 다른 메모리 주소의 스레드-스택때문에 생긴다. 예를 들면 솔라리스의 ps 는 스 택사이의 사용하지 않는 메모리를 사용하는 메모리로 간주한다. 이것은 swap -s를 이용 사 용가능한 스왑을 체크하여 확인할수 있다. 우리는 mysqld를 상용 메모리 유출 측정 프로그 램으로 테스팅해서 mysqld에는 메모리 유출이 없다. 10.3 속도 향상에 영향을 미치는 컴파일/링크 방법 <컴파일시 최적화하기> 다음 테스트의 대부분은 리눅스와 mysql 벤치마크를 가지고 수행되었지만 다른 운영 시스 템에도 암시해주는 것이 있다. static으로 링크를 할때 가장 빠른 실행 속도를 얻을 수 있다. 데이터베이스에 연결하기 위 해 TCP/IP보다는 유닉스 소켓을 사용하면 더 좋은 성능을 낼 수 있다. 리눅스에서 pgcc와 -O6을 사용하면 가장 빠르다. 'sql_yacc.cc'를 이 옵션으로 컴파일하려면 gcc/pgcc는 모든 성능을 내기 위해 많은 메모리가 필요하기 때문에 180M의 메모리가 필요 하다. 또한 mysql을 설정할때 libstdc++ 라이브러리를 포함하지 않기 위해 CXX=gcc라고 설 정해야 한다. - pgcc를 사용하고 모두다 -O6 옵션으로 컴파일하면 mysqld 서버는 gcc로 컴파일한 것보 다 11% 빨라진다. - 동적으로 링크하면 (-static을 사용하지 않고) 13% 느려진다. If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower. - 유닉스 소켓을 사용하는 것보다 tcp/ip로 연결하는 것이 7.5% 느려진다. - On a Sun sparcstation 10, gcc 2.7.3 is 13% faster than Sun Pro C++ 4.2. - On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads. (** 번역을 안한 이후. 리눅스랑 상관없으니깐... **) TcX에서 제공한 mysql 리눅스 배포판은 pgcc로 컴파일되었고 정적으로 링크되었다. 10.4 How MySQL uses indexes prefix- and end-space compressed. See section 7.26 CREATE INDEX syntax (Compatibil ity function). 모든 인덱스(PRIMARY, UNIQUE and INDEX()) 는 B-trees 에 저장된다. 문자열은 자동적 으로 앞 뒤의 공간(?)이 압축된다. 7.26 [인덱스 생성] 참고. 인덱스의 사용 : - WHERE 문에서 해당하는 레코드 빨리 찾기 - 조인을 수행할때 다른 테이블에서 레코드 가져오기 - 특정 키에서 MAX() 나 MIN() 값 찾기 - 소팅이나 그룹화할때 인덱스 키를 사용하면 테이블을 정열하거나 그룹화한다. 키에 DES C가 붙으면 역순으로 인덱스를 읽는다. - 어떤 경우에는 데이터 파일에 묻지 않고 값을 가져온다. 어떤 테이블에서 사용하는 모든 컬럼이 숫자이고 특정 키로 형성되어있으면 빠른 속도로 인덱스 트리에서 값을 가져올 수 있다. 다음 예제를 보자. mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; 다중 컬럼 인덱스가 col1 과 col2에 있으면 해당하는 레코드를 직접 가져올 수 있다. 분리 된 단일 컬럼 인덱스가 col1 과 col2 에 있으면 최적화기는 어떤 인덱스가 더 적은 레코드 를 가졌는지 확인하고 레코드를 가져오기 위해 그 인덱스를 사용하도록 결정한다. 테이블이 다중 컬럼 인덱스를 가졌다면 최적화기가 레코드를 찾는데 어떤 인덱스키를 사용 할 수 있다. 예를 들면 세가지 컬럼 인덱스(col1, col2, col3)를 가졌다면 (col1), (col1,col2) (col1,col2,col3) 인덱스를 사용하여 검색을 할 수 있다. MySQL can't use a partial index if the columns don't form a leftmost prefix of the inde x. Suppose you have the SELECT statements shown below: (** 해석이 잘 안되는데 예제를 보시면 무슨 말인지 알 수 있을 것임**) mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; If an index exists on (col1,col2,col3), only the first query shown above uses the index. The second and third queries do involve indexed columns, but (col2) and (col2,col3) are not leftmost prefixes of (col1,col2,col3). 인덱스가 (col1,col2,col3)로 있다면 위의 질의중 오직 첫번째 질의만 인덱스를 사용한다. 두 번째 및 세번째 질의은 인덱스된 컬럼이 포함되어 있지만 (col2) 와 (col2,col3)는 (col1,col2,c ol3) 인덱스에 해당하지 않는다. MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character. For example, the following SELECT stat ements use indexes: mysql은 또한 LIKE의 인수가 와일드카드 문자로 시작하지 않는 상수 문자열일이라면 LIK E 비교문에서 인덱스를 사용한다. 예를 들어 다음의 SELECT 문은 인덱스를 사용한다. mysql> select * from tbl_name where key_col LIKE "Patrick%"; mysql> select * from tbl_name where key_col LIKE "Pat%_ck%"; 첫번째 문장에서는 "Patrick" <= key_col < "Patricl" 을 가진 레코드만 고려된다. 두번째 문 장에서는 "Pat" <= key_col < "Pau" 을 가진 레코드만 고려된다. 다음의 SELECT 문은 인덱스를 사용하지 않는다: mysql> select * from tbl_name where key_col LIKE "%Patrick%"; mysql> select * from tbl_name where key_col LIKE other_col; 첫번째 문장에서 LIKE 값은 와일드카드 문자로 시작하고 있다. 두번째 문장에서는 LIKE 값이 상수가 아니다. 10.5 WHERE 문에서 최적화하기 (이번 절은 완전한 내용을 포함하고 있지는 않다. mysql은 많은 최적화방법이 있다.) In general, when you want to make a slow SELECT ... WHERE faster, the first thing t o check is whether or not you can add an index. All references between different tables should usually be done with indexes. You can use the EXPLAIN command to determine which indexes are used for a SELECT. See section 7.21 EXPLAIN syntax (Get informat ion about a SELECT). 일반적으로 느린 SELECT ... WHERE 문을 빠르게 하려면 가장 먼저 확인해야 할 것이 인 덱스 추가 문제이다. 다른 테이블사이에서 모든 레퍼런스(references 참조)는 일반적으로 인 덱스에 의해 수행된다. SELECT 문에서 어떤 인덱스를 사용하는지 결정하기 위해 EXPLAI N 명령을 사용할 수 있다. 7.21 [Explain]을 참고. mysql에서 수행하는 최적화는 다음과 같다. - 불필요한 삽입어 제거 ((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b ANDc) OR (a AND b AND c AND d) -상수 폴딩(folding) (a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5 - 상수 조건 제거(상수 폴딩때문에 필요) (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6 - 인덱스에서 사용되는 상수 표현은 한번에 계산된다. (Constant expressions used by indexes are evaluated only once.) - WHERE 절이 없는 단일 테이블의 COUNT(*)는 테이블 정보에서 직접 값을 가져온다. 단일 테이블에서 사용된 NOT NULL 표현도 이와 같이 수행된다. - 유효하지 않은 상수 표현은 미리 제거된다. mysql은 불가능하고 해당하는 레코드가 없는 SELECT 문을 빠르게 감지한다. - GROUP BY 나 그룹 펑션(COUNT(), MIN() ...)을 사용하지 않으면 HAVING은 WHERE 에 합쳐진다. (** HAVING 절에서는 인덱스를 사용하지 못함. 그러므로 가능한 HAVING절을 사용하지 않는게 속도면에서 좋다 **) - 각 서브 조인에서 빠르게 WHERE 문을 계산하고 가능한한 레코드를 제외하도록 간소하 게 WHERE 문이 만들어진다. - mysql은 일반적으로 최소한의 레코드를 찾기 위해 인덱스를 사용한다. =, >, >=, <, <=, BETWEEN 그리고 'something%' 처럼 앞이 와일드카드로 시작하지 않는 LIKE 문등을 사용하여 비교를 할 때 인덱스를 사용한다. (** 10.4 절에서 설명하였듯이 like 를 사용할때 와일드카드로 시작하는 like 문을 사용하면 인덱스를 사용하지 않는다. 일정한 단어로만 시 작하는 컬럼에서 자료를 찾을 때 유용할 것이다. **) - Any index that doesn't span all AND levels in the WHERE clause is not used to opti mize the query. 다음의 WHERE 문은 인덱스를 사용한다.: ... WHERE index_part1=1 AND index_part2=2 ... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */ ... WHERE index_part1='hello' AND index_part_3=5 /* optimized like "index_part1='hello'" */ 다음의 WHERE 문은 인덱스를 사용하지 않는다.: ... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */ ... WHERE index=1 OR A=10 /* No index */ ... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */ - 질의에서 다른 테이블보다 모든 상수 테이블을 먼저 읽는다. 상수 테이블은 다음과 같다. ㅇ빈 테이블이나 1개의 레코드만 있는 테이블 ㅇWHERE 문에서 UNIQUE 인덱스나 PRIMARY KEY 를 사용하고 모든 인덱스 는 상수 표현으로된 테이블 다음의 테이블은 상수 테이블로 사용된다. mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id; - 모든 가능성을 시도하여 테이블을 조인하는데 가장 좋은 조인 조합을 찾는다. (ORDER B Y나 GROUP BY의 모든 컬럼이 동일한 테이블에서 나오면 조인을 할때 이 테이블이 먼저 선택된다) - ORDER BY 문과 다른 GROUP BY 문이 있을 때, 또는 ORDER BY 나 GROUP BY가 조인 큐의 첫번째 테이블이 아닌 다른 테이블의 컬럼을 포함하고 있으면 임사 테이블을 만 든다. - 각 테이블 인덱스를 찾고 레코드의 30%미만을 사용하는 (best) 인덱스가 사용된다. 그런 인덱스가 없으면 빠른 테이블 검색이 사용된다. - 어떤 경우에는 mysql은 데이터 파일을 조회하지 않고 인덱스에서 레코드를 읽을 수 있 다. 인덱스에서 사용한 모든 컬럼이 숫자라면 질의를 처리하는데 단지 인덱스 트리만을 사 용한다. - 각 레코드가 출력되기 전에 HAVING 절에 맞지 않는 레코드는 건너뛴다. 다음은 매우 빠른 질의의 예이다: mysql> SELECT COUNT(*) FROM tbl_name; mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; mysql> SELECT MAX(key_part2) FROM tbl_name WHERE key_part_1=constant; mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10; mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10; 다음의 커리는 인덱스 트리만을 사용하여 값을 구한다.(인덱스 컬럼은 숫자라고 가정): mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; mysql> SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 and key_part2=val2; mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1; 다음의 질의는 개별적인 정열을 하지 않고 정열된 순서대로 열을 가져오는 데 인덱스를 사 용한다: mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,... 10.6 테이블 열고 닫는 방법 open 테이블의 캐쉬는 table_cache의 최대값까지 커질 수 있다. (기본값 64 ; 이 값은 mysql d에서 -0 table_cache=# 으로 바꿀 수 있다) 캐쉬가 꽉 찼을때, 그리고 다른 스레드가 테이 블을 열려고 할 때, 또는 mysqladmin refresh 나 mysqladmin flush-tables를 사용할때를 제 외하고는 테이블은 결코 닫히지 않는다. 테이블 캐쉬가 꽉 차면 서버는 캐쉬 엔트리를 사용하도록 조절하기 위해 다음의 절차를 사 용한다. - 가장 먼저 사용했던 순서대로 현재 사용하지 않는 테이블을 닫는다. - 캐쉬가 꽉 찼고 어떤 테이블도 닫히지 않지만 새로운 테이블을 열어야 한다면 캐쉬가 필 요한 만큼 임시적으로 확장된다. - 캐쉬가 임시적으로 확장된 상태이고 테이블을 사용할 수 없는 상황으로 가면 테이블을 닫고 캐쉬를 해제한다. 테이블은 각 동시병행적인 접근때마다 열린다. 동일한 테이블에 접근하는 두개의 스레드가 있거나 같은 질의에서 테이블에 두번 접근하면(with AS) 테이블을 두번 열여야 한다는 의 미이다. 테이블의 첫번째 개방은 두개의 파일 디스크립터를 가진다. ; 추가적인 테이블의 개 방은 하나의 파일 디스크립터를 가질 뿐이다. 처음에 개방에 사용하는 추가적은 파일 디스 크립터는 인덱스 파일에 사용된다. ; 이 디스크립터는 모든 스레드에서 공유된다. 10.6.1 데이터베이스에서 많은 수의 테이블을 만들때의 단점 디렉토리에 많은 파일이 있다면 open, close 그리고 create 오퍼레이션은 느려질 것이다. 서 로 다른 많은 테이블에서 SELECT 문을 수행하면 테이블 캐쉬가 꽉 찰 때 약간의 overhea d가 있을 것이다. 왜냐면 개방된 테이블이 있다면 다른 테이블은 닫혀야 하기 때문이다. 테 이블 캐쉬를 크게 해서 이러한 오우버헤드를 줄일 수 있다. 10.7 많은 테이블을 여는 이유 mysqladmin status 를 실행할 때 다음과 같이 나올 것이다: Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12 단지 6테이블을 사용했는데 이러한 결과는 당황스러울 것이다. mysql은 멀티스레드를 사용한다. 그래서 동시에 같은 테이블에서 많은 질의를 할 수 있다. 같은 파일에 대하여 다른 상황을 가지는 두개의 스레드에 대한 문제를 줄이기 위해 테이블 은 각 동시병행적인 스레드마다 독립적으로 개방된다. 이것은 테이타 파일에서 약간의 메모 리와 하나의 추가적인 파일 디스크립터를 사용한다. 모든 스레드에서 인덱스 파일은 공유된 다. 10.8 데이터베이스와 테이블에서 심볼릭 링크 사용 데이터베이스 디렉토리에서 테이블과 데이터베이스를 다른 위치로 옮기고 새로운 위치로 심 볼릭 링크를 사용할 수 있다. 이렇게 하는 것을 원할 경우가 있다. 예를 들면 데이터베이스 를 더 여유공간이 많은 파일시스템으로 옮기는 경우 등. mysql에서 테이블이 심볼링 링크되었다는 것을 감지하면 심볼링 링크가 가리키는 테이블을 대신 사용할 수 있다. realpath() call 을 지원하는 모든 시스템에서 작동한다. (최소한 리눅 스와 솔라리스는 realpath()를 지원한다) realpath()를 지원하지 않는 시스템에서 동시에 실 제 경로와 심볼릭 링크된 경로에 접근하면 안된다. 이런 경우에는 업데이트 된후에 테이블 이 모순될 수 있다. mysql은 기본값으로 데이터베이스 링크를 지원하지 않는다. 데이터베이스간에 심볼릭 링크 를 사용하지 않는 작동을 잘 할 것이다. mysql 데이터 디렉토리에 db1 데이터베이스가 있 고 db1을 가리키는 db2 심볼릭 링크를 만들었다고 해보자: shell> cd /path/to/datadir shell> ln -s db1 db2 이제 db1에 tbl_a라는 테이블이 있다면 db2에도 tbl_a가 나타날 것이다. 한 스레드가 db1.tbl _a를 업데이트하고 다른 스레드가 db2.tbl_a를 업데이트하면 문제가 생길 것이다. 정말로 이 기능이 필요하면 , `mysys/mf_format.c'에서 다음의 코드를 수정해야 한다.: if (!lstat(to,&stat_buff)) /* Check if it's a symbolic link */ if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff)) 위 코드를 다음과 같이 수정한다 : if (realpath(to,buff)) 10.9 테이블에 락 거는 방법 mysql의 모든 락은 deadlock-free 이다. 언제나 질의를 시작할때 한번에 모든 필요한 락을 요청하고 언제나 같은 순서대로 테이블에 락을 걸어 관리한다. WRITE 락을 사용하는 방법은 다음과 같다: - 테이블에 락이 없으면 그 테이블에 write 락을 건다. - 이런 경우가 아니라면 write 락 큐에 락을 요청한다. READ 락을 사용하는 방법은 다음과 같다: - 테이블에 write 락이 없으면 그 테이블에 read 락을 건다. - 이런 경우가 아니라면 read 락 큐에 락을 요청한다. 락이 해제되었을 때 락은 write 락 큐의 스레드에서 사용할 수 있으며 그러고 나서 read 락 큐의 스레드에서 사용한다. 테이블에서 업데이트를 많이 하면 SELECT 문은 더 이상 업데이트가 없을 때까지 기다린 다는 것을 의미한다. 이러한 문제를 해결하기 위해 테이블에서 INSERT 와 SELECT 오퍼레이션을 많이 사용하 는 경우에 다음과 같이 하면 된다. 임시 테이블에 레코드를 입력하고 한번에 임시 테이블에 서 실제 테이블로 레코드를 업데이트한다. 다음의 예를 보자: mysql> LOCK TABLES real_table WRITE, insert_table WRITE; mysql> insert into real_table select * from insert_table; mysql> delete from insert_table; mysql> UNLOCK TABLES; 만약 어떤 경우에 SELECT문에 우선권을 주고 싶다면 INSERT 옵션에서 LOW_PRIORITY or HIGH_PRIORITY 옵션을 사용할 수 있다. 7.13 [Insert] 참고. (** LOW_PRIORITY를 지 정하면 클라이언트에서 테이블을 읽지 않을 때까지 INSERT 문 수행이 미루어진다. **) 단일 큐를 사용하기 위해 `mysys/thr_lock.c' 의 락킹 코드를 바꿀 수 있다. 이런 경우 writ e 락과 read 락은 같은 우선권을 가지며 어떤 애플리케이션에서는 유용할 수 있다. 10.10 테이블을 빠르고 작게 배열하는 방법 <** 테이블 최적화 **> 다음은 테이블에서 최대의성능을 내는 방법과 저장 공간을 절약할 수 있는 테크닉이다: - 가능한한 NOT NULL로 컬럼을 선언한다. 속도가 빨라지며 각 컬럼마다 1 비트를 절약할 수 있다. - default 값을 가질 때 유리하다. 입력되는 값이 기본값과 다를 때만 확실하게 값이 입력된 다. INSERT 문에서 첫번째 TIMESTAMP 컬럼이나 AUTO-INCREAMENT 컬럼의 값을 입력할 필요가 없다. 18.4.49 [mysql_insert_id()] 참고. - 가능한한 테이블을 작게 만드려면 더 작은 integer 타입을 사용하자. 예를 들면 MEDIUM INT 가 보통 INT 보다 좋다. - 가변 길이 컬럼이 없다면(VARCHAR, TEXT or BLOB columns), 고정 길이 레코드 포 맷이 사용된다. 이 경우 속도는 더 빠르지만 불행히도(흑흑~) 낭비되는 공간이 더 많다. 10.1 4 [Row format] 참고. - mysql이 질의를 효과적으로 최적화하기 위해 많은 양의 데이터를 입력한후 isamchk --a nalyze를 실행하자. 이렇게 하면 동일한 값을 가진 줄의 평균 숫자를 가리키는 각 인덱스의 값을 업데이트한다. (물론 unique 인덱스에서는 항상 1이다) - 인덱스와 인덱스에 따른 데이타를 정열하려면 isamchk --sort-index --sort-records=1 을 사용하자.(if you want to sort on index 1). 인덱스에 따라 정렬된 모든 레코드를 읽기 위해 unique 인덱스를 가졌다면 이렇게 하는 것 이 속도를 빠르게 하는 가장 좋은 방법이다. - INSERT 문에서 가능한 다중 값 목록을 사용하자. 개별적인 SELECT 문보다 훨씬 빠르 다. 데이타를 테이블에 입력할 때 LOAD DATA INFILE을 사용하자. 많은 INSERT 문을 사용하는 것보다 보통 20배 빠르다. 7.15 [Load] 참고. 많은 인덱스를 가진 테이블에 데이타를 입력할때 다음의 과정을 사용하면 속도를 향상시킬 수 있다. 1. mysql이나 Perl 에서 CREATE TABLE로 테이블을 만든다. 2. mysqladmin flush-tables 실행. (** 열린 테이블을 모두 닫음 **) 3. isamchk --keys-used=0 /path/to/db/tbl_name 사용. 테이블에서 모든 인덱스 사용을 제 거한다. 4. LOAD DATA INFILE 를 이용 테이블에 데이타를 입력. 5. pack_isam을 가지고 있고 테이블을 압축하기 원하면 pack_isam을 실행. 6. isamchk -r -q /path/to/db/tbl_name 를 이용 인덱스를 다시 생성. 7. mysqladmin flush-tables 실행. - LODA DATA INFILE 과 INSERT 문에서 더 빠른 속도를 내려면 키 버퍼를 증가시킨 다. mysqld나 safe_mysqld에서 -O key_buffer=# 옵션을 사용하면 된다. 예를 들어 16M는 풍부한 램을 가졌다면 훌륭한 값이다. - 다른 프로그램을 사용하여 데이타를 텍스트 파일로 덤프할때 SELECT ... INTO OUTFIL E 을 사용하자. 7.15 [LOAD DATA INFILE] 참고. - 연속으로 다량의 insert와 update를 할 때 LOCK TABLE을 사용하여 테이블에 락을 걸 면 속도를 향상시킬 수 있다. LOAD DATA INFILE 그리고 SELECT ...INTO OUTFILE 는 원자적이기 때문에 LOCK TABLE을 사용하면 안된다. 7.23 [LOCK TABLES/UNLOCK TABLES] 참고. 테이블이 얼마나 단편화되었는지 점검하려면 '.ISM' 파일에서 isamchk -evi 를 실행한다. 1 3장 [Maintenance] 참고. 10.11 INSERT 문에서 속도에 영향을 미치는 부분 <** insert 최적화 **> insert 하는 시간은 다음와 같이 구성된다: Connect: (3) Sending query to server: (2) Parsing query: (2) Inserting record: (1 x size of record) Inserting indexes: (1 x indexes) Close: (1) (숫자)는 비례적인 시간이다. 이것은 테이블을 개방할때 초기의 overhead를 고려하고 있지 는 않다. (매 동시병행적으로 수행되는 질의마다 발생) The size of the table slows down the insertion of indexes by N log N (B-trees). 테이블의 크기는 N log N(B-trees)에 따라 인덱스의 입력이 느려진다. (**말이 좀 이상. 테 이블이 커짐에 따라 인덱스 생성도 느려진다는 뜻이겠죵 **) 테이블에 락을 걸거나 insert 문에서 다중 값 목록을 사용하여 입력 속도를 빠르게 할 수 있다. 다중 값 목록을 사용하면 단일 insert 보다 5배 정도 속도가 빨라진다. mysql> LOCK TABLES a WRITE; mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33); mysql> INSERT INTO a VALUES (8,26),(6,29); mysql> UNLOCK TABLES; 주요한 속도 차이는 모든 INSERT 문이 완료되고 난 후에 한번에 인덱스 버퍼가 쓰여기지 때문에 생긴다. 보통 서로 다른 여러 INSERT 문이 있으면 많은 인덱스 버퍼 플러쉬가 있 을 것이다. 모든 줄을 단일 문으로 입력하면 락은 필요없다. 락킹은 또한 다중 연결 테스트의 총 시간을 줄일 수는 있다. 그러나 어떤 스레드에서는 총 대기시간은 증가할 수 있다.(왜냐면 락을 기다리기 때문이다) 예를 들어보자: thread 1 does 1000 inserts thread 2, 3, and 4 does 1 insert thread 5 does 1000 inserts 락을 사용하지 않으면 2, ,3 4는 1과 5 전에 끝마칠 것이다. 락을 사용하면 2,3,4는 아마도 1 이나 5 전에 끝나지 않을 것이다. 그러나 총 시간은 40% 빨라진다. INSERT, UPDATE, DELETE 오퍼레이션은 mysql에서 매우 빠르다. 그렇기 때문에 줄에서 5개 이상의 insert나 update를 할 때 락을 추가하면 더 좋은 성능을 얻을 수 있다. 줄에 매 우 많은 자료를 입력한다면 다른 스레드에서 테이블에 접근하도록 하기 위해 때때로(각 100 0줄마다) UNLOCK TABLES를 사용하는 LOCK TABLES 실행하면 된다. 이렇게 하면 좋 은 성능을 낼 수 있다. (** 열심히 입력을 하고 중간에 락을 풀었다가 다시 락을 거는 것 반복함**) 물론 LOAD DATA INFILE 이 더 빠르다. 10.12 DELETE 문에서 속도에 영향을 미치는 부분 <** DELETE 문 최적화 **> 레코드를 삭제하는 시간은 정확히 인덱스 숫자에 비례한다. 레코드를 빠르게 지우기 위해 인덱스 캐쉬의 크기를 증가시킬 수 있다. 기본 인덱스 캐쉬는 1M 이다; 빠르게 삭제하기 위 해 증가되어야 한다.(충분한 메모리를 가지고 있다면 16M로 하자) 10.13 mysql에서 최대 속도를 얻는 방법 벤치마킹을 시작하자! mysql 벤치마크 스위트에서 어떤 프로그램을 사용할 수 있다. (일반 적으로 'sql-bench' 디렉토리에 있음) 그리고 입맞에 맞게 수정하자. 이렇게 하면 당신의 문 제를 해결할 수 있는 다른 해결책을 찾을 수 있으며 당신에게 가장 빠른 해결책을 테스트할 수 있다. - mysqld를 적절한 옵션으로 시작하자. 메모리가 많을수록 속도가 빠르다. 10.1 [MySQL parameters] 참고. - SELECT 문의 속도를 빠르게 하기 위해 인덱스를 만들자. 10.4 [MySQL indexes] 참고. - 가능한 효율적으로 컬럼 타입을 최적화하자. 예를 들면 가능한 NOT NULL로 컬럼을 정 의하자. 10.10 [Table efficiency] 참고. - --skip-locking 옵션은SQL 요청에서 파일 락킹을 없앤다. 속도가 빨라지지만 다음의 과 정을 따라야 한다: ㅇ isamchk로 테이블을 체크하거나 수리하기 전에 mysqladmin flush-tables 로 모 든 테이블을 플러시해야 한다. (isamchk -d tbl_name은 언제나 허용된다. 왜냐하면 이건 단 순히 테이블의 정보를 보여주기 때문이다) ㅇ 동시에 뜬 두개의 mysql 서버가 동일한 테이블을 업데이트하려 한다면 동일한 데이터 파일에 두개의 mysql 서버를 띄우면 안된다. --skip-locking 옵션은 MIT-pthreads로 컴파일할때 기본값이다. 왜냐면 모든 플랫 폼의 MIT-pthreads에서 flock()가 완전하게 지원이 되지 않기 때문이다. - 업데이트에 문제가 있다면 업데이트를 미루고 나중에 하자. 많은 업데이트를 하는 것이 한번에 하나를 업데이트하는 것보다 더 빠르다. - FreeBSD 시스템에서 MIT-pthreads에 문제가 있으면 FreeBSD 3.0 이후 버전으로 업데 이트 하는것이 좋다. 이렇게 하면 유닉스 소켓을 사용하는 것이 가능하며(FreBSD에서 유닉 스 소켓이 MIT-pthreads에서 TCP/IP 연결을 사용하는 것보다 빠르다) 그리고 스레드 패키 지가 조정(intergrated?)되어야 한다. - 테이블이나 컬럼 단계를 체크하는 GRANT는 성능을 떨어뜨린다. 10.14 로우 포맷과 다른 점은 무엇인가? 언제 VARCHAR/CHAR을 사용해야 하는가? mysql은 실제의 SQL VARCHAR 타입이 없다. 그대신 mysql은 레코드를 저장하고 이것을 VARCHAR로 에뮬레이트하는데 세가지 방법이 있다. 테이블에 VARCHAR, BLOB, TEXT 컬럼이 없으면 고정 row size를 사용한다. 그외에는 동적 row size를 사용한다. CHAR 과 VARCHAR 컬럼은 애플리케이션의 관점에서 동일하 게 취급된다; 둘다 trailing space는 컬럼을 가져올때 제거된다. isamchk -d 를 이용 테이블에서 사용하는 포맷을 체크할 수 있다. (-d 는 "테이블 묘사"를 의미) mysql은 세가지 다른 테이블 포맷을 가지고 있다; 고정길이, 다이나믹, 압축. 고정 길이 테이블 - 기본 포맷. 테이블에 VARCHAR, BLOB, TEXT 컬럼이 없을 때 사용. - 모든 CHAR, NUMERIC, DECIMAL 컬럼은 컬럼 길이에 space-padded 이다. (** space- padded를 무엇이라고 번역해야 할지 애매모호해서 **) - 매우 빠름 - 캐쉬하기 쉽다 - 손상 후 복구가 쉽다. 왜냐면 고정된 위이에 레코드가 위치하기 때문이다. - 많은 양의 레코드가 지워졌거나 운영 시스템에서 자유 공간을 늘리길 원치 않는다면 (isa mchk를 이용) 재조직화할 필요없다. - 보통 다이나믹 테이블보다 많은 디스크 공간을 필요로 한다. 다이나믹 테이블 - 테이블이 VARCHAR, BLOB, TEXT 컬럼을 포함하고 있을 때 사용. - 모든 문자열 컬럼은 다이나믹하다.(4보다 작은 길이를 가진 문자열 제외) - 컬럼이 문자열 컬럼에서 비었거나 ('') 숫자형 컬럼에서 0(NULL 값을 가진 컬럼과 동일 한 것이 아니다) 을 나타내는 비트맵이 모든 레코드 앞에 선행된다. 문자열 컬럼에서 trailin g space를 제거한 후 zero의 길이를 가지거나 숫자형 컬럼이 zero의 값을 가지면 비트 맵으 로 표시되고 디스크에 저장되지 않는다. 비지 않은 문자는 문자내용에 길이 바이트만큼 추 가되어 저장된다. - 보통 고정 길이 테이블보다 디스크 공간 절약. - 줄의 길이를 확장하는 정보를 가지고 줄을 업데이트하면 줄은 단편화될 것이다. 이런 경 우 더 좋은 성능을 위해 때때로 isamchk -r 을 실행해야 한다. 통계적으로(?) isamchk -ei tbl_name을 사용하자. - 손상후 복구가 어렵다. 왜냐면 레코드가 많은 조각드로 단편화되고 링크(단편)가 없어지 기 때문이다. - 다이나믹 사이즈 테이블의 예상되는 열 길이 : 3 + (number of columns + 7) / 8 + (number of char columns) + packed size of numeric columns + length of strings + (number of NULL columns + 7) / 8 각 링크마다 6 바이트가 더 있다. 다이나믹 레코드는 업데이트로 레코드가 늘어날때마다 링 크된다. 각 새로운 링크는 최소 20바이트일 것이며, 그래서 다음의 확장은 아마도 동일한 링 크로 될 것이다. 그게 아니라면 다른 링크가 있을 것이다. isamchk -ed 로 얼마나 많은 링 크가 있는지 체크할 수 있다. 모든 링크는 isamchk -r 로 제거할 수 있다.(** ?? **) There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an up date causes an enlargement of the record. Each new link will be at least 20 bytes, so th e next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with isamchk -ed. All links may be removed with isamchk -r. 압축 테이블 - 읽기 전용 테이블은 pack_isam 유틸리티로 만들 수 있다. 확장 mysql 이메일 지원을 구 입한 모든 고객은 내부적인 용도로 pack_isam을 사용할 권리가 주어진다. - 압축해제 코드는 모든 mysql 배포판에 있으므로 pack_isam이 없는 고객도 pack_isam으 로 압축된 테이블을 읽을 수 있다. (테이블이 같은 플랫폼에서 압축되어 있는한) - 매우 적은 디스크 용량을 사용. - 각 레코드는 개별적으로 압축이 된다.( 매우 적은 액세스 overhead) 레코드의 헤더는 테 이블의 가장 큰 레코드에 따라 (1-3 바이트) 고정된다. 각 컬럼은 다르게 압축이 된다. 압축 타입은 다음과 같다: ㅇ 일반적으로 각 컬럼마다 다른 Huffman 테이블이다. ㅇ Suffic 공간 압축 ㅇ Prefix 공간 압축 ㅇ 0 값을 가진 숫자는 1비트로 저장. ㅇ integer 컬럼의 값이 작은 범위를 가졌다면, 컬럼은 최대한 작은 타입으로 저장 된다. 예를 들면 BIGINT 컬럼은 모든 값이 0부터 255라면 TINIINT 컬럼(1바이트)로 저장 된다. ㅇ 컬럼이 몇가지 가능한 값으로만 구성되어 있다면, 컬럼 타입은 ENUM으로 변환 된다. ㅇ 컬럼은 위 압축 방법을 조합하여 사용한다. - 고정 길이나 다이나믹 길이의 테이블을 다룰 수 있다. 그러나 BLOB나 TEXT 컬럼은 다 룰 수 없다. - isamchk로 압축을 해재할 수 있다. mysql은 다른 인덱스 타입을 지원한다. 그러나 일반적인 타입은 NISAM이다. 이것은 B-tre e 인덱스이며 모든 키의 갑을 합하여 (키 길이+4)*0.67로 인덱스 파일의 크기를 대강 계산 할 수 있다. (이것은 모든 키가 정렬된 순서로 입력된 가장 나쁜 경우이다) String indexes are space compressed. If the first index part is a string, it will also be p refix compressed. Space compression makes the index file smaller if the string column h as a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression helps if there are many strings with an identical prefix. 문자열 인덱스는 공간이 압축된다. 첫번째 인덱스 부분이 문자열이라면, prefix가 압축된다. 문자열 컬럼이 다량의 trailing space를 가졌거나 언제나 완전한 길이를 사용하지 않는 VA RCHAR 컬럼일 때 space 압축은 인덱스 파일을 더 작게 만든다. prefix 압축은 많은 문자 열에 동일한 prefix가 있을 때 유용하다. |
| 블로그 > loves0508님의 블로그 http://blog.naver.com/loves0508/2942014 | |
이번 기사에서는 Swynk.com의 필자인 Alexander Chigrik가 제안하는 대용량 SQL Server 관리자를 위한 다양한 팁 14가지에 대해 알아보자. 이번 내용에서는 잘 알려지지는 않았지만 SQL Server 환경에서 성능을 개선하기 위한 매우 유용한 방법들에 대해서 알아보자. 1. 가능한 테이블의 컬럼수는 작게 설계하는 것이 좋다. 테이블의 컬럼수가 적은 것은 당연히 I/O의 성능 향상에 도움이 된다. 당연히 쿼리 연결시 필요한 컬럼만을 가져와야 하며 작은 컬럼수의 설계는 백업이나 리스토어 등의 업무에 성능 개선을 준다. 2. 컬럼 타입을 text/ntext를 써야 하는 경우라면 varchar/nvarchar 컬럼 형태로 사용하는 것이 좋다. 왜냐하면 SQL Server는 text/ntext 컬럼의 경우 다른 데이터들과 분할에서 저장하게 되며 이 저장된 값이 필요할때는 다른 형식의 값들보다 오래걸리는 것은 당연한 이야기이다. 3. 만약 유니코드값이 입력되지 않는 컬럼 타입에 nchar/nvarchar를 써야 하는 경우라면 char/varchar를 고려하는 것이 좋다. 당연히 이러한 설계는 테이블 사이즈를 감소시킨다. 작은 테이블 사이즈는 I/O비용을 감소시키고 데이터베이스의 부하를 줄이면서 성능향상의 기본이 된다. 4. 만약 테이블 컬럼에 들어올 데이터의 크기가 4바이트 이하라면 varchar/nvarchar 컬럼 대신 char/nchar를 사용하는 것이 좋다. 고정된 값의 크기를 갖는 char 데이터 타입은 가변적인 값을 갖는 varchar 데이터 타입보다 작은 사이즈를 갖는다. 결국 char 데이터 타입을 사용하면 업데이트를 하거나 입력을 하는데 도움이 된다. 업데이트를 실행하게 되면 기존 값을 지우고 새로운 값을 입력하는 수정작업이 이뤄지게 되는데 이러한 물리적인 데이터의 수정작업은 사이즈가 작을 수록 성능 향상에 도움이 되는 것이다. 5. 데이터베이스의 테이블을 3정규화를 하는 것이 좋다. 만약 3정규화를 통한 테이블 설계가 이뤄지지 않고 종속관계를 포함하지 않거나 2정규화 등을 통해 테이블 설계가 이뤄진다면 많은 문제를 발생시킬 수 있다. 3정규화를 통해 여러 테이블의 조인비용등의 두려움으로 인해 반정규화를 한다는 것은 위에서 말한 심플한 컬럼 디자인과 쿼리시 필요한 컬럼만을 조인하는 방법등으로 얼마든지 성능개선을 할 수 있다. 6. 데이터베이스의 테이블이 4정규화와 5정규화를 요구한다면 반정규화를 고민해보는것도 좋다. 정규화에서 4정규화와 5정규화를 요구한다면 그 결과는 성능저하를 요인할 수 있다. 이런 경우 성능 개선을 위해 반정규화 설계를 고민해보는 것이 필요하다. 7. 만약 모든 테이블의 레코드를 지우는 삭제작업을 해야 한다면 DELETE 명령어를 통한 삭제보다는 TRUNCATE TABLE을 사용하는 것을 고려해 볼만 하다. TRUNCATE TABLE를 사용하면 각각의 레코드를 지우며 로깅을 하는 DELETE 보다는 훨씬 빠른 방법이다. 8. 가능한 엔터프라이즈 메니저를 통해 원격 접속하여 관리하는 것은 서버에 많은 부담을 준다. 엔터프라이즈 매니저의 경우 많은 리소스를 요구하기 때문에 가능한 쿼리분석기등을 통한 제어가 부담을 줄일 수 있다. 9. PDC, BDC, 도메인 컨트롤러 또는 DNS서비스등이 설치된 곳에 설치하지 않는 것이 좋다. 도메인 컨틀롤러 등은 많은 오버헤드가 발생한다. 가능한 SQL Server 만을 독립적으로 설치해서 모든 자원이 서버만을 위해 사용되게 설계하는 것은 기본이다. 10. 성능 저하를 막기 위해 가능한 로컬 하드에 백업한 후 다른 백업장치로 옮기는게 좋다. 만약 백업을 하게 되면 SQL Server에서 몇가지 명령들을 사용할 수 없게 된다. 예를 들어 백업하는 동안 "ALTER DATABASE","ADD FILE","REMOVE FILE 옵션","shrink a database","CREATE INDEX","SELECT INTO" 등을 사용할 수 없다. 백업하는 시간이 오래걸리구 성능저하를 고려한다면 먼저 로컬 하드디스크에 백업한 후 백업 파일을 테이프 백업장치 등에 옮기는것이 좋다. 11. 가능한 로그를 남기지 않는 "nonlogged bulk copy"를 이용하는 것이 좋다. "nonlogged bulk copy" 는 로그를 남기면서 대량 복사 작업을 하는 것보다 무척 빠르다. 하지만 이것을 이용하려면 다음을 충족시켜야 한다.
12. 가능한 "bcp" 또는 "DTS"를 이용하는 것보다 "BULK INSERT" 명령어를 통해 데이터를 옮기는 것이 좋다. "BULK INSERT" 명령어를 이용하면 텍스트 파일로 생성한후 "DTS"를 통해서 값을 가져오거나 서버간 연결을 통해 "DTS"로 가져오는 것보다 훨씬 빠르다. 자신의 데이터가 수억레코드가 된다면 고려해 볼만한 내용이다. 13. SQL Server 테이블에 값을 넣거나 다른 데이터로 변환하고자 할때 DTS를 이용하는 것보다 bcp 유틸리티를 사용하는 것이 좋다. bcp 유틸리티를 사용하는 것이 DTS를 사용하는 것보다 매우 빠르다. 14. 가능한 트랜잭션을 짧게 구성하는 것이 좋다. 트랜잭션 처리 작업이 짧게 이뤄지도록 설계하는것은 데드락을 발생시키는 것을 줄이는 당연한 이야기이다. |
|
상상플러스 - 최근 공지
-
pelly.ryu 2016
라고 적고 보니 4번이 눈에 띄었네요. 지우려고 ... -
pelly.ryu 2016
저처럼 누군가 또 찾아올 것 같아 남깁니다.. `$c... -
와우 2014
깔끔하게 풀어두셔서 많은 도움이 되었습니다 ^^ ... -
ghewffsef 2014
잘배우고 갑니다.. 말한마디 한마디 가슴에 와... -
지나가던길 2014
고맙습니다. 많은 참고가 되었습니다.
0