DataRailroad

# 계정 확인
select user, host from mysql.user;

# 권한 부여
GRANT ALL PRIVILEGES ON [DB 이름].* TO '[계정 이름]'@'[계정 host]';
FLUSH PRIVILEGES;

# 권한 확인
SHOW GRANTS FOR '[계정 이름]'@'[계정 host]';

*MySQL에서 bulk insert를 할 때 DB에서 받아 줄 수 있는 최대치를 구하기 위함

*MySQL은 최대 용량으로 DB가 받아 줄 수 있는 양을 결정(개인적으로 확인했을 때)

# 확인 방법
SHOW VARIABLES LIKE 'max_allowed_packet';


# 테스트
INSERT INTO cm_max VALUES ( REPEAT('A', 10000000), REPEAT('가', 10000000));
# 10000000 은 10MB 정도, 만약 max_allowed_packet가 10MB 이상이면 max_allowed_packet 을 줄여서 테스트
# -> SQL Error [1301] [HY000]: (conn=257936) Result of repeat() was larger than max_allowed_packet (16777216) - truncated

# max_allowed_packet 줄이는 방법
SET GLOBAL max_allowed_packet = 128 * 1024 * 1024;

EXISTS 사용 방법

EXISTS는 먼저 쿼리를 조회해서 하나의 레코드를 가지고 오고 나서 서브쿼리를 실행하여 서브쿼리에 대한 결과가 존재하는지를 확인해서 있다면 레코드를 출력해줍니다.

여기서 가장 중요한 것은 서브쿼리에 대한 결과가 NULL이여도 결과가 존재한 것이기 때문에 결과가 True가 되어 레코드를 출력하게 됩니다.

TEST

테스트 해볼 사이트: https://sqltest.net/

SQL Script

CREATE TABLE mysql_test_a ( 
id VARCHAR(30),
name VARCHAR(30) NOT NULL
); 


INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('1', 'abab');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('2', 'John');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('3', 'dd');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('4', 'bb');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('5', 'aa');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('2', 'omokoe');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('6', 'John');

SQL Query

SELECT * FROM mysql_test_a a
where exists (
    select 1 from mysql_test_a b
    where a.id = b.id
    and b.name = 'John'
);

결과

'2', 'John'
'2', 'omokoe'
'6', 'John'

왜 '2', 'omokoe' 가 결과 값에 포함되어 있을까?

앞서 말했드이 먼저 쿼리를 조회하고 나서 서브쿼리를 조회 하는데, 서브쿼리 b 전체 행을 조회합니다. b 테이블에 id 값이 2인데 name에 값이 John인 레코드가 있으면 return True가 되고 레코드에 출력되는 것입니다. 그래서 '6', 'John'도 출력되는 것입니다.

Join 속도 개선

Join 속도 개선 전에 먼저 현재 작성한 쿼리가 어떤지 파악해야합니다.

Mysql기반 EXPLAIN을 통해 쿼리에 대해 분석해보겠습니다.

** 추후 다른 DBMS에서 확인하는 방법을 추가 업데이트 하도록 하겠습니다.

EXPLAIN 사용방법

EXPLAIN
SELECT * FROM 테이블명 WHERE 조건

결과

type 설명

• ALL, eq_ref: 조인시 기본 키나 고유키를 사용하여 하나의 값으로 접근(최대 1행만을 정확하게 패치).
• ref: 여러 개의 행을 패치할 가능성이 있는 접근.

컬럼 extra는 옵티마이저가 동작에 대한 힌트입니다.

extra 설명

EXPLAIN을 통해 분석하여 검색 범위 및 옵티마이저 동작에 문제가 없는데도 속도가 느리다면 아래 사항에 대해서 검토 해보면 좋습니다.

- left outer join 등 보다는 inner join 속도가 빠릅니다.

- 테이블에서 행의 순서를 가져올 때 분리해서 가져오는것도 생각해봐야합니다.(여러 테이블과 조인한 상태에서 테이블에 행을 가져오는건 속도 저하를 유발합니다.)

- 스칼라 서브 쿼리는 지양해야합니다.(실무에서 쿼리짜는 도중 스칼라 서브 쿼리를 사용했을 때 속도 차이가 많이 났습니다.)