[펌]Mysql설계시 고려사항

DB 2014.11.20 19:53

테이블 설계 시 유의 사항

1. 반드시 Primary Key를 정의하고 최대한 작은 데이터 타입을 선정한다.

  • 로그 성 테이블에도 기본적으로 PK 생성을 원칙으로 함
  • InnoDB에서 PK는 인덱스와 밀접한 관계를 가지므로 최대한 작은 데이터 타입을 가지도록 유지

2. 테이블 Primary Key는 auto_increment를 사용한다.

  • InnoDB에서는 기본 키 순서로 데이터가 저장되므로, Random PK 저장 시 불필요한 DISK I/O가 발생 가능
  • InnoDB의 PK는 절대 갱신되지 않도록 유지
    (갱신 시 갱신된 행 이후 데이터를 하나씩 새 위치로 옮겨야 함)

3. 데이터 타입은 최대한 작게 설계한다.

  • 시간정보는 MySQL데이터 타입 date/datetime/timestamp 활용
  • IP는 INET_ATON(‘IP’), INET_NTOA(int) 함수를 활용
  • 정수 타입으로 저장 가능한 문자열 패턴은 최대한 정수 타입으로 저장

4. 테이블 내 모든 필드에 NOT NULL 속성을 추가한다.

  • NULL을 유지를 위한 추가 비용 발생
    (NULL 허용 칼럼을 인덱싱 할 때 항목마다 한 바이트 씩 더 소요)

5. Partitioning을 적절하게 고려하여 데이터를 물리적으로 구분한다.

  • 데이터 및 인덱스 파일이 커질수록 성능이 저하되므로Partitioning 유도
  • PK 존재 시 PK 내부에 반드시 Partitioning 조건이 포함되어야 함

인덱스 설계 시 유의 사항

1. 인덱스 개수를 최소화 한다.

  • 현재 인덱스로 Range Scan이 가능한지 여부를 사전에 체크
  • 인덱스도 서버 자원을 소모하는 자료구조이므로 성능에 영향을 줌

2. 인덱스 칼럼은 분포도를 고려하여 선정한다.

  • 인덱스 칼럼 데이터의 중복이 줄어들수록 인덱스는 최대의 효과를 가짐
  • 하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 컬럼으로 적합함
    1
    2
    
    SELECT count(distinct INDEX_COLUMN)/count(*)
    FROM TABLE;

3. 커버링 인덱스(Covering Index)를 활용한다.

4. 스토리지 엔진 별 INDEX 특성을 정확히 인지한다.

  • InnoDB에서 데이터는 PK 순서로 저장되고, 인덱스는 PK를 Value로 가짐
  • MyISAM은 PK와 일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용
    (MyISAM 엔진에서 ORDER BY 시 DESC는 가급적 지양)

5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.

  • 긴 문자열 경우 Prefix 인덱스(앞 자리 몇 글자만 인덱싱)를 적용
    1
    
    CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))
  • Prifix Size는 앞 글자 분포도에 따라 적절하게 설정
    (하단 결과가 1에 가까울 수록 최적의 성능 유지, 0.9이상 권고)

    1
    2
    
    SELECT count(distinct LEFT(INDEX_COLUMN,3))/count(*)
    FROM TABLE;

6. CRC32함수 및 Trigger를 활용하여 인덱스 생성한다.

  • URL/Email같이 문자 길이기 긴 경우 유용
  • INSERT/UPDATE 발생 시 Trigger로 CRC32 함수 실행 결과 값을 인덱싱
  • CRC32 결과값을 저장할 칼럼 추가 및 인덱스 생성
    1
    2
    
    alter table user_tbl add email_crc int unsigned not null;
    create index idx01_email_crc on user_tbl (email_crc);
  • Insert Trigger 생성
    1
    2
    3
    4
    5
    6
    
    create trigger trg_user_tbl_insert
    before insert on user_tbl
    for each row
    begin
    set new.email_crc = crc32(lower(trim(new.email)));
    end$$
  • Update Trigger 생성
    1
    2
    3
    4
    5
    6
    7
    8
    
    create trigger trg_user_tbl_update
    before update on user_tbl
    for each row
    begin
    if old.email <> new.email then
    set new.email_crc = crc32(lower(trim(new.email)));
    end if;
    end$$
  • 검색 쿼리
    1
    2
    3
    4
    
    select *
    from user_tbl
    where email_crc = crc32(lower(trim('mail@domain.com')))
    and email= 'mail@domain.com'

    CRC32 결과가 중복되어도, email값을 직접 비교하는 부분에서 중복이 제거됩니다.

7. 중복 인덱스 생성 회피

  • MySQL은 동일한 인덱스를 중복 생성해도 에러를 발생하지 않음
  • Primary Key로 구성된 칼럼과 동일한 인덱스를 생성하지 않도록 주의



출처 : http://gywn.net/2012/05/matters-require-attention-with_mysql/

신고

'DB' 카테고리의 다른 글

[펌]Mysql설계시 고려사항  (1) 2014.11.20
MYSQL 테이블 복사  (0) 2014.09.13
테이블복사  (1) 2011.11.23
MSSQL - ROW_NUMBER  (0) 2011.11.01
ERROR : Conversion failed when converting date and/or time from character string.  (0) 2011.04.19

MYSQL 테이블 복사

DB 2014.09.13 01:23

MySQL 테이블 복사하기 


필드의 타입과, 필드의 숫자가 동일한 두 테이블간의 데이터를 복사하는 쿼리를 적어 놓는다. 

다른 두 데이터베이스간 테이블 복사하는 방법도 함께 적어 놓자. 


  복사할 테이블이 존재하지 않을경우

테이블을 생성후, 데이터를 복사

CREATE TABLE [대상 테이블명] SELECT * FROM [원본 테이블명] 



  복사할 테이블이 존재하는 경우 

기존의 데이터를 바로 복사 

INSERT INTO [대상 테이블명] SELECT * FROM [원본 테이블명]  



  다른 데이터 베이스 간의 테이블 값 복사 

mysql> INSERT INTO [대상 데이터베이스명].[대상 테이블명] 

        -> SELECT * FROM [원본 데이터베이스명].[원본테이블명] 




필드의 타입과 숫자가 다르면 당연히 복사가 안된다. ㅎ



  중복 데이터에 대한 에러를 무시하는 방법 

대상 필드가 기본키로 지정되어 있는데 중복 레코드로 인하여 에러를 발생시 다음과 같이 처리할 수 있다. 

mysql> INSERT ignore INTO .....


출처 : http://ra2kstar.tistory.com/109


신고

'DB' 카테고리의 다른 글

[펌]Mysql설계시 고려사항  (1) 2014.11.20
MYSQL 테이블 복사  (0) 2014.09.13
테이블복사  (1) 2011.11.23
MSSQL - ROW_NUMBER  (0) 2011.11.01
ERROR : Conversion failed when converting date and/or time from character string.  (0) 2011.04.19

테이블복사

DB 2011.11.23 21:00
외울대도 됏는데 맨날 헷갈려

* 테이블을 생성하면서 데이터도 같이 복사
Select * into [새로운 테이블명] From [기존 테이블명]

* 구조만 복사

Select * into [새로운 테이블명] From [기존 테이블명] Where 1=2

* 테이블에 있는 데이터만 복사

Inser into [복사될 테이블명] Select * From [기존 테이블명]

* 특정 데이터만 복사

Inser into [복사될 테이블명] Select * From [기존 테이블명] Where 조건

신고

'DB' 카테고리의 다른 글

[펌]Mysql설계시 고려사항  (1) 2014.11.20
MYSQL 테이블 복사  (0) 2014.09.13
테이블복사  (1) 2011.11.23
MSSQL - ROW_NUMBER  (0) 2011.11.01
ERROR : Conversion failed when converting date and/or time from character string.  (0) 2011.04.19

MSSQL - ROW_NUMBER

DB 2011.11.01 17:29
Create table Employee ( EmployeeID INT , EmployeeName Varchar(30))
ROWNUM using ROW_NUMBER
SELECT ROW_NUMBER () OVER (ORDER BY EmployeeID) AS RowNumber, EmployeeName
FROM Employee ORDER BY EmployeeID

ROWNUM using IDENTITY
SELECT IDENTITY(int, 100, 1) AS RowNumber , EmployeeName
INTO #tmp
FROM Employee ORDER BY EmployeeID
SELET RowNumber , EmployeeID FROM #tmp

ROWNUM using NEWID
SELECT NEWID() ,AS RowNumber, EmployeeName FROM Employee
ORDER BY EmployeeID

Related article:
http://samsudeenb.blogspot.com/2009/06/how-to-generate-sequence-number-in-sql.html



맨날헷갈려....
신고

'DB' 카테고리의 다른 글

[펌]Mysql설계시 고려사항  (1) 2014.11.20
MYSQL 테이블 복사  (0) 2014.09.13
테이블복사  (1) 2011.11.23
MSSQL - ROW_NUMBER  (0) 2011.11.01
ERROR : Conversion failed when converting date and/or time from character string.  (0) 2011.04.19

ERROR : Conversion failed when converting date and/or time from character string.

DB 2011.04.19 13:07
http://msdn.microsoft.com/ko-kr/library/ms187928.aspx

변환하자 스트링으로.. CONVERT함수 쓰긔

신고

'DB' 카테고리의 다른 글

[펌]Mysql설계시 고려사항  (1) 2014.11.20
MYSQL 테이블 복사  (0) 2014.09.13
테이블복사  (1) 2011.11.23
MSSQL - ROW_NUMBER  (0) 2011.11.01
ERROR : Conversion failed when converting date and/or time from character string.  (0) 2011.04.19


티스토리 툴바