메그넘

[ 중복 데이타 확인하기 ] 

SELECT  필드명, count(*) as 변수명   FROM   테이블명   GROUP BY  필드명  HAVING  변수명>n

SELECT  EMPID, COUNT(*) AS cnt  FROM `SYSBASE_EMPLOYEE_RESIGNATION`  GROUP BY  EMPID  HAVING  cnt > 1

MariaDB Log 종류

 -  Error_log :  query 에러에 관련된 메시지를 포함한다.
 -  General_log :  실행되는 전체 쿼리에 대해 저장, 쿼리 요청 받을때 바로 저장한다.
 -  Slow query_log :  long_query_time에 설정된 시간 이상을 소요한 정상적인 쿼리를 기록한다.

현재 DB의 로그 활성화 상태 보기

1. 시스템의  설정 파일을 수정하여 영구적으로 적용하기. ( 설정 후 데몬 재실행 필요 )
  - /etc/my.cnf 파일을 수정한다.

2. DB 에 접속하여  임시로 적용하기....( 데몬 재실행 필요없음 )
  - DB에 접속하여  명령으로 활성화 하였다.....사용 후  다시 명령으로 종료시킨다.

 my.cnf 파일의  옵션을 변경하여 dump 파일 복구 시 속도를 개선할 수 있다.

Mysql 서버에 로그인해서 상태를 확인한다.

우선 Mysql 서거바 어떤 쿼리를 실행하고 있는지 어떤 프로세스가 특히 오랜시간 동안 실행되고 있는지 확인하는 것이 좋다.

Mysql의   "show processlist"  라는 명령은 다음과 같은 다양한 프로세스의 정보를 보여줌으로 Mysql 서버에 로그인해서 가장 먼저 확인해 보는 것이 좋다.

• 현재  Mysql 서버에 존재하는 전체 프로세스 목록
• 각 프로세스가 어떤 작업(SQL)를 실행하고 있는지
• 각 작업의 현재상태
• 각 작업의 실행시간

"show processlist"  명령은 Mysql 서버에 접속된 클라이언트 수만큼의 레코드를 출력한다.  이때 스레드라 함은 실제 클라이언트와 연결돼 있는 스레드만을 의미한다. 즉 이명령의 결과로 출력되는 내용에서 한 레코드는 하나의 프로세스를 의미함과 동시에 하나의 커넥션을 의미한다.

mysql> show processlist;
+-----+-----------+-------------------+---------+---------+--------+----------+------------------+
| Id  | User      | Host              | db      | Command | Time   | State    | Info             |
+-----+-----------+-------------------+---------+---------+--------+----------+------------------+
|   5 | saferzone | localhost         | DEFCON4 | Sleep   |   5746 |          | NULL             |
|   6 | saferzone | localhost         | DEFCON4 | Sleep   |    104 |          | NULL             |
|   7 | saferzone | localhost         | DEFCON4 | Sleep   |   5779 |          | NULL             |
|   8 | saferzone | localhost         | DEFCON4 | Sleep   |   4051 |          | NULL             |
|  72 | saferzone | localhost         | DEFCON4 | Sleep   |   4922 |          | NULL             |
|  96 | root      | 127.0.0.1:40020   | DEFCON4 | Sleep   | 100545 |          | NULL             |
|  98 | root      | 127.0.0.1:40024   | DEFCON4 | Sleep   | 100375 |          | NULL             |
|  99 | root      | 127.0.0.1:40026   | DEFCON4 | Sleep   |     24 |          | NULL             |
| 146 | root      | 192.168.2.2:59488 | DEFCON4 | Sleep   |  25743 |          | NULL             |
| 159 | root      | 192.168.2.3:57341 | DEFCON4 | Sleep   |   4114 |          | NULL             |
| 163 | root      | localhost         | NULL    | Query   |      0 | starting | show processlist |
+-----+-----------+-------------------+---------+---------+--------+----------+------------------+
11 rows in set (0.00 sec)

위 내용에서 주의깊게 봐야 할 항목은 Command 칼럼의 값과 Time 칼럼이다. 때로는 Info 칼럼에 출력되는 SQL도 간단히 문제의 원인을 찾는데 많은 도움이 된다.

• Command 가  "Sleep" 이나 "Binlog Dump" 상태가 아닌 프로세스는 대부분 클라이언트의 요청으로 SQL을 실행하고 있음을 의미하는데, 이때 Time 칼럼의 값이 그 작업을 몇 초 동안 실행하고 있는지 알려준다.
• 만약 특정 쿼리를 실행하고 있는 프로세스가 오랜 시간 동안 실행되고 있다면 해당 프로세스의 State 값을 확인하자 State 칼럼은 그 작업이 현재 어떤 상태인지를 보여주는데,  Waiting 일때는 다른 프로세스가 선점하고 있는 잠금을 기다리는 것을 의미한다. Waiting 상태의 프로세스가 많을 때는 대부분 테이블의 잠금을 획득하고 해제하지 않아서 이런 현상이 발생한다. 도한 갑자기 너무 많은 사용자가 테이블 한두 개를 집중적으로 읽고 쓸때도 이런 현상이 나타난다. State가 Waiting 상태로 표시되는 것은 테이블 잠글을 기다릴때만 해당되며, InnoDB 스토리지 엔진의 레코드 잠금은 Waiting 으로 표시되지 않고 update라고 표시된다.

uptime  명령으로 1분, 5분, 15분 간의  CPU가 처리해야할 작업이 얼마나 쌓여 있는지 대략적으로 확인할 수 있다.

현재 CPU가 어떤 작업에 주로 사용되고 있는지 알아보려면 vmstat(Virtual Memory) 를 이용하면 된다.

일반적으로 vmstat는 1초 단위의 시간 간격을 파라미터로 사용한다. 
명령어 실행시 파라미터로 1초를 적용하면  1초 동안 샘플링된 상태 값을 1초 단위로 출력한다.

[root@localhost ~]# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0 6050228  33584 332100    0    0    47     6  101  226  1  1 98  0  0
 0  0      0 6050212  33584 332100    0    0     0     0   97  223  0  1 99  0  0
 0  0      0 6050212  33584 332100    0    0     0     0  108  233  0  0 100  0  0
 0  0      0 6050212  33584 332100    0    0     0     0   93  216  0  0 100  0  0
 0  0      0 6050212  33584 332100    0    0     0     0   95  219  0  1 99  0  0

vmstat 는 가상메모리의 상태를 보여주는 명령이지만, CPU나 프로세스 큐를 확인하는 용도로 더 많이 사용한다.

vmstat 유틸리티의 결과에서 proc 섹션과 cpu 섹션을 주로 참고한다.

procs  -r  : 프로세스 큐에서 cpu가 실행해주기를 기다리는 프로세스의 수

procs  -b : 지정된 이벤트가 발생하기 전까지는 실행될 수 없는 프로세스의 수

cpu   -us : 사용자 프로세스를 위해 사용한 cpu사용률로 mysqld 서버의 코드와 같이 애플리케이션의 코드가 사용한 

               cpu 시간을 의미한다. 대표적으로 정렬이나 group by 작업, 그리고 압축과 같은 작업이 많이 발생하는 자입                 에서는 사용자 cpu가 높게 나타난다.

cpu   -sy : 커널이 사용한 cpu 시간을 의미한다.

cpu   -id : cpu가 아무것도 하지 않고 대기(idle)하는데 사용한 cpu 사용률이다.

cpu  -wa : 디스크 입축력을 기다리는데 사용한 cpu 사용률을 의미한다. 주로 DBMS 서버가 실행 중인장비에서 관심을                둬야 할 값인데, wa(wait I/O) 값이 높으면 디스크 I/O가 과도하게 많이 발생해 cpu가 데이터의 입출력을

              기다리는 시간이 많다는 것을 의미한다.  

cpu  -st : 가상 머신으로부터 빼앗긴 시간을 의미하는데, 크게 신경쓰지 않아도 된다.

my.cnf 파일에 아래 내용을 설정해 주면 지정된 파일에 쿼리 로그를 남길 수 있다.

[root@AP1 etc]#vim  my.cnf        

general_log = 1

slow-query-log = 1 
general_log_file = /var/log/mysql_query.log
expire_logs_days = 2
max_binlog_size = 10M

JOIN 절은 두 개 이상의 테이블에서 관련성이 있는 컬럼에 기초하여 행(Row)을 결합하는 데 사용됩니다.

1. [ INNER JOIN ] --> INNER JOIN 키워드는 양쪽 테이블 모두에서 일치하는 레코드를 반환합니다. (교집합)

2. [ LEFT JOIN ] --> LEFT JOIN 키워드는 왼쪽 테이블(table1)의 모든 레코드와 오른쪽 테이블(table2)의 일치하는 레코드를 반환합니다. 오른쪽에 일치하는 레코드가 없으면 왼쪽에서는 결과가 NULL이 됩니다.

3. [ RIGHT JOIN ] --> RIGHT JOIN 키워드는 오른쪽 테이블(table2)의 모든 레코드와 왼쪽 테이블(table1)의 일치하는 레코드를 반환합니다. 왼쪽에 일치하는 레코드가 없으면 오른쪽에서는 결과가 NULL이 됩니다.

4. [ RULL OUTER JOIN ] --> FULL OUTER JOIN 키워드는 왼쪽 테이블(table1) 또는 오른쪽 테이블(table2) 레코드에 일치하는 모든 레코드를 반환합니다.

Mysql 데이터를 Select 할때 일부 필드의 특정값 ( 복수 지정 가능)만  빼고 불러와야 하는 상황이 있는데요.

예를들면 ID라는 필드에 admin, admin1, admin2 라는 관리자 계정을 빼고 불러온다거나...

이런 경우 where 절에 not in 조건으로 지정할 수 있다.

다음은 구누보드에서의 멤버 테이블 중 지정된 아이디를 제외하고 Select 하는 구문이다.

위의 쿼리를 실행하면

g4_member 테이블에 mb_id 에서 admin, admin1, admin2  만 제외하고 데이터를 불러오게 됩니다.

* [ 구조 복사 ]

  특징 : 기존 테이블의 설정 그대로 복사 된다.

    참고 => 기존 테이블에 'Primary Key' 또는 'auto_increment' 가 설정 되어 있으면 복사 할 수 없음.
    응용 => Create Table IF NOT EXISTS new_table like old_table (new_table 이 없으면 복사)

* [ 구조와 데이터 복사 ]

  특징 : 테이블의 구조와 함께 데이터도 함께 복사가 된다.

     주의 => 기존 테이블에 'Primary Key' 또는 'auto_increment' 가 설정 되어 있으면 해당 설정은 적용 되지 않고

                 값만 복사 됨.

* [ 데이터 복사 ]

  참고 ==> 대상 테이블의 컬럼 중에 자동 증가 값 설정 이 된 컬럼이 있을 경우 해당 컬럼에 데이터 입력시 중복된

                데이터가 있으면 오류 발생.
  응용 ==> Insert Into destination_table (column_a, column_b) (select a, b from source_table) 원하는 필드의

                데이터만 복사가 가능하다.

동일한 구조의 테이블 또는 다른 구조의 테이블에  select 한 내용을 insert 하는 방법.

1. select 한 내용의  전체 컬럼 insert

select 테이블과 insert할 테이블의 구조가 일치해야 한다.

2. 원하는 컬럼만  select 해서 insert

특정 컬럼을 직접 선택해서 insert하는 방법이다.