'분류 전체보기'에 해당되는 글 380건

일하다보면 사업부서의 요청으로 통계정보를 추출해달라는 요청을 많이 받는다.
DBA가 없는 회사는 개발자들이 직접 작업하는 경우가 많고 이게 업무에 생각보다 많은 부분을 차지하고 있다.

SAS나 SAP이 있는 회사라면 그걸 쓰면 될텐데.. 이건 너무 비싸고 그렇다고
별도의 데이터 마트나 샌드박스가 구축은 시간이 오래걸리고 이것도 생각보다 비용이 많이 들어간다.
그리고 결정적으로 SAS 도입을 검토한다고 해서 회의에 참여한 적이 있는데 음.. SAS쪽  사업담당자의

시연을 보면서 들던 생각은  저 정도를 쓰려면 MS의 ACCESS 정도는 문제없이 사용할 정도의 숙련도를 가진 사업담당자야할 거 같고, 교육을 받더라도 이해하고 제대로 사용하기에는 많은 시간이 걸릴 거 같다는 생각이 들었다.

일단 여기도 그렇지만 결국 EXCEL이다.
엑셀은 row수가 98만 건이 넘어가면 쉘을 쪼개던지 아니면 파일을 분리해야 한다.
보통은 피벗이나 엑셀함수로 원하는 정보를 만들텐데.. 데이터가 커지면 무한정 루프를 돌면서 업무지연이 발생한다는 것을 알게되었다.

최대한 요구사항에 맞게 데이터를 가공해서 전달하고 Excel에서 쓰는 함수를 최소화할 수 있게 전달해 주길 원했고
초기 포멧의 row seq 정렬에 맞게 추출해서 주기를 원했다.
결국 Excel이나 DB나 순서정렬이 문제였고, 거기에 맞게 가공하기 위한 작업을 하려면
DB도 대용량 데이터의 JOIN과 order by를 사용해야하고 filesort와 데이터 사이즈가 커지면 temparory memory를 쓸 수 밖에 없다.

다른 것보다 filesort를 쓴다는 것은 옵티마이져가 스캔한 것을 다시 한번 메모리에 올리고 재정렬을 해야하므로 부하가 많이 가는 작업이고, 보통은 DB에 부하를 주지 않게하기위해 out file로 처리해서 데이터를 재가공해서 EXCEL로 전달했다. order by 를 사용하지 않고 정렬이 가능한지 여부를 구글링 해보았는데 대부분 답은 정합성을 보장할 수 없다는 얘기였고, 정말 사용할 수 없는지 테스트 해보기로 했다.

1) MyISAM 테이블 엔진 테스트
일단 테이블 생성하고 DB툴을 이용하여 엑셀이나 CSV 파일을 DB에 import 한다.

CREATE TABLE `TMP_LOAD_20200619` (
  `SEQ_NO` int(10) unsigned NOT NULL COMMENT '시퀀스',
  `ENTER_NO` varchar(12) NOT NULL COMMENT '가입 시퀀스',
  PRIMARY KEY (`SEQ_NO`),
  UNIQUE KEY IDX_LOAD_20200619_01 (`ENTER_NO`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


select * from TMP_LOAD_20200619;
1	500053900208
2	500053900209
3	500053900210
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
...

//50만건 정도 올려보았는데 순서대로 insert 되는 것을 확인했다.
//select 역시 순서대로 나온다.

delete from TMP_LOAD_20200619
where SEQ_NO = 3;
commit;
// 시퀀스 3을 삭제해 보았다.

select * from TMP_LOAD_20200619;
1	500053900208
2	500053900209
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
11	500053900228
...

insert into TMP_LOAD_20200619
VALUES (500001,'500053904383');
commit;
// 500001번째 시퀀스를 가진 데이터를 추가로 넣어보았다.

select * from TMP_LOAD_20200619;

1	500053900208
2	500053900209
500001	500053904383
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
...

MyISAM 엔진의 특성이다.
초기 데이터에서 삭제하면 select하면 보이진 않지만 빈공간은 사라지지 않고 존재하고 새로 입력한 데이터가 거기에 들어간다. 이거를 다시 생각해보면 MyISAM은 한번 입력한 데이터의 정렬은 유지되며 신규로 update나 delete가 일어나지 않는이상 ASC의 정렬은 유지된다고 판단할 수 있다.

다시말하면 초기데이터가 유지된다면 order by 정렬을 사용하지 않아도 된다는 얘기다.

실제로 TMP_LOAD_20200619 테이블을 드라이빙 테이블로 Left Join을 여러개 수행하고 where 조건절로 끊어서 데이터를 가공해서 최종 order by 정렬을 사용한 것과 사용하지 않은 것을 beyond compare로 비교해 보았고
50만건이 100% 일치함을 확인했다.

MyISAM을 사용시 초기데이터가 바뀌지 않는다면 order by 정렬을 빼도 될거 같다.

2) InnoDB 테이블엔진 테스트

CREATE TABLE `TMP_LOAD_20200619` (
  `SEQ_NO` int(10) unsigned NOT NULL COMMENT '시퀀스',
  `ENTER_NO` varchar(12) NOT NULL COMMENT '가입 시퀀스',
  PRIMARY KEY (`SEQ_NO`),
  UNIQUE KEY IDX_LOAD_20200619_01 (`ENTER_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


1	500053900208
2	500053900209
3	500053900210
21	500053900211
22	500053900212
23	500053900213
7	500053900214
8	500053900215
9	500053900216
10	500053900217
..
// 원하는 순서가 아니다.. 뭔가 다르다.

InnoDB는 데이터 정렬하는 방법이 다르다.
테이블의 데이터를 PK값으로 정렬되어 저장하고 pk의 값의 정렬은

실제 데이터에서 보듯이 내가 생각하는 seq_no 순서대로의 순서가 아니고 clustered index로 저장하여 정렬한다.

#클러스터링 인덱스(clustered index)란?
여러개의 인덱스를 하나로 묶는다는 뜻으로 테이블의 PK에 대해서만 적용되는 내용이다.

즉 PK값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.
중요한 것은 PK값에 의해 레코드의 저장 위치가 결정된다는 것이고, 또한 프라이머리 키값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미한다.

#결론

innodb의 PK정렬이 order by 비용을 줄여 빠르다고 하는데, 실제 테스트해 본바로는 select는 대용량 일수록 MyISAM의 순서정렬을 사용하지 않은 쿼리가 훨씬 빨랐다.

데이터 추출작업이 필요할 때 order by를 뺄수 있는 MyISAM 엔진을 적극활용하자.

끝~

지금까지는 클라이언트 쪽에서는 쿼리의 타임아웃을 지정하는 방법이 있었다.

예를들면 Connector/J에서는 접속 시에 enableQueryTimeouts 옵션을 활성화하고,
애플리케이션 내에서 쿼리 실행 전에 java.sql.Statement#seQueryTimeout() 메서드를 사용하여 쿼리의 타임아웃을 지정할 수 있었다.

하지만 이 방법은 Connector/J 측에서 시간의 경과를 감지하고 서버로 접근해서 시간이 걸리는 쿼리에 kill 명령어를 실행하는 방식으로, Connector/J 이외에는 사용할 수 없다.

드리이버의 보조 없이 훨씬 쉽게 어떤 프로그래밍 언어로도 쿼리의 타임아웃을 설정하려면 어떻게 하는 것이 좋을까. 그렇게 하기 위해서는 서버 측에서 쿼리의 타임아웃을 지정할 수 있어야 한다. 그래서 Mysql 5.7에는 서버측에서 쿼리를 타임아웃 시키는 구조가 추가되었다.

타임아웃의 지정은 max_execution_time 이라는 시스템 변수로 한다. 마일스톤 릴리스나 릴리스 후보판에는 select 구문에서 직접 타임아웃을 지정할 수 있는 장치가 있었지만, 정식판에서는 그기능이 삭제되었다. 타임아웃지정은 max_execution_time 시스템 변수로 한다.

--> 슬로우쿼리로 고생했기 때문에 분명 좋은 기능이긴한데 global 변수라 SET 으로 실시간으로 바꿀 수 있는 것도 아니고 바꾸고 재시작해야하고..

OLAP의 경우 개발자가 실행계획 안보고 코딩하면 어느순간 슬로쿼리가 되어있을 테고..

근데 그게 결제쪽이면 강제로 Kill 시키는 기능이 들어가면 문제가 될 거 같은데...

근데 이걸 session 단위로 힌트로 쓸수 있다는 얘기가 있어서 한번 확인해 보았다.

참조 referance:
https://ohgyun.com/767

mysql 레퍼런스를 확인해본다.

dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

#Statement Execution Time Optimizer Hints

MAX_EXECUTION_TIME hint는 단지 select문에서만 허용한다.
서버가 명령문을 종료하기 전에 명렴문을 실행할 수 있는 시간을 N(밀리세컨단위의 시간값)을 지정한다.

MAX_EXECUTION_TIME(N)hint는 실행구문의 timeout을 N 밀리세컨으로 설정한다.
이 옵션이 유효하거나(my.cnf에 설정하였거나) N이 0이면 이 max_execution_time은 시스템 변수의 값이 적용된다.

max_execution_time 힌트를 다음과 같이 적용할 수 있다.

For statements with multiple SELECT keywords, such as unions or statements with subqueries,
MAX_EXECUTION_TIME applies to the entire statement and must appear after the first SELECT
Union 또는 subquery가 포함된 문과 같이 복수의 SELECT 키워드가 있는 문에는 MAX_EXECUTION_TIME은 전체 문장에 적용되며 첫 번째 select 후에 나타나야 한다.

--> 첫번째 실행이후에 두번째 실행하게되면 적용된다는 얘기인가?

테스트해보았는데 첫번째 select에서도 바로 실행된다. 첫번째 select 이라는 게 전체 row를 말하는 것이 아니고 1 row를 의미하는지 모르겠다.

It applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.
읽기 전용 SELECT 문에도 적용된다. 읽기 전용이 아닌 문장은 side effect로 데이터를 수정하는 stored function을 호출하는 구문이다.

It does not apply to SELECT statements in stored programs and is ignored
stored 프로그램이 포함된 select문은 적용되지 않고 무시한다.

--> function, procedure 가 사용된 select문에는 적용되지 않는다.

#테스트

2000만건 정도 있는 테이블을 스캔한다. 평상시 풀스캔이면 한도 없이 조회하고 안나온다. 보통 limit 200으로 끊어서 데이터를 보는데 힌트 믿고 한번 날려보자.

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM TESTDB.TB_xxxx_HIST;

error code)
Error occurred during SQL query execution
이유:
 SQL Error [3024] [HY000]: Query execution was interrupted, maximum statement execution time exceeded

--> 와우... 미라클.. ㅎㅎ 슬로우쿼리 파리미터와 같이 적용하면 query 장애는 안녕인가?

stored 프로그램이 포함된 select문은 적용되지 않고 무시한다.

--> 이 부분이 맘에 걸려서 함수를 추가해서 테스트해보았다.

--서브쿼리추가하고, 함수추가 후 테스트 해보았다.
select /*+ MAX_EXECUTION_TIME(2000) */  
T1.SEQ_NO ,
T2.CHNL_CD,
TESTDB.CODE_NM('xxxx_CD',T1.xxxx_CD) AS CHANNEL_NM,
(select CHANNEL_CD from TESTDB.TB_xxx_ETC where SEQ_NO = T1.SEQ_NO ) AS CARD
from TESTDB.TB_xxxxx T1
INNER JOIN TESTDB.TB_xxxx_HIST T2
ON T1.SEQ_NO = T2.SEQ_NO;

error code)
Error occurred during SQL query execution
이유:
 SQL Error [3024] [HY000]: Query execution was interrupted, maximum statement execution time exceeded

--> 별문제 없이 잘된다. select문에서는 사용해도 될 거 같다.  함수나 프로시저 단독으로 처리시에 확인이 안되는 거 같은데 프로시저 하나 짜고 다음에 다시 테스트해봐야겠다. 일단 select 문안에 컬럼으로 함수를 쓰는 경우는 사용가능하다.

참고로 테스트 버전은 mysql 5.7.19이다.

Mysql 5.7에서 새롭게 추가된 백업 명령어인 mysqlpump는 mysqldump에는 없는 몇 가지 특징을 가지고 있다.

둘은 철자도 아주 비슷하고 백업에 사용한다는 용도 면에서는 아주 비슷하다.
mysqlpump의 특징으로는 다음과 같은 것들이 언급된다.

1) 병렬덤프
2) 테이블, 뷰, 스토어드 프로그램을 개별로 필터링
3) 데이터의 복원과 인덱스 작성을 동시에 하지 않고, 테이터를 복원한 이후부터 실행
4) 사용자 계정의 정보를 권한 테이블에서가 아닌 CREATE USER와 GRANT 명령어로 출력
5) 출력의 압축
6) 백업의 진척 보고

--> 오라클의 imp/exp 가 impdb/expdb 처럼 병렬처리로 빠르게 import/export할 수 있는 mysql dump tool 이 나온 듯하다.

Mysql 5.7 배포당시의 mysqlpump에는 병렬백업 MVCC에 의한 데이터의 동기화가 되지 않는다는 치명적인 결점이 있었다. 그 때문에 병렬 덤프를 실행하려면 변경 작업을 멈춰야 했으므로 mysqldump의 대체품으로 사용할 수 없었다.
이런 제한은 Mysql 5.7.11에서 해제되어, 서버 변경 중에 병렬 덤프를 실행해도 정합성이 있는 백업을 할 수 있게 되었다. InnoDB를 일관되게 백업하려면 mysqldump와 마찬가지로 --single-transaction 옵션을 지정해야한다.
--single-transaction 옵션에서는 InnoDB 이외의 테이블 데이터는 일관성을 보증할 수 없다는 점도 마찬가지다.
mysqlpump는 옵션 등에 차이가 있긴 하지만, mysqldump와 같은 방식으로 사용할 수 있다. 아래표)는 전형적인 mysqlpump의 백업과 복원 명령어이다.
mysql CLI를 사용해 복원한다는 점도 동일하다. mysqlpump는 내부적으로 병렬덤프를 하지만 출력은 하나뿐이다. 기본적으로 표준 출력이 덤프의 출력처가 된다. 그 때문에 복원은 mysqldump와 마찬가니로 출력된 덤프파일을
mysql CLI를 사용해 실행하게 되어있다. 덧붙여서, --user는 사용자 계정을 백업하기 위한 옵션이다. 이 옵션을 설정하지 않으면 사용자 계정은 백업되지 않으므로 주의하기 바란다.

shell> mysqlpump --single-transaction --users > dump.sql
shell> mysql < dump.sql

mysqlpump는 기본적으로 전체의 데이터베이스를 백업한다. 명시적으로 --all-databases 옵션을 지정하는 것도 가능하지만 쓸 필요는 없다. 데이터베이스를 개별로 지정하려면 mysqldump와 마찬가지로 옵션이 아닌 인수로 데이터베이스
이름을 직접 지정하든가, --databases 옵션을 사용하든가, 또는 mysqlpump에서만 사용이 가능한 --include-databses, --exclude-databases 옵션을 사용하면 된다.
이들 두개의 옵션에서는 콤마를 구분하여 데이터베이스명을 지정하는 것 외에 와일드카드로 이용이 가능하다. 데이터베이스의 지정 방법을 표 9.5에 정리해 두었다.
단, --all-databases를 지정한 경우에는 정보 스키마, 성능스키마, sys스키마, ndbinfo와 mysql 데이터베이스에 있는 권한 테이블은 백업되지 않으므로 주의하기 바란다.

1) 전체데이터베이스를 백업 mysqlpump --all-databases
                                 mysqlpump (옵션없음)

2) 백업 대상 데이터베이스를 직접지정 mysqlpump --databases=db1,db2,db3

3) 와일드카드로 백업할 데이터베이스명을 지정 mysqlpump --exclude-databases=test%
4) 어떤 데이터베이스 안의 특정 테이블만 백업 mysqlpump db1 table1 table2 table3

병렬 덤프에 대해서는 조금 자세한 설명이 필요하다. mysqlpump에는 Mysql 서버에서 데이터를 수집하기 위해 여러 개 스레드를 생성한다. 각각의 스레드가 Mysql 서버로 접속을 일으켜, 데이터를 하나로 합치기 위해 큐로 삽입된다.
큐는 기본적으로 하나뿐이다. 또한 큐별 기본스레드 수는 2이다.
큐를 증가시키려면 --parallel-schemas 옵션을 사용한다.이 옵션의 서식은 리스트 9.3과 같다. 이 옵션은 여러 번 지정하는 것이 가능해서, 옵션을 지정할 떄마다 큐가 하나씩 늘어난다. N은 큐에 소속하는 스레드 수로, 정수로 지정한다.
각각의 스레드는 대상 Mysql 서버에 개별로 접속한다. 스레드가 지나치게 많아지면 서버가 수용할 수 있는 접속 수를 소비해 버리므로 주의해야 한다. 스레드 수에 이어, 이 큐에 의해 처리해야 하는 데이터베이스를 지정한다. 데이터 베이스를 여러 개 지정하는 경우에는 콤마로 구분하여 지정한다. --parallel-schemas 옵션을 지정해도, 기본큐는 반드시 작성된다. --parallel-schemas 옵션으로 지정되지 않은 데이터베이스는 기본 큐로 처리된다.

--parallel-schemas=[N:]데이터베이스명,[, 데이터베이스명..]

아래는 --parallel-schemas로 큐를 2개 추가하는 예이다. 기본 큐와 합쳐 총 3개의 큐가 작성되었다. 기본 큐와 --parallel-schemas 옵션으로 스레드 수를 지정하지 않는 큐에는 --default-parallelism 옵션으로
지정된 수의 스레드가 작성된다. 이 옵션의 기본값은 2이다. 리스트 9.4의 예에서는 기본 큐와 첫번째의 큐에 각각 3개의 스레드가, 그리고 두번째의 큐에 5개의 스레드가 생성된다. 이 예에는 합계 11개의 스레드, 다시말해서 11개의 접속에 의해 백업이 처리된다.

shell> mysqlpump --default-parallelism=3 \
--parallel-schemas=db1,db2 \
--parallel-schemas=5:db3,db4,db5

--> default-parallelism : 기본적으로 할당할 스레드 개수

     parallel-schemas : 큐할당 파라미터, 굳이 선언하지 않아도 기본으로 큐는 1개할당되고, 이 명령어를 사용시 추가로 큐를 열어 처리한다. default-parallelism이 3이니까 하나의 큐에 기본적으로 3개의 스레드는 할당하고 parallel-schemas=5로 쓰게되면 스레드를 5개까지 늘려처리한다.

다만 아래에서 보는 것처럼 여러스레드가 하나의 테이블을 백업하는 방식은 아니고 1 thread에 1 table 처리 방식이다.

보통 parallel 은 하나의 파일을 다중 processing 하는 것인데 이것은 그에 미치지 못한다. 예를 들어 1 table이 대용량이라도 1 thread에서만 백업처리되서 오라클의 imp가 impdp 로 개선되면서 10배이상의 성능향상을 보인것 만큼이나 그렇게 크게 효과가 있을 거 같진 않다.

병렬 덤프에는 한가지 큰 제약이 있다. 하나의 테이블을 여러 개의 스레드로 덤프할 수 없다는 것이다. 다시 말해 테이블 수가 스레드 수보다 적으면 스레드가 남아있어도 활용할 수가 없다. 리스트 9.4의 명령어에 의해 병렬 덤프를 나타낸 것이 아래 그림이다. db2와 db3에는 테이블이 2개밖에 없고, 그 외의 데이터베이스에는 4개 이상의 테이블이 있다고 판정한다.

이처럼 mysqlpump에서는 정의된 스레드가 모두 동시에 실행된다. 큐와 스레드의 수를 잘 정리하고 동시 실행 스레드 수가 너무 많아지지 않게 하자.

#Test

mysqlpump -uroot -p패스워드 --include-databases=TESTDB --include-tables=TB_TEST_CUST,TB_TEST_USER,TB_TEST_COM_MST,TB_TEST_COM_DTL \
--default-parallelism=4 > 12_TEST_TABLES.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 0/4 tables, 250/319596 rows
Dump progress: 2/4 tables, 83233/319596 rows
Dump progress: 2/4 tables, 176483/319596 rows
Dump progress: 2/4 tables, 267983/319596 rows
Dump completed in 9889 milliseconds

// default-parallelism=4 -->큐 1개에 스레드 4개를 열고 병렬로 덤프를 뜨겠다.
// include-databases -- 테이블 스키마는 TESTDB
// include-tables -- 테이블은 4개, % 와일드카드 됨

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 11 | root | localhost | NULL | Query   |    0 | starting | show processlist |
| 12 | root | localhost | NULL | Sleep   |    0 |          | NULL             |
| 13 | root | localhost | NULL | Sleep   |    0 |          | NULL             |
| 14 | root | localhost | NULL | Sleep   |    0 |          | NULL             |
| 15 | root | localhost | NULL | Sleep   |    0 |          | NULL             |
+----+------+-----------+------+---------+------+----------+------------------+
5 rows in set (0.00 sec)
// 작업이 시작될 때 프로세스리스트를 보면 스레드가 4개 할당된 것으로 확인할 수 있다.

#mysqlpump referance
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

#사용방법

alter table 테이블이름 modify 현재컬럼이름 int(20) NOT NULL;
alter table 테이블이름 change 현재컬럼이름 변경할컬럼이름 int(20) NOT NULL;

MODIFY는 컬럼 타입을 변경하기 위해 쓰고 CHANGE 는 컬럼이름을 바꾸기 위해 쓴다.
근데 CHANGE로도 컬럼 타입만을 바꾸기도 한다.(이름은 똑같이 쓰고..)

ex)
alter table 테이블이름 change 현재컬럼이름 현재컬럼이름 int(30) NOT NULL;

그래서 성능테스트 해봤는데 큰 차이는 없는 듯하다.
데이터 타입이 다를경우 어떤 차이가 발생하는지 테스트해 보았지만 별차이 없었다.

다만 테스트 중 성능이슈를 보려고 pk의 컬럼사이즈를 왔다갔다 테스트해봤는데
상용 운영DB에서는 int(1) -> int(10) 가능하지만 int(10) -> int(1) 은 곤란하다.

CREATE TABLE `TMP_LOAD_20200511` (
  `SEQ_NO` bigint(20) unsigned SEQ_NOT NULL,
  `DENT_NM` varchar(12) SEQ_NOT NULL COMMENT '가입번호',
  PRIMARY KEY (`SEQ_NO`),
  KEY `IDX_LOAD_20200511_01` (`DENT_NM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
;
--> 10만건 데이터 insert 


ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO bigint(30) unsigned NOT NULL; 
ALTER TABLE TMP_LOAD_20200511 CHANGE SEQ_NO SEQ_NO bigint(40) unsigned NOT NULL;
<-- 성능차이 없다.(0.05초)

ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO bigint(30) unsigned NOT NULL;
ALTER TABLE TMP_LOAD_20200511 CHANGE SEQ_NO SEQ_NO bigint(1) unsigned NOT NULL;
<-- 성능차이 없다.(0.05초)

ALTER TABLE TMP_LOAD_20200511 CHANGE SEQ_NO SEQ_NO varchar(10) NOT NULL; 
<-- 2.1초 bigint에서 varchar는 시간이 좀 걸린다.
ALTER TABLE TMP_LOAD_20200511 CHANGE SEQ_NO SEQ_NO bigint(1) unsigned NOT NULL; 
<-- 2.4초 varchar는 bigint 시간이 좀 걸린다.
ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO bigint(30) unsigned NOT NULL; 
<-- 성능차이 없다.(0.05초)

ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO varchar(10) NOT NULL;
<-- 2.1초 bigint에서 varchar는 시간이 좀 걸린다.


ALTER TABLE TMP_LOAD_20200511 MODIFY SEQ_NO varchar(1) NOT NULL;
Error occurred during SQL query execution
이유:
 SQL Error [1265] [01000]: Data truncated for column 'SEQ_NO' at row 2
 

 [admin@db ~]$ perror 1265
MySQL error code 1265 (WARN_DATA_TRUNCATED): Data truncated for column '%s' at row %lu
//에러한번 내주고 테스트 끝~

#결론

두개 성능차이 없고 컬럼 타입변경에는 MODIFY든 CHANGE 아무거나 쓰면 된다.

#PK가 다른 테이블의 FK일 떄 컬럼 변경 테스트(MODIFY, CHANGE 둘다 동일함... 그냥 착각이었음)

ex) TABLE A (pk)-> TABLE B (A pk를 FK로 갖음) 일때!

TABLE A의 PK의 컬럼타입 사이즈를 늘리는 것은 가능하지만 줄이면 아래와 같은 에러가 난다.

//TESTDB.TABLE_A 에 PK가 VARCHAR(20) 이고 TESTDB.TABLE_B 에서 A의 PK를 FK로 걸어놓았을 때!
ALTER TABLE TESTDB.TABLE_A MODIFY TEST_CD varchar(30) NOT NULL COMMENT '대분류코드';
--> pk 컬럼 사이즈 늘리기 가능

ALTER TABLE TESTDB.TABLE_A MODIFY TEST_CD varchar(10) NOT NULL COMMENT '대분류코드';
--> 에러
Error occurred during SQL query execution
이유:
 SQL Error [1833] [HY000]: Cannot change column 'TEST_CD': used in a foreign key constraint 'TABLE_B_ibfk_1' of table 'TESTDB.TABLE_B'
 --> TABLE_B의 FK는 아직 VARCHAR(20)이라서 발생
 
 //해결방법
 //체크 포인트를 끄고 반영후 -> 다시 켬
 set FOREIGN_KEY_CHECKS = 0;
 ALTER TABLE TESTDB.TABLE_A MODIFY TEST_CD varchar(10) NOT NULL COMMENT '대분류코드';
 --> 가능
 set FOREIGN_KEY_CHECKS = 1;
 
 
 
 

Mysql 서버에는 리플리케이션 실행시 slave 쪽에서 Master와는 다른 데이터베이스로 다시 쓰는 기능이 있다. 대응하는 옵션은 replication_rewrite_db 로 slave 쪽에서 지정할 수 있다. 나름대로 편리한 방식이지만, 어떤 사정에 의해 Slave로 마스터의 바이너리 로그를 적용해야할 때는 불편하다.

바이너리 로그를 재생하는 mysqlbinlog 명령어에는 데이터베이스를 고쳐쓰기 기능이 없이 그대로 적용할 수 없기 때문이다. 테이블마다 일일이 이름을 고친다면 바이너리 로그의 적용이 가능하기야 하지만 매우 번거로운 일이다.

그래서 Mysql 5.7의 mysqlbinlog에는 --rewrite-db 옵션이 추가되어, 바이너리 로그 재생 시에 데이터베이스명을 고쳐쓸 수 있게 되었다. 아래는 실제 실행 예다.

mysqlbinlog --rewrite-db="dbX->dbY" \
			--rewrite-db="db1->db2" \
			mysql-bin.000123 | mysql

반영중 뜻밖의 에러를 만났다.

CREATE TABLE TMP_LOAD_20200521 (
VALUE1 varchar(30) NOT NULL,
VALUE2 varchar(30) NOT NULL,
VALUE3 varchar(200) NOT NULL,
VALUE4 varchar(8) DEFAULT NULL,
VALUE5 varchar(8) DEFAULT NULL,
VALUE6 varchar(4000) DEFAULT NULL,
VALUE7 varchar(1) NOT NULL DEFAULT 'Y',
VALUE8 varchar(1) NOT NULL DEFAULT 'N',
VALUE9 varchar(50) NOT NULL,
VALUE10 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
VALUE11 varchar(50) NOT NULL,
VALUE12 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (VALUE1 ,VALUE2),
KEY IDX_VALUE3_01 (VALUE3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='테스트';

//스키마 생성안되고 에러남
SQL Error [1067] [42000]: Invalid default value for 'VALUE10'

mysql 5.7에서 개발하던 테이블 스키마를 mysql 5.5에 넣던 중에 발생햇는데..

음 왜 5.7을 5.5에 넣어야만 했는지는 사정이있고.. 여하튼 이게 안되네;

하위버전으로 마이그레이션 한 경우는 별로 없어서 케이스를 찾기 힘들었고

버전 올려라, 걍 datetime 써라 라는 얘기 밖에..

그러던 중에 이런 얘기를 찾을 수 있었다.

--------------------------------------------------------------------------------------

datetime 컬럼에 기본값이 now() 적용은 안되고 current_timestamp 를 사용해야함
mysql 5.5 버전 이하에서는 한 테이블당 1개의 컬럼만 current_timestamp를 사용할 수 있다.

mysql 5.6부터 이 제한이 해제되었다.

---------------------------------------------------------------------------------------

설마라고 생각하고 테스트를 진행했다.

일단 datetime은 timestamp로 바꾸고 not null 보다는 default로 넣으라는 얘기가 많아서 그대로 수정함

CREATE TABLE TMP_LOAD_20200521 (
VALUE1 varchar(30) NOT NULL,
VALUE2 varchar(30) NOT NULL,
VALUE3 varchar(200) NOT NULL,
VALUE4 varchar(8) DEFAULT NULL,
VALUE5 varchar(8) DEFAULT NULL,
VALUE6 varchar(4000) DEFAULT NULL,
VALUE7 varchar(1) NOT NULL DEFAULT 'Y',
VALUE8 varchar(1) NOT NULL DEFAULT 'N',
VALUE9 varchar(50) NOT NULL,
VALUE10 timestamp DEFAULT CURRENT_TIMESTAMP,
VALUE11 varchar(50) NOT NULL,
VALUE12 timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (VALUE1 ,VALUE2),
KEY IDX_VALUE3_01 (VALUE3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='테스트';

SQL Error [1293] [HY000]: Incorrect table definition; 
there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
// only one...설마

CREATE TABLE TMP_LOAD_20200521 (
VALUE1 varchar(30) NOT NULL,
VALUE2 varchar(30) NOT NULL,
VALUE3 varchar(200) NOT NULL,
VALUE4 varchar(8) DEFAULT NULL,
VALUE5 varchar(8) DEFAULT NULL,
VALUE6 varchar(4000) DEFAULT NULL,
VALUE7 varchar(1) NOT NULL DEFAULT 'Y',
VALUE8 varchar(1) NOT NULL DEFAULT 'N',
VALUE9 varchar(50) NOT NULL,
VALUE10 timestamp DEFAULT CURRENT_TIMESTAMP,
VALUE11 varchar(50) NOT NULL,
VALUE12 datetime NOT NULL,
PRIMARY KEY (VALUE1 ,VALUE2),
KEY IDX_VALUE3_01 (VALUE3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='테스트';

// 된다..헐;;

진짜 mysql 5.5는 CURRENT_TIMESTAMP 는 컬럼에 하나만 지원한다. ㅎㅎ

하위버전으로 마이그레이션하는 것은 어렵다는 것을 한번 더 확인했다.

끝~

오래된 Mysql에서 ALTER TABLE은 완전한 데이터의 복사가 필요했다. ALTER TABLE 을 실행하면 새로운 정의의, 사용자에게 보이지 않는 임시 테이블을 작성해서 원래의 테이블에서 데이터를 모두 복사한 후, 완료되면
재명명하는 처리를 했다. 이러한 조작이 진행되는 동안 사용자는 테이블의 데이터를 참조는 할 수 있지만 변경하거나 LOCK을 걸 수는 없다. 다시 말해 Non Locking Read만 허용했다.
그 외의 처리는 ALTER TABLE이 종료될 때까지 금지되었으므로 변경이나 LOCK이 필요한 애플리케이션은 정지해 버린다. 또한 마지막에 rename하는 시점만은 배타적 접근이 된다.

먼저 Mysql 5.1의 InnoDB Plugin과 Mysql 5.5에는 Fast index Creation이라는 기능이 추가되었다. 이것은 인덱스를 추가할 때 새로운 정의의 테이블을 작성하고 데이터를 완전히 복사하는 대신 인덱스만을 작성하는
아주 자연스러운 기능이다. 이로 인해 인덱스 작성에 걸리던 시간은 비약적으로 짧아졌지만 작성 중에는 마찬가지로 변경이나 Lock이 불가능했다.

그래서 Mysql 5.6에서는 마침내 InnoDB 온라인 DDL이 추가되었다. 온라인에서 테이블 정의의 변경이 실행 가능한 경우, 데이터를 복사하는 중에서도 참조뿐 아니라 변경도 가능했다.
단, ALTER TABLE을 시작하는 시점과 종료하는 시점에는 테이블 정의의 정합성을 확보하기 위해 그 순간만 배타 처리를 일으켜 row lock이 필요한 트랜잭션과는 동시에 실행할 수 없다. 이러한 제한이 있기는 하지만 그래도 온라인
상태가 아닌 경우보다는 애플리케이션에 대한 영향이 훨씬 적어졌다.
Mysql 5.6의 온라인 DDL로 충분하다고 생각할지 모르겠지만, 문제가 완전히 사라진 것은 아니다. 가장 큰 문제는 온라인 상태에서 실행할 수 있는 경우와 그렇지 않은 경우가 있다는 것이다. 온라인 상태에서 실행할 수 없는 DDL은
기존처럼 임시 테이블을 작성하고 데이터를 복사하고 마지막에 rename하는 방법으로 실행한다. 온라인 상태에서 실행할 수 없는 DDL은 다음과 같다.

-----------------------------------------------------

컬럼 정의 변경
기본키 삭제
전문 검색 인덱스 추가

-----------------------------------------------------

ysql 5.7 에서는 컬럼 정의의 변경에 관한 제한이 일부 완화되어, VARCHAR의 크기를 늘릴 때 데이터를 복사하지 않고 정의를 변경할 수 있게 되었다. 단, 크기를 늘릴 수는 있지만 줄이지는 못한다.
예를 들면 VARCHAR(32)인 컬럼을 VARCHAR(64)로 바꾼다면 리스트 4.8과 같이 명령어를 실행한다.

alter table tbl_name ALGORITHM=INPLACE, MODIFY col VARCHAR(64) NOT NULL;

온라인 DDL임을 강제하고자 할 때는 ALGORITHM=INPLACE을 지정한다. 이 지정이 있으면 DDL이 온라인 상태에서 처리될 수 없는 상황에서는 명령어 실행이 실패한다.
이 지정이 없는 경우에는 온라인 수행 가능 여부에 따라 자동적으로 ALTER TABLE의 실행방식을 선택한다. 덧붙여 이전부터 있었던 COPY에 의한 ALTER TABLE은 ALGORITHM=COPY 를 지정하면 된다.
VARCHAR의 크기를 늘릴 수 있지만 어떤 크기로든지 지정할 수는 없으니 주의해야 한다. 왜나하면 VARCHAR 타입의 컬럼에는 실제의 문자열의 길이를 표시하기 위한 메타 데이터가 있어 그것이 255 바이트 이하인지 그보다 큰지에 따라
나뉘기 때문이다. 또한 VARCHAR는 65,535 바이트를 넘으면 내부적으로는 TEXT 타입이 선택되므로 그 경계를 넘어 온라인 DDL로 크기를 늘릴 수는 없다. 예를 들면 온라인 DDL로 최대크기를 10 byte에서 100 byte로 늘릴 수는
있지만, 100 byte에서 1000 byte로 늘릴 수는 없다. 여기서 이러한 제한에 걸려있는 크기의 단위가 byte라는 점에 주의하자.

테이블 정의에서 VARCHAR의 크기는 문자 수를 나타내므로 컬럼의 최대 바이트 수가 얼마가 될지는 사용할 문자 코드와 문자 수로 계산해야 한다.
예를 들면 utf8md4의 경우, 한문자당 최대 4바이트를 소비한다. 따라서 VARCHAR(64)의 최대 바이트 수는 256이므로 VARCHAR(63)을 VARCHAR(64)로 변경하는 것은 온라인 상태에서 할 수 없다. 그러한 제한에 걸리지 않는다면
VARCHAR 크기의 변경은 실질적으로 메타 데이터를 고쳐쓰는 것만으로 완료한다.

--> mysql 5.5에서 5.7로 업데이트해서 5.6에서는 지원제한이 있고 5.7에서 업데이트된 내용인지 몰랐다.

컬럼의 사이즈 변경은 5.7.23에서 한번 더 버그패치가 된다. 최신버전으로 업데이트할 이유가 생겼다.

UNION의 실행 계획은 아주 단순하다. UNION으로 통합된 SELECT를 위에서부터 순서대로 실행해 나가면 된다. 단, Mysql 5.6까지는 UNION ALL의 경우에도 임시테이블을 작성하는 모양새였다.
UNION ALL은 중복을 배제하지 않기 떄문에 사실은 임시테이블을 작성할 필요가 전혀 없다.

Mysql 5.7에서는 이런 점을 개선하여 불필요하게 임시 테이블을 작성하지 않도록 했다. 한편, UNION DISTINCT의 경우에는 실행 계획에 포함된 각 행을 단숨에 처리해야 하기 때문에 임시 테이블을
이요하여 값의 중복을 배제했다. 이와같은 동작은 Mysql 5.7에서도 바뀌지 않았다.
-->temeporty table를 사용하지 않는 것만으로도 응답시간이 빨려졌고 성능개선이 되었다. 하지만 대용량 테이블 JOIN 시에는 UNION ALL의 개선을 크게 느낄 수는 없었다. 그냥 웬만하면 대용량테이블은 UNION ALL안에 두지 말자.

UNION은 SELECT를 순서대로 실행할 뿐이므로 상세한 해설은 생략하겠다. 한가지만 언급하자면, UNION을 사용한 쿼리의 성능은 각각의 SELECT에 좌우된다. 각각의 SELECT가 효율적이라면 UNION한 결과도 효율적이다.

UNION이 느린것 같다면 UNION에 문제가 있는 것이 아니라 열거된 SELECT 중 하나가 느릴 가능성이 높다.

단, 엄청나게 많은 select를 UNION에 열거하면 UNION 그 자체로 느려질 수 있다.
쿼리의 실행 시간은 대략 SELECT 수에 비례하여 길어지기 때문이다.