인덱스 설계와 커버링

CREATE INDEX IX_T1 ON Board.POSTS_BIG (board_num, user_num);
CREATE INDEX IX_T2 ON Board.POSTS_BIG (user_num, board_num);
GO

SET STATISTICS IO ON;
-- 어느 인덱스를 사용할지 직접 지정해 견줍니다.
SELECT COUNT(*) FROM Board.POSTS_BIG WITH (INDEX(IX_T1)) WHERE user_num = 7;
SELECT COUNT(*) FROM Board.POSTS_BIG WITH (INDEX(IX_T2)) WHERE user_num = 7;

SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(*) FROM Board.POSTS_BIG WITH (INDEX(IX_T1)) WHERE user_num = 7;
GO
SELECT COUNT(*) FROM Board.POSTS_BIG WITH (INDEX(IX_T2)) WHERE user_num = 7;
GO
SET SHOWPLAN_TEXT OFF;

CREATE INDEX IX_HIT ON Board.POSTS_BIG (hit_count);
GO

SET STATISTICS IO ON;
SELECT title FROM Board.POSTS_BIG WHERE hit_count <= 1;    -- 1,000행
SELECT title FROM Board.POSTS_BIG WHERE hit_count <= 2;    -- 1,500행
SELECT title FROM Board.POSTS_BIG WHERE hit_count <= 49;   -- 25,000행

-- 25,000행을 굳이 키 조회로 가져오게 합니다.
SELECT title FROM Board.POSTS_BIG WITH (INDEX(IX_HIT)) WHERE hit_count <= 49;

CREATE INDEX IX_HIT_COVER ON Board.POSTS_BIG (hit_count) INCLUDE (title);
GO

SET STATISTICS IO ON;
SELECT title FROM Board.POSTS_BIG WHERE hit_count <= 1;    -- 1,000행
SELECT title FROM Board.POSTS_BIG WHERE hit_count <= 49;   -- 25,000행

CREATE INDEX IX_HIT_KEY ON Board.POSTS_BIG (hit_count, title);
GO

-- 정렬까지 요구해 봅니다.
SET SHOWPLAN_TEXT ON;
GO
SELECT title FROM Board.POSTS_BIG WITH (INDEX(IX_HIT_COVER))
WHERE hit_count = 5 ORDER BY title;
GO
SELECT title FROM Board.POSTS_BIG WITH (INDEX(IX_HIT_KEY))
WHERE hit_count = 5 ORDER BY title;
GO
SET SHOWPLAN_TEXT OFF;

-- 세어 둔 것이 보이도록 몇 가지를 실행해 둡니다.
BEGIN TRAN;
UPDATE TOP (1000) Board.POSTS_BIG SET hit_count = hit_count + 1;
ROLLBACK;
SELECT COUNT(*) FROM Board.POSTS_BIG WHERE hit_count = 5;
SELECT TOP (20) title FROM Board.POSTS_BIG WHERE board_num = 2 ORDER BY group_num DESC, sort_no;
SELECT COUNT(*) FROM Board.POSTS_BIG WHERE user_num = 7;
GO

SELECT i.name AS 인덱스,
       ISNULL(s.user_seeks,   0) AS 탐색,
       ISNULL(s.user_scans,   0) AS 스캔,
       ISNULL(s.user_lookups, 0) AS 키조회,
       ISNULL(s.user_updates, 0) AS 갱신
FROM sys.indexes i
    LEFT JOIN sys.dm_db_index_usage_stats s
           ON s.object_id = i.object_id
          AND s.index_id  = i.index_id
          AND s.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID('Board.POSTS_BIG')
ORDER BY i.index_id;

WITH K AS (
    SELECT i.index_id, i.name,
           STUFF((SELECT ', ' + c.name
                  FROM sys.index_columns ic
                      JOIN sys.columns c ON c.object_id = ic.object_id
                                       AND c.column_id = ic.column_id
                  WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                    AND ic.is_included_column = 0
                  ORDER BY ic.key_ordinal FOR XML PATH('')), 1, 2, '') AS keys
    FROM sys.indexes i
    WHERE i.object_id = OBJECT_ID('Board.POSTS_BIG') AND i.index_id > 1
)
SELECT a.name AS 가려지는것, a.keys AS 그키,
       b.name AS 가리는것,  b.keys AS 이키
FROM K a JOIN K b ON a.index_id <> b.index_id AND b.keys LIKE a.keys + '%'
ORDER BY a.name;

-- 인덱스가 없는 열로 몇 번 조회하면 기록이 쌓입니다.
SELECT COUNT(*) FROM Board.POSTS_BIG WHERE category_num = 3 AND hit_count > 900;
SELECT COUNT(*) FROM Board.POSTS_BIG WHERE category_num = 5 AND hit_count > 900;
GO

SELECT CAST(gs.avg_user_impact AS decimal(5,1)) AS 개선율,
       gs.user_seeks AS 요청횟수,
       id.equality_columns AS 등호열,
       id.inequality_columns AS 부등호열,
       id.included_columns AS 포함열
FROM sys.dm_db_missing_index_group_stats gs
    JOIN sys.dm_db_missing_index_groups g ON g.index_group_handle = gs.group_handle
    JOIN sys.dm_db_missing_index_details id ON id.index_handle = g.index_handle
WHERE id.object_id = OBJECT_ID('Board.POSTS_BIG')
ORDER BY gs.avg_user_impact DESC;

-- 한 번 실행해 캐시를 채운 뒤, 세 번 재어 범위를 봅니다.
BEGIN TRAN;
DECLARE @t datetime2(7) = SYSDATETIME();

INSERT INTO Board.POSTS_BIG (board_num, category_num, user_num, parent_num,
    group_num, depth, sort_no, title, content, hit_count, reg_date)
SELECT TOP (10000) board_num, category_num, user_num, NULL,
    group_num, depth, sort_no, title, content, hit_count, reg_date
FROM Board.POSTS_BIG ORDER BY num;

PRINT CONCAT(DATEDIFF(millisecond, @t, SYSDATETIME()), ' ms');
ROLLBACK;

-- 인덱스 6개인 상태에서 1만 행을 고칩니다.
UPDATE TOP (10000) Board.POSTS_BIG SET hit_count = hit_count + 1;
--                                    ↑ 인덱스 셋에 들어 있는 열

UPDATE TOP (10000) Board.POSTS_BIG SET mod_date = '2026-06-01';
--                                    ↑ 어느 인덱스에도 없는 열