월별 정산 조회 쿼리에서 조건 하나를 '<'에서 '<='로 바꿨을 뿐인데 쿼리 시간이 급격히 증가한 사례를 정리한다.
원인은 옵티마이저의 인덱스 선택이 바뀌면서 "대량 범위 스캔"이 발생했기 때문이다.

테이블

• T_RECEIPT: 월별 정산 내역(대용량)
• T_CONTRACT: 계약 내역
• T_TARGET: 조회 대상 계약 내역(필터 역할)

쿼리

SELECT A.CONTRACT_NO
     , SUM(A.AMOUNT) AS AMOUNT
FROM T_RECEIPT A
JOIN T_CONTRACT B ON A.CONTRACT_NO = B.CONTRACT_NO
WHERE EXISTS (SELECT 1 FROM T_TARGET S WHERE S.CONTRACT_NO = A.CONTRACT_NO)
  AND A.SUM_YYMM <= '202512' -- 여기서 '<' → '<=' 변경
GROUP BY A.CONTRACT_NO;

현상

• A.SUM_YYMM < '202512'일 떄는 빠름
• A.SUM_YYMM <= '202512'로 바꾸자 엄청 느려짐

원인(실행계획을 확인해보니 인덱스 선택이 바뀜)

인덱스 선두 컬럼이 범위 조건이면 별 차이 없을것 같지만, 오히려 스캔 범위가 넓어질수록 인덱스가 오히려 느려질 수 있다.

옵티마이저가 월 선두 인덱스(SUM_YYMM)를 타면 SUM_YYMM가 '202512'라서 과거부터 당월까지 거의 전 구간이 스캔됨

즉 한 글자 변경 → 스캔 범위 증가 + 계획 변경 → "폭망"이 되었다.

해결

드라이빙을 CNTR_NO로 강제(힌트)해서 INDEX01로 타게 했다.

SELECT /*+ INDEX(A INDEX01_RECEIPT) */
       A.CONTRACT_NO
     , SUM(A.AMOUNT) AS AMOUNT
FROM T_RECEIPT A
JOIN T_CONTRACT B ON A.CONTRACT_NO = B.CONTRACT_NO
WHERE EXISTS (SELECT 1 FROM T_TARGET S WHERE S.CONTRACT_NO = A.CONTRACT_NO)
  AND A.SUM_YYMM <= '202512' -- 여기서 '<' → '<=' 변경
GROUP BY A.CONTRACT_NO;

ALTER TABLE table ADD COLUMN column char(2),  ALGORITHM=INSTANT;

🔍 예시

SHOW PROCESSLIST;

📋 결과

+--------+-----------------+--------------------+------------+---------+---------+---------------------------------+------------------------------------------------------------------+
| Id     | User            | Host               | db         | Command | Time    | State                           | Info                                                             |
+--------+-----------------+--------------------+------------+---------+---------+---------------------------------+------------------------------------------------------------------+
|      5 | event_scheduler | localhost          | NULL       | Daemon  | 1375562 | Waiting on empty queue          | NULL                                                             |
| 129524 | root            | localhost          | db         | Query   |       3 | Waiting for table metadata lock | ALTER TABLE table ADD COLUMN column char(2),  ALGORITHM=INSTANT; |
| 129525 | user            | ip                 | db         | Sleep   |       7 |                                 | NULL                                                             |
+--------+-----------------+--------------------+------------+---------+---------+---------------------------------+------------------------------------------------------------------+

🔍 예시

SELECT 
    r.THREAD_ID AS waiting_thread_id,
    r.PROCESSLIST_ID AS waiting_conn_id,
    r.PROCESSLIST_USER AS waiting_user,
    r.PROCESSLIST_HOST AS waiting_host,
    r.PROCESSLIST_COMMAND AS waiting_command,
    r.PROCESSLIST_TIME AS waiting_time,
    r.PROCESSLIST_INFO AS waiting_query,
    b.PROCESSLIST_ID AS blocking_conn_id,
    b.PROCESSLIST_INFO AS blocking_query
FROM 
    performance_schema.data_lock_waits dw
JOIN 
    performance_schema.threads r ON dw.REQUESTING_THREAD_ID = r.THREAD_ID
JOIN 
    performance_schema.threads b ON dw.BLOCKING_THREAD_ID = b.THREAD_ID;

📋 결과

Empty set (0.01 sec)

🔍 예시

SELECT 
    ps.id, ps.user, ps.host, ps.db, ps.command, ps.time, ps.state, ps.info 
FROM 
    information_schema.processlist ps 
WHERE 
    ps.state LIKE '%metadata lock%' OR ps.info LIKE '%테이블명%';

📋 결과

+--------+------+-----------+------------+---------+------+-----------+---------+
| id     | user | host      | db         | command | time | state     | info    |
+--------+------+-----------+------------+---------+------+-----------+---------+
| 129670 | root | localhost | db         | Query   |    0 | executing | query   |
+--------+------+-----------+------------+---------+------+-----------+---------+

ALGORITHM=INSTANT(MySQL 8.0.12+)

✅ ALGORITHM을 명시해야 하는 경우

1. 성능이 중요한 운영 환경에서

운영 중인 시스템에서는 ALTER로 인해 잠깐이라도 서비스 중단 또는 지연이 발생하면 안 됩니다. 이때:

• **ALGORITHM=INSTANT + LOCK=NONE**을 명시하면
  → 예측 가능한 성능 확보 가능
  → DML 차단 없이 변경 가능 여부 확실하게 판단 가능

2. 변경 작업이 어떤 방식으로 실행될지 확실히 알고 싶을 때

명시하지 않으면 MySQL은 자동으로 적절한 알고리즘을 선택 (ALGORITHM=DEFAULT)합니다. 이는 다음과 같은 문제가 발생할 수 있습니다:

• 원하지 않던 INPLACE나 COPY가 실행되어 성능 저하
• ALTER TABLE 명령이 수 초~수 분 이상 지연
• 예상치 못한 서비스 차단(locking) 발생

따라서 명시함으로써 다음을 보장할 수 있습니다:

• 성능 예측 가능성
• 잠금 수준 통제
• DDL 실행 실패 시 명확한 에러 발생

3. 배포 자동화 스크립트에서

CI/CD, Flyway, Liquibase 등 자동 배포 스크립트에서는:

• 예상하지 못한 DDL 수행이 서비스에 영향을 주지 않도록
• 명시적으로 ALGORITHM과 LOCK을 지정하는 것이 안정적

ALTER TABLE users ADD COLUMN timezone VARCHAR(50)
ALGORITHM=INSTANT LOCK=NONE;

이렇게 하면 조건이 맞지 않을 경우 DDL 실행이 에러로 중단되어 예측 불가능한 상태를 방지할 수 있습니다.

❌ 명시하지 않아도 되는 경우

• 테스트 환경에서의 실험용 DDL
• 작은 테이블에 대한 간단한 변경
• MySQL이 자동 선택해도 문제 없다고 확신할 때

이 경우는 다음처럼 간단히 작성해도 무방합니다:

ALTER TABLE logs ADD COLUMN archived TINYINT(1);

🧪 권장 사용 팁

1. 변경 전 확인:

EXPLAIN ALTER TABLE your_table ADD COLUMN new_col INT;

2. 예상 알고리즘 확인 후 명시적으로 실행:

ALTER TABLE your_table ADD COLUMN new_col INT
ALGORITHM=INSTANT LOCK=NONE;

3. 실패 시, ERROR 1846 같은 명확한 메시지로 어떤 조건이 위반되었는지 확인 가능

📌 결론

MySQL ALGORITHM 3가지

🔧 1. ALGORITHM=INSTANT

도입: MySQL 8.0.12+

✅ 장점

• 매우 빠름: 메타데이터만 수정되므로 거의 즉시 완료됨
• 데이터 복사 없음
• LOCK=NONE과 함께 사용 가능 → DML(SELECT/INSERT/UPDATE/DELETE) 모두 병행 가능
• 테이블 크기에 관계없이 작업 속도 일정

❌ 단점

• 제약 조건 많음:
  • 컬럼은 맨 끝에만 추가 가능 (AFTER 불가)
  • NOT NULL + DEFAULT 지정 불가
  • 일부 작업(인덱스 추가 등) 불가능
• 구형 버전에서는 지원되지 않음 (8.0.12 미만)

🔍 예시

ALTER TABLE users ADD COLUMN nickname VARCHAR(50) ALGORITHM=INSTANT;

⚙️ 2. ALGORITHM=INPLACE

도입: MySQL 5.6+

✅ 장점

• 많은 DDL 작업을 데이터 복사 없이 수행 가능
• 일부 인덱스 추가/삭제, 기본값 변경 등 비교적 다양한 작업 지원
• 대개 LOCK=NONE 또는 LOCK=SHARED와 함께 사용 가능 → 읽기는 병행 가능

❌ 단점

• 컬럼 변경 등의 작업에서는 일부 DML 차단 발생 가능
• 테이블 크기에 따라 시간이 다소 걸릴 수 있음
• INSTANT보다는 느림

🔍 예시

ALTER TABLE users ADD INDEX idx_email (email) ALGORITHM=INPLACE;

🛠️ 3. ALGORITHM=COPY

도입: MySQL 기본 알고리즘 (모든 버전에서 지원)

✅ 장점

• 제약 없음: 거의 모든 종류의 테이블 변경이 가능
• 오래된 MySQL 버전에서도 사용 가능

❌ 단점

• 전체 테이블을 복사 → 매우 느리고 디스크 I/O 많음
• LOCK=EXCLUSIVE만 가능 → DML 및 SELECT 모두 차단됨
• 대용량 테이블에서는 매우 위험

🔍 예시

ALTER TABLE users MODIFY COLUMN age SMALLINT ALGORITHM=COPY;

📊 정리 비교표

🧠 결론

ALGORITHM을 명시해도 느린 경우

🛠️ 해결 방법 및 조치

• 시스템 설정 조정: my.cnf 파일에서 다음과 같은 설정을 조정할 것을 제안합니다

innodb_open_files = 8000
table_open_cache = 8000
read_rnd_buffer_size = 16384
read_buffer_size = 524288

이러한 조정을 통해 테이블 오픈 캐시를 늘리고, 버퍼 크기를 조정하여 성능을 향상시킬 수 있습니다.

• 파일 디스크립터 제한 증가: 운영 체제의 ulimit 설정을 통해 열 수 있는 파일 수를 늘리는 것도 도움이 됩니다:

ulimit -n 20000

이 설정은 시스템이 동시에 열 수 있는 파일 수를 증가시켜, 테이블 오픈 시 발생하는 지연을 줄일 수 있습니다.

• 테스트 결과: 이러한 설정 변경 후, 20억 건 이상의 레코드를 가진 테이블에서 DROP TABLE 명령이 0.14초 만에 완료되었습니다.

✅ 결론 및 권장 사항

• ALGORITHM=INSTANT의 한계 인식: INSTANT 알고리즘은 메타데이터만 변경하므로 빠르게 실행되어야 하지만, 실제로는 메타데이터 락이 예상보다 오래 유지되어 전체 데이터베이스의 응답성이 저하될 수 있습니다.
• 시스템 설정 최적화: 테이블 오픈 캐시 및 버퍼 크기 등의 시스템 설정을 조정하여 성능을 향상시키고, 메타데이터 락으로 인한 지연을 최소화할 수 있습니다.
• 운영 시간 고려: 고트래픽 환경에서는 이러한 DDL 작업을 시스템 부하가 적은 시간대에 수행하는 것이 바람직합니다.
• 사전 시뮬레이션: EXPLAIN ALTER TABLE 명령을 사용하여 변경 작업의 영향을 사전에 평가하고, 예상치 못한 지연을 방지할 수 있습니다.

🔍 설정의 효과와 동작 원리

1. innodb_open_files = 8000

• 기능: InnoDB가 테이블마다 사용하는 내부 파일(.ibd) 수 제한
• 효과: 많은 수의 InnoDB 테이블이 있고 이를 동시에 열어야 할 때, 열린 파일 핸들 부족 문제 방지
• 언제 효과 있음?
  • 수천 개 이상의 테이블을 가진 시스템
  • 대용량 서버에서 병렬 처리 많을 때

2. table_open_cache = 8000

• 기능: MySQL이 캐시에 유지할 수 있는 테이블 핸들의 수
• 효과: 캐시에 열려 있는 테이블 수가 많을수록 디스크 I/O 줄어듦 → 쿼리 성능 향상
• 언제 효과 있음?
  • 많은 테이블이 자주 열리고 닫히는 환경
  • 다수의 동시 접속자가 다른 테이블을 사용하는 경우

3. read_buffer_size, read_rnd_buffer_size

• 기능: 테이블 스캔 또는 정렬된 읽기 시 사용하는 버퍼
• 효과: 정렬, 풀스캔 등의 작업 속도 향상
• 언제 효과 있음?
  • 정렬(ORDER BY), GROUP BY, LIKE '%xxx%' 등이 많을 때

4. ulimit -n 20000 (nofile)

• 기능: 리눅스에서 프로세스당 열 수 있는 파일 디스크립터 수
• MySQL 프로세스는 테이블, 인덱스, 로그 등 모든 파일마다 디스크립터를 사용
• 효과: 파일 핸들 부족으로 인한 오류(Too many open files) 방지, 속도 개선

📊 실제 효과는 언제 큰가?

즉, "큰 테이블"이 아니라 "많은 테이블" + "많은 연결 수" + "파일 핸들 제약"이 있는 시스템이라면 효과가 큽니다.

🚫 효과가 거의 없는 경우

• 테이블 수가 적고 (수십~수백 개)
• 대용량 작업이 아닌 경우
• 이미 시스템 리소스(메모리/파일 핸들 등)가 넉넉한 경우

이런 경우 설정을 높여도 눈에 띄는 성능 차이는 없을 수 있습니다.

✅ 결론

✅ 1. ulimit -n (열 수 있는 파일 수)

기본 개념:

• MySQL은 테이블, 인덱스, 로그 등 모든 파일에 대해 FD(File Descriptor)를 사용
• 너무 낮으면 "Too many open files" 에러 발생

계산 기준:

ulimit -n ≥ (table_open_cache * 2) + 연결 수 여유분

실무 권장:

• 최소 10,000 이상 설정 권장 (ulimit -n 10000)
• 대형 서비스라면 20,000~65,535 수준까지 확장 가능

설정 예:

ulimit -n 20000  # Linux shell

/etc/security/limits.conf

✅ 2. table_open_cache

기본 개념:

• MySQL이 동시에 열어둘 수 있는 테이블 수
• 세션마다 테이블을 공유하므로, 많은 동시 접속자 + 많은 테이블이 있는 경우 캐시 부족이 성능 저하 원인이 됨

계산 기준:

table_open_cache ≥ (동시 연결 수 * 평균 사용하는 테이블 수) + 25%

예:

• 200개의 동시 연결, 평균 10개의 테이블 사용
• → table_open_cache ≈ 200 * 10 * 1.25 = 2500

실무 권장:

튜닝 확인:

SHOW GLOBAL STATUS LIKE 'Open_tables';
SHOW GLOBAL STATUS LIKE 'Opened_tables';

• Opened_tables가 지속적으로 증가 → 캐시 부족

✅ 3. innodb_open_files

기본 개념:

• InnoDB가 관리할 수 있는 .ibd 파일 수 (테이블당 1개)
• file-per-table 사용 시 테이블 수만큼 필요

계산 기준:

innodb_open_files ≥ 실제 테이블 수 + 여유 20~30%

예:

• 3,000개 테이블 보유 → 최소 4,000 이상 설정

실무 권장:

• 4,000~10,000 설정 일반적
• 너무 낮으면 테이블 열기/닫기 반복으로 오버헤드 증가

✅ 4. read_buffer_size / read_rnd_buffer_size

기본 개념:

• 각 세션에서 사용하는 읽기 버퍼
• 너무 크면 메모리 소모 심함 (사용자 연결 수 고려)

실무 권장:

단, 이 값은 연결마다 할당됨 → 동시 접속 수가 많다면 과도한 설정은 OOM 위험 있음

🧪 모니터링 팁

✅ 결론

⚠ 주의: 너무 높은 값은 오히려 메모리 부족(OOM)을 유발할 수 있으므로, 서버 사양 (RAM, 연결 수 등)에 따라 조정하고, 모니터링 후 튜닝 반복하는 것이 중요합니다.

🧠 1. InnoDB 관련 핵심 설정

InnoDB는 MySQL의 기본 스토리지 엔진입니다.

🔹 innodb_buffer_pool_size

• 역할: 디스크 읽기를 줄이기 위한 캐시 역할 (InnoDB 데이터+인덱스 보관)
• 설정: 전체 메모리의 60~80% 권장

innodb_buffer_pool_size = 96G   # (128GB RAM 기준)

🔹 innodb_buffer_pool_instances

• 역할: 버퍼 풀을 나눠 동시 접근 성능 향상
• 설정: RAM이 1GB 이상일 경우 8~16 추천

innodb_buffer_pool_instances = 8

🔹 innodb_log_file_size

• 역할: InnoDB redo log 크기. 너무 작으면 쓰기 빈번, 너무 크면 복구 지연
• 설정: 512MB ~ 2GB 권장

innodb_log_file_size = 1G

🔹 innodb_flush_log_at_trx_commit

• 역할: 트랜잭션 로그 디스크 반영 방식
• 값:
  • 1: 안정성 우선 (기본값)
  • 2: 버퍼에 먼저, 일정 시간마다 플러시 (성능 우선)
• 설정 예시 (성능과 안정성 절충)

innodb_flush_log_at_trx_commit = 2

⚙️ 2. 쿼리 캐시 / 스레드 설정

🔹 query_cache_size / query_cache_type (주의: 8.0에서는 제거됨)

• MySQL 5.7 이하에서만 사용 가능

🔹 thread_cache_size

• 역할: MySQL 스레드 재사용 캐시
• 설정: max_connections의 5~10% 권장

thread_cache_size = 200

📋 3. 로그 및 모니터링 관련 옵션

🔹 slow_query_log

• 느린 쿼리 추적

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

🔹 log_error

• 에러 로그 위치 설정

log_error = /var/log/mysql/error.log

🚀 4. 접속 처리 및 타임아웃

🔹 max_connections

• 동시 접속자 수에 맞게 설정 (예: 2,000)

max_connections = 2000

🔹 wait_timeout, interactive_timeout

• 유휴 세션의 연결 유지 시간을 제한
• 과도한 세션 유지 방지

wait_timeout = 600
interactive_timeout = 600

📦 5. 기타 실무에서 자주 쓰는 옵션

🔹 skip-name-resolve

• DNS 역조회 방지 → 접속 속도 향상

skip-name-resolve

🔹 sql_mode

• 엄격한 모드 설정 가능 (STRICT, NO_ZERO_DATE, 등)

sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

LPAD() 함수를 사용하자

일반적으로 숫자형 텍스트를 ORDER BY문에 추가하면 '10A'가 '2A'보다 앞서 조회된다. '2A', '10A'순으로 정렬을 하고 싶으면 LPAD() 함수를 사용하면된다. 남은 자리만큼 0을 추가하여 '0002A', '0010A'로 정렬하면 '2A'가 앞서 조회된다.  

JDBC-8038:Expression is not in a GROUP BY clause. (Tibero)

GROUP BY절에 포함되지 않은 컬럼을 SELECT절에서 집계 함수를 사용하지 않고 사용했을 경우 발생하는 에러이다.

• 컬렴명 오타 확인
• CASE문을 GROUP BY 했을 경우 SELECT절에서 사용한 CASE문을 그대로 GROUP BY절에 작성해야한다.
• mybatis를 사용하는 경우 파라미터가 GROUP BY절에 포함되면 안된다.   

선언부(반환되는 데이터가 여러개일 경우)

CREATE OR REPLACE FUNCTION USER.FUNCTION_NAME (
    /* 함수 호출 시 받은 인자 */	
    P_LIST VARCHAR2,
    P_DEL VARCHAR2
) RETURN SPLIT_TBL PIPELINED
IS
    /* 함수에서 사용할 지역 변수 */	
    L_IDX		PLS_INTEGER;
    L_LIST		VARCHAR2(1000) := P_LIST;
    L_VALUE 	VARCHAR2(1000);

• PipelinedTable 함수를 사용해서 건별로 처리 후 추출

실행부

BEGIN
  LOOP
       L_IDX := INSTR(L_LIST,P_DEL);
       IF L_IDX > 0 THEN
           PIPE ROW(SUBSTR(L_LIST,1,L_IDX-1));
           L_LIST := SUBSTR(L_LIST,L_IDX+LENGTH(P_DEL));
       ELSE
           PIPE ROW(L_LIST);
           EXIT;
       END IF;
  END LOOP;
  RETURN;
END;

• P_LIST 문자열을 P_DEL 구분자로 split해서 추출 후 반환

반환 타입

CREATE TYPE USER."SPLIT_TBL" AS TABLE OF VARCHAR2(1000);

선언부

CREATE OR REPLACE PROCEDURE USER.PROCEDURE_NAME (
    /* 프로시저 호출 시 받은 인자 */
    PARAM1	IN VARCHAR2,
    PARAM2	IN NUMBER
    PARAM3	IN TABLE_NAME.TABLE_COLUMN%TYPE,
    PARAM4	IN VARCHAR2:=TO_CHAR(SYSDATE, 'YYYYMMDD')
    PARAM5	OUT INTEGER
)
IS
    /* 프로시저에서 사용할 지역 변수 */
    VAR1	IN VARCHAR(20);
    VAR2	IN INTEGER;
    VAR3	IN INTEGER;
    
    /* 조회한 결과의 메모리 영역을 가리키는 일종의 포인터 */
    CURSOR CUR1 IS
    	SELECT ADD_MONTHS(SYSDATE, -LEVEL) AS MON_LIST
        FROM DUAL
        CONNECT BY LEVEL < 10;

• IN: 프로시저 호출 시 받은 데이터
• OUT: 프로시저 종료 시 반환될 데이터(map to push)
• CURSOR: 조회한 데이터를 참조

실행부

BEGIN
	BEGIN
		SELECT 101
		INTO VAR1
		FROM TALBE
		WHERE CON1 > 0;
	EXCEPTION
    	WHEN NO_DATA_FOUND THEN
        	PARAM5 := 0;
	END;
    
    /* IF문 */
    IF PARAM2 > 0 THEN	
        PARAM5 := 1;
    ELSIF PARAM2 = 0 THEN
    	PARAM5 := 0;
    ELSE	
    	PARAM5 := -1;
    END IF;
    
    /* 커서가 가리키는 데이터 순회 */
    FOR CUR IN CUR1 LOOP
    	SELECT CUR.MON_LIST
        FROM DUAL;
    END LOOP;

    /* 예외 핸들러 */
    EXCEPTION
    	WHEN OTHERS THEN
            ROLLBACK;
            PARAM5 := -1;
            RAISE_APPLICATION_ERROR(ERROR_CODE, ERROR_MSG);
END;

• INTO: 조회한 데이터를 변수에 할당, 이때 조회한 데이터가 없으면 에러가 발생하기 때문에 예외 처리를 해줘야 한다.
• 커서를 이용한 반목문 및 INSERT, UPDATE, DELETE 등 수행할 작업을 작성
• 호출날짜, 에러 로그 등을 저장하는 작업 필수