TypeORM 쿼리 로그, MySQL 쿼리 플랜, Query Builder을 이용한 쿼리 최적화 with NestJS

박재하·2023년 12월 1일

EXPLAIN mysql query builder typeOrm 쿼리 최적화 쿼리 플랜

프로젝트 <별 하나에 글 하나>

목록 보기

29/40

목표

GET /stars, GET /post/:id 프론트 요청에 맞게 새로 구현

기존에 다 학습하고 검증까지 완료했던 기능들의 인터페이스만 변경하는 것이므로 간단히 과정과 결과만 요약하겠다.

GET /star

module엔 AuthModule, TypeORMModule(Board), MongooseModule(Star) 등록
controller엔 다음을 구현
- by-author 파라미터로 별 목록을 리턴하는 findAllStarsByAuthor
- Auth Guard로 user data를 얻어 자신의 별 목록을 리턴하는 findAllStarsMine
service엔 다음을 구현
- author에 따른 글 목록을 받고, star id를 이용해 MongoDB에서 별 정보를 받아 양식에 맞게 별 목록을 리턴

GET /post/:id

service에선 image를 download하는 대신 이미지의 Key(uuid)만 전달
controller에선 Board를 폼데이터로 전달하는 대신 다음을 구현
- 양식(GetPostByIdResDto)에 맞게 id title, content, like_cnt를 반환하며
- images는 NCP Object Storage에 바로 접근할 수 있는 url을 생성하여 배열로 반환

동작 화면

GET /star

GET /post/:id

쿼리 최적화

TypeORM 쿼리 로그

import { TypeOrmModuleOptions } from '@nestjs/typeorm';
import { configDotenv } from 'dotenv';
configDotenv();

export const typeOrmConfig: TypeOrmModuleOptions = {
	type: process.env.MYSQL_TYPE as any,
	host: process.env.MYSQL_HOST,
	port: parseInt(process.env.MYSQL_PORT),
	username: process.env.MYSQL_USERNAME,
	password: process.env.MYSQL_PASSWORD,
	database: process.env.MYSQL_DATABASE,
	entities: [__dirname + '/../**/*.entity.{js,ts}'],
	synchronize: true,
	logging: true,
};

logging: true를 추가해 TypeORM 메소드 실행 시마다 어떤 쿼리를 실제로 보내는 지를 확인해 본다.

GET /stars 쿼리 로그 확인

SELECT `Board`.`id` AS `Board_id`,
  `Board`.`title` AS `Board_title`,
  `Board`.`content` AS `Board_content`,
  `Board`.`created_at` AS `Board_created_at`,
  `Board`.`updated_at` AS `Board_updated_at`,
  `Board`.`like_cnt` AS `Board_like_cnt`,
  `Board`.`star` AS `Board_star`,
  `Board`.`userId` AS `Board_userId`,
  `Board__Board_user`.`id` AS `Board__Board_user_id`,
  `Board__Board_user`.`username` AS `Board__Board_user_username`,
  `Board__Board_user`.`password` AS `Board__Board_user_password`,
  `Board__Board_user`.`nickname` AS `Board__Board_user_nickname`,
  `Board__Board_user`.`created_at` AS `Board__Board_user_created_at`,
  `Board__likes`.`id` AS `Board__likes_id`,
  `Board__likes`.`username` AS `Board__likes_username`,
  `Board__likes`.`password` AS `Board__likes_password`,
  `Board__likes`.`nickname` AS `Board__likes_nickname`,
  `Board__likes`.`created_at` AS `Board__likes_created_at`,
  `Board__images`.`id` AS `Board__images_id`,
  `Board__images`.`mimetype` AS `Board__images_mimetype`,
  `Board__images`.`filename` AS `Board__images_filename`,
  `Board__images`.`size` AS `Board__images_size`,
  `Board__images`.`created_at` AS `Board__images_created_at`,
  `Board__images`.`boardId` AS `Board__images_boardId`
  FROM `board` `Board`
  LEFT JOIN `user` `Board__Board_user` ON `Board__Board_user`.`id`=`Board`.`userId`
  LEFT JOIN `board_likes_user` `Board_Board__likes` ON `Board_Board__likes`.`boardId`=`Board`.`id`
  LEFT JOIN `user` `Board__likes` ON `Board__likes`.`id`=`Board_Board__likes`.`userId`
  LEFT JOIN `image` `Board__images` ON `Board__images`.`boardId`=`Board`.`id`
  WHERE (`Board__Board_user`.`nickname` = ?) -- PARAMETERS: ["test2"]

MySQL 쿼리 플랜 조회

학습메모 3을 참고해 쿼리 플랜을 조회해보자.

mysql -h 192.168.64.2 -u ubuntu -p

로컬에서 VM 데이터베이스에 원격 접근

show databases;
use b1g1;

이제 앞선 쿼리 앞에 EXPLAIN문을 붙여 쿼리 플랜을 확인해본다.

EXPLAIN [SELECT ... 쿼리 구문]

그 전에 쿼리 잘 실행되나 확인하고(? 구문에 'test2'를 삽입해 테스트)

EXPLAIN 붙여서 결과 보자.

mysql> EXPLAIN SELECT `Board`.`id` AS `Board_id`, `Board`.`title` AS `Board_title`, `Board`.`content` AS `Board_content`, `Board`.`created_at` AS `Board_created_at`, `Board`.`updated_at` AS `Board_updated_at`, `Board`.`like_cnt` AS `Board_like_cnt`, `Board`.`star` AS `Board_star`, `Board`.`userId` AS `Board_userId`, `Board__Board_user`.`id` AS `Board__Board_user_id`, `Board__Board_user`.`username` AS `Board__Board_user_username`, `Board__Board_user`.`password` AS `Board__Board_user_password`, `Board__Board_user`.`nickname` AS `Board__Board_user_nickname`, `Board__Board_user`.`created_at` AS `Board__Board_user_created_at`, `Board__likes`.`id` AS `Board__likes_id`, `Board__likes`.`username` AS `Board__likes_username`, `Board__likes`.`password` AS `Board__likes_password`, `Board__likes`.`nickname` AS `Board__likes_nickname`, `Board__likes`.`created_at` AS `Board__likes_created_at`, `Board__images`.`id` AS `Board__images_id`, `Board__images`.`mimetype` AS `Board__images_mimetype`, `Board__images`.`filename` AS `Board__images_filename`, `Board__images`.`size` AS `Board__images_size`, `Board__images`.`created_at` AS `Board__images_created_at`, `Board__images`.`boardId` AS `Board__images_boardId` FROM `board` `Board` LEFT JOIN `user` `Board__Board_user` ON `Board__Board_user`.`id`=`Board`.`userId`  LEFT JOIN `board_likes_user` `Board_Board__likes` ON `Board_Board__likes`.`boardId`=`Board`.`id` LEFT JOIN `user` `Board__likes` ON `Board__likes`.`id`=`Board_Board__likes`.`userId`  LEFT JOIN `image` `Board__images` ON `Board__images`.`boardId`=`Board`.`id` WHERE (`Board__Board_user`.`nickname` = 'test2' );
+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-------------+
| id | select_type | table              | partitions | type   | possible_keys                          | key                            | key_len | ref                            | rows | filtered | Extra       |
+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-------------+
|  1 | SIMPLE      | Board__Board_user  | NULL       | const  | PRIMARY,IDX_e2364281027b926b879fa2fa1e | IDX_e2364281027b926b879fa2fa1e | 202     | const                          |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Board              | NULL       | ALL    | FK_c9951f13af7909d37c0e2aec484         | NULL                           | NULL    | NULL                           |   42 |    92.86 | Using where |
|  1 | SIMPLE      | Board_Board__likes | NULL       | ref    | PRIMARY,IDX_cc61d27acb747ad30ab37c7399 | PRIMARY                        | 4       | b1g1.Board.id                  |    1 |   100.00 | Using index |
|  1 | SIMPLE      | Board__likes       | NULL       | eq_ref | PRIMARY                                | PRIMARY                        | 4       | b1g1.Board_Board__likes.userId |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Board__images      | NULL       | ref    | FK_7d46d16528472a594493ecb6600         | FK_7d46d16528472a594493ecb6600 | 5       | b1g1.Board.id                  |    5 |   100.00 | NULL        |
+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-------------+
5 rows in set, 1 warning (0.02 sec)

GET /stars 쿼리 최적화

쿼리를 수정하고, 이를 MySQL 쿼리 플랜으로 지속적으로 확인하며 최적화를 진행해보자.

이름을 바꾸는 구문들 때문에 길어진 것도 있지만, 그래도 쿼리가 너무 길다.
like, images와 관련된 컬럼과 테이블은 조회 및 JOIN할 필요가 없음
- user만 LEFT JOIN으로 nickname 조회
- 컬럼은 id, title, star_id만 조회
쿼리 단에서 부터 필요한 컬럼들만 추출해서 보내보자.

SELECT `Board`.`id` AS `Board_id`,
  `Board`.`title` AS `Board_title`,
  `Board`.`star` AS `Board_star`
  FROM `board` `Board`
  LEFT JOIN `user` `Board__Board_user` ON `Board__Board_user`.`id`=`Board`.`userId`
  WHERE (`Board__Board_user`.`nickname` = 'test2');

SELECT board.id AS id,
board.title AS title,
board.star AS star_id
FROM board
LEFT JOIN user
ON user.id = board.userId
WHERE user.nickname = 'test';

더 간단하게는 위와 같이만 출력해도 된다.

EXPLAIN을 붙여보자.

EXPLAIN
SELECT board.id AS id,
board.title AS title,
board.star AS star_id
FROM board
LEFT JOIN user
ON user.id = board.userId
WHERE user.nickname = 'test';

mysql> EXPLAIN
    -> SELECT board.id AS id,
    -> board.title AS title,
    -> board.star AS star_id
    -> FROM board
    -> LEFT JOIN user
    -> ON user.id = board.userId
    -> WHERE user.nickname = 'test';
+----+-------------+-------+------------+-------+----------------------------------------+--------------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                          | key                            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+----------------------------------------+--------------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY,IDX_e2364281027b926b879fa2fa1e | IDX_e2364281027b926b879fa2fa1e | 202     | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | board | NULL       | ref   | FK_c9951f13af7909d37c0e2aec484         | FK_c9951f13af7909d37c0e2aec484 | 5       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+----------------------------------------+--------------------------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

데이터가 많이 없어 속도의 차이는 확인하기 힘들지만, 조회 단계가 user, board 두 단계로 확연히 줄어든 것을 확인할 수 있다.

또한 user.nickname을 세컨더리 인덱스로 등록해서 성능을 향상하려 하였으나, 이미 플랜의 첫 레코드에 Extra: Using index, Key 중 IDX_e2364281027b926b879fa2fa1e가 사용된 것을 확인될 수 있는데, 정체가 뭘까?

-- Active: 1693885143266@@192.168.64.2@3306@b1g1
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `nickname` varchar(50) NOT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_78a916df40e02a9deb1c4b75ed` (`username`),
  UNIQUE KEY `IDX_e2364281027b926b879fa2fa1e` (`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

user 테이블의 ddl을 확인해보면 nickname컬럼을 TypeORM Entity에서 unique: true로 설정해준 덕분에, 알아서 UNIQUE KEY가 생성되고, 이로 인해 인덱싱이 되어있음을 확인할 수 있다.

학습메모 4를 참고하여 Unique 설정만으로 세컨더리 인덱싱이 된다는 사실을 확인할 수 있었다. 잘했어 과거의 나!

TypeORM Query Builder로 최적화 적용

이후 querybuilder를 이용해 이것을 실제로 적용하고 TypeORM 쿼리 로그로 확인해보자.

// const boards = await this.boardRepository.findBy({
// 	user: { nickname: author },
// });
const boards = await this.boardRepository
	.createQueryBuilder()
	.select('board.id as id')
	.addSelect('board.title as title')
	.addSelect('board.star as star')
	.from(Board, 'board')
	.leftJoinAndSelect('board.user', 'user')
	.where('user.nickname = :nickname', { nickname: author })
	.getMany();

음.. 빈 배열이 나오고 쿼리도 멋대로 바꾸는 경향이 좀 있다.
그들이 원하는 방식으로 쿼리를 짜줘야 하나보다...

여러차례 검색해보고 gpt도 돌려보고 테스트도 해보고 하다 다음 코드로 잘 동작함을 확인할 수 있었다.

const boards = await this.boardRepository
	.createQueryBuilder()
	.select(['board.id', 'board.title', 'board.star'])
	.from(Board, 'board')
	.leftJoin('board.user', 'user')
	.where('user.nickname = :nickname', { nickname: author })
	.getMany();

테스트 결과 동작도 똑같이 잘 한다.

뿌듯

SELECT `board`.`id` AS `board_id`,
`board`.`title` AS `board_title`,
`board`.`star` AS `board_star`
FROM `board` `Board`, `board` `board`
LEFT JOIN `user` `user` ON `user`.`id`=`board`.`userId`
WHERE `user`.`nickname` = ? -- PARAMETERS: ["test2"]

위는 개선된 최종 쿼리 로그!

GET /post/:id 쿼리 로그 확인

SELECT DISTINCT `distinctAlias`.`Board_id` AS `ids_Board_id`
FROM (
  SELECT `Board`.`id` AS `Board_id`,
  `Board`.`title` AS `Board_title`,
  `Board`.`content` AS `Board_content`,
  `Board`.`created_at` AS `Board_created_at`,
  `Board`.`updated_at` AS `Board_updated_at`,
  `Board`.`like_cnt` AS `Board_like_cnt`,
  `Board`.`star` AS `Board_star`,
  `Board`.`userId` AS `Board_userId`,
  `Board__likes`.`id` AS `Board__likes_id`,
  `Board__likes`.`username` AS `Board__likes_username`,
  `Board__likes`.`password` AS `Board__likes_password`,
  `Board__likes`.`nickname` AS `Board__likes_nickname`,
  `Board__likes`.`created_at` AS `Board__likes_created_at`,
  `Board__images`.`id` AS `Board__images_id`,
  `Board__images`.`mimetype` AS `Board__images_mimetype`,
  `Board__images`.`filename` AS `Board__images_filename`,
  `Board__images`.`size` AS `Board__images_size`,
  `Board__images`.`created_at` AS `Board__images_created_at`,
  `Board__images`.`boardId` AS `Board__images_boardId`,
  `Board__user`.`id` AS `Board__user_id`,
  `Board__user`.`username` AS `Board__user_username`,
  `Board__user`.`password` AS `Board__user_password`,
  `Board__user`.`nickname` AS `Board__user_nickname`,
  `Board__user`.`created_at` AS `Board__user_created_at`
  FROM `board` `Board`
  LEFT JOIN `board_likes_user` `Board_Board__likes`
  ON `Board_Board__likes`.`boardId`=`Board`.`id`
  LEFT JOIN `user` `Board__likes` ON `Board__likes`.`id`=`Board_Board__likes`.`userId`
  LEFT JOIN `image` `Board__images` ON `Board__images`.`boardId`=`Board`.`id`
  LEFT JOIN `user` `Board__user` ON `Board__user`.`id`=`Board`.`userId`
  WHERE (`Board`.`id` = ?)
) `distinctAlias`
ORDER BY `Board_id` ASC LIMIT 1;
-- PARAMETERS: [222]

SELECT `Board`.`id` AS `Board_id`,
`Board`.`title` AS `Board_title`,
`Board`.`content` AS `Board_content`,
`Board`.`created_at` AS `Board_created_at`,
`Board`.`updated_at` AS `Board_updated_at`,
`Board`.`like_cnt` AS `Board_like_cnt`,
`Board`.`star` AS `Board_star`,
`Board`.`userId` AS `Board_userId`,
`Board__likes`.`id` AS `Board__likes_id`,
`Board__likes`.`username` AS `Board__likes_username`,
`Board__likes`.`password` AS `Board__likes_password`,
`Board__likes`.`nickname` AS `Board__likes_nickname`,
`Board__likes`.`created_at` AS `Board__likes_created_at`,
`Board__images`.`id` AS `Board__images_id`,
`Board__images`.`mimetype` AS `Board__images_mimetype`,
`Board__images`.`filename` AS `Board__images_filename`,
`Board__images`.`size` AS `Board__images_size`,
`Board__images`.`created_at` AS `Board__images_created_at`,
`Board__images`.`boardId` AS `Board__images_boardId`,
`Board__user`.`id` AS `Board__user_id`,
`Board__user`.`username` AS `Board__user_username`,
`Board__user`.`password` AS `Board__user_password`,
`Board__user`.`nickname` AS `Board__user_nickname`,
`Board__user`.`created_at` AS `Board__user_created_at`
FROM `board` `Board`
LEFT JOIN `board_likes_user` `Board_Board__likes`
ON `Board_Board__likes`.`boardId`=`Board`.`id`
LEFT JOIN `user` `Board__likes`
ON `Board__likes`.`id`=`Board_Board__likes`.`userId`
LEFT JOIN `image` `Board__images`
ON `Board__images`.`boardId`=`Board`.`id`
LEFT JOIN `user` `Board__user`
ON `Board__user`.`id`=`Board`.`userId`
WHERE ( (`Board`.`id` = ?) ) AND ( `Board`.`id` IN (222) );
-- PARAMETERS: [222]

쿼리 요청이 무려 두 번 일어난다.

절차

위 과정과 마찬가지로 쿼리 플랜을 확인하고, 필요없는 쿼리 로직을 삭제하여 최적화한 후 쿼리 플랜을 통해 잘 개선되었는지 확인한다.
이후 이를 다시 TypeORM QueryBuilder를 통해 재현하여 TypeORM에 개선된 쿼리가 잘 반영되었는지를 로그로 확인한다.

MySQL 쿼리 플랜 조회

마찬가지로 쿼리가 잘 동작하는지 확인하고, 앞에 EXPLAIN 키워드 삽입.

+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-----------------+
| id | select_type | table              | partitions | type   | possible_keys                          | key                            | key_len | ref                            | rows | filtered | Extra           |
+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-----------------+
|  1 | SIMPLE      | Board              | NULL       | const  | PRIMARY                                | PRIMARY                        | 4       | const                          |    1 |   100.00 | Using temporary |
|  1 | SIMPLE      | Board_Board__likes | NULL       | ref    | PRIMARY,IDX_cc61d27acb747ad30ab37c7399 | PRIMARY                        | 4       | const                          |    1 |   100.00 | Using index     |
|  1 | SIMPLE      | Board__likes       | NULL       | eq_ref | PRIMARY                                | PRIMARY                        | 4       | b1g1.Board_Board__likes.userId |    1 |   100.00 | Using index     |
|  1 | SIMPLE      | Board__images      | NULL       | ref    | FK_7d46d16528472a594493ecb6600         | FK_7d46d16528472a594493ecb6600 | 5       | const                          |    2 |   100.00 | Using index     |
|  1 | SIMPLE      | Board__user        | NULL       | const  | PRIMARY                                | PRIMARY                        | 4       | const                          |    1 |   100.00 | Using index     |
+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-----------------+
5 rows in set, 1 warning (0.02 sec)

첫 번째 쿼리에 대한 쿼리 플랜

+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-------------+
| id | select_type | table              | partitions | type   | possible_keys                          | key                            | key_len | ref                            | rows | filtered | Extra       |
+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-------------+
|  1 | SIMPLE      | Board              | NULL       | const  | PRIMARY                                | PRIMARY                        | 4       | const                          |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Board_Board__likes | NULL       | ref    | PRIMARY,IDX_cc61d27acb747ad30ab37c7399 | PRIMARY                        | 4       | const                          |    1 |   100.00 | Using index |
|  1 | SIMPLE      | Board__likes       | NULL       | eq_ref | PRIMARY                                | PRIMARY                        | 4       | b1g1.Board_Board__likes.userId |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Board__images      | NULL       | ref    | FK_7d46d16528472a594493ecb6600         | FK_7d46d16528472a594493ecb6600 | 5       | const                          |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | Board__user        | NULL       | const  | PRIMARY                                | PRIMARY                        | 4       | const                          |    1 |   100.00 | NULL        |
+----+-------------+--------------------+------------+--------+----------------------------------------+--------------------------------+---------+--------------------------------+------+----------+-------------+
5 rows in set, 1 warning (0.01 sec)

GET /post/:id 쿼리 최적화

쿼리만 대충 봐도 중복을 제거하기 위해 첫 번째 쿼리에 SELECT DISTINCT 구문으로 두 번째 쿼리의 결과의 중복 제거를 한 후, 다시 두 번째 쿼리를 날린다.
- 이것만으로도 상당히 미효율적임. LIMIT 1만 추가해줘도 됨 -> image는 따로 select
like_cnt만 반환하면 되기 때문에 board_likes_user 조인테이블을 LEFT JOIN 할 필요는 없음.
- 같은 이유로 like를 위한 user LEFT JOIN은 필요없음. 한 번만 조인하여 author만 확인하면 됨.
images의 경우 조인보다 별도로 SELECT쿼리를 한 번 더 보내는 것이 더 효율적일 수 있음.
- 이부분은 비교하기 힘들기 때문에 TypeORM의 출력구조를 유지하기 위해 보류
추가로 POST /star 최적화와 마찬가지로 꼭 필요한 컬럼만 SELECT
- id, title, content, images(image_id 배열)

SELECT `Board`.`id` AS `id`,
`Board`.`title` AS `title`,
`Board`.`content` AS `content`,
`Board`.`like_cnt` AS `like_cnt`,
`Board__images`.`filename` AS `Board__images_filename`
FROM `board` `Board`
LEFT JOIN `image` `Board__images`
ON `Board__images`.`boardId`=`Board`.`id`
LEFT JOIN `user` `Board__user`
ON `Board__user`.`id`=`Board`.`userId`
WHERE (`Board`.`id` = 222);
-- PARAMETERS: [222]

+----+-------------+---------------+------------+-------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys                  | key                            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | Board         | NULL       | const | PRIMARY                        | PRIMARY                        | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Board__images | NULL       | ref   | FK_7d46d16528472a594493ecb6600 | FK_7d46d16528472a594493ecb6600 | 5       | const |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | Board__user   | NULL       | const | PRIMARY                        | PRIMARY                        | 4       | const |    1 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.02 sec)

시간을 2/3으로 줄였고, 트리 탐색 횟수도 3회로 줄였다.

추가로 두 번째 열에 대한 최적화가 가능할 것으로 보임.
image 테이블의 filename 컬럼을 secondary index로 등록하면 인덱스도 태울 수 있겠다.

TypeORM에서 세컨더리 인덱스 만드는 방법?

Image Entity 파일에 데코레이터를 추가해주면 되겠다.

import {
  ...
	Index,
  ...
} from 'typeorm';
...

@Entity()
@Index('idx_filename', ['filename'])
export class Image extends BaseEntity {
	...
	@Column({ type: 'varchar', length: 50, nullable: false })
	filename: string;
  ...
}

-- Active: 1693885143266@@192.168.64.2@3306@b1g1
CREATE TABLE `image` (
  `id` int NOT NULL AUTO_INCREMENT,
  `mimetype` varchar(50) NOT NULL,
  `filename` varchar(50) NOT NULL,
  `size` int NOT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `boardId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_7d46d16528472a594493ecb6600` (`boardId`),
  KEY `idx_filename` (`filename`),
  CONSTRAINT `FK_7d46d16528472a594493ecb6600` FOREIGN KEY (`boardId`) REFERENCES `board` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

DDL에도 KEY idx_filename (filename) 열이 잘 추가된 것을 확인할 수 있다.

아 근데 생각해보니 지금은 filename을 직접 조회할 때가 아니라 boardId 기준으로 찾는거라 필요가 없다.. 위에 사진 봐도 FK 등록되어 자동 세컨더리 인덱싱이 되어 있는 FK_7d46d16528472a594493ecb6600 인덱스를 이미 타고 있다 ㅋ

그래도 이미지 정보 조회를 위해 이대로 두자. 인덱싱 공부 끝

추가로 인터페이스 명세에 author도 들어가지 않아서, user 테이블 조회도 안해도 되시겠다.

SELECT `Board`.`id` AS `id`,
`Board`.`title` AS `title`,
`Board`.`content` AS `content`,
`Board`.`like_cnt` AS `like_cnt`,
`Board__images`.`filename` AS `Board__images_filename`
FROM `board` `Board`
LEFT JOIN `image` `Board__images`
ON `Board__images`.`boardId`=`Board`.`id`
WHERE (`Board`.`id` = 222);
-- PARAMETERS: [222]

최최종

row 2개로 깔끔하게 떨어졌다.

TypeORM Query Builder로 최적화 적용

이제 적용해보자. 여러 줄에 걸친 한 post 레코드의 결과값이 다시 Image[] 배열로 합쳐지는지가 관건.

const found: Board = await this.boardRepository
	.createQueryBuilder()
	.select(['board.id', 'board.title', 'board.content', 'board.like_cnt'])
	.from(Board, 'board')
	.leftJoinAndMapMany(
		'board.images',
		Image,
		'image',
		'image.boardId = board.id',
	)
	.where('board.id = :id', { id })
	.getOne();

수많은 시행착오와 자료조사를 거쳐 leftJoinAndMapMany로 해결했다.

Image의 PartialType으로 image.filename만 가져오도록 노력해봤는데,
그러면 Board Entity의 타입 정의에 위배돼선지 많은 문제들이 생겨 결국 가져온 뒤에 추출해주는 기존의 방식대로 하는 걸로 결론내렸다.

조회 및 반환 잘 됨

아래는 최종 쿼리

SELECT `board`.`id` AS `board_id`,
`board`.`title` AS `board_title`,
`board`.`content` AS `board_content`,
`board`.`like_cnt` AS `board_like_cnt`,
`image`.`id` AS `image_id`,
`image`.`mimetype` AS `image_mimetype`,
`image`.`filename` AS `image_filename`,
`image`.`size` AS `image_size`,
`image`.`created_at` AS `image_created_at`,
`image`.`boardId` AS `image_boardId`
FROM `board` `Board`, `board` `board`
LEFT JOIN `image` `image`
ON `image`.`boardId` = `board`.`id`
WHERE `board`.`id` = ? -- PARAMETERS: [222]

동작 화면

POST /star (Before)

SELECT `Board`.`id` AS `Board_id`,
  `Board`.`title` AS `Board_title`,
  `Board`.`content` AS `Board_content`,
  `Board`.`created_at` AS `Board_created_at`,
  `Board`.`updated_at` AS `Board_updated_at`,
  `Board`.`like_cnt` AS `Board_like_cnt`,
  `Board`.`star` AS `Board_star`,
  `Board`.`userId` AS `Board_userId`,
  `Board__Board_user`.`id` AS `Board__Board_user_id`,
  `Board__Board_user`.`username` AS `Board__Board_user_username`,
  `Board__Board_user`.`password` AS `Board__Board_user_password`,
  `Board__Board_user`.`nickname` AS `Board__Board_user_nickname`,
  `Board__Board_user`.`created_at` AS `Board__Board_user_created_at`,
  `Board__likes`.`id` AS `Board__likes_id`,
  `Board__likes`.`username` AS `Board__likes_username`,
  `Board__likes`.`password` AS `Board__likes_password`,
  `Board__likes`.`nickname` AS `Board__likes_nickname`,
  `Board__likes`.`created_at` AS `Board__likes_created_at`,
  `Board__images`.`id` AS `Board__images_id`,
  `Board__images`.`mimetype` AS `Board__images_mimetype`,
  `Board__images`.`filename` AS `Board__images_filename`,
  `Board__images`.`size` AS `Board__images_size`,
  `Board__images`.`created_at` AS `Board__images_created_at`,
  `Board__images`.`boardId` AS `Board__images_boardId`
  FROM `board` `Board`
  LEFT JOIN `user` `Board__Board_user` ON `Board__Board_user`.`id`=`Board`.`userId`
  LEFT JOIN `board_likes_user` `Board_Board__likes` ON `Board_Board__likes`.`boardId`=`Board`.`id`
  LEFT JOIN `user` `Board__likes` ON `Board__likes`.`id`=`Board_Board__likes`.`userId`
  LEFT JOIN `image` `Board__images` ON `Board__images`.`boardId`=`Board`.`id`
  WHERE (`Board__Board_user`.`nickname` = ?);
  -- PARAMETERS: ["test2"]

POST /star (After)

SELECT `board`.`id` AS `board_id`,
`board`.`title` AS `board_title`,
`board`.`star` AS `board_star`
FROM `board` `Board`, `board` `board`
LEFT JOIN `user` `user` ON `user`.`id`=`board`.`userId`
WHERE `user`.`nickname` = ?; -- PARAMETERS: ["test2"]

GET /post/:id (before)

SELECT DISTINCT `distinctAlias`.`Board_id` AS `ids_Board_id`
FROM (
  SELECT `Board`.`id` AS `Board_id`,
  `Board`.`title` AS `Board_title`,
  `Board`.`content` AS `Board_content`,
  `Board`.`created_at` AS `Board_created_at`,
  `Board`.`updated_at` AS `Board_updated_at`,
  `Board`.`like_cnt` AS `Board_like_cnt`,
  `Board`.`star` AS `Board_star`,
  `Board`.`userId` AS `Board_userId`,
  `Board__likes`.`id` AS `Board__likes_id`,
  `Board__likes`.`username` AS `Board__likes_username`,
  `Board__likes`.`password` AS `Board__likes_password`,
  `Board__likes`.`nickname` AS `Board__likes_nickname`,
  `Board__likes`.`created_at` AS `Board__likes_created_at`,
  `Board__images`.`id` AS `Board__images_id`,
  `Board__images`.`mimetype` AS `Board__images_mimetype`,
  `Board__images`.`filename` AS `Board__images_filename`,
  `Board__images`.`size` AS `Board__images_size`,
  `Board__images`.`created_at` AS `Board__images_created_at`,
  `Board__images`.`boardId` AS `Board__images_boardId`,
  `Board__user`.`id` AS `Board__user_id`,
  `Board__user`.`username` AS `Board__user_username`,
  `Board__user`.`password` AS `Board__user_password`,
  `Board__user`.`nickname` AS `Board__user_nickname`,
  `Board__user`.`created_at` AS `Board__user_created_at`
  FROM `board` `Board`
  LEFT JOIN `board_likes_user` `Board_Board__likes`
  ON `Board_Board__likes`.`boardId`=`Board`.`id`
  LEFT JOIN `user` `Board__likes` ON `Board__likes`.`id`=`Board_Board__likes`.`userId`
  LEFT JOIN `image` `Board__images` ON `Board__images`.`boardId`=`Board`.`id`
  LEFT JOIN `user` `Board__user` ON `Board__user`.`id`=`Board`.`userId`
  WHERE (`Board`.`id` = ?)
) `distinctAlias`
ORDER BY `Board_id` ASC LIMIT 1;
-- PARAMETERS: [222]

SELECT `Board`.`id` AS `Board_id`,
`Board`.`title` AS `Board_title`,
`Board`.`content` AS `Board_content`,
`Board`.`created_at` AS `Board_created_at`,
`Board`.`updated_at` AS `Board_updated_at`,
`Board`.`like_cnt` AS `Board_like_cnt`,
`Board`.`star` AS `Board_star`,
`Board`.`userId` AS `Board_userId`,
`Board__likes`.`id` AS `Board__likes_id`,
`Board__likes`.`username` AS `Board__likes_username`,
`Board__likes`.`password` AS `Board__likes_password`,
`Board__likes`.`nickname` AS `Board__likes_nickname`,
`Board__likes`.`created_at` AS `Board__likes_created_at`,
`Board__images`.`id` AS `Board__images_id`,
`Board__images`.`mimetype` AS `Board__images_mimetype`,
`Board__images`.`filename` AS `Board__images_filename`,
`Board__images`.`size` AS `Board__images_size`,
`Board__images`.`created_at` AS `Board__images_created_at`,
`Board__images`.`boardId` AS `Board__images_boardId`,
`Board__user`.`id` AS `Board__user_id`,
`Board__user`.`username` AS `Board__user_username`,
`Board__user`.`password` AS `Board__user_password`,
`Board__user`.`nickname` AS `Board__user_nickname`,
`Board__user`.`created_at` AS `Board__user_created_at`
FROM `board` `Board`
LEFT JOIN `board_likes_user` `Board_Board__likes`
ON `Board_Board__likes`.`boardId`=`Board`.`id`
LEFT JOIN `user` `Board__likes`
ON `Board__likes`.`id`=`Board_Board__likes`.`userId`
LEFT JOIN `image` `Board__images`
ON `Board__images`.`boardId`=`Board`.`id`
LEFT JOIN `user` `Board__user`
ON `Board__user`.`id`=`Board`.`userId`
WHERE ( (`Board`.`id` = ?) ) AND ( `Board`.`id` IN (222) );
-- PARAMETERS: [222]

GET /post/:id (after)

SELECT `board`.`id` AS `board_id`,
`board`.`title` AS `board_title`,
`board`.`content` AS `board_content`,
`board`.`like_cnt` AS `board_like_cnt`,
`image`.`id` AS `image_id`,
`image`.`mimetype` AS `image_mimetype`,
`image`.`filename` AS `image_filename`,
`image`.`size` AS `image_size`,
`image`.`created_at` AS `image_created_at`,
`image`.`boardId` AS `image_boardId`
FROM `board` `Board`, `board` `board`
LEFT JOIN `image` `image`
ON `image`.`boardId` = `board`.`id`
WHERE `board`.`id` = ? -- PARAMETERS: [222]