본 포스팅은 '기억보단 기록'을 이라는 타이틀로 유명한 'jojoldu' 님의 인덱스관련 포스팅을 공부차원에서 단순 요약한 포스팅에 불과하므로,

보다 정확한 정보 습득을 원하시는 분들은 필히 아래 링크로 이동 하시길 바랍니다.

https://jojoldu.tistory.com/243

 

[인덱스 기준]

1. 카디널리티가 높은 --> 낮은 칼럼 순서로 인덱스를 잡아야 좋다.

카디널리티가 높다 : 중복수치가 낮다 ex) 주민등록번호

카디널리티가 낮다 : 중복수치가 높다 ex) 이름

 

2. 인덱스를 잡을 때 키의 길이는 짧을 수록 좋다.

InnoDB(MySQL)에서 디스크에 데이터를 저장하는 가장 기본단위를 페이지라 하며, 인덱스 역시 페이지 단위로 관리된다.

인덱스가 저장되는 페이지는 16KB 크기로 고정되어 있다.

키의 크기가 길 수록 페이지에 저장되는 인덱스의 양은 줄어들고,

페이지에 저장된 인덱스의 양이 줄어들 수록, 조회시 더 많은 페이지를 조회해야 한다.

 

 

[인덱스 사용한 조회]

1. 복수개의 칼럼으로 인덱스를 구성했을 때, 조건절 주의사항

인덱스를 회원그룹(group), 나이(age), 성별(gender)  과 같이 잡았을 때,

아래의 쿼리는 인덱스(age)를 타지 않음

select *
from user
where age = 18

반면, 아래의 쿼리는 인덱스(group)를 탄다.

select *
from user
where group='premium'

: 첫번째 인덱스 칼럼은 조회조건에 포함되어야만 한다.

 

2. 그 외의 주의사항

1) between, like, <, > 등의 범위 조건에 사용된 칼럼은 인덱스를 타지만, 그 뒤의 인덱스 칼럼들은 인덱스로 사용되지 않음.

아래 쿼리에서 gender는 인덱스로 사용되지 않는다.

* LIKE 의 경우 'str%' 좌측 일치 패턴만 인덱스가 사용된다.

select *
from user
where group = 'premium'
and age > 18
and gender = 'M'

2) =, IN 조건으로 사용된 칼럼 뒤의 인덱스 칼럼들은 인덱스로 사용된다.

(IN은 =을 여러번 실행 시킨 것이므로)

단, IN 조건 안에 서브쿼리를 넣게 되면 성능상 이슈가 발생. (서브쿼리 외부 먼저 실행 후 IN 구절은 체크조건으로 실행되므로)

 

3) AND 연산자는 ROW 수를 줄이는 역할을 하지만 OR 연산자는 비교해야할 ROW가 더 늘어나므로 풀 테이블 스캔이 발생할 확률이 높음. 

 

4) 인덱스로 사용된 컬럼은 컬럼값 그대로 사용해야 인덱스가 사용된다.

select *
from user
where group = 'premium'
and age+1 = 18

위 쿼리는 인덱스를 타지 못한다.

위 경우, age = 18-1 과 같이 수정해야 한다.

칼럼이 문자열인데 숫자로 조회시 타입이 달라 인덱스가 사용되지 않는다.

 

5) null의 경우 where ? is null 로 인덱스 사용이 가능

일반적인 DBMS 와 달리 Mysql 에선 null 값도 인덱스로 관리

 

 

[인덱스 사용한 조회시 조회 칼럼 순서]

조회조건에 포함되어 있는지가 중요할 뿐, 반드시 인덱스 순서와 조회 칼럼 순서를 지킬 필요는 없다.

(옵티마이저가 조회 칼럼 순서를 인덱스 칼럼 순서에 맞춰 재배열 하는 과정이 추가되긴한다)

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

[Mysql] 우분투 설치, 유저 생성 및 권한 부여  (4) 2020.01.18
JOIN 에서 WHERE 와 ON 의 차이, 그리고 OUTER JOIN  (4) 2019.11.14
SYSDATE vs NOW (SYSDATE 와 NOW 의 차이)  (0) 2019.11.02
Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21

[ON vs WHERE]

ON : JOIN 을 하기 전 필터링을 한다 (=ON 조건으로 필터링이 된 레코들간 JOIN이 이뤄진다)

WHERE : JOIN 을 한 후 필터링을 한다 (=JOIN을 한 결과에서 WHERE 조건절로 필터링이 이뤄진다)

 

[INNER JOIN 에서의 ON vs WHERE]

1. INNER JOIN + ON 조건절 + ON 조건절

1
2
3
4
5
SELECT *
FROM a
INNER JOIN b
ON a.key = b.key
AND a.key2 = b.key2
 cs

2. EQUI JOIN + WHERE 조건절

1
2
3
4
SELECT *
FROM a AS a , b AS b
WHERE a.key = b.key
AND a.key2 = b.key2
 cs

3. INNER JOIN + ON 조건절 + WHERE 조건절

1
2
3
4
5
SELECT *
FROM a
INNER JOIN b
ON a.key = b.key
WHERE a.key2 = b.key2
 cs

위 세 쿼리는 모두 같은 결과, 같은 퍼포먼스가 나온다.

즉, inner join 에서의 on 조건과 where 조건은 같다.

https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause

 

[그래도 EQUI JOIN 보단 INNER JOIN 을 써야 하는 이유]

1. 가독성

2. OUTER JOIN 으로 수정이 용이

 

[OUTER JOIN 에서의 ON vs WHERE]

1. OUTER JOIN 정리 (출처)

 

2. OUTER JOIN 에서의 ON vs WHERE

1) OUTER JOIN test 를 위한 샘플 준비.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 샘플 테이블 생성
create table table1 (
  col1 INT,
  col2 VARCHAR(10)
);
create table table2 (
  col1 INT,
  col2 VARCHAR(10)
);
commit;
 
-- 샘플 데이터 입력
insert into table1 (col1, col2) values (1, '하나');
insert into table1 (col1, col2) values (2, '둘');
insert into table1 (col1, col2) values (3, '셋');
 
insert into table2 (col1, col2) values (1, '일');
insert into table2 (col1, col2) values (2, '이');
commit;
 cs

2) 데이터 확인

table1 :

col1 col2
1 하나
2 둘
3 셋

table2 :

col1 col2
1 일
2 이

 

3) OUTER JOIN 에서의 ON

1
2
3
4
5
SELECT t1.col1, t1.col2, t2.col1, t2.col2
FROM   table1 t1
LEFT OUTER JOIN table2 t2
ON t1.col1 = t2.col1
AND t2.col2 = '일';
 cs

위 쿼리의 결과 :

t1.col1 t1.col2 t2.col1 t2.col2
1 하나 1 일
2 둘 null null
3 셋 null null

>> table1의 전체 row와 

     table2에서 col2 칼럼 값이 '일'인 로우만 뽑은 row 들을 OUTER JOIN.

>> on 조건은 join 을 하기 전에 필터링 되는 기준

 

4) OUTER JOIN 에서의 WHERE (위 쿼리의 ON 조건을 WHERE 에 그대로 추가)

1
2
3
4
5
select t1.col1, t1.col2, t2.col1, t2.col2
from  table1 t1
LEFT OUTER JOIN table2 t2
ON t1.col1 = t2.col1
where t2.col2 = '일';
 cs

위 쿼리의 결과

t1.col1 t1.col2 t2.col1 t2.col2
1 하나 1 일

>> table1의 전체 row와

     table2 전체 row 를 OUTER JOIN 한 후,

     col2 칼럼 값이 '일'인 로우만 뽑는다.

>> WHERE 조건은 JOIN을 한 이후에 필터링하는 기준

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

[Mysql] 우분투 설치, 유저 생성 및 권한 부여  (4) 2020.01.18
[MySQL] 인덱스(INDEX) 관련 정리  (0) 2020.01.11
SYSDATE vs NOW (SYSDATE 와 NOW 의 차이)  (0) 2019.11.02
Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21

SYSDATE() : SYSDATE() 함수가 호출된 시각

NOW() : NOW() 가 포함된 쿼리(구문(statement))가 호출된 시각

 

[Ex]

1
2
3
4
5
6
7
SELECT
        SYSDATE(), 
        SLEEP(3) AS 'sleep', 
        SYSDATE(),
        NOW(), 
        SLEEP(3) AS 'sleep', 
        NOW();
 cs

 

[결과]

 

[쿼리 한 줄씩 해석]

SELECT

SYSDATE() 함수 호출될 때의 시각 (6:52:28)

SLEEP(3)    3초간 Thread sleep (6:52:31)

SYSDATE() 함수 호출될 때의 시각 (6:52:31)

NOW()       쿼리(구문)가 실행될 때의 시각(6:52:28)

SLEEP(3)    3초간 Thread sleep (6:52:34)

NOW()      쿼리(구문)가 실행될 때의 시각(6:52:28)

 

※ SYSDATE와 NOW 는 각각 언제 사용해야 하는가?

- 실행결과에 따른 시각이 중요, 이를 저장해야하는 경우 NOW 사용

- 호출된 시각이 중요한 경우 SYSDATE 사용

ex) Spring Batch/Spring Quartz의 경우 Schedule, Job의 실행시각들이 메타테이블에 의해 관리가 되는데(Schedule, Job 관리가 inMemory 방식이 아닌 jdbc 방식인 경우), 쿼리가 끝난 시각이 아닌 호출시점의 시각이 중요하므로 SYSDATE를 사용하여 관리되고 있다.

 

참고 : https://database.guide/sysdate-vs-now-in-mysql-whats-the-difference/

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

[MySQL] 인덱스(INDEX) 관련 정리  (0) 2020.01.11
JOIN 에서 WHERE 와 ON 의 차이, 그리고 OUTER JOIN  (4) 2019.11.14
Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12

Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)

 

고객들이 열람할 가이드 정보가 있고,

가이드 정보의 카테고리를 N depth 로 분류(멀티 셀렉트박스 사용)하여 조회할 수 있게 하여 다음과 같은 화면을 만들어 보자.

<1: 테이블 설계>

1. '전체' 옵션의 유무

case 1 멀티 셀렉트박스에 '전체'라는 옵션이 없는 경우 :

마지막 셀렉트박스의 value(node_id)를 기준으로 가이드를 조회

case 2 멀티 셀렉트박스에 '전체'라는 옵션이 존재하는 경우 :

'전체'가 선택된 마지막 셀렉트박스 직전의 value(node_id)를 기준으로 모든 자손 node_id를 찾아 조회해야한다.

 

예를 들어,

case 1 ex) 고객지원 > 고객지원sub > 고객지원sub의sub 와 같이 마지막 select box에 특정 값이 선택된 경우 :

고객지원sub의sub 의 value(node_id) 를 기준으로 가이드 조회

case 2 ex) 고객지원 > 고객지원sub > 전체 와 같이 마지막 select box가 '전체'로 선택된 경우 :

고객지원sub을 부모 노드로 삼는 모든 자손 노드를 기준으로 가이드 조회

 

2. 동적 depth 여부

case 1 : depth 가 특정 값으로 고정인 경우, 셀렉트박스용 분류 코드테이블을 따로 두고 join 을 몇번 걸면 해결이 된다

case 2 : depth 가 동적(N)인 경우, join 이 N 번 되어야 하므로 단순한 join으로 해결 할 수 없다..

 

전체 옵션이 존재하고 depth 가 동적(N)인 경우를 전재하고 설계 및 개발해보자.

 

 

<2: 테이블 생성>

1. 메인테이블 생성 : 가이드 정보 테이블 생성

가이드 정보 테이블은 제목, 가이드의 노드 아이디, 상세 내용만 지니고 있다.

1
2
3
4
5
6
7
8
CREATE TABLE `guide_info` (
  `SEQ` int(11) NOT NULL AUTO_INCREMENT COMMENT '일련번호',
  `TITLE` varchar(100) NOT NULL COMMENT '제목',
  `GUIDE_ID` int(11) NOT NULL COMMENT '가이드 노드 아이디',
  `TEXT_ITEM_1` text COMMENT '가이드 내용',
  PRIMARY KEY (`SEQ`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='고객지원 가이드 게시판';
 
 cs

 

2. 서브테이블 생성 : 가이드 분류 정보를 따로 지니고 있는 노드 정보 테이블 생성

1
2
3
4
5
6
7
8
9
CREATE TABLE `node_info` ( 
  `NODE_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '노드아이디', 
  `PNODE_ID` int(11) NOT NULL COMMENT '부모노드아이디', 
  `NODE_PATH` varchar(200) DEFAULT NULL COMMENT '노드경로', 
  `NODE_NM` varchar(20) DEFAULT NULL COMMENT '노드명', 
  `DEPTH` int(11) DEFAULT '1' COMMENT '노드DEPTH', 
  `LEAF_YN` varchar(1) DEFAULT 'N' COMMENT '자식노드존재유무', 
  PRIMARY KEY (`NODE_ID`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='노드 정보'; 
 cs

 

생성된 테이블 :

NODE_ID(노드아이디)와 PNODE_ID(부모 노드 아이디)가 서로 참조하는 관계의 테이블 구조.

 

샘플 데이터 :

 

<3: 쿼리 작성>

필요한 쿼리 종류는 크게 두가지로 나눠볼 수 있다.

1. 셀렉트박스의 구성요소인 노드정보(node_info TABLE)를 조회하는 쿼리 : 멀티셀렉트박스를 그리기 위한 데이터를 뽑는 쿼리

2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록(guide_info TABLE)을 조회한 결과 : 조회 결과를 출력하기 위한 데이터를 뽑는 쿼리

 

3-1. 셀렉트 박스의 구성요소인 노드정보를 조회하는 쿼리 :

N번째(Ndepth) 셀렉트박스가 특정값으로 선택된 경우 N+1번째(N+1depth) 셀렉트박스 조회

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  node_id
      , pnode_id
      , node_nm
      , depth
      , leaf_yn
  FROM  node_info 
 WHERE  1=1
<choose>
    <when test='node_id neq null and node_id neq ""'> 
       AND  pnode_id = #{node_id}
    </when>
    <otherwise>  
       AND  pnode_id = 1
    </otherwise>
</choose>
 
 
 cs

* 8~15line : node_id 가 없는 경우, 아무것도 선택되지 않은 경우(화면에 최초 진입한 상태)이며 최고 조상이라 할 수 있는 node_id 1 을 기준으로 자식 노드를 구한다

 

3-2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록을 조회한 결과

1) 마지막 셀렉트박스가 특정 값으로 선택된 경우 : 단순 join 으로 해결

2) 마지막 셀렉트박스가 '전체'로 선택된 경우 : 아래와 같이 해결

 

ORACLE 은 CONNECT BY 함수를 사용하여 트리구조의 데이터를 출력 할 수 있겠지만,

MYSQL 은 CONNECT BY 함수를 지원하지 않는다.

 

다음과 같이 특정 노드 밑에 있는 모든 자손, 특정 노드 상위에 있는 모든 조상 노드를 구할 수 있다.

 

1. 특정 노드의 자손 구하기

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT node_id
     , pnode_id
     , depth
     , leaf_yn
     , node_path
 FROM  (
        SELECT * 
          FROM node_info
      ORDER BY pnode_id, node_id
        ) node_sorted,
       (SELECT @pv := #{node_id}) initial
WHERE   FIND_IN_SET(pnode_id, @pv)
  AND   LENGTH(@pv := concat(@pv, ',', node_id))
 cs

 

2. 특정 노드의 조상 구하기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT   nid AS node_id
       , pnode_id
       , leaf_yn
       , depth
       , node_path
  FROM (
        SELECT  @nid AS nid,
              (
               SELECT  @nid := pnode_id
                 FROM  node_info
                WHERE  node_id = nid
               ) AS pnode_id
               , leaf_yn
               , depth
               , node_path
          FROM  (
                 SELECT  @nid := #{node_id}
                ) vars
                , node_info
         WHERE  @nid <> 0
        ) as tmp
 cs
참조 : https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

 

3. 특정 분류 밑의 모든 가이드 목록 조회 (특정 노드아이디 기준 모든 자손 노드를 구하는 1번 쿼리를 sub query로 사용)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  seq, 
        title, 
        (SELECT node_path FROM node_info where node_id = gi.guide_id) as guide_path
  FROM  guide_info gi
 WHERE  1=1
   AND  gi.guide_id IN  (SELECT * FROM (
                                             SELECT   node_id
                                               FROM  (SELECT node_id, pnode_id
                                                        FROM node_info
                                                    ORDER BY pnode_id, node_id
                                                     ) node_sorted,
                                                     (select @pv := #{node_id}) initial
                                              WHERE   FIND_IN_SET(pnode_id, @pv)
                                                AND   LENGTH(@pv := concat(@pv, ',', node_id))
                            ) as temp
                            )
ORDER BY seq
 cs

 

※ 위와 같이 subquery 를 사용하여 sql 에서 처리하지 않고, 소스레벨에서 처리하고 싶은 경우 foreach 를 사용하여 다음과 같이 처리할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT   gi.title, gi.guide_id, gi.text_item_1, ni.node_path AS rs
  FROM   guide_info gi
       , node_info ni
 WHERE   1=1
   AND   ni.node_id = gi.guide_id
<if test = 'sch_word neq null and sch_word neq ""'>
   AND   title LIKE CONCAT('%',#{sch_word},'%')
</if>
<if test = 'totalSearchFlag neq null and totalSearchFlag neq ""'>
  <choose>
     <when test = 'totalSearchFlag eq "Y"'>
      AND   ni.node_id IN 
       <foreach item="node_id_grp" index="i" collection="node_id_grp" separator="," open="(" close=")">
        #{node_id_grp}
       </foreach>
      </when>
      <otherwise>
        AND   ni.node_id = #{node_id}
      </otherwise>
  </choose>
</if>
 
 cs

* totalSearchFlag 는 selectBox 에서 '전체'가 선택되었음을 판별하기 위해 사용되는 값

* 1번 쿼리(특정노드의 모든 자손 구하기)로 뽑아온 모든 자손 노드를 필드명이 node_id_grp 인 List 자료형 에 담아 sql mapper 로 넘겨주어 사용

* open 은 for 문이 맨처음 돌기 전에, close 는 for문이 끝까지 돌고나서, separator 는 for 문이 한 번 씩 돌 때마다 적용된다.

* foreach(for문)로 작성된 쿼리 결과물은 다음과 같다

  (생략) ~ AND ni.node_id IN (2, 6)

 

 

 

위 쿼리를 사용해 가져온 데이터를 화면에 그려보자.

https://developyo.tistory.com/55?category=689339

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

JOIN 에서 WHERE 와 ON 의 차이, 그리고 OUTER JOIN  (4) 2019.11.14
SYSDATE vs NOW (SYSDATE 와 NOW 의 차이)  (0) 2019.11.02
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12
[Mysql/ORACLE] 여러 행을 한 줄로 표현하기(GROUP_CONCAT)  (0) 2019.05.27

1. 더하기

DATE_ADD(date, INTERVAL expr type)

DATE_ADD(날짜, INTERVAL 시간 시간타입)

 

ex) 

DATE_ADD(now(), INTERVAL 1 HOUR) : 현재 시각에 1시간 더하기

DATE_ADD(now(), INTERVAL -10 MINUTE) : 현재 시각에 10분 빼기

DATE_ADD(now(), INTERVAL 100 SECOND) : 현재 시각에 100초 더하기

1
2
3
4
SELECT  seq 
  FROM  hst_info
 WHERE  1=1
   AND  JOIN_DATE < DATE_ADD(now(), INTERVAL -1 HOUR)
 cs

: 현재시각(now()) 에 -1 시간(HOUR)을 더한(DATE_ADD) 값이 JOIN_DATE 보다 더 큰 hst_info 테이블의 seq 값을 조회

* expr 값으로 음수를 넣을 경우 음수를 더하면 뺄셈이 되므로 DATE_SUB 함수와 같이 동작한다.

 

 

2. 빼기

DATE_SUB(date, INTERVAL expr type)

DATE_SUB(날짜, INTERVAL 시간 시간타입)

 

ex) 

DATE_SUB(now(), INTERVAL 1 HOUR) : 현재 시각에 1시간 빼기

DATE_SUB(now(), INTERVAL -10 MINUTE) : 현재 시각에 10분 더하기

DATE_SUB(now(), INTERVAL 100 SECOND) : 현재 시각에 100초 빼기

1
2
3
4
SELECT  seq 
  FROM  hst_info
 WHERE  1=1
   AND  JOIN_DATE < DATE_SUB(now(), INTERVAL 1 HOUR)
 cs

: 현재시각(now()) 에 1 시간(HOUR)을 뺀(DATE_SUB) 값이 JOIN_DATE 보다 더 큰 hst_info 테이블의 seq 값을 조회

* expr 값으로 음수를 넣을 경우 음수를 빼는건 덧셈이 되므로( -(-1) = +1) DATE_ADD 함수와 같이 동작한다.

 

 

3. 기간 구하기

TIMESTAMPDIFF(interval, datetime_expr1, datetime_expr2)

TIMESTAMPDIFF(시간타입, 시작시각, 끝시각)

 

ex)

TIMESTAMPDIFF(DAY, '20190820', now()) : 현재년월일 - 20190820 (%Y%m%d) 을 DAY 로 환산

TIMESTAMPDIFF(SECOND, '20190820140000', now()) : 현재년월일 - 20190820140000 (%Y%m%d%H%i%s) 을 SECOND 로 환산

1
2
3
SELECT seq
  FROM hst_info
 WHERE TIMESTAMPDIFF(SECOND, JOIN_DATE, now()) > 3600
 cs

: JOIN_DATE 시각 부터 현재시각(now()) 까지의 시간차이를 초(SECOND)로 환산한 값이 1시간(3600초) 보다 큰 hst_info 테이블의 seq 값을 조회

 

 

위와 같은 함수들을 사용하여

이력 및 통계를 뽑아내거나, 배치를 돌릴 때 시간을 기준으로 데이터를 조작할 수 있다.

 

참고 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

 

 

* ORACLE 시간계산

https://oracle.tistory.com/216

 

 

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

SYSDATE vs NOW (SYSDATE 와 NOW 의 차이)  (0) 2019.11.02
Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12
[Mysql/ORACLE] 여러 행을 한 줄로 표현하기(GROUP_CONCAT)  (0) 2019.05.27
mysql GROUP BY (only full group by)  (3) 2019.03.11

SELECT 한 결과를 INSERT/UPDATE 하기

 

별도의 sql id 로 select, insert 쿼리를 각각 분리하여 선언하고

select 한 값을 서버에서 다시 insert sql 의 매개변수로 던져주어 DB에 insert 할 경우

WAS서버와 DB 서버간 DB connection 이 두 번 발생 하므로, 

다음과 같은 방법을 이용하자.

 

1. mysql 의 select insert 문법 사용

ex1)

1
2
INSERT INTO sampleTable (col1, col2, col3)
SELECT #{val1}, #{val2}, #{val3}
 cs

ex2)

1
2
INSERT INTO sampleTable(col1, col2, col3)    
     SELECT #{val1}, #{val2}, MAX(col3) FROM sampleTable WHERE pkKey = #{pkKey}
 cs

 

2. mybatis 의 selectKey 사용

1
2
3
4
5
6
7
8
9
<insert id="sampleSelectInsert" parameterType="java.util.HashMap">
<selectKey keyProperty="selectRs" resultType="integer" order="before">
  SELECT MAX(val)
    FROM sampleTable
   WHERE key = #{value}
</selectKey>
INSERT INTO targetTable (col1, col2, col3)    
                SELECT #{val1}, #{val2}, #{selectRs}
</insert>
 cs

* order 옵션을 before 로 사용시 메인 쿼리 (위 예제에서 insert sql) 실행 전에 selectKey sql 실행.

  위의 경우 insert sql 실행 전에 select sql가 실행되어야 selectRs 라는 값을 가져올 수 있으므로 order 를 before로 지정

* order 옵션을 after 로 사용시 메인 쿼리 실행 후에 selectKey sql 이 실행되므로, insert 이후의 seq(시퀀스) max 값을

  return해야 할 때 유용하게 사용 가능

 

※ update 에서 selectkey 를 사용할 경우 mybatis 버전이 낮을 때 에러가 발생하는 경우가 있는 것 같다.

   select + update 를 사용해야 하는 경우 서브쿼리를 사용하자.

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21
[Mysql/ORACLE] 여러 행을 한 줄로 표현하기(GROUP_CONCAT)  (0) 2019.05.27
mysql GROUP BY (only full group by)  (3) 2019.03.11
데이터 형식 바꾸기 : DATE_FORMAT(mysql), TO_DATE(Oracle), CONVERT(sybase)  (0) 2018.12.10

n개의 row 로 결과가 출력되는 쿼리를 한 줄의 row에 표현하고 싶은 경우, GROUP_CONCAT을 사용한다.

* SELECT GROUP_CONCAT('칼럼명' SEPARATOR '구분문자')

   FROM 테이블명

 

예를 들어,

쿼리 :

1
2
SELECT user_id AS result 
  FROM user_info;
 cs

결과 :

result
user1
user2
user3

 

n개의 로우가 출력되는 쿼리에 GROUP_CONCAT 사용시,

쿼리:

1
2
SELECT GROUP_CONCAT(user_id SEPARATOR '^') AS result 
  FROM user_info;
 cs

결과 :

result
user1^user2^user3

위처럼 하나의 row에 구분자를 기준으로 모든 row가 붙어서 출력된다.

* GROUP_CONCAT('데이터' ORDER BY '정렬기준' desc SEPARATOR '구분자') 와 같이 order by 사용도 가능.

 

* 응용

1
2
3
4
5
6
7
SELECT COUNT(*)
  FROM sample_table
 WHERE json_data REGEXP (
                         SELECT GROUP_CONCAT(user_id SEPARATOR '|') AS result 
                           FROM user_info
 );
 
 cs

적절한 예인지 모르겠으나, 위와 같이 복수개의 문자열에 대한 LIKE 조건을 건 것 처럼 사용 할 수도 있지 않을까..

* jstl 을 사용한 클라이언트단에서의 출력

 

1
2
3
4
5
6
<td>
    <c:set var="user_id" value="${fn:split(list.user_id, '^')}" />
    <c:forEach var="obj" items="${user_id}" varStatus="idx">
    ${obj}<c:if test="${!idx.last}"><br></c:if>
    </c:forEach>
</td>
 cs

 

※ ORACLE

LISTAGG(column, 'separator') WITHIN GROUP(ORDER BY column)

1
2
SELECT LISTAGG(user_id, ',') WITHIN GROUP(ORDER BY user_id) AS result
FROM user_info
 cs

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12
mysql GROUP BY (only full group by)  (3) 2019.03.11
데이터 형식 바꾸기 : DATE_FORMAT(mysql), TO_DATE(Oracle), CONVERT(sybase)  (0) 2018.12.10

Spring Framework 에서 Redis 연동 및 사용

redis 는 메모리DB로 NOSql 중 하나.

보통 이중화를 위해 redis를 여러개 띄워 놓고, 이를 sentinel 로 모니터링하는 식으로 사용.

 

sentinel 은 단어가 지니고 있는 뜻(감시자)처럼 redis 를 모니터링 하는 용도로 쓰인다.

보통 홀수개(3, 5...)로 sentinel 을 사용하고,

sentinel들은 redis master 서버가 죽었다고 판단 할 경우, 자체적으로 투표를 진행하여 redis slave를 redis master로 승격시킨다.

 

* Ubuntu에 redis를 설치하여 직접 사용해 보았으나 과정 중 캡쳐를 해놓는다거나 따로 정리하진 못했다.

* redis 자체가 워낙 많이 쓰이고 설치/사용이 어렵지 않아 구글링 조금만 하면 환경은 큰 어려움 없이 잡을 수 있다.

 

문제는 spring framework (jdk1.7)에서 sentinel 및 redis 사용법인데..

우리나라말로 설명이 잘 되어 있는 레퍼런스는 찾기 힘들고, 구글링도 쉽지 않다는 점..

 

현재 프로젝트에서 redis 를 사용하게 될 지 정확한 결론이 나지 않았지만

일단 짬짬히 시간날 때 마다 공부 좀 하고 소스 좀 짜며.. 모듈 구현을 해보았다.

 

* 환경 : 

server : jboss/wildfly 9

language : java

framework : spring 3.x

jdk : 1.7

sentinel 3대, redis 2대

 

***

기존 서비스 내의 채팅서버에서 사용하고 있는 redis서버를 사용했으며, 채팅서버는 vert.x로 구현이 되어 있는 상황.

vert.x 에서 redis 를 사용하기 위해 vert.x 에서 지원하는 lib 을 사용하게되면,

sentinel 에도 비밀번호 설정(sentinel configuration 파일의 require-pass 설정)이 필수적으로 되어 있어야 한다고 하는데, 

문제는 jdk 1.7 에 지원되는 spring redis(jedis) jar엔 sentinel 패스워드가 잡혀있는 환경에서 redis 를 사용할 수 있도록 지원하는 method 가 없는 듯 하다..   

(jedis jar 에 구현되어 있는 method 들(JedisSentinelPool 등..)의 인자값을 보면 password 를 받게 되있는 method 들이 몇개 있었으나, redis password만 인자값으로 받고있을 뿐, sentinel password를 인자값으로 받고 있는 오버로딩 된 생성자가 없다. 혹시나 하고 redis password 인자값 부분에 sentinel password를 넣고 connection을 시도해보았지만 역시나 되지 않음)

 

 

[ pom.xml ]

1
2
3
4
5
6
7
8
9
10
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-redis</artifactId>
            <version>1.7.0.RELEASE</version>
        </dependency>
 cs

 

lettuce 라는 녀석도 있었지만 jedis 만 sentinel 지원을 한다길래 jedis를 사용했다.

(lettuce 최신 버전은 지원하고 있는지 잘 모르겠다)

 

[ CustomJedisSentinelPool.java ]

sentinel 로부터 master redis를 가져오는 객체.

listener를 멀티스레드로 동작시켜, redis 서버 상태가 변할지라도 redis master를 가져올 수 있게 하는 객체.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
package ~.utils;
 
 
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.concurrent.atomic.AtomicBoolean;
 
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import redis.clients.jedis.HostAndPort;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPoolAbstract;
import redis.clients.jedis.JedisPubSub;
import redis.clients.jedis.Protocol;
import redis.clients.jedis.exceptions.JedisConnectionException;
import redis.clients.jedis.exceptions.JedisException;
 
public class CustomJedisSentinelPool extends JedisPoolAbstract {
 
      protected GenericObjectPoolConfig poolConfig;
 
      protected int connectionTimeout = Protocol.DEFAULT_TIMEOUT;
      protected int soTimeout = Protocol.DEFAULT_TIMEOUT;
 
      protected String password;
      protected String sentinelPassword;
      
      protected int database = Protocol.DEFAULT_DATABASE;
 
      protected String clientName;
 
      protected Set<MasterListener> masterListeners = new HashSet<MasterListener>();
 
      protected Logger log = LoggerFactory.getLogger(getClass().getName());
 
      private volatile JedisFactory factory;
      private volatile HostAndPort currentHostMaster;
      
      private final Object initPoolLock = new Object();
      
      public void setSentinelPassword(String sentinelPassword){
          this.sentinelPassword = sentinelPassword;
      }
      
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels,
          final GenericObjectPoolConfig poolConfig) {
        this(masterName, sentinels, poolConfig, Protocol.DEFAULT_TIMEOUT, null, null,
            Protocol.DEFAULT_DATABASE);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels) {
        this(masterName, sentinels, new GenericObjectPoolConfig(), Protocol.DEFAULT_TIMEOUT, null, null,
            Protocol.DEFAULT_DATABASE);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels, String password) {
        this(masterName, sentinels, new GenericObjectPoolConfig(), Protocol.DEFAULT_TIMEOUT, password);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels,
          final GenericObjectPoolConfig poolConfig, int timeout, final String password) {
        this(masterName, sentinels, poolConfig, timeout, password, null, Protocol.DEFAULT_DATABASE);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels,
          final GenericObjectPoolConfig poolConfig, final int timeout) {
        this(masterName, sentinels, poolConfig, timeout, null, null, Protocol.DEFAULT_DATABASE);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels,
          final GenericObjectPoolConfig poolConfig, final String password) {
        this(masterName, sentinels, poolConfig, Protocol.DEFAULT_TIMEOUT, password);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels,
          final GenericObjectPoolConfig poolConfig, int timeout, final String password, final String sentinelPassword ,
          final int database) {
        this(masterName, sentinels, poolConfig, timeout, timeout, password, sentinelPassword, database);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels,
          final GenericObjectPoolConfig poolConfig, int timeout, final String password, final String sentinelPassword,
          final int database, final String clientName) {
        this(masterName, sentinels, poolConfig, timeout, timeout, password, sentinelPassword, database, clientName);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels,
          final GenericObjectPoolConfig poolConfig, final int timeout, final int soTimeout,
          final String password, final String sentinelPassword, final int database) {
        this(masterName, sentinels, poolConfig, timeout, soTimeout, password, sentinelPassword, database, null);
      }
 
      public CustomJedisSentinelPool(String masterName, Set<String> sentinels,
          final GenericObjectPoolConfig poolConfig, final int connectionTimeout, final int soTimeout,
          final String password, final String sentinelPassword, final int database, final String clientName) {
        this.poolConfig = poolConfig;
        this.connectionTimeout = connectionTimeout;
        this.soTimeout = soTimeout;
        this.password = password;
        this.sentinelPassword = sentinelPassword;
        this.database = database;
        this.clientName = clientName;
 
        HostAndPort master = initSentinels(sentinels, masterName);
        initPool(master);
      }
 
      @Override
      public void destroy() {
        for (MasterListener m : masterListeners) {
          m.shutdown();
        }
 
        super.destroy();
      }
 
      public HostAndPort getCurrentHostMaster() {
        return currentHostMaster;
      }
 
      private void initPool(HostAndPort master) {
        synchronized(initPoolLock){
          if (!master.equals(currentHostMaster)) {
            currentHostMaster = master;
            if (factory == null) {
              factory = new JedisFactory(master.getHost(), master.getPort(), connectionTimeout,
                  soTimeout, password, database, clientName);
              initPool(poolConfig, factory);
            } else {
              factory.setHostAndPort(currentHostMaster);
              // although we clear the pool, we still have to check the
              // returned object
              // in getResource, this call only clears idle instances, not
              // borrowed instances
              internalPool.clear();
            }
 
//            log.info("Created JedisPool to master at " + master);
          }
        }
      }
 
      private HostAndPort initSentinels(Set<String> sentinels, final String masterName) {
 
        HostAndPort master = null;
        boolean sentinelAvailable = false;
 
//        log.info("Trying to find master from available Sentinels...");
        
        for (String sentinel : sentinels) {
          final HostAndPort hap = HostAndPort.parseString(sentinel);
 
//          log.debug("Connecting to Sentinel {}", hap);
 
          Jedis jedis = null;
          try {
            jedis = new Jedis(hap);
            
            if(this.sentinelPassword != null && !this.sentinelPassword.isEmpty()){
                jedis.auth(this.sentinelPassword);
            }
            
            List<String> masterAddr = jedis.sentinelGetMasterAddrByName(masterName);
 
            // connected to sentinel...
            sentinelAvailable = true;
 
            if (masterAddr == null || masterAddr.size() != 2) {
//              log.warn("Can not get master addr, master name: {}. Sentinel: {}", masterName, hap);
              continue;
            }
 
            master = toHostAndPort(masterAddr);
//            log.debug("Found Redis master at {}", master);
            break;
          } catch (JedisException e) {
            // resolves #1036, it should handle JedisException there's another chance
            // of raising JedisDataException
            log.warn(
              "Cannot get master address from sentinel running @ {}. Reason: {}. Trying next one.", hap,
              e.toString());
          } finally {
            if (jedis != null) {
              jedis.close();
            }
          }
        }
 
        if (master == null) {
          if (sentinelAvailable) {
            // can connect to sentinel, but master name seems to not
            // monitored
            throw new JedisException("Can connect to sentinel, but " + masterName
                + " seems to be not monitored...");
          } else {
            throw new JedisConnectionException("All sentinels down, cannot determine where is "
                + masterName + " master is running...");
          }
        }
 
//        log.info("Redis master running at " + master + ", starting Sentinel listeners...");
 
        for (String sentinel : sentinels) {
          final HostAndPort hap = HostAndPort.parseString(sentinel);
          MasterListener masterListener = new MasterListener(masterName, hap.getHost(), hap.getPort());
          // whether MasterListener threads are alive or not, process can be stopped
          masterListener.setDaemon(true);
          masterListeners.add(masterListener);
          masterListener.start();
        }
 
        return master;
      }
 
      private HostAndPort toHostAndPort(List<String> getMasterAddrByNameResult) {
        String host = getMasterAddrByNameResult.get(0);
        int port = Integer.parseInt(getMasterAddrByNameResult.get(1));
 
        return new HostAndPort(host, port);
      }
 
      @Override
      public Jedis getResource() {
        while (true) {
            try {
                Jedis jedis = super.getResource();
                jedis.setDataSource(this);
                
                // get a reference because it can change concurrently
                final HostAndPort master = currentHostMaster;
                final HostAndPort connection = new HostAndPort(jedis.getClient().getHost(), jedis.getClient()
                        .getPort());
                
                if (master.equals(connection)) {
                    // connected to the correct master
                    return jedis;
                } else {
                    returnBrokenResource(jedis);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw e;
            }
        }
      }
 
      @Override
      protected void returnBrokenResource(final Jedis resource) {
        if (resource != null) {
          returnBrokenResourceObject(resource);
        }
      }
 
      @Override
      protected void returnResource(final Jedis resource) {
        if (resource != null) {
          resource.resetState();
          returnResourceObject(resource);
        }
      }
 
      protected class MasterListener extends Thread {
 
        protected String masterName;
        protected String host;
        protected int port;
        protected long subscribeRetryWaitTimeMillis = 5000;
        protected volatile Jedis j;
        protected AtomicBoolean running = new AtomicBoolean(false);
 
        protected MasterListener() {
        }
 
        public MasterListener(String masterName, String host, int port) {
          super(String.format("MasterListener-%s-[%s:%d]", masterName, host, port));
          this.masterName = masterName;
          this.host = host;
          this.port = port;
        }
 
        public MasterListener(String masterName, String host, int port,
            long subscribeRetryWaitTimeMillis) {
          this(masterName, host, port);
          this.subscribeRetryWaitTimeMillis = subscribeRetryWaitTimeMillis;
        }
 
        @Override
        public void run() {
 
          running.set(true);
 
          while (running.get()) {
 
            j = new Jedis(host, port);
            if(sentinelPassword != null && !sentinelPassword.isEmpty()){
                j.auth(sentinelPassword);
            }
            try {
              // double check that it is not being shutdown
              if (!running.get()) {
                break;
              }
              
              /*
               * Added code for active refresh
               */
              List<String> masterAddr = j.sentinelGetMasterAddrByName(masterName);  
              if (masterAddr == null || masterAddr.size() != 2) {
                log.warn("Can not get master addr, master name: {}. Sentinel: {}：{}.",masterName,host,port);
              }else{
                  initPool(toHostAndPort(masterAddr)); 
              }
 
              j.subscribe(new JedisPubSub() {
                @Override
                public void onMessage(String channel, String message) {
                  log.debug("Sentinel {}:{} published: {}.", host, port, message);
 
                  String[] switchMasterMsg = message.split(" ");
 
                  if (switchMasterMsg.length > 3) {
 
                    if (masterName.equals(switchMasterMsg[0])) {
                      initPool(toHostAndPort(Arrays.asList(switchMasterMsg[3], switchMasterMsg[4])));
                    } else {
                      log.debug(
                        "Ignoring message on +switch-master for master name {}, our master name is {}",
                        switchMasterMsg[0], masterName);
                    }
 
                  } else {
                    log.error(
                      "Invalid message received on Sentinel {}:{} on channel +switch-master: {}", host,
                      port, message);
                  }
                }
              }, "+switch-master");
 
            } catch (JedisException e) {
 
              if (running.get()) {
                log.error("Lost connection to Sentinel at {}:{}. Sleeping 5000ms and retrying.", host,
                  port, e);
                try {
                  Thread.sleep(subscribeRetryWaitTimeMillis);
                } catch (InterruptedException e1) {
                  log.error("Sleep interrupted: ", e1);
                }
              } else {
                log.debug("Unsubscribing from Sentinel at {}:{}", host, port);
              }
            } finally {
              j.close();
            }
          }
        }
 
        public void shutdown() {
          try {
            log.debug("Shutting down listener on {}:{}", host, port);
            running.set(false);
            // This isn't good, the Jedis object is not thread safe
            if (j != null) {
              j.disconnect();
            }
          } catch (Exception e) {
            log.error("Caught exception while shutting down: ", e);
          }
        }
      }
    }
 cs

*

jedis jar의 JedisSentinelPool.class 를 열어보면 아래 소스와 거의 일치한다..

그럼에도 불구하고 기존 구현된 jedis jar의 JedisSentinelPool 을 사용하지 않고 굳이 JedisPoolAbstract을 상속받아 구현하게 된 이유는, Jedis jar의 JedisSentinelPool은 Sentinel에 패스워드 설정이 잡혀있는 경우, initSentinel(..) method내에서 sentinel 로 부터 redis master 정보를 가져오는 jedis.sentinelGetMasterAddrByName(..) method 에서 Exception 이 나기 때문.. (auth fail exception)

 

실질적으로 수정한 부분은 jedis 객체로부터 master 정보를 가져오는 sentinelGetMasterAddrByName(..)메소드를 호출하기 전에 jedis객체에 sentinel 패스워드를 set해준게 전부..

(163~165 line, 298~301 line)

 

 

[ JedisFactory.java ]

CustomJedisSentinelPool.java 에서 사용하는 객체로, 

Jedis jar 에 구현되어 있지만 접근제한자가 default이므로 동일한 package가 아닌 CustomJedisSentinelPool에선 해당 객체를 생성할 수 없어 CustomJedisSentinelPool과 동일한 package 밑에 JedisFactory를 만들어 줌.

* Jedis jar 내의 JedisFactory.class 와 동일한 소스

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
package ~.utils;
 
import java.net.URI;
import java.util.concurrent.atomic.AtomicReference;
 
import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.SSLParameters;
import javax.net.ssl.SSLSocketFactory;
 
import org.apache.commons.pool2.PooledObject;
import org.apache.commons.pool2.PooledObjectFactory;
import org.apache.commons.pool2.impl.DefaultPooledObject;
 
import redis.clients.jedis.BinaryJedis;
import redis.clients.jedis.HostAndPort;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.exceptions.InvalidURIException;
import redis.clients.jedis.exceptions.JedisException;
import redis.clients.jedis.util.JedisURIHelper;
 
/**
 * PoolableObjectFactory custom impl.
 */
class JedisFactory implements PooledObjectFactory<Jedis> {
  private final AtomicReference<HostAndPort> hostAndPort = new AtomicReference<HostAndPort>();
  private final int connectionTimeout;
  private final int soTimeout;
  private final String password;
  private final int database;
  private final String clientName;
  private final boolean ssl;
  private final SSLSocketFactory sslSocketFactory;
  private final SSLParameters sslParameters;
  private final HostnameVerifier hostnameVerifier;
 
  JedisFactory(final String host, final int port, final int connectionTimeout,
      final int soTimeout, final String password, final int database, final String clientName) {
    this(host, port, connectionTimeout, soTimeout, password, database, clientName,
        false, null, null, null);
  }
 
  JedisFactory(final String host, final int port, final int connectionTimeout,
      final int soTimeout, final String password, final int database, final String clientName,
      final boolean ssl, final SSLSocketFactory sslSocketFactory, final SSLParameters sslParameters,
      final HostnameVerifier hostnameVerifier) {
    this.hostAndPort.set(new HostAndPort(host, port));
    this.connectionTimeout = connectionTimeout;
    this.soTimeout = soTimeout;
    this.password = password;
    this.database = database;
    this.clientName = clientName;
    this.ssl = ssl;
    this.sslSocketFactory = sslSocketFactory;
    this.sslParameters = sslParameters;
    this.hostnameVerifier = hostnameVerifier;
  }
 
  JedisFactory(final URI uri, final int connectionTimeout, final int soTimeout,
      final String clientName) {
    this(uri, connectionTimeout, soTimeout, clientName, null, null, null);
  }
 
  JedisFactory(final URI uri, final int connectionTimeout, final int soTimeout,
      final String clientName, final SSLSocketFactory sslSocketFactory,
      final SSLParameters sslParameters, final HostnameVerifier hostnameVerifier) {
    if (!JedisURIHelper.isValid(uri)) {
      throw new InvalidURIException(String.format(
        "Cannot open Redis connection due invalid URI. %s", uri.toString()));
    }
 
    this.hostAndPort.set(new HostAndPort(uri.getHost(), uri.getPort()));
    this.connectionTimeout = connectionTimeout;
    this.soTimeout = soTimeout;
    this.password = JedisURIHelper.getPassword(uri);
    this.database = JedisURIHelper.getDBIndex(uri);
    this.clientName = clientName;
    this.ssl = JedisURIHelper.isRedisSSLScheme(uri);
    this.sslSocketFactory = sslSocketFactory;
    this.sslParameters = sslParameters;
    this.hostnameVerifier = hostnameVerifier;
  }
 
  public void setHostAndPort(final HostAndPort hostAndPort) {
    this.hostAndPort.set(hostAndPort);
  }
 
  @Override
  public void activateObject(PooledObject<Jedis> pooledJedis) throws Exception {
    final BinaryJedis jedis = pooledJedis.getObject();
    if (jedis.getDB() != database) {
      jedis.select(database);
    }
 
  }
 
  @Override
  public void destroyObject(PooledObject<Jedis> pooledJedis) throws Exception {
    final BinaryJedis jedis = pooledJedis.getObject();
    if (jedis.isConnected()) {
      try {
        try {
          jedis.quit();
        } catch (Exception e) {
        }
        jedis.disconnect();
      } catch (Exception e) {
 
      }
    }
 
  }
 
  @Override
  public PooledObject<Jedis> makeObject() throws Exception {
    final HostAndPort hostAndPort = this.hostAndPort.get();
    final Jedis jedis = new Jedis(hostAndPort.getHost(), hostAndPort.getPort(), connectionTimeout,
        soTimeout, ssl, sslSocketFactory, sslParameters, hostnameVerifier);
 
    try {
      jedis.connect();
      if (password != null) {
        jedis.auth(password);
      }
      if (database != 0) {
        jedis.select(database);
      }
      if (clientName != null) {
        jedis.clientSetname(clientName);
      }
    } catch (JedisException je) {
      jedis.close();
      throw je;
    }
 
    return new DefaultPooledObject<Jedis>(jedis);
 
  }
 
  @Override
  public void passivateObject(PooledObject<Jedis> pooledJedis) throws Exception {
    // TODO maybe should select db 0? Not sure right now.
  }
 
  @Override
  public boolean validateObject(PooledObject<Jedis> pooledJedis) {
    final BinaryJedis jedis = pooledJedis.getObject();
    try {
      HostAndPort hostAndPort = this.hostAndPort.get();
 
      String connectionHost = jedis.getClient().getHost();
      int connectionPort = jedis.getClient().getPort();
 
      return hostAndPort.getHost().equals(connectionHost)
          && hostAndPort.getPort() == connectionPort && jedis.isConnected()
          && jedis.ping().equals("PONG");
    } catch (final Exception e) {
      return false;
    }
  }
}
 
 cs


[ RedisUtil.java ]

CustomJedisSentinelPool 을 사용하여 redis master 객체를 얻어오고, redis에 값을 넣고 빼는 등 실질적으로 redis를 사용하는 클래스

 

별도의 property로 sentinel ip:port, sentinel password, redis password, database number를 관리.

* sentinel 로 부터 redis 객체를 가져오기 때문에 redis ip:port 는 별도로 관리해줄 필요가 없음.

 

getConnection() : pool 객체 생성(내부적으로 listener 생성 및 동작)

getValue(String key) : key 기준으로 값을 가져오는 메소드

setValue(String key, String value, int sec) : key, value 를 특정시간(sec)동안 redis 에 저장하는 메소드

disConnection(..) : 사용한 redis 와의 접속 해제 및 자원 반납

                        (* 자원 반납 제대로 하지 않을 경우 server exhausted Exception 발생)

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
package ~.utils;
 
import java.util.HashSet;
import java.util.Set;
 
import javax.annotation.PostConstruct;
 
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
 
import redis.clients.jedis.Jedis;
 
import ~.APIException;
 
@Component("RedisUtil")
public class RedisUtil {
    
    @Value("#{properties['sentinel.Addr']}")
    private String SENTINEL_ADDR;
    
    @Value("#{properties['sentinel.master']}")
    private String MASTER_NAME;
    
    @Value("#{properties['sentinel.pa']}")
    private String SENTINEL_P;
    
    @Value("#{properties['sentinel.redis.pa']}")
    private String REDIS_P;
    
    @Value("#{properties['sentinel.db']}")
    private int DATABASE;
 
    private Set<String> sentinels;
    
    private final String deli = "\\^";
    
    private CustomJedisSentinelPool pool;
    
    protected static final Logger logger = LoggerFactory.getLogger(RedisUtil.class);
    
    @PostConstruct
    private void getConnection(){
        
        if(pool != null){
            return;
        }
        
        
        if(sentinels == null){ 
        
            sentinels = new HashSet<String>();
        }
        
        if(sentinels.isEmpty()){
        
            String[] sentinelArr = this.SENTINEL_ADDR.split(this.deli);
            
            for(String sentinelNode : sentinelArr ){
        
                sentinels.add(sentinelNode);
            }
        }
        
        logger.debug("redis master name : " + this.MASTER_NAME);
        logger.debug("redis redis p : " + this.REDIS_P);
        logger.debug("redis sentinel p : " + this.SENTINEL_P);
        logger.debug("redis database : " + this.DATABASE);
        
        GenericObjectPoolConfig gopc = new GenericObjectPoolConfig();
        gopc.setMaxWaitMillis(1000);
        gopc.setBlockWhenExhausted(false);
        pool = new CustomJedisSentinelPool(this.MASTER_NAME, 
                sentinels, gopc, 500, this.REDIS_P, this.SENTINEL_P, this.DATABASE);
        
        logger.info("get Connection 3");
    }
    
    public String getValue(String key){
        String result="";
 
        Jedis jedis = null;
        
        
        try{
            jedis = this.pool.getResource();
        
        } catch (Exception e){
            logger.error("redis connection fail : " + e);
        }
        
        result = jedis.get(key);
        logger.debug("retrived value : " + result);
        
        
        this.disconnection(jedis);
        
        return result;
    }
    
    public boolean setValue(String key, String value, int sec){
        boolean result=true;
        Jedis jedis = null;
        
 
        try{
            jedis = this.pool.getResource();
            String setexResult = jedis.setex(key, sec, value);
        
        
            if(!"OK".equalsIgnoreCase(setexResult)){
                throw new APIException();
            }
            
        } catch (APIException ae){
            logger.warn("set redis data fail : " + ae);
            result = false;
        } catch (Exception e){
            logger.error("exception in set value : " + e);
            result = false;
        }
        logger.info("set value 3");
        this.disconnection(jedis);
        
        return result;
    }
        
    private void disconnection(Jedis jedis){
        try{
 
                if(jedis != null){
                    jedis.disconnect();
                    jedis.close();
                    jedis = null;
                }
 
        } catch (Exception e){
            logger.error("redis disconnection fail : {}" , e);
        }
    }
 
    
}
 
 cs

 

RedisTemplate 객체를 사용할 경우 값을 다양한 방법으로 넣고 뺄 수 있던데,

해당 부분은 나중에 공부를 더 해보는걸로..

 

반응형
저작자표시 비영리

 MYSQL GROUP BY(incompatible with sql_mode=only full group by)에 대해 알아보자

 

로컬 및 개발 환경에서 멀쩡히 돌아가던 쿼리가

테스트 서버에 올리고 나니 다음과 같은 Exception을 뱉었다.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column '칼럼명' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

: group by 절에 포함되지 않은 칼럼(집계되지 않은 칼럼(nonaggregated column))을 select 절에서 뽑아올 경우, 어느 칼럼을 표시해야 할 지 몰라 exception 이 발생하는 현상이다. 

only_full_group_by 란?

mysql 5.7버전부터 sql_mode로 추가된 설정으로

집계되지 않은 칼럼을 select 절에서 뽑아오는 경우 exception을 낼지 exception 을 내지 않고 동작할지 결정하는 옵션이다.

 

간단히 예를 들면.

아래와 같이 사람 별로 영어 점수 총 합계를 조회하는 쿼리가 있다.

1
2
3
4
5
SELECT id as '아이디'
       ,  phone_num as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
FROM  sampletable
GROUP BY id
 cs

phone_num (전화번호) 은 유니크한 값으로 다른 레코드와 중복될 수 없는 값이란 걸 개발자는 알겠지만,

DB입장에선 phone_num의 경우 어떤 값을 표시해야 할지 알 수 없다. (이를 집계되지 않은 칼럼 nonaggregated column)이라 칭함)

 

* 동일한 쿼리를 ORACLE 에서 실행시 적합하지 않은 group by 사용으로 exception 이 발생한다.

* mysql 에서 실행시 only_full_group_by 옵션의 활성화/비활성화 상태에 따라 exception이 발생/발생하지 않는다.

 

[해결 방법]

1. 쿼리를 수정한다.

1) 쿼리 자체를 수정. 

1-1) group by 절에 nonaggregated column 추가

1
2
3
4
5
6
SELECT  id as '아이디'
       ,  phone_num as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
FROM  sampletable
GROUP BY id, phone_num
 
 cs

 

1-2) 서브쿼리 사용(경우에 따라 left outer join 사용)

1
2
3
4
5
6
7
  SELECT  id as '아이디'
       ,  phone_num as '전화번호'
       ,  ( SELECT SUM(eng_point) 
            FROM  sampletable
            WHERE st.id = id 
            GROUP BY id) as '영어점수합계'            
FROM  sampletable st
 cs

 

2) select 절의 집계되지 않은 칼럼에 ANY_VALUE(칼럼) 함수 추가

1
2
3
4
5
SELECT  id as '아이디'
       ,  ANY_VALUE(phone_num) as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
FROM  sampletable
GROUP BY id
 cs

 

2. mysql 설정파일을 수정한다. 

2-1) 현재 옵션 상태를 확인한다.

 

sql_mode 조회.

> select @@sql_mode;

 

sql_mode 조회 결과.

> | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,                                                                                        DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

ONLY_FULL_GROUP_BY 이 포함되어 있다면 해당 설정이 활성화 되어 있음을 의미.

 

2-2) my.cnf 설정파일을 열어 해당 부분을 찾아 제거

2-3) mysql 재기동.

 

※ 머릿속에서 나온 예제이므로 적합하지 않을 수 있으며, 실제 동작하지 않는 쿼리일 수도 있다.

※ 애초에 쿼리를 잘 짜면 이 같은 문제가 나올 수 없다...

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12
[Mysql/ORACLE] 여러 행을 한 줄로 표현하기(GROUP_CONCAT)  (0) 2019.05.27
데이터 형식 바꾸기 : DATE_FORMAT(mysql), TO_DATE(Oracle), CONVERT(sybase)  (0) 2018.12.10

MYSQL에서 데이터 형식 바꾸기(DATE_FORMAT)

 

oracle , sybase , mysql 전부 데이터 형식 바꾸는 표현식이 전부 다르다..

 

1. mysql (출처 : https://www.w3schools.com/sql/func_mysql_date_format.asp)

SELECT DATE_FORMAT(NOW(), '%y/%m/%d')

return : 18/12/10

 

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d')

return : 2018/12/10

 

%Y : 4자리 년도

%y : 2자리 년도

%H : 00~23

%h : 00~12

%Y%m%d%H%i%s : yyyyMMddHH24miss

%y%m%d%h%i%s : yyMMddHHmiss

 

2. ORACLE

SELECT TO_DATE(SYSDATE, 'YY/MM/DD') 

return : 18/12/10

 

3. SYBASE ( http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1520/html/iqrefbb/Convert.htm 참고 )

SELECT CONVERT(varchar, GETDATE(), 111)

return : 2018/12/10

 

뭐 sybase야 다시 쓸 일이 없을 것 같긴 하지만..

거지같다복잡하다

 

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12
[Mysql/ORACLE] 여러 행을 한 줄로 표현하기(GROUP_CONCAT)  (0) 2019.05.27
mysql GROUP BY (only full group by)  (3) 2019.03.11

+ Recent posts

티스토리툴바