2023/01/12 ~ 2023/01/17

mojh7 commented 1 year ago

다음 스터디

2023-01-17 화 pm 10:00

학습 범위

11.4.5 ~ 11.4.7.2

정리 범위

11.4.5 - 단
11.4.6 ~ 11.4.7.2 - 모

danbi5228 commented 1 year ago

11.4.5 LIMIT n

LIMIT 은 쿼리 결과에서 지정된 순서에 위치한 레코드만 가져오고자 할 때 사용됨
오라클의 ROWNUM과 달리 MySQL 에서는 WHERE 조건이 아니기 때문에 항상 쿼리의 마지막에 실행됨
필요한 레코드 건수만 준비되면 즉시 쿼리가 종료됨
- 즉, 쿼리가 ... ORDER BY col LIMIT n 이라고 할 때, 정렬이 완료되지 않았다고 하더라도 상위 n건까지만 정렬되면 작업을 멈춤
GROUP BY 절이나 DISTINCT 와 같이 LIMIT이 사용될 경우 (p. 78)
1. SELECT / FROM / LIMIT 만 사용 - 풀 테이블 스캔 진행하면서 LIMIT 만큼만 읽고 읽기작업 종료
2. SELECT / FROM / GROUP BY / LIMIT 사용 - GROUP BY로 그루핑과 정렬을 모두 수행한 뒤 LIMIT 수행 실질적으로 작업 내용을 크게 줄여주지는 못함
3. SELECT / DISTINCT / FROM / LIMIT 사용 - 정렬없이 유니크한 값만 만들어내면 됨 레코드 스캔과 동시에 중복 제거작업을 동시에 진행하면서 LIMIT 건수 만큼 채워지면 쿼리 종료
4. SELECT / FROM / WHERE / ORDER BY / LIMIT - 조건에 일치하는 레코드를 읽은 뒤 필요한 만큼 (LIMIT) 정렬되면 종료 이 작업도 실질적인 작업량을 크게 줄여주지는 못함
인자가 1개인 경우 상위 n개의 레코드를 가져옴
인자가 2개인 첫 번째 인자에 지정된 위치부터 두 번째 인자에 명시된 개수 만큼의 레코드를 가져옴
- LIMIT(시작위치(오프셋), 가져올 레코드 수). ex) LIMIT 10의 경우 LIMIT 0, 10 과 동일
LIMIT 인자로 별도의 표현식이나 서브쿼리를 사용할 수 없음
- ex) ... LIMIT (100-10) ; error
화면에 출력되는 레코드 수 보다는 서버가 결과를 만들어 내기 위해 어떠한 작업을 했는지가 중요함
- 동일한 갯수의 레코드가 화면에 출력되더라도, 수행시간이 크게 차이날 수 있음 (p. 80)
- 페이징을 위해 LIMIT 조건을 사용할 때, 처음 몇 개의 조회로 끝나지 않는다면 WHERE 조건을 추가해서 레코드를 읽어오도록 하는 것이 수행속도를 줄여줌. 이때 중복이나 누락이 발생하지 않도록 유의 (p.80 ~ 81)

mojh7 commented 1 year ago

11.4.6 COUNT()

결과 레코드의 건수를 반환하는 함수

칼럼이나 표현식을 인자로 받으며 "*" 를 사용할 수 있다

SELECT 절에 사용될 때 처럼 모든 칼럼을 가져오라는 의미가 아닌 그냥 레코드 자체를 의미
COUNT(*) 라고 해서 레코드의 모든 칼럼을 읽는 형태로 처리하지 않음
굳이 COUNT(PK) 나 COUNT(1)과 같이 사용하지 않고 COUNT(*) 라고 표현해도 동일한 처리 성능을 보임

mysql> select * from member;
+----+------------+--------------------------------------------------------------+---------------+----------------------------+----------------------------+
| id | account_id | password                                                     | role          | created_at                 | updated_at                 |
+----+------------+--------------------------------------------------------------+---------------+----------------------------+----------------------------+
|  1 | test1      | $2a$10$nT0gOH.JjmBi/5vMBIBOPeuk8kbHG4et1KR/3tNtsr/il.Yj0QKBm | ROLE_SELLER   | 2022-05-19 16:26:42.482000 | 2022-05-19 16:26:42.482000 |
|  2 | test2      | $2a$10$4ibQ0mvSnVXoRexSe8ogL.1s7Xxb8OZl4bb9lpHgUpfBEc0NT4lba | ROLE_CUSTOMER | 2022-05-19 16:26:47.914000 | 2022-05-19 16:26:47.914000 |
|  3 | test3      | $2a$10$G.qUWO9y.m8v3a7N93oyIuZr5OzR/9LuM6mNcmUzXwL7sbOx4UqHe | ROLE_CUSTOMER | 2022-05-19 16:26:51.337000 | 2022-05-19 16:26:51.337000 |
|  4 | test4      | $2a$10$1h7PIxB8mCORT69vsg1Lve6YFzl/O4k349WrPhlN76BUB905JVQqC | ROLE_CUSTOMER | 2022-05-21 11:36:39.744000 | 2022-05-21 11:36:39.744000 |
|  5 | test55     | $2a$10$DoxFvCayK1r.vRstW7xHI.Awy7PuRvmMXU7TTzm4Rp1W5fqeDl6ua | ROLE_CUSTOMER | 2022-05-22 11:59:01.942000 | 2022-05-22 11:59:01.942000 |
+----+------------+--------------------------------------------------------------+---------------+----------------------------+----------------------------+
5 rows in set (0.09 sec)

mysql> select count(*) from member;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

mysql> select count(id) from member;
+-----------+
| count(id) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(1) from member;
+----------+
| count(1) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select count(2) from member;
+----------+
| count(2) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select * from coupon;
+----+-----------+---------+-------------+-----------+---------+----------------------------+----------------------------+----------------------------+----------------------------+
| id | member_id | title   | description | max_count | status  | start_at                   | end_at                     | created_at                 | updated_at                 |
+----+-----------+---------+-------------+-----------+---------+----------------------------+----------------------------+----------------------------+----------------------------+
|  1 |         1 | coupon1 | ?ㅻ챸1     |         0 | CREATED | 2022-05-20 22:51:01.000000 | 2022-07-21 13:30:00.000000 | 2022-05-22 13:04:56.544000 | 2022-05-22 13:04:56.544000 |
|  2 |         1 | coupon1 | ?ㅻ챸1     |   1000000 | CREATED | 2022-05-21 01:00:00.000000 | 2022-07-21 13:30:00.000000 | 2022-05-22 13:10:17.581000 | 2022-05-22 13:10:17.581000 |
|  3 |         1 | coupon1 | ?ㅻ챸1     |         2 | CREATED | 2022-06-21 01:00:00.000000 | 2022-09-21 13:30:00.000000 | 2022-06-24 07:13:21.184000 | 2022-06-24 07:13:21.184000 |
|  4 |         1 | 荑좏룿A | ?ㅻ챸A     |      3000 | CREATED | 2022-06-24 00:00:00.000000 | 2022-08-24 13:30:00.000000 | 2022-06-24 07:13:55.794000 | 2022-06-24 07:13:55.794000 |
|  5 |         1 | 荑좏룿A | ?ㅻ챸A     |      3000 | ENABLED | 2022-06-24 00:00:00.000000 | 2022-08-24 13:30:00.000000 | 2022-06-26 13:38:08.330000 | 2022-06-26 13:38:08.330000 |
|  6 |         1 | 荑좏룿A | ?ㅻ챸A     |      3000 | CREATED | 2022-06-24 00:00:00.000000 | 2022-08-24 13:30:00.000000 | 2022-06-30 11:59:55.575000 | 2022-06-30 11:59:55.575000 |
|  8 |         1 | 荑좏룿A | ?ㅻ챸A     |      3000 | CREATED | 2022-06-24 00:00:00.000000 | 2022-08-24 13:30:00.000000 | 2022-06-30 13:17:49.072000 | 2022-06-30 13:17:49.072000 |
+----+-----------+---------+-------------+-----------+---------+----------------------------+----------------------------+----------------------------+----------------------------+
7 rows in set (0.01 sec)

mysql> select count(*) from coupon;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> select count(12321) from coupon;
+--------------+
| count(12321) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(1) from coupon;
+----------+
| count(1) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> select count(id) from coupon;
+-----------+
| count(id) |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

MyISAM 스토리지 엔진을 사용하는 테이블은 항상 테이블의 메타 정보에 전체 레코드 건수를 관리함

그래서 SELECT COUNT(*) FROM tb_table 과 같이 WHERE 조건이 없는 COUNT(*) 쿼리는 MySQL 서버가 실제 레코드 건수를 세어보지 않아도 바로 결과를 반환할 수 있기 때문에 빠르게 처리됨
WHERE 조건이 있는 COUNT(*)쿼리는 조건에 일치하는 레코드를 읽어야 해서 일반적인 DBMS와 같이 처리

InnoDB 스토리지 엔진에 경우 WHERE 조건이 없는 COUNT(*) 쿼리라 해도 직접 데이터나 인덱스를 읽어야만 레코드 건수를 가져올 수 있다

큰 테이블에서 COUNT() 함수를 사용하는 작업 주의

테이블이 가진 대략적인 레코드 건수로 충분하다면 SHOW TABLE STATUS명령으로 통계 정보를 참조하는 것도 좋은 방법

통계 정보가 실제 레코드 건수랑 많은 차이가 날 수 있는데 이 때는 ANALYZE TABLE 명령으로 통계 정보를 갱신하면 됨

COUNT(*)쿼리에서 가장 많이 하는 실수

ORDER BY나 LEFT JOIN과 같은 레코드 건수를 가져오는 것과는 무관한 작업을 포함하는 것

페이징 처리를 위해 사용할 때가 많은데, 이 때 많은 개발자들이 SELECT 쿼리를 그대로 복사해 칼럼이 명시된 부분을 삭제하고 COUNT(*) 함수로 대체해서 사용

이 때 단순히 COUNT(*) 만 실행하는 쿼리임에도 ORDER BY나 별도의 체크 조건을 가지지 않은 LEFT JOIN이 사용된 채로 실행될 때가 많다

ORDER BY 절은 어떤 경우에도 필요치 않음
LEFT JOIN도 레코드 건수의 변화가 없거나 아우터 테이블에서 별도의 체크를 하지 않아도 되는 경우 모두 제거하는 것이 성능상 좋다

MySQL 8.0 버전부터는 SELECT COUNT(*) 쿼리에 사용된 ORDER BY 절은 옵티마이저가 무시하도록 개선됨

일반적으로 칼럼의 값을 SELECT 하는 쿼리보다 COUNT(*) 쿼리가 훨씬 빠르게 실행될 것으로 생각

인덱스를 제대로 사용하도록 튜닝되지 못한 COUNT(*) 쿼리는 페이징 해서 데이터를 가져오는 쿼리보다 훨씬 느리게 실행될 수도 있음

COUNT() 함수에 칼럼명이나 표현식이 인자로 사용되면 칼럼이나 표현식이 NULL이 아닌 레코드 건수만 반환

NULL이 될 수 있는 칼럼을 COUNT() 함수에 사용할 때 의도대로 작동하는지 확인하는 것이 좋다

11.4.7 JOIN()

JOIN이 어떻게 인덱스를 사용하는지 쿼리 패턴별로 살펴보기

11.4.7.1 Join의 순서와 인덱스

인덱스 레인지 스캔 두 과정

인덱스를 탐색(Index Seek)하는 단계
인덱스를 스캔(Index Scan)하는 단계

인덱스를 이용해서 쿼리하는 작업은 가져오는 레코드는 건수가 소량이기 때문에 인덱스 스캔작업은 부하가 작다

특정 인덱스 키를 찾는 인덱스 탐색 작업은 상대적으로 부하가 높다

조인 작업에서 드라이빙 테이블을 읽을 때 인덱스 탐색 작업 단 한번만 수행, 그 이후 스캔만 실행하면 됨

하지만 드리븐 테이블에서는 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복

드라이빙 테이블과 드리븐 테이블이 1:1로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 더 큰 부하를 가짐

그래서 옵티마이저는 항상 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다

SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=de.emp_no;

이 두 테이블의 조인쿼리에서 employees 테이블의 emp_no 칼럼과 dept_emp 테이블의 emp_no 칼럼에 각각 인덱스가 있을 때와 없을 때 조인 순서

두 칼럼 모두 각각 인덱스가 있는 경우

어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리 할 수 있음
이럴 때 옵티마이저가 통계 정보를 이용해 적절히 드라이빙 테이블을 선택하게 됨
보통 어느 쪽 테이블이 드라이빙 테이블이 되든 옵티아미저가 선택하는 방법이 최적일 때가 많음

employees.emp_no에만 인덱스가 있는 경우

dept_emp 테이블이 드리븐 테이블로 선택될 때
- employees 테이블의 레코드 건수만큼 dept_emp 테이블을 풀 스캔해야만 where 절에 조건에 일치하는 레코드를 찾을 수 있다
그래서 옵티마이저는 항상 dept_emp 테이블을 드라이빙 테이블로 선택한다
e.emp_no=100001 과 같이 employees 테이블을 아주 효율적으로 접근 할 수 있는 조건이 있더라도 employees 테이블을 드라이빙 테이블로 선택하지 않을 가능성이 높다
드리븐 테이블이 보통 큰 부하를 차지하니 인덱스를 이용할 수 있는 테이블을 드리븐 테이블로 선택하게 되는듯???

dept_emp.emp_no에만 인덱스가 있는 경우

바로 위에 경우와 반대로 처리됨, employees 테이블의 반복된 풀 스캔을 피하기 위해 employees 테이블을 드라이빙 테이블로 선택하고 dept_emp 테이블을 드리븐 테이블로 조인을 수행

두 칼럼 모두 인덱스가 없는 경우

두 칼럼 모두 각각 인덱스가 있는 겨웅와 같이 어느 테이블을 드라이빙으로 선택하더라도 드리븐 테이블은 풀 스캔이 발생
레코드 건수가 적은 테이블을 드라이빙 테이블로 선택하는 것이 훨씬 효율적
조인 조건을 빠르게 처리할 적절한 인덱스가 없는 경우 mysql 8.0.18 이전 버전까지는 블록 네스티드 루프 조인을 사용했음
8.0.18 버전 부터는 블록 네스티드 루프 조인이 없어지고 해시 조인이 도입됨(9.3.1.19절)

11.4.7.2 Join 칼럼의 데이터 타입

WHERE 절에서와 마찬가지로 테이블의 조인 조건에서도 비교 대상 칼럼과 표현식의 데이터 타입을 동일하게 사용해야한다

일치하지 않으면 인덱스를 효율적으로 이용할 수 없다

# tb_test1.user_type은 INT 타입, tb_test2.user_type은 CHAR(1)

SELECT *
FROM tb_test1 tb1, tb_test2 tb2
WHERE tb1.user_type=tb2.user_type;

tb_test2 테이블의 user_type 칼럼은 PK라서 이 쿼리에서 최소한 드리븐 테이블은 PK키를 이용한 인덱스 레인지 스캔을 사용해 조인이 처리될 것으로 예상됨

하지만 실제 실행 계획에서 두 테이블 모두 풀 테이블 스캔으로 접근했다

실행 계획 두 번째 줄의 Extra 칼럼을 보면 조인 버퍼를 이용한 해시 조인이 실행된 것을 알 수 있다

11.4.3.2 문자열이나 숫자 비교에서 살펴본 것과 같은 것

비교 조건의 양쪽 항이 테이터 타입이 달라서 tb_test2 테이블의 user_type 칼럼을 CHAR(1)에서 INT로 변환해서 비교를 수행하게된다

인덱스의 변형이 필요하기 때문에 tb_test2 테이블의 인덱스를 제대로 사용할 수 없게됨

옵티마이저는 드리븐 테이블이 인덱스 레인지 스캔이 아닌 풀 테이블 스캔이 필요한 것을 알고 조금 더 빠르게 처리하고자 조인 버퍼를 활용한 해시 조인을 택하게됨

인덱스 사용에 영향을 미치는 데이터 타입 불일치 문제가 발생하지 않는 데이터 관계

CHAR와 VARCHAR
INT와 BIGINT, SMALLINT
DATE와 DATETIME

문제가 될 가능성이 높은 대표적인 패턴들

CHAR 타입과 INT 타입의 비교와 같이 데이터 타입의 종류가 완전히 다른 경우
같은 CHAR 타입이더라도 문자 집합이나 콜레이션이 다른 경우
같은 INT 타입이더라도 부호(Sign)의 존재 여부가 다른 경우

87p 쿼리를 보면

각 테이블의 user_type 칼럼의 타입을 CHAR(1)로 똑같이 했지만 COLLATE가 다름

그래서 실행 계획을 보면 드리븐 테이블을 풀 테이블 스캔하는 실행 계획으로 조인이 실행됐기 때문에

옵티마이저가 조인 버퍼를 사용함

표준화 규칙을 가지고 데이터 모델링된 경우에는 이러한 케이스가 잘 발생하지 않지만 조금씩 데이터 모델을 변경하다 보면 이런 현상이 자주 발생

칼럼의 모든 문자 집합과 콜레이션을 통일하는 것만이 유일한 해결책

Character Set

문자가 컴퓨터에 저장될 때 어떠한 코드로 인코딩되어 저장될 것인지에 대한 규칙이 정의되어있는 집합

Collation

특정 문자셋에 의해 DB에 저장된 값들을 검색하거나 정렬 등의 작업을 위해 문자들간에 서로 비교할 때 사용하는 규칙들의 집합

https://linuxism.ustd.ip.or.kr/432

mojh7 / real-mysql-study