여기서는 MySQL을 써먹으면서 생겼던 일들을 두서없이 풀어놓은 곳입니다.

• 참고문헌
  • Elmasri외 1인, Fundamentals of Database Systems, 2011, Pearson Education.
  • 김유성 교수, 데이터베이스 시스템 설계, 2013년 상반기 강의.
  • 그 외의 여러 위키피디아 페이지들.

앞으로 찾아봐서 업데이트 할것을 여기에 적자.

• Character-Set과 Collation의 차이.
• Index의 기법과 B 트리의 원리
• Index 이야기
• SQL 꼼수들

앞으로 MySQL 외의 Oracle 과 Tibero 와 같은 상용 DB이야기가 들어갈 예정.

DBMS이 Database와 Programming Language간에 Transactional Memory를 구현하기 위한 동시성 제어기법.

한 개체가 DB에 쓰기 수행중일 때 다른 개체가 읽기를 시도하면, 가장 쉬운 방법은, 쓰기가 완료될 때까지 기다렸다가 읽는것.¹⁾. 허나 이것은 아주 느리니까, MVCC는 각 사용자가 DB접근시 스냅샷에 접근한다는 개념을 도입한다. 사용자가 쓰기 명령을 할때, 다른 사용자는 이런 변경이 완료되기 전까지는 결과를 보지 못한다.²⁾

MVCC DB에서 데이터를 Update할 필요가 있을때, 이것은 이전 data를 Overwrite하지 않고, 옛 데이터를 “쓸모없음³⁾“이라고 표시한 후, 새로운 데이터를 다른 곳에 기록한다. 이렇게 되면, 다중 버전이 저장되지만, 가장 최신의 데이터를 조회하게 됨. 읽는 이는 설령 읽고있는 데이터가 삭제/변경되어도 여전히 데이터에 접근가능 하다. 게다가, DB가 메모리나 디스크의 공백을 채우는 오버헤드도 감소하게 된다. 단, 주기적으로 “쓸모없는”데이터를 삭제하고 정리하는 작업이 필요로 하게된다.

Normalization

데이터의 중복을 막고 효율적인 관계로 분류하는 것이 정규화. 하지만 사실 지나친 정규화는 잦은 JOIN과 복잡한 UPDATE연산을 유발시켜서 성능을 많이 저하시킨다. 그래서 데이터 스키마를 더 간단하게 만들고 써먹을 수 있는 NoSQL DBMS 가 각광받고, MySQL을 NoSQL처럼 써먹기도 한다. 내가 그랬음.

데이터베이스의 설계는 논리 설계, 구현단계에서 '좋고 나쁨'을 따져볼 수 있다. 관계 스키마의 '질'은 다음의 척도로 따져볼 수 있다.

• 속성의 의미가 분명 한가.
• Tuple 내에 불필요한 정보가 있는가.
• Tuple 내에 NULL값이 있는가.
• 가짜(spurious) tuple을 만들어내지는 않는가.

좋은 스키마를 설계하기 위한 가이드라인이 있다. Functional Dependency를 참고하자.

1. 의미가 명확하도록 관계 스키마를 설계 하라. 하나의 관계에 두개 이상의 속성을 병합하지 말자.
2. 관계 스키마에 있어서 '갱신 예외;update anomalies'가 있지 않도록 하자. 만약 불가피 하다면, 표시를 명확하게. DB Update시 제대로 작동하도록 설계할 것.
3. NULL이 자주 발생할 여지가 있는 속성을 배치하지 말자. 불가피 하다면, 예외적인 경우에만 생성하도록.
4. 적절한 관계를 맺은 속성들 끼리만 join되도록 관계를 설정하자.⁴⁾ FK, PK 조합으로 관계를 맺지 않도록 하자.

예외(Anomalies)는 낭비적 행동을 유발하고, NULL은 공간낭비를 초래하면서 퍼포먼스 낭비까지 유발하고, Join시 유효하지 않은 정보를 생성하게 됩니다 ㅇㅇ. Tuple 내에 NULL값이 다수 존재하는 것은 관계가 Fat 하다는 것을 의미. NULL이 많다는 것은 저장공간을 낭비하고, 의미 해석을 어렵게 한다.

관계 스키마의 분석을 위한 도구. 위에 서술한 가이드라인을 잘 충족했는지 검사 할수 있게 해준다.

반복속성을 제거하며, 내재 관계(nested relation)을 허용하지 않는다. nested relation 속성을 새 관계로 정의하고 pk를 부여한다.

위 그림에서 (a)는 정규화 되어 있지 않은 형태이며 (b)가 tuple이 삽입된 예제. 이를 1차 정규화 하면 (c)의 형태가 형성된다.

Full Functional Dependency에 입각한 정규화.⁵⁾ nonprime 속성 A 모두가 관계 스키마 R에 대해 함수적 종속 관계인 상태.

Trasitive Dependency에 입각한 정규화. 2차 정규화를 만족하면서 모든 nonprime 속성이 PK에 대해 타동적으로(transitively) 관계맺어지지 않은 상태?뭔 소리지

“관계”가 비-키(nonkey) 속성에 종속된 비키 속서잉 있어서는 안된다 라는데… 뭔말인지… 그림으로 봅시다.

PK는 Property_id, 후보키는 Country_name, Lot#.

(a)는 1차 정규화가 이루어진 상태. 허나 엄청나게 복잡한 Functional Dependency관계를 갖고 있다.

(b)의 LOTS1, LOTS2는 2차 정규화 된 상태. Tax_rate속성은 PK에 대해 Functional Dependecy를 갖고 있지 않기 때문에 최초 테이블에서 빠져나와서 LOTS2로 분화되었다.

(c)의 LOTS1A, LOTS1B, LOTS2는 3차 정규화 된 상태. Area, Price는 nonkey property이고, 3차 정규화에서는 이 속성들이 관계를 맺지 않도록 규정한다.

자주 써먹는 리눅스 꼼수 참고할것.apt-get을 쓰는 우분투보다 훨-씬 거지같다. 우분투 만세

등록예정.

https://www.linux.co.kr/home2/board/subbs/board.php?bo_table=lecture&wr_id=1654 여기를 참고할것.

콘솔 내에서 해당 유저에 대해 접근권한을 부여하자.

insert into mysql.user (host, user, password, ssl_cipher, x509_issuer, x509_subject) values ('%', 'root', password('--패스워드--'), '', '', '');

이러고 서비스를 다시시작해야된다.

보통은 관리 콘솔 등을 통해 패스워드를 입력할 기회를 제공하지만 그것이 아니라면,

update user set password=password('new-password') where user='root';
flush privileges; // 권한 테이블을 업데이트 하는것.

레코드를 삽입하는 경우에 처리하려면 INSERT IGNORE 아닌경우에는 키 속성 혹은 Unique Index를 참조하면 된다.

SELECT (column name) FROM (table name) (6371*acos(cos(radians(lat좌표값))*cos(radians(slLat))*cos(radians(slLng)-radians(lng좌표값))+sin(radians(lat좌표값))*sin(radians(slLat))))
AS distance

이때 6371은 KM단위로 환산하기 위한 상수이다.

원래 거리를 계산 하기 위한 기본적인 공식은, sqrt(x^2 + y^2). 그래서, sqrt((x1-x2)^2+ (y1-y2)^2);

이건 거리 계산하는 걸 좀더 고민해보고 공식으로 정리할 것.

  CREATE VIEW (view 이름) AS (SELECT 구문을 통해 생성된 레코드 테이블.)

이렇게 해놓으면 복잡한 SELECT 문을 수행하는 가상 테이블을 만들고 이를 SELECT하여 내부적으로 쿼리를 절약할 수 있긴 하다. 나머지는 RDB교과서의 View 항목을 참고해야 할것 같다. View 에는 insertion을 수행할 수도 있긴 하지만 힘들고 권장되지 않는다고 하는데 곧 추가하기.

DUMP 를 떠놓는 것은 아래 명령으로 간단하게 가능하다.

  mysqldump -u root -p (databaseName) > (arbit_name).sql

특정 테이블만 떠놓으려면,

  mysqldump -u root -p (databaseName) (someTable) > (arbit_name).sql

Schema만 백업받기.

  mysqldump -u root -p -d (databaseName) (someTable-optional) > (arbit_name).sql
  

Data 만 백업받기.

  mysqldump -u root -p --no-create-info (databaseName) (someTable-optional) > (arbit_name).sql

복구 할때는 그냥 SQL 문을 실행하는 것이라 생각하면 된다.

  mysql -u root -p (dbName) < (arbit_name).sql

아래의 방법은 아예 디폴트 캐릭터 셋을 바꾸는 것임.

mysql 콘솔 접속 후, 우선 아래 명령을 통해 확인.

show variables like 'c%';

이러면 utf8이 아니라 latin1같은 이상한 코드로 반영이 되어있다. 이것을 바꾸기 위해서 /etc/my.cnf 를 변경하고 서비스를 재시작하자.

[mysqld]
init_connect="SET collation_connection=utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

트랜잭션은 일련의 명령을 하나의 단위로 묶어내어서 명령의 '원자성'을 보장하기 위한 것. 기본적으로, MySQL의 Transaction처리에 관한 내용은 아래의 주소에서 다 언급을 하고 있다.

MySQL 공식 레퍼런스.

명령은 아래와 같은 내용으로 수행.

  START TRANSACTION;
  SELECT () FROM (table) WHERE (where);
  UPDATE (table) SET (something) WHERE (where);
  COMMIT;
  

기본적으로 MySQL은 autocommit 이 활성화 된 상태. 이는 즉 명령을 실행하는 대로 바로 디스크에 기록한다는 의미. 이것을 '암시적'으로 해제 하기 위해 사용하는 것이 START TRANSACTION.. 사실 위의 명령에 COMMIT명령이 있는 이유가 명시적인 Commit 명령을 수행하기 위한 것인 듯. 이게아닐때 보통은 BEGIN을 사용한다.

되돌릴 때는 ROLLBACK을 사용한다.

  ROLLBACK (something)

꾸준하게 쓰자 꾸준하게…

이걸로 한방에 하면 끝.

ALTER TABLE `테이블명` CHANGE `컬럼명` `컬럼명` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;

여기서 컬럼명에 들어갈 내용이 타임스탬프 임을 생각하자.

Master와 Slave 관계의 두 데이터베이스가 필요하다.

별거 없다. 일단 이렇게 써먹었다.

SELECT (column) FROM (table) WHERE (column) in (Another Query)...

기억으로는 성능 이슈가 있었던것 같은데, 나중에 추가하기.

지금 수행하는 프로젝트에서는 LEFT JOIN을 많이 사용한다.

• LEFT JOIN : 두 테이블간 관계에서 주 테이블의 레코드는 모두 싣고 보조 테이블의 레코드는 FK에 있으면 싣고 없으면 말고.
• INNER JOIN : JOIN 하려는 FK 가 있을때만 새로 싣는다.