-- 기본 인덱스 생성
CREATE INDEX idx_email ON users(email);

-- 또는 테이블 생성 시
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    INDEX idx_email (email)
);

-- 복합 인덱스 생성
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- 이 인덱스가 효과 있는 쿼리들:
SELECT * FROM users WHERE last_name = '김';              -- ✅ 사용 가능
SELECT * FROM users WHERE last_name = '김' AND first_name = '철수'; -- ✅ 사용 가능
SELECT * FROM users WHERE last_name = '김' AND age = 25; -- ✅ 부분 사용

-- 이 인덱스가 효과 없는 쿼리들:
SELECT * FROM users WHERE first_name = '철수';           -- ❌ 사용 불가
SELECT * FROM users WHERE age = 25;                     -- ❌ 사용 불가

-- 유니크 인덱스 (중복값 불허)
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- UNIQUE 제약조건도 내부적으로 유니크 인덱스 생성
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

-- 예시: 주문 테이블에서 user_id별 최근 주문 조회
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- 이 쿼리는 인덱스만으로 완전히 처리 가능! (테이블 접근 불필요)
SELECT user_id, status, created_at 
FROM orders 
WHERE user_id = 123 AND status = 'completed'
ORDER BY created_at DESC;

-- 전문 검색 인덱스 생성
CREATE FULLTEXT INDEX idx_ft_content ON articles(title, content);

-- MATCH ... AGAINST 문법으로 검색
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL 인덱스' IN BOOLEAN MODE);

-- IN BOOLEAN MODE에서 쓸 수 있는 연산자
-- +단어: 반드시 포함
-- -단어: 반드시 제외
-- *: 와일드카드
-- "문구": 정확한 문구 검색

-- MySQL 8.0부터 지원하는 함수 기반 인덱스
CREATE INDEX idx_lower_email ON users((LOWER(email)));

-- 이제 이 쿼리가 인덱스를 탈 수 있어!
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- JSON 컬럼에도 적용 가능
CREATE INDEX idx_json_name ON users((JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))));

-- 기본 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 더 자세한 정보 (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- JSON 형식으로 출력
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123;

-- 분석할 쿼리
EXPLAIN SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- 결과 예시 (문제 있는 경우)
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | 500K | Using where; Using filesort |
|  1 | SIMPLE      | u     | ref  | PRIMARY       | PRIMARY | 8    |    1 |                             |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+

-- orders 테이블에서 type=ALL, Using filesort 발생 → 개선 필요!

-- 해결책: 복합 인덱스 추가
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 개선 후 EXPLAIN 결과
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys    | key              | key_len | rows | Extra               |
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | o     | range | idx_status_created | idx_status_created | 1030 |  200 | Using index condition |
|  1 | SIMPLE      | u     | const | PRIMARY          | PRIMARY          | 8       |    1 |                     |
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+

-- 슬로우 쿼리 로그 설정 (my.cnf 또는 동적 설정)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- 1초 이상 걸리는 쿼리 기록
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 인덱스 미사용 쿼리도 기록

-- 현재 설정 확인
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- mysqldumpslow으로 슬로우 쿼리 분석
# 가장 느린 쿼리 10개
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 가장 많이 발생한 쿼리 10개
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 특정 패턴 검색
mysqldumpslow -s t -t 5 -g "SELECT.*orders" /var/log/mysql/slow.log

-- 가장 많이 실행된 쿼리 TOP 10
SELECT 
    DIGEST_TEXT,
    COUNT_STAR AS 실행횟수,
    AVG_TIMER_WAIT/1000000000 AS 평균실행시간_초,
    MAX_TIMER_WAIT/1000000000 AS 최대실행시간_초,
    SUM_ROWS_EXAMINED AS 총검사행수
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- 인덱스 사용 현황 확인
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_READ DESC;

-- 컬럼별 카디널리티 확인
SELECT 
    COUNT(DISTINCT gender) AS gender_cardinality,
    COUNT(DISTINCT age) AS age_cardinality,
    COUNT(DISTINCT city) AS city_cardinality,
    COUNT(DISTINCT email) AS email_cardinality,
    COUNT(*) AS total_rows
FROM users;

-- 인덱스 카디널리티 확인
SHOW INDEX FROM users;
-- Cardinality 컬럼 확인 (통계 기반 추정값)

-- 실전 예시: 이커머스 주문 조회
-- 쿼리 패턴:
-- 1. 특정 사용자의 특정 상태 주문을 최신순으로 조회
-- 2. SELECT에서 id, user_id, status, total_amount, created_at 사용

SELECT id, user_id, status, total_amount, created_at
FROM orders
WHERE user_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 20;

-- 최적 인덱스 설계:
-- user_id (등호 조건, 카디널리티 높음)
-- status (등호 조건)
-- created_at (ORDER BY)
-- total_amount (SELECT 커버링)
CREATE INDEX idx_orders_optimal 
ON orders(user_id, status, created_at, total_amount);

-- EXPLAIN 확인 시 Extra: Using index 가 뜨면 완벽!

-- ❌ 1. 컬럼에 함수 적용
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- ✅ 개선
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ❌ 2. 컬럼에 연산 적용
SELECT * FROM products WHERE price * 1.1 > 10000;
-- ✅ 개선
SELECT * FROM products WHERE price > 10000 / 1.1;

-- ❌ 3. 앞쪽 와일드카드 LIKE
SELECT * FROM users WHERE name LIKE '%철수';
-- ✅ 개선 (뒤쪽 와일드카드는 인덱스 사용 가능)
SELECT * FROM users WHERE name LIKE '김%';

-- ❌ 4. 암묵적 타입 변환
-- phone 컬럼이 VARCHAR인데 숫자로 비교
SELECT * FROM users WHERE phone = 01012345678;
-- ✅ 개선
SELECT * FROM users WHERE phone = '01012345678';

-- ❌ 5. OR 조건 (다른 컬럼)
SELECT * FROM users WHERE name = '김철수' OR email = 'test@test.com';
-- ✅ 개선 (각각 인덱스가 있다면 UNION 사용)
SELECT * FROM users WHERE name = '김철수'
UNION
SELECT * FROM users WHERE email = 'test@test.com';

-- ❌ 6. NOT IN, NOT EXISTS (경우에 따라 다름)
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);
-- ✅ 개선 (LEFT JOIN 활용)
SELECT u.* FROM users u
LEFT JOIN banned_users b ON u.id = b.user_id
WHERE b.user_id IS NULL;

-- 특정 인덱스 강제 사용
SELECT * FROM orders FORCE INDEX (idx_status_created)
WHERE status = 'pending' AND created_at > '2024-01-01';

-- 특정 인덱스 사용 권장 (옵티마이저가 무시할 수 있음)
SELECT * FROM orders USE INDEX (idx_status_created)
WHERE status = 'pending';

-- 특정 인덱스 사용 금지
SELECT * FROM orders IGNORE INDEX (idx_old_index)
WHERE status = 'pending';

-- 주의: 인덱스 힌트는 최후의 수단! 
-- 옵티마이저를 믿되, 통계가 잘못됐을 때만 사용해.

-- 테이블 통계 업데이트 (옵티마이저가 올바른 실행계획 수립하도록)
ANALYZE TABLE orders;
ANALYZE TABLE users;

-- 여러 테이블 한번에
ANALYZE TABLE orders, users, products;

-- InnoDB 통계 설정 확인
SHOW VARIABLES LIKE 'innodb_stats%';

-- 통계 샘플 페이지 수 늘리기 (더 정확한 통계)
SET GLOBAL innodb_stats_persistent_sample_pages = 20; -- 기본값 20
-- 큰 테이블이라면 더 높게 설정 고려

-- 날짜 기반 파티셔닝 예시
CREATE TABLE orders_partitioned (
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(10,2),
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at),  -- 파티션 키가 PK에 포함되어야 함
    INDEX idx_user_status (user_id, status)
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 파티션 프루닝 확인 (특정 파티션만 스캔)
EXPLAIN SELECT * FROM orders_partitioned 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- partitions 컬럼에서 p2024만 표시되면 성공!

-- ICP 설정 확인 및 제어
SHOW VARIABLES LIKE 'optimizer_switch';
-- index_condition_pushdown=on 확인

-- ICP 비활성화 (테스트용)
SET optimizer_switch = 'index_condition_pushdown=off';

-- 다시 활성화
SET optimizer_switch = 'index_condition_pushdown=on';

-- 문제 있는 JOIN 쿼리
SELECT u.name, o.total_amount, p.name AS product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.city = '서울'
  AND o.status = 'completed'
  AND o.created_at >= '2024-01-01';

-- 필요한 인덱스들:
-- users: city 컬럼 인덱스
CREATE INDEX idx_users_city ON users(city);

-- orders: user_id + status + created_at 복합 인덱스
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

-- order_items: order_id 인덱스 (FK 인덱스)
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- products: PK가 있으면 충분 (id로 조인)

-- JOIN 순서 최적화: 작은 결과셋이 드라이빙 테이블이 되도록
-- MySQL 옵티마이저가 자동으로 최적화하지만, 
-- STRAIGHT_JOIN으로 강제 지정도 가능

-- 한 번도 사용되지 않은 인덱스 찾기 (MySQL 재시작 후부터 카운트)
SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME,
    s.SEQ_IN_INDEX,
    t2.COUNT_READ
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t 
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage t2
    ON s.TABLE_SCHEMA = t2.OBJECT_SCHEMA 
    AND s.TABLE_NAME = t2.OBJECT_NAME 
    AND s.INDEX_NAME = t2.INDEX_NAME
WHERE t.TABLE_SCHEMA = 'your_database'
  AND s.INDEX_NAME != 'PRIMARY'
  AND (t2.COUNT_READ = 0 OR t2.COUNT_READ IS NULL)
ORDER BY t.TABLE_NAME, s.INDEX_NAME;

SELECT 
    TABLE_NAME AS 테이블명,
    ROUND(DATA_LENGTH/1024/1024, 2) AS 데이터크기_MB,
    ROUND(INDEX_LENGTH/1024/1024, 2) AS 인덱스크기_MB,
    ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2) AS 전체크기_MB,
    TABLE_ROWS AS 예상행수,
    ROUND(INDEX_LENGTH/DATA_LENGTH * 100, 1) AS 인덱스비율_퍼센트
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 중복 또는 불필요한 인덱스 찾기
-- (A) 인덱스와 (A, B) 인덱스가 있으면 (A) 인덱스는 중복!
SELECT 
    a.TABLE_NAME,
    a.INDEX_NAME AS 중복가능인덱스,
    a.COLUMN_NAME,
    b.INDEX_NAME AS 포함하는인덱스
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b 
    ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
    AND a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
    AND a.INDEX_NAME != b.INDEX_NAME
WHERE a.TABLE_SCHEMA = 'your_database'
  AND a.SEQ_IN_INDEX = 1
ORDER BY a.TABLE_NAME, a.INDEX_NAME;

-- 현재 실행 중인 쿼리 목록 (5초 이상 실행 중인 것)
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME AS 실행시간_초,
    STATE,
    LEFT(INFO, 200) AS 쿼리내용
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
  AND TIME > 5
ORDER BY TIME DESC;

-- 특정 쿼리 강제 종료 (필요시)
KILL QUERY [프로세스ID];

-- 잠금 대기 중인 쿼리 확인
SELECT 
    r.trx_id AS 대기중인트랜잭션,
    r.trx_mysql_thread_id AS 대기스레드,
    b.trx_id AS 잠금보유트랜잭션,
    b.trx_mysql_thread_id AS 잠금스레드,
    b.trx_query AS 잠금보유쿼리
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

-- MySQL 8.0의 혁신적인 기능!
-- 인덱스를 삭제하지 않고 옵티마이저에게 "안 보이게" 할 수 있어

-- 인덱스를 invisible로 설정 (옵티마이저가 사용 안 함)
ALTER TABLE orders ALTER INDEX idx_old_index INVISIBLE;

-- 이 상태에서 성능 테스트 → 문제없으면 실제 삭제
DROP INDEX idx_old_index ON orders;

-- 다시 visible로 복원 (삭제 전 롤백 가능!)
ALTER TABLE orders ALTER INDEX idx_old_index VISIBLE;

-- 현재 인덱스 visibility 확인
SELECT INDEX_NAME, IS_VISIBLE 
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'orders'
GROUP BY INDEX_NAME, IS_VISIBLE;

-- MySQL 8.0부터 내림차순 인덱스 지원
CREATE INDEX idx_created_desc ON orders(created_at DESC);

-- 이런 쿼리에서 filesort 없이 처리 가능
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- 복합 인덱스에서 혼합 정렬 방향도 가능
CREATE INDEX idx_user_date ON orders(user_id ASC, created_at DESC);

-- 이 쿼리가 이제 filesort 없이 처리됨!
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY user_id ASC, created_at DESC;

-- MySQL 8.0에서 도입된 Skip Scan
-- 복합 인덱스의 첫 번째 컬럼이 WHERE에 없어도 인덱스 활용 가능!

-- (gender, age) 복합 인덱스가 있을 때
CREATE INDEX idx_gender_age ON users(gender, age);

-- 기존에는 이 쿼리가 인덱스를 못 썼지만
-- MySQL 8.0 Skip Scan으로 인덱스 활용 가능!
SELECT * FROM users WHERE age > 25;

-- EXPLAIN에서 확인
-- Extra: Using index for skip scan

-- 단, gender의 카디널리티가 낮을 때 효과적
-- (gender가 '남'/'여' 2가지라면 각각에 대해 age 범위 탐색)

-- 시나리오: 상품 검색 + 필터링 + 정렬 쿼리 최적화

-- 문제 쿼리 (느림)
SELECT p.id, p.name, p.price, p.stock, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active'
  AND p.price BETWEEN 10000 AND 50000
  AND p.category_id = 5
ORDER BY p.created_at DESC
LIMIT 20 OFFSET 0;

-- Step 1: EXPLAIN으로 현황 파악
EXPLAIN SELECT p.id, p.name, p.price, p.stock, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active'
  AND p.price BETWEEN 10000 AND 50000
  AND p.category_id = 5
ORDER BY p.created_at DESC
LIMIT 20 OFFSET 0;

-- 결과: products 테이블 type=ALL, Using filesort 발생

-- Step 2: 인덱스 설계
-- 조건 분석:
-- - category_id = 5 (등호, 카디널리티 중간)
-- - status = 'active' (등호, 카디널리티 낮음)
-- - price BETWEEN (범위)
-- - ORDER BY created_at DESC

-- 최적 인덱스 (등호 조건 먼저, 범위 조건 뒤, 정렬 컬럼 마지막)
CREATE INDEX idx_products_search 
ON products(category_id, status, price, created_at);

-- 커버링 인덱스 버전 (JOIN 없이 처리하려면)
CREATE INDEX idx_products_covering
ON products(category_id, status, price, created_at, id, name, stock);

-- Step 3: 개선 후 EXPLAIN 확인
-- type: range, Extra: Using index condition (또는 Using index)

-- Step 4: 페이지네이션 최적화 (OFFSET이 크면 느려짐)
-- 문제: LIMIT 20 OFFSET 10000 → 10020개 읽고 10000개 버림

-- 해결: 커서 기반 페이지네이션
SELECT p.id, p.name, p.price, p.stock
FROM products p
WHERE p.status = 'active'
  AND p.category_id = 5
  AND p.price BETWEEN 10000 AND 50000
  AND p.created_at < '2024-06-01 12:00:00'  -- 이전 페이지 마지막 created_at
ORDER BY p.created_at DESC
LIMIT 20;