24. 10. 17. 개발일지 // mysql, 인덱싱

* db 공부

먼저 게시글 검색을 구현하려는데 무작정 제목, 내용에 index걸면 되는건지 궁금하다.
elastic search를 왜 쓰는지도 궁금하다.
일단 mysql 에 대해 학습해보자.

나이 컬럼에 인덱스를 만들면 나이에 대해 정렬후 pk값이 매핑되는거구나.
무작정 index를 많이 만들면 쓰기 속도만 느려질뿐이다.

생성일자, 부서명 idx가있을때, 어느 컬럼에 인덱스를 걸어야할까?
2개다걸기 -> 옵티마이저는 어차피 1개만 사용한다.
결론 : 데이터의 중복이 적은 컬럼에 한해서 인덱스를 거는게 효율적이다!
(항상 그런것은 아니다. explain analyze를 보고 판단필요)

멀티 인덱스는 어떨까?
created index (created_at, department)
created index (department, created_at)
위 2개는 다른 인덱스이다.
실험결과 성능이 비슷하다.
index는 적을수록좋으므로 단일 인덱스를 만들자.
실무에서도 모든 조합에 대해 인덱스를 걸어보고 수동으로 실행시간을 보고 어디에 인덱스를 걸지 판단하는지?

test용 데이터를 넣어보자.

INSERT INTO community.posts (title, content, createdAt, updatedAt, memberId, views)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 100 
)
SELECT 
    CONCAT('Title', LPAD(n, 7, '0')) AS title, 
    CONCAT('This is the content of post number ', n, '. ', 
           CASE 
               WHEN n % 10 = 1 THEN '인사팀의 공지사항입니다.'
               WHEN n % 10 = 2 THEN '정비팀의 업무 보고입니다.'
               WHEN n % 10 = 3 THEN '마케팅 전략에 대한 논의입니다.'
               WHEN n % 10 = 4 THEN '금융 동향 분석입니다.'
               WHEN n % 10 = 5 THEN '회계 보고서입니다.'
               WHEN n % 10 = 6 THEN '법무팀의 법률 자문입니다.'
               WHEN n % 10 = 7 THEN '전산팀의 시스템 업데이트 안내입니다.'
               WHEN n % 10 = 8 THEN '특수임무 수행 결과입니다.'
               WHEN n % 10 = 9 THEN '수색대의 임무 보고서입니다.'
               ELSE '일반 업무 보고입니다.'
           END) AS content,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS updated_at,
    FLOOR(1 + RAND() * 50) AS memberId,
    FLOOR(RAND() * 10000) AS views
FROM cte;

에러원인 : memberId가 실제 존재하는 id가 아닌경우 에러가난다. (외래키 제약조건)

해결 : member table에 먼저 data 삽입

-- Members 테이블에 데이터 삽입
INSERT INTO community.members (id, email, nickname, password, createdAt, updatedAt)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000 
)
SELECT 
    n AS id,
    CONCAT('user', LPAD(n, 4, '0'), '@example.com') AS email,
    CONCAT('password', LPAD(n, 4, '0')) AS password,
    CONCAT('User', LPAD(n, 4, '0')) AS name,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS updated_at
FROM cte;

문제 : 이미 존재하는 id와 충돌
해결 : INSERT IGNORE INTO

-- Members 테이블에 데이터 삽입
INSERT IGNORE INTO community.members (id, email, nickname, password, createdAt, updatedAt)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000 
)
SELECT 
    n AS id,
    CONCAT('user', LPAD(n, 4, '0'), '@example.com') AS email,
    CONCAT('password', LPAD(n, 4, '0')) AS password,
    CONCAT('User', LPAD(n, 4, '0')) AS name,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS updated_at
FROM cte;

100만건 데이터 넣기 (외래키 제약조건 1개)

SET SESSION cte_max_recursion_depth = 1000000; 

INSERT INTO community.posts (title, content, createdAt, updatedAt, memberId, views)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 
)
SELECT 
    CONCAT('Title', LPAD(n, 7, '0')) AS title, 
    CONCAT('This is the content of post number ', n, '. ', 
           CASE 
               WHEN n % 10 = 1 THEN '인사팀의 공지사항입니다.'
               WHEN n % 10 = 2 THEN '정비팀의 업무 보고입니다.'
               WHEN n % 10 = 3 THEN '마케팅 전략에 대한 논의입니다.'
               WHEN n % 10 = 4 THEN '금융 동향 분석입니다.'
               WHEN n % 10 = 5 THEN '회계 보고서입니다.'
               WHEN n % 10 = 6 THEN '법무팀의 법률 자문입니다.'
               WHEN n % 10 = 7 THEN '전산팀의 시스템 업데이트 안내입니다.'
               WHEN n % 10 = 8 THEN '특수임무 수행 결과입니다.'
               WHEN n % 10 = 9 THEN '수색대의 임무 보고서입니다.'
               ELSE '일반 업무 보고입니다.'
           END) AS content,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS updated_at,
    FLOOR(1 + RAND() * 50) AS memberId,
    FLOOR(RAND() * 10000) AS views
FROM cte;

26초 걸림

게시글 조회를 해보자
엄청 오래걸린다...
typeorm에서 쿼리로그복사 -> gpt에게 줌 -> 들여쓰기 등 맞춰줌 -> workbench에 붙여넣은후 실행

-- 수정된 쿼리 (공백 제거 및 테이블 구조 확인 후)
SELECT DISTINCT `distinctAlias`.`Post_id` AS `ids_Post_id`, `distinctAlias`.`Post_createdAt` 
FROM (
    SELECT 
        `Post`.`id` AS `Post_id`, 
        `Post`.`title` AS `Post_title`, 
        `Post`.`content` AS `Post_content`, 
        `Post`.`views` AS `Post_views`, 
        `Post`.`image` AS `Post_image`, 
        `Post`.`createdAt` AS `Post_createdAt`, 
        `Post`.`updatedAt` AS `Post_updatedAt`, 
        `Post`.`memberId` AS `Post_memberId`, 
        `Post__Post_member`.`id` AS `Post__Post_member_id`, 
        `Post__Post_member`.`nickname` AS `Post__Post_member_nickname`, 
        `Post__Post_member`.`password` AS `Post__Post_member_password`, 
        `Post__Post_member`.`email` AS `Post__Post_member_email`, 
        `Post__Post_member`.`createdAt` AS `Post__Post_member_createdAt`, 
        `Post__Post_member`.`updatedAt` AS `Post__Post_member_updatedAt` 
    FROM `posts` `Post` 
    LEFT JOIN `members` `Post__Post_member` ON `Post__Post_member`.`id` = `Post`.`memberId`
) `distinctAlias` 
ORDER BY `distinctAlias`.`Post_createdAt` DESC, `distinctAlias`.`Post_id` ASC 
LIMIT 10;

order by에 있는 created_at에 인덱싱을 걸어보자.

index type이 나왔다.
별로 안좋은 타입같은데?

참고 : typeorm ddl-auto 설정 && 서버 재실행시 모든 index를 날려버리고 다시만드는것 같다.
워크벤치에서 만들어뒀던 인덱스들이 없어졌다.
서버재실행시 index를 drop 시키는 모습

인덱스가 없는경우 쿼리 플랜

인덱스가 있는경우 쿼리 순서

이제 최적화된 쿼리의 실행 순서와 복합 인덱스의 필요성에 대해 설명드리겠습니다:

쿼리 실행 순서:
a. Index scan on Post:

'idx_created_at_id' 복합 인덱스를 사용하여 Post 테이블을 스캔합니다.
이 인덱스는 이미 createdAt DESC, id 순으로 정렬되어 있습니다.

b. Nested loop left join:

각 Post 행에 대해 Post__Post_member 테이블을 조회합니다.
이 과정은 최대 10번만 실행됩니다.

c. 임시 테이블 생성:

조인 결과를 임시 테이블에 저장하고 중복을 제거합니다.
이 단계에서 최대 10개의 고유한 행만 처리합니다.

d. 임시 테이블 스캔:

생성된 임시 테이블을 스캔하여 최종 결과를 얻습니다.

e. 결과 반환:

최종적으로 10개 이하의 행을 결과로 반환합니다.


복합 인덱스(createdAt, id)가 필요한 이유:
a. 정렬 순서 최적화:

'createdAt DESC, id' 순으로 정렬된 복합 인덱스는 쿼리의 ORDER BY 절과 정확히 일치합니다.
이로 인해 별도의 정렬 작업이 필요 없어집니다.

b. 범위 검색과 고유성 보장:

'createdAt'만으로는 정확히 10개의 행을 특정할 수 없습니다(같은 시간에 여러 게시물이 있을 수 있음).
'id'를 포함함으로써 각 행을 고유하게 식별할 수 있습니다.

c. 커버링 인덱스:

'id'가 인덱스에 포함되어 있어, 실제 테이블 데이터를 조회하지 않고도 필요한 정보를 얻을 수 있습니다.

d. 효율적인 페이지네이션:

'createdAt'과 'id'를 함께 사용하면, 다음 페이지의 시작점을 정확히 지정할 수 있습니다.
예: WHERE (createdAt < :lastCreatedAt) OR (createdAt = :lastCreatedAt AND id < :lastId)

e. 인덱스 선택성 향상:

'createdAt'만으로는 선택성이 낮을 수 있지만, 'id'를 추가함으로써 각 인덱스 항목의 고유성이 보장됩니다.

그래도 여전히 type이 index인데 이를 개선할수 있을까?
시도해보자.

개선후 쿼리

const posts = await this.postRepository
    .createQueryBuilder("post")
    .leftJoinAndSelect("post.member", "member")
    .where("post.createdAt <= :date", { date: new Date() })
    .orderBy("post.createdAt", "DESC")
    .addOrderBy("post.id", "DESC")
    .take(10)
    .getMany();

쿼리빌더로 생성되는 sql

SELECT DISTINCT
    `distinctAlias`.`post_id` AS `ids_post_id`,
    `distinctAlias`.`post_createdAt`,
    `distinctAlias`.`post_id`
FROM (
    SELECT
        `post`.`id` AS `post_id`,
        `post`.`title` AS `post_title`,
        `post`.`content` AS `post_content`,
        `post`.`views` AS `post_views`,
        `post`.`image` AS `post_image`,
        `post`.`createdAt` AS `post_createdAt`,
        `post`.`updatedAt` AS `post_updatedAt`,
        `post`.`memberId` AS `post_memberId`,
        `member`.`id` AS `member_id`,
        `member`.`nickname` AS `member_nickname`,
        `member`.`password` AS `member_password`,
        `member`.`email` AS `member_email`,
        `member`.`createdAt` AS `member_createdAt`,
        `member`.`updatedAt` AS `member_updatedAt`
    FROM `posts` `post`
    LEFT JOIN `members` `member` ON `member`.`id` = `post`.`memberId`
    WHERE `post`.`createdAt` <= ?
) `distinctAlias`
ORDER BY
    `distinctAlias`.`post_createdAt` DESC,
    `distinctAlias`.`post_id` DESC,
    `post_id` ASC
LIMIT 10

mysql의 CURDATE() 사용

-- 쿼리빌더 개선쿼리
explain select DISTINCT
    `distinctAlias`.`post_id` AS `ids_post_id`,
    `distinctAlias`.`post_createdAt`,
    `distinctAlias`.`post_id`
FROM (
    SELECT
        `post`.`id` AS `post_id`,
        `post`.`title` AS `post_title`,
        `post`.`content` AS `post_content`,
        `post`.`views` AS `post_views`,
        `post`.`image` AS `post_image`,
        `post`.`createdAt` AS `post_createdAt`,
        `post`.`updatedAt` AS `post_updatedAt`,
        `post`.`memberId` AS `post_memberId`,
        `member`.`id` AS `member_id`,
        `member`.`nickname` AS `member_nickname`,
        `member`.`password` AS `member_password`,
        `member`.`email` AS `member_email`,
        `member`.`createdAt` AS `member_createdAt`,
        `member`.`updatedAt` AS `member_updatedAt`
    FROM `posts` `post`
    LEFT JOIN `members` `member` ON `member`.`id` = `post`.`memberId`
    WHERE `post`.`createdAt` <= CURDATE()
) `distinctAlias`
ORDER BY
    `distinctAlias`.`post_createdAt` DESC,
    `distinctAlias`.`post_id` DESC,
    `post_id` ASC
LIMIT 10;

range scan 이란?
where문을쓰면 range scan 이 되는듯 하다.
근데 rows가 너무많은데?

결론 : 항상 range 가 index보다 좋은것은 아니다. 케바케

* 페이징 쪽의 속도를 개선해보자.

현재 3824 ms 엄청 느리다.

홈컨트롤러쪽 코드에 skip 만 추가하였다.

쿼리 빌더 말고 orm으로 하는 방법도 있을것같은데
이것도 나쁘지 않다.
orm은 잘알고 써야된다는 마스터님의 말이 다시 떠오른다.
orm으로 짯을때보다 엄청난 성능개선이 생겼다.
orm을 잘 모른채로 썻다는 말일지도..
참고로 orm 측의 쿼리는 index를걸어도 full scan을 해버린다.
내가 인덱스를 잘못건건지? 쿼리를 잘못짠건지? (후자일가능성이 높음)

그런데, 앞쪽페이징은 빠른데 뒤쪽페이징은 엄청 느린현상 발견! -> 해결중....
-> skip기반의 offset 명령어는 offset이 100이면 100개의 data를 탐색해야한다. => offset이 클수록 느려지는게 당연
해결 : cursor기반의 페이지네이션 필요...

* 결론

쿼리빌더사용
orderBy에 있는 createdAt 컬럼에 indexing후 3800 ms -> 150 ms 로 성능개선

* 페이징과 전체글의 갯수를 위해 posts의 전체 갯수가 필요하다.

그런데 단순 count쿼리가 120ms나 잡아먹는걸 발견했다

~~일단 속도가 봐줄만하니까 일단 넘어가자.~~

* 페이징 뷰 동적으로 수정

게시글이 100만개가 되면서 페이지 숫자가 너무 많아지는 문제가 생겼다.
ejs 수정

<%
function generatePagination(currentPage, totalPages, displayRange = 5) {
    let start = Math.max(1, currentPage - Math.floor(displayRange / 2));
    let end = Math.min(totalPages, start + displayRange - 1);
    start = Math.max(1, end - displayRange + 1);

    let pages = [];
    if (start > 1) {
        pages.push(1);
        if (start > 2) pages.push('...');
    }
    for (let i = start; i <= end; i++) {
        pages.push(i);
    }
    if (end < totalPages) {
        if (end < totalPages - 1) pages.push('...');
        pages.push(totalPages);
    }
    return pages;
}

const currentPage = parseInt(curPage.curPage) || 1;
const totalPages = Math.ceil(data.total / 10);
const pages = generatePagination(currentPage, totalPages);
%>

<nav class="navbar">
    <% if (currentPage > 1) { %>
        <a href="/post?page=<%= currentPage - 1 %>">&laquo; Previous</a>
    <% } %>

    <% pages.forEach(page => { %>
        <% if (page === '...') { %>
            <span>...</span>
        <% } else if (page === currentPage) { %>
            <span class="current"><%= page %></span>
        <% } else { %>
            <a href="/post?page=<%= page %>"><%= page %></a>
        <% } %>
    <% }); %>

백엔드 (컨트롤러) 에서는 현재 페이지가 어딘지 추가로 보내줘야 한다.

const curPage  =  {
    curPage: +req.query['page'],
}

const posts = data.data;
model.set("posts",posts);
model.set("data", data);
model.set("curPage", curPage);

graph TD
    A[시작] --> B[현재 페이지와 총 페이지 수 입력]
    B --> C[시작 페이지 계산]
    C --> D[끝 페이지 계산]
    D --> E{시작 > 1?}
    E -->|Yes| F[1 추가]
    E -->|No| G[페이지 배열 생성]
    F --> H{시작 > 2?}
    H -->|Yes| I["'...' 추가"]
    H -->|No| G
    I --> G
    G --> J[시작부터 끝까지 페이지 추가]
    J --> K{끝 < 총 페이지?}
    K -->|Yes| L["'...' 추가"]
    K -->|No| M[완료]
    L --> N[마지막 페이지 추가]
    N --> M

<nav class="navbar">
    <div class="pagination">
        <% if (currentPage > 1) { %>
            <a href="/post?page=<%= currentPage - 1 %>" class="page-link">&laquo; Previous</a>
        <% } %>

        <% pages.forEach(page => { %>
            <% if (page === '...') { %>
                <span class="page-link">...</span>
            <% } else if (page === currentPage) { %>
                <span class="page-link current"><%= page %></span>
            <% } else { %>
                <a href="/post?page=<%= page %>" class="page-link"><%= page %></a>
            <% } %>
        <% }); %>

        <% if (currentPage < totalPages) { %>
            <a href="/post?page=<%= currentPage + 1 %>" class="page-link">Next &raquo;</a>
        <% } %>
    </div>

    <div class="nav-actions">
        <div class="search-box">
            <input type="text" placeholder="검색어를 입력하세요" class="search-input">
            <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" width="24" height="24">
                <path fill="none" stroke="currentColor" stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M15.5 15.5L20 20M10 17a7 7 0 100-14 7 7 0 000 14z"/>
            </svg>
        </div>
        <button onclick="location.href='/post/form'" class="action-button">
            글쓰기
        </button>
    </div>
</nav>

/* 페이지네이션 */
.navbar {
    display: flex;
    justify-content: space-between;
    align-items: center;
    padding: 10px 20px;
    background-color: #f8f9fa;
    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
}

.pagination {
    display: flex;
    align-items: center;
}

.page-link {
    padding: 5px 10px;
    margin: 0 2px;
    border: 1px solid #ddd;
    color: #333;
    text-decoration: none;
    border-radius: 3px;
    transition: background-color 0.3s;
}

.page-link:hover:not(.current) {
    background-color: #e9ecef;
}

.page-link.current {
    background-color: #007bff;
    color: white;
    border-color: #007bff;
}

.nav-actions {
    display: flex;
    align-items: center;
}

.search-box {
    display: flex;
    margin-right: 10px;
}

.search-input {
    padding: 5px 10px;
    border: 1px solid #ddd;
    border-right: none;
    border-radius: 3px 0 0 3px;
}

.search-button {
    padding: 5px 10px;
    background-color: #007bff;
    color: white;
    border: 1px solid #007bff;
    border-radius: 0 3px 3px 0;
    cursor: pointer;
    display: flex;
    align-items: center;
    justify-content: center;
}

.search-button svg {
    width: 20px;
    height: 20px;
}

.search-button:hover {
    background-color: #0056b3;
}

.action-button {
    padding: 5px 15px;
    background-color: #007bff;
    color: white;
    border: none;
    border-radius: 3px;
    cursor: pointer;
    transition: background-color 0.3s;
}

.action-button:hover {
    background-color: #218838;
}

@media (max-width: 768px) {
    .navbar {
        flex-direction: column;
    }

    .pagination, .nav-actions {
        margin-top: 10px;
    }
}

* 레퍼런스

https://www.youtube.com/watch?v=VvYh8HBM0A8&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=6

저작자표시

'JS > boostCamp' 카테고리의 다른 글

24.10.15. 개발일지 // oauth fix, 이미지 업로드, Not allowed to load local resource error 해결 (0)	2024.10.16
24.10.14. 개발일지 // github oauth구현 (0)	2024.10.14
24.10.11. 개발일지 // 배포, redis, 댓글, 조회수증가, 페이징, dns, 에러페이지 (3)	2024.10.14
24. 10. 10. 개발일지 // 글쓰기 구현, 동적 url 매핑 , n+1 문제 (0)	2024.10.10
24. 10. 9. 개발일지 // redirectAttributes, 로그인이완료된후 원래페이지 이동, db, n+1 문제 (1)	2024.10.10

* db 공부

* 페이징 쪽의 속도를 개선해보자.

* 결론

* 페이징과 전체글의 갯수를 위해 posts의 전체 갯수가 필요하다.

* 페이징 뷰 동적으로 수정

* 레퍼런스

'JS > boostCamp' 카테고리의 다른 글

티스토리툴바