인덱스 기본: B-Tree/복합 인덱스/쿼리 최적화 감각

-- ✅ 인덱스 적합:
-- - 대량의 데이터 (수만 행 이상)
-- - 높은 선택도 (Selectivity) - 고유한 값이 많음
-- - WHERE, JOIN, ORDER BY에 자주 사용되는 컬럼
SELECT * FROM orders WHERE customer_id = 12345;

-- ❌ 인덱스 부적합:
-- - 소량의 데이터 (수천 행 이하)
-- - 낮은 선택도 - 고유 값이 적음 (예: 성별, 불린)
-- - 자주 변경되는 컬럼
SELECT * FROM users WHERE is_active = true;  -- 50% 이상이 true면 비효율

-- Leaf Node가 연결 리스트 구조
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

-- B-Tree 탐색:
1. '2025-01-01' 시작 위치 찾기 (O(log n))
2. Leaf Node를 순차 탐색 (O(k), k = 결과 수)

-- ORDER BY 절에 인덱스 활용
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 인덱스가 이미 정렬되어 있어서 추가 정렬 불필요

-- InnoDB는 항상 Clustered Index 사용
CREATE TABLE users (
    id BIGINT PRIMARY KEY,      -- Clustered Index (자동)
    email VARCHAR(255) UNIQUE,  -- Non-Clustered Index
    name VARCHAR(100)
);

-- PK가 없으면 첫 번째 UNIQUE NOT NULL 컬럼 사용
-- 그것도 없으면 숨겨진 ROW_ID 사용

-- Non-Clustered Index 생성
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(name, age);  -- 복합 인덱스

-- UNIQUE 제약 조건은 자동으로 인덱스 생성
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,  -- Unique Index 자동 생성
    phone VARCHAR(20)
);

-- 명시적 Unique Index 생성
CREATE UNIQUE INDEX idx_phone ON users(phone);

-- 중복 값 삽입 시 에러
INSERT INTO users (id, email) VALUES (1, 'a@example.com');
INSERT INTO users (id, email) VALUES (2, 'a@example.com');  -- ERROR: Duplicate entry

-- 여러 컬럼을 하나의 인덱스로 구성
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 인덱스 구조 (Leftmost Prefix Rule)
┌─────────────────────────────────┐
│ name    │ age │ city   │ PK(id)│
├─────────────────────────────────┤
│ Alice   │ 25  │ Seoul  │   1   │
│ Alice   │ 28  │ Busan  │   5   │
│ Bob     │ 30  │ Seoul  │   2   │
│ Charlie │ 28  │ Seoul  │   3   │
└─────────────────────────────────┘

✅ 인덱스 사용 가능:
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users WHERE name = 'Alice' AND age = 25;
SELECT * FROM users WHERE name = 'Alice' AND age = 25 AND city = 'Seoul';

❌ 인덱스 사용 불가:
SELECT * FROM users WHERE age = 25;  -- name이 없음
SELECT * FROM users WHERE city = 'Seoul';  -- name, age가 없음

⚠️ 일부 사용:
SELECT * FROM users WHERE name = 'Alice' AND city = 'Seoul';
-- name만 인덱스 사용, city는 필터링

-- 예시: 주문 테이블
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

-- customer_id: 10,000 고유 값
-- order_date: 365 고유 값
-- → customer_id를 앞에 배치 (선택도 높음)

-- 자주 사용되는 쿼리:
SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2025-01-01';
-- ✅ 인덱스 활용

-- 쿼리에 필요한 모든 컬럼을 인덱스에 포함
CREATE INDEX idx_name_email ON users(name, email);

-- ✅ Covering Index (인덱스만으로 쿼리 완료)
SELECT name, email FROM users WHERE name = 'Alice';
-- Leaf Node에 name, email이 모두 있어서 실제 데이터 접근 불필요

-- ❌ Non-Covering (실제 데이터 접근 필요)
SELECT name, email, age FROM users WHERE name = 'Alice';
-- age는 인덱스에 없어서 Bookmark Lookup 발생

-- Covering Index
EXPLAIN SELECT name, email FROM users WHERE name = 'Alice';

-- Extra: Using index
-- 인덱스만 읽음 (빠름)

-- Non-Covering
EXPLAIN SELECT name, email, age FROM users WHERE name = 'Alice';

-- Extra: Using where
-- 인덱스 + 테이블 읽음 (느림)

-- ✅ 인덱스 사용
SELECT * FROM orders WHERE customer_id = 12345;
CREATE INDEX idx_customer_id ON orders(customer_id);

-- ✅ 범위 검색
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';
CREATE INDEX idx_order_date ON orders(order_date);

-- ❌ 함수 사용 시 인덱스 무효화
SELECT * FROM orders WHERE DATE(order_date) = '2025-01-01';
-- → 인덱스 사용 안 됨 (order_date에 함수 적용)

-- ✅ 수정: 범위 조건으로 변경
SELECT * FROM orders
WHERE order_date >= '2025-01-01 00:00:00'
  AND order_date < '2025-01-02 00:00:00';

-- ❌ LIKE '%keyword%' (인덱스 무효화)
SELECT * FROM products WHERE name LIKE '%phone%';

-- ✅ LIKE 'keyword%' (인덱스 사용)
SELECT * FROM products WHERE name LIKE 'phone%';

-- Full-Text Search가 필요한 경우:
CREATE FULLTEXT INDEX idx_name ON products(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

-- ✅ JOIN 컬럼에 인덱스
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_product_id ON order_items(product_id);

SELECT o.id, c.name, p.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2025-01-01';

-- 인덱스 전략:
1. orders.order_date (WHERE 절)
2. orders.customer_id (JOIN)
3. order_items.order_id (JOIN)
4. order_items.product_id (JOIN)

-- ✅ 인덱스 활용 정렬
CREATE INDEX idx_created_at ON posts(created_at DESC);

SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;
-- Extra: Using index (정렬 불필요)

-- ❌ 인덱스와 정렬 방향 불일치
SELECT * FROM posts ORDER BY created_at ASC;
-- Extra: Using filesort (정렬 발생)

-- ✅ 복합 인덱스 정렬
CREATE INDEX idx_user_date ON posts(user_id, created_at DESC);

SELECT * FROM posts WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;
-- ✅ 인덱스 활용

-- 카디널리티 확인
SELECT COUNT(DISTINCT column_name) AS cardinality,
       COUNT(*) AS total_rows,
       COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 예시:
SELECT COUNT(DISTINCT email) AS cardinality,
       COUNT(*) AS total_rows,
       COUNT(DISTINCT email) / COUNT(*) AS selectivity
FROM users;

-- 결과:
-- cardinality: 1,000,000
-- total_rows: 1,000,000
-- selectivity: 1.0000

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

-- 출력 결과:
+----+-------------+--------+------+------------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys    | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_customer_id  | idx_... | 8       | const |   10 | NULL  |
+----+-------------+--------+------+------------------+---------+---------+-------+------+-------+

주요 컬럼:
- type: 조인 타입 (성능 지표)
- key: 실제 사용된 인덱스
- rows: 예상 스캔 행 수
- Extra: 추가 정보

-- const (최고 성능)
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const (PK로 정확히 1개 행)

-- ref (좋음)
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
-- type: ref (Non-Unique 인덱스로 여러 행)

-- range (보통)
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';
-- type: range (범위 스캔)

-- ALL (최악)
EXPLAIN SELECT * FROM users WHERE age > 25;
-- type: ALL (인덱스 없으면 전체 스캔)

-- ✅ Using index (최고)
EXPLAIN SELECT name FROM users WHERE name = 'Alice';
-- Covering Index: 인덱스만 읽음

-- ✅ Using where (좋음)
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
-- 인덱스 사용 후 WHERE 필터링

-- ⚠️ Using filesort (주의)
EXPLAIN SELECT * FROM users ORDER BY age;
-- 정렬 발생 (인덱스 없음)

-- ⚠️ Using temporary (주의)
EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name;
-- 임시 테이블 생성

-- ❌ Using where; Using filesort (나쁨)
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 ORDER BY amount;
-- 필터링 후 정렬 (인덱스 필요)

-- ❌ 성능 문제 쿼리
EXPLAIN SELECT o.id, o.total_amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING'
  AND o.order_date >= '2025-01-01'
ORDER BY o.total_amount DESC
LIMIT 10;

-- 실행 계획 (Before):
+----+-------------+-------+------+---------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | key     | ref  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+---------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | o     | ALL  | NULL    | NULL | NULL    | NULL | 500000 | Using where; Using filesort |
|  1 | SIMPLE      | c     | ref  | PRIMARY | o... | 8       | ...  |      1 | NULL                        |
+----+-------------+-------+------+---------+------+---------+------+--------+-----------------------------+

문제:
1. orders 테이블: type = ALL (전체 스캔)
2. Extra: Using filesort (정렬 발생)
3. rows: 500,000 (너무 많음)

-- ✅ 인덱스 추가
CREATE INDEX idx_status_date_amount ON orders(status, order_date, total_amount DESC);

-- 실행 계획 (After):
+----+-------------+-------+-------+-------------------------+--------+---------+------+------+--------------+
| id | select_type | table | type  | key                     | ref    | key_len | ref  | rows | Extra        |
+----+-------------+-------+-------+-------------------------+--------+---------+------+------+--------------+
|  1 | SIMPLE      | o     | range | idx_status_date_amount  | NULL   | 13      | NULL |  100 | Using where  |
|  1 | SIMPLE      | c     | ref   | PRIMARY                 | o...   | 8       | ...  |    1 | NULL         |
+----+-------------+-------+-------+-------------------------+--------+---------+------+------+--------------+

개선:
1. type: ALL → range (인덱스 범위 스캔)
2. Extra: Using filesort 제거 (인덱스 정렬)
3. rows: 500,000 → 100 (5,000배 감소)

성능: 3.5초 → 0.02초 (175배 향상)

-- ❌ OFFSET이 큰 경우 느림
SELECT * FROM posts ORDER BY created_at DESC LIMIT 100 OFFSET 100000;

-- OFFSET 100000:
-- 1. 100,000개 행 스캔
-- 2. 100,000개 행 버림
-- 3. 100개 행 반환
-- 비효율적!

-- ✅ 커서 기반 페이징 (Seek Method)
SELECT * FROM posts
WHERE created_at < '2025-01-15 10:00:00'  -- 이전 페이지의 마지막 created_at
ORDER BY created_at DESC
LIMIT 100;

-- 인덱스:
CREATE INDEX idx_created_at ON posts(created_at DESC);

-- 성능 비교:
-- OFFSET 100000: 2.5초
-- 커서 기반: 0.01초 (250배 빠름)

-- ❌ 전체 COUNT (느림)
SELECT COUNT(*) FROM orders WHERE customer_id = 12345;

-- 대량 데이터에서 COUNT(*)는 전체 스캔 필요

-- ✅ 해결책 1: 근사치 사용
SELECT table_rows
FROM information_schema.tables
WHERE table_name = 'orders';

-- ✅ 해결책 2: 캐싱
-- Redis에 count 저장, 주기적 갱신

-- ✅ 해결책 3: 분리된 카운터 테이블
CREATE TABLE customer_order_counts (
    customer_id BIGINT PRIMARY KEY,
    order_count INT DEFAULT 0
);

-- 주문 생성 시 카운터 증가 (트랜잭션)
BEGIN;
INSERT INTO orders (...) VALUES (...);
INSERT INTO customer_order_counts (customer_id, order_count) VALUES (12345, 1)
    ON DUPLICATE KEY UPDATE order_count = order_count + 1;
COMMIT;

-- ❌ Subquery (느림)
SELECT *
FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE is_active = true
);

-- Subquery는 외부 쿼리마다 실행될 수 있음 (Dependent Subquery)

-- ✅ JOIN으로 변환
SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.is_active = true;

-- 또는 EXISTS 사용
SELECT *
FROM products p
WHERE EXISTS (
    SELECT 1 FROM categories c
    WHERE c.id = p.category_id AND c.is_active = true
);

-- 성능 비교:
-- Subquery: 1.2초
-- JOIN: 0.05초 (24배 빠름)

-- ❌ OR 조건 (인덱스 비효율)
SELECT * FROM users WHERE name = 'Alice' OR email = 'alice@example.com';

-- 인덱스: idx_name, idx_email
-- → 두 인덱스 모두 스캔 후 UNION (비효율)

-- ✅ UNION ALL로 분리
SELECT * FROM users WHERE name = 'Alice'
UNION ALL
SELECT * FROM users WHERE email = 'alice@example.com' AND name != 'Alice';

-- 각 쿼리가 독립적인 인덱스 사용

-- ✅ 또는 복합 인덱스
CREATE INDEX idx_name_email ON users(name, email);

-- MySQL 8.0+
SELECT
    object_schema AS database_name,
    object_name AS table_name,
    index_name,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND COUNT_READ = 0
  AND COUNT_FETCH = 0
ORDER BY object_schema, object_name;

-- 사용되지 않는 인덱스 삭제
DROP INDEX unused_index ON table_name;

-- 중복 인덱스 예시:
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_name_age ON users(name, age);

-- idx_name은 idx_name_age와 중복
-- (name만 검색할 때 idx_name_age도 사용 가능)

-- 중복 인덱스 조회:
SELECT
    table_name,
    GROUP_CONCAT(index_name) AS duplicate_indexes
FROM information_schema.statistics
WHERE table_schema = 'your_database'
GROUP BY table_name, column_name
HAVING COUNT(*) > 1;

-- 인덱스 조각화 확인
SHOW TABLE STATUS LIKE 'orders';

-- Data_free가 크면 조각화됨

-- 테이블 최적화 (인덱스 재구성)
OPTIMIZE TABLE orders;

-- 또는 테이블 재생성
ALTER TABLE orders ENGINE=InnoDB;

-- my.cnf 설정
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 1초 이상 쿼리 기록

-- 슬로우 쿼리 분석 도구
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 출력:
Count: 1250  Time=3.50s (4375s)  Lock=0.00s (0s)  Rows=100.0 (125000)
  SELECT * FROM orders WHERE status = 'S' ORDER BY created_at DESC LIMIT N;

-- 해결책: status, created_at에 복합 인덱스 생성