Real MariaDB (2017년)
목록
개발 생산성 0 0 49,988

by 구루비스터디 MariaDB 개발 생산성 가상 칼럼 동적 칼럼 LIMIT ROWS EXAMINED [2019.08.11]

7.3 개발 생산성

7.3.1 LIMIT ROWS EXAMINED

  • 레코드의 건수가 넘어서게 되면 쿼리를 중지할 수 있게 하는 기능

MariaDB [employees]> select * from employees where last_name ='Sudbeck' LIMIT ROWS EXAMINED 100;
Empty set, 1 warning (0.00 sec)

MariaDB [employees]> 
MariaDB [employees]> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1931
Message: Query execution was interrupted. The query examined at least 101 rows, which exceeds LIMIT ROWS EXAMINED (100). The query result may be incomplete.
1 row in set (0.00 sec)

--

Message: Query execution was interrupted. The query examined at least 101 rows, which exceeds LIMIT ROWS EXAMINED (100). The query result may be incomplete.
1 row in set (0.00 sec)

MariaDB [employees]> select count(*) from dept_emp where dept_no='d001' LIMIT ROWS EXAMINED 20000;
Empty set, 1 warning (0.45 sec)

MariaDB [employees]> select count(*) from dept_emp where dept_no='d002' LIMIT ROWS EXAMINED 20000;
+----------+
| count(*) |
+----------+
|    17346 |
+----------+
1 row in set (0.14 sec)


  • 위 명령어의 판정 건수는 클라이언트로 최종 전송되는 건수를 의미하는게 아니라 사용자가 요청한 결과를 만들어 내기 위해서 MariaDB서버가 내부적으로 핸들링한 레코드의 건수를 의미한다.


7.3.2 DELETE ... RETURNING ...

  • 삭제된 레코드를 다시 가져오는 방법 RETURNING 절 뒤에는 집합 함수나 서브 쿼리를 사용할 수 없다.
 
MariaDB [employees]>  insert into employees values (1, '1985-01-21','Matt','Lee','M','2014-1.18');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MariaDB [employees]> delete from employees where first_name = 'Matt' and last_name = 'Lee' RETURNING emp_no , first_name, last_name;

+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|      1 | Matt       | Lee       |
+--------+------------+-----------+
1 row in set (2.32 sec)


7.3.3 마이크로 초 단위의 시간 저장

  • MariaDB 5.3 버전부터 DATETIME 타입에 0 , 3, 6 숫자를 부여하여 밀리초 , 마이크로초를 저장할 수 있다.

MariaDB [employees]> create table tb_microsecond(fd1 DATETIME(0) , fd2 DATETIME(3) , fd3 DATETIME(6));
Query OK, 0 rows affected (0.15 sec)

MariaDB [employees]> insert into tb_microsecond VALUES (NOW(6),NOW(6),NOW(6));
Query OK, 1 row affected (0.04 sec)

MariaDB [employees]> select * from tb_microsecond
    -> ;
+---------------------+-------------------------+----------------------------+
| fd1                 | fd2                     | fd3                        |
+---------------------+-------------------------+----------------------------+
| 2017-06-22 15:48:11 | 2017-06-22 15:48:11.954 | 2017-06-22 15:48:11.954716 |
+---------------------+-------------------------+----------------------------+
1 row in set (0.00 sec)

--

MariaDB [employees]> select UNIX_TIMESTAMP(now(6));
+------------------------+
| UNIX_TIMESTAMP(now(6)) |
+------------------------+
|      1498114169.195263 |
+------------------------+
1 row in set (0.00 sec)

MariaDB [employees]> select now(6),date_sub(now(6),interval 10000 MICROSECOND);
+----------------------------+---------------------------------------------+
| now(6)                     | date_sub(now(6),interval 10000 MICROSECOND) |
+----------------------------+---------------------------------------------+
| 2017-06-22 15:50:03.740734 | 2017-06-22 15:50:03.730734                  |
+----------------------------+---------------------------------------------+
1 row in set (0.00 sec)


7.3.4 DATETIME 타입의 기본값 설정

  • MySQL , MariaDB 5.5 부터는 TIMESTAMP 타입 현재시간을 기본값으로 설정가능
  • DATETIME은 불가능 , MariaDB 10.0 부터 DATETIME 타입도 현재시간을 기본값으로 가질 수 있다. (CURRENT_TIMESTAMP)
 
MariaDB [employees]> create table tb_datetime(
    -> fd1 INT PRIMARY KEY,
    -> fd2 DATETIME DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.32 sec)

MariaDB [employees]> insert into tb_datetime (fd1) values (1);
Query OK, 1 row affected (0.04 sec)

MariaDB [employees]> select * from tb_datetime;
+-----+---------------------+
| fd1 | fd2                 |
+-----+---------------------+
|   1 | 2017-06-22 15:51:33 |
+-----+---------------------+
1 row in set (0.00 sec)


7.3.5 정규 표현식 기능 확장

  • MySQL 5.5 / 5.6 POSIX 호환 정규 표현식 라이브러리 사용
  • MariaDB 10.0.5 PCRE 정규 표현식 라이브러리 사용


REGEXP_REPLACE (문자열 , 정규표현식 , 대체문자열) - 변환

MariaDB [employees]> select regexp_replace('abc123def','[0-9]','*') as change_number;
+---------------+
| change_number |
+---------------+
| abc***def     |
+---------------+
1 row in set (0.00 sec)


REGEXP_INSTR(문자열 , 정규표현식) - 위치 리턴

MariaDB [employees]> select regexp_instr('KOREAN','N') as return_num;
+------------+
| return_num |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)


REGEXP_SUBSTR(문자열 , 정규표현식) - 일치 문자열 가져오기
 
 MariaDB [employees]> select regexp_substr('ABC123DEF','[A-Z]+');
+-------------------------------------+
| regexp_substr('ABC123DEF','[A-Z]+') |
+-------------------------------------+
| ABC                                 |
+-------------------------------------+
1 row in set (0.00 sec)


7.3.6 가상(Virtual) 컬럼

  • 다른 컬럼에 의해서 자동으로 설정되는 기능을 의미함.
제약사항
가상 컬럼의 표현식은 252자 내외
서브 쿼리와 같이 외부 테이블의 데이터를 참조하는 표현식은 사용 불가능
사용자정의 함수와 스토어드 함수 , not-determinstic 함수(내장함수)를 이용한 표현식에 사용불가
상수 표현식은 가상컬럼의 표현식으로 불가
가상컬럼의 표현식에 다른 가상컬럼은 사용 불가


VIRTUAL
해당 기능 지원 스토리지 엔진 : InnoDB , Aria , MyISAM , CONNECT 스토리지
인덱스 생성 불가 , SELECT 쿼리 시만 내부적 계산과정 수행 , ALTER TABLE MODIFY , CHANGE 등 수행불가



MariaDB [employees]> CREATE TABLE tb_virtual_emp (
    -> emp_no int(11) NOT NULL,
    -> birth_date date NOT NULL,
    -> first_name varchar(14) NOT NULL,
    -> birth_month TINYINT AS (MONTH(birth_date)) VIRTUAL,
    -> PRIMARY KEY(emp_no),
    -> KEY ix_firstname(first_name)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)

MariaDB [employees]> insert into tb_virtual_emp (emp_no,birth_date,first_name)
    -> select emp_no,birth_date,first_name from employees;
Query OK, 300024 rows affected (15.56 sec)
Records: 300024  Duplicates: 0  Warnings: 0

select * from tb_virtual_emp;
...
|  15025 | 1952-12-03 | Vincent        |          12 |
|  15026 | 1963-12-12 | Geoffrey       |          12 |
|  15027 | 1959-04-17 | Sudhanshu      |           4 |
|  15028 | 1963-07-25 | Toney          |           7 |
|  15029 | 1954-04-08 | Giordano       |           4 |
|  15030 | 1954-02-21 | Rasikan        |           2 |
...


PERSISTENT
MariaDB 모든 엔진에서 사용가능 , 인덱스 생성 가능 , ALTER TABLE MODIFY , CHANGE 등 사용가능


7.3.7 동적(Dynamic) 칼럼

  • NoSQL 형태의 데이터 저장 및 접근을 위해서 동적 칼럼 기능을 제공함.
  • 하나의 대용량 컬럼 정의하고, 그 칼럼을 여러 개의 임의 칼럼으로 정의하여 사용가능


동적 칼럼 함수
COLUMN_CREATE : 컬럼명 , 컬럼값 지정하여 생성
COLUMN_ADD : 기존 동적컬럼에 새로운 논리적 컬럼 추가
COLUMN_GET : 동적 컬럼의 논리 컬럼의 값을 가져오는 함수
COLUMN_DELETE : 논리 동적 컬럼 제거
COLUMN_EXISTS : 지정된 이름의 논리 동적 컬럼이 존재하는지 체크
COLUMN_LIST : 동적 컬럼이 가지고 있는 논리 동적 컬럼 목록 추출
COLUMN_CHECK : 오류없이 정상 패키징 되었는지 확인하는 함수
COLUMN_JSON : JSON 포맷으로 반환



MariaDB [employees]> create table tb_dynamic_emp(
    -> emp_no INT NOT NULL,
    -> dyna_cols BLOB,
    -> PRIMARY KEY (emp_no));
Query OK, 0 rows affected (0.36 sec)

MariaDB [employees]> INSERT INTO tb_dynamic_emp(emp_no , dyna_cols) VALUES (10001,COLUMN_CREATE('birth_date','1983-05-07','first_name','Dong-Hoon','last_name','Lee','gender','M','hire_date','2011-06-01'));
Query OK, 1 row affected (0.04 sec)
 
MariaDB [employees]> select * from tb_dynamic_emp\G
*************************** 1. row ***************************
   emp_no: 10001
dyna_cols:  ,     #  " nderhire_datelast_namebirth_datefirst_name!M!2011-06-01!Lee!1983-05-07!Dong-Hoon
1 row in set (0.00 sec)


MariaDB [employees]> update tb_dynamic_emp SET dyna_cols=COLUMN_ADD(dyna_cols,'country','Korea');
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [employees]> select emp_no,COLUMN_GET(dyna_cols,'country' as CHAR) as v_country from tb_dynamic_emp;
+--------+-----------+
| emp_no | v_country |
+--------+-----------+
|  10001 | Korea     |
+--------+-----------+
1 row in set (0.00 sec)


MariaDB [employees]> update tb_dynamic_emp SET dyna_cols=COLUMN_DELETE(dyna_cols,'country');
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [employees]> select emp_no,COLUMN_EXISTS(dyna_cols,'country') as existence from tb_dynamic_emp;
+--------+-----------+
| emp_no | existence |
+--------+-----------+
|  10001 |         0 |
+--------+-----------+
1 row in set (0.00 sec)

MariaDB [employees]> select emp_no,column_list(dyna_cols) as col_list from tb_dynamic_emp;
+--------+------------------------------------------------------------+
| emp_no | col_list                                                   |
+--------+------------------------------------------------------------+
|  10001 | `gender`,`hire_date`,`last_name`,`birth_date`,`first_name` |
+--------+------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [employees]> select emp_no,COLUMN_CHECK(dyna_cols) as is_valid from tb_dynamic_emp;
+--------+----------+
| emp_no | is_valid |
+--------+----------+
|  10001 |        1 |
+--------+----------+
1 row in set (0.00 sec)

MariaDB [employees]> select emp_no,COLUMN_JSON(dyna_cols) as is_valid from tb_dynamic_emp;
+--------+--------------------------------------------------------------------------------------------------------------+
| emp_no | is_valid                                                                                                     |
+--------+--------------------------------------------------------------------------------------------------------------+
|  10001 | {"gender":"M","hire_date":"2011-06-01","last_name":"Lee","birth_date":"1983-05-07","first_name":"Dong-Hoon"} |
+--------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


"구루비 데이터베이스 스터디모임" 에서 2017년에 "Real MariaDB" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4211

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입