0songha0

자주 쓰는 MariaDB 쿼리 정리 / MySQL 사용법

MySQL DDL 쿼리

https://0songha0.github.io/plan-design-db/2025-01-08-1

MySQL DQL 쿼리

https://0songha0.github.io/plan-design-db/2025-01-08-2

MySQL DML 쿼리

데이터 INSERT문

INSERT INTO 테이블명 (
	컬럼명1,
	컬럼명2
) VALUES (
	'값1',
	'값2'
);

INSERT INTO DB명.테이블명 시 에러

SQL Error [1142] [42000]: (conn=1043447) INSERT command denied to user 'DB명'@'10.0.0.4' for table '테이블명'

개발 DB명과 운영 DB명이 다른 경우, 쿼리에 DB명이 들어가면 위와 같은 에러가 날 수 있습니다.
쿼리에서 DB명을 제거하고, ‘INSERT INTO 테이블명’으로 실행하면 됩니다.

UPDATE 문

UPDATE 테이블명
SET 컬럼명 = '변경값'
WHERE 컬럼명 = '값'

조회 조건에 해당하는 row에 대해 컬럼 값을 변경합니다.

JOIN으로 조회된 행 DELETE

DELTE    
	a
FROM
	테이블1 a
INNER JOIN
	테이블2 b
ON
	b.키열 = a.키열
WHERE
	b.열 = '값'

DELETE문 실행 전에는 항상 FROM 이하 구문을 SELECT 해서 어떤 데이터를 삭제하는지 확인해야 합니다.

MySQL 설정

MySQL 타임아웃 설정 방법

SET GLOBAL max_execution_time=30000;
-- 최대 쿼리 실행 시간 (밀리초)

SET GLOBAL wait_timeout=30;
-- 비대화형 유휴 연결 타임아웃 (초)

SET GLOBAL interactive_timeout=30;
-- 대화형 터미널 세션 타임아웃 (초)

DBMS 자체 타임아웃 설정 방법입니다.
JDBC 타임아웃 설정이 더 일반적이나, 둘 다 설정하는 것이 안전합니다.

MySQL DB 타임아웃 시간 조회

SHOW VARIABLES LIKE 'max_execution_time';

DB 쿼리 타임아웃 시간을 확인하는 쿼리입니다.

MySQL DB 정보 확인

DB 버전 확인

SELECT VERSION();

DB 목록 조회

show databases

DB 서버에 속한 모든 DB 이름을 조회할 수 있습니다.

MySQL 쓰레드 조회 쿼리

활성 쓰레드 수

SHOW STATUS LIKE 'Threads_connected';

현재 연결된 클라이언트 수를 보여줍니다.

현재 실행 중 쓰레드 수

SHOW STATUS LIKE 'Threads_running';

현재 연결된 클라이언트 중, 대기하는 클라이언트를 제외하고 쿼리를 실행 중인 클라이언트 수만 보여줍니다.

최대 쓰레드 수

SHOW VARIABLES LIKE 'max_connections';

1,000개 연결 허용 시 최소 1GB 이상의 메모리가 필요하며, CPU 과부하 발생을 고려하여 적절한 설정이 필요합니다.

현재 모든 쓰레드 상태

SHOW PROCESSLIST;

각 쓰레드의 Id, User, Host, Command, State, Time, Info 정보가 포함됩니다.
어떤 쿼리가 실행 중인지, 어떤 상태인지 등을 확인할 수 있습니다.

MySQL 인덱스 관련 쿼리

테이블 인덱스 조회 쿼리

SHOW INDEX FROM 테이블명;

현재 테이블에 생성된 모든 인덱스를 확인할 수 있습니다.

Key_name PK 인덱스는 PRIMARY, 논클러스터드 인덱스는 인덱스명
Seq_in_index 인덱스에 현재 컬럼이 포함된 순서
Column_name 인덱스가 적용된 컬럼명
Cardnality 고유 값의 수
카디널리티가 높은 = 유니크한 값이 많은 컬럼에 인덱스를 걸면 쿼리 성능이 더 좋습니다.

인덱스 생성 쿼리

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬럼명2);
-- 인덱스명 예시 : idx_테이블명_컬럼명1_컬럼명2 또는 idx_테이블명_순번

테이블 잠금이 발생할 수 있으므로, 운영 중 인덱스 생성은 지양해야 합니다.
논클러스터드 인덱스 추가 시 테이블 데이터와 별도로 저장되며, 물리적인 데이터 정렬과는 무관합니다.
날짜+시간처럼 카디널리티가 높은 하나의 컬럼에는 단일 컬럼 인덱스를 생성해도 좋습니다.

인덱스 장단점
인덱스를 생성하면 조회 성능은 좋아지지만 INSERT, UPDATE, DELETE 성능이 저하됩니다.
특히, 데이터가 많아질수록 삽입 속도가 점점 느려지는 것을 확인할 수 있습니다.
B-Tree 자료구조로 정렬된 인덱스 데이터도 함께 관리하기 때문입니다.

한 테이블에 4~5개 정도의 인덱스 수가 권장됩니다.

단일 컬럼 인덱스 설정 시, 멀티 컬럼 인덱스 설정 시 성능 차이가 크지 않으면
단일 컬럼 인덱스로 인덱스를 최소화 하는 것이 효율적일 수 있습니다.

인덱스 생성하면 좋은 컬럼 ★

  • 테이블 수정 (INSERT, UPDATE, DELETE) 보다 테이블 조회 (SELECT) 작업이 많은 컬럼
  • Join 조건 또는 WHERE 절에서 자주 사용되는 컬럼
  • = (동등 조건) 으로 조회되는 컬럼
  • IN 조건에 사용되는 컬럼
  • 부등호(>, <, ≤, ≥), BETWEEN, LIKE ‘값%’ 등 범위 조건에 사용되는 컬럼
  • 선택도가 10% 이하로 낮은 컬럼 (데이터가 많이 걸러지는 컬럼)
  • 카디널리티가 높은 컬럼 (고유 값의 수가 많아 중복이 적은 컬럼)

인덱스 삭제 쿼리

DROP INDEX 인덱스명 ON 테이블명;
또는
ALTER TABLe 테이블명 DROP INDEX 인덱스명;

MySQL 정합성 관련 쿼리

PK 없는 테이블 조회 쿼리

SELECT
	t.TABLE_SCHEMA,
	t.TABLE_NAME
FROM
	information_schema.TABLES t
	LEFT JOIN information_schema.TABLE_CONSTRAINTS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
	AND t.TABLE_NAME = c.TABLE_NAME
	AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE
	c.CONSTRAINT_NAME IS NULL
	AND t.TABLE_TYPE = 'BASE TABLE'
	AND t.TABLE_SCHEMA NOT IN (
		'mysql',
		'information_schema',
		'performance_schema',
		'sys'
	);

이중화 된 네이버 클라이드 MySQL DB 사용 시에는 모든 테이블에 PK 추가가 권장됩니다.
ROW 기반 복제나 InnoDB 클러스터에서는 모든 테이블이 PK를 가져야 정상적인 복제 성능을 보장하므로,
PK가 없는 데이블이 존재하면 행 식별 과정에서 데이터 정합성이 깨져 Standby DB 복제가 중단될 수 있습니다.

최신 글