Ch12 - Index

더미데이터 생성

• 5분 ~ 10분정도 소요
  
  
  
• 쓰레드가 달라서 아무런 영향이 없음.

인덱스 생성

select * from member_tb where nickname='닉네임7';
CREATE INDEX nickname_idx ON member_tb ( nickname );
select * from member_tb where nickname='닉네임7';

select *
from member_tb
where money between 10000 and 20000;

create index money_idx on member_tb (money);
select *
from member_tb
where money between 10000 and 20000;

※ InnoDB 스토리지 엔진은 B+Tree를 사용

SELECT *
FROM member_tb
WHERE money between 10000 and 20000; 

SELECT avg(age)
FROM member_tb
WHERE money between 10000 and 20000;

// 복합 인덱스 생성
CREATE INDEX money_age_idx ON member_tb ( money, age );
SELECT * FROM member_tb WHERE money between 10000 and 20000; // 0.000
SELECT avg(age) FROM member_tb WHERE money between 10000 and 20000; // 0.031

인덱스 초기화 및 UK 설정

SHOW INDEX FROM member_tb;

DROP INDEX nickname_idx ON member_tb;
DROP INDEX uk_nickname ON member_tb;
DROP INDEX money_idx ON member_tb;
DROP INDEX money_age_idx ON member_tb;

ALTER TABLE member_tb DROP INDEX uk_nickname;