[MySQL] 테이블 스키마 설계 고려사항

테이블 생성시 초보자들이 흔히 하는 실수는 primary key 부분 설계를 잘못하는 것 같다.

KIMSQ RB 의 테이블을 가져다 내 나름대로 간단하게 분석하고자 한다.

그누보드, KIMSQ RB 와 같은 빌더 들은 프로그램 고수가 만드는 거라고 보면 된다.
따라서 이런 빌더의 테이블 구조를 분석하는 것은 내 프로그램 지식 향상에 큰 도움이 된다.
여기서 테이블의 칼럼 하나 하나 분석하고자 하는 것은 아니다.

이런 방법으로 테이블 설계가되어 있구나 하는 정도만 이해하도록 하기 위함이다.

회원 ID를 저장할 때 테이블을 분리해서 저장하도록 설계되어 있다.

rb_s_mbrid 테이블의 uid 칼럼 = rb_s_mbrdata 테이블의 memberuid 와 관련이 있도록 설계되어 있다.

rb_s_mbrid 테이블의 uid 칼럼은 정수형 11자리, NOT NULL, AUTO_INCREMENT (자동증가) 로 되어 있다.

즉, 테이블에 데이터가 추가될 때마다 자동으로 증가되도록 설계되어 있다.

데이터를 삭제하고 새로운 데이터를 추가하면, 지워진 데이터 번호가 5번이라고 하자. 새로 추가된 데이터는 자동증가가 되므로 절대 5번이 되지 않는다. 데이터베이스의 기본 사상은 무결점이다.

PRIMARY KEY (`uid`), PRIMARY KEY (`memberuid`)

두개의 값은 항상 서로 같도록 설계하고 있다. rb_s_mbrid 테이블에서는 자동증가되도록 하고 다른 (rb_s_mbrdata) 테이블에서는 자동 증가는 없다.

PHP 코드 상에서 데이타를 저장할 때 두개의 테이블에 나누어서 저장되도록 되어 있다.

rb_s_mbrdata 테이블의 memberuid 칼럼은 rb_s_mbrid 테이블의 uid 칼럼의 FOREIGN KEY 다.

primary key 에 대해 검색하면 https://msdn.microsoft.com/ko-kr/library/ms179610.aspx 에 자세하게 나온다.

MS-SQL 에 대한 사항이지만, primary key 정의는 SQL 이 거의 동일하다고 보면 된다.

primary key

테이블에 대해 primary key 제약 조건을 지정하면 데이터베이스 엔진은 primary key 열에 대해 고유 인덱스를 자동으로 만들어 데이터 고유성을 적용한다.
또한 쿼리에서 primary key가 사용되는 경우 이 인덱스를 사용하여 데이터에 빠르게 액세스할 수 있다.
primary key 제약 조건이 두 개 이상의 열에 정의되는 경우 한 열에 중복된 값이 있을 수 있지만
primary key 제약 조건 정의에 있는 모든 열의 값 조합은 각각 고유해야 한다.

라고 나온다.

FOREIGN KEY

primary key 제약 조건과 달리 외래 키 제약 조건을 만들어도 해당 인덱스가 자동으로 생성되지 않는다.
외래 키 열은 쿼리에서 한 테이블의 외래 키 제약 조건 열을 다른 테이블의 기본 또는 고유 키 열과 연결하여 테이블의 데이터를 병합하는 조인에서 자주 사용된다.
데이터베이스 엔진 에서는 인덱스를 만들어 외래 키 테이블에 있는 관련 데이터를 빠르게 찾을 수 있다.

foreign key (칼럼명) references 부모테이블명(부모칼럼명) on delete cascade;

restrict	참조하는 부모테이블의 칼럼(column)이 삭제되어도 지우지 마라.
cascade	참조하는 부모테이블의 칼럼(column)이 삭제되면 자식 테이블의 칼럼도 모두 삭제하라
set null	참조하는 부모테이블의 칼럼(column)이 삭제되면 자식 테이블의 칼럼이 모두 null이 된다.
no action	참조하는 부모테이블의 칼럼(column)이 삭제되어도 무시하라.
set default	참조하는 부모테이블의 칼럼(column)이 삭제되면 지정된 값으로 대체하라.

on delete rule 또는 on update rule 로 옵션을 지정할 수 있는데, 옵션을 주지 않으면 삭제와 변경이 제한된다.

primary key를 제대로 정의하는 것은 데이터베이스 디자인에 있어서 매우 중요한 출발점이다.
모든 테이블에는 primary key가 있어야 하며, 오직 하나의 primary key만 존재할 수 있다.
회원 테이블을 설계할 때 primary key 로 정의하는 칼럼은 uid 또는 idx 를 많이 사용한다.
id 또는 userID 칼럼은 중복없이 생성되도록 하기 위해서 unique index 를 설정한다.
primary key의 유무가 엄청난 성능 차이를 보인다.
일반적으로 primary key를 기준으로 데이터를 select 한다거나 primary key를 기준으로
다른 컬럼(들)의 값을 update 또는 delete하는 작업이 흔히 수행되기 때문에
테이블에 primary key를 정의해 주면 where 조건절에 primary key가 검색조건으로 사용된 쿼리들의 성능은 현저하게 향상된다.
primary key를 생성하지 않아서 primary key를 사용하는 쿼리들의 성능이 나쁜 것은 물론이며,
테이블에 잘못된 중복 데이터들이 저장됨으로 인하여 데이터 무결성까지 손상되어 있는 비극적인 상황까지 발전한 경우도 있다.

Primary key 컬럼은 반드시 NOT NULL로 정의해야 한다.

KEY `site` (`site`) : 인덱스 설정이 되어 있다는 의미다.

MySQL 의 인덱스는 여러개를 설정해도 실제 동작될 때에는 1개만 동작된다.

Oracle 은 여러개의 인덱스를 자동으로 선택하여 최적의 알고리즘으로 동작한다고 들었다.

따라서, 어떤 인덱스가 동작되도록 할 것인지는 설계자의 몫이다.

테이블 설계시 하나의 테이블에 모든 데이터가 저장되도록 하지 않고 다른 테이블에 분산해서 저장되도록 설계를 잘 하는 것이 중요하다.

테이블 검색시 문자열을 검색하는 것보다 숫자를 검색하면 성능이 훨씬 우수하다.

검색할 결과가 문자열이라고 해도 테이블 설계시 숫자로 검색되도록 설계하여 성능 향상이 되도록 하는 것도 필요하다.

CREATE TABLE IF NOT EXISTS `rb_s_mbrid` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `site` int(11) NOT NULL DEFAULT '0',
  `id` varchar(50) NOT NULL DEFAULT '',
  `pw` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`),
  KEY `site` (`site`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `rb_s_mbrdata` (
  `memberuid` int(11) NOT NULL,
  `site` int(11) NOT NULL DEFAULT '0',
  `auth` tinyint(4) NOT NULL DEFAULT '0',
  `sosok` int(11) NOT NULL DEFAULT '0',
  `level` int(11) NOT NULL DEFAULT '0',
  `comp` tinyint(4) NOT NULL DEFAULT '0',
  `admin` tinyint(4) NOT NULL DEFAULT '0',
  `adm_view` text NOT NULL,
  `email` varchar(50) NOT NULL DEFAULT '',
  `name` varchar(30) NOT NULL DEFAULT '',
  `nic` varchar(50) NOT NULL DEFAULT '',
  `grade` varchar(20) NOT NULL DEFAULT '',
  `photo` varchar(200) NOT NULL DEFAULT '',
  `home` varchar(100) NOT NULL DEFAULT '',
  `sex` tinyint(4) NOT NULL DEFAULT '0',
  `birth1` smallint(6) NOT NULL DEFAULT '0',
  `birth2` smallint(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `birthtype` tinyint(4) NOT NULL DEFAULT '0',
  `tel1` varchar(14) NOT NULL DEFAULT '',
  `tel2` varchar(14) NOT NULL DEFAULT '',
  `zip` varchar(6) NOT NULL DEFAULT '',
  `addr0` varchar(6) NOT NULL DEFAULT '',
  `addr1` varchar(200) NOT NULL DEFAULT '',
  `addr2` varchar(100) NOT NULL DEFAULT '',
  `job` varchar(30) NOT NULL DEFAULT '',
  `marr1` smallint(6) NOT NULL DEFAULT '0',
  `marr2` smallint(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `sms` tinyint(4) NOT NULL DEFAULT '0',
  `mailing` tinyint(4) NOT NULL DEFAULT '0',
  `smail` tinyint(4) NOT NULL DEFAULT '0',
  `point` int(11) NOT NULL DEFAULT '0',
  `usepoint` int(11) NOT NULL DEFAULT '0',
  `money` int(11) NOT NULL DEFAULT '0',
  `cash` int(11) NOT NULL DEFAULT '0',
  `num_login` int(11) NOT NULL DEFAULT '0',
  `pw_q` varchar(250) NOT NULL DEFAULT '',
  `pw_a` varchar(100) NOT NULL DEFAULT '',
  `now_log` tinyint(4) NOT NULL DEFAULT '0',
  `last_log` varchar(14) NOT NULL DEFAULT '',
  `last_pw` varchar(8) NOT NULL DEFAULT '',
  `is_paper` tinyint(4) NOT NULL DEFAULT '0',
  `d_regis` varchar(14) NOT NULL DEFAULT '',
  `tmpcode` varchar(50) NOT NULL DEFAULT '',
  `sns` text NOT NULL,
  `addfield` text NOT NULL,
  `cp` int(11) DEFAULT '0',
  `mcp` int(11) DEFAULT '0',
  `cpcode` int(11) DEFAULT NULL,
  `recommand` varchar(60) DEFAULT NULL,
  `broadcast` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`memberuid`),
  KEY `site` (`site`),
  KEY `auth` (`auth`),
  KEY `comp` (`comp`),
  KEY `sosok` (`sosok`),
  KEY `level` (`level`),
  KEY `admin` (`admin`),
  KEY `email` (`email`),
  KEY `name` (`name`),
  KEY `nic` (`nic`),
  KEY `sex` (`sex`),
  KEY `birth1` (`birth1`),
  KEY `birth2` (`birth2`),
  KEY `birthtype` (`birthtype`),
  KEY `addr0` (`addr0`),
  KEY `job` (`job`),
  KEY `marr1` (`marr1`),
  KEY `marr2` (`marr2`),
  KEY `sms` (`sms`),
  KEY `mailing` (`mailing`),
  KEY `smail` (`smail`),
  KEY `point` (`point`),
  KEY `usepoint` (`usepoint`),
  KEY `now_log` (`now_log`),
  KEY `d_regis` (`d_regis`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;