1. 인덱스 대상 후보컬럼 선정 기준
  • 분포도가 좋은 컬럼인가?
    • 분포도의 기준은 1%이내
    • ex) 주문 테이블의 ‘배송여부’ 컬럼은 Y/N으로 분포도는 50%이다. 논리적식별은 50%이지만 대부분의 실데이터는 Y가 될것이고 분포도가 한쪽으로 극단적으로 쏠리기 때문에 인덱스 후보로 해도된다.
    • ex) 고객 테이블의 성별은 남자/여자 로 50%이다. 하지만 이는 변하지 않는 데이터 이며, 50%의 분포도를 가지므로 인덱스가 딱히 필요치 않다
    • 결합 인덱스의 경우도 해당 결합된 데이터의 분포도를 추산하여 인덱스를 선정한다.
  • 갱신이 자주 발생하지 않는 컬럼인가?
    • update가 발생하면 index를 갱신하기 때문에 성능에 좋지 않지만! 꼭 필요한 경우에는 복합인덱스로 해야 한다. ( 검색 조건에 필요한 경우에 )
  • 조건절에서 자주 사용되는 컬럼인가?
    • 조건절에 있다고 무조건 인덱스를 설정해선 안된다. 쿼리의 사용 빈도를 측정후 해당 쿼리에 사용되는 조건절의 컬럼에 대해서만 인덱스를 추가해줘야 한다. 
    • 만일 하루 10번 실행 / 1000번 실행되는 두개의 쿼리에서 포함된 조인절이 있다면 해당 조건절의 인덱스, 1000번 실행되는 쿼리의 인덱스만 추가해줘도 된다. (과도한 인덱스 추가는 성능의 저하를 가져온다.)
  • 조인의 연결고리에 사용되는 컬럼인가?
  • 소트 발생을 제거하는 컬럼인가?
    • ex) select * from 주문 
      where 상품코드 = ‘’ and 배송여주  = ‘’
      order by 주문일자 desc
 일 경우 두가지 인덱스를 고민해야 한다. 
       인덱스1 : 상품코드 + 배송여부
       인덱스2 : 상품코드 + 배송여부 + 주문일자
소트부하가 크다면 2번으로 인덱스부하가 크다면 1번으로 해야 한다. ( 통계적으로 알수 없으니 대부분2번으로 한다. )


2. 인덱스 생성시 유의사항
  • 인덱스 생성시 유사 인덱스가 있는지 확인
    • 유사 인덱스끼리의 경합에 의해 더 느린 인덱스를 실행하여 성능문제 발생
  • 미사용 인덱스 일 경우에만 삭제하기


3. RBO / CBO
  • RBO(Rule Based Optimizer) - 현재는 사용되지 않음
  • CBO ( Cost Based Optimizer) - 주기적으로 통계정보(AO : Analyze Object)를 갱신하여 가장 적게드는 실행계획을 결정


4.  쿼리와 인덱스
  • select * from 주문, 고객
    where 주문.고객번호 = 고객.고객번호
    and 주문.주문일자 = ?
    and 고객.고객명 = ?
  • 위와 같은 쿼리에선 인덱스를 주는 방법은 
    • 주문일자로 검색할 경우 : 주문일자 + 고객.고객번호 
    • 고객명으로 검색할 경우 : 고객명 + 주문.고객번호
    • 주문일자+고객명으로 검색할 경우 : 주문일자 + 고객.고객번호 + 주문.고객번호 + 고객명
    • 위의 세가지 경우 3번은 optimizer가 최소 비용으로 선택하지만, 1, 2의 검색일 경우 타지 않을 수 있다
    • 검색의 조건에 따라 인덱스가 필요하며, 필요한 경우 힌트를 이용해 강제로 인덱스를 지정해야 한다


5. 테이블 수정과 인덱스
select * 
from 사원, 교육계획, 과목, 강사, 교육평가
where 사원.사원번호 = 교육계획.사원번호
and 교육계획.과목번호 = 과목.과목번호
and 교육계획.강사번호 = 강사.강사번호(+)
and 교육계획.교육번호 = 교육평가.교육번호
and 사원.성명 = ‘홍길동’
and 과목.과목명 = ‘수학’
and 교육평가.평정 = ‘A+’

위의 쿼리를 수정및 인덱스 추가 일 경우
  1. 최초 진입형 테이블 우선
    1. join의 첫번째 사원 -> 교육계획 -> ? -> ? -> ?
  2. outer 도바 inner가 먼저 
    1. 강사 테이블의 경우 outer이므로 사원 -> 교육계획 -> ? -> ? -> 강사
  3. where절에서 데이터를 축소 할수 있는것 부터
    1. 과목.과목명 = ‘수학’ 은 몇개의 과목명 분포가 있는지 알수 없음 반면 교육평가.평점 = ‘A+’일 경우 F~A까지의 분포가 예상되므로 먼저 불러들어 데이터 축소를 할수 있음  사원 -> 교육계획->교육평가->과목->강사 순으로 쿼리 수정
  4. 최종적으로 다음과 같이 수정
    1. select * from 사원, 교육계획, 교육평가, 과목, 강사
      where 사원.사원번호 = 교육계획.사원번호
      and 교육계획.교육번호 = 교육평가.교육번호
      and 교육계획.과목번호 = 과목.과목번호
      and 교육계획.강사번호 = 강사.강사번호(+)
      and 사원.성명 = ‘홍길동
      and 교육평가.평점 = ‘A+
      and 과목.과목명 = ‘수학



6. 수직분할 / 수평분할
    만약 한 테이블에 수많은 컬럼이 존재 한다면 디스크의 여러 블록에 데이터가 저장되므로 I/O 성능 저하를 불러온다. 
- 로우체이닝 : 길이가 너무 커서 하나의 블록에 저장되지 못하고 다수의 블록에 나누어져 저장
- 로우마이그레이션 : 수정된 데이터를 해당 테이터 블록에 저장하지 못하고 다른 블록의 빈 공간에 저장

- 수직분할 : 컬럼을 기준으로 분할하는 것으로 일반적으로 정규화에 속한다 
- 수평분할 : 대용량 테이블을 같은 테이블을 여러개 생성하여 일정한 구분에 따라 데이블을 나누는 것으로 보통 시간적(일별 / 월별 / 연도별 데이터), 지역별(서울, 경기 등 ) 으로 나누어 테이블을 관리하여 성능을 향상시킨다.

+ 하나의 테이블에 대량의 데이터가 있다면 파티션 분할을 통해 성능 개선에 도움을 줄수 있다.
  

    



WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret



ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value


Mysql 버전이 높아지면서 보안관련 인한 오류


User 생성시 Host, User ,Password, ssl_cipher, x509_issuer, x509_subject 를 입력 필수


ssl_cipher, x509_issuer, x509_subject 값은 '' 빈값을 입력.



$ insert into user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject ) values('localhost','사용자명',password('비밀번호'),'','','');




ERROR 1364 (HY000): Field 'authentication_string' doesn't have a default value


* mysql 5.5 에서 user 생성시 authentication_string 필드 추가 필수. '' 값으로 넣기.



$ insert into user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject, authentication_string) values('localhost','사용자명', password('비밀번호'),'','','','');



WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret

SET sql_mode = ''


or


set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';



WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret

mysql int bigint

Database/DB 2016.06.20 11:51

mysql을 사용하면서  몰랐던 정보들을 알아서 적어본다. 


int / bigint 에 관한 내용인데 


먼저 int / bigint 의 최대값은 unsigned 일 경우 각각 4억과 4천경이다(저만큼 넣을 데이터가 있는건가..)......


int는 4바이트(자릿수 10자리)


bigint는 8바이트로 (자릿수 20자리) 로 생각면 된다. 


자세한 사항은 공식 홈피 확인


https://dev.mysql.com/doc/refman/5.5/en/integer-types.html




두번째로 대부분 많은 분들이 테이블 선언시 int(4) 하면 4자리까지만 들어간다고 생각하실수도 있는데 (실은 내가 그랬다.)


만일 3자리 숫자 999 를 넣었다면 출력될때 0999 로 해당 4자리이하일때 앞에 0을 붙여서 내보낸다. (zerofill 옵션)


물론 저장할때엔 그냥 999로 저장함. 


그러니 테이블 생성할때 int(숫자)라면 단순히 zerofill 옵션이니 필요없다면 굳이 사용안해도 된다. (자릿수를 위한거라면 TINYINT / SMALLINT / MEDIUMINT 을 써야 옳다.)





WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret


재미있는것을 발견( 지금까지 몰랐던것을 발견)!!



테이블간의 관계인 fk( Foreign Key Constraint) 가 설정되어 있을때 

fk 생성시 해당 칼럼을 not null 속성을 넣지 않았다. (이건 가능함)

헌데 not null  속성을 넣을려면


먼저 해당 fk 를 지운후 alter 로 notnull 속성을 넣어준후 다시 fk 속성을 넣어줘야 한다. 


여기까진 문제가 없다. 

이번엔 unsigned 속성을 넣어보려 한다. 

헌데  fk의 기본이 되는 pk 속성중에 unsigned 속성이 없다면 어떻게 되느냐?!


아까와 같이 

"먼저 해당 fk 를 지운후 alter 로 unsigned 속성을 넣어준후 다시 fk 속성을 넣어줘야 한다. "

에서 unsigned 속성을 넣은후 다시 fk 속성을 넣을 때 에러가 발생한다!!!

왜냐하면 fk의 pk의 속성엔 unsigned 속성이 없으니까!!

문제는 이때 fk는 지워졌지만 unsigned 속성은 정상적으로 들어가 있다. (fk 주기 전에 속성을 변경했으므로)


그래서 "어라?! 에러가 났는데 잘 되어 있네?!" 라고 생각하도 진행하다가 fk가 없어진거 발견.

헌데 fk를 넣을려고 하면 그냥 해당 FK넣을수 있는 칸이 생성되질 않는다. (mysql workbench 시)

이게 뭐야.. 하면서 몇시간을 허비.. 


이래서 IDE자동화가 무서운 겁니다. 

오늘의 교훈!! IDE를 너무 믿진 마세요!!



- 아마 콘솔로 작업했으면 에러 메시지 보고 바로 해결했을 경우이다. 





WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret



만일 mysql 이 설치가 되었고 ps -ef | grep mysql 현 상태에서 서비스가 실행중이라면 

실행하고 다시 접속하면 정상적으로 실행된다. 

<pre class="brush:javascript">

mysqld_safe --user=root &

</pre>




WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret

두개의 테이블에서 각각 한개의 칼럼을 비교하여 없는것을 찾는 조인이 부정형 조인 / 안티 조인이라 한다.


 두개의 테이블 A , B 가 아래와 같이 있다고 하자.                 

만일  A not in (B) 를 한다면 결과 값은 bbb 가 나오게 된다.

만일 B not in (A) 가 된다면? 아래의 그림처럼 ccc, ddd 가 나오게 된다.





소스는 심플하다.



select 

distinct a.id , a.name

from A a

where 

 a.id is not null

 and a.id not in (select /*+ HASH_AJ*/  id  from B  where id is not null)


 - 오라클 11g부터는 not null을 명시 하지 않아도 된다. 그전 버전을 사용한다면 꼭 not null을 넣어주기 바랍니다.(결과값이 달라집니다.)

- 실행계획은 강제적으로 /*+ HASH_AJ*/ 을 넣었습니다. 명시 하지 않으면 다르게 실행될 가능성이 있습니다.





WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret

toad 결과의 한글이 깨진게 아닙니다. sql 쿼리 날릴때 한글이 갑자기 않보입니다.(결과가 깨진게 아닙니다.)

근데 쿼리는 정상적으로 돌아갑니다. 않보이는 곳 복사하여 메모장에서 보니 정상적입니다.


문제의 화면입니다.


-_- 무슨 이유인지는 모르겠지만 갑자기 토드의 폰트가 바뀌었습니다.

해당 폰트에서는 한글이 먹히지 않는 폰트였구요.(쿼리 치다가 갑자기 바뀌어서 무슨일인가 했습니다.)

토드에서 폰트를 변경하는 방법은 다음과 같습니다.


1) 메뉴바에서 View->Toad Options 로 들어갑니다.

2) Editor -> Languages 부분의 Edit 로 들어갑니다.


3) Highlighting -> Custom font 로 들어갑니다. 


4) 이제 원하는 폰트로 바꾸시면 됩니다.




이제 쿼리문의 한글도 정상적으로 보입니다.


단축키로 폰트가 바뀌지는 않을텐데.. 이 부분은 의문입니다.

저의 경우는 포토샵때문에 많은 폰트가 지정되어 있어서 포토샵할때는 별문제가 없었지만

토드에서 이런경우가 발생하니 처음엔 꽤나 당황스러웠습니다.



WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  6개가 달렸습니다.
  1. 좋은정보 감사합니다.^^
    제가 사용하는 툴은 아니지만 한번 사용해 봐야 겟어요.^^
    여기 오늘날씨는 무쟈게 덥답니다.
    건강조심하시고 즐거운 하루 되세요.
  2. sql게이트 쓰시나요?ㅎ
    여러 DB프로그램쓰다가.. 역시 토드만한게 없더라고요;ㅎ
    이제 여름시작이네요~ 초보플밍님도 몸건강하세요!
  3. 굿입니다 감사합니다.
  4. 깃헙 정리하신것 검색타고 들렀습니다.
    개발자인데, 블로그를 잘 지으셨네요. 부럽.

    좋은 내용(개발외것도) 잘 둘러보고 갑니다.

    어쩜 종종 들릴지도.(그래봤자 티스토어 계정이 없어서 눈팅이 될듯.)
secret


제목 그대로 입니다.

DBlink 시키느라고 뻘짓을 하루동안이나 했는데.

기쁨의 환희에 젖어 select 날리는 순간.

(테이블과 사용자는 보안상 지웠습니다.)


"ora-22992 오류 lob 위치를 알수없다!"

물론 해당 테이블을 로컬 DB에 똑같이 생성해주면 되지만. 그건 말도 안되는 일.

하필 가장 중요한 칼럼이 clob로 되어 있다.ㅜㅡㅜ 

해당 테이블을 view로 만들어서 보는 방법도 있다고 한다. 링크 참조 : oracleclub

뭐... 해당 DBA와 상의해서 DB서버에  다이렉트로 붙기로 합의;ㅋ

(물론 다이렉트로 붙으면 안된다는것은 모든 서버사이드 개발자와 DBA가  아는 기본상식; )





WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret


잘되던 웹싸이트가 가끔씩 에러를 뿜어낸단다. 설마해서 계속 클릭해봤더니 위와 같이 에러가 나왔다.
에러 내용도 상당히 희한한데 . 어떻게 고쳐야 할지도 막막한 상황.

구굴링을 해보면 DB LINK 설정이 잘못되어 있거나 Oracle8 버전이하에서의 버그라고만 씌어져 있다.

DBLINK 는 내가 손볼곳이 아니라서 잘모르겠고, 오라클도 10버전이라서 난감해하고 있었는데

JDBC 의 버전이 맞지 않아서 생기는 경우도 있다고 한다.

JDBC가 하위버전일 경우에 나오는 버그로 세션이 제대로 끝기지 않는다고 한다.


결국 odbc.jar 파일을 최신 파일로 교체해주니 아직까진 에러가 나오진 않았다.(4일정도 됐음.)

혹시나 하는 분들은 최신 odbc 파일로 파꿔보시길. 

WRITTEN BY
No.190
세계정복의 시작점

트랙백  0 , 댓글  0개가 달렸습니다.
secret