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;

3초.. 엄청오래걸림

type: all => full scan

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

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

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

  

  서버재실행시 index를 drop 시키는 모습

인덱스 생성전 실행시간

(createdAt, id) 복합 인덱스 생성후

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

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

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

쿼리 실행 순서:
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;

explain

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

where문을 뺀경우, index full scan type이된다.

빨라졌다. ???

where문을 넣은경우 : 평균 10~15 ms

where문을 뺀경우 : 평균 8~10 ms

• 결론 : 항상 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>

• css

/* 페이지네이션 */
.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