인덱스 생성/ 변경 / 삭제.MySQL

2023.02.09 - [MySQL] - 인덱스의 내부 작동.MySQL

인덱스의 내부 작동.MySQL

 

이전 글에서 계속됩니다.

 

---

인덱스 생성

인덱스 생성 문법

 

---

인덱스 삭제 형식

간단히 인덱스를 삭제하는 구문

---

-- -------------------------------
-- 인덱스 생성하고 사용하는 실습

use sqldb;
select * from buytbl;
select * from usertbl;

drop table buytbl;
drop table usertbl;

 -- 테이블 생성
 -- 회원 테이블
create table usertbl(
   userID      char(8)         not null   primary key,   -- 사용자 아이디(PK)
    name      varchar(10)      not null,               -- 이름
    birthYear   int          not null,               -- 출생년도
    addr      char(2)         not null,               -- 지역 : 경기, 서울, 경남 식으로 2글자 입력
    mobile1      char(3),                           -- 휴대폰의 국번 : 011, 016, 017, 018, 019, 010               
    mobile2      char(8),                           -- 휴대폰의 나머지 전화번호
    height      smallint,                           -- 키
    mDate      date                              -- 회원 가입일
 );
 
 -- 회원 구매 테이블 (buytbl)
 create table buytbl(
   num         int         auto_increment not null primary key,   -- 순번(PK)
   userID      char(8)      not null,                        -- 아이디(FK)
    prodName   char(6),                                 -- 물품명
    groupName   char(4),                                 -- 분류
    price      int         not null,                        -- 단가
    amount      smallint   not null,                        -- 수량
    foreign key (userID) references usertbl(userID)
);

USE SQLDB;

show tables;

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울',  null,  NULL    , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남',  NULL,  NULL    , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화',  NULL, 30,   2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000,   1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,   5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,   10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'   , '서적', 15,   5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'   , '서적', 15,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화',  NULL, 30,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'   , '서적', 15,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화',  NULL, 30,   2);

show index from usertbl;

show table status like 'usertbl';

-- 보조 인덱스 생성
create index idx_usertbl_addr
   on usertbl(addr);
    
-- 인덱스 생성 후 table에 대한 분석/처리
analyze table usertbl;
show table status like 'usertbl';

-- 출생년도에 고유 보조 인덱스(Unique Secondary Index) 생성 : 중복 값 허용되지 않는다.
-- 실행되지 않음(생성 전 중복이 있는지 확인)
create unique index idx_usertbl_birthYear
   on usertbl(birthYear);

select birthYear from usertbl order by birthYear;

-- 이름에 고유 보조 인덱스 생성
create unique index idx_usertbl_name
   on usertbl(name);
    
show index from usertbl;

-- 동일한 이름의 데이터 입력 : 유니크 제약조건 error
-- 실행되지 않음
insert into usertbl values('GPS', '김범수', 1983, '미국', null, null, 162, null);

-- 인덱스 생성시 2개 이상의 컬럼을 조합해서 인덱스 생성 가능
create index idx_usertbl_name_birthYear
   on usertbl(name, birthYear);

-- 색인 삭제
drop index idx_usertbl_name on usertbl;

show index from usertbl;

select * from usertbl where name = '윤종신' and birthYear = '1969';

 

---

인덱스의 성능비교

-- 인덱스 성능 비교

create database if not exists indexdb;

use indexdb;

select count(*) from employees.employees;

-- employees 테이블에 데이터를 랜덤으로 가져오기
create table emp select * from employees.employees order by rand();
create table emp_c select * from employees.employees order by rand();
create table emp_se select * from employees.employees order by rand();

select * from emp limit 5;
select * from emp_c limit 5;
select * from emp_se limit 5;

-- 테이블 상태 확인
show table status;

show index from emp;
show index from emp_c;
show index from emp_se;

-- emp : 인덱스 없음, emp_c : 클러스터형 인덱스, emp_se : 보조 인덱스
alter table emp_c add primary key(emp_no);
alter table emp_se add index idx_emp_no(emp_no);

select * from emp limit 5;
select * from emp_c limit 5;		-- 클러스터형 (자동 정렬이 되어 있다.)
select * from emp_se limit 5;		-- 보조 인덱스 (정렬이 되지 않는다.)

analyze table emp, emp_c, emp_se;
show table status;

use indexdb;

-- 인덱스 X
show global status like 'Innodb_pages_read';	-- 현재 까지 페이지를 읽은 수		2131
select * from emp where emp_no = 100000;		-- 1037,	- cost : 29384.05
show global status like 'Innodb_pages_read';	-- 현재 까지 페이지를 읽은 수		3168

-- 클러스터형 : 보통 경우 클러스터 형이 더 좋다.
show global status like 'Innodb_pages_read';	-- 현재 까지 페이지를 읽은 수		3168
select * from emp_c where emp_no = 100000;		-- 1,		- cost : 1.00
show global status like 'Innodb_pages_read';	-- 현재 까지 페이지를 읽은 수		3169

-- 보조 인덱스 : 
show global status like 'Innodb_pages_read';	-- 현재 까지 페이지를 읽은 수		3169
select * from emp_se where emp_no = 100000;		-- 1,		- cost : 0.35
show global status like 'Innodb_pages_read';	-- 현재 까지 페이지를 읽은 수		3170