반응형

1. 실행중인 쿼리의 explain 기능

 

1-1) 기능설명

Mysql을 관리하고 있다면 show processlist를 사용해 장시간 실행중인 sql이 없는지를 조사하는 사람이 많을 것이다.
그 결과 장시간 실행하고 있는 sql을 발견하면 왜 실행에 시간이 걸리는지 알아내기 위해 EXPLAIN 명령어를 사용한다.
EXPLAIN 명령어를 사용하기 위해 SHOW FULL PROCESSLIST 명령어로 SQL 전문을 가져온 다음, 이를 복사해서
EXPLAIN을 실행했을 거이다. 하지만 실행중인 쿼리의 길이가 길면 SQL을 출력하여 복사하는 것도 아주 번거로운 일이다.
Mysql 5.7에서는 이처럼 불필요한 조작을 할 필요가 없어졌다.
커넥션 ID를 저장하기만 해도 실행중인 SQL의 EXPLAIN 결과를 볼수 있게 되었다.
예를 들어 장시간 실행 중인 SQL의 커넥션 ID가 777이라고 하자. 리스트 3.3은 그 커넥션에 대한 EXPLAIN을 출력하는 명령어다.

 

#실행방법

explain for connection (프로세스ID);

 

1-2) 테스트

mysql> show processlist;
+----+--------+-----------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User   | Host                  | db        | Command | Time | State        | Info                                                                                                 |
+----+--------+-----------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 33 | xxxadm | xxx.xxx.xxx.148:42939 | xxxxxxxxx | Query   |   11 | Sending data | /* ApplicationName=DBeaver 7.0.3 - SQLEditor <Script.sql> */ select
T1.*,
case 
when (select  |
| 34 | root   | localhost             | NULL      | Query   |    0 | starting     | show processlist                                                                                     |
+----+--------+-----------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

--> 일단 프로세스 리스트로 현재 수행중인 슬로우 쿼리를 확인한다.

 

 

mysql> explain for connection 33;
ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE

--> 안된다.. 이유는 CREATE TABLE SELECT ~ 라서 ^^;;

 

select문만 실행해보자.

mysql> explain for connection 33;
+----+--------------------+----------------------+------------+------+---------------+------------+---------+----------------------+---------+----------+-------------+
| id | select_type        | table                | partitions | type | possible_keys | key        | key_len | ref                  | rows    | filtered | Extra       |
+----+--------------------+----------------------+------------+------+---------------+------------+---------+----------------------+---------+----------+-------------+
|  1 | PRIMARY            | T1                   | NULL       | ALL  | NULL          | NULL       | NULL    | NULL                 |     286 |   100.00 | NULL        |
|  4 | DEPENDENT SUBQUERY | TB_xxxxxxx           | NULL       | ref  | TB_xxxxxxx    | TB_xxxxxxx | 5       | xxx.T1.seq_no        |       1 |   100.00 | Using index |
|  3 | DEPENDENT SUBQUERY | TMP_LOAD_20200506_01 | NULL       | ALL  | NULL          | NULL       | NULL    | NULL                 | 6851030 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | TB_xxxxxxx           | NULL       | ref  | TB_xxxxxxx    | TB_xxxxxxx | 5       | xxx.T1.seq_no        |       1 |   100.00 | Using index |
+----+--------------------+----------------------+------------+------+---------------+------------+---------+----------------------+---------+----------+-------------+
4 rows in set (0.00 sec)

--> SELECT으로 바꾸면 아래처럼 바로 확인할 수 있다. 진짜 편리한 기능이다. 장애때 유용하게 써먹을 수 있을 거 같다.

반응형
블로그 이미지

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

,
반응형

5.2 테이블 파티셔닝을 활용해 대용량 데이터를 관리하자

#파티셔닝 간단히 이해하기

파티셔닝은 하나의 커다란 데이터를 여러 개의 데이터로 분할 저장함으로써 데이터 선택 효율을 높이는 기법이다.

특히 InnoDB에서는 PK순으로 데이터가 저장되기 때문에 무작위로 PK를 생성하여 insert를 수행하면 성능이 급격히 저하될 수 있다. 이 경우 테이블에 파티셔닝을 적용하면 비효율을 어느 정도 극복할 수 있다. 하지만 파티셔닝 사용에는 다음과 같은 몇 가지 제약 사항이 있다.

 

1) 파티셔닝 키는 PK와 연관된 컬럼이어야 한다.

2) 파티셔닝 사용시 Unique Key혹은 Forgien Key와 같은 제약조건을 추가할 수 없다.

(단 Unique Key가 PK로 사용되는 경우는 제외)

3) 파티셔닝 적용 시 풀텍스트 인덱싱 혹은 스페셜 인덱싱 기능을 사용할 수 없다.

4) 데이터 조회시 반드시 파티셔닝 키가 포함되어야하며 그렇지 않은 경우 전체 데이터 파일을 스캔한다.

--> 반드시 파티셔닝 키가 포함되어야하며 그렇지 않을경우 Full scan 한다는 얘기.. ㅎㄷㄷ

 

#파티셔닝 테이블 스키마

CREATE TABLE partition_test (
ID varchar(50) NOT NULL,
NAME varchar(40) NOT NULL,
TYPE varchar(20) NOT NULL,
DETAIL varchar(255) NOT NULL,
REG_TIME datetime NOT NULL,
KEY(ID)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(REG_TIME)) (
    PARTITION P_MIN VALUES LESS THAN (to_days('19000101')),
    PARTITION P_20110516 VALUES LESS THAN (to_days('20120517')),
    PARTITION P_20110517 VALUES LESS THAN (to_days('20120518')),
    ....
    PARTITION P_MAX VALUES LESS THAN MAXVALUE
);

쿼리에서 P_MIN을 넣은 이유는 불필요한 파티셔닝 데이터 스탠을 방지하기 위해서다.

날짜 데이터를 파티셔닝한 상태에서 RANGE 파티셔닝 옵션으로 범위 검색(예를 들어 BETWEEN과 같은)을 하면 가장 앞단의 파티셔닝 파일을 스캔한다. 그런데 가장 앞단의 파티셔닝에 데이터가 수천만 건 저장되어 있다면 범위 검색을 할 때마다 대용량 데이터를 매번 스캔해야 하는 비효율이 발생한다. 즉, 불필요한 스캔을 방지하기 위해 생성한 깡통 파티셔닝이라고 보면 된다(Mysql 5.5 이상에서는 Range Column 옵션으로 날짜를 파티셔닝해도 이러한 문제가 발생하지 않는다) --> Mysql 5.5 이하버전에서는 MIN을 반드시 넣어야한다는 얘기 5.5이상에서는 굳이 Min 넣을 필요 없다.

 

#파티셔닝 플랜확인

mysql> explain partitions
select * from partition_test
where REG_TIME BETWEEN '20120517' and '20120518'

#파티셔닝 추가

파티셔닝을 하나 더 추가하기위해서 다음과 같이 P_MAX 파티셔닝 테이블을 재정의한다. 여기서 P_MAX 파티션에 데이터가 많으면 파티셔닝이 추가되는 동안 테이블 잠금이 발생하여 서비스에 영향을 줄 수 있으므로, 재정의 전에 반드시 확인하는 습관을 들이도록 하자.

-->P_MAX에 데이터가 많으면 재정의하는데 시간이 많이 걸리므로 count(*) 데이터가 얼마나되는지 확인하라는 얘기며

혹시 예상치 못한 락이 발생할 수도 있으므로 실행 전에 파티션테이블에 락이 있는지 확인한다.

select * from information_schema.metadata_lock_info;

ALTER TABLE partition_test REORGANIZE PARTITION P_MAX INTO (
	PARTITION P_20120523 VALUES LESS THAN (to_days('20120524')),
    PARTITION P_MAX VALUES LESS THAN MAXVALUE
);

--> 기존 스크립트는 MAXVALUE Drop 후에 재생성했었는데 REORANIZE 로 바꿔봐야겠다.

 

#파티셔닝 삭제

ALTER TALBE partition_test DROP partition P_20110516;

 

5.3.2 통계 테이블을 적절히 사용하자

서비스를 수행하다 보면 데이터 수를 세는 로직이 필요한 경우가 많다. 예를 들어 데이터 페이징을 위해 데이터 수를 카운트하는데, 페이징을 계산하려면 반드시 전체 데이터 수가 필요하다. 어느 경우나 마찬가지로 데이터 수가 크지 않다며 큰 문제가 없겠지만 데이터가 누적되어 대용량화되면 어느 시점부터는 성능에 커다란 저해요소가 된다.

 

이러한 경우에 통계 테이블을 적절하게 도입해서 관리하면 매번 데이터 건수를 확인하는 카운트 쿼리를 실행하지 않아도 현재 필요한 카운트 정보를 바로 가져올 수 있다.

통계테이블

위와같이 데이터를 처리하려면 로직을 일부 변경해야 한다. 데이터를 insert 하는 로직만 있었다면 데이터를 insert한 후 데이터 카운트를 변경하는 로직을 추가해야 한다.

 

특정 일의 데 통계를 구하는 서비스를 구현하려면 다음과 같이 간단한 쿼리를 사용하면 된다.

select count(*) from log where name = 'RED';

만약 하루에 한번씩 호출되는 쿼리라면 큰 문제가 없겠지만 애플리케이션에서 자주 위와같은 쿼리를 호출한다면 서비스 성능에 엄청난 타격을 준다.

 

이 문제를 해결하기 위해 통계성 테이블을 다음과 같이 추가한다.

CREATE TABLE log_stat(
name varchar('16') not null,
name_count int not null,
parimary key(name)
);

그리고 데이터를 insert할 때 log_stat에 관련 건수를 업데이트한다.

INSERT INTO log_stat (name, name_count) VALUES ('RED', 1)
ON DUPLICATE KEY UPDATE name_count = name_count + 1;

RED 데이터 카운트는 다음과 같이 수행한다.

select name_count FROM log_stat WHERE name ='RED';

이렇게 하면 RED의 데이터 수를 찾기 위해 매번 log 데이터 카운트를 수행하지 않아도 된다.

따라서 서비스 품질이 향상될 뿐만 아니라 DB 효율도 자연스럽게 높아진다.

-->상당히 유용한 정보이다. 테이블 정규화만 제대로 된다면 rownum 안쓰는 것과 더불어 상용서비스에 바로 적용검토해봐야할 거 같다.

 

반응형
블로그 이미지

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

,
반응형

#트랜잭션 isolation 레벨에서 테이블 잠금이 발생할 수 있음을 기억하자
Mysql의 스토리지 엔진으로 InnoDB를 사용할 떄, 전체 데이터를 스캔하는 쿼리를 실행할 경우 서비스에 처리하는 데이터에 잠금현상이 발생할 수 있다.
쿼리 실행이 종료될 때까지 다른 세션에서는 테이블 안에 있는 데이터를 변경하지 못하기 때문에 서비스에 직접적인 영향이 있다.
이것은 InnoDB의 기본 Isolation 레벨이 REPEATABLE-READ 이기 때문에 발생하는 현상으로, 세션변수 일부를 변경하여 사전에 문제를 해결할 수 있다.

 

#현상
Mysql 에서 트랜잭션 Isolation 레벨은 기본적으로 REPEATABLE-READ 이다.
이 상태에서 insert into select .. 혹은 create table as select ..로 데이터를 처리하면 참조하는 테이블에 테이블단위 잠금이 발생할 수 있다.
select 만 발생함에도 테이블의 데이터를 변결할 수 없는 기이한 현상이 발생하는 것이다.

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

 

#발생원인
Mysql InnoDB 스토리지 엔진의 기본 Isolation 레벨이 REPEATABLE-READ로 설정되어 있기 때문에 발생한다. REPEATABLE-READ에서는 트랜잭션을 처리할 때
SELECT 결과의 일관성을 보장하기 위해 스냅샷을 이용한다.

위와 같이 insert 와 select 가 동시에 있는 경우에는 select 결과를 보장하기 위해 암묵적으로 select 임에도 불구하고 데이터 잠금과 비슷한 효과가 나타난다.
즉, select 작업이 종료될 때까지 해당데이터 변경작업을 할 수 없다.

 

#트랜젝션 isolation 설명

1) READ-UNCOMMITTED
다른 트랜잭션이 commit전의 상태를 볼수 있음 --> 응 안써요
바이너리 로그가 자동으로 row based 로 기록됨 (statement 설정불가, Mix 설정시 자동변환)

 

2) READ-COMMITED
commit된 내역을 읽을 수 있는 상태로, 트랜잭션이 다르더라도 다른 특정 트랜잭션이 commit을 수행하면 해당 데이터를 read 할 수 있음
바이너리 로그가 자동으로 row based로 기록됨(statement 설정불가, Mixed 설정시 자동변환)

REPEATABLE-READ와 달리 트랜잭션 발생할 때 마다 스냅샷을 뜨는 것으로 알고 있다.

만약 반영한다면 DB의 시스템 부하관련해서 좀 더 확인해봐야한다.

 

3) REPEATABLE-READ
Mysql InnoDB 스토리지 엔진의 기본 Isolation 레벨 --> default 옵션이다.
select 시 현재 데이터 버전의 스냅샷을 만들고 만든 스냅샷에서 데이터를 조회
동일 트랜젝션 내에서 데이터 일관성을 보장 데이터를 다시 읽으려면 트랜잭션을 다시 시작해야함

 

4) SERIALIZABLE
가장 높은 isolation 레벨로 트랜잭션이 완료될 때까지 select 문장이 사용하는 모든 데이터에 공유 락(shared lock)이 걸림 --> 응 안써요
다른 트랜잭션에서는 해당 영역에 관한 데이터 변경뿐만 아니라 입력도 불가

 

#해결방법
Isolation 레벨을 read-commited 로 변경하면 해결할 수 있다.
다음과 같이 세션 설정을 변경한 후에 create table as select, insert into select 를 수행하면 테이블 잠금 없이 데이터가 잘 처리되는 것을 확인할 수 있다.

 

SET GLOBAL transaction isolation level READ COMMITED;

 

vi /ect/my.cnf
-----------------------------------
transaction-isolation = READ-COMMITTED

 

단 isolation 레벨이 READ-COMMITED 인 경우에는 바이너리 로그 포맷을 MIXED로 설정해도 바이너리 로그가 ROW 기반으로 기록된다는 점을 반드시 기억해야한다.
만약 Mysql 리플리케이션 환경에서 시스템을 운영하고 있다면 설정을 변경한 후에 반드시 데이터 도익화가 정상적으로 잘 이루어지고 있는지를 주기적으로 체크해야한다.

--> replication 사용중이라면 DB 중단하고 cnf와 binlog_format=ROW 반영후 binlog reset하고

DB 재시작하는 것이 맘이 편할꺼 같은데?


#스키마 레벨에서의 접근법

#Mysql InnoDB에서 primary key는 성능에 직접적인 영향을 준다.
Mysql의 InnoDB에서는 primary key 순서로 데이터가 저장된다. primary key는 데이터에 접근하는 물리적인 주소로 사용된다고 봐도 된다.
MS-SQL의 클러스터 인덱스, 오라클의 IOT(index Organized table)라는 개념이 Mysql에서는 Primary key로 구현된 것이다.
(오라클의 클러스터 테이블 Cluster table 과는 다른 개념이다.) 즉, 인덱스 순서로 데이터가 정렬되어 디스크에 저장된다.

#B+TREE 알고리즘에 따른 pk 저장순서
pk 데이터 (4개가 있다고 하면 아래와 같이 순차적으로 생성되어있다.)
10->20-> 30-> 40

 

#insert into ~ pk데이터 15 가 들어간다면
pk 데이터
10->15-> 20-> 30-> 40
10과 20 사이로 들어간다. 이건 다시말하면 pk를 다시 정렬해야한다는 얘기로 부하를 야기할 수 있다.
데이터 크기가 비대해지면 UUID와 같이 무작위로 pk를 생성하여 테이블에 데이터를 넣으면 데이터가 누적됨에 따라 점차적으로 성능이 저하될 것이고
극심한 disk I/O wait에 빠져서 서비스품질이 크게 떨어질 수 있다. 데이터를 테이블에 넣을 때마다 위와 같이 순서에 맞게 물리적으로 이동시켜야하기 때문이다.

 

#테이블에 pk가 없다면?
1) 인덱스 중 unique 속성이 있는 key를 pk로 대체 사용
2) pk 선언도 없고 unique key도 없으면 내부적으로 6Byte 크기의 대체 pk를 생성하여 사용
결과적으로 모든 InnoDB는 pk를 선언하지 않아도 내부적으로는 pk를 가지고 있다는 것을 반드시 기억해야 한다

 

# 불필요한 인덱스 삭제
인덱스가 늘어날수록 insert 성능은 크게 떨어진다.
인덱스는 데이터를 접근하기 위한 필수요소지만 많은 것이 무조건 좋은 것은 아니다.
인덱스도 cpu와 메모리 자원을 소비하는 데이터라는 점을 반드시 기억하기 바란다.
pk = pk
key (보조인덱스) = pk + 인덱스

 

#테스트

인덱스 수 0개 1개 2개 3개
시간 4.14초 7.18초 10.40초 12.61초

 

반응형
블로그 이미지

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

,
반응형

읽으면서 한번 더 짚고 넘어갈 부분은 메모해서 정리한다.

 

#InnoDB 스토리지엔진
1) 트랜젝션 지원
2) MVCC (multiversion Concurrency Control - 다중 버전 동시성 제어 메커니즘)
- 행단위 잠금으로 데이터 변경 작업을 수행하기 때문에 연관이 없는 데이터를 다른 사용자가 변경할 수 있다.
3) MyISAM은 인덱스만 메모리에 올리지만 InnoDB는 인덱스와 데이터를 모두 메모리에 올린다는 것이 가장큰 차이점이다. 메모리에 인덱스와 데이터가 적재되어 있기 때문에 메모리버퍼 크기(Innodb_buffer_pool_size)가 DB 성능에 큰 영향을 미친다.


#테이블 조인방식
단일코어(1 cpu)에 Nested Loop Join 알고리즘
- 선행테이블 A의 조건 검색 결과값 하나하나를 테이블 B와 비교하여 조인하는 방식이다.
프로그램적으로 풀자면 for문안에 for문과 유사하다. 결국 처리할 데이터가 적으면 수행속도가 빠르지만 테이블 A와 테이블 B 중 하나라도 연산해야할 데이터가 많아지면 쿼리 효율이 기하급수적으로 떨어진다.

#LIKE 검색
LIKE 조건이 '검색어%'와 같이 검색어가 앞단에 있다면 데이터 분포도를 따져서 수행한다.
LIKE 조건이 '%검색어'와 같은 형태로 반드시 수행해야 한다면 LIKE 조건 이외의 조건절을 적극 활용하여 LIKE 처리가 필요한 데이터 범위를 최대한 줄인다.

 

#성능 저하를 유발하는 불필요한 서브쿼리 (rownum 생성 제거)
- 오라클에는 rownum 개념이 있기 때문에 데이터 결과 행에 번호를 쉽게 붙일 수 있다. 아쉽게도 Mysql에는 rownum 개념이 없지만
(select @RNUM:=0) 같은 방식으로 행 번호를 매길 수 있다. 하지만 이를 잘못 사용하면 성능이 저하된다.
결과값에 불필요한 Temporary table을 만들어 저장한다.
결과적으로 엄청난 횟수의 조인 연산이 내부적으로 발생하는 것이다. 다음과 같이 불필요한 서브쿼리를 제거해서 쿼리를 다시 작성한다.
페이지 번호는 DB에서 무리하게 생성하는 것보다 애플리케이션 서버에서 생성하는 것이 Mysql의 성능을 높이는 데 유리하다.

-> 그동안 튜닝포인트 중 하나였는데 푸시해도 잘 안들어줬는데 성동찬님책에서 있네 ㅎㅎ

개발쪽에 얘기해서 쪼는데 제대로인 설명인 듯

반응형
블로그 이미지

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

,
반응형

현재는 카카오페이에 있는 성동찬님이 쓴 책으로

이번에 mysql 튜닝관련 도움을 받으려고 구매했다.

근데.. 설치를 mysql 5.1로 하라네.. 2013년 출판이네...

MyISAM 이면 어쩌지;;

페이지는 112 쪽으로 가볍게 읽을 수 있을 거 같다.

 

반응형
블로그 이미지

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

,
반응형

보고 싶었는데 번역본이 없어서 구글링으로만 리플리케이션 부분 참고만 했었던..

번역본이 나왔네?^^

replication과 performance schema 공부좀 해볼까~

 

반응형
블로그 이미지

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

,
반응형

1. Mysql 실행계획(explain) 사용

- SQL문 앞에 explain을 붙여주면 된다

 

2. type 컬럼

1) ALL -- 테이블 풀스캔

--> 풀스캔이 무조건 나쁜 것은 아니다 where 조건절을 걸어도 옵티마이저가 검색row수가 적을 때는 풀스캔으로 처리할 경우도 많다.

 

2) index  -- 인덱스 스캔, extra 컬럼 using index 출력시 비용감소 확인

 

3) range -- 래인지 스캔은 제한된 형태의 인덱스 스캔이다. 레인지 스캔의 경우 인덱스의 특정부분에서 시작해서 특정 범위에 있는 값을 가지는 행을 반환한다. 전체 인덱스를 살펴봐야 할 필요는 없으므로 전체 인덱스 스캔보다 더 나은 성능을 보인다. where 절에 Between 이나 >가 있을 경우 범위 검색을 사용하게 되며, in() 이나 OR 리스트와 같이 어떤 값들의 리스트를 찾는 경우에도 인덱스를 사용할 수 있다. 이경우에도 레인지 스캔이라고 표시되지만 접근방법에는 큰자이가 있고, 성능상에도 꽤 차이가 난다.

 

4) ref -- 이방식은 어떤 값 하나에 대해 매치되는 행들을 반환해주는 인덱스 접근방식이다. 이때 같은 값을 가지는 여러 개의 행을 찾게 될 수도 있으므로 탐색과 스캔이 함께 사용되며, 고유하지 않은 값에 대한 인덱스나 고유하지 않은 프리픽스에 대한 인덱스에만 이런식으로 접근할 수 있다. 인덱스를 어떤 참조(reference) 값과 비교하기 때문에 ref라고 불리며, 참조 값은 상수여야하며, 다중-테이블 쿼리의 경우엔 이전 테이블에서 넘어온 값도 참조 값으로 사용될 수 있다.

접근 방식 ref_or_null은 ref방식의 변형으로 Mysql이 초기 탐색을 마친후 NULL 엔트리들을 찾기 위해 두 번째 탐색을 해야만 한다는 것을 의미한다.

ex) select * from ref_table where key_column ='상수' or key_column is null

 

5) eq_ref -- Mysql이 기껏해야 값 하나만을 반환해준다는 것을 알고 있을 때 이런 인덱스 탐색법이 사용되며, Mysql이 쿼리를 실행하기 위해 참조 값을 기본키 혹은 유니크 인덱스에 비교할 떄 이런 접근방법을 사용하는 것을 확인할 수 있다. Mysql에서 값을 하나라도 찾으면 더 이상 매치되는 행들ㄹ의 범위를 평가하거나 매치되는 행을 찾을 필요가 없으므로 이런 접근 방법은 아주 잘 최적화되었다고 볼 수 있다.

 

6) const, system -- 쿼리의 일부를 상수로 대체해서, 쿼리를 최적화할 수 있을 경우 Mysql에서는 이런 접근방법들을 사용하게 된다. where 절에 기본키를 이용해 검색하는 조건을 사용하는 경우 Mysql에서는 이 쿼리를 상수로 바꿔버린 뒤 조인에서 테이블을 사실상 제거해버린다.

 

7) NULL -- 이방법은 Mysql이 최적화 단계에서 쿼리를 처리할 수 있으므로 실행 단계에서 인덱스나 테이블에 접근조차하지 않았다는 것을 의미한다. 예를들어 인덱스가 되어 있는 컬럼에서 최솟값을 선택하려는 경우라면 인덱스만 찾아보면 되므로 실행단계에서 테이블에 접근할 필요가 없다.

 

3. rows 컬럼

이 컬럼은 원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할지에 대한 예측값을 의미한다. 이 숫자는 중첩루프(Nested-loop) 조인계획에서 루프 한 번당 몇 개의 레코드를 읽어야 할지에 대한 예측값이다. 다시말하면 이 값은 그저 Mysql이 테이블에서 읽어야할 거라고 생각하는 행 수를 의미하는 것만이 아니고, Mysql이 쿼리를 실행 관점에서 어떤 기준 이상으로 효율적으로 실행시킬 떄 읽어야하는 행 수의 평균을 의미한다.(기준은 조인 순서에서 앞쪽에서 나온 테이블에서 넘어온 칼럼뿐 아니라 SQL에서 주어진 상수들에 대한 것도 포함한다)

--> rows 의  전체 행을 곱해주면 얼마나 많은 행을 읽을지 조사할 수 있지만

     실제 행수를 읽는 값과 정확히 일치하지 않는다는 말

 

ex) 테이블 4개 lefe outer join 되어있는 쿼리 실행계획

전체 테이블 row 수 -- 11899, 193, 83, 37606

 = 7721 * 1 * 1 * 195 = 1505595 (예상 rows수)

 

id select_type table partition type possible_keys key key_len ref rows filtered extra
1 SIMPLE NT   ALL IDX_DV_DEVICE_COME_NOTI       7721 9.1 Using where
1 SIMPLE DM   eq_ref PRIMARY PRIMARY 62 NEW.NT.MODEL_CD 1 10 Using where
1 SIMPLE DC   eq_ref PRIMARY PRIMARY 4 NEW.NT.COLOR_SEQ 1 100  
1 SIMPLE DS   ref PRIMARY PRIMARY 66

NEW.NT.MODEL_CD,

NEW.NT.COLOR_SEQ

195 100 Using where; Using index

 

 

4. filtered 컬럼

이 컬럼은 어떤 테이블에 대한 조인 조건이나 where 절에 있는 조건 등을 만족시키는 행이 어느 정도 될지에 대한 비관적 예측을 나타낸다. 이 퍼센트 값을 rows 컬럼에 곱해주면 쿼리 계획에서 이전 테이블과 조인할 때 얼마나 많은 행과 조인하게 될지에 대해 Mysql이 예측한 값을 보여준다.

 

ex) 테이블 4개 lefe outer join 되어있는 쿼리 실행계획

7721 에 필터 9.1 -> 7721 * 9.1 * 0.01 = 702.611 

where 절에 의해 거의 1/10으로 줄었구나..옵티마이져가 풀스캔으로 처리할만 하다.

 

5. Extra 컬럼

이 컬럼은 다른 컬럼에는 적합하지 않은 나머지 정보들을 나타낸다. Mysql 매뉴얼에서는 여기에 나올 수 있는 대부분의 값들이 설명되어 있다.

 

Using index -- 이는 Mysql이 테이블에 접근하지 않도록 커버링 인덱스를 사용한다는 것을 알려준다. 실행 계획상의 Type 컬럼이 index인 것과 혼동하지 않도록 주의하자

 

Using where -- 이 값은 Mysql 서버가 스토리지 엔진에서 값을 가져온 뒤 행을 필더링한다는 것을 의미한다. 인덱스에 포함된 컬럼들이 사용된 where 조건들은 대부분 스토리지 엔진에서 인덱스를 읽을 때 체크되기 때문에 where 절을 가진 쿼리가 모두 실행계획 상에 Using where를 표시하는 것은 아니다. 때로는 Using where가 있다는 사실이 다른 인덱스를 사용하면 쿼리를 더 효율적으로 만들 수 있다는 의미가 되기도 한다.

 

Using temporary -- 이는 Mysql이 쿼리 결과를 정렬하기 위해 임시 테이블을 사용한다는 것을 의미한다.

 

Using filesort -- 이는 Mysql이 결과의 순서를 맞추기 위해 인덱스 순서로 테이블을 읽는 것이 아니라 외부 정렬을 사용해야 한다는 것을 의마한다. Mysql은 두가지 파일 정렬 알고리즘들을 가지고 있는데, 각 방식은 메모리나 디스크에서 수행될 수 있으며 Explain으로는 어떤 파일 정렬이 사용될지나 정렬이 디스크에서 이루어질지 메모리에서 이루어질지 등을 알 수가 없다.

 

Range checked for each record(index map :N)

-- 이 값은 적합한 인덱스가 없으므로 각 레코드의 조인에서 각 인덱스들을 재평가한다는 것을 의미한다. N은 실행계획의 possible_keys 컬럼에 나타나 있는 인덱스들의 비트맵 값이다.

 

 

 

 

 

반응형

'쇠똥굴리기(BOOK) > Mysql 성능최적화' 카테고리의 다른 글

로컬 호스트 전용 연결  (0) 2019.09.17
운영체제 보안  (0) 2019.09.10
localhost와 127.0.0.1로 연결 및 차이점  (0) 2019.09.09
1.Mysql 아키텍처  (0) 2019.09.04
INTRO  (0) 2019.09.04
블로그 이미지

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

,
반응형

동일한 호스트에 있는 응용 프로그램에 Mysql을 사용하면 Mysql에 네트워크를 통한 접근을 전혀 허용하지 않아도 될 확률이 높다. 외부연결을 허용할 필요를 없애면 공격자가 여러분의 Mysql 서버에 접근하는 방법의 수가 줄어든다.

 

네트워크 접근 비활성화는 관리 변경을 원격에서 하는 기능을 제한하므로, Mysql 서버에 SSH로 로그인하거나, 여러분이 직접 변경하도록 해주는 웹 기반 응용프로그램을 설치해야 한다. 일부 윈도우 시스템에서는 원격 로그인이 어려울 수도 있는데, 그런 경우 원격 접근의 대안이 있다. 해결책 한 가지는 phpMyAdmin을 설치하는 것이다. 하지만 거기에도 보안상의 문제가 있으므로 주의해야한다.

 

skip_networking 옵션은 Mysql이 어떠한 TCP 소켓에 대해서도 접속 대기를 하지 않게 하지만, 유닉스 소켓의 연결은 계속 허용할 것이다. 네트워크 지원없이 Mysql을 시작하기는 간단하다. 다음 옵션을 my.cnf 파일의 [mysqld]절에 넣는다.

 

[mysqld]

skip_networking

 

skip_networking 옵션은 불편한 부작용이 있다. 이옵션은 안전한 원격 연결과 복제에 stunnel 등의 도구를 사용하는 것을 막으며 자바 응용프로그램의 연결을 막는다.( Mysql JDBC 드라이버인 Connector/J는 TCP/IP를 통해서만 연결한다.)

다른 방법으로는 아래와 같이 Mysql을 구성하는 것이 있다.

[mysqld]

bind_address=127.0.0.1

이렇게 하면 TCP 연결이 가능해지지만 로컬 장치에서만 가능하므로 안전하며 편리하다. 잘 알려진 GNIU/리눅스 배포판 일부는 이 구성을 기본으로 포함하도록 바뀌었다.

 

* skip_networking 으로 구성된 Mysql 슬레이브 서버 구성은 흥미롭다. 이것은 마스터에 연결을 시작하므로 슬레이브의 모든 데이터가 갱신되지만 어떠한 TCP  연결도 허용되지 않으므로 원격으로 피해를 입을 수 없는 더욱 안전한 백업 복제를 할 수 있다. 하지만 장애 조치 구성에서는 그러한 슬레이브를 사용할 수 없다.

다른 클라이언트가 거기에 연결할 수 없기 때문이다.

-> 마스터 & 슬레이브 구성할 떄는 들어가면 위험한 옵션이었군

반응형

'쇠똥굴리기(BOOK) > Mysql 성능최적화' 카테고리의 다른 글

실행계획(explain) 사용  (2) 2019.09.25
운영체제 보안  (0) 2019.09.10
localhost와 127.0.0.1로 연결 및 차이점  (0) 2019.09.09
1.Mysql 아키텍처  (0) 2019.09.04
INTRO  (0) 2019.09.04
블로그 이미지

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

,