클러스터형 인덱스에 새로운 데이터 입력
use testdb;
INSERT INTO clustertbl VALUES('FNT','푸니타');
INSERT INTO clustertbl VALUES('KAI','카아이');
--> USERID 가 PRIMARY키로 걸려있기 때문에
스펠링 순으로 정렬된다 .
----------------------------------------------
보조 인덱스에 새로운 데이터 입력
INSERT INTO secondarytbl VALUES('FNT','푸니타');
INSERT INTO secondarytbl VALUES('KAI','카아이');
--> USERID가 UNIQUE키로 걸려있기 때문에
정렬이 되지 않고 기존 데이터 맨 마지막에 추가한다.
----------------------------------------------
인덱스 내부작동
-클러스터형 인덱스의 생성시에는 데이터 페이지 전체 다시 정렬
이미 대용량의 데이터가 입력된 상태라면 클러스터형 인덱스 생성하는 것은 심각한 시스템 부하
-인덱스 자체의 리프페이지가 곧 데이터
인덱스 자체에 데이터가 포함되어 있음
-클러스터형 인덱스는 보조 인덱스보다 검색 속도는 빠름
데이터 입력시 더 느림
----------------------------------------------
보조 인덱스 생성시 별도의 페이지에 인덱스 구성
인덱스 자체의 리프 페이지는 데이터가 아니고 데이터가 위치하는 주소
클러스터형 검색속도는 더 느림 / 데이터의 입력 수정 삭제는 덜 느림
보조 인덱스는 여러 개 생성할 수 있음
-남용할 경우에는 시스템 성능을 떨어뜨리는 결과 발생
----------------------------------------------
클러스터형 인덱스와 보조 인덱스가 혼합 되어있을 경우
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
DROP TABLE IF EXISTS mixedtbl;
CREATE TABLE mixedtbl -- Cluster Table 약자
(userID CHAR(8),
name VARCHAR(10),
addr CHAR(2)
);
INSERT INTO mixedtbl VALUES('LSG', '이승기', '서울');
INSERT INTO mixedtbl VALUES('KBS', '김범수', '경남');
INSERT INTO mixedtbl VALUES('KKH', '김경호', '전남');
INSERT INTO mixedtbl VALUES('JYP', '조용필', '경기');
INSERT INTO mixedtbl VALUES('SSK', '성시경', '서울');
INSERT INTO mixedtbl VALUES('LJB', '임재범', '서울');
INSERT INTO mixedtbl VALUES('YJS', '윤종신', '경남');
INSERT INTO mixedtbl VALUES('EJW', '은지원', '경북');
INSERT INTO mixedtbl VALUES('JKW', '조관우', '경기');
INSERT INTO mixedtbl VALUES('BBK', '바비킴', '서울');
ALTER TABLE mixedtbl
ADD CONSTRAINT PK_mixedtbl_userID
PRIMARY KEY (userID);
--> USERID에 PRIMARY키 지정
ALTER TABLE mixedtbl
ADD CONSTRAINT UK_mixedtbl_name
UNIQUE (name);
--> name에 UNIQUE키 지정
-------------------------------------------------------
<인덱스 제거>
인덱스 삭제 형식
형식 : DROP INDEX 인덱스이름 ON 테이블이름;
-------------------------------------------------------
< 인덱스 성능 비교 >
인덱스 없는 경우, 클러스터형 인덱스 , 보조 인덱스를 설정하여 쿼리 속도 비교
서버 부하 비교
-실습할 데이터 베이스 생성
CREATE DATABASE IF NOT EXISTS indexdb;
employees의 employees의 개수를 파악
USE INDEXdb;
SELECT COUNT(*) FROM employees.employees;
테이블 3개로 복사
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();
-------------------------------------------------------
SHOW TABLE STATUS;
-세 테이블 모두 인덱스 없음
[실습]
emp_c에는 클러스터형 인덱스 생성
--> ALTER TABLE emp_c ADD PRIMARY KEY(emp_no);
emp_Se에는 보조 인덱스를 생성
--> ALTER TABLE emp_Se ADD INDEX idx_emp_no (emp_no);
다시 데이터를 5건씩만 확인
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp_c LIMIT 5;
SELECT * FROM emp_Se LIMIT 5;
-------------------------------------------------------
인덱스 성능 비교
생성한 인덱스 적용
ANALYZE TABLE emp, emp_c , emp_Se; --> ANALYZE를 해야 인덱스 적용이 완료 된다.
SHOW INDEX FROM 테이블명;
-------------------------------------------------------
MYSQL 전체의 시스템 상태 초기화
workbench 종료
CMD창 관리자 실행
NET STOP Mysql
NET START Mysql ( 컴퓨터 재부팅한 효과)
다시 workbench 실행
-------------------------------------------------------
이제 테이블을 조회할 때 인덱스를 사용하는 성능의 차이
USE indexdb;
-------------------------------------------------------
인덱스 없는 EMP 테이블 조회
-SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; ~ 쿼리 실행 전의 읽은 페이지 수
-SELECT * FROM emp WHERE emp_no = 100000;
-SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; ~ 쿼리 실행후에 읽은 페이지 수
-------------------------------------------------------
emp_no < 11000 범위로 다시 확인 돋보기
SELECT * FROM emp WHERE emp_no < 11000;
SELECT * FROM emp_c WHERE emp_no < 11000;
SELECT * FROM emp_se WHERE emp_no < 11000;
-------------------------------------------------------
<인덱스의 성능 비교>
데이터 중복도에 따른 인덱스 효용
인덱스를 만들지 않은 EMP테이블의 GENDER(성별)열에 인덱스 생성
ALTER TABLE emp ADD INDEX idx_gender (gender);
ANALYZE TABLE Emp; --생성한 인덱스를 통계에 적용 시킴
SHOW INDEX FROM Emp;
SELECT * FROM emp WHERE gender = 'M' LIMIT 500000;
--쿼리비용 약 2만정도 나옴
--DATA READ 19MB 읽음
-------------------------------------------------------
<실습 결과>
데이터의 중복도가 높은 경우에 ,
인덱스를 사용하는것이 효과적
하지만 인덱스의 관리비용과 INSERT등의 구문에서는
오히려 성능이 저하될 수 있다는 점 등을
고려하면 인덱스가 반드시 바람직하다고 보기는 어려움
-------------------------------------------------------
결론: 인덱스를 생성해야 하는 경우 , 안되는 경우
인덱스는 열 단위에 생성
-두 개 이상의 열을 조합해서 인덱스 생성 가능
WHERE절에서 사용되는 열에 인덱스를 만들어야함
-테이블 조회시 WHERE절의 조건에 해당 열이 나오는 경우에만 인덱스 주로 사용
WHERE절에 사용되더라도 자주 사용해야 가치가 있음
-SELECT문이 자주 사용 되어야 효과적
-INSERT문이 자주 사용되고 생성된 인덱스가 클러스터형이면 효율 감소
데이터의 중복도가 높은 열은 인덱스를 만들어도 효과가 없음
-인덱스의 관리비용 때문에 인덱스가 없는 편이 나은 경우도 있음
외래 키 지정한 열에는 자동으로 외래 키 인덱스 생성
-------------------------------------------------------
JOIN에 자주 사용되는 열에는 인덱스를 생성해 주는 것이 좋음
INSERT / UPDATE / DELETE가 얼마나 자주 일어나는지 고려 해야함
-인덱스는 단지 읽기에서만 성능 향상
-데이터의 변경에서는 오히려 부담
클러스터형 인덱스는 테이블당 하나만 생성 가능
-클러스터형 인덱스를 생성할 열은 (BETWEEN, >,< 등의 조건)
로 사용하거나 집계 함수를 사용하는 경우 아주 적절하게 사용
클러스터형 인덱스가 테이블에 아예 없는것이 좋은 경우도 있음
사용하지 않는 인덱스는 제거
-공간 확보 및 데이터의 입력 시에 발생되는 부하 줄임
-------------------------------------------------------
뷰 <VIEW>
일반 사용자 입장에서 테이블과 동일하게 사용하는 객체
뷰를 생성한 후에는 테이블처럼 접근 가능하여 동일한 결과를 얻을 수 있음
사용자 > 조회,변경 > 뷰(SELECT문) > 쿼리 실행 > 테이블(데이터)
사용자 < 결과 < 뷰(SELECT문) <쿼리 결과값 < 테이블(데이터)
*뷰와 테이블의 차이는 뷰는 쿼리를 사용하는것이지
테이블의 객체를 가져오는것이 아니다.*
뷰의 장점 : 보안에 도움(사용자가 중요정보에 바로 접근X)
복잡한 쿼리 단순화 : 긴 쿼리를 뷰로 작성, 뷰를 테이블처럼 사용 가능
뷰의 약점 : CPU사용으로 인한 처리 속도가 저하된다.
부모쿼리와 어떤 식으로든 연관된 서브쿼리라서 부모쿼리의
데이터가 변경되는 것에 영향을 받는다.
-------------------------------------------------------
<뷰 작성과 삭제 >
서브쿼리
-SELECT * FROM (SELECT * FROM SAMPLE54) as t;
CREATE VIEW 뷰이름 AS SELECT 명령
-위의 서브쿼리를 뷰로 구성해보기
-뷰 삭제 하기
DROP VIEW 뷰이름
-------------------------------------------------------
<뷰의 열 이름 지정하기>
#괄호를 치면 변수처럼 저장이 가능하다.
CREATE VIEW sample54v(n,v,v2)AS SELECT no,a,a*2 FROM SAMPLE54;
SHOW TABLES
SELECT * FROM sample54v;
SELECT * FROM sample54v WHERE n = 1;
SHOW tables;
#뷰는 실제 객체가 아니기 때문에 ALTER VIEW는 사용이 불가능하다.
오로지 불러올때 이름만 바꿔서 불러올 수 있다.
-------------------------------------------------------
<집합 연산>
[합집합] 두 쿼리의 결과를 행으로 합치는 것
[실습] 두테이블의 합집합 구하기 !
SELECT * FROM SAMPLE71_a
UNION
SELECT * FROM SAMPLE71_b;
--> a열 안에 sample71_B의 값이 들어간다.
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 11 |
+------+
-------------------------------------------------------
[실습1] 샘플71_a 열을 오름차순 정렬한 a열과
71_b의 b열의 합집합 구하기
SELECT a as c FROM sample71_a
UNION
SELECT b as c FROM sample71_b order by c;
-------------------------------------------------------
[실습2] 샘플 71_a와 샘플 71_b를 UNION ALL 사용 합집합 구하기
(중복허용)
SELECT a as c FROM sample71_a
UNION ALL
SELECT b as c FROM sample71_b order by c;
-------------------------------------------------------
<테이블 결합 JOIN>**
두개이상의 테이블 하나로 만드는것 (원본 테이블은 관리가 되고 있다.)
종류 : INNER JOIN, OUTER JOIN, CROSS JOIN, SELF JOIN
데이터베이스의 테이블
-중복과 공간 낭비를 피하고 데이터의 무결성을 위해서 여러 개의 테이블을 분리하여 저장
-분리된 테이블들은 서로 관계(Relation)을 가짐
-1대 다 보편적
-------------------------------------------------------
CROSS JOIN (상호 조인)
한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능
CROSS JOIN 의 결과 개수 = 두 테이블 개수를 곱한 개수
-테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용
-ON구문을 사용할 수 없다. (ON : 어떤것을 기준으로 참조해서 조인)
곱집합 : 각 집합에서 임의의 원소를 가져와 만들 수 있는 조합을 하나의 튜플로 표현
A ={1,3}
B ={2,4}
A x B = {(1,2),(1,4),(3,2),(3,4)}
SELECT * FROM 테이블명1, 테이블명2
[실습] 샘플 72_x와 샘플 72_y를 교차결합 후 행의 개수 확인하기
--> SELECT * FROM sample72_x, sample72_y;
--> SELECT COUNT(*) FROM sample72_x, sample72_y; # 9개
+------+------+
| x | y |
+------+------+
| C | 1 |
| B | 1 |
| A | 1 |
| C | 2 |
| B | 2 |
| A | 2 |
| C | 3 |
| B | 3 |
| A | 3 |
+------+------+
-------------------------------------------------------
<내부 결합>
-조인 중에서 가장 많이 사용하는조인
-대개의 업무에서 조인은 INNER JOIN 사용
-일반적으로 JOIN이라 하는것은 'INNER JOIN' 지칭
SELECT * FROM 상품;
SELECT * FROM 재고수;
DESC 상품;
DESC 재고수;
ALTER TABLE 재고수 MODIFY 상품코드 char(4)PRIMARY KEY;
[실습1] 상품 테이블과 재고수 테이블을 교차결합 (곱집합)
--> SELECT * FROM 상품,재고수;
[실습2] 상품 테이블의 상품코드와 재고수 테이블의 상품코드가 서로 같은 행을 검색
-->
SELECT * FROM 상품,재고수
WHERE 상품.상품코드 = 재고수.상품코드;
[실습3] 상품분류가 '식료품'인 조건 추가 또한, 상품명과 재고수만 반환 하도록 SELECT 에 열을 지정
-->
SELECT * FROM 상품.상품명, 재고수.재고수 from 상품, 재고수
WHERE 상품.상품코드 = 재고수.상품코드
AND 상품.상품분류 = '식료품';
SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건
[실습] 위의 식 활용 앞 장의 내용 구현하기
SELECT * FROM 상품 INNER JOIN 재고수 ON 상품.상품코드 = 재고수.상품코드;
[실습]메이커와 상품2 테이블에서 상품명과 메이커명을 같이 출력해보기
SELECT 상품2.상품명, 메이커.메이커명 FROM 상품2 INNER JOIN 메이커
on 상품2.메이커코드 = 메이커.메이커코드
-------------------------------------------------------
<자기 결합>
SELECT S1.상품명, S2.상품명
FROM 상품 S1 INNER JOIN 상품 S2
ON S1.상품코드 = S2.상품코드
-------------------------------------------------------
<외부 결합>
조인의 조건에 만족되지 않는 행 까지도 포함 시키는 것
SELECT <열 목록>
FROM <첫번째 테이블(LEFT 테이블)>
<LEFT | RIGHT | FULL> OUTER JOIN <두번째 테이블(RIGHT 테이블)>
ON <조인될 조건>
[WHERE 검색조건];
[실습] 아래 코드 실행
[1]SELECT * FROM 상품3;
[2]SELECT * FROM 재고수;
[3]SELECT * FROM 상품3,재고수;
[4]SELECT 상품3.상품명, 재고수.재고수
FROM 상품3 INNER JOIN 재고수
ON 상품3.상품코드 = 재고수.상품코드;
[실습] 앞의 코드에서 INNER JOIN을 LEFT JOIN으로 변경 후 실행하기
SELECT 상품3.상품명, 재고수.재고수
FROM 상품3 LEFT JOIN 재고수
ON 상품3.상품코드 = 재고수.상품코드;
--> 매칭 된게 없으면 NULL값으로 포함
[실습2] 앞의 코드에서 INNER JOIN을 RIGHT JOIN으로 변경 후 실행하기
SELECT 상품3.상품명, 재고수.재고수
FROM 상품3 RIGHT JOIN 재고수
ON 상품3.상품코드 = 재고수.상품코드;
-------------------------------------------------------
<테이블 스페이스>
-물리적인 공간 의미
-데이터베이스는 논리적 공간
-테이블스페이스를 지정하지 않은 경우
시스템 테이블스페이스에 테이블 저장됨
시스템 변수 innodb_data_file_path에 관련내용 저장됨
[실습] 아래 코드 실행
SHOW VARIABLES LIKE 'innodb_data_file_path';
-------------------------------------------------------
<성능 향상을 위한 테이블 스페이스 추가>
소용량 데이터 사용할때는 필요 X 단, 대용량 데이터를 운영할 경우에는 성능 향상을 위해
테이블 스페이스의 분리를 적극 고려
각 테이블이 별도의 테이블스페이스에 저장되도록 시스템 변수 innodb_file_per_table이 ON으로 설정
확인방법: SHOW VARIABLES LIKE 'innodb_file_per_table';
테이블 스페이스 3개 생성
CREATE TABLESPACE ts_a ADD DATAFILE 'ts_a.ibd';
CREATE TABLESPACE ts_b ADD DATAFILE 'ts_b.ibd';
CREATE TABLESPACE ts_c ADD DATAFILE 'ts_c.ibd';
-------------------------------------------------------
[각 테이블스페이스에 파일 생성]
USE sample;
CREATE TABLE table_a(id INT) TABLESPACE ts_a;
[테이블을 만든 후에 ALTER TABLE 문으로 테이블스페이스 변경 가능]
CREATE TABLE table_b (id INT)
ALTER TABLE table_b TABLESPACE ts_b;
-------------------------------------------------------
[대용량의 테이블을 복사한 후 테이블 스페이스 지정]
CREATE TABLE table_c (SELECT * FROM employees.salaries);
ALTER TABLE table_c TABLESPACE ts_c;
-------------------------------------------------------
[쿼리 응답시간 지정 없애기]
워크벤치 > EDIT >PREFERENCES > 왼쪽 SQL EDITOR 선택
Mysql SESSION
DBMS CONNECTION read timeout interval (in seconds) 0
DBMS connection timeout interval (in seconds) 0
설정 하기
-------------------------------------------------------
<데이터 모델링 >
ex)
집을 지을때
지반설계 : 데이터베이스 설계
건물 설계 : 소프트웨어 설계
< 순서 >
-요구사항 수집 및 분석
-설계
-구현
-운영
-감시 및 개선
