Database 7일차

🔨 단계별 실습 요약

1. DB 생성 (ERD 기반)

sql
복사편집
CREATE DATABASE web;
USE web;

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(100)
);

CREATE TABLE sections (
  section_id INT PRIMARY KEY,
  section_name VARCHAR(100),
  course_id INT,
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE lectures (
  lecture_id INT PRIMARY KEY AUTO_INCREMENT,
  lecture_name VARCHAR(100),
  section_id INT,
  FOREIGN KEY (section_id) REFERENCES sections(section_id)
);

---

2. front.html 만들기

html
복사편집
<!-- /var/www/web/front.html -->
<h1>이니셜 front server</h1>

접속 확인:

👉 http://XXX.XXX.XXX.XX/front.html

---

3. back.jsp 만들기 (서버 IP 확인)

jsp
복사편집
<%@ page language="java" %>
<p>Server IP: <%= request.getLocalAddr() %></p>

👉 http://XXX.XXX.XXX.XX/api/back.jsp

→ 여러 번 새로고침하면 XX, XX번 톰캣 IP가 번갈아 나와야 함 (nginx 라운드로빈 확인)

---

4. insert.html 만들기

/insert.html에서 course, section, lecture 각각 저장 가능하게 구성

→ <form>의 action="/api/insert.jsp"로 POST 요청

---

5. insert.jsp (Tomcat XX/XX 공통)

DB 연결: 마스터(XX번) 사용

jsp
복사편집
String dbURL = "jdbc:mariadb://XXX.XXX.XXX.XX:3306/web";

액션값(course, section, lecture)에 따라 insert 쿼리 실행

---

6. select.jsp (Tomcat XX번 중심)

DB 연결: 슬레이브(XX번) 사용

jsp
복사편집
String dbURL = "jdbc:mariadb://XXX.XXX.XXX.XX:3306/web";

JOIN 조회 SQL

sql
복사편집
SELECT
  c.course_id, c.course_name,
  s.section_id, s.section_name,
  l.lecture_name
FROM courses c
LEFT JOIN sections s ON c.course_id = s.course_id
LEFT JOIN lectures l ON s.section_id = l.section_id
ORDER BY c.course_id, s.section_id, l.lecture_id;

---

7. nginx 설정

/etc/nginx/sites-enabled/web

nginx
복사편집
upstream tomcat_backend {
  server XXX.XXX.XXX.XX:8080;
  server XXX.XXX.XXX.XX:8080;
}

server {
  listen 80;
  root /var/www/web;

  location / {
    try_files $uri $uri/ =404;
  }

  location /api {
    rewrite ^/api(/.*)$ $1 break;
    proxy_pass http://tomcat_backend;
  }
}

🔁 systemctl restart nginx

---

8. 마지막 점검

항목 확인

insert.jsp로 값 입력 시 master DB에 저장됨
slave DB에서도 즉시 반영되는지 확인 (select.jsp)
back.jsp 새로고침하면 IP 번갈아 나오는지 확인

---

📌 전체 흐름 요약

text
복사편집
[사용자] -- insert.html
         → [insert.jsp] → [마스터 DB 저장]

[사용자] -- select.jsp
         → [슬레이브 DB 조회]

→ 마스터/슬레이브 간 레플리케이션 정상 동작 확인
→ Nginx 로드밸런싱 확인 (back.jsp IP 번갈아 나옴)

✅ 실습 개요

• INDEX: 검색 속도를 높이기 위한 데이터 구조
• EXPLAIN: SQL 쿼리가 실제로 어떻게 실행되는지 보여줌
• PROFILE: 쿼리 실행 시간 분석 (각 단계별 소요 시간)

---

✅ 1. 테이블 생성 및 테스트 데이터 삽입

sql
복사편집
-- 기존 post 테이블 삭제
DROP TABLE IF EXISTS post;

-- post 테이블 생성
CREATE TABLE post (
    idx INT AUTO_INCREMENT PRIMARY KEY,         -- 게시글 ID
    writerIdx INT,                               -- 작성자 ID (member 테이블 참조 가정)
    contents VARCHAR(100),                       -- 게시글 내용
    createdAt DATETIME                           -- 생성일
);

-- 1000개 더미 데이터 삽입
INSERT INTO post (writerIdx, contents, createdAt)
SELECT
    FLOOR(1 + RAND() * 1000),                             -- 1~1000 사이 임의의 작성자 ID
    CONCAT('이것은 테스트 게시글 내용입니다 - ', n),      -- 내용 예시
    NOW()
FROM (
    WITH RECURSIVE seq AS (
        SELECT 1 AS n
        UNION ALL
        SELECT n + 1 FROM seq WHERE n < 1000
    )
    SELECT * FROM seq
) AS temp;

---

✅ 2. 성능 분석 활성화 (PROFILE)

sql
복사편집
-- 쿼리 프로파일링 기능 활성화
SET profiling = 1;

---

✅ 3. 인덱스 없이 쿼리 실행 + PROFILE 확인

sql
복사편집
-- 인덱스 없는 상태에서 특정 작성자의 게시글 조회
SELECT * FROM post WHERE writerIdx = 777;

-- 최근 실행된 쿼리 목록 및 실행 시간 확인
SHOW PROFILES;

-- 첫 번째 쿼리의 상세 실행 단계 시간 확인
SHOW PROFILE FOR QUERY 1;

---

✅ 4. EXPLAIN으로 실행 계획 확인 (인덱스 없는 상태)

sql
복사편집
-- EXPLAIN으로 실행 계획 확인
EXPLAIN SELECT * FROM post WHERE writerIdx = 777;

-- 확인 포인트:
-- type: ALL → 테이블 전체 검색 (느림)
-- rows: 1000 → 예상 스캔 행 수 많음
-- key: NULL → 인덱스 미사용

---

✅ 5. 인덱스 생성

sql
복사편집
-- writerIdx 컬럼에 인덱스 생성
CREATE INDEX idx_writerIdx ON post(writerIdx);

---

✅ 6. 인덱스 적용 후 쿼리 재실행 + PROFILE 비교

sql
복사편집
-- 동일한 쿼리 재실행
SELECT * FROM post WHERE writerIdx = 777;

-- 새 쿼리 실행 시간 확인
SHOW PROFILES;

-- 두 번째 쿼리의 상세 시간 확인
SHOW PROFILE FOR QUERY 2;

---

✅ 7. EXPLAIN 재확인 (인덱스 적용 후)

sql
복사편집
EXPLAIN SELECT * FROM post WHERE writerIdx = 777;

-- 확인 포인트:
-- type: ref → 인덱스를 활용한 검색 (빠름)
-- key: idx_writerIdx → 사용된 인덱스 이름
-- rows: 적음 → 적은 수의 행만 검색

---

✅ 요약 정리

개념 설명

INDEX	특정 컬럼에 빠른 검색을 위해 데이터 구조 추가 (writerIdx에 생성)
EXPLAIN	쿼리 실행 전에 MySQL이 어떤 방식으로 데이터를 찾는지 계획을 보여줌
PROFILE	실제로 쿼리가 실행되면서 각 단계마다 걸린 시간 분석 가능

• EXPLAIN→ 어떤 인덱스를 사용할지, 몇 개 행을 읽을지, 테이블을 어떻게 탐색할지 미리 알 수 있습니다.
• → 쿼리를 실행하기 전에 MySQL이 어떻게 데이터를 읽고 처리할 계획인지, 즉 실행 계획을 보여주는 명령어입니다.
• PROFILE→ 쿼리 실행의 병목 구간이나 성능 문제를 구체적으로 분석할 수 있습니다.
• → 쿼리를 실제 실행한 후에 각 실행 단계별로 얼마나 시간이 걸렸는지 상세하게 보여주는 명령어입니다.

쉽게 말해,

EXPLAIN은 계획을 보여주고, PROFILE은 실행 결과의 시간 분석을 보여줍니다.

INDEX (인덱스)

• 무엇인가?책 뒤에 있는 ‘찾아보기’ 목록과 비슷한 역할을 합니다.
• 테이블에서 데이터를 빠르게 찾도록 도와주는 특별한 데이터 구조입니다.
• 왜 필요한가?
• 테이블이 커질수록 원하는 데이터를 찾는 데 시간이 오래 걸리는데, 인덱스가 있으면 검색 속도가 훨씬 빨라집니다.
• 어떻게 작동하나?
• 특정 컬럼에 대해 별도의 정렬된 자료구조(B-트리 등)를 만들어서 검색할 때 전체 테이블을 훑지 않고도 바로 위치를 찾을 수 있습니다.
• 주의점
  • 인덱스가 많으면 데이터 삽입/수정/삭제 시에 인덱스도 같이 갱신해야 하므로 오버헤드가 발생합니다.
  • 모든 컬럼에 인덱스를 만드는 것은 오히려 성능 저하를 초래할 수 있습니다.

---

한 줄 요약

인덱스는 검색 속도를 높여주는 ‘빠른 찾기’용 데이터 구조입니다.