Category Archives: Mysql

우분투에서 mysql 사용하기

설치하기

mysql 서버와 클라이언트를 설치한다

설치하는 중 root 사용자 패스워드를 입력하라는 화면이 나오면 사용할 root 패스워드를 입력한다.

mysql 설정파일(/etc/mysql/my.cnf)에서 bind-address 설정부분을 주석처리한다.

데이터베이스 생성하기

create 명령어를 사용해서 데이터베이스를 생성한다. 여기에서는 springbook이라는 이름으로 데이터베이스를 생성한다.

데이터베이스에 접속하기

앞에서 생성한 데이터베이스에 접속한다.

현재 만들어진 테이블을 살펴본다. 아직 테이블을 만드지 않았으므로 아무런 테이블도 표시되지 않는다.

캐릭터 셋(Character Set) 설정하기

테이블을 생성하기에 앞서 클라이언트, 서버, 데이터베이스 등의 캐릭터 셋을 설정해야 한다.

지원되는 캐릭터 셋 확인하기

현재 머신에 설치된 mysql에서 사용가능한 Charater Set을 확인한다.

현재 설정된 캐릭터 셋 확인하기

현재 설치된 캐릭터 셋을 확인한다.

mysql_charaterset

결과에서 보는 바와 같이, client, connection, database, result, server의 캐릭터 셋이 기본적으로 latin1로 되어 있음을 확인할 수 있다.

utf8로 캐릭터 셋 변경하기

캐릭터 셋을 변경하는 방법에는 여러가지가 있으나, mysql 데몬이 재시작된 후에도 캐릭터 셋 정보를 그대로 유지하려면 설정파일(my.cnf)을 편집해야 한다.

아래와 같이 utf8로 된 부분을 [client], [mysqld], [mysql] 영역에 추가한다

mysql 서버 데몬을 재시작한다.

springbook 데이터베이스에 새로 접속해 보면, 아래와 같이 캐릭터 셋이 utf8로 모두 변경되었음을 확인할 수 있다.

mysql_charaterset_utf8

테이블 생성하기

create table 문을 사용해서 새로운 테이블을 생성한다.

show tables 명령을 실행해보면 users 테이블이 생성되었음을 확인할 수 있다.

사용자 추가하기

사용자는 GRANT 명령어를 사용해서 추가할 수 있다. 여기에서는 아이디가 “spring”이고, 패스워드가 “book”인 사용자를 추가하고, 동시에 springbook 데이터베이스에 대한 권한을 모두 주고자 한다.

데이터베이스를 mysql로 이동한다. mysql 데이터베이스는 사용자가 등록되는 데이터베이스다.

이제 user 테이블을 조회해보면 방금 등록한 spring 사용자가 등록되었음을 확인할 수 있다. root 게정도 함께 보이며, 패스워드는 암호화되어 저장되므로 조회 결과가 순식간에 스크롤된다.

참고자료

 

MySql 운영 관련 정보 정리하기

당분간 “MySQL(MySQL의 사용, 관리, 프로그래밍을 위한 완벽 가이드) / Paul Dubois 지음 / 김형훈 옮김 / 지앤선 출판사” 를 보면서 MySQL 운영 관련 정보를 정리해보고자 한다.

주제별 목차는 다음과 같다.

  • MySQL 저장 엔진
  • 서버 SQL 모드
  • MySQL 대소문자 구분
  • 쿼리 최적화
    • 인덱스 사용하기
    • 서버 관리자를 위한 최적화
  • MySSQL 데이터 디렉토리
    • 데이터 디렉토리의 위치
    • 데이터 디렉토리의 구조
  • MySQL 로그 관리
  • 서버환경변수 튜닝하기

MySQL 저장 엔진

MySQL은 여러 종류의 저장 엔진을 지원한다.

MySQL 저장 엔진 종류

  • ARCHIVE
    데이터 삽입 이후 레코드 변화 없음
  • BLACKHOLE
    쓰기는 무시하고, 읽기는 아무런 결과도 반환하지 않는 테이블 생성
  • CSV
    CSV 포맷으로 데이터 저장
  • EXAMPLE
    저장 엔진의 시작 방법에 대한 최소한의 예제를 제공
  • Falcon
    트랜잭션 엔진
  • FEDERATED
    원격 테이블 접속을 위한 엔진
  • InnoDB
    외부 키를 가지는 트랜잭션 엔진
  • MEMORY
    In-memory 테이블
  • MERGE
    MyISAM 테이블의 집합 관리
  • MyISAM
    기본 저장 엔진
  • NDB
    MySQL 클러스터용 엔진

사용중인 MySQL에서 지원하는 엔진에 대한 정보는 아래와 같이 확인할 수 있다.

5.1 이후 버전에서는 information_schema 데이터베이스의 engines라는 테이블에 동일한 정보가 존재한다.

이 중에서 가장 많이 활용되는 MyISAM과 InnoDB를 좀더 살펴 보자.

MyISAM

아래의 특성을 가진다.

  • 키 압축 기능 제공
    테이블을 생성할 떄 PACK_KEYS=1 옵션을 사용한다.
  • 유연한 시퀀스 사용
    MYISAM에서 자동으로 생성되는 시퀀스 값은 계속 증가되며, 재사용되지 않는다.
  • FULLTEXT 인덱스 지원
    MySQL FULLTEXT 검색 참조
  • 공간 데이터 타입과 SPATIAL 인덱스를 지원

InnoDB

  • 트랜잭션 지원
    커밋과 롤백 지원
  • 외부키와 참조 무결성 지원
  • 저수준 잠그과 다중 버저 지원
  • 공유 테이블
    테이블을 모두 공유 테이블 공간안에서 관리한다. 따라서 테이블은 운영체제가 개별 파일에 허용하는 최대 크기를 초과할 수 있다.

MyISAM vs. InnoDB

두가지 저장 엔진중 어느 엔진을 사용해야 하나? 정답은 상황에 따라 다르다. stackoverflow, MyISAM versus InnoDB 의 답변중에 간단한 정리는 다음과 같다.

기준 MyISAM InnoDB
FULLTEXT 지원 Yes
트랜잭션 지원 Yes
SELECT가 더 많이 수행되는 경우 Yes
INSERT, UPDATE, DELETE가 더 많이 수행되는 경우 Yes
Row Locking(단일 테이블에 대한 멀티 프로세싱) 지원 Yes
외부키와 참조 무결성 지원 Yes

[hr]

서버 SQL 모드

MySQL 서버는 다양한 SQL 모드에서 동작할 수 있다. SQL 모드는 sql_mode 시스템 변수를 설정해서 변경할 수 있다. SQL 모드는 어떤 종류의 SQL을 지원할지, 그리고 어떤 유형의 데이터 검증 작업을 이용할 지를 결정한다. 이러한 SQL 모드를 통해 MySQL을 다양한 환경에서 실행할 수 있을 뿐만 아니라, 다른 종류의 데이터베이스와 연동해서 사용할  수도 있다.
예를 들어 MySQL 에서 두 텍스트형 컬럼을 연결하기 위해서는 CONCAT()함수를 사용한다. 반면 Oracle에서는 || 연산자를 이용하여 텍스트형 컬럼을 연결할 수 있다. 만약 MySQL에서도 || 연산자를 텍스트형 컬럼을 연결하기 위해 사용하고자 한다면 PIPES_AS_CONCAT 모드를 적용하면 된다.
기본적으로 sql_mode는 빈 값을 가지며, MySQL 서버의 디폴트 형태로 적용된다. 현재 세션 또는 전역 SQL 모드를 확인하려면 아래의 명령어를 사용한다.

 

MySQL에서 지원하는 다양한 모드는 (한글) 5.2.5 서버 SQL 모드MySQL 문서 – 5.1.7. Server SQL Modes를 참고하라.

[hr]

MySQL 대소문자 구분

헷갈리는 대소문자 구분을 일단 정리하면 다음과 같다.

구분 대상
대소문자 구분함

데이터베이스, 테이블, 뷰 이름

* 운영체제에 따라 다름

대소문자 구분 없음 컬럼 이름, 인덱스 이름, alias 이름

MySQL은 내부적으로 서버 머신의 파일 시스템에 있는 디렉터리와 파일을 사용해서 데이터베이스와 테이블을 구현했다. 따라서 MySQL이 데이터베이스와 테이블에 대해 대소문자를 구분하는지 여부는 어떤 운영체제를 사용하느냐에 따라 다르다.
윈도우의 경우 파일 이름의 대소문자를 구별하지 않으므로, MySQL 서버를 윈도우 머신에 설치한 경우 데이터베이스와 테이블 이름에 대해 대소문자를 구분하지 않는다.
반면 UNIX 계열의 경우 파일 이름에 대해 대소문자를 구분하므로, 데이터베이스와 테이블 이름에 대해 대소문자를 구분하게 된다.
예외적으로 Mac OS X에 있는 HFS+ 파일 시스템은 대소문자 구별을 하지 않는다.

[hr]

쿼리 최적화

인덱스 사용하기

인덱스를 사용하면 WHERE 절을 사용한 SELECT문의 검색속도, JOIN 연산의 처리 속도를 높일 수 있다. 하지만 인덱스를 만들면 인덱스가 만들어진 컬럼에 값을 갱신(추가, 수정, 삭제)하는 경우 인덱스 또한 갱신해야 하므로 처리속도가 느려진다. 따라서 어느 컬럼에 인덱스를 사용할지 신중하게 선택해야 한다.

아래는 인덱스를 선정하는 기준을 정리했다.

인덱스 선정 기준

  • 검색, 정렬, 그룹화에 사용되는 컬럼에 대해서는 인덱스를 만들되, 출력 컬럼에 대해서는 인덱스를 만들 필요가 없다.
    정리하면 WHERE절, JOIN절, GROUP BY절, ORDER BY절에 나오는 컬럼이 인덱스 대상이 된다.
  • 유일성이 높은 컬럼에 대해 인덱스를 생성한다.
    분포도가 고른 경우, 테이블의 행을 쉽게 구분지을 수 있다. 반면 성별과 같이 “여자”, “남자”로 구성된 컬럼의 경우에는 인덱스크 별 효과가 없다. 실제로 테이블의 행에 30%이상 발생하는 컬럼에 대해서는 쿼리 옵티바이저가 테이블 full 스캔을 시도한다.
  • 가능한 짧은 길이의 인덱스를 생성한다. 이유는 다음과 같다.
    • 값이 작을수록 비교 연산이 더 빨라진다.
    • 인덱스가 작을수록 디스크 I/O가 줄어든다.
    • 인덱스가 작을수록, 인덱스 키 버퍼 안에 더 많은 인덱스를 유지할 수 있다.
  • 컬럼이 문자열인 경우 앞자리 문자열로만 유일성을 어느정도 보장할 수 있다면 전체 컬럼에 대해 인덱스를 생성하지 않는다.
    문약 문자열 컬럼이 100바이트이고, 첫 50바이트만으로 인덱스의 유일성을 어느정도 보장할 수 있다면 첫 50바이트만에 대해서만 인덱스를 생성한다. 앞서 이야기한 인덱스는 길이가 짧을수록 좋기 때문이다.
  • 결합 인덱스를 생성하는 경우 순서가 중요하다.
    먄약 state, city, zip에 대해 결합 인덱스를 생성한다면 반드시 state, city, zip 순서로 검색을 사용해야 한다. 이러한 조합에는 state 단독, state -> city, state -> city -> zip 순서가 포함된다. 만약 city -> zip으로만 검색한다면 인덱스가 사용되지 않는다.
  • slow 쿼리 로그를 통해 인덱스를 선정할 수 있다.
    slow 쿼리 로그에 특정 쿼리가 반복적으로 나타난다면, 해당 쿼리는 최적화해야할 대상이 된다.

서버 관리자를 위한 최적화

데이터베이스 서버를 관리할 때 성능을 위한 기본적인 원칙은 다음과 같다.

  1. 메모리에 있는 데이터를 접근하는 것이 디스크에 저장된 데이터를 접근하는 것보다 빠르다.
  2. 가능한 한 데이터를 메모리에 두게 되면 디스크를 사용하는 빈도가 줄어든다.
  3. 인덱스에서 정보를 유지하는 것이 데이터 행의 내용을 유지하는 것보다 더 중요하다

이러한 원칙을 따르는 가장 단순한 방법은 서버 캐시의 크기를 늘리는 것이다. 서버 캐시와 관련된 중요한 시스템 변수 3가지는 아래와 같다.

  • 테이블 캐시(table cache)
  • 인덱스 키 버퍼(index key buffer)
  • 쿼리 캐시(query cache)

 

테이블 캐시 최적화하기

서버가 테이블 파일을 접근하여 오픈한 경우, 나중에 파일을 재사용하기 위해 오픈된 상태로 유지할 수 있다. 테이블 캐시는 오픈된 테이블에 대한 정보를 유지하는데 사용한다. table_cache 변수를 사용해서 테이블 캐시 크기를 설정할 수 있다.

서버가 많은 테이블에 접근한다면 테이블 캐시는 금방 가득차고, 새로운 테이블에 접근해야할 때마다 접근 빈도가 낮은 테이블을 닫아서 새로운 공간을 마련해야 한다. 현재 열려 있는 테이블 수는 아래와 같이 확인할 수 있다.

또는 어드민 명령어로 상태를 확인해도 결괄르 확인할 수 있다.

 

만약 Open_tables 값이 변하지 않거나 느리게 변한다면 table_cache 값이 적절하게 설정되었다고 볼 수 있다. 반면 값이 빠르게 증가한다면, table_cache 값에 비해 오픈해야할 테이블이 많아서 계속해서 테이블을 닫고 여는 작업을 처리한다는 뜻이다.

인덱스 키 버퍼 최적화하기

MyISAM 엔진을 사용하는 경우, 인덱스 블록을 유지하기 위해 인덱스 키 버퍼를 사용한다. 인덱스 키 버퍼는 설정 파일에서 key_buffer_size 변수를 통해 설정할 수 있으며 기본 값은 8MB다.

쿼리 캐시 사용하기

MySQL은 반복적으로 실행되는 SELECT 문장을 캐싱하여 처리속도를 높인다. 동작 방식은 다음과 같다.

  • SELECT 문장이 처음 실행되는 경우, 쿼리문과 결과값을 쿼리 캐시에 저장한다.
  • 다음에 동일한 SELECT 문장을 수신한 경우, 쿼리를 실행하지 않고 결과값을 캐시에서 가져와서 반환한다.
  • 이때 SELECT 문장이 동일하다는 의미는, 쿼리문의 문자열이 완벽히 동일해야 한다는 뜻이다(대소문자까지 구분한다)
  • 테이블이 갱신도힌는 경우, 쿼리 캐시에 해당 테이블을 참조하는 쿼리문이 있다면 해당 쿼리문은 유효하지 않게 되면 캐시에서 삭제된다.

서버가 쿼리 캐시를 사용하지는 아래의 명령어를 통해 확인할 수 있다.

쿼리 캐시의 사이즈는 설정 파일에서 query_cach_size를 통해 설정할 수 있다.

지속적으로 변경되는 테이블에서 정보를 가져오는 쿼리의 경우 캐시를 사용하지 않으면 더 효과적일 수 있다. 만약 SELECT 문에서 쿼리 캐시를 사용하지 않으려면, SELECT SQL_NO_CACHE 문을 사용하면 된다.

[hr]

MySSQL 데이터 디렉토리

데이터 디렉토리의 위치

mysqld은 모든 정보를 데이터 디렉토리(data directory)에 저장한다. 데이터베이스에서 관리하는 데이터뿐만 아니라, 서버의 동작사항과 관련된 상태 및 로그 파일도 데이터 디렉토리에 저장한다.

데이터 디렉토리의 디폴트 경로

데이터 디렉토리의 디폴트 경로는 서버를 컴파일하는 시점에 정해진다. UNIX 환경의 경우 다음과 같다.

  • 소스를 컴파일하여 설치하는 경우
    /usr/local/mysql/var
  • 이진 파일로 설치하는 경우
    /usr/local/mysql/data
  • RPM 파일로 설치하는 경우
    /var/lib/mysql

데이터 디렉토리 경로는 환경변수에 유지된다.

데이터 디렉토리의 구조

MySQL 데이터 디렉토리는 트리 구조로 구성되어 계층적인 형태를 띈다.

  • 각 데이터베이스는 데이터 디렉토리에서 하나의 디렉토리를 가진다.
  • 데이터베이스 내의 각 테이블 뷰, 트리거는 데이터베이스 디렉토리 하위의 파일에 대응한다.

이 외에도 아래의 파일들도 위치한다.

  • PID 파일
  • 상태파일과 로그파일

예를 들어 데이터베이스로는 mysql, world, test가 있고, world 디렉토리에 City, Country, CountryLanguage 테이블이 있는 경우, 아래와 구조로 이루어진다.

mysql_directory_structure

[hr]

MySQL 로그 관리

MySQL에서는 5가지 유형의 로그를 생성한다.

  • 에러 로그(error log)
    서버 시작 및 종료 기록을 포함
    문제가 되는 쿼리에 대한 로그
  • 일반 쿼리 로그(general query log)
    클라이언트의 연결, 클라이언트로부터의 쿼리 등 다양한 이벤트가 기록
    서버로 수신되는 모든 쿼리를 기록
    서버의 활동상황(누가 연결하며, 무엇을 하고 있는지)을 감시하는데 유용
  • 느린 쿼리 로그(slow query log)
    말 그대로 수행시간이 긴 쿼리다. – “느림”의 기준은 /etc/my.cnf 설정 파일에 long_query_time에 초단위로 기록한다.

    3초 이상 수행되는 쿼리문은 느린 쿼리로 간주되어 로그 파일에 기록된다.
    주의할 점은 CPU 타임이 아니라, 실제 수행 시간으므로 사용자가 많은 경우에는 동일한 쿼리라도 느리게 수행될 수 있다.
    slow query 로그에 남은 쿼리문은 최적화해야할 대상이 된다.
  • 이진 로그(binary log)
    UPDATE, DELTE, INSERT, CREATE TABLE, GRANT 등의 명령문에 의해서 수정이 발생한 명령문을 기록
    서버가 비정상 종료할 경우, 백업 이후의 데이터를 복원하기 위해 사용
  • 릴레이 로그(relay log)
    서버가 레플리케이션 슬레이브(replication slave) 서버로 동작하는 경우, 마스터 서버로부터 수신한 데이터 수정 이벤트를 기록

로그는 mysqld을 실행할 때 기록할 로그 유형과 위치를 지정해야 한다.

[hr]

서버환경변수 튜닝하기

서버의 동작에 영향을 미치는 시스템 변수는 SHOW VARIABLES 명령문으로 확인할 수 있다.

실제 서버의 동작상태를 모니터링할 때는 SHOW STATUS 명령문으로 상태 변수를 확인할 수 있다.

환경 변수는 전역 레벨 또는 세션 레벨로 존재한다.

시스템 변수는

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

에서

상태 변수는

http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

에서 확인할 수 있다.

References