'SQL'에 해당되는 글 94건

MySQL 에서 테이블에 칼럼을 추가/삭제/수정하고자 할 경우 명령어다.

홈페이지 버전업을 하면서 기존 테이블에 필드를 추가해둔 것이 있어서 추가된 필드만 별도로 MySQL 상에서 미리 작업하면 편리할 거 같아서 테스트해보고 기록해둔다.

// 칼럼 추가

ALTER TABLE `테이블명` ADD `추가할 칼럼명` 칼럼 타입 AFTER `현재 칼럼명`;
ALTER TABLE `rb_bskrbbs_data` ADD `hidden1` TINYINT( 4 ) NOT NULL DEFAULT '0' AFTER `hidden`;
ALTER TABLE `rb_bskrbbs_data` ADD `category1` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `category`;


한번에 여러개 칼럼 추가할 때
ALTER TABLE `rb_bskrbbs_data`
ADD `category1` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `category` ,
ADD `category2` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `category1` ,
ADD `category3` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `category2`;

// 칼럼 삭제

ALTER TABLE `테이블명` DROP `칼럼명`;

ALTER TABLE `rb_bskrbbs_data` DROP `category1`;

한번에 여러개 칼럼 삭제할 때

ALTER TABLE `rb_bskrbbs_data`
  DROP `category1`,
  DROP `category2`,
  DROP `category3`;

// 컬럼명 변경 및 타입 변경
ALTER TABLE `테이블명` change `컬럼명` `변경할컬럼명` varchar(20);

ALTER TABLE `rb_bskrbbs_data` CHANGE `category1` `category1` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `rb_bskrbbs_data` CHANGE `category1` `category4` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `rb_bskrbbs_data` CHANGE `category4` `category1` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

// 컬럼 타입 수정
ALTER TABLE `테이블명` modify `컬럼명` varchar(14);

ALTER TABLE `rb_bskrbbs_data` CHANGE `category1` `category1` CHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `rb_bskrbbs_data` CHANGE `category1` `category1` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE `rb_bskrbbs_data` modify `category1` VARCHAR( 120 );
ALTER TABLE `rb_bskrbbs_data` modify `category1` CHAR( 100 );
ALTER TABLE `rb_bskrbbs_data` modify `category1` VARCHAR( 100 );

// 테이블명 수정
ALTER TABLE `테이블명` rename `변경할테이블명`;

// 테이블 삭제
DROP TABLE `테이블명`;

phpMyAdmin 을 이용하면 위 내용을 알 필요가 없이 처리할 수가 있다.

UNION 은 서로 다른 테이블에서 같은 유형의 결과를 얻고 싶을 때 사용한다.

물론 같은 테이블을 UNION 으로 해서 결과를 얻어낼 수도 있다.

테이블 A 와 테이블 B 가 있는데, 테이블 A의 칼럼명과 테이블 B의 칼럼명이 서로 달라도 칼럼의 데이터형식이 호환되면 UNION 으로 결과를 얻어낼 수가 있다.

물론, 테이블 A 와 테이블 B의 칼럼수가 서로 달라도 상관없다.

하지만 화면에 출력될 칼럼 수는 동일하게 개수를 맞춰야 한다.

SELECT column_1, column_2 FROM 테이블 A WHERE 조건

UNION

SELECT column_1, column_2 FROM 테이블 B WHERE 조건;

SELECT column_1, column_2 FROM 테이블 A WHERE 조건

UNION ALL

SELECT column_1, column_2 FROM 테이블 B WHERE 조건;

UNION 과 UNION ALL 의 차이점은 조회결과에 중복을 포함하지 않느냐, 포함하느냐 이다.

중복을 포함해서 결과를 가져오라고 하면 속도는 당연히 더 빠르다.

하지만, 두 개의 테이블에서 E-Mail 을 추출해서 메일을 보낸다고 하면 중복이 발생하면 안되므로 UNION 을 사용해야 한다. UINO 으로 결합하면 중복을 제거한다.

UNION 으로 결합해서 나온 결과를 정렬을 한다면 어떻게 해야 할까?

SELECT column_1, column_2 FROM 테이블 A WHERE 조건

UNION

SELECT column_1, column_2 FROM 테이블 B WHERE 조건

ORDER BY column_1, column_2;

MySQL 에서 DB에 쌓이는 게시물의 가장 최근 10개의 글을 보여주는 쿼리문

SELECT 칼럼1, 칼럼2 FROM 테이블명 ORDER BY uid DESC LIMIT 10;

PHP 와 연동하여 LIMIT 다음에 나오는 숫자를 변수로 받으면 숫자만큼 최근 게시물을 화면에 뿌려줄 수 있다.

최근 게시물 10개를 랜덤하게 뿌려주고 싶을 때의 쿼리문

(SELECT 칼럼1, 칼럼2 FROM 테이블명 ORDER BY uid DESC LIMIT 10) order by rand();

VBA 에서는 Instr 함수는 셀에서 찾는 위치를 반환해주는 기능이라 유용하게 사용하고 있다.

MySQL 에서도 InStr 함수가 있어서 테스트를 해보니 VBA 에서 사용하는 InStr 함수와 똑같다.

<그림출처 : http://www.w3resource.com/mysql/string-functions/mysql-instr-function.php>

select cat1name, cat2name,findtext from catkeyword where instr('뭔가 냄새가 나',findtext) > 0;

VBA 와 연동하여 SELECT 문의 결과를 엑셀 셀에다가 뿌려줄 수 있다.

물론 MySQL 에 직접 접속하여 테스트를 해보고나서 엑셀 VBA 와 연동하여 테스트를 해봤다.

키워드를 MySQl 테이블에 계속 추가하고, 엑셀 VBA 에서 포함여부를 체크하는 루틴을 만들면 된다.

Application.StatusBar = "셀: " & C.Address(0, 0) & " / " & C.Text & " 진행중..."
sSQL = "SELECT cat1name, cat2name"
sSQL = sSQL & " FROM catkeyword"
sSQL = sSQL & " Where instr('" & C.Text & "',findtext) > 0 "

1. 중복된 갯수가 n 개 이상인

SELECT 필드명, count(*) as 변수명 FROM 테이블명 GROUP BY 필드명 HAVING 변수명 > n;

또는

SELECT 필드명, count(*)  FROM 테이블명 GROUP BY 필드명 HAVING count(*) > n;

// 중복된 값을 찾고자 하는 필드명을 지정한다. 

// 가령 mp3rand 라고 지정하면 GROUP BY mp3rand

칼럼에 INDEX 를 걸기 전과 건 후의 속도 차이를 보면 엄청난 차이가 발생한 걸 알 수 있다.

2. 중복된 데이터 추출

3. 중복된 것 모두 찾기

SELECT 필드명, count(*) FROM 테이블명 GROUP BY 필드명

영문, 한글자까지 문자의 정확한 길이를 알려면 CHAR_LENGTH() 함수를 사용한다.

SELECT eng, CHAR_LENGTH(eng) FROM data WHERE uid =번호;

어떤 정보를 조회, 수정, 삭제 하는 업무를 수행할 때, 하나의 테이블만 가지고 작업을 하는 경우는 거의 없다.

대부분 두개 이상의 테이블을 연결하여 원하는 데이터를 조회하거나 변경하고 삭제한다.

양쪽 테이블에 같은 조건이 존재할 경우의 값만 가져오는 Join 을 EQUI Join (등가 조인)이라고 한다.

DBMS가 먼저 접근하는 테이블을 드라이빙 테이블이라고 하는데, 드라이빙 테이블을 어떤 테이블로 하느냐에 따라 전체적인 조인 수행 속도에 영향을 끼칠 수 있다.

기본적인 구문은

SELECT column_name(s)
FROM tableA
INNER JOIN tableB
ON tableA.column_name = tableB.column_name;

w3schools.com 에 이렇게 나오는데 여기서 column_nams(s) 라는 말이 잘 와닿지 않을 수 있다.

아래 JOIN 구문을 보면 이해가 명확해진다.

테이블명을 칼럼마다 전부 적어주려면 구문이 길어지므로 별칭으로 간단하게 a, b 등을 사용한다.

SELECT 하는 column 이 테이블A 에 있는 column 인지 테이블B 에 있는 column 인지 명확하게 해주어야 한다.

칼럼(column)이 명확하게 구분되는 경우에는 안적어줘도 되지만, 명확하게 하는게 좋다. 테이블A, 테이블B가 다른 테이블이 아니라 같은 테이블을 조인하는 걸 Self JOIN 이라고 한다.

select a.no, b.mp3 from 테이블A a, 테이블B b where a.no = b.relateduid;

select a.no, b.mp3 from 테이블A a JOIN 테이블B b ON a.no = b.relateduid;   -- ANSI JOIN

위 2개의 JOIN Query 문은 동일하다.

테이블A 와 테이블B 사이의 콤마 대신에 JOIN, Where 대신에 ON 을 사용하게 다르다.

만약, 3개의 테이블을 JOIN 한다고 하면....

select a.no, b.mp3, c.membername from 테이블A a, 테이블B b, 테이블C c WHERE a.no = b.relateduid AND a.relateduid = c.memberid;

select a.no, b.mp3, c.membername from 테이블A a JOIN 테이블B b ON a.no = b.relateduid JOIN 테이블C c ON a.relateduid = c.memberid;

테이블A 의 특정 칼럼의 데이터를 업데이트 해야 하는 경우

다른 테이블(테이블 B)의 데이터를 바탕으로 테이블 A의 특정 칼럼을 업데이트 해야 하는 경우가 있다.

UPDATE 테이블명A a JOIN 테이블명B b
ON a.조인할 컬럼명 = b.조인할 컬럼명
SET 변경할 컬럼명 = 변경할값
( WHERE 절 )

의 쿼리문으로 해결할 수 있다.

약  4만건의 데이터를 JOIN Update 를 했더니 0.92초 걸렸다.

테이블 칼럼에 중복되는 자료가 존재하면 안되기 때문에 테이블 설계시에 중복발생이 되지 않도록 인덱스를 걸어두었다.

이미 등록한 자료를 다시 등록을 시도해봤다.

load data local infile '/home/httpd/test/test.csv'
into table `rand_mp3file`
fields terminated by ';'  -- 칼럼을 구분할 구분자를 지정
lines terminated by '\r\n'   -- Row(행)을 구분해줄 구분자를 지정
ignore 1 lines     -- 등록할 파일의 첫줄을 무시하고 등록하라
 (`relatedno` ,
  `mp3file`  ,
  `mp3rand`
);

총 5개의 레코드인데 전부 Skipped 된 걸로 나온다.

즉 등록된 자료는 하나도 없는 것으로 나온다.

UNIQUE INDEX 를 추가하고 업데이트 방식을 해보고자 한다.

중복된 자료가 있을 경우와 중복되지 않은 자료가 있을 경우 어떤 현상이 생기는지 궁금해졌다.

mysql > show index from 테이블명;

첫번째 데이터 등록은 잘 등록되었는데, 추가 자료를 등록할 때는 어떻게 등록해야 UNIQUE 하게 자료가 잘 등록될까?

등록하기 전에 이 자료가 중복이 발생되는 자료인지 중복검사를 하는 방법을 알아봐야겠다.

물론 엑셀 VBA 와 MySQL 을 연결해서 검사를 하면 중복 여부는 확인이 가능하다.

문제는 속도가 너무 느리다는 단점이 있다.

하나의 데이터를 검색할 때마다 테이블의 자료 갯수만큼 반복해야 하니까~~

NULL 은 INDEX 가 만들어지지 않는다. 따라서 NULL 이 많은 항목은 INDEX 로는 부적합하다.

INDEX 와 JOIN 에 대한 이해만 정확하게 해도 최적화된 SQL 을 작성하는데 큰 도움이 된다.

INDEX란, 데이터의 고유성을 확인하거나 검색 성능을 향상시키기 위해 사용하는 객체이다. 특정 칼럼(column)에 INDEX를 생성하면, 해당 칼럼의 데이터들을 정렬하여 별도의 공간에 데이터의 물리적 주소와 함께 저장한다.

INDEX를 생성할 때 수행하는 정렬 작업은 이후 해당 데이터를 빠르게 검색하는데 중요한 역할을 한다.

INDEX 설계는 이론적으로 설계 단계에서 완성해야 한다. 프로젝트 운영중에도 INDEX 설계를 해야 하는 경우는 자주 발생한다. 요구사항 변경으로 설계 단계에서 예측하지 못했던 INDEX 가 필요해질 수 있기 때문이다.

INDEX의 가장 중요한 기능은 데이터의 조회 속도를 빠르게 하는 것이다.

기본적으로 조건절(WHERE) 에 등장하는 칼럼과 조인 조건으로 사용되는 칼럼에는 INDEX를 만드는 것이 성능면에서 유리하다.

조인 조건으로 사용된 칼럼에 INDEX 가 없으면 FULL TABLE SCAN 이 발생하면서 성능이 저하된다.

스칼라 서브쿼리 내에서 사용하는 조인 조건 칼럼에도 INDEX 를 생성해야 한다.

조회는 거의 하지 않고 데이터의 입력 및 변경 작업을 주로 하는 테이블은 최소한의 INDEX를 생성하는 것이 좋다.

ORDER BY 절에 제시되는 칼럼에도 INDEX를 생성하면 정렬 부하를 줄일 수 있지만, 조건절에 자주 등장하는 칼럼과 적절하게 조합하여 INDEX 를 만들면 훨씬 큰 성능을 기대할 수 있다.

UNIQUE INDEX 란 인덱스 안에 있는 Key 값에 중복되는 데이터가 없다는 뜻이다. UINQUE INDEX 가 설정되어 있다는 뜻은 해당 테이블의 칼럼에 중복된 값이 없다는 뜻이고, 앞으로도 중복된 값이 들어올 수 없다는 뜻이다.

mysql > show index from 테이블명;  -- 테이블에 생성된 인덱스 조회

mysql > alter table 테이블명 ADD UNIQUE INDEX (칼럼명);

(칼럼1, 칼럼2) 등 다중으로 할 수도 있다.

mysql > alter table 테이블명 ADD INDEX (칼럼명);

mysql > alter table 테이블명 DROP INDEX 칼럼명;

INDEX 를 추가/삭제를 한 테이블을 내보내기를 해서 보자.

CREATE TABLE IF NOT EXISTS `rand_mp3file` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `relatedno` int(11) NOT NULL DEFAULT '0',
  `mp3file` varchar(12) NOT NULL,
  `mp3rand` varchar(30) NOT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `display` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `mp3rand` (`mp3rand`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

처음 테이블 생성시에 INDEX 추가 명령어를 몰라도 추가하고 난 다음에 테이블을 내보내기해서 보면 인덱스가 어떻게 설정되어 있는지 확인할 수 있다.

phpmyadmin 상에서는 더 쉽게 인덱스를 추가할 수 있다.

그런데 간혹 동작이 안되는 경우가 있다. MySQL 에서 직접 해보면 에러가 발생해서 안되는 것이었다.

에러 메시지가 어떤 것인지 확인이 안되다보니...

인덱스(INDEX)를 사용하지 못하는 경우

ㅇ 칼럼의 내외부 변형

ㅇ LIKE 검색시 앞에 %를 사용

ㅇ 여러 칼럼에 대한 OR 조건 사용 : OR 를 사용하여 여러 칼럼에 대한 조건을 나열할 때,

     INDEX를 포함하지 않는 칼럼이 있다면, FULL TABLE SCAN 을 한다.

     OR 로 연결된 조건을 비교하는 작업을 수행할 때는 OR 뒤부터 비교를 시작하므로,

     결과를 만족하는 값이 많은 조건을 뒤에 두는 것이 성능면에서 유리하다.

ㅇ 부정형(<>, !=, NOT IN, NOT EXISTS) 비교, IS NULL, IS NOT NULL 을 사용한 비교

MySQL 의 날짜, 시간함수에 대해 알아보자.

from dual 의 dual 은 실제 테이블이 아니라, MySQL 에서 기본 제공한다. MySQL 이 제공하는 기본 함수 이해는 직접 명령어를 입력해보는 것이다.

1. 현재 날짜

select curdate() from dual;

2. 현재 시간

select curtime() from dual;

3. now()

select now() from dual;

4. 날짜 형식 변환

select DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') from dual;

* Y : 4자리 연동, y : 2자리 연도, m : 2자리 월

   H : 24시간 형식, h : 12시간 형식, i : 2자리 분, s : 2자리 초

select date_format(now(),'%Y-%m-%d') from dual;

DB에 있는 특정 필드의 값을 표현한다고 할때를 가정하고 직접 날짜를 입력했다.

5. 날짜의 요일을 숫자로 반환하는 dayofweek

요일의 시작은 일요일 1, 월요일 2, 화요일 3, 수요일 4, 목요일 5, 금요일 6, 토요일이 7이다.

select dayofweek(sysdate()) from dual;

* sysdate()는 이 함수가 실행되는 서버 시간을 가져오고, 다른 함수들은 쿼리가 실행되는 시간을 가져온다.

select dayofweek('20140527') from dual;

6. 날짜의 요일을 숫자로 반환하는 Weekday

월요일 0, 화요일 1, 수요일 2, 목요일 3, 금요일 4, 토요일 5, 일요일 6을 반환

select weekday(sysdate()) from dual;

select dayofyear(sysdate()) from dual;

8. 1월 1일부터 해당날짜까지의 주수를 반환

 select week(sysdate(), 0) from dual;

9. 특정한 날짜 기준으로 이전/이후 날짜를 계산

날짜를 계산하기 위해서 보통 date_add 함수를 사용한다.

select now(), DATE_ADD(now(), interval 3 month) from dual; -- 지금날짜와 3개월 후의 날짜를 출력

select date_add(sysdate(), interval -10 day) from dual; -- 10일전의 날짜

select date_sub(sysdate(), interval 1 day) from dual;

 select date_add(sysdate(), interval 1 month) from dual;

* interval 숫자 Type → second(초), minute(분), hour(시간), day(일), week(주), month(월), year(년)

select * from tableName
where (date between 시작날짜 AND 마지막날짜)
AND IF(date=시작날짜, time>=시작시간, IF(date=마지막날짜, time<=마지막시간, 1))";

인터넷을 검색해보면 Optimize는 너무 자주해도 안좋고 적당한 선에서 하면 좋다고 되어 있다.

MySQL DB 타입이 innoDB 인 경우에는 Optimize(최적화)를 안해도 된다고 한다.

innoDB는 락에 대해서 다양하게 관리를 해주기때문에 (행단위) 데이타 변경에 강하고

MyISAM 은 읽기위주의 솔루션에서 강점을 발휘한다.

// MySQL 특정 DB의 모든 테이블 최적화
mysqlcheck -u root -p --optimize DB명;

mysql의 replace() 함수는 문자열 치환 함수이다.
사용방법은 오라클의 regexp_replace와 동일하다.

ㅇ 치환할 데이터 확인하기

select replace(컬럼명, '원래 문자열', '바꿀 문자열') from 테이블명;

ㅇ 데이터 치환하기

update 테이블명 SET 칼럼명 = replace(칼럼명, '원래 문자열','바꿀 문자열') WHERE 조건;

MySQL 테이블의 Type을 잘못 지정해서 에러가 발생한 원인을 찾느라고 개고생을 했다.

PHP 프로그램으로 구조변경을 하면서 새로 코딩을 하고, 테스트를 하는데 엉뚱한 결과가 계속 나왔다.

PHP 코드를 계속 살펴보고, 잘못된 것이 없는데 왜 그럴까 하면서

테이블 데이터를 Truncate 로 날려보기를 수십번 ...

이 방법 저 방법을 사용하면서 코드 에러 나는 부분을 찾아보고자

echo "<script>alert('$content')</script>";

에러가 발생하는 부분에 자바스크립트로 찍어가면서 확인을 해봤다.

팝업창에 나온 숫자는 분명이 280 이다.

그런데 MySQL DB 테이블에는 숫자가 127로 찍힌다.

순간 불현듯이 떠오른건 테이블의 타입을 tinyint(4) 로 선언하게 기억났다.

tinyint 는 - 128 ~ 127 사이의 값을 DB 테이블에 저장한다.

숫자가 280 인데 DB가 받아들일 수 있는 최대 숫자가 127 이다보니 자동으로 숫자를 변환해서 127로 계속해서 기록을 했던 것이다. ㅠㅠㅠㅠ

이것 때문에 2시간 이상을 헛고생을 했다. 맥이 빠진다. 해결해야 할 사항이 아직도 많은데....

프로그램의 로직에는 아무런 문제가 없는데, PHP 문법을 잘모르거나 자바스크립트를 잘 몰라서 헤매는 경우가 너무 많다.

그렇다고 집중해서 코딩을 배울 나이도 아니고... 흐미

// 전화번호 마스킹처리(**) 하여 보여주기
// REPLACE(바꿀려는 필드명, '수정전 문자열', '수정후 문자열')

use 접속할DB명;
select uid, replace(tel,substring(tel,5,2),'**') from data limit 10;

select uid, replace(tel,right(tel,2),'**') from data limit 10;

select uid, tel, substring(tel,1,3),replace(tel,right(tel,2),'**') from data where tel LIKE '01%';

오라클 Decode 함수는 자주 사용하는 함수이므로 익혀두면 개발하면서 유용하게 사용할 수 있다.

decode 함수는 MySQL 이나 MS-SQL 에서는 사용할 수 없다.

하나의 값만 비교하는 경우

  decode(값, 조건, true return, false return)

여러 값과 비교하는 경우

  decode(값, 조건1, true return1, 조건2, true return2, false return)

오라클에는 left, right 가 없다. 오직 substr 로 해결해야 한다.

테이블에 정의되지 않은 칼럼, 1월 ~ 12월은 쿼리상에서 만들어낸 데이터이다. 이를 보통 Entity 재정의라고 한다.

SELECT
상품,
sum(decode(substr(접수일자, 1, 6), '201401', 1, 0)) jan,
sum(decode(substr(접수일자, 1, 6), '201402', 1, 0)) feb,
sum(decode(substr(접수일자, 1, 6), '201403', 1, 0)) mar,
sum(decode(substr(접수일자, 1, 6), '201404', 1, 0)) apr,
sum(decode(substr(접수일자, 1, 6), '201405', 1, 0)) may,
sum(decode(substr(접수일자, 1, 6), '201406', 1, 0)) jun,
sum(decode(substr(접수일자, 1, 6), '201407', 1, 0)) jul,
sum(decode(substr(접수일자, 1, 6), '201408', 1, 0)) aug,
sum(decode(substr(접수일자, 1, 6), '201409', 1, 0)) sep,
sum(decode(substr(접수일자, 1, 6), '201410', 1, 0)) oct,
sum(decode(substr(접수일자, 1, 6), '201411', 1, 0)) nov,
sum(decode(substr(접수일자, 1, 6), '201412', 1, 0)) dec
FROM
(
 SELECT 접수번호, 접수일자, 접수시간, 처리일자, 처리시간, 상품, 유형, 접수채널, 접수자명, 처리자명
 FROM goods
)
where 유형 = '해제요청' AND 상담 LIKE '%해제%'
GROUP BY 상품;

http://www.gurubee.net/lecture/1028

에 DECODE 와 CASE 함수에 대해 설명하고 있다.

select goods, count(*) from data group by goods;

하나의 칼럼을 다중 IF 문으로 처리하여 숫자 대신 문자로 표시하는 방법이다.

select goods, if(goods = 0, "미가입",if(goods = 1, "휴대폰", if(goods = 2, "API", if(goods=6, "타사", if(goods = 8, "분배", "해지"))))) as "휴대폰", count(*) from data group by goods;

또다른 방법은 CASE WHEN 을 사용하는 것이다.

select goods, case goods when 0 then "미가입" when 1 then "휴대폰" when 2 then "API" when 6 then "타사" when 8 then "분배" ELSE "해지" END as "휴대폰", count(*)  from data group by goods;

둘다 결과는 동일하게 나온다.

CASE WHEN END 를 사용하는 것이 좀더 편하고 좋다.

조건에 따라 값을 지정해주는 Case 문

칼럼의 값이 숫자로 되어 있는 경우 이해도가 떨어지므로 이름으로 표기하는게 좋겠다는 생각이 들 수 있다. 이때 CASE WHEN END 함수를 이용하면 된다.

Case 문의 형식은 아래와 같이 2가지로 표시할 수 있다.

칼럼에 조건을 걸어서 출력하고 싶은 글자로 표시하는 방법과 칼럼 값을 출력하고 싶은 글자로 표시하는 방법

예제를 보면 이해가 빠를 것이므로 예문을 보자. 

SELECT CASE AAA WHEN '1' THEN a WHEN '2' THEN b ELSE c END FROM table_name;

설명 - AAA 칼럼의 값이 '1' 이면 a, '2' 이면 b, 둘다 아닐경우 c 를 출력

설명 : code별로 2015년 2월의 개수를 구하고자 한다. 아래처럼 조회(출력)하면 코드가 숫자로 정렬된다.

SELECT CODE,COUNT(*) AS "개수"

FROM table_name

WHERE regisdate = "201502" 

GROUP BY CODE;

설명 : 코드를 직접 값으로 지정하여 글자로 정렬되어 출력된다.

SELECT CASE CODE WHEN 1 THEN '사과' ELSE '포도' END, COUNT(*) AS "개수"

FROM table_name

WHERE regisdate = "201208"

GROUP BY CODE;

-- 조건 함수처리
SELECT empno, ename, case when sal >=0 and sal <= 1000 then 'E'
when sal > 1000 and sal <= 2000 then 'D'
when sal > 2000 and sal <= 3000 then 'C'
when sal > 3000 and sal <= 4000 then 'B'
when sal > 4000 and sal <= 5000 then 'A'
end 
FROM table_name;

조건안에 칼럼명이 들어간 경우에는 case 컬럼명 대신 case 만 적어도 인식된다.

(case when(is_finish = 0 and is_checking = 0) then role when is_checking = 1 then '완료' when is_checking = 2 then '수정' when is_checking = 3 then '검토중' else '대기' end) as is_checking

처럼 when 에 두가지 조건을 제시해서 결과 표시에는 특정 필드를 적어줄 수도 있다.

아니면 특정한 값을 계산해서 표시할 수도 있다.

오라클에서는 Decode 함수가 많이 사용된다.

-- decode 함수 : decode(A,B,'1') A가 B라면 1을 출력
select empno, ename, sal,
decode(sign(sal-4000),1,'A',
decode(sign(sal-3000),1,'B',
decode(sign(sal-2000),1,'C',
decode(sign(sal-1000),1,'D', 'E')
))) GRADE
from emp;

인덱스 설정법

인덱스와 조인(JOIN)에 대한 이해만 정확하게 하고 있으면 최적화된 SQL을 작성하는데 큰 도움이 된다.

인덱스(Index)란?

• 해당 데이터의 검색을 위해 전체 테이블을 읽지 않고 해당 row의 주소를 통해서 직접 접근할 경우에 사용
• 특정 칼럼에 Index를 생성하면, 해당 칼럼의 데이터들을 정렬하여 별도의 공간에 데이터의 물리적 주소와 함께 저장한다.
  
• NULL 은 Index 만들어지지 않는다. NULL이 많은 항목은 Index 로는 부적합하다.
• Index 의 가장 중요한 기능은 데이터의 조회(select) 속도를 빠르게 하는 것이다.
  Index가 없다면 특정한 값을 찾기 위해 모든 데이터를 다 뒤져야 한다.
• 테이블을 삭제하면 관련 인덱스는 자동으로 삭제된다.
• 인덱스를 사용하면 디스크의 I/O를 감소시킬 수 있다.
  

기본적으로 조건절에 등장하는 칼럼과 조인 조건으로 사용되는 컬럼에는 인덱스를 만드는 것이 성능 면에서 유리하다.

인덱스 설정

MySQL 인덱스 설정 예시

ALTER TABLE `SALES_DATA`
  ADD KEY `shop_agent_ym` (`shop_code`,`agent_code`,`YM`);

ref : 조인의 순서와 상관없이 사용되며, primary key나 unique 키 등의 제약조건이 없다. 인덱스의 종류와 관계없이 동등 조건으로 검색할 때는 ref 접근 방법이 사용된다.

WHERE 절은 "테이블내의 모든 행을 검색하는 대신 검색 조건을 지정하여 사용자가 원하는 행들만 검색하는 기능"이다.

WHERE 조건식은 단일 조건식과 복수 조건식이 있다.

연산의 우선순위는 비교연산자 (=, !=, <>, >, >=, <, <=) SQL연산자(BETWEEN, IN, LIKE, IS NULL), NOT, AND, OR 순이다.

검색조건을 설정할 때 LIKE '%검색어%' 를 사용해야 할 경우가 있는데 변수 앞에 '%' 를 사용하면 해당 칼럼에 Index를 설정했다 하더라도 FULL SCAN을 한다.

조건절에 자주 등장하는 칼럼이라고 판단하여 최적화된 인덱스를 생성했다 하더라도, Index를 사용할 수 없는 상황이 발생하는데 대표적인 경우가 칼럼의 내외부 변형, IS NULL, IS NOT NULL을 사용한 비교, 여러 칼럼에 대한 OR조건 사용, 부정형(NOT, <>, !=, NOT EXISTS) 비교, 그리고 위에 언급한 LIKE 검색시 변수 앞에 %를 사용하는 경우이다.

가령, 주소록 필드를 저장할 때 통으로 된 하나의 필드로 저장하는 경우에는 검색하고자 하는 값이 중간에 들어가게 되어 FULL SCAN을 할 수 밖에 없다. 이럴 때는 칼럼을 여러개로 분리하여 앞에 %를 사용하지 않도록 하고 AND 조건으로 검색을 하도록 한다.

select * from dept where substr(dname,1,3) = 'DEF' ; 에서 dname 칼럼에 번형을 가한 경우에는 dname 칼럼에 인덱스(Index)를 설정해 두었더라도 Index를 타지 않는다.

select * from dept where dname LIKE 'DEF%' ; 로 수정하면 Index를 탄다.

다음은 Oracle SQL Developer 에서 scott 계정에 접속하여 테스트한 결과이다.

먼저, sal 칼럼에 Index를 설정했다.

create index emp_sal_idx on emp (sal) ;  -- sal 칼럼에 인덱스 생성

인덱스(Index) 삭제 명령어는 drop index emp_sal_dix; -- 인덱스 삭제

select * from emp where sal * 12 < 10,000 ;

select * from emp where sal < 10,000 / 12 ;

WHERE 조건식 을 어떻게 사용하느냐에 따라 성능에 영향을 미칠 수 있다. 개발자들의 SQL 사용 능력의 개인차가 심하다. 동일한 실행결과를 추출하는 다양한 형태의 SQL 이 존재한다.

성능향상을 위해서는

• index를 타는지 항상 체크하라.
  index를 활용하지 않은 검색은 데이터가 많으면 많을수록 성능은 급격히 떨어진다
  하지만 성능을 최적화하기 위해 무조건 index를 생성하는 것은 아니다. 경우에 따라서는 테이블 전체를 검색하는 방법이 성능이 보장될 수도 있다.
  
• select 쿼리문 작성시 필요한 칼럼(column)만 명시하라. select * 을 사용하는 것을 피하라.
  사용하지 않는 데이터를 호출하는 것만으로도 이미 많은 부하가 생긴다. 특히 text 타입의 데이터를 호출하는 경우는 그 정도가 심해진다.
• where조건문의 왼쪽은 되도록 변형되지 않은 순수한 column만을 선언하라.
  where 조건을 AND 등으로 여러개 조건을 설정해야 할 경우라면 가장 왼쪽 조건에서 대부분의 데이터가 걸러지도록 칼럼을 선택하고 설정된 칼럼에 인덱스를 설정하는 것이 좋다.
  
• JOIN을 사용하는 경우 INNER JOIN을 되도록 사용하라.
  두개의 테이블을 JOIN 해서 사용할 때 JOIN 방법에 따라서 수행속도의 차이는 현저히 나타난다. 처리결과는 같지만 연결하기 위해 처리하는 일량에서 차이가 난다.
  
• 서브쿼리 사용시 불필요한 select 구문을 줄여라.
  

전반적인 성능향상을 꾀하려면 CPU, 메모리, I/O 의 밸런스를 맞추어야 한다. DB성능 튜닝은 이 세가지 자원에 걸리는 부하를 적절하게 분산시켜 비용대비 최적의 성능을 뽑아내는것이 핵심이다.

일반적으로 대용량 테이블이라 하더라도 인덱스가 제대로 작동하고 있다면 검색 자체가 성능저하를 가져 오지는 않는다. 다만, 복수의 JOIN과 같이 대량의 Nested Loop 가 발생한다면 엄청난 양의 I/O가 발생하고 이것이 RDB전체의 성능을 크게 저하시키는 원인이 되기도 한다.

HDD의 I/O 속도는 메모리에 비하여 많이 느리다. 10,000RPM HDD와 DDR3-2500 메모리의 성능 차이는 약 800배에 이른다. DBMS 같은 시스템에서는 효과적으로 버퍼(캐시)를 구현하여 I/O 발생을 최소화하도록 구현하고 있다. 그래서 히트(hit)율이 높을 때는, HDD에서 SSD로 교체해도 성능 향상 효과가 미미한 경우가 있다. 하지만 SSD를 사용하면 버퍼 크기를 작게 유지해도 되기 때문에 대용량의 메모리를 유지할 필요가 적어지기도 한다.

MYSQL Clone 의 성능을 다룬 자료가 있는데 다시 보면 도움이 될 거 같아서 링크를 걸어둔다. http://blog.embian.com/5

NULL 검색

where username is null;

MySQL 운영하면서 겪은 문제점과 해법들

원본 : http://vstarmanv.tistory.com/350

컨설팅 회사에 MySQL을 운영하면서 겪고 있는 문제들을 해소하고자 여러가지를 물어보았고 큰 도움을 얻었다.본격적인 컨설팅은 아니었기 때문에 돈은 지불하지 않고 도움을 받았다.

1. 데이터가 이천만건 정도 있는데 테이블의 스키마를 변경하려고 하면 많은 시간이 걸리는데 어떻게 해야하나?

• 페이스북이 개발한 툴킷을 이용한다. 온라인으로 스키마 변경하는데는 3가지 방법이 있는데 페이스북에서 개발한 방법이 제일 좋다.
• InnoDB 대신 TokuDB 를 사용할 수도 있다. 컬럼을 추가하거나 수정, 삭제, 인덱스 걸기 등의 스키마 변경시 InnoDB엔진이 몇시간이 걸릴 때 TokuDB 엔진은 몇초면 된다.

2. 데이터베이스 부하가 심하다. 쿼리 최적화 외에 성능 개선할 방법이 없을까?

• MySQL의 변형인 MariaDB를 추천한다. MySQL으로 운영하다가도 바이너리만 MariaDB 를 설치하면 된다. MySQL 상용버전에 들어있는 Thread Pool 같은 기능을 무료로 사용 가능하며, MySQL보다 더 많은 테스트가 이루어지고 테이블 변경시 변경 상황 등이 제공된다. Oracle MySQL 을 쓰면서 아쉬웠던 점이 해결된다.
• TokuDB 라는 것도 있다. InnoDB를 대신하여 사용 가능하며 쓰기 속도가 크게 향상되었다. SSD에서 Insert 는 InnoDB 대비 9배 빨라졌다. 단, 유료다. 그러나 연구용이나 스타트업은 싸게 해준다.

3. 읽기쪽 부하를 줄이기 위해 Slave를 추가해야하는데 지금까지는 잠시 서비스를 잠시 중단하고 복사하는 방법을 사용했는데 좋은 방법이 없을까?

• Xtrabackup 을 사용하면 서버가 돌아가는 중간에도 데이터를 백업할 수 있다.
• 증분 백업도 가능하다.
• Xtrabackup 의 단점은 데이터를 통째로 바이너리 형태로만 백업한다는 점이다.
• SQL 형태로 뽑아내고 싶다면 Mydumper 를 추천한다.
  

4. 몇천만건이 넘어가는 데이터를 쌓을 때 주의할 점은?

• 100만건이 넘어가면 꼭 파티셔닝을 고려해야 한다.
• 대체로 시간단위로 데이터를 물리적으로 분리해서 저장한다. 적절히만 사용해도 수십억건의 데이터 관리도 불가능하지 않다.
  

5. 검색 부하가 매우 심하다. 좋은 검색 엔진을 추천해달라. 

• MyISAM 스토리지 엔진은 Fulltext 검색에 부하가 심하다.
  
• 스핑크스를 추천한다.
• 추천 받아서 적용해보니, 위치검색, 한글 검색, 비 텍스트 검색 모두에서 매우 빠른 성능을 보여준다. 게다가 확장성이 좋아서 저가 서버에 분산 배치해두었는데 로드가 거의 없다. 기존에는 MyISAM Full-text Search 을 쓰다가 Groonga Full-text Search 를 사용했었는데 MyISAM 은 Table locking 때문에 정기적으로 크게 느려졌고, Groonga 라는 검색엔진은 버그도 많고 비 텍스트 검색에서 최악의 성능을 보여준다. 암튼 MySQL 서버 로드가 무지 무지 올라갔었다. 그런데 스핑크스를 도입하면서 전반적으로 빨라지고 위치검색도 덤으로 10배 빨라졌다. 매우 만족한다.

6. 마스터 서버가 다운된적이 있다. 이중화 하려면 어떻게 해야하나?

• MySQL MHA 를 추천한다.
• 기타 대안 : MySQL Cluster 혹은 MySQL-MMM 

7. 어떤 쿼리가 부하를 일으키는지 확인하는데 효과적인 방법 없을까? Slow Log 가 너무 많이 쌓여서 우선순위대로 정렬이 되면 좋겠다.

MONyog를 추천한다.

• 실시간으로 쿼리를 분석하고 느린 쿼리들을 취합하여 가장 느린 쿼리부터 개선할 수 있다. 
• 어떤 쿼리가 Lock 을 유발하는지 알 수 있다. 놀라운것은, 어떤 쿼리가 Lock에 영향을 받았는지 까지 알 수 있다. 환상적이다.
• Cache Miss 같은 것이 어떤곳에서 발생하는지를 보고 메모리를 늘려야 하는지 등을 결정할 수 있게 해준다.
• Connection Refuse 가 얼마나 일어나는지 확인가능하다.
• 어떤 테이블이 많이 접근되는지 알 수 있다. (가장 접근이 많은 테이블 부터 최적화를 할 수 있다)

[기타] 내가 생각하는 MySQL의 장점

온라인 스키마 변경, 고가용성 때문에 NoSQL을 고려한적이 많았으나 MySQL에서 해결 가능하다. NoSQL은 숙련된 인력을 구하기 힘들기 때문에 개발 비용이 더 올라갈 수 있고 데이터 분석에 SQL 만큼 좋은 인터페이스를 가진 것이 거의 없다. 빨리 치고 나가야하는 스타트업에게 MySQL은 좋은 선택인것 같다

DBMS는 컴퓨터에 저장된 대량의 데이터를 체계적으로 관리하고 사용자가 원하는 정보를 효과적으로 검색하기 위한 시스템 소프트웨어를 의미한다

DBMS에 의해 관리되는 데이터의 집합을 ‘데이터베이스’라고 한다.

데이터베이스(DB)에는 많은 테이블이 존재할 수 있다.

테이블 1개로 원하는 데이터를 모두 저장할 수도 있지만 보통은 테이블을 설계할 때 서로 연관되게 여러개의 테이블을 만든다.

테이블에 있는 데이터 검색은 SELECT 명령어를 사용한다.

데이터베이스(DB)에서 데이터를 검색하려면 SQL을 사용하여 데이터를 요청한다.

SQL문은 여러 줄에 걸쳐서 작성 가능하고 반드시 ;(세미콜론)으로 끝난다.

요청하는 문법은 SELECT / FROM / WHERE 로 되어 있다.

SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 조건

위 그림으로 설명하자면 칼럼1은 품목, 칼럼2는 단가, 칼럼3는 코드 라고 보면 된다.

테이블로부터 (FROM 테이블) 꺼내는(SELECT) 것이 무엇인가를 정해주는 거라고 이해하면 쉽다.

테이블은 데이타베이스의 기본적인 데이타 저장 단위 이다. 데이타베이스 테이블은 사용자가 접근 가능한 모든 데이타를 보유하며 레코드와 컬럼으로 구성 된다.

SELECT * FROM 테이블;   → *는 모든 칼럼을 의미한다. 즉 테이블의 모든 칼럼을 다 조회(출력)하라는 의미이다. 만약 데이터 갯수(record : 행)가 1만개 정도 된다고 하면, 모든 데이터를 모니터 상에 출력하는 것은 무의미할 것이다. 보여줄 데이터를 딱 10개만 보여주라는 의미로 한정하는 것은 LIMIT 10 을 붙이면 된다.

SELECT * FROM 테이블 LIMIT 10;

출력하고 싶은 조건을 지정할 때는 WHERE 를 사용해서 WHERE 품목 = '구두' 로 한정할 수 있다.

SELECT * FROM 테이블 WHERE 품목 = '구두';

SQL문은 대소문자를 구별하지 않는다. 즉 SELECT 라고 대문자로 써도 되고, select 라고 소문자로 써도 된다.

테이블내의 특정 칼럼 데이터를 보고자 한다면 SELECT 뒤에 해당 칼럼을 차례대로 기술하고 쉼표로 구분해서 여러개를 지정할 수 있다.

SELECT 품목, 단가 FROM 테이블명;

WHERE 조건을 주는 여러가지 사항을 잘 알아둘 필요가 있다.