-- B-Tree 인덱스 생성
CREATE INDEX idx_user_email ON users(email);

-- 인덱스를 활용한 범위 검색
SELECT * FROM users
WHERE email BETWEEN 'a@example.com' AND 'z@example.com';

-- 에러: 인덱스 키가 너무 큼
CREATE INDEX idx_long_text ON posts(content);
-- ERROR 1071: Specified key was too long

-- 해결: 프리픽스 인덱스 사용
CREATE INDEX idx_content_prefix ON posts(content(100));

-- 카디널리티 확인
SHOW INDEXES FROM users;

-- Cardinality 컬럼에 고유 값 개수 표시
+-------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Cardinality |
+-------+------------+----------+--------------+-------------+
| users |          0 | PRIMARY  |            1 |      100000 |
| users |          1 | idx_city |            1 |          50 |
| users |          1 | idx_age  |            1 |          80 |
+-------+------------+----------+--------------+-------------+

선택도 = 카디널리티 / 전체 행 수

-- gender 컬럼은 선택도가 낮아 인덱스를 사용하지 않을 수 있음
SELECT * FROM users WHERE gender = 'M';
-- EXPLAIN: type = ALL (Full Table Scan)

-- user_id는 선택도가 높아 인덱스를 사용
SELECT * FROM users WHERE user_id = 12345;
-- EXPLAIN: type = const (Primary Key)

-- 통계 갱신 (random dive 방식)
ANALYZE TABLE users;

-- 통계 확인
SELECT * FROM mysql.innodb_index_stats
WHERE table_name = 'users';

-- 복합 인덱스 생성
CREATE INDEX idx_name_age_sex ON users(name, age, sex);

-- 케이스 1: 인덱스 순서와 동일
SELECT * FROM users
WHERE name = 'John' AND age = 25 AND sex = 'M';
-- 인덱스 사용: idx_name_age_sex

-- 케이스 2: WHERE 절 순서가 다름
SELECT * FROM users
WHERE age = 25 AND name = 'John' AND sex = 'M';
-- 인덱스 사용: idx_name_age_sex (동일하게 사용)

-- 케이스 3: WHERE 절 순서가 완전히 다름
SELECT * FROM users
WHERE sex = 'M' AND age = 25 AND name = 'John';
-- 인덱스 사용: idx_name_age_sex (동일하게 사용)

EXPLAIN SELECT * FROM users
WHERE age = 25 AND name = 'John' AND sex = 'M';

+------+------------------+------+-----------------------+
| type | key              | rows | Extra                 |
+------+------------------+------+-----------------------+
| ref  | idx_name_age_sex |    1 | Using index condition |
+------+------------------+------+-----------------------+

-- 복합 인덱스: (name, age, sex)

-- OK: name부터 시작
WHERE name = 'John'  -- name 사용

-- OK: name부터 시작하고 age까지
WHERE name = 'John' AND age = 25  -- name, age 사용

-- OK: name부터 시작하지만 age가 없으므로 sex는 인덱스 사용 불가
WHERE name = 'John' AND sex = 'M'  -- name만 사용 (sex는 사용 불가)

-- 사용 안 함: leftmost(name)로 시작하지 않음
WHERE age = 25  -- Full Table Scan (Skip Scan은 예외적 최적화)

-- 사용 안 함: leftmost(name)로 시작하지 않음
WHERE sex = 'M'  -- Full Table Scan

-- 복합 인덱스 생성
CREATE INDEX idx_name_age_sex ON users(name, age, sex);

-- WHERE 절 순서를 바꿔도 동일한 실행 계획
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 25;
EXPLAIN SELECT * FROM users WHERE age = 25 AND name = 'John';
-- 둘 다 동일: type=ref, key=idx_name_age_sex, rows=1

-- 범위 조건
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- 부등호 조건
SELECT * FROM users WHERE created_at > '2024-01-01';

-- IN 절
SELECT * FROM users WHERE user_id IN (1, 5, 10);

EXPLAIN 출력:
+------+-------+------+------------------------+
| type | key   | rows | Extra                  |
+------+-------+------+------------------------+
| range| idx_age| 1000 | Using index condition |
+------+-------+------+------------------------+

-- ORDER BY만 있고 WHERE 조건이 없는 경우
SELECT * FROM users ORDER BY email;

EXPLAIN 출력:
+------+-------+--------+----------------+
| type | key   | rows   | Extra          |
+------+-------+--------+----------------+
| index| idx_email| 100000 | Using index |
+------+-------+--------+----------------+

-- 복합 인덱스 생성
CREATE INDEX idx_gender_age ON users(gender, age);

-- 첫 번째 컬럼(gender)을 WHERE 절에 사용하지 않음
SELECT * FROM users WHERE age = 25;

EXPLAIN 출력:
+------+---------------+------+--------------------------------+
| type | key           | rows | Extra                          |
+------+---------------+------+--------------------------------+
| range| idx_gender_age| 2000 | Using index for skip scan      |
+------+---------------+------+--------------------------------+

-- Skip Scan 플래그 확인
SHOW VARIABLES LIKE 'optimizer_switch';
-- skip_scan=on (기본값)

-- Skip Scan 비활성화
SET optimizer_switch='skip_scan=off';

-- 복합 인덱스 생성
CREATE INDEX idx_city_age ON users(city, age);

-- GROUP BY로 city별 최소 나이 조회
SELECT city, MIN(age) FROM users GROUP BY city;

EXPLAIN 출력:
+------+-------------+------+----------------------------+
| type | key         | rows | Extra                      |
+------+-------------+------+----------------------------+
| range| idx_city_age| 50   | Using index for group-by   |
+------+-------------+------+----------------------------+

-- 두 개의 단일 컬럼 인덱스 생성
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);

-- OR 조건으로 두 인덱스 모두 활용
SELECT * FROM users
WHERE age = 25 OR city = 'Seoul';

EXPLAIN 출력:
+-------------+-------------------+------+------------------------------+
| type        | key               | rows | Extra                        |
+-------------+-------------------+------+------------------------------+
| index_merge | idx_age,idx_city  | 3000 | Using union(idx_age,idx_city)|
+-------------+-------------------+------+------------------------------+

-- 인덱스가 없거나 선택도가 낮은 경우
SELECT * FROM users WHERE gender = 'M';

EXPLAIN 출력:
+------+------+--------+-------+
| type | key  | rows   | Extra |
+------+------+--------+-------+
| ALL  | NULL | 100000 |       |
+------+------+--------+-------+

CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    tags JSON  -- ["MySQL", "Database", "Index"]
);

-- "MySQL" 태그를 가진 포스트를 찾고 싶다
SELECT * FROM posts WHERE JSON_CONTAINS(tags, '"MySQL"');

일반 인덱스의 한계:
Row 1: tags = ["MySQL", "Database", "Index"]  ← 배열 전체가 하나의 값
Row 2: tags = ["Java", "Spring"]              ← 배열 전체가 하나의 값
Row 3: tags = ["MySQL", "InnoDB"]             ← 배열 전체가 하나의 값

→ "MySQL"을 찾으려면? 모든 행을 스캔하며 JSON_CONTAINS() 실행 (느림)
→ EXPLAIN: type = ALL, rows = 10000

멀티밸류 인덱스 구조:
"MySQL"    → [Row 1, Row 3]  ← 인덱스 엔트리
"Database" → [Row 1]
"Index"    → [Row 1]
"Java"     → [Row 2]
"Spring"   → [Row 2]
"InnoDB"   → [Row 3]

→ "MySQL"을 찾으려면? 인덱스에서 "MySQL" 검색 (빠름)
→ EXPLAIN: type = ref, rows = 2

-- 태그 멀티밸류 인덱스 생성
ALTER TABLE posts
ADD INDEX idx_tags((CAST(tags AS CHAR(20) ARRAY)));

-- 이제 인덱스를 사용해서 빠르게 검색
SELECT * FROM posts WHERE JSON_CONTAINS(tags, '"MySQL"');

CREATE INDEX index_name ON table_name (
    (CAST(json_column AS type ARRAY))
);

-- 타입 예시:
-- CHAR(N): 문자열 배열 (태그, 카테고리명)
-- UNSIGNED: 정수 배열 (카테고리 ID, 우편번호)
-- DATE: 날짜 배열 (기념일, 이벤트 날짜)

-- 오름차순 정렬 (정순 스캔)
SELECT * FROM users ORDER BY user_id ASC LIMIT 10;

EXPLAIN FORMAT=TREE 출력:
-> Limit: 10 row(s)
    -> Index scan on users using PRIMARY

-- 내림차순 정렬 (역순 스캔)
SELECT * FROM users ORDER BY user_id DESC LIMIT 10;

EXPLAIN FORMAT=TREE 출력:
-> Limit: 10 row(s)
    -> Index scan on users using PRIMARY (reverse)

-- MySQL 5.7 이하
CREATE INDEX idx_score_desc ON users(score DESC);
-- DESC가 무시되고 실제로는 ASC로 저장됨

-- 내림차순 정렬 쿼리 시
SELECT * FROM users ORDER BY score DESC;
-- Backward Index Scan 발생 (느림)

-- 내림차순 인덱스 생성
CREATE INDEX idx_user_score_desc
ON users(user_id ASC, score DESC);

-- 혼합 정렬 쿼리
SELECT * FROM users
ORDER BY user_id ASC, score DESC
LIMIT 10;

EXPLAIN FORMAT=TREE 출력:
-> Limit: 10 row(s)
    -> Index scan on users using idx_user_score_desc
    -- (reverse) 없음 = Forward Scan 사용

-- 사용자별 최신 주문 조회
CREATE INDEX idx_order_user_date
ON orders(user_id ASC, created_at DESC);

SELECT * FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 10;
-- Forward Scan 사용 (빠름)

-- InnoDB만 지원
CREATE INDEX idx_score_desc ON users(score DESC)
ENGINE=InnoDB;  -- OK

-- GROUP BY MIN()/MAX() 최적화에 사용 불가
SELECT MIN(score) FROM users;
-- DESC 인덱스가 있어도 인덱스 스캔 대신 filesort 사용

-- 유니크 인덱스
SELECT * FROM users WHERE email = 'user@example.com';
-- 인덱스에서 "user@example.com"을 찾음 → 끝 (유니크하므로 하나만 존재)

-- 일반 세컨더리 인덱스
SELECT * FROM posts WHERE category = 'MySQL';
-- 인덱스에서 "MySQL"을 찾음
-- → 다음 레코드 확인 → "MySQL"인지 체크
-- → 다르면 끝, 같으면 계속

페이지 1: [MySQL, MySQL, MySQL, Python, ...]
          ↑ 찾음  ↑ 다음 레코드 확인 (메모리에서 비교)

페이지 1: [..., MySQL]
페이지 2: [MySQL, ...]  ← 다음 페이지 읽기 필요

디스크 I/O: ~10ms
메모리 비교: ~0.001ms (약 10,000배 빠름)

CREATE TABLE tb_unique (
    id INTEGER NOT NULL,
    nick_name VARCHAR(100),
    PRIMARY KEY (id),
    UNIQUE INDEX ux_nickname (nick_name),
    INDEX ix_nickname (nick_name)  -- 불필요한 중복 인덱스
);

-- 부모 테이블: 사용자
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 자식 테이블: 게시글
CREATE TABLE posts (
    id INT PRIMARY KEY,
    user_id INT,  -- users 테이블의 id를 참조하는 외래키
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- user_id는 반드시 users 테이블에 존재하는 id여야 함

CREATE TABLE tb_parent (
    id INT NOT NULL,
    fd VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE tb_child (
    id INT NOT NULL,
    pid INT DEFAULT NULL,  -- parent.id 칼럼 참조
    fd VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY ix_parentid (pid),
    CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO tb_parent VALUES (1, 'parent-1'), (2, 'parent-2');
INSERT INTO tb_child VALUES (100, 1, 'child-100');