인덱스 기초

SELECT OBJECT_NAME(i.object_id) AS 표, i.name AS 인덱스, i.type_desc AS 종류,
       (SELECT STRING_AGG(c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END, ', ')
               WITHIN GROUP (ORDER BY ic.key_ordinal)
        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) AS 키열
FROM sys.indexes i
    JOIN sys.tables t ON t.object_id = i.object_id
WHERE t.is_ms_shipped = 0
ORDER BY 표, i.index_id;

CREATE TABLE Board.T_IX (
    num   int           NOT NULL,
    code  varchar(20)   NOT NULL,
    title nvarchar(100) NOT NULL,
    hits  int           NOT NULL
);

-- 10만 행을 채웁니다. 값은 모두 행 번호에서 만들어 언제 돌려도 같습니다.
INSERT INTO Board.T_IX (num, code, title, hits)
SELECT TOP (100000)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
       'C' + RIGHT('000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(6)), 6),
       N'제목 ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS nvarchar(10)),
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 1000
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

-- 이 표가 디스크에서 어떤 모양인지 봅니다.
SELECT i.type_desc AS 구조, ps.index_level AS 수준,
       ps.page_count AS 페이지, ps.page_count * 8 AS KB
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Board.T_IX'), NULL, NULL, 'DETAILED') ps
    JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id;

SET STATISTICS IO ON;
SELECT code, title FROM Board.T_IX WHERE num = 50000;
SET STATISTICS IO OFF;

ALTER TABLE Board.T_IX ADD CONSTRAINT PK_T_IX PRIMARY KEY CLUSTERED (num);

SELECT i.type_desc AS 구조, ps.index_level AS 수준,
       ps.page_count AS 페이지, ps.record_count AS 행
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Board.T_IX'), NULL, NULL, 'DETAILED') ps
    JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
ORDER BY ps.index_level;

SET STATISTICS IO ON;
-- 클러스터형 키로 찾습니다.
SELECT code, title FROM Board.T_IX WHERE num = 50000;

-- 키가 아닌 열로 찾습니다.
SELECT num, title FROM Board.T_IX WHERE code = 'C050000';
SET STATISTICS IO OFF;

CREATE NONCLUSTERED INDEX IX_T_IX_code ON Board.T_IX (code);

SELECT i.name AS 인덱스, i.type_desc AS 종류, ps.index_level AS 수준,
       ps.page_count AS 페이지, ps.page_count * 8 AS KB
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Board.T_IX'), NULL, NULL, 'DETAILED') ps
    JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
ORDER BY i.index_id, ps.index_level;

SET STATISTICS IO ON;
-- 인덱스가 담고 있는 열만 가져옵니다.
SELECT num FROM Board.T_IX WHERE code = 'C050000';

-- 담고 있지 않은 열을 더합니다.
SELECT num, title FROM Board.T_IX WHERE code = 'C050000';
SET STATISTICS IO OFF;

-- 실행하지 않고 계획만 봅니다. 이 문장은 배치에 혼자 있어야 합니다.
SET SHOWPLAN_TEXT ON;
GO
SELECT num, title FROM Board.T_IX WHERE code = 'C050000';
GO
SET SHOWPLAN_TEXT OFF;

DECLARE @t nvarchar(100);
SET STATISTICS IO ON;
SELECT @t = title FROM Board.T_IX WHERE code = 'C050000';      -- 1건
SELECT @t = title FROM Board.T_IX WHERE code LIKE 'C0500%';    -- 100건
SELECT @t = title FROM Board.T_IX WHERE code LIKE 'C050%';     -- 1,000건
SELECT @t = title FROM Board.T_IX WHERE code LIKE 'C05%';      -- 10,000건
SET STATISTICS IO OFF;

CREATE NONCLUSTERED INDEX IX_T_IX_code_inc ON Board.T_IX (code) INCLUDE (title);

DECLARE @t nvarchar(100);
SET STATISTICS IO ON;
SELECT @t = title FROM Board.T_IX WHERE code = 'C050000';
SELECT @t = title FROM Board.T_IX WHERE code LIKE 'C0500%';
SELECT @t = title FROM Board.T_IX WHERE code LIKE 'C050%';
SELECT @t = title FROM Board.T_IX WHERE code LIKE 'C05%';
SET STATISTICS IO OFF;

SELECT i.name AS 인덱스, SUM(ps.page_count) AS 페이지, SUM(ps.page_count) * 8 AS KB
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Board.T_IX'), NULL, NULL, 'DETAILED') ps
    JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
GROUP BY i.name, i.index_id
ORDER BY i.index_id;

-- lab-setup.sql 에 들어 있는 정의입니다.
CREATE NONCLUSTERED INDEX IX_POSTS_list
    ON Board.POSTS (board_num, group_num DESC, sort_no)
    INCLUDE (title, user_num, reg_date, hit_count, depth);

-- 목록 화면이 내는 것과 같은 문장입니다.
SET STATISTICS IO ON;
SELECT TOP (20) num, title, user_num, reg_date, hit_count
FROM Board.POSTS WHERE board_num = 2
ORDER BY group_num DESC, sort_no;

-- 본문을 하나 더합니다. content 는 인덱스에 없습니다.
SELECT TOP (20) num, title, user_num, reg_date, hit_count, content
FROM Board.POSTS WHERE board_num = 2
ORDER BY group_num DESC, sort_no;
SET STATISTICS IO OFF;

-- 같은 모양의 표 둘. 한쪽에만 비클러스터형 인덱스를 셋 겁니다.
CREATE TABLE Board.T_W0 (num int NOT NULL, code varchar(20) NOT NULL,
    title nvarchar(100) NOT NULL, hits int NOT NULL,
    CONSTRAINT PK_T_W0 PRIMARY KEY CLUSTERED (num));

CREATE TABLE Board.T_W3 (num int NOT NULL, code varchar(20) NOT NULL,
    title nvarchar(100) NOT NULL, hits int NOT NULL,
    CONSTRAINT PK_T_W3 PRIMARY KEY CLUSTERED (num));

CREATE NONCLUSTERED INDEX IX_T_W3_code  ON Board.T_W3 (code);
CREATE NONCLUSTERED INDEX IX_T_W3_hits  ON Board.T_W3 (hits) INCLUDE (title);
CREATE NONCLUSTERED INDEX IX_T_W3_title ON Board.T_W3 (title);
GO

DECLARE @rec int, @bytes bigint, @i int;

-- 한 행씩 1,000번 넣습니다. 응용 프로그램이 실제로 하는 방식입니다.
BEGIN TRAN;
SET @i = 1;
WHILE @i <= 1000
BEGIN
    INSERT INTO Board.T_W0 (num, code, title, hits)
    VALUES (@i, 'C' + RIGHT('000000' + CAST(@i AS varchar(6)), 6),
            N'제목 ' + CAST(@i AS nvarchar(10)), @i % 100);
    SET @i += 1;
END
SELECT @rec = database_transaction_log_record_count,
       @bytes = database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions WHERE database_id = DB_ID();
ROLLBACK;
SELECT N'인덱스 없음' AS 표, @rec AS 로그레코드, @bytes AS 로그바이트;

-- 같은 WHILE 문을 한 번 더 적고 넣는 표만 T_W3 로 바꿔 다시 잽니다.

SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(*) FROM Board.POSTS WHERE board_num = 2;   -- 첫 열
GO
SELECT COUNT(*) FROM Board.POSTS WHERE sort_no = 0;      -- 셋째 열
GO
SET SHOWPLAN_TEXT OFF;

-- 인덱스를 사용하지 못합니다.
SELECT COUNT(*) FROM Board.POSTS WHERE YEAR(reg_date) = 2026;

-- 같은 결과이고 인덱스를 사용할 수 있습니다.
SELECT COUNT(*) FROM Board.POSTS
WHERE reg_date >= '2026-01-01' AND reg_date < '2027-01-01';