그동안 단일 건이나 간단한 IF문만 사용하여

프로시저를 생성했는데 MySQL에서도 Oracle의 Cursor같이 여러개의 데이터를 뽑아와 

데이터를 반복하여 작업을 진행해보려한다.



1. TEST 테이블 생성하기


1
2
3
4
5
6
7
8
9
CREATE TABLE `TEST_TB1` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `useYn` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6;
cs



2. 기초 데이터 넣기


1
2
3
4
5
6
7
8
9
-- auto increment 초기화
ALTER TABLE TEST_TB1 AUTO_INCREMENT = 0;
 
-- 데이터 삽입
INSERT INTO TEST_TB1(name, useYn) VALUES('조현영', 'Y');
INSERT INTO TEST_TB1(name, useYn) VALUES('박초아', 'Y');
INSERT INTO TEST_TB1(name, useYn) VALUES('김태연', 'Y');
INSERT INTO TEST_TB1(name, useYn) VALUES('김지숙', 'Y');
INSERT INTO TEST_TB1(name, useYn) VALUES('이상현', 'Y');
cs


테이블 조회 결과 





3. 프로시저 만들기


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
DELIMITER $$
DROP PROCEDURE  IF EXISTS test_proc1;
 
CREATE PROCEDURE test_proc1()
 
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_count INT DEFAULT -1;
  DECLARE v_id varchar(20);
  DECLARE v_name varchar(20);
  DECLARE v_useYn varchar(20);
    
  -- select한 결과를 cursor1로 정의 
  DECLARE cursor1 CURSOR FOR 
   SELECT id
         , name
         , useYn
     FROM TEST_TB1;
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
  OPEN cursor1;
 
  my_loop: LOOP
 
  -- loop 하며 cursor1의 데이터를 불러와 변수에 넣는다.
  FETCH cursor1 
   INTO v_id
        , v_name
        , v_useYn;
    
     SET v_count = v_count +1 ; 
 
    -- cursor1 반복이 끝나면 loop 빠져나간다.
    IF done THEN
      LEAVE my_loop;
    END IF;
     
     IF(v_id = 1) THEN
         UPDATE TEST_TB1
           SET name = '전효성'
         WHERE id = v_id;
 
    ELSEIF (v_id = 2) THEN
    INSERT INTO TEST_TB1(name, useYn)
         VALUES('insert값', 'N');
         
    ELSE 
         UPDATE TEST_TB1
           SET name = CONCAT(v_name, v_id)
         WHERE id = v_id;
         
     END IF;
     
  END LOOP;
 
  SELECT v_count; 
 
  -- 커서를 닫는다. 
  CLOSE cursor1;
 
END $$
 
DELIMITER ;
 
cs



cursor1에서 select한 데이터를 모두 갖고 있고

fetch를 통해 cursor의 데이터를 변수에 담고


변수에 담은 데이터를 하나씩 LOOP를 돌면서

IF문에 걸릴경우 UPDATE나 INSERT문을 만나

처리를 진행하게 된다.


LOOP를 돌릴때마다 변수의 count를 올리고 있다.



4. 프로시저 호출하기


1
CALL test_proc1();
cs



프로시저를 실행하게 되면 기존 데이터가 변경되고 신규 데이터가 추가된 것을 확인 할 수 있다.


저작자표시

'전체 > MySQL' 카테고리의 다른 글

MySQL 프로시저 생성 (테이블 백업) 및 이벤트 스케쥴러 등록,삭제  (1) 2019.07.30
select return (0 row 0 total rows) null 결과 출력처리방법  (0) 2019.07.19
MySQL function 만들기  (3) 2019.07.17
MySQL 계층형 쿼리 구현하기  (13) 2019.07.16
SQL EXPLAIN 정리  (0) 2018.11.19


테이블 백업을 수행하는 프로시저를 생성해보려고 한다.

기존 테이블(직원, 휴가, 휴가기록)에서 백업 테이블을 새로 생성하는 프로시저를 만든다.

프로시저를 생성하고 이벤트 스케쥴(event schedule)까지 걸어보겠다.



1. MySQL 프로시저 생성하기


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DELIMITER $$
DROP PROCEDURE  IF EXISTS backup_proc;
 
CREATE PROCEDURE backup_proc()
 
BEGIN
    DROP TABLE IF EXISTS BACKUP_EMP;
    CREATE TABLE BACKUP_EMP SELECT * FROM EMP_EMPLOYEE;
    
    DROP TABLE IF EXISTS BACKUP_VAC;
    CREATE TABLE BACKUP_VAC SELECT * FROM VAC_VACATION;
    
    DROP TABLE IF EXISTS BACKUP_VAC_HIS;
    CREATE TABLE BACKUP_VAC_HIS SELECT * FROM VAC_HISTORY;
    
    SELECT '테이블 백업 성공';
END $$
 
DELIMITER ;
cs


백업테이블이 존재한다면 드랍하고 다시 생성할 것이고

없다면 백업테이블을 기존테이블에서 새로 생성한다는 간단한 쿼리이다.


프로시저를 저장했다면 프로시저를 호출해보자.



2. 프로시저 호출하기


1
CALL backup_proc();
cs





프로시저가 성공적으로 실행됐다면 select 구문에서 설정한 '테이블 백업 성공' 이라는 결과값이 나오고

BACKUP 테이블이 3개 생성된 것을 확인 할 수 있다.


3. 이벤트 스케쥴러 사용하기


매번 프로시저를 call해서 backup 테이블을 만들수는 없는 것이다.

이벤트 스케쥴러에 등록하여 DAY, MONTH, TIME 별로 시간을 지정해 준다면 자동으로 이벤트가 실행될 것이다.



- 이벤트 스케쥴러 ON


1
2
SHOW VARIABLES LIKE 'event%';
SET GLOBAL event_scheduler = ON ;
cs



- 이벤트 만들기


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 매일 반복해서 실행
CREATE EVENT everyDayEvent1
    ON SCHEDULE 
 EVERY 1 DAY
STARTS '2019-07-30 00:00:00'
    DO CALL backup_proc();
 
-- 1달마다 반복해서 실행
CREATE EVENT everyMonthEvent1
    ON SCHEDULE 
 EVERY 1 MONTH
STARTS '2019-07-30 00:00:00'
    DO CALL backup_proc();
 
-- 1년마다 반복해서 실행
CREATE EVENT everyYearEvent1
    ON SCHEDULE 
 EVERY 1 YEAR
STARTS '2019-01-01 00:00:00'
    DO CALL backup_proc();
cs


이벤트를 만들고 스케쥴을 걸어 프로시저를 실행하는 구문이다.

이벤트를 생성하면 현재 등록된 이벤트의 목록을 확인해야 한다.



- 등록된 이벤트 조회하기


1
SELECT * FROM information_schema.`EVENTS` ;
cs


- 이벤트 삭제하기


1
2
3
DROP EVENT IF EXISTS everyDayEvent1;
DROP EVENT IF EXISTS everyMonthEvent1;
DROP EVENT IF EXISTS everyYearEvent1;
cs


백업 테이블명에 백업날짜를 붙여만들거나 

백업 테이블명을 각각 다르게하여 각각 스케쥴링을 걸어놓아 

백업을 정기적으로 할 수 있을 것 같다.



저작자표시

'전체 > MySQL' 카테고리의 다른 글

MySQL 프로시저 loop, fetch, cursor 사용하기  (1) 2019.07.30
select return (0 row 0 total rows) null 결과 출력처리방법  (0) 2019.07.19
MySQL function 만들기  (3) 2019.07.17
MySQL 계층형 쿼리 구현하기  (13) 2019.07.16
SQL EXPLAIN 정리  (0) 2018.11.19


쿼리에서 조건을 걸어 데이터가 return 0 row일때

쿼리에서 "값이 없다" 라는 결과를 리턴값으로 처리하는 방법을 사용해보려한다.


1. 테이블 생성하기


1
2
3
4
5
6
CREATE TABLE `CODE_TABLE` (
    `CODE_CD` VARCHAR(10) NOT NULL COLLATE 'utf8_bin',
    `CODE_NM` VARCHAR(10) NOT NULL COLLATE 'utf8_bin'
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;
cs


코드테이블이라는 테스트 테이블을 생성한다.

코드테이블은 코드, 코드명 컬럼으로 구성된다.


2. 데이터 넣기


1
2
3
4
5
INSERT INTO CODE_TABLE VALUES('10','개발팀');
 
INSERT INTO CODE_TABLE VALUES('20','영업팀');
 
INSERT INTO CODE_TABLE VALUES('30','기획팀');
cs


기초데이터를 넣어준다.



3. 없는 데이터 조회하기


1
2
3
4
-- SELECT한 Row가 0이므로 IFNULL 처리 불가        
SELECT IFNULL(A.CODE_NM, '없는 코드') AS CODE_NM
  FROM CODE_TABLE A
 WHERE A.CODE_CD = '100';
cs


현재 CODE_CD컬럼의 데이터는 10,20,30 이 들어가 있고

where절에서 '100' 을 조회하면 데이터가 조회되지 않는다.


위와 같이 쿼리를 작성하면 데이터가 있다면 코드명이 나오고

데이터가 없다면 아무것도 나오지 않는다.


SELECT 한 결과가 0건이라도

IFNULL에 걸린 것 처럼 "없는 코드" 라고 결과가 나오게 하고 싶다.



4. DUAL 테이블과 외부조인 걸기


1
2
3
4
     SELECT IFNULL(A.CODE_NM, '없는 코드') AS CODE_NM
       FROM CODE_TABLE A 
RIGHT OUTER JOIN (SELECT '') AS m_dual
         ON A.CODE_CD = '100';
cs



DUAL을 외부조인으로 사용하여 CODE가 없다면 "없는코드", 있다면 코드명이 나온다.


5. 집계함수 사용하기


1
2
3
     SELECT IFNULL(MAX(A.CODE_NM), '없는 코드') AS CODE_NM
       FROM CODE_TABLE A 
      WHERE A.CODE_CD = '100';
cs



집계함수를 사용하여 CODE가 없다면 "없는코드", 있다면 코드명이 나온다.


프로시저나 함수를 작성할때 

결과값이 없더라도 MESSAGE로 출력하고 싶다면 유용하게 쓰일수 있을 것 같다.

저작자표시

'전체 > MySQL' 카테고리의 다른 글

MySQL 프로시저 loop, fetch, cursor 사용하기  (1) 2019.07.30
MySQL 프로시저 생성 (테이블 백업) 및 이벤트 스케쥴러 등록,삭제  (1) 2019.07.30
MySQL function 만들기  (3) 2019.07.17
MySQL 계층형 쿼리 구현하기  (13) 2019.07.16
SQL EXPLAIN 정리  (0) 2018.11.19


MySQL function을 이용해 직원 출석등록 함수를 만들어보겠다.


1. 테이블 생성하기


1
2
3
4
5
6
7
CREATE TABLE `EMP_ATTEND` (
    `ATTEND_YMD` VARCHAR(8) NOT NULL COLLATE 'utf8_bin',
    `EMP_NO` VARCHAR(9) NOT NULL COLLATE 'utf8_bin'
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;
 
cs


간단하게 직원출석 테이블을 생성한다.



2. 함수 생성하기


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
DELIMITER $$
 
DROP FUNCTION IF EXISTS FNC_ATTEND;
 
CREATE FUNCTION FNC_ATTEND(attendType VARCHAR(10), empNo VARCHAR(10)) RETURNS BOOL
 
BEGIN
     DECLARE exist_flag INT;
     DECLARE returnVal  BOOL;
         SET returnVal = FALSE;
 
     -- 출석 등록
     IF 'attend'= attendType THEN
         SELECT COUNT(1)
           INTO exist_flag
           FROM EMP_ATTEND
          WHERE EMP_NO = empNo 
            AND ATTEND_YMD = DATE_FORMAT(NOW(), '%Y%m%d')
         ;
         
         IF exist_flag = 0 THEN
             INSERT INTO EMP_ATTEND(
                      ATTEND_YMD
                    , EMP_NO
             )VALUES(
                   DATE_FORMAT(NOW(), '%Y%m%d')
                     , empNo 
             );
             SET returnVal = TRUE;
         END IF;
     END IF;
      RETURN returnVal;
END $$
 
DELIMITER ;
cs


function의 argument 2개를 지정해 생성하고 return 값은 boolean으로 받겠다.


만약 arg로 들어온 첫번째 값이  문자열 'attend' 일경우 

테이블에 해당 직원의 오늘날짜로 출석한 데이터가 있는지 확인하고

데이터가 없다면 오늘날짜와 직원번호로 값을 insert한다.


값이 insert 된 후 returnVal의 값은 TRUE가 된다.



3. 함수 호출하기


1
2
3
4
-- if return value 0 then false
-- else if return value 1 then true
 
SELECT FNC_ATTEND('attend', '1000');
cs



함수를 생성하고 위와 같이 호출을 해본다.


2개 파람값을 넣고 호출하면 1이나 0이 나오게 된다.

0이 나오면 false, 1이 나오면 true이다.

 


4. 테이블 조회하기


1
SELECT * FROM EMP_ATTEND;
cs


직원출석 테이블을 조회하면 값이 잘 들어간 것을 확인할 수 있다.



EMP_NO 하나당 하루에 한번 출석등록을 할 수 있는 function을 만들어보았다.

저작자표시

'전체 > MySQL' 카테고리의 다른 글

MySQL 프로시저 생성 (테이블 백업) 및 이벤트 스케쥴러 등록,삭제  (1) 2019.07.30
select return (0 row 0 total rows) null 결과 출력처리방법  (0) 2019.07.19
MySQL 계층형 쿼리 구현하기  (13) 2019.07.16
SQL EXPLAIN 정리  (0) 2018.11.19
ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유  (0) 2018.10.29


Oracle에서는 CONNECT BY PRIOR, START WITH를 사용하여

자신의 ID와 연결된 부모 ID를 찾아가 계층적으로 쿼리결과를 뽑을 수 있다.

오라클에서만 제공하는 구문이다.


1
2
3
4
5
6
7
-- UPMENU_ID에 연결된 MENU_ID가 계층적으로 표현된다.
 
 SELECT MENU_ID
      , UPMENU_ID
   FROM SYS_MENUINFO A
CONNECT BY PRIOR MENU_ID = UPMENU_ID 
  START WITH UPMENU_ID = '00000'
cs


오라클 쿼리 결과



MySQL에서 위와 같은 계층형 쿼리로 구현하고자 한다면 Oracle에서 제공하는 구문을 사용할 수 없다.

아래는 테스트로 구현할 계층형 형태의 쿼리 결과이다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
동물
 ┗말
   ┗얼룩말
   ┗조랑말
      ┗망아지
 
 ┗닭
   ┗흰닭
      ┗흰병아리
           ┗흰달걀
 
   ┗검은닭
      ┗검은병아리
           ┗검은달걀
cs




MySQL에서 계층형 쿼리 구문이 따로 없어 

함수를 만들어 재귀함수를 사용해 구현해본다.


ANIMAL 테이블을 생성한다.


1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `ANIMAL` (
 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
`p_id` int(10) unsigned default '0',
 
`nm` varchar(50),
 
primary key(`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
cs


ANIMAL 테이블에 데이터를 넣어준다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
insert into ANIMAL(p_id, nm) values ( 0, '동물');
 
insert into ANIMAL(p_id, nm) values ( 1, '말');
 
insert into ANIMAL(p_id, nm) values ( 1, '닭');
 
insert into ANIMAL(p_id, nm) values ( 2, '얼룩말');
 
insert into ANIMAL(p_id, nm) values ( 2, '조랑말');
 
insert into ANIMAL(p_id, nm) values ( 3, '흰닭');
 
insert into ANIMAL(p_id, nm) values ( 3, '검은닭');
 
insert into ANIMAL(p_id, nm) values ( 5, '망아지');
 
insert into ANIMAL(p_id, nm) values ( 6, '흰병아리');
 
insert into ANIMAL(p_id, nm) values ( 7, '검은병아리');
 
insert into ANIMAL(p_id, nm) values ( 9, '흰달걀');
 
insert into ANIMAL(p_id, nm) values ( 10, '검은달걀');
cs


함수 fnc_hierarchi 를 만들어준다.


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
DROP FUNCTION IF EXISTS fnc_hierarchi;
 
DELIMITER $$
 
CREATE FUNCTION  fnc_hierarchi() RETURNS INT
 
NOT DETERMINISTIC
 
READS SQL DATA
 
BEGIN
 
    DECLARE v_id INT;
    DECLARE v_parent INT;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
 
    SET v_parent = @id;
    SET v_id = -1;
 
    IF @id IS NULL THEN
        RETURN NULL;
    END IF;
 
    LOOP
    
    SELECT MIN(id)
      INTO @id 
      FROM ANIMAL
     WHERE p_id = v_parent
       AND id > v_id;
 
    IF (@id IS NOT NULL) OR (v_parent = @start_with) THEN
       SET @level = @level + 1;
    RETURN @id;
    END IF;
    
    SET @level := @level - 1;
 
    SELECT id, p_id
      INTO v_id , v_parent 
        FROM ANIMAL
       WHERE id = v_parent;
   
    END LOOP;
 
END
 
$$
 
DELIMITER ;
cs


조회해본다.


1
2
3
4
5
6
7
8
9
10
11
12
SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT('    ', level  - 1),'┗'), ani.nm)
                 ELSE ani.nm
           END AS nm
     , ani.id
     , ani.p_id
     , fnc.level
  FROM
     (SELECT fnc_hierarchi() AS id, @level AS level
        FROM (SELECT @start_with:=0, @id:=@start_with, @level:=0) vars
          JOIN ANIMAL
         WHERE @id IS NOT NULL) fnc
  JOIN ANIMAL ani ON fnc.id = ani.id
cs


의도한 대로 쿼리결과가 나온것을 확인했다.



저작자표시

'전체 > MySQL' 카테고리의 다른 글

select return (0 row 0 total rows) null 결과 출력처리방법  (0) 2019.07.19
MySQL function 만들기  (3) 2019.07.17
SQL EXPLAIN 정리  (0) 2018.11.19
ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유  (0) 2018.10.29
group by 이해하기  (0) 2018.10.24


1. SQL EXPLAIN 정리


1.1 부서 테이블


1
2
3
select * 
  from dbadev.dept
;
cs



부서테이블에는 4개의 행 ( 4rows returned )


1.2 직원 테이블


1
2
3
select * 
  from dbadev.emp
;
cs





직원테이블에는 14개의 행 ( 14rows returned )


1.3 부서와 직원 테이블을 내부조인



1
2
3
4
5
6
7
8
9
10
11
SELECT a.deptno
     , a.dname
     , a.loc
     , b.empno
     , b.ename
     , b.job
  FROM dbadev.dept as a
 INNER JOIN dbadev.emp as b
    ON a.deptno = b.deptno
 WHERE a.deptno= '20'
;
cs




1.4 EXPLAIN을 사용한 부서테이블과 직원테이블 내부조인 쿼리 실행결과


1
2
3
4
5
6
7
8
9
10
11
12
EXPLAIN
SELECT a.deptno
     , a.dname
     , a.loc
     , b.empno
     , b.ename
     , b.job
  FROM dbadev.dept as a
 INNER JOIN dbadev.emp as b
    ON a.deptno = b.deptno
 WHERE a.deptno= '20'
;
cs





MySQL 실행 계획 항목 설명


1. id

쿼리의 실행 순서대로 1부터 순차적으로 값을 부여. 즉 쿼리의 실행 순서라고 봐도 무방함.

다만 join 의 경우엔 하나의 구문에서 두개 이상의 테이블을 참조하기때문에 모든 테이블에 같은 순번이 부여됨.



2. select_type

select 구문의 실행 타입.


 속성값

 내용

 SIMPLE

 단순 select 구문으로 별다른 조인이나 서브쿼리가 없음.

 PRIMARY

 서브쿼리를 이용할 경우 서브쿼리의 부모가 되는 select 쿼리

 union을 사용할 경우 union 의 첫번째 select 쿼리.

 UNION

 union을 사용한 쿼리에서 첫번째를 제외한 나머지 select 쿼리.

 DEPENDENT UNION

 UNION과 기본적으로 동일하나 외부쿼리에 영향을 받음

 UNION RESULT

 UNION 쿼리의 결과

 UNCACHEABLE UNION

 UNION과 기본적으로 동일하나 공급되는 모든 값에 대해 UNION 쿼리를 재처리

 SUBQUERY

 서브쿼리 또는 서브쿼리를 구성하는 첫번째 select 구문

 DEPENDENT SUBQUERY

 SUBQUERY와 기본적으로 동일하나 외부쿼리에 영향을 받음
 UNCACHEABLE SUBQUERY SUBQUERY와 기본적으로 동일하나 입력 값에 의한 캐싱을 이용할 수 없음


3. table


테이블명. 약칭(Alias)을 사용할 경우 약칭이 표시됨


4. type


단일 테이블만 사용된 쿼리일 경우 : 테이블 access 형태

두 개 이상의 테이블이 조인된 SQL 일 경우 : 테이블 간의 조인 형태

아래 목록의 순서는 성능이 좋은 것 부터 나열되어 있음 


 속성값

 내용

 sytem

 테이블에 row가 1건이라 매칭되는 row도 1건인 경우.

 const

 옵티마이저가 unique/primary key를 사용하여 매칭되는 row가 1건인 경우.

 eq_ref

 1:1의 join 관계

 unique/primary key를 사용하여 join을 처리함.

 ref

 1:n의 join 관계

 non-unique 인덱스가 사용되거나, 복합키로 구성된 인덱스 중, 

 일부 컬럼만 이용하여 조인될 경우

 ref_or_null

 ref와 동일하나 null 값에 대한 최적화가 되어있음.

 fulltext

 fulltext 인덱스를 사용

 index_merge

 동일한 테이블에서 두개 이상의 인덱스가 동시에 사용됨.(fulltext 인덱스는 제외)
 unique_subquery 서브쿼리에서 unique한 값이 생성되는 경우
 index lookup function이 사용됨.(서브쿼리 최적화)
 index_subquery unique_subquery와 비슷하나 결과값이 unique하지 않은 경우
 range 주어진 범위내의 row를 스캔함
 범위내의 row가 많으면 많을수록 성능이 저하됨
 index 인덱스를 사용하긴 하나 전체 인덱스 block을 스캔함.
 즉 인덱스를 사용하긴 하나 all 타입과 흡사함
 all 전체 데이터 block을 스캔.(full table scan)


5. possible_keys

옵티마이저가 쿼리 처리를 위해 고려한 인덱스 후보. 즉 사용가능한 인덱스들의 리스트.

possible_keys와 key값은 항상 같지 않다. 즉 옵티마이저가 인덱스를 고려했지만 사용하지 않을수도 있음.


6. key

옵티마이저가 실제로 사용한 인덱스 키.

type값이 index_merge 일때 key값은 사용된 모든 인덱스 키를 출력함.



7. key_len

옵티마이저가 사용한 인덱스 키의 길이값. key컬럼에서 인덱스가 언급되지 않았다면 null값.

key_len값으로 옵티마이저가 실제 복수 컬럼키중 얼마나 많은 부분을 사용할 것인지 알수 있다.


8. ref

행을 추출하는데 키와 함께 사용된 컬럼이나 상수값.



9. rows

쿼리를 수행하기 위해 검색해야 할 row의 개수. 인덱스와 조건을 최적화 해서 row의 개수를 줄이면 줄일수록 퍼포먼스가 향상됨.



10. extra

옵티마이저가 쿼리를 해석한 추가적인 정보를 출력함





위쪽에 정리된 표를 바탕으로 EXPLAIN 실행결과



1번째 row를 해석하면


id가 1 (쿼리의 실행 순서가 1번째임)

SIMPLE (단순 select 구문)

테이블명은 dept 테이블을 alias로 명시한 a로 표시

const (옵티마이저가 unique/primary key를 사용하여 매칭되는 row가 1건인 경우 표시됨, a.deptno = 20으로 명시했으므로)

옵티마이저가 쿼리 처리를 위해 고려한 인덱스 후보는 PRIMARY

옵티마이저가 실제로 사용한 인덱스 키 PRIMARY

옵티마이저가 사용한 인덱스 키의 길이값 4

행을 추출하는데 키와 함께 사용된 컬럼이나 상수 4

쿼리를 수행하기 위해 검색해야 할 row의 개수 1


2번째 row를 해석하면


id가 1 (쿼리의 실행 순서가 1번째임)

SIMPLE (단순 select 구문)

테이블명은 emp 테이블을 alias로 명시한 b로 표시

ALL 전체 데이터 block을 스캔.(full table scan)

쿼리를 수행하기 위해 검색해야 할 row의 개수 14

Using where : where절이 다음 조인에 사용될 row나 출력될 row의 개수를 제한하는 경우 나온다.



'전체 > MySQL' 카테고리의 다른 글

MySQL function 만들기  (3) 2019.07.17
MySQL 계층형 쿼리 구현하기  (13) 2019.07.16
ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유  (0) 2018.10.29
group by 이해하기  (0) 2018.10.24
where case문, or문 사용하기  (1) 2018.06.20


1.. Compare ANSI and Conventional(non-ANSI)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Compare ANSI and Conventional
-- 1. ANSI Syntax
SELECT COUNT(*)
  FROM t1 a
 INNER JOIN t2 b
    ON a.cmpn_no = b.cmpn_no
 INNER JOIN t3 c
    ON b.cmpn_no = c.cmpn_no
 WHERE b.rgstr_id = 'abc'
   AND c.pimg_file_nm IS NOT NULL
;
-- 2. Conventional syntax
SELECT COUNT(*) 
  FROM t1 a
     , t2 b
     , t3 c
 WHERE a.cmpn_no = b.cmpn_no
   AND b.cmpn_no = c.cmpn_no
   AND b.rgstr_id = 'abc'
   AND c.pimg_file_nm IS NOT NULL
;
-- returns same results
cs



2. ANSI inner join 안 조건, 밖 조건


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 조건의 위치가 달라질때
-- 1. inner join 안 조건
SELECT COUNT(*)
  FROM t1 a
 INNER JOIN t2 b
    ON a.cmpn_no = b.cmpn_no
   AND b.rgstr_id = 'abc'       -- a,b inner join 다음 조건1
 INNER JOIN t3 c
    ON b.cmpn_no = c.cmpn_no  -- b,c inner join 다음 조건2
   AND c.pimg_file_nm IS NOT NULL
;
-- 2. inner join 밖 조건
SELECT COUNT(*)
  FROM t1 a
 INNER JOIN t2 b
    ON a.cmpn_no = b.cmpn_no
 INNER JOIN t3 c
    ON b.cmpn_no = c.cmpn_no
 WHERE b.rgstr_id = 'abc'          -- a,b b,c inner join 다음 조건1
   AND c.pimg_file_nm IS NOT NULL -- 다음 조건2
;
-- returns same results
cs



ANSI 조인과 non-ANSI 조인의 결과는 같았다.


ANSI조인을 사용하는 이유는 


1. JOIN 절을 사용하면 관계 논리가 필터 논리 (WHERE)와 분리되므로 더 명확하고 이해하기 쉽다.

2. 외부 조인 구문 (+ 사용)이 모호하고 쿼리 결과가 구현에 따라 달라 지거나 쿼리를 전혀 해석 할 수없는 경우가 있다.

3. 우발적 인 교차 결합을 피할 수 있다.


ANSI JOIN 사용하는 것이 그렇지 않은 syntax를 사용하는 것 보다 안전하다.



'전체 > MySQL' 카테고리의 다른 글

MySQL 계층형 쿼리 구현하기  (13) 2019.07.16
SQL EXPLAIN 정리  (0) 2018.11.19
group by 이해하기  (0) 2018.10.24
where case문, or문 사용하기  (1) 2018.06.20
쿼리에서 select해서 update하기, 쿼리에서 select한 값을 list로 받아와서 update하기  (0) 2018.06.07


group by 이해하기


응모자 테이블(pr_evnt_tkpt)에 

한 사람(ec_cust_no)이 같은 이벤트번호(cmpn_no)로 여러번 응모가 가능하다.


ex) pr_evnt_tkpt 전체 데이터가 다음과 같이 들어가 있다.


1
2
3
4
5
6
ptcp_no   cmpn_no   ec_cust_no 
   1         1         E100
   2         1         E100
   3         1         E100
   4         2         E200
   5         2         E300
cs

쿼리 1.

1
2
3
4
select * from pr_evnt_tkpt a
 where a.cmpn_no = '1'
   and a.ec_cust_no = 'E100'
;
cs


결과 1.


1
2
3
4
ptcp_no   cmpn_no   ec_cust_no 
   1         1         E100
   2         1         E100
   3         1         E100
cs

쿼리 2.

1
2
3
4
5
-- 각각의 이벤트에 총 응모한 횟수
select a.cmpn_no, count(*)
  from pr_evnt_tkpt a
  group by a.cmpn_no
;
cs

결과 2.

1
2
3
cmpn_no  count 
   1       3
   2       2
cs

쿼리 3.

1
2
3
4
5
-- 각각의 이벤트에 한 회원이 응모한 횟수
select a.cmpn_no, a.ec_cust_no, count(*) 
  from pr_evnt_tkpt a 
  group by a.cmpn_no, a.ec_cust_no 
;
cs

결과 3.


1
2
3
4
cmpn_no  ec_cust_no  count 
   1        E100       3
   2        E200       1
   2        E300       1
cs



다음과 같이 카운트 값을 확인 할 수 있었다.



'전체 > MySQL' 카테고리의 다른 글

SQL EXPLAIN 정리  (0) 2018.11.19
ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유  (0) 2018.10.29
where case문, or문 사용하기  (1) 2018.06.20
쿼리에서 select해서 update하기, 쿼리에서 select한 값을 list로 받아와서 update하기  (0) 2018.06.07
MySQL 부등호 비교, 만 나이 구하는 쿼리  (0) 2018.05.15

+ Recent posts

티스토리툴바