꿈꾸는 개발자, DBA 커뮤니티 구루비


//테스트 DB 버전 정보

MariaDB [employees]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.1.21-MariaDB |
+-----------------+
1 row in set (0.00 sec)

7.1 성능향상 - MariaDB , MySQL Enterprise

7.1.1 스레드 풀(Thread Pool)

7.1.1.1 MySQL 서버의 전통적인 연결 및 처리방식

1(Client):1(Thread) 방식

단점 : 스레드가 많아질 수록 서버내 자원에 대한 병목 현상 발생
이를 보안하기 위해서 Concurrent Thread 수를 낮추어 컨텍스트 스위치를 줄이는 위해 도입된 방법이 Thread Pool 입니다.

7.1.1.2 MariaDB의 스레드 풀

5.1 버전부터 기능 구현, 10.X 이후로 계속적으로 개선되고 있음

MariaDB의 Thread Pool의 장점

자동 스레드 개수조절
스레드풀 자체 관리 비용 낮음
운영체제에서 지원하는 스레드 방식 구현, 아닐 경우 IO 멀티 플렉싱 방법 사용
스레드 최대개수 제한으로 자원사용 제한

윈도우 (네이티브 스레드 풀)

thread_pool_size 시스템 변수 지원 안됨
유닉스 (직접 스레드풀) 구현
threadpool_min_threads 시스템 변수 형식적으로만 존재

유저 → 커넥션 정보 → 스레드풀(여유 스레드 확인,없으면 새로 생성) → 할당 → 정상 접속 및 질의 수행

성능향상 : CPU 바운드 작업인 경우
성능저하 : 쿼리요청이 늘어나다 다시 부하가 거의 없는 경우

7.1.1.3 MariaDB 스레드 풀의 사용과 튜닝

우선 , Thead_handling 시스템변수 값을 one-thread-per-connection 에서 pool of threads로 변경

7.1.1.3.1 윈도우즈 스레드 풀 관련 시스템 변수 설정

thread_pool_min_threads = 최소 스레드 개수
thread_pool_max_threads = 최대 스레드 개수

7.1.1.3.2 유닉스 계열의 스레드 풀 관련 시스템 변수 설정

thread_pool_size = 스레드 그룹개수 , MariaDB 5.5에서만 제공

thread_pool_stall_limit = 스레드풀이 하나도 없을 경우 대기시간 설정변수(ms), MariaDB 5.5만 제공

thread_pool_max_threads = 최대 스레드 개수

thread_pool_idle_timeout = IDLE 상태 스레드 개수를 줄이기 위한 대기 시간,MariaDB 5.5에서만 제공

thread_pool_oversubscribe = 인터널 파라미터 , MariaDB 5.5에서만 제공

커넥션이 많은 경우 설정 파라미터

extra_port = 관리자용 별도 포트

extra_max_connections = 최대 몇개의 커넥션을 연결할지 정의하는 파라미터

스레드풀 상태확인(Monitoring) 파라미터

threadpool_threads 스레드풀에 있는 스레드 개수

threadpool_idle_threads 남아있는 스레드 개수

7.1.1.4 주의 사항

스레드풀 활성화 시 스레드 캐시 비활성화 된다.
스레드풀을 다 사용할 경우 error log에 ERROR발생을 주기적으로 확인해야 한다.

7.2 관리 및 진단

7.2.1 show explain for <thread=id>

MariaDB 10.0부터 스레드의 쿼리 실행계획 확인 가능

  
show processlist 

MariaDB [employees]> show processlist;
+----+------+--------------------+-----------+---------+------+-------+------------------+----------+
| Id | User | Host               | db        | Command | Time | State | Info             | Progress |
+----+------+--------------------+-----------+---------+------+-------+------------------+----------+
| 13 | root | 192.168.56.1:49182 | employees | Sleep   |    0 |       | NULL             |    0.000 |
| 14 | root | localhost          | employees | Query   |    0 | init  | show processlist |    0.000 |
+----+------+--------------------+-----------+---------+------+-------+------------------+----------+
2 rows in set (0.00 sec)

show explain for <해당 번호>; explain 명령 실행계획은 옵티마이저가 구조와 통계정보를 기반으로 예뮬레이션에 보여주는 내용이라면 show explain은 실제 수행되는 상태의 실행계획이다.


MariaDB [employees]> show processlist;
+----+------+--------------------+-----------+---------+------+--------------+----------------------------------+----------+
| Id | User | Host               | db        | Command | Time | State        | Info                             | Progress |
+----+------+--------------------+-----------+---------+------+--------------+----------------------------------+----------+
| 13 | root | 192.168.56.1:49182 | employees | Query   |    3 | Sending data | select * from employees,salaries |    0.000 |
| 14 | root | localhost          | employees | Query   |    0 | init         | show processlist                 |    0.000 |
+----+------+--------------------+-----------+---------+------+--------------+----------------------------------+----------+
2 rows in set (0.00 sec)

MariaDB [employees]> show explain for 13;
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |  299113 |       |
|    1 | SIMPLE      | salaries  | ALL  | NULL          | NULL | NULL    | NULL | 2837904 |       |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------+
2 rows in set, 1 warning (0.03 sec)
 
MariaDB [employees]> show explain for 13;
ERROR 1933 (HY000): Target is not running an EXPLAINable command

7.2.2 슬로우 쿼리 로그에 실행계획 출력

MariaDB 10.0.5 버전부터 슬로우쿼리 실행시간 + 실행계획 출력가능

log_slow_verbosity 시스템 변수 옵션

microtime : 각종정보를 마이크로 초 단위 표시

query_plan : SELECT 쿼리 실행계획 간략화 표시

full : 모든 옵션을 표시함

explain : 10.0.5 부터 추가된 옵션 , explain 결과 모두 기록하게 함

참고사이트 : http://m.blog.naver.com/jevida/220357383281

7.2.3 구조화된 실행 계획 출력


MariaDB [employees]> explain format=JSON
    -> select count(*) from employees e,dept_emp de where de.emp_no=e.emp_no and e.last_name like '%M%';
----------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
----------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "e",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "rows": 299113,
      "filtered": 100,
      "attached_condition": "(e.last_name like '%M%')"
    },
    "table": {
      "table_name": "de",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "emp_no"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["emp_no"],
      "ref": ["employees.e.emp_no"],
      "rows": 1,
      "filtered": 100,
      "using_index": true
    }
  }
} |
----------------------------------------------------------------------+

7.2.4 스레드 단위의 메모리 사용량

MariaDB 10.0 부터 show status 명령어에 Memory_used 란 항목이 추가됨
show global status 명령어는 전체 메모리 사용량을 의미함


MariaDB [(none)]> show status like '%Memory%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Innodb_read_views_memory | 192      |
| Memory_used              | 67464    |
| Qcache_free_memory       | 33429488 |
+--------------------------+----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> show global status like '%Memory%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| Innodb_read_views_memory | 192       |
| Memory_used              | 207512784 |
| Qcache_free_memory       | 33429488  |
+--------------------------+-----------+
3 rows in set (0.01 sec)

모든 스레드의 메모리 사용량을 확인하고 싶다면 INFORMATION_SCHEMA 데이터베이스로 접속해서 processlist 테이블 조회하면 된다.


MariaDB [(none)]> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [information_schema]> select id,user,host,memory_used from processlist;
+----+------+--------------------+-------------+
| id | user | host               | memory_used |
+----+------+--------------------+-------------+
| 16 | root | localhost          |       84584 |
| 15 | root | 192.168.56.1:49183 |       67464 |
+----+------+--------------------+-------------+
2 rows in set (0.00 sec)

MariaDB [information_schema]> select sum(memory_used) from processlist;
+------------------+
| sum(memory_used) |
+------------------+
|           152048 |
+------------------+
1 row in set (0.00 sec)

7.2.5 SHUTDOWN 명령어

MariaDB 10.0.4 부터는 원격지에서 shutdown 명령어 사용 가능

7.2.6 사용자나 쿼리 실행 강제 종료(KILL)

특정 커넥션 접속 종료 : KILL CONNECTION 스레드 아이디


MariaDB [information_schema]> show processlist;
+----+------+--------------------+--------------------+---------+------+-------+------------------+----------+
| Id | User | Host               | db                 | Command | Time | State | Info             | Progress |
+----+------+--------------------+--------------------+---------+------+-------+------------------+----------+
| 15 | root | 192.168.56.1:49183 | employees          | Sleep   | 2199 |       | NULL             |    0.000 |
| 16 | root | localhost          | information_schema | Query   |    0 | init  | show processlist |    0.000 |
+----+------+--------------------+--------------------+---------+------+-------+------------------+----------+
2 rows in set (0.00 sec)

MariaDB [information_schema]> kill connection 15;
Query OK, 0 rows affected (0.03 sec)

MariaDB [information_schema]> show processlist;
+----+------+-----------+--------------------+---------+------+-------+------------------+----------+
| Id | User | Host      | db                 | Command | Time | State | Info             | Progress |
+----+------+-----------+--------------------+---------+------+-------+------------------+----------+
| 16 | root | localhost | information_schema | Query   |    0 | init  | show processlist |    0.000 |
+----+------+-----------+--------------------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

특정 커넥션의 쿼리 강제 종료 : KILL QUERY 스레드 아이디

특정 커넥션의 쿼리를 쿼리 아이디로 강제 종료 : KILL QUERY ID 쿼리ID값

SELECT * FROM PROCESSLIST; 컬럼 QUERY_ID 조회 후 KILL QUERY ID 수행

(KIIL QUERY 와 KILL QUERY ID 차이 : 전자 모든 실행중인 쿼리 종료,후자 특정 쿼리만 종료)

특정 유저의 커넥션이나 쿼리 강제 종료 : KILL CONNECTION USER '유저명' OR '유저명@%';

KILL QUERY USER '유저명' OR '유저명@%';

강제종료의 수준 조절 : KILL HARD

SOFT QUERY 쿼리ID ;

7.2.7 GET DIAGNOSTICS

MySQL 5.6 and MariaDB 10.0.x 에러 발생 시 에러번호 , SQLSTATE 그리고 에러 메시지 스토어드 프로그램에서 참조할 수 있는 기능이다.

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)

h2 .7.4 파티션

7.4.1. 명시적 파티션 지정

다음 SQL문에 직접 파티션 명시 가능
SELECT
DELETE
INSERT
REPLACE
UPDATE
LOAD DATA
LOAD XML

FROM 절 뒤 테이블명 뒤에 PARTITION 절을 명시하고 해당 파티션을 입력한다.

7.4.1.1 명시적 파티션 지정 사용법


MariaDB [employees]> CREATE TABLE tb_partition_employees(
    -> emp_no int NOT NULL,
    -> birth_date date NOT NULL,
    -> first_name varchar(14) NOT NULL,
    -> last_name varchar(16) NOT NULL,
    -> gender enum('M','F') NOT NULL,
    -> hire_date date NOT NULL
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE COLUMNS(hire_date)(
    -> PARTITION p0 VALUES LESS THAN ('1990-01-01'),
    -> PARTITION p1 VALUES LESS THAN ('2000-01-01'),
    -> PARTITION p2 VALUES LESS THAN ('2010-01-01')
    -> );
Query OK, 0 rows affected (0.49 sec)

MariaDB [employees]> Insert Into tb_partition_employees SELECT * FROM employees;
Query OK, 300024 rows affected (13.55 sec)
Records: 300024  Duplicates: 0  Warnings: 0

MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees;
+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (1.14 sec)


MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p0);
+----------+
| COUNT(*) |
+----------+
|   164797 |
+----------+
1 row in set (0.55 sec)

MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p1);
+----------+
| COUNT(*) |
+----------+
|   135214 |
+----------+
1 row in set (0.50 sec)

MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p1,p2);
+----------+
| COUNT(*) |
+----------+
|   135227 |
+----------+
1 row in set (0.31 sec)

MariaDB [employees]> insert into tb_partition_employees PARTITION(p0) values (1,'1984-01-12','Matt','Lee','M','2009-01-01');
ERROR 1748 (HY000): Found a row not matching the given partition set
MariaDB [employees]> select * from tb_partition_employees where emp_no=10001;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.90 sec)

MariaDB [employees]> select * from tb_partition_employees PARTITION(p2) where emp_no=10001;
Empty set (0.00 sec)

MariaDB [employees]>  update tb_partition_employees PARTITION(p2) set gender='F' where emp_no=10001;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

MariaDB [employees]>  update tb_partition_employees PARTITION(p0) set gender='F' where emp_no=10001;
Query OK, 1 row affected (0.89 sec)
Rows matched: 1  Changed: 1  Warnings: 0

7.4.1.2 명시적 파티션 지정 기능의 용도

명시적인 파티션 지정 기능은 옵티마이저가 파티션 프루닌ㅇ을 제대로 처리하지 못할 떄 명시적으로 사용할 파티션을 지정해 주는 용도로 사용할 수 있다.


MariaDB [employees]> create function getDate()
    -> RETURNS DATE
    -> NOT DETERMINISTIC --> 파티션 프루닝기능 제거역활
    -> RETURN '2000-01-02';
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> explain partitions 
    -> select * from tb_partition_employees where hire_date=getDate()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_partition_employees
   partitions: p0,p1,p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299356
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [employees]> explain partitions
    -> select * from tb_partition_employees PARTITION(p2) where hire_date=getDate()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_partition_employees
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 13
        Extra: Using where
1 row in set (0.01 sec)

7.4.2 파티션 테이블 스페이스 교체(Exchange)

MySQL 5.6 과 MariaDB 10.0 추가 기능
Exchange 기능 제약사항
파티션 테이블과 스와핑할 테이블 구조가 동일
스와핑을 할 테이블은 임시테이블이면 안됨
스와핑될 테이블은 파티션 조건을 만족해야한다.(파티션 범위)
스와핑할 두 테이블은 다른 테이블들과 참조관계가 없어야한다.(Foreign Key)
해당 테이블에 대한 INSERT , ALTER , DROP , CREATE 권한 필요


MariaDB [employees]> create table tb_swap(
    -> emp_no int NOT NULL,
    -> birth_date date NOT NULL,
    -> first_name varchar(14) NOT NULL,
    -> last_name varchar(16) NOT NULL,
    -> gender enum('M','F') NOT NULL,
    -> hire_date date NOT NULL
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

MariaDB [employees]> insert into tb_swap values(1,'1985-01-12','Matt','Lee','M','1989-10-10');
Query OK, 1 row affected (0.01 sec)

MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p0);
+----------+
| COUNT(*) |
+----------+
|   164797 |
+----------+
1 row in set (0.36 sec)

MariaDB [employees]> select count(*) from tb_swap;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

MariaDB [employees]> alter table tb_partition_employees EXCHANGE PARTITION p0 WITH TABLE tb_swap;
Query OK, 0 rows affected (0.14 sec)


MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p0);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

MariaDB [employees]> select count(*) from tb_swap;
+----------+
| count(*) |
+----------+
|   164797 |
+----------+
1 row in set (0.36 sec)

MariaDB [employees]> alter table tb_partition_employees EXCHANGE PARTITION p1 WITH TABLE tb_swap;
ERROR 1737 (HY000): Found a row that does not match the partition

08. 기타 기능 PART1(43p, 468 ~ 511) (by wooriwil) [2017.06.23]