JJON's StorY

출처 : https://dev.mysql.com/doc/refman/8.0/en/explain.html

MySQL 8.0.18에서는 EXPLAIN ANALYZE 쿼리를 실행하면 옵티마이저의 기대치가 실제 실행과 어떻게 일치 하는지에 대한 타이밍 및 추가 iterator-based 정보와 함께 쿼리를 실행하고 출력을 생성 합니다.
다음 정보가 제공됩니다.

- Estimated execution cost
- Estimated number of returned rows
- Time to return first row
- Time to return all rows (actual cost)
- Number of rows returned by the iterator
- Number of loops


조회 실행 정보는 TREE 노드가 반복자를 나타내는 출력 형식을 사용하여 표시됩니다 . 
EXPLAIN ANALYZE 항상 TREE 출력 형식을 사용하고 형식 지정자를 허용하지 않습니다. 
FOR CONNECTION 과 함께 사용할 수도 없습니다.

EXPLAIN ANALYZE 는 SELECT 명령문 만 사용할 수 있습니다 .

출력 예 :

예제 출력에 사용 된 테이블은 다음에 표시된 명령문으로 작성되었습니다.

출처 : https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

MySQL 8.0.18 부터 MySQL은 각 조인이 동일 조인 조건을 가지며 인덱스를 사용하지 않는 모든 쿼리에 해시 조인을 사용합니다.

해시 조인은 일반적으로 빠르며 이전 버전의 MySQL에서 사용 된 block nested loop 알고리즘 대신 사용됩니다.

다음 EXPLAIN FORMAT=TREE과 같이 사용하여 해시 조인이 사용되고 있음을 알 수 있습니다 .

주어진 조인에 해시 조인이 사용되고 있는지 확인하려면 옵션 EXPLAIN과 함께 사용해야합니다 FORMAT=TREE. EXPLAIN ANALYZE 사용하면 해시 조인에 대한 정보도 표시합니다.

해시 조인은 각 테이블 쌍에 대해 하나 이상의 조인 조건이 여기에 표시된 쿼리와 같은 동일 조인 인 경우 여러 조인과 관련된 쿼리에도 사용됩니다.

방금 표시된 것과 같은 경우 조인이 실행 된 후 동일 조인이 아닌 추가 조건이 필터로 적용됩니다. 

방금 표시된 출력에서 ​​볼 수 있듯이 여러 해시 조인은 여러 등일 조인 조건을 가진 조인에 사용될 수 있습니다.


조인 테이블과 동일 조인 조건이 쌍을 이룰 수 없는 경우 해시 조인을 사용할 수 없습니다.

이 경우 인덱스를 사용할 수없는 MySQL 8.0.18 이전 버전에서와 같이 느린 블록 중첩 루프 알고리즘이 사용됩니다.

해시 조인은 Cartesian product에도 적용됩니다.

기본적으로 MySQL은 가능할 때마다 해시 조인을 사용합니다. 해시 조인을 두 가지 방법 중 하나로 사용할지 여부를 제어 할 수 있습니다.

optimizer_switch 서버 시스템 변수를 global or session level에서 hash_join=on또는 hash_join=off에 대한 설정을 변경 합니다. 
기본값은 hash_join=on입니다.

경우에 따라 옵티마이저 파라미터를 사용 하거나 HASH_JOIN, NO_HASH_JOIN 힌트를 사용합니다.

해시 조인에 의한 메모리 사용량은 join_buffer_size 시스템 변수를 사용하여 제어 할 수 있습니다 . 
해시 조인은 join_buffer_size 보다 많은 메모리를 사용할 수 없습니다. 
해시 조인에 필요한 메모리가 사용 가능한 양을 초과하면 MySQL은 디스크의 파일을 사용하여 이를 처리합니다. 
이 경우 해시 조인이 open_files_limit에 설정된 것보다 많은 파일을 만들면 조인에 실패 할 수 있습니다. 

int(11) auto increment 최대 4294967295 이후에는 error

unsigned BIGINT 최대 18446744073709551615 까지 사용 가능 

-- 테이블의 상태 확인

show table status where name='T_HADOOP_LATEST_METRICS';

+-------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| Name                    | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |

+-------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| T_HADOOP_LATEST_METRICS | InnoDB |      10 | Compact    | 18124 |            144 |     2621440 |               0 |      7913472 |   4194304 |     4294967295 | 2017-11-06 13:57:16 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |

+-------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

1 row in set (0.00 sec)

-- 시퀀스 조회 (20억 이상)

SELECT table_name,table_comment,table_schema,table_type,engine,

version,row_format,table_rows,avg_row_length,data_length,

max_data_length,index_length,AUTO_INCREMENT,create_time,table_collation,

CEILING((data_length+index_length)/1024/1024) AS total_mb,

CEILING((data_length)/1024/1024) AS data_mb,

CEILING((index_length)/1024/1024) AS index_mb

FROM information_schema.tables 

WHERE table_schema='cloumon'

and Auto_increment > 2000000000

ORDER BY Auto_increment DESC

;

+-------------------------+---------------+--------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+----------------+---------------------+-----------------+----------+---------+----------+

| table_name              | table_comment | table_schema | table_type | engine | version | row_format | table_rows | avg_row_length | data_length | max_data_length | index_length | AUTO_INCREMENT | create_time         | table_collation | total_mb | data_mb | index_mb |

+-------------------------+---------------+--------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+----------------+---------------------+-----------------+----------+---------+----------+

| T_HADOOP_LATEST_METRICS |               | cloumon      | BASE TABLE | InnoDB |      10 | Compact    |      18628 |            140 |     2621440 |               0 |      7913472 |     4294967295 | 2017-11-06 13:57:16 | utf8_general_ci |       11 |       3 |        8 |

| T_HOST_LATEST_METRICS   |               | cloumon      | BASE TABLE | InnoDB |      10 | Compact    |      17253 |             92 |     1589248 |               0 |      3178496 |     4294967295 | 2017-11-06 13:57:18 | utf8_general_ci |        5 |       2 |        4 |

| T_HBASE_LATEST_METRICS  |               | cloumon      | BASE TABLE | InnoDB |      10 | Compact    |      14489 |            182 |     2637824 |               0 |      8454144 |     4142014594 | 2017-11-06 13:57:17 | utf8_general_ci |       11 |       3 |        9 |

| T_MR_LATEST_METRICS     |               | cloumon      | BASE TABLE | InnoDB |      10 | Compact    |       3402 |            105 |      360448 |               0 |      1081344 |     2501997189 | 2017-11-06 13:57:20 | utf8_general_ci |        2 |       1 |        2 |

+-------------------------+---------------+--------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+----------------+---------------------+-----------------+----------+---------+----------+

4 rows in set (2.59 sec)

-- 시퀀스 초기화

use cloumon;

alter table T_HADOOP_LATEST_METRICS auto_increment=1;

alter table T_HOST_LATEST_METRICS auto_increment=1;

alter table T_HBASE_LATEST_METRICS auto_increment=1;

alter table T_MR_LATEST_METRICS auto_increment=1;

기본적으로 100만개의 레코드를 벌크 인서트 할 때 아래 방법을 사용 합니다.

그럼 테이블 레코드 형식의 배열에 값을 저장하고 한번에 insert 하면,

느낌이 오시나요?

sql 실행 횟수가 줄어 듭니다.