🗄️ SQL 정리 노트
🔁 Transaction (트랜잭션)
여러 SQL 작업을 하나의 묶음으로 처리하여, 모두 성공 시 COMMIT, 하나라도 실패 시 ROLLBACK하는 기능
| 항목 | 설명 |
|---|---|
| 목적 | 데이터의 무결성 유지 및 안정성 보장 |
| 자동 커밋 해제 | SET AUTOCOMMIT = 0; |
| 트랜잭션 시작 | START TRANSACTION; |
| 최종 저장 | COMMIT; |
| 되돌리기 | ROLLBACK; |
| 임시 저장 | SAVEPOINT 저장지점명; |
| 저장 지점으로 복구 | ROLLBACK TO 저장지점명; |
💡 Java와의 비교
Spring Boot에서는 @Transactional을 사용하며, RuntimeException 발생 시 자동 ROLLBACK 처리된다.
👁️ View (뷰)
SELECT 쿼리의 결과를 임시로 저장하는 가상의 테이블
| 항목 | 설명 |
|---|---|
| 목적 | 복잡한 SQL의 재사용성 향상 및 보안/권한 관리 지원 |
| 주의점 | 일부 View는 수정 불가능 (읽기 전용) |
🧾 View 명령어
| 명령어 | 설명 |
|---|---|
CREATE (OR REPLACE) VIEW 뷰이름 AS 쿼리문; | 뷰 생성 (또는 대체 생성) |
ALTER VIEW 뷰이름 AS 새쿼리문; | 뷰 수정 |
DROP VIEW IF EXISTS 뷰이름; | 뷰 삭제 |
SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW'; | 현재 DB의 뷰 목록 조회 |
🔐 DCL (Data Control Language)
사용자 계정 및 권한을 관리하는 명령어 세트
| 단계 | 명령어 및 설명 |
|---|---|
| 1️⃣ 계정 생성 | CREATE USER '계정명'@'도메인' IDENTIFIED BY '비밀번호'; → 도메인: %(모든 외부 접속 허용), localhost(로컬 전용) |
| 2️⃣ 다른 계정 접속 | Database 창 → Connect to Database → Username: 계정명 SHOW DATABASES; 로 확인 |
| 3️⃣ 권한 부여 | GRANT 권한 ON DB명.테이블명 TO '계정명'@'도메인'; → 전체 권한: ALL PRIVILEGES |
| 4️⃣ 권한 확인 | SHOW GRANTS FOR '계정명'@'도메인'; |
| 5️⃣ 권한 취소 | REVOKE 권한 ON DB명.테이블명 FROM '계정명'@'도메인'; |
| 6️⃣ 비밀번호 수정 | ALTER USER '계정명'@'도메인' IDENTIFIED BY '새비밀번호'; |
| 7️⃣ 계정 삭제 | DROP USER '계정명'@'도메인'; |
| 8️⃣ 전체 계정 확인 | SELECT * FROM mysql.user; |
💡 뷰(View)에도 접근 권한을 부여할 수 있으며, 세밀한 권한 제어가 가능하다.
⚡ Index (인덱스)
데이터 검색 속도를 향상시키는 색인 구조
| 항목 | 설명 |
|---|---|
| 기본 인덱스 | 기본키(Primary Key)는 자동으로 인덱스를 가짐 |
| 목적 | 빠른 데이터 탐색 및 JOIN 성능 향상 |
| 주의 | 너무 많은 인덱스는 INSERT/UPDATE 성능 저하를 초래함 |
🧾 Index 명령어
| 명령어 | 설명 |
|---|---|
SHOW INDEX FROM 테이블명; | 인덱스 목록 조회 |
CREATE INDEX 인덱스명 ON 테이블명(속성명); | 단일 컬럼 인덱스 생성 |
EXPLAIN ANALYZE 쿼리문; | 쿼리 실행 계획 및 성능 측정 |
DROP INDEX 인덱스명 FROM 테이블명; | 인덱스 삭제 |
CREATE INDEX 인덱스명 ON 테이블명(속성1, 속성2); | 복합 인덱스 생성 |
복합 인덱스 주의사항
- 첫 번째 속성은 단독 인덱스로 사용 가능.
- 두 번째 속성부터는 단독 검색 불가 (복합 조합으로만 사용 가능).
🔍 JOIN과 인덱스
- JOIN 절에서 인덱스가 지정되어 있으면 실행 시간이 단축됨.
🧠 문자열 검색 인덱스 (FULLTEXT)
| 명령어 | 설명 |
|---|---|
CREATE FULLTEXT INDEX 인덱스명 ON 테이블명(컬럼명); | 자연어 기반 인덱스 생성 |
EXPLAIN ANALYZE SELECT * FROM 테이블명 WHERE MATCH(컬럼명) AGAINST('검색어'); | Fulltext 검색 성능 분석 |
📘
FULLTEXT는 CHAR, VARCHAR, TEXT, LONGTEXT 타입 컬럼에서만 사용 가능하며,MATCH()로 검색 시 번역 과정이 없어 실행 속도가 매우 빠름.
