오랫만에 자료 남깁니다.

MySQL 에서 날짜 계산하는 펑션을 소개 드립니다.

DATE_ADD 펑션을 사용하면 되구요.....

DATE_ADD(기준일, 기간) 형태로 사용하게 됩니다.  아래에 예시 쿼리를 남깁니다.

하루
SELECT COLUMN1,2.... FROM TABLE WHERE DATE_COLUMN BETWEEN DATE_ADD(NOW(),INTERVAL -1 DAY ) AND NOW();

일주일
SELECT COLUMN1,2.... FROM TABLE WHERE DATE_COLUMN BETWEEN DATE_ADD(NOW(),INTERVAL -1 WEEK ) AND NOW();

한달
SELECT COLUMN1,2.... FROM TABLE WHERE DATE_COLUMN BETWEEN DATE_ADD(NOW(),INTERVAL -1 MONTH ) AND NOW();

행복하세요!!

mysqldump 를 이용하여 전체 테이블을 백업한 뒤 특정 테이블만 복구하기 위한 방법을 안내해 드립니다.

단계적으로 볼때,  기본 mysql 명령으로는 특정 파일에 대한 복원이 힘들기 때문에,

dump 파일에서 복구해야 할 테이블 영역을 정규식을 이용하여 잘라낸 뒤 해당 테이블을 복구 하는 방법입니다.

1. Database 백업

2. 필요 테이블 내용 추출

3. 테이블 복원

행복하세요~

database 백업 및 복원 방법

DB 백업하기

DB 복원하기

생성된 테이블의 CREATE TABLE 구문 얻기

mysql 에서 이미 생성되어져 있는 테이블(table) 에 대한 create table 구문을 얻어내는 방법입니다.

구문 : SHOW CREATE TABLE tbl_name

샘플)

mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `s` char(60) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!!

커버링 인덱스(Covering Index)라는 내용인데, 대용량 데이터 처리 시 적절하게 커버링 인덱스를 활용하여 쿼리를 작성하면 성능을 상당 부분 높일 수 있습니다.

커버링 인덱스란?

커버링 인덱스란 원하는 데이터를 인덱스에서만 추출할 수 있는 인덱스를 의미합니다. B-Tree 스캔만으로 원하는 데이터를 가져올 수 있으며, 칼럼을 읽기 위해 굳이 데이터 블록을 보지 않아도 됩니다.

인덱스는 행 전체 크기보다 훨씬 작으며, 인덱스 값에 따라 정렬이 되기 때문에 Sequential Read 접근할 수 있기 때문에, 커버링 인덱스를 사용하면 결과적으로 쿼리 성능을 비약적으로 올릴 수 있습니다.

백문이 불여일견! 아래 테스트를 보시죠.

테이블 생성

먼저 다음과 같이 테이블을 생성합니다.

create table usertest (
 userno int(11) not null auto_increment,
 userid varchar(20) not null default '',
 nickname varchar(20) not null default '',
 .. 중략 ..
 chgdate varchar(15) not null default '',
 primary key (userno),
 key chgdate (chgdate)
) engine=innodb;

약 1,000만 건 데이터를 무작위로 넣고 몇가지 테스트를 해봅니다.

커버링 인덱스(SELECT)

select chgdate , userno
from usertest
limit 100000, 100

************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: index
possible_keys: NULL
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 9228802
        Extra: Using index
1 row in set (0.00 sec)

쿼리 실행 계획의 Extra 필드에 “Using Index” 결과를 볼 수 있는데, 이는 인덱스만으로 원하는 데이터 추출을 하였음을 알 수 있습니다.

이처럼 데이터 추출을 인덱스에서만 수행하는 것을 커버링 인덱스라고 합니다. 아시겠죠? ^^

그렇다면 일반 쿼리와 성능 테스트를 해볼까요?

커버링 인덱스(WHERE)

1) 일반 쿼리

select *
from usertest
where chgdate like '2010%'
limit 100000, 100

쿼리 수행 속도는 30.37초이며, 쿼리 실행 계획은 다음과 같습니다.

************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: range
possible_keys: CHGDATE
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 4352950
        Extra: Using where

Extra 항목에서 “Using where” 내용은, Range 검색 이후 데이터는 직접 데이터 필드에 접근하여 추출한 것으로 보면 됩니다.

2) 커버링 인덱스 쿼리

select a.*
from (
      select userno
      from usertest
      where chgdate like '2012%'
      limit 100000, 100
) b join usertest a on b.userno = a.userno

쿼리 수행 시간은 0.16초이며 실행 계획은 다음과 같습니다.

************* 1. row *************
           id: 1
  select_type: PRIMARY
        table:
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra:
************* 2. row *************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.userno
         rows: 1
        Extra:
************* 3. row *************
           id: 2
  select_type: DERIVED
        table: usertest
         type: range
possible_keys: CHGDATE
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 4352950
        Extra: Using where; Using index

Extra 에서 “Using Index”를 확인할 수 있습니다.

그렇다면 30초 넘게 수행되는 쿼리가 0.16초로 단축됐습니다. 왜 이렇게 큰 차이가 발생했을까요?

첫 번째 쿼리는 Where에서 부분 처리된 결과 셋을 Limit 구문에서 일정 범위를 추출하고, 추출된 값을 데이터 블록에 접근하여 원하는 필드를 가져오기 때문에 수행 속도가 느립니다.

두 번째 쿼리에서도 동일하게 Where에서 부분 처리된 결과 셋이 Limit 구문에서 일정 범위 추출되나, 정작 필요한 값은 테이블의 Primary Key인 userno 값입니다. InnoDB에서 모든 인덱스 Value에는 Primary Key를 값으로 가지기 때문에, 결과적으로 인덱스 접근만으로 원하는 데이터를 가져올 수 있게 됩니다. 최종적으로 조회할 데이터 추출을 위해서 데이터 블록에 접근하는 건 수는 서브 쿼리 안에 있는 결과 갯수, 즉 100건이기 때문에 첫 번째 쿼리 대비 월등하게 좋은 성능이 나온 것입니다.

커버링 인덱스(ORDER BY)

커버링 인덱스를 잘 사용하면 Full Scan 또한 방지할 수 있습니다. 대부분 RDBMS에는 테이블에 대한 통계 정보가 있고, 통계 정보를 활용해서 쿼리 실행을 최적화 합니다.

다음 재미있는 테스트 결과를 보여드리겠습니다. 전체 테이블에서 chgdate 역순으로 400000번째 데이터부터 10 건만 가져오는 쿼리입니다.

1) 일반 쿼리

select *
from usertest
order by chgdate
limit 400000, 100

************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9228802
        Extra: Using filesort
1 row in set (0.00 sec)

분명 인덱스가 있음에도, Full Scan 및 File Sorting이 발생합니다. 인덱스를 태웠을 때 인덱스 블록을 읽어들이면서 발생하는 비용보다 단순 Full Scan이 더 빠르다고 통계 정보로부터 판단했기 때문이죠. 인덱스도 데이터라는 것은 항상 기억하고 있어야 합니다^^

결과 시간은 책정 불가입니다. (안끝나요~!)

2) 커버링 인덱스 쿼리

위 결과와 다르게 커버링 인덱스는 조금 더 재미있는 결과를 보여줍니다.

select a.*
from (
      select userno
      from usertest
      order by chgdate
      limit 400000, 100
) b join usertest a on b.userno = a.userno

************* 1. row *************
           id: 1
  select_type: PRIMARY
        table:
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra:
************* 2. row *************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.userno
         rows: 1
        Extra:
************* 3. row *************
           id: 2
  select_type: DERIVED
        table: usertest
         type: index
possible_keys: NULL
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 400100
        Extra: Using index

File Sorting이 발생하지 않고 커버링 인덱스가 사용되었으며, 실행 시간 또한 0.24초로 빠르게 나왔습니다.^^

Conclusion

커버링 인덱스는 InnoDB와 같이 인덱스와 데이터 모두 메모리에 올라와 있는 경우에 유용하게 쓰일 수 있습니다. 물론 커버링 인덱스가 좋기는 하지만, 커버링 인덱스를 사용하기 위해 사용하지 않는 인덱스를 주구장창 만드는 것은 최대한 피해야 하겠죠^^

잊지마세요. 인덱스도 데이터라는 사실을..

데이터베이스 테이블 깨짐 현상 체크 및 복구

MySQL 에서는 테이블이 손상 되었는지 여부를 확인(check table)하고, 손상된 테이블이라면 테이블을 복구(repair table)하고, 속도 개선을 위해 테이블을 최적화(optimize table) 하는 기능들을 제공한다. 이 기능들에 대한 쿼리는 상당히 단순하다.

1. MySQL 특정 테이블 체크(check)

2. MySQL 특정 테이블 복구(repair)

3. MySQL 특정 테이블 최적화(optimize)

그리고 위와 같이 특정 테이블에 대해 체크, 복구, 최적화를 할 수 있으나, 서버 장애 등으로 인해 DB의 전체 상태를 점검하거나 전체를 최적화하고 싶을 때는 테이블 하나하나 확인을 하기 어렵다. 그럴 때 아래와 같이 특정 DB의 모든 테이블을 체크하고, 복구하고, 최적화 할 수 있다.

4. MySQL 특정 DB의 모든 테이블 체크 및 자동 복구

5. MySQL 특정 DB의 모든 테이블 최적화

쿼리에서 대소문자 비교하기

일반적으로 MySQL Where 조건에서 비교를 하면 대소문자 구분없이 값을 찾습니다.

이때 binary 펑션으로 감싸주면 대소문자 비교가 가능합니다~

member_table

이런테이블이있다고할때

이렇게 쿼리를 하면 id 가 SangMin, sangmin 두개의 데이터를 가져오는데용

이런 경우에는 위의 쿼리문을 아래와 같이 변경하여 보면..

binary 함수를 이용하면 대소문자를 구별해서 데이터를 가져옵니다

대소문자 구별이 필요한 곳에는 반드시 이용이 필요할 것입니다요~~

GRANT 문을 이용한 사용자 및 권한 관리

▶ 모든 권한 주기

mysql> grant all privileges on *.* to 사용자명@호스트
     -> identified by '비밀번호' with grant option;

▶ 특정 사용자에게 특정 DB의 모든 테이블에 모든 권한 부여하기

mysql> grant all privileges on DB명.* to 사용자명@호스트 identified by '비밀번호';

▶ 특정 사용자에게 특정 DB의 모든 테이블에 select, insert 권한 부여하기

mysql> grant select, insert on DB명.* to 사용자명@호스트 
     -> identified by '비밀번호';

▶ 특정 사용자에게 특정 DB의 특정 테이블의 정해준 컬럼에만 update 권한 부여하기

mysql> grant update(컬럼1,컬럼2,컬럼3) on DB명.테이블명 to 사용자명@호스트
     -> identified by '비밀번호';

▶ 모든 호스트에서 접속가능하고 select 권한만 갖는 사용자를 추가할 땐

mysql> grant select on DB명.* to 사용자명@% identified by '비밀번호';

▶ ip주소가 192.168.0.으로 시작하는 컴퓨터에서 접속가능한 사용자를 추가할 땐

mysql> grant all privileges on *.* to 사용자명@'192.168.0.%' identified by '비밀번호';

▶ 모든 권한 삭제

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 사용자명@호스트;

▶ usage (삭제하면 로그인조차 안됨) 권한 부여, 삭제

usage 권한 부여
mysql> GRANT USAGE ON *.* TO USER_NAME@HOST IDENTIFIED BY 'USER_NAME';
usage 권한 삭제
mysql> REVOKE USAGE ON *.* FROM USER_NAME@HOST;

▶ 권한 확인하기

mysql> SHOW GRANTS FOR 사용자명;

▶ 사용자 삭제 

mysql> drop user 사용자명@호스트;
▶ 변경사항 적용 
mysql> flush privileges;

MySQL Database 를 사용함에 있어서 사용자를 추가하는 예제

사용자를 추가하기 위해 user 테이블에 항목을 추가하는 방법을 사용할 수 있으나
여러가지 오류를 발생 시킬 수 있으므로 권하지 않습니다.

따라서 GRANT 문을 이용하여 사용자 계정을 추가하는 방법을 설명합니다.

GRANT 명령문은 사용자 계정에 권한을 부여하는 명령으로 특정 사용자에게 특정 데이터베이스에 대한 권한을 설정할 수 있습니다.

이제 test_user 라는 사용자를 만들고 이 사용자의 비밀번호를 '1234'로 설정하고 my_database에 대한 모든 권한을 부여하는 방법을 설명하겠습니다.

우선 root 권한으로 MySQL DB 에 접속 합니다.

mysql -u root -p

비밀번호를 묻는 프롬프트가 나오면 비밀번호를 입력한 후 접속합니다.


접속 후 my_database 라는 DB 를 만들어 봅니다.

create database my_database;

사용자 생성 및 권한을 부여합니다.

grant all privileges on my_database.* to test_user@localhost identified by '1234' with grant option;


상기 명령으로 생성된 유저는 localhost 에서의 접속만 허용하게 됩니다.
이 부분에 별도의 IP 나 hostname 을 주게되면 해당 IP 혹은 hostname 에서만 접속이 가능하게
됩니다.

만약 이부분을 '%' 로 지정하게 된다면, 모든 원격지에서의 접근이 가능해지게 됩니다.

MySQL 에서 auto_increment 컬럼에 대한 시작값 변경하기

MySQL 을 사용하다보면 auto_increment 로 정의된 컬럼에서 새로운 시작값을 주어야 하는 경우가

발생하고는 합니다.

예를들어 1000 개의 데이터가 들어있는 테이블에서 10번 이후의 모든 데이터를 삭제 한다고 한다면,

다음에 신규로 등록되는 increment 의 값은 11 이 아닌 1001  이 되게 됩니다.

이럴때,  데이터를 삭제한 후 자동증가값을 11 부터 시작하고자 한다면, 다음과 같은 명령으로

auto_increment 의 시작값을 바꾸어 주시면 됩니다.

alter table 테이블명 auto_increment=시작값;

이렇게 명령을 해주시면 끝입니다.