wanted 백엔드 첼린지 2-1 MySQL 기본개념

MySQL의 기본 개념들과 실행 계획에 대해 알아보자.

 

 

쿼리 실행 순서

MySQL은 사용자가 실행한 쿼리를 SQL 파서가 파싱을 하여  파서 트리를 만든 뒤 파서 트리를 기준으로 옵티마이저가 해당 트리를 분석하여 실행한다.

위에서 파싱을 한다고 했는데, CS에서는 몇가지의 파싱 방식이 존재 한다.
1. Lexical analysis : The server breaks down the query into individual tokens, such as keywords, identifiers, and operators.
2. Syntax analysis : The server checks the query for proper syntax and structure, and verifies that all the elements of the query are used correctly.
3. Semantic analysis : The server checks that all the tables and columns referenced in the query exist, and verifies that the query is semantically correct.
4. Query optimization : The server optimizes the query for execution by creating an execution plan that outlines the most efficient way to retrieve the requested data.

 

일반적인 쿼리의 작동 순서는 아래와 같다.

1. 불필요한 조건 제거

2. 복잡한 연산 단순화

3. join 발생 시 read 순서 결정

4. 인덱스 결정

5. 임시테이블 필요 여부 결정

 

SELECT 쿼리의 실행 순서는 아래와 같다.

1. FROM          : 조회 테이블 확인

2. WHERE       : 데이터 추출 조건 확인 

3. GROUP BY : 컬럼 그룹화 확인 

4. HAVING      : 그룹화 조건 확인

5. SELECT      : 데이터 추출

6. ORDER BY : 데이터 순서 정렬

7. LIMIT            : 데이터 개수 제한

 

 

 

실행 계획(explain)

실행 계획은 사용자가 사용한 쿼리를 어떻게 불러올 것인지에 대한 계획이다. 사용자는 실행 계획 정보를 통해 SQL 튜닝이 가능하다. (실행 계획은 실제 수행 순서가 아닌 MySQL의 통계 정보를 기반으로 계산한 예측값이다.)

EXPLAIN is a command in MySQL that allows you to obtain information about how MySQL executes a query. It shows how MySQL plans to execute the query, including which tables and indexes it will use, how it will join the tables, and the order in which it will process the query.

- chatGPT

explain 명령어를 사용하여 실행계획에 대해 알 수 있다.

현재 test 테이블에는 id(INT, not null), name(varchar50), age(INT) 테이블이 있고,

약 백만개의 데이터를 MySQL의 PROCEDURE를 사용하여 넣어 두었다.

 

모든 데이터를 select 하는 쿼리에 explain 명령어를 추가하여 실행해보자.

이렇게 실행 계획이 테이블 형태로 나오는 것을 볼 수 있다. 각 칼럼은 다음과 같은 내용을 담고 있다.

• id: A unique identifier for each select operation in the query.
• select_type: The type of select operation being performed (e.g. SIMPLE, PRIMARY, SUBQUERY, etc.).
• table: The name of the table being accessed.
• type: The type of access method being used to retrieve data (e.g. ALL, INDEX, RANGE, etc.).
• possible_keys: A list of indexes that MySQL could potentially use for the query.
• key: The index that MySQL actually uses for the query.
• key_len: The length of the index that MySQL uses for the query.
• ref: The column or constant value that is used with the index to select rows.
• rows: The estimated number of rows that MySQL will examine to execute the query.
• Extra: Additional information about how MySQL executes the query, such as whether it needs to do a file sort, use a temporary table, or use a union.

 

 

이중에서 type은 테이블에서 어떻게 행 데이터를 가져올 것인가를 알려준다. 이는 조인의 타입을 알려주며, 쿼리 속도와 아주 밀접한 관련이 있다.

• const: This is the fastest access method and is used when the query refers to a single row using a primary key or unique index.

const는 제일 빠른 경우이다. 예로 특정 pk의 레코드를 찾을 때, 이는 고유한 하나의 값만을 찾으므로 매우 빠를 것이다.

 

• eq_ref: This access method is used when the query refers to a single row from a table using a unique index.
• ref: This access method is used when the query uses a non-unique index to find rows that match a specific value.
• fulltext: This access method is used when the query uses a full-text index to search for words or phrases within text columns.
• ref_or_null: This access method is similar to ref, but also includes rows that have a NULL value in the indexed column.
• index_merge: This access method is used when the query uses multiple indexes to retrieve rows from a table.
• unique_subquery: This access method is used when the query uses a subquery that returns a unique value to retrieve rows from a table.
• index_subquery: This access method is used when the query uses a subquery that returns a non-unique value to retrieve rows from a table.
• range: This access method is used when the query uses a range to find rows that match a specific set of values.
• index: This access method is used when the query scans the entire index to find rows that match a specific value.

 

• all: This is the slowest access method and is used when the query scans the entire table to find rows that match a specific value.

all은 제일 느린 옵션으로 모든 테이블을 하나하나 찾아봐야 하는 경우이다. 예로 test 테이블에서 where문에 name을 사용하면 모든 테이블을 스캔해야 하므로 매우 느릴 것이다.

 

실제로 위의 쿼리를 실행하는데 약 0.2sec가 걸렸고 이는 다른 쿼리들에 비해 상당히 오랜 시간이 걸림을 알 수 있다.

해서 name 컬럼에 대한 인덱스를 만들고 같은 쿼리를 날려보자.

CREATE INDEX test_name_index ON test(name);

그리고 똑같은 explain 쿼리를 수행하면 내용이 달라진다.

type이 ref로 바뀌었는데, 이는 인덱스 테이블이 join을 했기 때문이다. 그리고 possible_keys와 key에 방금 만든 인덱스 테이블이 추가되었고, Extra 역시 index Condition을 사용한 부가적인 작업을 했음을 알 수 있다. 그리고 explain을 제외하고 select 쿼리를 실행해보면

 

select의 속도 0.001sec으로 가 압도적으로 빨라졌음을 알 수 있다.

 

 

 

Join

join은 여러 테이블에서 데이터를 가져오는 방법이다. 즉 서로 다른 두개 이상의 테이블을 묶어서 하나의 결과 집합으로 만드는 것이다.

join의 종류에는 몇가지가 있다.

 

1. inner join

https://stevestedman.com/2015/03/mysql-join-types-poster/

두 테이블에 모두 같은 값을 가지고 있는 경우이다. 가장 많이 사용되는 조인이다.

예로 테이블2의 PK가 테이블1의 FK인 경우가 있다.

 

 

2. left join

https://stevestedman.com/2015/03/mysql-join-types-poster/

해당 조인은 왼쪽 테이블 값을 모두 가져오고 오른쪽 테이블은 왼쪽 테이블과 값이 매치되는 경우에만 가져오는 것이다. 만약 오른쪽 테이블에 왼쪽 테이블과 일치되는 값이 없다면 null로 표시된다.

 

 

3. right join

https://stevestedman.com/2015/03/mysql-join-types-poster/

left join과 반대이다.

 

 

4. CROSS join

https://www.w3schools.com/mysql/mysql_join_cross.asp

합집합과 같은 느낌으로 한쪽 테이블의 행 하나당 다른 쪽 테이블의 모든 행을 하나씩 모든 행들을 각각 조인한다.

아래 그림을 보면 이해가 쉽다.

https://www.w3resource.com/sql/joins/cross-join.php

 

https://www.devart.com/dbforge/sql/sqlcomplete/sql-cross-join.html

 

6. SELF join

본인 테이블을 대상으로 join을 한다. 위와 같은 경우 같은 테이블을 대상으로 id와 friend_id 일치 여부를 통해 join하고 있다.

 

 

 

 

Subquery

서브 쿼리는 쿼리 안에 쿼리 안에 포함된 또 다른 쿼리이다. 예로 아래와 같은 쿼리가 있을 수 있다.

SELECT *
FROM USER
WHERE USER.age
IN (
        SELECT USER.age
        FROM USER
        WHERE USER.age > 30
)

여기서 빨간색 쿼리문이 서브쿼리이다. 

서브 쿼리는 괄호로 감싸서 작성해야 하며 ORDER BY를 적용할 수 없다.

또한 서브쿼리는 메인쿼리보다 먼저 실행되며, 서브쿼리에서 실행된 값을 메인 쿼리가 사용하는 것이다. 

 

 

 

MySQL 내장 함수

• 내장 함수는 MySQL이 기본으로 제공하는 함수이다. 다양한 내장 함수들이 있지만 주로 사용되는 내장 함수는 aggregation(그룹함수)이다.

• aggregation은 여러 레코드들의 값을 병합하여 하나의 값으로 만들어 주며, GROUP BY와 함께 사용된다. 

•  COUNT(), MAX(), MIN(), AVG()와 같은 aggregation들이 있다.

• aggregate 함수는 where 조건에 충족되는 로우가 있을 때 마다 실행되어 하나의 결과를 반환한다. 

 

예로 아래와 같은 SQL이 있을 경우

SELECT SUM(amount) FROM sales WHERE product = 'Widget';

해당 SQL은 테이블들의 데이터를 보면서 product가 WIdget인지의 여부를 판단할 것이다. 이때 일치한다면 그때마다 SUN 함수가 실행되어 계속해서 amount값을 더해 나가는 것이다.

 

 

 

 

참고:

https://nohriter.tistory.com/129

https://jeong-pro.tistory.com/243

https://jaehoney.tistory.com/55

https://stevestedman.com/2015/03/mysql-join-types-poster/

https://www.w3schools.com/mysql/mysql_join_cross.asp

ㄴㅇ