게시글 페이지 네이션 오류 해결 및 속도비교

목적

게시글 페이지네이션 속도를 개선하자.

개선하는 이유

우리 서비스는 (해시테그, 최신순, 인기순, 답변필요, 테그) 등등 페이지네이션 요청을 할 경우가 매우 많다.
지금 로직은 데이터가 많아 질수록 쿼리 속도가 확연하게 느려진다.

해결 방법

커버링 인덱싱 적용

개선된 이유

예를 들어 100개의 게시글이 있고 10개씩 끊어서 페이지네이션을 구현한다고 가정하자.

페이지네이션에 필요한 데이터는 클라이언트가 보고 있는 페이지의 질문글 리스트와 총 질문글의 수가 필요하다.

기존의 로직은 100개의 게시글이 있다면 100개의 게시글의 모든 데이터(데이터 베이스 레코드)를 조회한 후 해당 인덱싱에 알맞게 10개의 데이터를 자른다.

필요한 10개의 데이터 뿐만 아니라 100개의 게시글의 모든 데이터(데이터 베이스 레코드)를 불필요하게 가져오고 있다.

그래서 커버링 인덱싱을 적용했다.

먼저 게시글 테이블에서 게시글의 id만 100개를 조회한 후 해당 인덱싱에 알맞게 10개의 데이터를 자른다.

이후 이 id와 일치하는 데이터들을 가져오기 때문에 10개의 데이터만 가져오면 된다

100개의 게시글의 모든 데이터가 아니라 id만 조회하기 때문에 속도가 전자보다 많이 줄어든다.

그 후 추출한 데이터와 게시글 테이블을 조인을 한 다음 우리가 필요한 게시글 정보만 조회하는것이다

코드

커버링 인덱스 적용 전

	async getQuestionList(pageInfo) {
		const questionList = await this.questionRepository
			.createQueryBuilder('question')
			.innerJoinAndSelect('question.user', 'question_user')
			.leftJoin('question.hashtag', 'question_hashtag')
			.select(['question.id', 'question.created_at', 'question.is_solved', 'question.like_count', 'question.view_count', 'question.answer_count', 'question.title', 'question.text',
				'question_user.id', 'question_user.created_at', 'question_user.email', 'question_user.nickname', 'question_user.photo',
				'question_hashtag.id', 'question_hashtag.name'
			])
			.limit(pageInfo.limit)
			.offset(pageInfo.offset)
			.orderBy('question.id', 'DESC')
			.getMany();

		const questionCount = await this.questionRepository
			.count();
		return { questionList, questionCount };
	}

	async getQuestionListOrderByLikeCount(pageInfo) {
		const questionList = await this.questionRepository
			.createQueryBuilder('question')
			.innerJoinAndSelect('question.user', 'question_user')
			.leftJoin('question.hashtag', 'question_hashtag')
			.select(['question.id', 'question.created_at', 'question.is_solved', 'question.like_count', 'question.view_count', 'question.answer_count', 'question.title', 'question.text',
				'question_user.id', 'question_user.created_at', 'question_user.email', 'question_user.nickname', 'question_user.photo',
				'question_hashtag.id', 'question_hashtag.name'
			])
			.limit(pageInfo.limit)
			.offset(pageInfo.offset)
			.orderBy('question.like_count', 'DESC')
			.addOrderBy('question.id', 'DESC')
			.getMany();

		const questionCount = await this.questionRepository
			.count();
		return { questionList, questionCount };
	}

	async getQuestionListUnsolved(pageInfo) {
		const questionList = await this.questionRepository
			.createQueryBuilder('question')
			.where('question.is_solved = :is_solved', { is_solved: false })
			.innerJoinAndSelect('question.user', 'question_user')
			.leftJoin('question.hashtag', 'question_hashtag')
			.select(['question.id', 'question.created_at', 'question.is_solved', 'question.like_count', 'question.view_count', 'question.answer_count', 'question.title', 'question.text',
				'question_user.id', 'question_user.created_at', 'question_user.email', 'question_user.nickname', 'question_user.photo',
				'question_hashtag.id', 'question_hashtag.name'
			])
			.limit(pageInfo.limit)
			.offset(pageInfo.offset)
			.orderBy('question.id', 'DESC')
			.getMany();

		const questionCount = await this.questionRepository
			.createQueryBuilder('question')
			.where('question.is_solved = :is_solved', { is_solved: false })
			.getCount();
		return { questionList, questionCount };
	}

	async getQuestionListByKeyword(pageInfo, orderBy) {
		const keywords = pageInfo.keyword.split(" ");
		let subQuery;

		subQuery = this.questionRepository
			.createQueryBuilder('question')
			.where('question.title like :title', { title: `%${keywords[0]}%` })
		for (let i = 1; i < keywords.length; i++) {
			const subStr = 'question.title like :title' + String(i);
			const subTitle = "title" + String(i);
			subQuery.orWhere(subStr, { [subTitle]: `%${keywords[i]}%` })
		}
		const questionCount = await subQuery.getCount();

		const questionList = await subQuery.innerJoinAndSelect('question.user', 'question_user')
			.leftJoin('question.hashtag', 'question_hashtag')
			.select(['question.id', 'question.created_at', 'question.is_solved', 'question.like_count', 'question.view_count', 'question.answer_count', 'question.title', 'question.text',
				'question_user.id', 'question_user.created_at', 'question_user.email', 'question_user.nickname', 'question_user.photo',
				'question_hashtag.id', 'question_hashtag.name'
			])
			.orderBy('question.id', 'DESC')
			.limit(pageInfo.limit)
			.offset(pageInfo.offset)
			.orderBy('question.id', 'DESC')
			.getMany();

		return { questionList, questionCount }
	}

커버링 인덱스 적용 후

	async getQuestionList(pageInfo) {
		const subQuery = await this.questionRepository
			.createQueryBuilder('covers')
			.select(['covers.id'])
			.orderBy('covers.id', 'DESC')
			.limit(pageInfo.limit)
			.offset(pageInfo.offset)

		const questionList = await this.questionRepository
			.createQueryBuilder('question')
			.innerJoin(`(${subQuery.getQuery()})`, 'covers',
				'question.id = covers.covers_id')
			.innerJoinAndSelect('question.user', 'question_user')
			.leftJoin('question.hashtag', 'question_hashtag')
			.select(['question.id', 'question.created_at', 'question.is_solved', 'question.like_count', 'question.view_count', 'question.answer_count', 'question.title', 'question.text',
				'question_user.id', 'question_user.created_at', 'question_user.email', 'question_user.nickname', 'question_user.photo',
				'question_hashtag.id', 'question_hashtag.name'
			])
			.orderBy('question.id', 'DESC')
			.getMany();

		const questionCount = await this.questionRepository
			.count();
		return { questionList, questionCount };
	}

	async getQuestionListOrderByLikeCount(pageInfo) {
		const subQuery = await this.questionRepository
			.createQueryBuilder('covers')
			.select(['covers.id', 'covers.like_count'])
			.orderBy('covers.like_count', 'DESC')
			.addOrderBy('covers.id', 'DESC')
			.limit(pageInfo.limit)
			.offset(pageInfo.offset)

		const questionList = await this.questionRepository
			.createQueryBuilder('question')
			.innerJoin(`(${subQuery.getQuery()})`, 'covers',
				'question.id = covers.covers_id')
			.innerJoinAndSelect('question.user', 'question_user')
			.leftJoin('question.hashtag', 'question_hashtag')
			.select(['question.id', 'question.created_at', 'question.is_solved', 'question.like_count', 'question.view_count', 'question.answer_count', 'question.title', 'question.text',
				'question_user.id', 'question_user.created_at', 'question_user.email', 'question_user.nickname', 'question_user.photo',
				'question_hashtag.id', 'question_hashtag.name'
			])
			.orderBy('question.like_count', 'DESC')
			.addOrderBy('question.id', 'DESC')
			.getMany();

		const questionCount = await this.questionRepository
			.count();
		return { questionList, questionCount };
	}

	async getQuestionListUnsolved(pageInfo) {
		const subQuery = await this.questionRepository
			.createQueryBuilder('covers')
			.select(['covers.id'])
			.where('covers.is_solved = :is_solved', { is_solved: false })
			.orderBy('covers.id', 'DESC')
			.limit(pageInfo.limit)
			.offset(pageInfo.offset)

		const questionList = await this.questionRepository
			.createQueryBuilder('question')
			.innerJoin(`(${subQuery.getQuery()})`, 'covers',
				'question.id = covers.covers_id')
			.setParameters(subQuery.getParameters())
			.innerJoinAndSelect('question.user', 'question_user')
			.leftJoin('question.hashtag', 'question_hashtag')
			.select(['question.id', 'question.created_at', 'question.is_solved', 'question.like_count', 'question.view_count', 'question.answer_count', 'question.title', 'question.text',
				'question_user.id', 'question_user.created_at', 'question_user.email', 'question_user.nickname', 'question_user.photo',
				'question_hashtag.id', 'question_hashtag.name'
			])
			.orderBy('question.id', 'DESC')
			.getMany();

		const questionCount = await this.questionRepository
			.createQueryBuilder('question')
			.where('question.is_solved = :is_solved', { is_solved: false })
			.getCount();
		return { questionList, questionCount };
	}

	async getQuestionListByKeyword(pageInfo, orderBy) {
		const keywords = pageInfo.keyword.split(" ");
		let subQuery;

		subQuery = this.questionRepository
			.createQueryBuilder('covers')
			.where('covers.title like :title', { title: `%${keywords[0]}%` })
		for (let i = 1; i < keywords.length; i++) {
			const subStr = 'covers.title like :title' + String(i);
			const subTitle = "title" + String(i);
			subQuery.orWhere(subStr, { [subTitle]: `%${keywords[i]}%` })
		}
		const orderByData = await this.orderByList(subQuery, orderBy, pageInfo);
		const questionCount = orderByData.count;
		const questionList = await this.questionRepository
			.createQueryBuilder('question')
			.innerJoin(`(${orderByData.subQuery.getQuery()})`, 'covers',
				'question.id = covers.covers_id')
			.setParameters(orderByData.subQuery.getParameters())
			.innerJoinAndSelect('question.user', 'question_user')
			.leftJoin('question.hashtag', 'question_hashtag')
			.select(['question.id', 'question.created_at', 'question.is_solved', 'question.like_count', 'question.view_count', 'question.answer_count', 'question.title', 'question.text',
				'question_user.id', 'question_user.created_at', 'question_user.email', 'question_user.nickname', 'question_user.photo',
				'question_hashtag.id', 'question_hashtag.name'
			])
			.orderBy('question.id', 'DESC')
			.getMany();
		return { questionList, questionCount }
	}