Program Note: mysql

레이블이 mysql인 게시물을 표시합니다. 모든 게시물 표시

MySql - 논리연산자

* SQL에서 모든 논리 연산자는 TRUE, FALSE 또는 NULL(UNKNOWN)로 계산된다.
MySQL에서는 1 (TRUE), 0 (FALSE) 그리고 NULL로 계산된다.

* NOT, !
논리 NOT. 피연산자가 0이면 1을, 피연산자가 0이 아니면 0을, NOT NULL 일 경우에는 NULL을 리턴.
사용예)
SELECT NOT 10; -> 0
SELECT NOT 0; -> 1
SELECT NOT NULL; -> NULL

* AND, &&
논리 AND. 모든 피연산자가 0이 아니고 NULL도 아니면 1을, 한 개 또는 그 이상의 피연산자가 0이라면 0을, 그렇
지 않을 경우에는 NULL을 리턴.
사용예)
SELECT 1 && 1; -> 1
SELECT 1 && 0; -> 0
SELECT 1 && NULL; -> NULL

* OR, ||
논리 OR. 양쪽의 피연산자가 NULL이 아닌 경우 양쪽의 피연산자가 0이 아니면 1을, 그렇지 않으면 0을 리턴.
NULL 피연산자를 사용하면, 다른 피연산자가 0이 아니면 1을, 그렇지 않으면 NULL을 리턴. 만일 양쪽의 피연산자가 모두 NULL이라면, 결과는 NULL.
사용예)
SELECT 1 || 1; -> 1
SELECT 1 || 0; -> 1
SELECT 0 || 0; -> 0

* XOR
논리 XOR. 피연산자 중의 하나가 NULL이면 NULL을 리턴. NULL이 아닌 피연산자의 경우, 피연산자의 홀수 개수가 0이 아니면 1을, 그렇지 않으면 0을 리턴.
사용예)
SELECT 1 XOR 1; -> 0
SELECT 1 XOR 0; -> 1
SELECT 1 XOR NULL; -> NULL

MySql - 연산자 우선순위

* 연산자 우선순위는 아래와 같으며, 낮은 것 -> 높은 것 순서.
동일한 라인은 같은 우선순위.
:=
||, OR, XOR
&&, AND
NOT
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (unary minus), ~ (unary bit inversion)
!
BINARY, COLLATE

* NOT에 대한 우선순위는 MySQL 5.0.2 이후에 존재.
이전 버전, 또는 HIGH_NOT_PRECEDENCE SQL 모드가 활성화 되어 있는 경우의 5.0.2 까지는, NOT의 우선 순위는 ! 연산자의 우선순위와 같다.

* 연산자의 우선순위는 수식에 있는 항의 계산 순서를 결정.
우선순위를 무시하고 그룹 항을 명확하게 지정하고자 한다면, 괄호를 사용
사용예)
SELECT 1+2*3; => 7
SELECT (1+2)*3; => 9

MySql - 리플리케이션이란

* MySQL은 단 방향, 즉 비동기 리플리케이션을 지원.
하나의 서버는 마스터로 동작하고, 나머지 한 개 이상의 다른 서버들은 슬레이브로 동작.

* 싱글-마스터 리플리케이션에서, 마스터 서버는 업데이트를 자신의 바이너리 로그 파일에 작성하고 로그 로테이션의 트레이스를 유지하기 위해 이 파일의 인덱스를 유지 관리한다. 바이너리 로그 파일은 다른 슬레이브 서버에 전달되는 업데이트 레코드 역할을 한다. 슬레이브가 자신의 마스터에 연결이 될 때, 마스터 정보를 자신이 마지막으로 업데이트가 성공했을 때 읽었던 로그에 전달한다. 슬레이브는 그 시간 이후에 발생한 모든 사항에 대한 업데이트를 전달 받고, 블록 (block)을 한 후에 마스터가 새로운 업데이트를 알려 주기를 기다리게 된다.

* 슬레이브 서버를 체인드 리플리케이션 서버로 설정하면, 자신이 마스터 역할을 하게 된다.

* 다중-마스터 리플리케이션은 가능하기는 하지만, 싱글-마스터 리플리케이션에서는 발생하지 않는 문제들이 나타나게 된다.

* 리플리케이션을 사용하는 경우, 복제된 테이블에 대한 모든 업데이트는 마스터 서버에서 실행되어야 한다. 그렇지않으면, 사용자가 마스터에 있는 테이블에서 행하는 업데이트와 슬레이브에서 행하는 업데이트간의 충돌을 피하도록 항상 주의해야만 한다.

* 리플리케이션은 견고성, 속도, 그리고 시스템 관리에 많은 혜택을 제공.
- 견고성은 마스터/슬레이브 설정을 가지고 증가된다. 마스터에서 문제가 발생하면, 백업 형태의 슬레이브로 전환할 수 있다.
- 마스터와 슬레이브 서버 간에 클라이언트 쿼리 처리를 분산함으로써 클라이언트에 대해 보다 개선된 응답 시간을 제공해 줄 수 있다. 슬레이브에 SELECT 쿼리를 전달해서 마스터의 쿼리 처리 업무를 줄여 줄 수 있다. 마스터와 슬레이브의 동기화가 끊어지지 않도록 하기 위해 데이터를 수정하는 명령문들은 여전히 마스터에 전달된다. 이러한 로드 밸런싱 전략은 업데이트 하지 않는 쿼리가 압도적으로 많은 경우에 효과적이다.
- 마스터의 방해 없이 슬레이브 서버를 사용해서 데이터베이스 백업을 실행할 수 있다. 마스터는 백업이 진행되는 동안에도 업데이트 프로세스를 지속할 수 있다.

MySql - 테이블 스캔 피하기

* EXPLAIN을 실행하면 MySQL이 쿼리를 해석하기 위해 테이블을 스캔할 때 type 컬럼에 ALL을 보여줌.
이것은 일반적으로 아래 조건 아래에서 발생.
- 테이블이 너무 작아서 키 룩업 (lookup)을 실행하는 것보다 테이블 스캔을 하는 것이 더 빠름. 일반적으로 10개 미만의 짧은 길이의 행을 가진 테이블이 여기에 해당.
- 인덱스된 컬럼에 대해서 사용할 수 있는 제약 사항이 ON 또는 WHERE 구문에 존재하지 않음.
- 인덱스된 컬럼을 상수 값과 비교할 수 있고, MySQL이 테이블 대부분을 커버하고 있는 상수를 계산해서 테이블 스캔이 빠르게 진행되도록 만드는 경우.
- 다른 컬럼을 통해서 낮은 기수 (cardinality)를 가지고 있는 (많은 열이 키 값과 매치가 됨) 행을 사용할 수 있는 경우, MySQL은 많은 키 룩업 (lookup)이 진행이 되고 이에 따라서 테이블 스캔이 보다 빠를 것이라고 가정.

* 작은 테이블의 경우에는 테이블 스캔이 적절할 수도 있을 것이나 대형 테이블의 경우 옵티마이저가 올바르지 않은 테이블 스캔을 선택하지 못하도록 하기 위해서 아래 기법을 사용.
- 스캔이 된 테이블에 대한 키 배포 업데이트 작업은 ANALYZE TABLE tbl_name를 사용.
- 주어진 인덱스를 사용하는 것 보다 테이블 스캔을 하는 것이 보다 비효율적이라고 MySQL에게 지시하기 위해서, 스캔이 된 테이블에 대해서 FORCE INDEX를 사용.
SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;
- 어떠한 키 스캔도 1,000개 이상의 키 검색이 발생하지 않는다고 가정하게끔 옵티마이저를 만들기 위해서, mysqld를 --max-seeks-for-key=1000 옵션과 함께 시작하거나 또는 SET max_seeks_for_key=1000를 사용.

MySql - 인덱스 병합 결합 접근 알고리즘

* 이 알고리즘에 대한 표준은 인덱스 병합 방식 교차 알고리즘과 유사.

* 이 알고리즘은 OR과 결합된 서로 다른 키에서 테이블의 WHERE 구문이 여러 개의 범위 조건으로 변환될 때 적용될 수 있으며, 각 조건은 다음 중에 하나가 된다.
- 아래 형태에서는, 인덱스가 정확히 N 개의 부분을 가짐. (즉, 모든 인덱스 부분 커버)
key_part1=const1 and key_part2=const2 ... and key_partN=constN
- InnoDB 테이블의 주요 키 (primary key)에 걸친 모든 범위 조건.
- 인덱스 병합 방식 교차 알고리즘을 적용할 수 있는 조건.

예)
SELECT * FROM t1
WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table
WHERE (key1=1 and key2=2) OR (key3='foo' and key4='bar') and key5=5;

MySql - 인덱스 병합 교차 접근 알고리즘

* 이 접근 알고리즘은 WHERE 구문이 AND와 결합된 서로 다른 키에 있는 여러 개의 범위 조건으로 변환될 때 사용될 수 있으며, 각각의 조건은 아래의 것 중에 하나가 된다.

아래의 형태에서는, 인덱스가 정확히 N 개의 부분을 가진다 (즉, 모든 인덱스 부분이 커버된다)
key_part1=const1 and key_part2=const2 ... and key_partN=constN
InnoDB 테이블의 주요 키 (primary key)에 걸친 모든 범위 조건.
예)
SELECT * FROM innodb_table WHERE prime_key < 10 and key_col1=20;
SELECT * FROM tbl_name WHERE (key1_part1=1 and key1_part2=2) and key2=2;
인덱스 병합 교차 알고리즘은 사용된 모든 인덱스에서 동시에 스캔을 실행하고, 병합된 인덱스 스캔으로부터 전달받는 열 시퀀스에 대해 교차를 만들어 낸다.

만일 사용된 인덱스가 쿼리에서 사용된 모든 컬럼을 커버한다면, 전체 테이블 열은 추출되지 않는다.
이 경우 EXPLAIN 결과는 Extra 필드 (field)에 있는 Using index를 가진다.
예)
SELECT COUNT(*) FROM t1 WHERE key1=1 and key2=1;
만일 사용된 인덱스가 쿼리에서 사용된 모든 컬럼을 커버하지 못한다면, 사용된 모든 키에 대한 범위 조건이 만족스러울 때에만 전체 열이 추출된다.

만일 병합된 조건 중의 하나가 InnoDB 테이블의 주요 키 (primary key)에 걸쳐지는 조건이라면, 이 조건은 열 추출용으로 사용되지는 않지만, 다른 조건을 사용해서 추출된 열을 걸러 낼 때 사용된다.

MySql - 성능 최적화 (글로벌 변수)

* innodb_buffer_pool_size, innodb_log_file_size, innodb_log_files_in_group,
innodb_flush_log_at_trx_commit, innodb_doublewrite, sync_binlog
정도가 성능에 직접적인 영향을 미침.

* innodb_buffer_pool_size
InnoDB에게 할당하는 버퍼 사이즈로 50~60%가 적당.
지나치게 많이 할당하면 Swap이 발생할 수 있음.

* innodb_log_file_size
트랜잭션 로그를 기록하는 파일 사이즈이며, 128MB ~ 256MB가 적당.

* innodb_log_files_in_group
트랜잭션 로그 파일 개수로 3개로 설정.

* innodb_flush_log_at_trx_commit
일반적으로 2로 설정.
0 : 초당 1회씩 트랜잭션 로그 파일(innodb_log_file)에 기록
1 : 트랜잭션 커밋 시 로그 파일과 데이터 파일에 기록
2 : 트랜잭션 커밋 시 로그 파일에만 기록, 매초 데이터 파일에 기록

* innodb_doublewrite
이중으로 쓰기 버퍼를 사용하는지 여부를 설정하는 변수.
활성화 시 innodb_doublewrite 공간에 기록 후 데이터 저장.
활성 권장.

* sync_binlog
트랜잭션 commit시 바이너리 로그에 기록할 것인지에 관한 설정.
비활성 권장

MySql - 문자열 합치기 (CONCAT)

* CONCAT 함수 사용

* CONCAT (문자열1, 문자열2, .....)
문자열1, 문자열2 ...을 합쳐서 반환.

ex)
SELECT CONCAT('abc', 'def'); => 'abcdef'
SELECT CONCAT('abc', 'def', 'ghi') => 'abcdefghi'

MySql - 랜덤으로 정렬하기 (RAND)

*** 설명 ***
RAND() 함수를 ORDER BY 절에 사용.

*** 사용예 ***
SELECT * FROM table_name ORDER BY RAND();

MySql - 문자열을 날짜로 변경하기 (STR_TO_DATE)

*** 설명 ***
STR_TO_DATE 함수 사용
문자열 형식의 값을 날짜형식의 값으로 변경
DATE_FORMAT함수의 반대 기능

*** 사용예 ***
SELECT STR_TO_DATE('20161006182238', '%Y%m%d%H%i%s');
-> 2016-10-06 18:22:38
SELECT STR_TO_DATE('2016-10-06 18:22:38', '%Y-%m-%d %H:%i:%s');
-> 2016-10-06 18:22:38
SELECT STR_TO_DATE('2016/10/06 18:22:38', '%Y/%m/%d %T');
-> 2016-10-06 18:22:38

*** 사용되는 표현식 ***

표현식   설명
%M    월(Janeary, December, ...)
%W   요일(Sunday, Monday, ...)
%D    월(1st, 2dn, 3rd, ...)
%Y    연도(1987, 2000, 2013)
%y   연도(87, 00, 13)
%X    연도(1987, 2000) %V와 같이 쓰임.
%x    연도(1987, 2000) %v와 같이 쓰임.
%a    요일(Sun, Tue, ...)
%d    일(00, 01, 02, ...)
%e   일(0, 1, 2, ...)
%c    월(1, 2, ..., 12)
%b    월(Jan, Dec, ...)
%j    몇번째 일(120, 365)
%H    시(00, 01, 02, 13, 24)
%h    시(01, 02, 12)
%i    분(00, 01, 30)
%r    "hh:mm:ss AM|PM"
%T    "hh:mm:ss"
%S   초
%s    초
%p   AM, PM
%w    요일(0, 1, 2) 0:일요일
%U    주(시작:일요일)
%u    주(시작:월요일)
%V    주(시작:일요일)
%v   주(시작:월요일)

MySql - INSERT ~ ON DUPLICATE KEY UPDATE 사용하기

*** 설명 ***
유니크 인덱스 혹은 PRIMARY KEY 값이 중복되어 인서트하지 못하는 경우 업데이트를 수행한다. 오라클의 MERGE INTO와 비슷한 기능.

*** 예제 ***
INSERT INTO TEST_TABLE (A, B, C)
VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE B = 4, C = 5;
=> A 컬럼이 PRIMARY KEY.
TEST_TABLE에 A 컬럼이 1 인 행이 없으면 INSERT INTO TEST_TABLE (A, B, C) VALUES (1, 2, 3) 이, 있으면 UPDATE B = 4, C = 5 이 실행된다.

MySql - 문자열 길이 (LENGTH)

*** 문자열 길이를 구하는 함수 ***
LENGTH           : 바이트 수
CHAR_LENGTH     : 글자 수
BIT_LENGTH tprk   : 비트(1바이트는 8비트) 수

*** 예제 ***
SELECT LENGTH('대한민국'), CHAR_LENGTH('대한민국'), BIT_LENGTH('대한민국');
=>             12                  4    96

SELECT LENGTH('English'), CHAR_LENGTH('English'), BIT_LENGTH('English');
=>              7                   7                      56

MySql - 시간차 계산하기

*** TIMESTAMPDIFF ***
두 DATETIME 간의 시간차이를 초/분/시/일 등의 단위로 반환.

ex) 초 단위
SELECT TIMESTAMPDIFF(SECOND, NOW(), DATE_ADD(NOW(), INTERVAL 1 DAY));
==> 86400

ex) 분 단위
SELECT TIMESTAMPDIFF(MINUTE, NOW(), DATE_ADD(NOW(), INTERVAL 1 DAY));
==> 1440

ex) 시 단위
SELECT TIMESTAMPDIFF(HOUR, NOW(), DATE_ADD(NOW(), INTERVAL 1 DAY));
==> 24

ex) 일 단위
SELECT TIMESTAMPDIFF(DAY, NOW(), DATE_ADD(NOW(), INTERVAL 1 DAY));
==> 1

MySql - 문자열 자르기 (SUBSTRING)

* SUBSTRING 혹은 SUBSTR 을 사용

* SUBSTRING(문자열, 시작인덱스), SUBSTR(문자열, 시작인덱스)
시작 인덱스(1부터)부터 끝까지 문자열을 잘라서 반환.
ex)
SELECT SUBSTRING('abcdef', 1); => 'abcdef'
SELECT SUBSTR('abcdef', 2); => 'bcdef'

* SUBSTRING(문자열, 시작인덱스, 길이), SUBSTR(문자열, 시작인덱스, 길이)
시작 인덱스(1부터)부터 길이만큼 문자열을 잘라서 반환.
ex)
SELECT SUBSTRING('abcdef', 1, 3); => 'abc'
SELECT SUBSTR('abcdef', 2, 3); => 'bcd'

MySql - IFNULL, ISNULL, IF 사용하기

*** IFNULL ***
IFNULL (VAL1, VAL2)
VAL1의 값이 null이 아니면 VAL1, null 이면 VAL2를 리턴한다.

ex)
SELECT IFNULL('VAL', 'N');   => VAL
SELECT IFNULL(NULL, 'N');   => N

*** ISNULL ***
ISNULL (VAL1)
VAL1의 값이 null이면 1(true), null이 아니면 0(false)를 리턴한다.

ex)
SELECT ISNULL('A');     => 0 (false)
SELECT ISNULL(NULL);   => 1 (true)

*** IF ***
IF (VAL1, VAL2, VAL3)
VAL1의 값이 true이면 VAL2, false이면 VAL3를 리턴한다.

ex)
SELECT IF(1, 'Y', 'N');   => Y
SELECT IF(0, 'Y', 'N');    => N
SELECT IF(TRUE, 'Y', 'N');    => Y
SELECT IF(FALSE, 'Y', 'N');    => N

MySql - 요일 구하기

*** DAYOFWEEK ***
요일을 숫자로 반환한다.
일요일 1 ~ 토요일 7

ex) SELECT DAYOFWEEK(NOW());
=> 6

*** WEEKDAY ***
요일을 숫자로 반환한다.
월요일 0 ~ 일요일 6

ex) SELECT WEEKDAY(NOW());
=> 4

MySql - date_add 사용하기

*** 설명 ***
date형식에 지정된 시간을 추가한다.

*** 사용예 ***
Syntax : DATE_ADD(date, INTERVAL expr type)

SELECT NOW(), DATE_ADD(NOW(),INTERVAL 1 MONTH)
=> 2016-04-12 08:51:03, 2016-05-12 08:51:03

SELECT NOW(), DATE_ADD(NOW(),INTERVAL -1 MONTH)
=> 2016-04-12 08:51:03, 2016-03-12 08:51:03

*** type 값 ***
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

MySql - 날짜를 문자열로 변경하기(DATE_FORMAT)

*** 설명 ***
DATE_FORMAT 함수 사용
날짜형식의 값을 문자열 형식의 값으로 변경
STR_TO_DATE 함수의 반대 기능

*** 사용예 ***
SELECT DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');
-> 20160401083246
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
-> 2016-04-01 08:35:45
SELECT DATE_FORMAT(NOW(),'%Y/%m/%d %T');
->2016/04/01 08:36:08

*** 사용되는 표현식 ***

표현식	설명
%M	월(Janeary, December, ...)
%W	요일(Sunday, Monday, ...)
%D	월(1st, 2dn, 3rd, ...)
%Y	연도(1987, 2000, 2013)
%y	연도(87, 00, 13)
%X	연도(1987, 2000) %V와 같이 쓰임.
%x	연도(1987, 2000) %v와 같이 쓰임.
%a	요일(Sun, Tue, ...)
%d	일(00, 01, 02, ...)
%e	일(0, 1, 2, ...)
%c	월(1, 2, ..., 12)
%b	월(Jan, Dec, ...)
%j	몇번째 일(120, 365)
%H	시(00, 01, 02, 13, 24)
%h	시(01, 02, 12)
%i	분(00, 01, 30)
%r	"hh:mm:ss AM\|PM"
%T	"hh:mm:ss"
%S	초
%s	초
%p	AM, PM
%w	요일(0, 1, 2) 0:일요일
%U	주(시작:일요일)
%u	주(시작:월요일)
%V	주(시작:일요일)
%v	주(시작:월요일)

MySql - limit 사용하기

*** 사용법1 ***
LIMIT 정수
쿼리 실행 결과의 갯수를 제한한다.

예제)
select * from TABLE_NAME limit 5;

*** 사용법2 ***
LIMIT 정수, 정수
쿼리 실행 결과를 지정된 인덱스부터 지정된 갯수만큼 조회한다.
첫번째 정수는 시작 인덱스를 지정. 0부터 시작.
두번째 정수는 결과 갯수를 지정.

예제)
select * from TABLE_NAME limit 0, 5;