인덱스 Index

SELECT * FROM employees WHERE id = 35;

-- 기본 인덱스 생성
CREATE INDEX idx_name ON employees(name);

-- 인덱스 이름 규칙: idx_테이블명_컬럼명
CREATE INDEX idx_employees_email ON employees(email);

-- 여러 컬럼을 하나의 인덱스로
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- 순서가 중요! (department → salary 순으로 정렬)

CREATE INDEX idx_abc ON table(a, b, c);

-- ✔ 인덱스 사용 O
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- ⚠️ 인덱스 일부만 사용
WHERE a = 1 AND c = 3  -- a만 인덱스 사용

-- ✘ 인덱스 사용 X (첫 컬럼 a가 없음)
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

-- 중복 값 허용 안 함
CREATE UNIQUE INDEX idx_email ON employees(email);

-- PRIMARY KEY는 자동으로 유니크 인덱스 생성
CREATE TABLE users (
    id INT PRIMARY KEY  -- 자동으로 유니크 인덱스
);

-- 텍스트 검색용 (LIKE '%keyword%' 대체)
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 사용
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('검색어');

-- 인덱스 삭제
DROP INDEX idx_name ON employees;

-- MySQL
ALTER TABLE employees DROP INDEX idx_name;

-- 100만 건 데이터에서 검색
-- 인덱스 없음: 0.5초 (Full Scan)
-- 인덱스 있음: 0.001초 (Index Scan)

SELECT * FROM employees WHERE id = 12345;

-- 인덱스가 이미 정렬되어 있어서 빠름
CREATE INDEX idx_salary ON employees(salary);

SELECT * FROM employees ORDER BY salary;
-- 별도 정렬 작업 불필요 (Using index)

CREATE INDEX idx_department ON employees(department);

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;
-- 인덱스를 활용한 빠른 그룹화

CREATE INDEX idx_salary ON employees(salary);

-- 인덱스의 첫 번째/마지막 값만 읽으면 됨
SELECT MIN(salary) FROM employees;  -- 0.001초
SELECT MAX(salary) FROM employees;  -- 0.001초

-- 외래키에 인덱스 생성
CREATE INDEX idx_dept_id ON employees(dept_id);

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
-- JOIN 조건 컬럼에 인덱스 → 빠른 매칭

INSERT INTO employees VALUES (101, '홍길동', 50000);

-- 인덱스 컬럼 수정 시 느림
UPDATE employees SET salary = 60000 WHERE id = 10;

-- phone에 인덱스 없음 → 빠름
UPDATE employees SET phone = '010-1234-5678' WHERE id = 10;

DELETE FROM employees WHERE id = 10;

-- 데이터 변경이 많으면 인덱스 단편화 발생
-- 주기적으로 인덱스 재구성 필요
ALTER TABLE employees ENGINE=InnoDB;  -- 재구성

-- 또는
OPTIMIZE TABLE employees;  -- 최적화

-- email로 자주 검색
SELECT * FROM users WHERE email = 'hong@example.com';

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

-- 높은 카디널리티 (인덱스 효과적) ✔
email    → 10,000명 중 10,000개 고유값 (100%)
주민번호  → 10,000명 중 10,000개 고유값 (100%)
전화번호  → 10,000명 중 9,800개 고유값 (98%)

-- 낮은 카디널리티 (인덱스 비효율) ✘
성별     → 10,000명 중 2개 고유값 (남/여)
결혼여부  → 10,000명 중 2개 고유값 (Y/N)
등급     → 10,000명 중 5개 고유값 (VIP, Gold, Silver...)

-- 성별 인덱스 생성 (비효율)
CREATE INDEX idx_gender ON employees(gender);

SELECT * FROM employees WHERE gender = 'M';
-- 전체의 50%를 조회 → Full Scan이 더 빠를 수 있음

-- 최신 게시글 조회
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

-- 인덱스 생성
CREATE INDEX idx_created_at ON posts(created_at);

-- 외래키에 인덱스 필수
CREATE INDEX idx_dept_id ON employees(dept_id);
CREATE INDEX idx_user_id ON orders(user_id);

-- JOIN 성능 향상
SELECT * FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- 조회는 많고, 수정은 적은 경우
-- 예: 코드 테이블, 카테고리 테이블, 지역 테이블
CREATE INDEX idx_code ON code_master(code);

-- 데이터 100건 미만 → 인덱스 불필요
-- Full Scan이 더 빠름

-- 실시간 업데이트되는 컬럼 (재고 수량, 조회수 등)
UPDATE products SET view_count = view_count + 1;
-- 인덱스 재구성 비용 > 검색 성능 향상

-- 주소 컬럼은 검색 조건으로 거의 안 씀
CREATE INDEX idx_address ON users(address);  -- 불필요

-- 전체의 80% 이상 조회 → Full Scan이 더 빠름
SELECT * FROM employees WHERE salary > 30000;
-- 대부분의 직원이 3만원 이상 받으면 인덱스 무의미

-- ✘ 인덱스 사용 불가
CREATE INDEX idx_hire_date ON employees(hire_date);

SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
-- hire_date에 함수 적용 → 인덱스 무용지물

-- ✔ 인덱스 사용 O
SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

-- ✘ 인덱스 사용 불가
SELECT * FROM employees WHERE salary * 1.1 > 50000;

-- ✔ 인덱스 사용 O
SELECT * FROM employees WHERE salary > 50000 / 1.1;

CREATE INDEX idx_name ON employees(name);

-- ✔ 인덱스 사용 O
SELECT * FROM employees WHERE name LIKE '김%';

-- ✘ 인덱스 사용 불가
SELECT * FROM employees WHERE name LIKE '%길동';
SELECT * FROM employees WHERE name LIKE '%길%';

-- ✘ 인덱스 사용 불가 (대부분의 DBMS)
SELECT * FROM employees WHERE department != 'IT';
SELECT * FROM employees WHERE NOT department = 'IT';

-- ✔ 인덱스 사용 O
SELECT * FROM employees WHERE department = 'IT';

-- ✘ 인덱스 효율 떨어짐
SELECT * FROM employees 
WHERE name = '홍길동' OR email = 'hong@example.com';

-- ✔ UNION으로 대체 (각각 인덱스 사용)
SELECT * FROM employees WHERE name = '홍길동'
UNION
SELECT * FROM employees WHERE email = 'hong@example.com';

-- ✘ 인덱스 사용 불가
CREATE INDEX idx_phone ON users(phone);  -- VARCHAR 타입

SELECT * FROM users WHERE phone = 01012345678;
-- 숫자를 문자와 비교 → 암시적 형변환 → 인덱스 무용지물

-- ✔ 인덱스 사용 O
SELECT * FROM users WHERE phone = '01012345678';

-- MySQL
SHOW INDEX FROM employees;

-- 결과
Table      | Key_name        | Column_name | Index_type
-----------|-----------------|-------------|------------
employees  | PRIMARY         | id          | BTREE
employees  | idx_department  | department  | BTREE
employees  | idx_email       | email       | BTREE

EXPLAIN SELECT * FROM employees WHERE department = 'IT';

-- 복합 인덱스 생성
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- 커버링 인덱스 활용
SELECT department, salary 
FROM employees 
WHERE department = 'IT';
-- 테이블 접근 없이 인덱스만으로 조회 (최고 성능!)

-- EXPLAIN 결과: Extra = "Using index"

-- ✘ 비효율
CREATE INDEX idx_gender_email ON users(gender, email);
-- gender는 선택도 낮음 (M/F 2개)

-- ✔ 효율적
CREATE INDEX idx_email_gender ON users(email, gender);
-- email은 선택도 높음 (거의 유니크)

-- ✘ 과도한 인덱스
CREATE INDEX idx1 ON employees(name);
CREATE INDEX idx2 ON employees(email);
CREATE INDEX idx3 ON employees(phone);
CREATE INDEX idx4 ON employees(department);
CREATE INDEX idx5 ON employees(salary);
CREATE INDEX idx6 ON employees(hire_date);
-- INSERT/UPDATE 성능 급격히 저하

-- ✔ 필요한 것만 생성 (3~5개 권장)

-- 사용하지 않는 인덱스 찾기 (MySQL)
SELECT * FROM sys.schema_unused_indexes;

-- 삭제
DROP INDEX idx_unused ON employees;

-- 자주 실행되는 쿼리
SELECT * FROM orders 
WHERE customer_id = 100 
AND order_date >= '2024-01-01'
ORDER BY order_date DESC;

-- 최적 인덱스
CREATE INDEX idx_customer_date 
ON orders(customer_id, order_date);
-- customer_id로 필터 → order_date로 정렬 (정렬 불필요)

-- 느린 페이징
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 10000, 10;  -- 10,000개 건너뛰고 10개 조회

-- 빠른 페이징 (커버링 인덱스)
SELECT * FROM posts p
JOIN (
    SELECT id FROM posts 
    ORDER BY created_at DESC 
    LIMIT 10000, 10
) t ON p.id = t.id;

-- 자주 실행되는 쿼리
SELECT * FROM products 
WHERE category = 'Electronics' 
AND price BETWEEN 100000 AND 500000
AND stock > 0;

-- 최적 인덱스
CREATE INDEX idx_category_price_stock 
ON products(category, price, stock);