MySQL sys 스키마로 테이블 스캔이 많은 쿼리 찾기: 10만 행 실험과 인덱스 개선 검증

고객 한 명의 주문 합계를 구하는 SQL은 결과가 한 행뿐입니다. 그런데 결과가 작다고 해서 MySQL이 읽는 데이터도 작은 것은 아닙니다. 조건에 맞는 인덱스가 없으면 10건을 찾기 위해 테이블 전체를 훑고, 이런 조회가 초당 수십 번 반복되면 서버 전체의 부담이 됩니다.

이 글에서는 MySQL 8.4.11에 주문 10만 건을 만들고, sys.statements_with_full_table_scans로 테이블 스캔이 발생한 SQL을 찾았습니다. 이어서 고객 번호 인덱스를 추가하고 실행계획과 구간별 통계를 비교했습니다. 확인한 내용은 다음 세 가지입니다.

• EXPLAIN의 예상 행 수와 실제 조사 행 수가 왜 다르고, 무엇을 근거로 판단해야 하는지
• 인덱스 추가 효과를 누적 통계에서 어떻게 분리해 측정하는지
• 개선이 끝났는데도 해당 SQL이 sys 스캔 목록에 계속 남는 이유

실제 운영에서도 sys 스키마로 풀 스캔이 발생한 쿼리를 찾고, 인덱스를 추가해 성능을 개선한 일이 많았습니다. 이 글은 그 작업 흐름을 재현 환경에서 단계별 숫자로 확인한 기록입니다.

실제 서비스 장애 사례가 아니라 반복 조회에서 불필요한 스캔이 생기는 조건을 재현한 실험입니다. 각 5회씩 실행한 소규모 측정이므로 시간 수치는 참고용이며, 판단의 중심은 접근 방식과 조사 행 수에 둡니다.

1. 테스트 환경과 측정 조건

항목	실험 조건
MySQL 버전	8.4.11 (Community, Windows)
테스트 테이블	lab44_sys_scan.orders_scan
스토리지 엔진	InnoDB
전체 데이터	100,000행
고객 수	10,000명 (고객당 주문 10건)
조회 대상	customer_id = 4242, 주문 10행
초기 인덱스	PRIMARY KEY (order_id)
추가 인덱스	INDEX idx_customer_id (customer_id)
비교 횟수	변경 전 5회, 변경 후 5회
측정 방식	SQL digest별 누적 통계와 변경 전후 차감
캐시 조건	캐시 초기화 없이 연속 실행

운영 데이터와 분리한 lab44_sys_scan 스키마를 사용했습니다. 아래는 같은 데이터 분포를 만드는 재현용 SQL입니다. 고객 번호를 1부터 10,000까지 순환시키면 모든 고객이 정확히 10건의 주문을 갖게 됩니다. 금액 컬럼의 값과 그 밖의 컬럼 구성은 실험 결과에 영향을 주지 않는 범위에서 단순화했습니다.

CREATE DATABASE lab44_sys_scan CHARACTER SET utf8mb4;
USE lab44_sys_scan;

CREATE TABLE orders_scan (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (order_id)
) ENGINE = InnoDB;

SET SESSION cte_max_recursion_depth = 100000;

INSERT INTO orders_scan (order_id, customer_id, amount)
WITH RECURSIVE seq (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT
    n,
    MOD(n - 1, 10000) + 1,
    ROUND(1000 + MOD(n * 37, 9000), 2)
FROM seq;

ANALYZE TABLE orders_scan;

데이터를 넣은 뒤 버전, 전체 행 수, 고객 수, 대상 고객의 주문 수, 현재 인덱스를 한 번에 확인했습니다.

SELECT
    VERSION() AS mysql_version,
    DATABASE() AS test_schema,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT customer_id) AS customers,
    SUM(customer_id = 4242) AS target_rows,
    (
        SELECT GROUP_CONCAT(
            CONCAT(INDEX_NAME, '(', COLUMN_NAME, ')')
            ORDER BY INDEX_NAME, SEQ_IN_INDEX
            SEPARATOR ', '
        )
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = 'lab44_sys_scan'
          AND TABLE_NAME = 'orders_scan'
    ) AS index_definition
FROM orders_scan;

전체 100,000행, 고객 10,000명, 대상 고객 주문 10행, 인덱스는 PRIMARY(order_id) 하나입니다. 찾으려는 데이터는 전체의 0.01%에 불과합니다.

실험 대상 SQL은 다음과 같습니다. 결과 열에 lab44_total이라는 별칭을 붙여, 나중에 digest 통계에서 이 SQL을 쉽게 구분할 수 있도록 했습니다.

SELECT SUM(amount) AS lab44_total
FROM orders_scan
WHERE customer_id = 4242;

2. 원인 가설: 고객 번호 조건에 쓸 인덱스가 없다

기본키가 있다고 모든 조회가 인덱스를 쓰는 것은 아닙니다. 이 테이블의 기본키는 order_id인데, 검색 조건은 customer_id입니다. 그래서 “고객 번호 인덱스가 없어 조건에 맞는 주문을 찾으려고 전체 테이블을 읽는다”는 가설을 세우고 실행계획부터 확인했습니다.

EXPLAIN FORMAT=TRADITIONAL
SELECT SUM(amount) AS lab44_total
FROM orders_scan
WHERE customer_id = 4242;

컬럼	값	의미
type	ALL	테이블 전체를 순서대로 읽는 접근 방식
possible_keys / key	NULL	후보 인덱스도, 선택된 인덱스도 없음
rows	91,624	옵티마이저가 추정한 조사 행 수
filtered	10	조사한 행 중 조건을 통과할 비율의 추정치(%)
Extra	Using where	읽은 뒤 WHERE 조건으로 걸러냄

EXPLAIN의 숫자는 실측값이 아니다

일반 EXPLAIN의 rows와 filtered는 모두 옵티마이저의 추정치입니다. 91,624는 InnoDB가 샘플링한 테이블 크기 추정값이라 실제 100,000행과 다릅니다. 이 차이만으로 데이터 누락이나 오류를 의심할 필요는 없습니다.

filtered = 10은 더 크게 빗나갔습니다. 옵티마이저 예상대로라면 약 9,000행이 조건을 통과해야 하지만, 실제로는 10행뿐입니다. customer_id에는 인덱스도 히스토그램도 없어서, 옵티마이저가 분포를 모르고 기본 비율을 적용했기 때문입니다. 추정이 이렇게 크게 빗나가면 조인 순서 같은 다른 판단도 틀어질 수 있습니다. 이 단계에서 믿을 수 있는 정보는 type = ALL과 key = NULL, 즉 접근 방식뿐입니다. 실제로 몇 행을 읽었는지는 실행 후 통계로 따로 확인해야 합니다.

3. sys 스키마로 실제 조사 행 수를 검증

sys.statements_with_full_table_scans는 performance_schema.events_statements_summary_by_digest를 읽기 쉽게 정리한 뷰입니다. 리터럴 값을 ?로 바꾼 정규화 SQL(digest) 단위로, 인덱스를 쓰지 않은 실행 횟수와 조사 행 수를 누적해 보여 줍니다.

범위를 이 SQL 하나로 좁히기 위해, 대상 SQL을 한 번 실행한 뒤 digest 값을 변수에 저장했습니다. 데이터 확인용 SQL이나 진단 SQL 자신의 통계가 섞이지 않게 하려는 조치입니다.

SELECT DIGEST
INTO @lab44_digest
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'lab44_sys_scan'
  AND DIGEST_TEXT LIKE '%lab44_total%'
  AND DIGEST_TEXT NOT LIKE '%DIGEST%'
ORDER BY LAST_SEEN DESC
LIMIT 1;

대상 SQL을 총 5회 실행한 뒤 sys 뷰를 조회했습니다.

SELECT
    query,
    exec_count,
    no_index_used_count,
    no_index_used_pct,
    rows_examined,
    rows_examined_avg,
    total_latency
FROM sys.statements_with_full_table_scans
WHERE db = 'lab44_sys_scan'
  AND digest = @lab44_digest;

지표	측정값	해석
exec_count	5	해당 SQL의 누적 실행 횟수
no_index_used_count	5	인덱스를 사용하지 않은 실행 횟수
no_index_used_pct	100	인덱스를 사용하지 않은 실행 비율
rows_examined	500,000	누적 조사 행 수
rows_examined_avg	100,000	실행당 평균 조사 행 수
total_latency	72.82 ms	5회 실행의 누적 시간

조건에 맞는 주문은 10행인데 실행마다 100,000행을 조사했습니다. 결과 10행을 얻으려고 만 배를 읽은 셈입니다. 실행계획의 ALL과 실제 조사 행 수가 같은 원인을 가리키므로 가설이 확인되었습니다.

단, rows_examined는 SQL 실행 계층이 조사한 행 수입니다. 디스크에서 읽은 페이지 수나 물리 I/O 횟수와는 다른 지표이며, 이번 실험에서는 물리 I/O를 따로 측정하지 않았습니다.

정규화 SQL에 보이는 LIMIT의 정체

결과의 query 컬럼 끝에는 원래 SQL에 없던 LIMIT ?가 붙어 있습니다. MySQL Workbench의 결과 행 제한(Limit Rows) 기능이 SELECT 문 끝에 LIMIT을 덧붙인 것으로 보입니다. 다만 화면만으로 추가된 값까지 확정할 수는 없습니다.

이 SQL은 GROUP BY 없는 합계 조회라 결과가 원래 한 행입니다. 따라서 LIMIT이 붙어도 합계 계산에 필요한 입력 행 수는 줄어들지 않습니다.

운영에서 더 중요한 점은 digest가 SQL 문장 형태로 결정된다는 사실입니다. 같은 조회라도 LIMIT이 붙은 버전과 붙지 않은 버전은 서로 다른 digest로 집계됩니다. 그래서 관리 도구에서 재현한 SQL이 애플리케이션의 SQL과 다른 줄에 쌓일 수 있습니다. 재현 실험을 할 때는 도구의 행 제한 설정을 변경 전후로 똑같이 유지하고, 운영 SQL을 추적할 때는 애플리케이션이 실제로 보낸 문장의 digest를 기준으로 해야 합니다.

4. 인덱스 추가 후 실행계획과 실측값 비교

변경 전 누적값을 먼저 저장

digest 통계는 서버가 시작된 뒤 계속 누적됩니다. 인덱스를 추가한 다음에 그냥 다시 조회하면 변경 전과 후의 실행이 합쳐진 값이 나옵니다. 그래서 변경 직전의 누적값을 변수에 저장해 두었습니다.

SELECT
    COUNT_STAR,
    SUM_ROWS_EXAMINED,
    SUM_NO_INDEX_USED,
    SUM_TIMER_WAIT
INTO
    @lab44_before_exec,
    @lab44_before_rows,
    @lab44_before_no_index,
    @lab44_before_time
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'lab44_sys_scan'
  AND DIGEST = @lab44_digest;

고객 번호 인덱스 추가

변경 내용은 customer_id 단일 열 인덱스 추가입니다.

ALTER TABLE lab44_sys_scan.orders_scan
    ADD INDEX idx_customer_id (customer_id);

ANALYZE TABLE lab44_sys_scan.orders_scan;

같은 조건으로 EXPLAIN을 다시 실행했습니다.

컬럼	변경 전	변경 후
type	ALL	ref
key	NULL	idx_customer_id
ref	NULL	const
rows (추정)	91,624	9
filtered (추정)	10	100
Extra	Using where	NULL

접근 방식이 ref로 바뀌었고, 인덱스에서 상수 4242와 같은 범위만 찾는 계획이 선택되었습니다. 인덱스가 조건을 이미 걸러 주기 때문에 Using where도 사라졌습니다. rows = 9는 실제 10행과 다르지만 이 역시 추정치이므로 일치할 필요는 없습니다. 인덱스 통계가 생기면서 추정이 실제에 훨씬 가까워졌다는 점이 의미 있는 변화입니다.

누적값 차감으로 변경 후 5회만 분리

인덱스를 추가한 뒤 같은 SQL을 다시 5회 실행했습니다. 그다음 현재 누적값에서 저장해 둔 변경 전 값을 빼서, 변경 후 구간만 따로 계산했습니다. SUM_TIMER_WAIT는 피코초 단위라서 1,000,000,000으로 나누어 밀리초로 바꿨습니다.

SELECT
    'before_index' AS phase,
    @lab44_before_exec AS executions,
    @lab44_before_rows AS rows_examined,
    ROUND(
        @lab44_before_rows
        / NULLIF(@lab44_before_exec, 0),
        2
    ) AS rows_per_exec,
    @lab44_before_no_index AS no_index_used,
    ROUND(
        @lab44_before_time
        / NULLIF(@lab44_before_exec, 0)
        / 1000000000,
        3
    ) AS avg_ms

UNION ALL

SELECT
    'after_index' AS phase,
    COUNT_STAR - @lab44_before_exec AS executions,
    SUM_ROWS_EXAMINED - @lab44_before_rows AS rows_examined,
    ROUND(
        (SUM_ROWS_EXAMINED - @lab44_before_rows)
        / NULLIF(COUNT_STAR - @lab44_before_exec, 0),
        2
    ) AS rows_per_exec,
    SUM_NO_INDEX_USED - @lab44_before_no_index AS no_index_used,
    ROUND(
        (SUM_TIMER_WAIT - @lab44_before_time)
        / NULLIF(COUNT_STAR - @lab44_before_exec, 0)
        / 1000000000,
        3
    ) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'lab44_sys_scan'
  AND DIGEST = @lab44_digest;

비교 항목	인덱스 추가 전	인덱스 추가 후
실행 횟수	5	5
총 조사 행 수	500,000	50
실행당 조사 행 수	100,000	10
인덱스 미사용 횟수	5	0
평균 실행 시간	14.564 ms	0.462 ms

두 결과의 숫자가 서로 맞는지도 확인했습니다. 변경 전 평균 14.564ms에 5회를 곱하면 72.82ms로, 앞서 sys 뷰에서 확인한 total_latency와 정확히 일치합니다. 차감 SQL이 의도대로 변경 전 구간을 재현했다는 근거입니다.

실행당 조사 행 수는 100,000행에서 10행으로 줄었습니다. 이제 결과로 필요한 행만 정확히 읽습니다. 평균 실행 시간은 약 96.8% 감소했고, 변경 전 시간은 변경 후의 약 31.5배였습니다.

다만 시간 비교는 각 5회의 소규모 측정이고, 캐시를 초기화하지 않은 연속 실행이라 캐시 상태와 실행 순서의 영향이 섞여 있습니다. 31.5배라는 비율을 다른 환경에 그대로 적용하면 안 됩니다. 이번 실험에서 더 확실한 개선 근거는 다음 두 가지입니다.

• 접근 방식이 ALL에서 ref로 바뀌었다는 것
• 조사 행 수가 결과 행 수와 같아졌다는 것

한 단계 더 나아가면 (customer_id, amount) 복합 인덱스도 생각해 볼 수 있습니다. 합계에 필요한 amount까지 인덱스에 들어 있으면 테이블 본문을 다시 찾아가지 않고 인덱스만으로 결과를 만들 수 있습니다. 이 경우 EXPLAIN의 Extra에 Using index가 표시됩니다. 이번 실험에서는 측정하지 않았고, 고객당 10행 수준에서는 차이가 크지 않을 수 있습니다.

5. 개선 후에도 테이블 스캔 목록에 남는 이유

인덱스를 추가한 뒤 sys 뷰를 다시 조회해도 대상 SQL은 목록에서 사라지지 않았습니다.

SELECT
    exec_count,
    no_index_used_count,
    no_index_used_pct,
    rows_examined,
    rows_examined_avg
FROM sys.statements_with_full_table_scans
WHERE db = 'lab44_sys_scan'
  AND digest = @lab44_digest;

지표	최종 누적값	구성
exec_count	10	변경 전 5 + 변경 후 5
no_index_used_count	5	변경 전 5 + 변경 후 0
no_index_used_pct	50	5 ÷ 10
rows_examined	500,050	500,000 + 50
rows_examined_avg	50,005	500,050 ÷ 10

이 뷰는 인덱스를 쓰지 않은 실행이 한 번이라도 누적된 digest를 보여 줍니다. 인덱스를 추가해도 이전 실행 기록은 지워지지 않으므로, 서버가 재시작되거나 통계가 초기화되기 전까지 이 SQL은 목록에 남습니다.

그래서 이 화면을 다음처럼 읽으면 틀립니다.

잘못된 해석	실제 의미
개선 후에도 실행마다 50,005행을 조사한다	두 구간을 합친 평균입니다. 변경 후 실행은 10행만 조사했습니다.
최근 실행의 절반이 풀 스캔이다	미사용 5회는 모두 변경 전 실행입니다. 변경 후에는 0회입니다.
목록에 남아 있으니 인덱스가 효과가 없다	목록은 과거 누적 기록입니다. 효과는 변경 후 구간의 증분으로 판단합니다.

개선 여부의 판단 기준은 목록에서 사라졌는지가 아닙니다. 변경 후 구간에서 no_index_used_count가 늘었는지, 그리고 실행당 조사 행 수가 얼마인지가 기준입니다.

목록을 깨끗하게 비우고 싶다면 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest로 digest 통계를 초기화할 수 있습니다. 하지만 이 명령은 특정 SQL이 아니라 서버 전체의 digest 통계를 지웁니다. 다른 SQL의 비교 기준까지 함께 사라지므로, 운영 서버에서는 이번 실험처럼 차감 방식으로 확인하는 편이 안전합니다.

6. 운영 환경에서 적용할 DBA 판단 기준

정렬은 x$ 뷰의 원시값으로

sys 뷰의 기본 정렬은 인덱스 미사용 비율 순입니다. 그런데 비율 100%인 SQL 중에는 하루 한 번 도는 작은 조회도 섞여 있습니다. 실제 부담 순으로 보려면 누적 시간으로 다시 정렬해야 합니다.

이때 주의할 점이 있습니다. sys 뷰의 total_latency는 “72.82 ms”처럼 단위가 붙은 문자열이라 크기순 정렬이 맞지 않습니다. 같은 이름의 x$ 뷰는 피코초 단위 숫자를 그대로 제공하므로, 정렬에는 이쪽을 사용합니다. 아래 SQL은 이번 실험에서 실행하지 않은 운영용 예시입니다.

SELECT
    db,
    LEFT(query, 80) AS query_head,
    exec_count,
    no_index_used_pct,
    rows_examined_avg,
    ROUND(total_latency / 1000000000, 1) AS total_ms,
    last_seen
FROM sys.x$statements_with_full_table_scans
WHERE db NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
ORDER BY total_latency DESC
LIMIT 20;

last_seen도 같이 보면 좋습니다. 마지막 실행이 오래전이라면 이미 고쳐졌거나 더는 쓰이지 않는 SQL일 가능성이 높습니다.

스캔 비율만으로 인덱스를 추가하지 않음

테이블이 작거나 대부분의 행을 읽어야 하는 조회라면 풀 스캔이 오히려 합리적입니다. 우선순위는 다음 네 가지를 함께 보고 정합니다.

• 실행 빈도(exec_count)
• 실행당 조사 행 수와 결과 행 수의 차이
• 누적 실행 시간
• 업무 중요도

이번 실험처럼 10행을 얻으려고 100,000행을 읽는 SQL이 자주 실행된다면 우선순위가 높습니다. 반대로 대부분의 행을 반환하는 집계 쿼리는 비율이 100%여도 인덱스로 얻을 이득이 작습니다.

digest 평균과 특정 값의 결과는 다름

digest는 리터럴을 ?로 바꾼 SQL 유형 단위로 모입니다. 이번에는 고객 4242만 반복 조회했지만, 운영에서는 주문이 수천 건인 고객과 한 건인 고객의 실행이 한 줄에 섞입니다. 평균 조사 행 수가 낮아도 특정 고객 조회만 느릴 수 있으므로, 이상한 사례는 해당 값으로 EXPLAIN ANALYZE를 실행해 따로 확인하는 것이 좋습니다.

인덱스의 쓰기 비용과 생성 작업 영향

이번 실험은 조회 개선만 검증했고, INSERT·UPDATE·DELETE 비용 변화는 측정하지 않았습니다. 운영에 반영할 때는 다음을 함께 점검합니다.

• 기존 인덱스와 중복되지 않는지
• 추가 저장 공간과 쓰기 부하가 감당할 만한지
• 대용량 테이블에서 인덱스 생성 작업 중 메타데이터 잠금 대기가 생기지 않는지

비교 구간을 기록으로 남김

차감 방식은 비교 구간 사이에 서버 재시작이나 통계 초기화가 없어야 성립합니다. digest 테이블이 꽉 차서 일부 SQL이 개별 집계에서 빠지는 경우도 있습니다. 변경 전 값을 저장할 때 측정 시각, digest, COUNT_STAR를 함께 남겨 두면 나중에 결과가 이상해도 원인을 되짚을 수 있습니다.

7. sys 스캔 목록을 읽을 때 구분할 세 가지

이번 실험에서는 10행을 찾는 합계 조회가 실행마다 100,000행을 읽었습니다. 고객 번호 인덱스를 추가하자 조사 행 수가 결과 행 수와 같은 10행으로 줄었습니다. 그런데도 sys 목록에는 과거 기록 때문에 미사용 비율 50%로 남아 있었습니다.

구분	헷갈리기 쉬운 값	판단 근거
추정과 실측	EXPLAIN의 rows, filtered	sys·digest 통계의 rows_examined
누적과 구간	sys 뷰의 평균과 비율	변경 전 값을 뺀 변경 후 증분
SQL 유형과 특정 값	digest 단위 평균	문제 값으로 실행한 EXPLAIN ANALYZE

sys 스키마는 후보를 찾아 주는 출발점일 뿐입니다. 실행계획으로 원인 가설을 세우고, 실제 조사 행 수로 검증한 뒤, 변경 후 구간만 분리해 개선을 확인하는 순서를 지키면 튜닝 결과를 숫자로 설명할 수 있습니다.