#Step 15 — 인덱스

USE shop;
SHOW INDEX FROM access_logs;

EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;

SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;

ALTER TABLE access_logs ADD INDEX idx_customer (customer_id);   -- (0.688 sec, 1회성)

EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;

SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;

SELECT
  COUNT(*)                                       AS total,
  COUNT(DISTINCT customer_id)                    AS cust_card,
  COUNT(DISTINCT status_code)                    AS status_card,
  ROUND(COUNT(*) / COUNT(DISTINCT customer_id))  AS rows_per_cust,
  ROUND(COUNT(*) / COUNT(DISTINCT status_code))  AS rows_per_status
FROM access_logs;

SELECT log_id, path, logged_at FROM access_logs
WHERE customer_id = 7 ORDER BY logged_at DESC LIMIT 5;

ALTER TABLE access_logs ADD INDEX idx_cust_time (customer_id, logged_at);

EXPLAIN SELECT log_id, path, logged_at FROM access_logs
WHERE customer_id = 7 ORDER BY logged_at DESC LIMIT 5;

-- 스킵 스캔을 잠깐 끄고 순수한 동작을 봅니다
SET SESSION optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT COUNT(*) FROM access_logs
WHERE logged_at >= '2024-06-01' AND logged_at < '2024-06-02';

SET SESSION optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT COUNT(*) FROM access_logs
WHERE logged_at >= '2024-06-01' AND logged_at < '2024-06-02';

-- 필요한 컬럼(customer_id, logged_at)이 전부 인덱스에 있다 → 커버링
EXPLAIN SELECT customer_id, logged_at FROM access_logs
WHERE customer_id = 7 AND logged_at >= '2024-06-01';

-- path 는 인덱스에 없다 → 테이블을 읽어야 함 (커버링 실패)
EXPLAIN SELECT customer_id, path FROM access_logs
WHERE customer_id = 7 AND logged_at >= '2024-06-01';

-- idx_cust_time 이 (customer_id, logged_at) 이지만 logged_at 에 MONTH() 를 씌움
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE MONTH(logged_at) = 6;

WHERE logged_at >= '2024-06-01' AND logged_at < '2024-07-01'   -- sargable

EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = '7';   -- 리터럴 '7'

EXPLAIN SELECT COUNT(*) FROM access_logs WHERE CAST(customer_id AS CHAR) = '7';

ALTER TABLE access_logs ADD INDEX idx_path (path);

EXPLAIN SELECT COUNT(*) FROM access_logs WHERE path LIKE '%detail';    -- 앞 %

EXPLAIN SELECT COUNT(*) FROM access_logs WHERE path LIKE '/products%';  -- 뒤 %

-- customer_id 는 인덱스 있음, status_code 는 인덱스 없음
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7 OR status_code = 500;

SELECT ... WHERE customer_id = 7
UNION
SELECT ... WHERE status_code = 500;   -- 각 SELECT 가 자기 인덱스를 탄다

ALTER TABLE access_logs DROP INDEX idx_path;   -- (3)에서 만든 것 정리

-- ORDER BY customer_id ASC, logged_at DESC  → ASC 전용 인덱스로는 filesort
EXPLAIN SELECT customer_id, logged_at FROM access_logs
WHERE customer_id BETWEEN 5 AND 8 ORDER BY customer_id ASC, logged_at DESC LIMIT 10;

ALTER TABLE access_logs ADD INDEX idx_mixed (customer_id ASC, logged_at DESC);
EXPLAIN SELECT customer_id, logged_at FROM access_logs
WHERE customer_id BETWEEN 5 AND 8 ORDER BY customer_id ASC, logged_at DESC LIMIT 10;

ALTER TABLE access_logs DROP INDEX idx_mixed;

ALTER TABLE access_logs ALTER INDEX idx_customer INVISIBLE;   -- 숨김
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;

ALTER TABLE access_logs ALTER INDEX idx_customer VISIBLE;     -- 되돌림

CREATE TABLE s15_uk (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(20) NOT NULL,
  UNIQUE KEY uk_code (code)) ENGINE=InnoDB;
EXPLAIN SELECT * FROM s15_uk WHERE code = 'A';

-- 이메일 3000건 (앞 8글자면 이미 다 구별됨)
SELECT COUNT(DISTINCT email)          AS full_distinct,
       COUNT(DISTINCT LEFT(email, 4)) AS p4,
       COUNT(DISTINCT LEFT(email, 8)) AS p8
FROM s15_email;

ALTER TABLE s15_email ADD INDEX idx_email_prefix (email(8));   -- 앞 8글자만

CREATE TABLE s15_ft (id INT AUTO_INCREMENT PRIMARY KEY, body TEXT,
  FULLTEXT KEY ft_body (body)) ENGINE=InnoDB;
INSERT INTO s15_ft (body) VALUES
  ('mysql index tuning guide'), ('postgres vacuum internals'), ('mysql replication and index');

EXPLAIN SELECT * FROM s15_ft WHERE MATCH(body) AGAINST('index' IN NATURAL LANGUAGE MODE);

SELECT id, body FROM s15_ft WHERE MATCH(body) AGAINST('index' IN NATURAL LANGUAGE MODE);

ALTER TABLE access_logs DROP INDEX idx_customer;
ALTER TABLE access_logs DROP INDEX idx_cust_time;
-- idx_path, idx_mixed, idx_desc 등은 각 절에서 이미 DROP 했습니다.

SHOW INDEX FROM access_logs;   -- PRIMARY 만 남았는지 확인

-- =====================================================================
-- Step 15 — 인덱스 : practice.sql
-- ---------------------------------------------------------------------
-- 실행:  mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < practice.sql
--   * EXPLAIN 은 계획만 보고, SELECT 는 실제 실행시간을 확인하세요.
--   * 실행시간을 직접 보려면 대화형 클라이언트에서 한 줄씩 실행하는 것을 권합니다.
--
-- ⚠️ 이 스텝은 access_logs 에 인덱스를 만들어도 됩니다. 마지막에 전부 DROP 합니다.
--    다른 공용 테이블에는 인덱스를 만들거나 지우지 마세요.
-- =====================================================================
USE shop;

-- [15-0] 시작 상태 확인 — PRIMARY 하나뿐
SHOW INDEX FROM access_logs;


-- ---------------------------------------------------------------------
-- [15-2] 첫 인덱스 — 전후 실행시간 실측
-- ---------------------------------------------------------------------
-- BEFORE: 풀스캔 (type: ALL, rows ~100만)
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;
SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;          -- 약 0.076 sec

-- 인덱스 생성
ALTER TABLE access_logs ADD INDEX idx_customer (customer_id);    -- 약 0.688 sec (1회성)

-- AFTER: type: ref, Using index (커버링) → 약 25배 빠름
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;
SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;          -- 약 0.003 sec


-- ---------------------------------------------------------------------
-- [15-4] 카디널리티와 선택도
-- ---------------------------------------------------------------------
SELECT
  COUNT(*)                                       AS total,
  COUNT(DISTINCT customer_id)                    AS cust_card,
  COUNT(DISTINCT status_code)                    AS status_card,
  ROUND(COUNT(*) / COUNT(DISTINCT customer_id))  AS rows_per_cust,
  ROUND(COUNT(*) / COUNT(DISTINCT status_code))  AS rows_per_status
FROM access_logs;
-- customer_id 30종, status_code 6종 → 둘 다 선택도 낮음(값 하나가 수만~십수만 행)


-- ---------------------------------------------------------------------
-- [15-5] 복합 인덱스와 선두 컬럼 규칙
-- ---------------------------------------------------------------------
-- BEFORE: 풀스캔 + filesort
EXPLAIN SELECT log_id, path, logged_at FROM access_logs
WHERE customer_id = 7 ORDER BY logged_at DESC LIMIT 5;

ALTER TABLE access_logs ADD INDEX idx_cust_time (customer_id, logged_at);

-- AFTER: filesort 사라짐, Backward index scan → 약 0.001 sec
EXPLAIN SELECT log_id, path, logged_at FROM access_logs
WHERE customer_id = 7 ORDER BY logged_at DESC LIMIT 5;
SELECT log_id, path, logged_at FROM access_logs
WHERE customer_id = 7 ORDER BY logged_at DESC LIMIT 5;

-- 선두 컬럼 규칙: logged_at 단독 조건은 (customer_id, logged_at) 을 "탐색"하지 못한다
-- 스킵 스캔을 끄고 순수 동작 확인 → type: index (인덱스 풀스캔!)
SET SESSION optimizer_switch = 'skip_scan=off';
EXPLAIN SELECT COUNT(*) FROM access_logs
WHERE logged_at >= '2024-06-01' AND logged_at < '2024-06-02';

-- 스킵 스캔 켜기(8.0 기본): 선두 컬럼 카디널리티가 낮으면 부분 활용 → Using index for skip scan
SET SESSION optimizer_switch = 'skip_scan=on';
EXPLAIN SELECT COUNT(*) FROM access_logs
WHERE logged_at >= '2024-06-01' AND logged_at < '2024-06-02';


-- ---------------------------------------------------------------------
-- [15-6] 커버링 인덱스
-- ---------------------------------------------------------------------
-- 필요한 컬럼(customer_id, logged_at)이 전부 인덱스에 있음 → Using index (커버링)
EXPLAIN SELECT customer_id, logged_at FROM access_logs
WHERE customer_id = 7 AND logged_at >= '2024-06-01';

-- path 는 인덱스에 없음 → 테이블 접근 필요 (Using index 없음)
EXPLAIN SELECT customer_id, path FROM access_logs
WHERE customer_id = 7 AND logged_at >= '2024-06-01';


-- ---------------------------------------------------------------------
-- [15-7] 인덱스를 못 타는 패턴들
-- ---------------------------------------------------------------------
-- (1) 컬럼에 함수 → type: index (풀스캔)
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE MONTH(logged_at) = 6;
-- 해결: 범위로 (sargable)
EXPLAIN SELECT COUNT(*) FROM access_logs
WHERE logged_at >= '2024-06-01' AND logged_at < '2024-07-01';

-- (2) 오해 풀기: 정수 컬럼 vs 문자열 리터럴 → 리터럴이 변환됨, 인덱스 정상 사용
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = '7';
-- 진짜 문제: 컬럼 쪽이 변환될 때 → 풀스캔
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE CAST(customer_id AS CHAR) = '7';

-- (3) 앞 % LIKE 못 탐 / 뒤 % LIKE 는 탐
ALTER TABLE access_logs ADD INDEX idx_path (path);
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE path LIKE '%detail';      -- type: index
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE path LIKE '/products%';   -- type: range

-- (4) OR 한쪽에 인덱스 없으면 풀스캔 (status_code 무인덱스)
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7 OR status_code = 500;
-- 해결: UNION 으로 쪼개면 각 SELECT 가 자기 인덱스를 탄다
EXPLAIN
SELECT log_id FROM access_logs WHERE customer_id = 7
UNION
SELECT log_id FROM access_logs WHERE path = '/cart';

ALTER TABLE access_logs DROP INDEX idx_path;


-- ---------------------------------------------------------------------
-- [15-8] 8.0 신기능 인덱스
-- ---------------------------------------------------------------------
-- 내림차순 인덱스: 혼합 정렬(ASC, DESC)의 filesort 제거
-- BEFORE: ASC 인덱스로는 filesort
EXPLAIN SELECT customer_id, logged_at FROM access_logs
WHERE customer_id BETWEEN 5 AND 8 ORDER BY customer_id ASC, logged_at DESC LIMIT 10;

ALTER TABLE access_logs ADD INDEX idx_mixed (customer_id ASC, logged_at DESC);
-- AFTER: filesort 사라짐
EXPLAIN SELECT customer_id, logged_at FROM access_logs
WHERE customer_id BETWEEN 5 AND 8 ORDER BY customer_id ASC, logged_at DESC LIMIT 10;
SHOW INDEX FROM access_logs WHERE Key_name = 'idx_mixed';   -- Collation 컬럼: A(ASC)/D(DESC)
ALTER TABLE access_logs DROP INDEX idx_mixed;

-- 인비저블 인덱스: 지우지 않고 옵티마이저에게만 숨김
ALTER TABLE access_logs ALTER INDEX idx_customer INVISIBLE;
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;   -- idx_customer 후보에서 빠짐
ALTER TABLE access_logs ALTER INDEX idx_customer VISIBLE;
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;   -- 다시 사용


-- ---------------------------------------------------------------------
-- [15-9] 유니크 / 프리픽스 인덱스
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s15_uk, s15_email;

-- 유니크 인덱스 → type: const (정확히 1행)
CREATE TABLE s15_uk (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(20) NOT NULL,
  UNIQUE KEY uk_code (code)) ENGINE=InnoDB;
INSERT INTO s15_uk (code) VALUES ('A'),('B');
EXPLAIN SELECT * FROM s15_uk WHERE code = 'A';

-- 프리픽스 인덱스: 앞 N글자만
CREATE TABLE s15_email (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(100) NOT NULL) ENGINE=InnoDB;
INSERT INTO s15_email (email) SELECT CONCAT(SUBSTRING(MD5(n),1,12), '@example.com') FROM tally WHERE n <= 3000;
-- 적정 프리픽스 길이 찾기: full 과 거의 같아지는 최소 N
SELECT COUNT(DISTINCT email)          AS full_distinct,
       COUNT(DISTINCT LEFT(email, 4)) AS p4,
       COUNT(DISTINCT LEFT(email, 8)) AS p8
FROM s15_email;
ALTER TABLE s15_email ADD INDEX idx_email_prefix (email(8));
EXPLAIN SELECT id FROM s15_email WHERE email = CONCAT(SUBSTRING(MD5(1),1,12), '@example.com');
DROP TABLE s15_uk, s15_email;


-- ---------------------------------------------------------------------
-- [15-10] 전문검색(FULLTEXT) 인덱스
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s15_ft;
CREATE TABLE s15_ft (id INT AUTO_INCREMENT PRIMARY KEY, body TEXT,
  FULLTEXT KEY ft_body (body)) ENGINE=InnoDB;
INSERT INTO s15_ft (body) VALUES
  ('mysql index tuning guide'), ('postgres vacuum internals'), ('mysql replication and index');
EXPLAIN SELECT * FROM s15_ft WHERE MATCH(body) AGAINST('index' IN NATURAL LANGUAGE MODE);
SELECT id, body FROM s15_ft WHERE MATCH(body) AGAINST('index' IN NATURAL LANGUAGE MODE);
DROP TABLE s15_ft;


-- ---------------------------------------------------------------------
-- [15-11] 인덱스 정리 — access_logs 를 원래 상태(PK만)로
-- ---------------------------------------------------------------------
ALTER TABLE access_logs DROP INDEX idx_customer;
ALTER TABLE access_logs DROP INDEX idx_cust_time;
SHOW INDEX FROM access_logs;    -- PRIMARY 만 남아야 정상

SELECT 'Step 15 practice 완료' AS msg;

-- =====================================================================
-- Step 15 — 인덱스 : exercise.sql  (문제 7개)
-- ---------------------------------------------------------------------
-- 정답은 solution.sql. EXPLAIN 을 직접 돌려 확인하세요.
--
-- ⚠️ access_logs 에 인덱스를 만들어도 되지만, 각 문제 끝에 반드시 DROP 하세요.
--    다른 공용 테이블에는 인덱스를 만들거나 지우지 마세요.
-- =====================================================================
USE shop;


-- ---------------------------------------------------------------------
-- [문제 1] 느린 쿼리에 맞는 인덱스 설계
-- ---------------------------------------------------------------------
-- 다음 쿼리가 자주 실행됩니다. filesort/풀스캔 없이 처리되도록 인덱스 하나를 설계하세요.
--   SELECT log_id, status_code, duration_ms
--   FROM access_logs
--   WHERE customer_id = 15 AND method = 'POST'
--   ORDER BY logged_at DESC
--   LIMIT 20;
--
-- (1-1) 인덱스를 만들기 전 EXPLAIN 을 확인하세요.
-- (1-2) 인덱스를 만들고 EXPLAIN 이 어떻게 바뀌는지 확인하세요.
--       힌트: 등치 조건 컬럼 → 정렬 컬럼 순서로 배치.
-- (1-3) 확인 후 인덱스를 DROP 하세요.

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 2] 이 복합 인덱스는 어떤 쿼리를 커버하나
-- ---------------------------------------------------------------------
-- idx_test = (customer_id, status_code, logged_at) 를 만들었다고 합시다.
ALTER TABLE access_logs ADD INDEX idx_test (customer_id, status_code, logged_at);

-- 아래 (a)~(e) 각각이 idx_test 를 "탐색(seek)"에 쓸 수 있는지 예측하고,
-- EXPLAIN 으로 확인하세요. (type 이 ref/range 면 탐색, index/ALL 이면 못 탐)
--   (a) WHERE customer_id = 7
--   (b) WHERE customer_id = 7 AND status_code = 200
--   (c) WHERE status_code = 200
--   (d) WHERE customer_id = 7 AND logged_at >= '2024-06-01'
--   (e) WHERE customer_id = 7 AND status_code = 200 AND logged_at >= '2024-06-01'

-- 여기에 EXPLAIN 들 작성 후:
ALTER TABLE access_logs DROP INDEX idx_test;



-- ---------------------------------------------------------------------
-- [문제 3] 커버링 인덱스로 Using index 만들기
-- ---------------------------------------------------------------------
-- 다음 쿼리가 테이블을 아예 읽지 않도록(Using index) 인덱스를 설계하세요.
--   SELECT customer_id, method, COUNT(*)
--   FROM access_logs
--   WHERE customer_id = 3
--   GROUP BY customer_id, method;
--
-- 인덱스 전/후 EXPLAIN 을 비교하고, 확인 후 DROP 하세요.

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 4] 인덱스를 못 타는 쿼리 고치기
-- ---------------------------------------------------------------------
-- 아래 세 쿼리는 인덱스를 못 탑니다. sargable 하게(인덱스를 타도록) 고쳐 쓰세요.
-- (먼저 idx_time 을 만들고, 원본/수정본 EXPLAIN 을 비교하세요)
ALTER TABLE access_logs ADD INDEX idx_time (logged_at);

--   (a) SELECT COUNT(*) FROM access_logs WHERE YEAR(logged_at) = 2024;
--   (b) SELECT COUNT(*) FROM access_logs WHERE logged_at + INTERVAL 0 DAY >= '2024-06-01';
--   (c) SELECT COUNT(*) FROM access_logs WHERE DATE(logged_at) = '2024-06-15';

-- 여기에 수정본 작성 후:
ALTER TABLE access_logs DROP INDEX idx_time;



-- ---------------------------------------------------------------------
-- [문제 5] 선택도로 복합 인덱스 컬럼 순서 정하기
-- ---------------------------------------------------------------------
-- "특정 고객이 특정 경로에 남긴 로그"를 자주 조회합니다.
--   WHERE customer_id = ? AND path = ?
-- (customer_id, path) 와 (path, customer_id) 중 어느 순서가 나을까요?
--
-- (5-1) 두 컬럼의 카디널리티를 구하는 쿼리를 쓰세요.
-- (5-2) 어느 순서가 나은지 근거와 함께 주석으로 답하세요.
--       (둘 다 등치 조건일 때 선두 컬럼 선택 기준은 무엇일까요?)

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 6] 인비저블 인덱스로 삭제 안전성 실험
-- ---------------------------------------------------------------------
-- idx_dur = (duration_ms) 인덱스가 정말 필요한지 확인하려 합니다.
ALTER TABLE access_logs ADD INDEX idx_dur (duration_ms);

-- (6-1) 이 쿼리가 idx_dur 을 쓰는지 EXPLAIN 으로 확인:
--       SELECT COUNT(*) FROM access_logs WHERE duration_ms BETWEEN 100 AND 110;
-- (6-2) idx_dur 을 "지우지 않고" 숨겨서, 위 쿼리가 어떻게 바뀌는지 확인:
-- (6-3) 다시 보이게 되돌린 뒤 DROP:

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 7] 프리픽스 인덱스 적정 길이 찾기
-- ---------------------------------------------------------------------
-- 아래 테이블의 sku 컬럼에 프리픽스 인덱스를 걸려고 합니다.
DROP TABLE IF EXISTS s15_ex_sku;
CREATE TABLE s15_ex_sku (id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(40) NOT NULL) ENGINE=InnoDB;
-- 앞부분이 'PROD-2024-' 로 공통, 뒤가 구별되는 형태
INSERT INTO s15_ex_sku (sku)
SELECT CONCAT('PROD-2024-', LPAD(n, 8, '0')) FROM tally WHERE n <= 5000;

-- (7-1) LEFT(sku, N) 의 distinct 수를 N=8,10,12,14,16 에 대해 구해서,
--       full distinct 에 근접하는 최소 N 을 찾으세요.
-- (7-2) 그 길이로 프리픽스 인덱스를 만들고, 이 sku 설계에서 왜 짧은 프리픽스가
--       쓸모없는지 주석으로 설명하세요.

-- 여기에 작성:

DROP TABLE IF EXISTS s15_ex_sku;


-- ---------------------------------------------------------------------
-- 안전 정리 — 이 파일을 통째로 실행했다면 access_logs 에 남은 실습 인덱스를 제거
--   (문제를 손으로 풀 때는 각 문제에서 이미 DROP 했을 수 있어 IF EXISTS 로 감쌉니다)
-- ---------------------------------------------------------------------
-- 이 파일이 setup 으로 만든 idx_dur 을 정리합니다(문제 6에서 직접 DROP 했다면 --force 로 넘어감).
ALTER TABLE access_logs DROP INDEX idx_dur;

-- 남은 인덱스가 있는지 최종 확인 — PRIMARY 만 남아야 정상입니다.
SELECT DISTINCT INDEX_NAME AS remaining_index
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'access_logs';

-- =====================================================================
-- Step 15 — 인덱스 : solution.sql  (정답 + 해설)
-- ---------------------------------------------------------------------
-- ⚠️ access_logs 에 만든 인덱스는 각 문제 끝에서 DROP 합니다.
-- =====================================================================
USE shop;


-- ---------------------------------------------------------------------
-- [정답 1] 느린 쿼리에 맞는 인덱스 설계
-- ---------------------------------------------------------------------
-- 조건: customer_id = ? (등치), method = ? (등치), ORDER BY logged_at DESC
-- 규칙: 등치 컬럼 → 정렬 컬럼 순서. 등치가 둘이면 순서는 무관하지만 정렬 컬럼은 반드시 뒤.
ALTER TABLE access_logs ADD INDEX idx_q1 (customer_id, method, logged_at);

EXPLAIN SELECT log_id, status_code, duration_ms FROM access_logs
WHERE customer_id = 15 AND method = 'POST' ORDER BY logged_at DESC LIMIT 20;
-- 결과: type: ref, key: idx_q1, Extra: "Using where; Backward index scan"
--   → filesort 없음! (customer_id, method) 로 좁힌 구간이 logged_at 로 정렬돼 있어
--     DESC 는 뒤에서부터 읽으면 됨(Backward index scan).
--
-- 해설:
--   * status_code, duration_ms 는 SELECT 목록에만 있고 조건/정렬에 없으므로 인덱스에 넣지 않아도 됨.
--     (넣으면 커버링까지 되지만 인덱스가 커짐. LIMIT 20 이라 테이블 접근 20번은 저렴하므로 굳이 불필요.)
--   * 순서를 (logged_at, customer_id, method) 로 하면? 선두가 정렬 컬럼이라
--     customer_id 등치 조건이 인덱스 탐색을 못 해서 훨씬 나쁨.
ALTER TABLE access_logs DROP INDEX idx_q1;


-- ---------------------------------------------------------------------
-- [정답 2] 이 복합 인덱스는 어떤 쿼리를 커버하나
-- ---------------------------------------------------------------------
ALTER TABLE access_logs ADD INDEX idx_test (customer_id, status_code, logged_at);

-- (a) WHERE customer_id = 7            → 탄다 (ref). 선두 컬럼 등치
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7;

-- (b) customer_id = 7 AND status_code = 200 → 탄다 (ref, key_len 6). 앞 두 컬럼 등치
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7 AND status_code = 200;

-- (c) WHERE status_code = 200         → 못 탄다 (type: index = 인덱스 풀스캔).
--     선두 컬럼(customer_id)이 조건에 없어 탐색 불가.
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE status_code = 200;

-- (d) customer_id = 7 AND logged_at >= '2024-06-01' → 탄다 (range).
--     중간 컬럼(status_code)을 건너뛰므로 8.0 스킵 스캔이 관여
--     (Extra: Using index for skip scan). customer_id 등치 + logged_at 범위.
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE customer_id = 7 AND logged_at >= '2024-06-01';

-- (e) 세 컬럼 모두 → 탄다 (range). 앞 두 등치 + 마지막 범위. 인덱스를 가장 잘 활용.
EXPLAIN SELECT COUNT(*) FROM access_logs
WHERE customer_id = 7 AND status_code = 200 AND logged_at >= '2024-06-01';

-- 정리: (a)O (b)O (c)X (d)O(스킵스캔) (e)O
--   핵심은 (c). "인덱스의 두 번째/세 번째 컬럼만으로는 탐색할 수 없다" = 선두 컬럼 규칙.
ALTER TABLE access_logs DROP INDEX idx_test;


-- ---------------------------------------------------------------------
-- [정답 3] 커버링 인덱스로 Using index 만들기
-- ---------------------------------------------------------------------
-- 쿼리가 참조하는 컬럼: customer_id(조건+GROUP), method(GROUP+SELECT). 딱 두 개.
-- 두 컬럼을 다 담는 인덱스 → 테이블 접근 없이 처리 → Using index
ALTER TABLE access_logs ADD INDEX idx_q3 (customer_id, method);

EXPLAIN SELECT customer_id, method, COUNT(*)
FROM access_logs WHERE customer_id = 3 GROUP BY customer_id, method;
-- 결과: type: ref, key: idx_q3, Extra: Using index
--   → customer_id=3 구간 안에서 method 가 이미 정렬돼 있어 GROUP BY 도 인덱스로 처리.
--     COUNT(*) 는 인덱스 항목만 세면 되므로 테이블을 안 읽음.
ALTER TABLE access_logs DROP INDEX idx_q3;


-- ---------------------------------------------------------------------
-- [정답 4] 인덱스를 못 타는 쿼리 고치기 (sargable 화)
-- ---------------------------------------------------------------------
ALTER TABLE access_logs ADD INDEX idx_time (logged_at);

-- (a) YEAR(logged_at) = 2024  → 함수 때문에 못 탐 (type: index)
--     고침: 컬럼에 손대지 말고 범위로
EXPLAIN SELECT COUNT(*) FROM access_logs                        -- BEFORE: type index
WHERE YEAR(logged_at) = 2024;
EXPLAIN SELECT COUNT(*) FROM access_logs                        -- AFTER: type range
WHERE logged_at >= '2024-01-01' AND logged_at < '2025-01-01';

-- (b) logged_at + INTERVAL 0 DAY >= '2024-06-01'  → 컬럼에 연산 → 못 탐
--     고침: 연산을 없앤다
EXPLAIN SELECT COUNT(*) FROM access_logs                        -- AFTER
WHERE logged_at >= '2024-06-01';

-- (c) DATE(logged_at) = '2024-06-15'  → 함수 → 못 탐
--     고침: 그날 00:00 이상 ~ 다음날 00:00 미만 범위로
EXPLAIN SELECT COUNT(*) FROM access_logs                        -- AFTER: type range
WHERE logged_at >= '2024-06-15' AND logged_at < '2024-06-16';

-- 교훈: "컬럼을 가공하지 말고, 비교 대상(리터럴) 쪽을 가공하라."
--   YEAR(col)=2024  대신  col >= '2024-01-01' AND col < '2025-01-01'
--   이것이 sargable(Search ARGument ABLE) 쿼리의 핵심입니다.
ALTER TABLE access_logs DROP INDEX idx_time;


-- ---------------------------------------------------------------------
-- [정답 5] 선택도로 복합 인덱스 컬럼 순서 정하기
-- ---------------------------------------------------------------------
SELECT COUNT(DISTINCT customer_id) AS cust_card,   -- 30
       COUNT(DISTINCT path)        AS path_card    -- 8
FROM access_logs;

-- 답: (customer_id, path) 가 낫다.
--   둘 다 등치 조건일 때는 "카디널리티가 높은(값이 더 다양한) 컬럼을 선두로" 둡니다.
--   customer_id(30종)로 먼저 좁히면 1/30 로 줄지만, path(8종)로 먼저 좁히면 1/8 밖에 못 줄입니다.
--   선두에서 더 많이 좁힐수록 뒤 컬럼이 훑을 범위가 작아져 유리합니다.
--
--   다만 실무에서는 카디널리티만으로 정하지 않습니다:
--     * "customer_id 만으로 조회하는 쿼리"도 많다면 (customer_id, path) 가 그 쿼리까지 커버(선두 컬럼 규칙).
--     * 반대로 "path 만으로 조회"가 잦다면 그쪽을 선두로 두거나 별도 인덱스를 고려.
--   → 컬럼 순서는 "카디널리티 + 실제 쿼리 패턴"을 함께 봅니다.


-- ---------------------------------------------------------------------
-- [정답 6] 인비저블 인덱스로 삭제 안전성 실험
-- ---------------------------------------------------------------------
ALTER TABLE access_logs ADD INDEX idx_dur (duration_ms);

-- (6-1) 이 쿼리는 idx_dur 을 쓴다 (type: range, Using index)
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE duration_ms BETWEEN 100 AND 110;

-- (6-2) 지우지 않고 숨긴다 → 옵티마이저가 못 봄 → 풀스캔(type: ALL)으로 퇴화
ALTER TABLE access_logs ALTER INDEX idx_dur INVISIBLE;
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE duration_ms BETWEEN 100 AND 110;
--   → type: ALL. "이 인덱스를 지우면 이 쿼리가 풀스캔이 되겠구나"를 실제 삭제 없이 확인.
--     성능이 나빠지면 아래처럼 즉시 되돌리면 됨(재생성보다 훨씬 빠름).

-- (6-3) 되돌린 뒤 정리
ALTER TABLE access_logs ALTER INDEX idx_dur VISIBLE;
EXPLAIN SELECT COUNT(*) FROM access_logs WHERE duration_ms BETWEEN 100 AND 110;   -- 다시 range
ALTER TABLE access_logs DROP INDEX idx_dur;
--
-- 결론: 이 실험에서 idx_dur 은 숨기자마자 쿼리가 풀스캔이 됐으므로 "필요한 인덱스"입니다. 지우면 안 됩니다.


-- ---------------------------------------------------------------------
-- [정답 7] 프리픽스 인덱스 적정 길이 찾기
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s15_ex_sku;
CREATE TABLE s15_ex_sku (id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(40) NOT NULL) ENGINE=InnoDB;
INSERT INTO s15_ex_sku (sku)
SELECT CONCAT('PROD-2024-', LPAD(n, 8, '0')) FROM tally WHERE n <= 5000;

-- (7-1) 프리픽스 길이별 distinct
SELECT COUNT(DISTINCT sku)           AS full_distinct,   -- 5000
       COUNT(DISTINCT LEFT(sku,  8)) AS p8,              -- 1
       COUNT(DISTINCT LEFT(sku, 10)) AS p10,             -- 1
       COUNT(DISTINCT LEFT(sku, 12)) AS p12,             -- 1
       COUNT(DISTINCT LEFT(sku, 14)) AS p14,             -- 1
       COUNT(DISTINCT LEFT(sku, 16)) AS p16,             -- 51
       COUNT(DISTINCT LEFT(sku, 18)) AS p18              -- 5000
FROM s15_ex_sku;

-- (7-2) 결과 해석:
--   sku = 'PROD-2024-XXXXXXXX' 형태. 앞 'PROD-2024-' 가 10글자로 모두 공통입니다.
--   그래서 LEFT(sku, 10) 까지는 전부 같은 값(distinct = 1) → 프리픽스가 완전히 무력.
--   숫자 부분(8자리)이 앞에서부터 조금씩 갈라지므로 16글자에서 51종, 18글자에서야 5000종(=full).
--
--   → 이 sku 설계에서 유효한 프리픽스는 18글자인데, sku 전체가 18글자입니다.
--     즉 "프리픽스로 아낄 공간이 없습니다." 프리픽스 인덱스가 무의미한 대표적 경우입니다.
--
--   교훈: 프리픽스 인덱스는 "구별되는 정보가 문자열 앞쪽에 있을 때"만 효과적입니다.
--     공통 접두사(PROD-2024- 같은)가 길면, 그 뒤까지 잘라야 해서 프리픽스의 이점이 사라집니다.
--     이런 데이터라면 (a) 공통 접두사를 빼고 저장하거나, (b) 숫자부만 별도 컬럼으로 분리하는 게 낫습니다.
ALTER TABLE s15_ex_sku ADD INDEX idx_sku_prefix (sku(18));   -- 굳이 만든다면 18
DROP TABLE IF EXISTS s15_ex_sku;

SELECT 'Step 15 solution 완료' AS msg;