MySQL 인덱스 성능 측정 — Innodb_pages_read · IGNORE INDEX 완전 정리

 

 

MySQL 실습 정리

인덱스 성능 측정

Innodb_pages_read와 IGNORE INDEX로 인덱스 사용 차이를 비교한다.

1 핵심 개념

Innodb_pages_read와 IGNORE INDEX로 인덱스 사용 차이를 비교한다. 이 폴더는 인덱스 성능 확인과 저장 프로시저 작성 흐름을 함께 다룬다.

인덱스 실습은 실행 전후의 읽은 페이지 수를 비교하는 것이 핵심이고, 프로시저 실습은 DELIMITER, 매개변수, 변수 선언, CALL 흐름을 정확히 닫는 것이 핵심이다.

성능 측정은 숫자 비교, 프로시저는 실행 흐름 비교로 접근하면 오류를 빨리 찾을 수 있다.

2 원본 예제 코드

USE indexdb; SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; SELECT * FROM emp WHERE emp_no = 100000; SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; SELECT * FROM emp_c WHERE emp_no = 100000; SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; SELECT * FROM emp_se WHERE emp_no = 100000; SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; SELECT * FROM emp_c IGNORE INDEX(PRIMARY) WHERE emp_no < 500000 LIMIT 1000000; ALTER TABLE emp ADD INDEX idx_gender(gender); ANALYZE TABLE emp; SELECT * FROM emp WHERE gender = 'M' LIMIT 500000;

3 용어 카드

PROCEDURE서버에 저장해 CALL로 실행하는 SQL 블록

DELIMITER프로시저 본문 작성 중 문장 종료 기호를 임시 변경

IN프로시저 안으로 값을 전달하는 입력 매개변수

OUT프로시저 밖으로 값을 돌려주는 출력 매개변수

DECLARE프로시저 내부 지역 변수 선언

SELECT INTO조회 결과를 변수에 저장하는 문법

CASE여러 조건 중 하나를 선택하는 분기문

IGNORE INDEX특정 인덱스를 쓰지 않고 조회하도록 유도하는 힌트

 

반응형

 

4 실행 흐름

1

대상 준비indexdb 또는 sqlDB를 선택하고 필요한 테이블이 있는지 확인한다.

2

기준 실행인덱스나 프로시저를 바꾸기 전 기본 SELECT 결과를 먼저 본다.

3

문법 적용CREATE INDEX, CREATE PROCEDURE, CASE, IN/OUT 구문을 적용한다.

4

결과 검산SHOW STATUS, SHOW INDEX, CALL 결과, OUT 변수 값을 확인한다.

5

차이 해석읽은 페이지 수, 반환 행, 조건 분기 결과가 왜 달라졌는지 설명한다.

5 인덱스 성능 기준

비교 대상	의미	확인 포인트
emp	인덱스 없는 테이블	전체 스캔 가능성
emp_c	PRIMARY KEY 테이블	클러스터드 인덱스 효과
emp_se	보조 인덱스 테이블	secondary index 효과
Innodb_pages_read	디스크에서 읽은 페이지 누적값	실행 전후 차이
IGNORE INDEX	인덱스 미사용 비교	힌트 적용 전후 차이

6 프로시저 문법 기준

문법	역할	주의
DROP PROCEDURE IF EXISTS	기존 프로시저 정리	반복 실행 오류 방지
DELIMITER $$	본문 내부 세미콜론과 종료 구분	마지막에 DELIMITER ; 복구
IN 매개변수	호출자가 값을 전달	CALL 값 타입 확인
OUT 매개변수	결과 값을 밖으로 반환	@사용자변수로 받기
SELECT INTO	조회 결과를 변수에 저장	결과가 여러 행이면 오류 가능

7 분기 처리 기준

IF ELSE는 참/거짓 또는 큰 범위의 조건 분기에 좋고, CASE는 여러 값 중 하나를 선택할 때 읽기 쉽다. 띠 계산처럼 나머지 값이 여러 경우로 나뉘면 CASE가 적합하다.

상황	추천 문법	이유
젊음/나이 있음처럼 두 갈래	IF ELSE	조건이 단순하다
12가지 띠 계산	CASE	여러 분기를 나열하기 쉽다
조회 결과를 변수로 판단	SELECT INTO + IF	중간 값을 저장해야 한다
삽입 후 ID 반환	OUT + LAST_INSERT_ID	호출자가 결과를 받아야 한다

8 검산 쿼리

SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; SHOW INDEX FROM emp; CALL userProc1('조관우'); CALL userProc2(1970, 178); CALL userProc3('테스트값', @myValue); SELECT @myValue;

프로시저는 생성 성공보다 CALL 결과가 중요하다. 인덱스는 생성 성공보다 실행 전후 측정값과 조회 조건이 중요하다.

9 자주 하는 실수

실수	증상	해결
DELIMITER 복구 누락	다음 SQL 실행이 이상해짐	DELIMITER ; 실행
SELECT INTO가 여러 행 반환	프로시저 오류	WHERE 조건을 유일하게 조정
OUT 값을 일반 변수로 받음	값 확인 실패	@myValue 같은 사용자 변수 사용
컬럼에 연산 적용	인덱스 활용 저하	emp_no = 100000 / 1처럼 상수 쪽 계산
낮은 선택도 인덱스 과신	성능 개선이 작음	gender 같은 컬럼은 비교 측정 필수

10 측정 해석

Innodb_pages_read는 누적값이므로 한 번의 숫자만 보지 말고 실행 전과 실행 후의 차이를 비교한다. 캐시 상태에 따라 결과가 흔들릴 수 있으므로 같은 조건을 여러 번 비교할 때는 그 점을 감안한다.

패턴	해석	다음 확인
읽은 페이지 차이가 작음	인덱스 또는 캐시 효과 가능	EXPLAIN으로 계획 확인
IGNORE INDEX 후 증가	인덱스 효과가 있었음	인덱스 유지 가치 확인
gender 인덱스 효과 작음	선택도가 낮을 수 있음	분포와 LIMIT 조건 확인
컬럼 연산 후 증가	인덱스 활용 방해 가능	조건식 재작성

11 실전 적용 순서

1

문제 정의느린 조회인지, 반복 SQL인지, 출력값 반환인지 목적을 정한다.

2

작은 예제로 확인짧은 SELECT나 CALL로 동작을 먼저 검증한다.

3

객체 생성인덱스 또는 프로시저를 생성한다.

4

반복 실행여러 입력값과 조건으로 결과가 안정적인지 확인한다.

5

정리불필요한 인덱스와 테스트 프로시저를 정리한다.

12 한 줄 복습

상황	문법	핵심
프로시저 생성	CREATE PROCEDURE	DELIMITER와 END 확인
입력값 전달	IN parameter	CALL에서 값 전달
결과값 반환	OUT parameter	@변수로 받기
여러 조건 분기	CASE WHEN	END CASE 필수
인덱스 비교	IGNORE INDEX	사용/미사용 차이 확인
측정값 보기	Innodb_pages_read	전후 차이 비교

13 디버깅 체크

문제	확인할 것	처리
프로시저 생성 실패	DELIMITER, END, 괄호, 매개변수 쉼표	본문을 짧게 나누어 확인
CALL 결과가 비어 있음	WHERE 조건과 입력 매개변수 값	같은 조건을 SELECT로 먼저 실행
OUT 값이 NULL	OUT 변수 대입 위치	SET outValue 또는 SELECT INTO 확인
인덱스 효과가 작음	컬럼 선택도와 조건식 형태	EXPLAIN과 IGNORE INDEX 비교
측정값이 들쭉날쭉함	캐시와 누적 상태 변수	동일 조건 반복 비교

14 결과 판정표

결과	정상 해석	다음 확인
CALL이 한 행 이상 반환	프로시저 내부 SELECT 정상	입력값을 바꿔 재호출
CASE 결과 문자열 출력	분기와 CONCAT 정상	다른 사용자명으로 테스트
@myValue에 숫자 저장	OUT 매개변수 정상	testTBL의 마지막 id 확인
IGNORE INDEX 후 읽기 증가	인덱스 효과 확인	인덱스 유지 필요성 판단
컬럼 연산 후 읽기 증가	인덱스 활용 저하 가능	상수 쪽 연산으로 재작성

15 마무리 기준

프로시저 예제는 생성 문법보다 다양한 CALL 입력에서 결과가 안정적인지가 중요하다. 인덱스 예제는 인덱스를 만들었다는 사실보다 측정값과 실행 계획이 어떻게 달라지는지가 핵심이다.

학습용 SQL이라도 실행 전 기준값, 실행 후 결과값, 실패 케이스를 함께 남기면 나중에 같은 오류를 빠르게 복구할 수 있다.

핵심 요약

주제인덱스 성능 측정

핵심Innodb_pages_read와 IGNORE INDEX로 인덱스 사용 차이를 비교한다.

인덱스측정값은 실행 전후 차이로 판단한다.

프로시저DELIMITER, IN/OUT, CALL 흐름을 정확히 닫는다.

검산SHOW STATUS, SHOW INDEX, CALL 결과를 함께 확인한다.

Tags

#MySQL #PROCEDURE #INDEX #CASE #IN OUT #SQL

티스토리 태그 입력용

MySQL, PROCEDURE, INDEX, CASE, IN OUT, IGNORE INDEX, SQL, 데이터베이스, DB