[재하] 1128(화) 개발기록
- Sharp 플랫폼 종속성 문제 해결
- GET /star, GET /post/:id 프론트 요청에 맞게 새로 구현
- 쿼리 최적화
- TypeORM 쿼리 로그
- GET /star
- GET /star 쿼리 로그 확인
- MySQL 쿼리 플랜 조회
- GET /star 쿼리 최적화
- TypeORM Query Builder로 최적화 적용
- GET /post/:id
- GET /post/:id 쿼리 로그 확인
- MySQL 쿼리 플랜 조회
- GET /post/:id 쿼리 최적화
- TypeORM Query Builder로 최적화 적용
- 테스트 동작 화면
CI/CD 파이프라인이 모두 잘 완료되었으나, Sharp 패키지의 플랫폼 종속적 문제로 was가 죽어버리는 문제가 발생했��.
학습메모 1을 참고 (공식문서)
npm install --arch=x64 --platform=linux --libc=glibc sharp하지만 우리는 npm이 아니라 yarn berry.. --platform 옵션이 없어 이에 대응하는 명령을 찾기가 좀 어려웠다.
결국 학습메모 2를 참고해 아래와 같은 명령으로 해결할 수 있다는 것을 확인했다.
SHARP_IGNORE_GLOBAL_LIBVIPS=1 npm_config_arch=x64 npm_config_platform=linux yarn workspace server add sharpFROM node:20-alpine
WORKDIR /app
ADD . /app
RUN SHARP_IGNORE_GLOBAL_LIBVIPS=1 npm_config_arch=x64 npm_config_platform=linux yarn workspace server add sharp
RUN yarn workspace server build
EXPOSE 3000
ENTRYPOINT ["yarn", "workspace", "server", "start:prod"]Dockerfile-was 도커파일에 RUN 명령으로 위와 같이 빌드 전 패키지를 플랫폼에 맞게 추가 설치해주는 것으로 해결
이제 잘 올라감.
기존에 다 학습하고 검증까지 완료했던 기능들의 인터페이스만 변경하는 것이므로 간단히 과정과 결과만 요약하겠다.
- module엔 AuthModule, TypeORMModule(Board), MongooseModule(Star) 등록
- controller엔 다음을 구현
- by-author 파라미터로 별 목록을 리턴하는 findAllStarsByAuthor
- Auth Guard로 user data를 얻어 자신의 별 목록을 리턴하는 findAllStarsMine
- service엔 다음을 구현
- author에 따른 글 목록을 받고, star id를 이용해 MongoDB에서 별 정보를 받아 양식에 맞게 별 목록을 리턴
- service에선 image를 download하는 대신 이미지의 Key(uuid)만 전달
- controller에선 Board를 폼데이터로 전달하는 대신 다음을 구현
- 양식(GetPostByIdResDto)에 맞게 id title, content, like_cnt를 반환하며
- images는 NCP Object Storage에 바로 접근할 수 있는 url을 생성하여 배열로 반환
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 메소드 실행 시마다 어떤 쿼리를 실제로 보내는 지를 확인해 본다.
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"]학습메모 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)
쿼리를 수정하고, 이를 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_ciuser 테이블의 ddl을 확인해보면 nickname컬럼을 TypeORM Entity에서 unique: true로 설정해준 덕분에, 알아서 UNIQUE KEY가 생성되고, 이로 인해 인덱싱이 되어있음을 확인할 수 있다.
학습메모 4를 참고하여 Unique 설정만으로 세컨더리 인덱싱이 된다는 사실을 확인할 수 있었다. 잘했어 과거의 나!
이후 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"]위는 개선된 최종 쿼리 로그!
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에 개선된 쿼리가 잘 반영되었는지를 로그로 확인한다.
마찬가지로 쿼리가 잘 동작하는지 확인하고, 앞에 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)
- 쿼리만 대충 봐도 중복을 제거하기 위해 첫 번째 쿼리에 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_ciDDL에도 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개로 깔끔하게 떨어졌다.
이제 적용해보자. 여러 줄에 걸친 한 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]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"]
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"]
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]
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]
