Chapter 08 테이블과 뷰
MySQL 5.0부터 테이블 압축 기능을 제공해 대용량의 데이턱 들어간 테이블 저장공간을 대폭 절약할 수 있다.
CREATE TABLE sanghee (
id INT,
name VARCHAR(255)
) ROW_FORMAT=COMPRESSED;
제약조건이란 데이터의 무결성을 지키이 위한제한된 조건을 의미한다.
기본키 제약조건: 많은 행의 데이터를 구분할 수 있는 식별자.
테이블 정보 확인하기
describe 테이블이름;
속성에 바로 정의하거나, constraint ~ 혹은 alter를 사용해 지정한다.
mysql은 primary ey로 지정하면 항상 키 이름을 primary로 보여주기에 이름을 직접 지정하는 것이 별 읨가 없다. 그러나 foreign key는 하나의 테이블에 여러개가 생성될 수 있음으로 이름을 지정해서 관리하는 것이 편리하다.
CREATE TABLE usertbl (
userId CHAR(8) NOT NULL,
name VARCHAR(10) NOT NULL,
CONSTRAINT pk_usertbl_userId PRIMARY KEY (userId)
);ALTER TABLE usertbl
ADD CONSTRAINT p_usertbl_userId PRIMARY KEY (userId);
테이블에 지정된 키를 보기
show keys from 테이블이름;
만일 '제품코드'만으로는 중복이 될 수 있고 '제품일련번호'도 중복이 될수있다면 이 둘을 합쳐 유일한 값을 만들어 기본키로 사용하게 할 수 있다.
CREATE TABLE prodtbl (
prodCode CHAR(3) NOT NULL,
prodId CHAR(4) NOT NULL,
prodDate DATETIME NOT NULL
);
ALTER TABLE prodtbl
ADD CONSTRAINT pk_prodtbl_prodCode_prodId
PRIMARY KEY (prodCode, prodId);
테이블정보를 확인하면 두 열이 합쳐져 하나의 기본키 제약 조건을 설정하고 있음을 확인 할 수 있다.
show index from prodTbl
foreign key ~ 컬럼명 reference 테이블(primary키)
외래키 두 테이블 사이의 관계를 선언해 데이터 무결성을
보장해주는 역할, 하나의 테이블이 다른 테이블에 의존하게 된다.
create문, alter문을 이용할 수 있다.
CREATE TABLE usertbl (
userId CHAR(8) NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL
);
CREATE TABLE buytbl (
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userId CHAR(8) NOT NULL,
prodName CHAR(8) NOT NULL,
FOREIGN KEY (userId) REFERENCES usertbl(userId)
);ALTER TABLE buytbl
ADD CONSTRAINT fk_usertbl_buytbl
FOREIGN KEY (userid)
REFERENCES usertbl(userid);
on update cascade
트리거, 별도로 지정하지 않으면 on update no action 및 on delete no action을 지정ㅎ는 것과 동일하다.
ALTER TABLE buytbl
ADD CONSTRAINT fk_usertbl_buytbl
FOREIGN KEY (userId)
REFERENCES usertbl (userId)
ON UPDATE CASCADE;
unique
중복되지않는 유일한 값을 입력해야하는 조건 primary 키과 거의 비슷한대신 null 값을 허용한다는 것이 차이점
칼럼에 직접지정, constaint문을 사용할 수 있다.
CREATE TABLE usertbl (
userid CHAR(8) NOT NULL PRIMARY KEY,
email CHAR(30) NULL UNIQUE
);
DROP TABLE IF EXISTS usertbl;
CREATE TABLE usertbl (
userid CHAR(8) NOT NULL PRIMARY KEY,
email CHAR(30) NULL,
CONSTRAINT ak_email UNIQUE (email)
);
check
제약조건을 입력되는 데이터를 점검하는 기능
CREATE TABLE usertbl (
userid CHAR(8) PRIMARY KEY,
name VARCHAR(8),
birthyear INT CHECK (birthyear >= 1900 AND birthyear <= 2023),
CONSTRAINT ch_name CHECK (name IS NOT NULL)
);
ALTER TABLE usertbl
ADD CONSTRAINT ch_mobile1
CHECK (mobile1 IN ('010', '011', '016', '017', '018', '019'));
default 기본값 지정, alter table 컬럼 alter column으로 수정할 수 있다.
CREATE TABLE usertbl (
userid CHAR(8) NOT NULL PRIMARY KEY,
addr CHAR(2) NOT NULL DEFAULT '서울',
height SMALLINT NULL DEFAULT 170
);
ALTER TABLE usertbl
ALTER COLUMN height SET DEFAULT 171;
row_format = compressed 테이블 압축
mysql 5.0부터 테이블 압축 기능을 제공하다.
CREATE TABLE compressedtbl (
emp_no INT,
first_name VARCHAR(4)
) ROW_FORMAT=COMPRESSED;
임시테이블 temporary table , 테이블과 정의하는것이 동일하나 세션 내에서만 존재하며 세션이 닫히면 자동으로 삭제된다.
임시테이블은 생성한 클라이언트에서만 접근이 가능하다.
임시테이블 명과 실제 테이블 명이 겹쳤을 경우 무조건 임시테이블로 접근이 되며 실제 테이블은 임시테이블이 삭제가 되기전에는 접근 할 수 없다.
CREATE TEMPORARY TABLE IF NOT EXISTS employees (
id INT,
name CHAR(5)
);
DESCRIBE employees;
테이블 삭제
DROP TABLE table1, table2, table3;
테이블 수정
alter = 이미 생성된 테이블에 추가/변경/수정/삭제
ALTER TABLE usertbl ADD homepage VARCHAR(30) DEFAULT 'example.com' NULL;
ALTER TABLE usertbl DROP COLUMN mobile1;
ALTER TABLE usertbl CHANGE name uName VARCHAR(20) NULL;
ALTER TABLE usertbl DROP PRIMARY KEY;
ALTER TABLE buytbl DROP FOREIGN KEY buytbl_ibfk_1;
view
일반 사용자 입장에서는 테이블과동일한 개체, select문객체. 보안에 도움이 되고복잡한 쿼리를 단순화시켜줄 수있다.
CREATE VIEW v_usertbl AS
SELECT userid, name, addr
FROM usertbl;
SELECT * FROM v_usertbl;
CREATE OR REPLACE VIEW v_height177 AS
SELECT *
FROM usertbl
WHERE height >= 177
WITH CHECK OPTION;
테이블 스페이스 tablespace
소용량일때는 신경쓰지않으나 대용량 데이터를 다룰때 테이블 스페이스에 대한 설정을 한다.
데이터베이스가 테이블이 저장되는 논리적 공간이라면 테이블 스페이스는 테이블이 실제로 저장되는 물리적인 공간을 말한다. 지금까지의 테이블은 모두 시스템 테이블스페이스 system tablespace에 저장되었다.
SHOW VARIABLES LIKE 'innodb_data_file_path';
각 테이블이 별도의 테이블 스페이스에 저장되려면 innodb_file_per_table 이 on으로 설정되어있어야한다.
SHOW VARIABLES LIKE 'innodb_file_per_table';
대용량 테이블을 동시에여러개 사용하는 경우 테이블마다별도의 테이블 스페이스에 저장하는 것이 성능에 효과적이다.
테이블스페이스는 데이터베이스와 관련이 없어 use문으로 데이터베이스를 선택할 필요없다.
테이블 스페이스 확장명은 .ibd여야한다.
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';
CREATE TABLE table_a (id INT) TABLESPACE ts_a;
대용량 테이블을 저장할경우 error2013이 난다면 쿼리 응답시간이 짧게 지정되어있어서 그렇다.
workbench - edit - preferences - sql editor - mysql session - dbms connection read timeout interval과 dbms connection timeout interval 두개를 0으로 설정 - ok 0은 시간 제한을 없애는것을 말한다.
CREATE TABLE table_c AS (SELECT * FROM employees.salaries);
ALTER TABLE table_c TABLESPACE ts_c;
chapter 09 인덱스
mysql에서 사용되는 인덱스의 종류는 크게 두가지로 나뉘는데 클러스터형 인덱스와 보조 인덱스이다
다른 dbms에서는 클러스터형 인덱스와 비클러스터형 인덱스로 나누기도 한다. 보조인덱스와 비클러스터형 인덱스는 거의 비슷한 개념이다.
클러스터형 인덱스는 테이블당 한개만 생성할 수 있고, 보조 인덱스는 테이블당 여러개를 생성할 수 있다.
클러스터형 인덱스는 행 데이터를 인덱스로 지정한 열에 맞춰 자동 정렬한다.
데이터베이스 튜닝
sql 서버가 기존보다 더 좋은 성능을 내도록 하는 전반적인 방법론.
응답시간을 빨리하는것.
서버 부하량을 최소화하는것.
테이블 정의시 primary key를 정의하면 자동으로 클러스터형 인덱스가 생성된다.
show index했을때 non_unique가 0이면 인덱스값들이 서로 중복되지않는 unique인덱스 1이면nonunique인덱스를 의미한다.
CREATE TABLE tbl1 (
a INT PRIMARY KEY,
b INT,
c INT
);
SHOW INDEX FROM tbl1;
unuque제약조건은 한 테이블에 여러개를생성할 수 있다. unique제약조건으로 설정하면 보조인덱스가 자동으로 생성된다.
CREATE TABLE tb2 (
a INT PRIMARY KEY,
b INT UNIQUE,
c INT UNIQUE,
d INT
);
SHOW INDEX FROM tb2;
unique에 notnull이 포함되면 클러스형 인덱스로 지정된다.
CREATE TABLE tbl4 (
a INT UNIQUE NOT NULL,
b INT UNIQUE,
c INT UNIQUE,
d INT
);
SHOW INDEX FROM tbl4;
unique notnull 과 primary 키가 모두 있다면 d 열에 클러스터형 인덱스사 생성되고 unique notnull 열에는 보조 인덱스가 생성된다. primary key로 설정된열에 우선 클러스터형 인덱스가 생성되는것.
CREATE TABLE tbl5 (
a INT UNIQUE NOT NULL,
b INT UNIQUE,
c INT UNIQUE,
d INT PRIMARY KEY
);
SHOW INDEX FROM tbl5;
insert 순서와 관계없이 이 클러스터형 인덱스가생성된 열 기준으로 select시 오름차순 정렬되 보여진다.
만일 열에 primary키가 이동한다면 다시 맞춰 정렬된다.
SELECT * FROM usertbl;
ALTER TABLE usertbl DROP PRIMARY KEY;
ALTER TABLE usertbl ADD CONSTRAINT pk_name PRIMARY KEY (name);
SELECT * FROM usertbl;
B Tree, balanced tree 균형트리
노드란 트리 구조에서데이터가 존재하는 공간.
루트노드 가장 상위 노드
리프노드 제일 마지막에 존재하는 노드
mysql 의 b tree에 노드에 해당되는 것에 page, 16kbyte( = 16384 byte) 크기의 최소한 저장단위, 아무리 작은 데이터라도 16kbyte를 차지하게 된다. 개념적으로 노드라고 부르지만 mysql에서는 노드가 페이지가 된다. 이 크기는 꽤 많은 행이 들어간다. mysql 페이지 크기를 확인할 수 있다.
SHOW VARIABLES LIKE 'innodb_page_size';
다른 dbms에서는 8kbyte나 다른 크기도 많이사용된다.
select문 사용시 아주 뛰어난 성능을 발휘한다 . 훨씬 많은 양의데이터, 깊은 레벨일경우에 이 차이는 기하급수적으로 난다. 인덱스를 구성하면 insert, update, delete 성능이 나빠지는 단점이 있다고 있다. 이는 페이지 분할이라는 작업이 발생되기 때문이고 자주일어날 경우 성능에 큰 영향을 준다. 보조인덱스는 데이터페이지를 정렬하지않고 뒤쪽 빈부분에 삽입됨으로 클러스터형 인덱스보다 성능에 주는 부하가 적다.
클러스터형 인덱스 생성시에는 데이터 페이지 전체가 다시 정렬된다. 이미 대용량데이터가 입력된 상태라면 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템 부하을 줄 수 있음으로 신중하게 생각해야한다. 클러스터형을 어느열에 생성하는지에 따라서도 시스템 성능이 달라질 수 있다. 보조인덱스와 혼합되어 사용되는 경우에 되도록 클러스터형 인덱스 열을 적은 자릿수 열을 선택하는 것이 바람직하다.
create index로 생성되는 인덱스는 보조 인덱스로 생성된다. 생성한 인덱스를 실제적용시키려면 analyze table문으로 먼저 테이블을 분석처리해야한다.
SELECT * FROM usertbl;
SHOW INDEX FROM usertbl;
SHOW TABLE STATUS LIKE 'usertbl';
CREATE INDEX idx_usertbl_addr ON usertbl (addr);
SHOW TABLE STATUS LIKE 'usertbl';
ANALYZE TABLE usertbl;
SHOW TABLE STATUS LIKE 'usertbl';
CREATE UNIQUE INDEX idx_usertbl_birthyear ON usertbl (birthYear);
SHOW INDEX FROM usertbl;
인덱스 삭제시에는 보조인덱스부터삭제하는 것이 좋다.
DROP INDEX idx_usertbl_birthyear ON usertbl;
SELECT table_name, constraint_name
FROM information_schema.referential_constraints
WHERE constraint_schema = 'sqldb';
ALTER TABLE buytbl DROP FOREIGN KEY buytbl_ibfk_1;
ALTER TABLE usertbl DROP PRIMARY KEY;
쿼리 실행 전, 후에 읽은 페이지수를 출력할 수 있다.
이것으로 데이터를 찾기 위해 얼마나 많은 페이지를 읽었는지 부하를 예상할 수 있다.
SHOW GLOBAL STATUS LIKE 'innodb_pages_read';
SELECT * FROM emp WHERE emp_no = 100000;
SHOW GLOBAL STATUS LIKE 'innodb_pages_read';
많은 부하가 예상될경우 hint를사용해 인덱스를 사용하지 못하도록강제로 지정할 수 있다.
전체 데이터를 읽어야할 경우 인덱스를 사용하든 사용하지않든 비슷한 결과일수있으나 읽는 양은 적어 효율적이라 할 수 있다
SHOW GLOBAL STATUS LIKE 'innodb_pages_read';
SELECT *
FROM emp_c IGNORE INDEX (PRIMARY)
WHERE emp_no < 5000000
LIMIT 10000000;
SHOW GLOBAL STATUS LIKE 'innodb_pages_read';
종옹 하는 오해가 클러스터형 인덱스는 꼭 있어야 한다는 생각이다.
unique키에 not null을 함께 지정하면 클러스터형인덱스가 생성됨으로 null로 지정해야한다 . 반드시 값을 입력하게 하고 응용 프로그래밍 화면에서 필수로 입력되도록 프로그래밍 하는 방식을 사용하는 것이 좋다 .
실무에서도 사용되지않는 쓸모없는 보조 인덱스 몇개만 삭제해도 성능이 향상된다
데이터 페이지를 왔다갔다 하며 읽게 하는것보다 인덱스가 없다고 치고 처음부터 찾아보는것이 더 빠르다.
인덱스 관리비용과 insert등의구문에서는 성능이 저하될 수 있다는 점을 고려하면 인덱스가 반드시 바람직하다고 보기는 어렵다. 인덱스는 단지 읽기에서만 성능을 향상하고 insert 작업이 거의 발생되지않는 테이블일경우 유리하다.
chapter 10 스토어드 프로그램
스토어드 프로시저, mysql에서 제공되는 프로그래밍 기능
간단한 스토어드 프로시저 생성 예를 보자.
DROP PROCEDURE IF EXISTS userProc;
DELIMITER $$
CREATE PROCEDURE userProc()
BEGIN
SELECT * FROM usertbl;
END $$
DELIMITER ;
CALL userProc();
DROP PROCEDURE IF EXISTS userProc2;
DELIMITER $$
CREATE PROCEDURE userProc2(
IN userbirth INT,
IN userheight INT
)
BEGIN
SELECT *
FROM usertbl
WHERE birthyear > userbirth
AND height > userheight;
END $$
DELIMITER ;
CALL userProc2(1970, 178);
출력 매개변수가 있는 프로시저
DROP PROCEDURE IF EXISTS userProc3;
DELIMITER $$
CREATE PROCEDURE userProc3(
IN txtValue CHAR(10),
OUT outValue INT
)
BEGIN
INSERT INTO testtbl VALUES (NULL, txtValue);
SELECT MAX(id) INTO outValue
FROM testtbl;
END $$
DELIMITER ;
CREATE TABLE IF NOT EXISTS testtbl (
id INT AUTO_INCREMENT PRIMARY KEY,
txt CHAR(10)
);
CALL userProc3('테스트값', @myValue);
SELECT CONCAT('현재입력된 id값', @myValue);
declare 변수선언
스토어드 프로시저 내의 if처리
DROP PROCEDURE IF EXISTS ifelseProc;
DELIMITER $$
CREATE PROCEDURE ifelseProc (IN userName VARCHAR(10))
BEGIN
DECLARE byear INT;
SELECT birthyear INTO byear
FROM usertbl
WHERE name = userName;
IF (byear >= 1980) THEN
SELECT '젊어요';
ELSE
SELECT '나이가 지긋해요';
END IF;
END $$
DELIMITER ;
CALL ifelseProc('홍길동');
sql case문을 사용할 수 있다.
DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc(
IN userName VARCHAR(10)
)
BEGIN
DECLARE byear INT;
DECLARE tti CHAR(3);
SELECT birthYear INTO byear
FROM usertbl
WHERE name = userName;
CASE
WHEN (byear % 12 = 0) THEN SET tti = '원숭이';
WHEN (byear % 12 = 1) THEN SET tti = '닭';
WHEN (byear % 12 = 2) THEN SET tti = '개';
ELSE SET tti = '등등';
END CASE;
SELECT CONCAT(userName, '의 띠 ', tti);
END $$
DELIMITER ;
CALL caseProc('홍길동');
프로시저에서의 반복문
drop procedure if exists whileProc;
delimiter $$
create procedure whileProc()
begin
declare str varchar(100);
declare i int;
declare k int;
set i = 2;
while ( i < 10) do
set str = '';
set k = 1;
while ( k < 10) do
set str = concat(str, ' ' , i, 'x', k, '=', i*k);
set k = k +1;
end while;
set i = i +1;
insert into gugutbl values(str);
end while;
end $$
delimiter;
call whileProc();
select * from gugutbl;
프로시저의 오류처리 declare exit handler for ~
DROP PROCEDURE IF EXISTS errorProc;
DELIMITER $$
CREATE PROCEDURE errorProc()
BEGIN
DECLARE i INT;
DECLARE hap INT;
DECLARE saveHap INT;
DECLARE EXIT HANDLER FOR 1264
BEGIN
SELECT CONCAT('int 오버플로 직전의 합계 -->', saveHap);
SELECT CONCAT('1+2+3+4+...+', i, '=오버플로');
END;
SET i = 1;
SET hap = 0;
WHILE (TRUE) DO
SET saveHap = hap;
SET hap = hap + i;
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
CALL errorProc();
프로시저와 @변수 설정
DROP PROCEDURE IF EXISTS nameProc;
DELIMITER $$
CREATE PROCEDURE nameProc(
IN tblName VARCHAR(20)
)
BEGIN
SET @sqlquery = CONCAT('SELECT * FROM ', tblName);
PREPARE myQuery FROM @sqlquery;
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;
CALL nameProc('userTBL');
프로시저는 alter등을 사용해 함수내용을 바꿀 수없다. drop시킨 후에 다시 create해야한다.
drop procedure nameProc;
커서
테이블에서 여러개의 행을 쿼리한 후, 결과행 집합을 한행씩 처리하기 위한 방식.
커서는 대부분 스토어드 프로시저의 내용을 활용된다.
OPEN userCursor;
grade_loop: LOOP
FETCH userCursor INTO id, hap;
IF endOfRow THEN
LEAVE grade_loop;
END IF;
CASE
WHEN hap >= 1500 THEN
SET usergrade = '최우수고객';
WHEN hap >= 1000 THEN
SET usergrade = '우수고객';
WHEN hap >= 1 THEN
SET usergrade = '일반고객';
ELSE
SET usergrade = '유령고객';
END CASE;
UPDATE usertbl
SET grade = usergrade
WHERE userid = id;
END LOOP;
CLOSE userCursor;
스토어드 함수 정의를 보면 스토어드 프로시저와 상당히 유사하나 아래의차이가 있다.
- in, out을 사용할 수없다. 무조건 입력 파라미터로 사용된다
- 별도의 반환구문이 없고 여러개의 out문으로 값을 반환하던 프로시저와 달리 스토어드는 return 문으로 반환한 데이터 형식을 지정하고 하나의 값을 반환한다. 따라서 스토어드 프로시저 안에는 select문을 사용할수있지만 스토어드 함수안에서는 집합결과를 반환하는 select을 사용할수없다.
- call로 호출되던스토어드 프로시저와 달리 프로시저와달리 스토어드 함수는 select 문장안에서 호출된다.
즉 스토어드 프로시저는 여러 sql문이나 숫자계산 등 다양한 용도로 사용되나 스토어드 함수는 어떤 계산을 통해 하나의 값을 반환하는데에 목적차이가 있다.
스토어드 프로시저에 긴 쿼리를 생성해놓았다면 단지 스토어드 프로시저 이름 및 매개변수 등 몇글자 텍스트만 전송하면 되기에 네트워크 부하를 줄일 수있어 결과적으로 mysql성능을 향상시킨다.
c#이나 java등의 클라이언트 응용 프로그램에서 sql문을 작성하지않고 이름만 호출하도록 설정할 수 있어 프로시저 내용을 일관되게 수정/유지보수 등의 작업을 할 수 있다. 모듈식 프로그래밍. 보안강화에도 좋다. 사용자별로 테이블에 접근 권한을 주지않고 스토어드 프로시저에만 접근 권한을 줌으로써 보완을 강화할 수 있다.
mysql에서는 다양한 내장함수를 제공하나 사용자가 원하는 모든 함수를 제공하지 않음으로 필요시 사용자가 직접 함수를 만들어 사용할 필요가 있다.
스토어드 함수를 사용하기 위해서 스토어드 함수 생성권한을 허용한다.
SET GLOBAL log_bin_trust_function_creators = 1;
스토어드 함수 예시
DROP FUNCTION IF EXISTS getageFunc;
DELIMITER $$
CREATE FUNCTION getageFunc(byear INT)
RETURNS INT
BEGIN
DECLARE age INT;
SET age = YEAR(CURDATE()) - byear;
RETURN age;
END $$
DELIMITER ;
필요하다면 반환값을 select into로 저장했다가 사용한다
SELECT getAgeFunc(1979) INTO @age1979;
SELECT getAgeFunc(1989) INTO @age1989;
SELECT CONCAT('1989년과 1979년의 나이차: ', (@age1979 - @age1989));
트리거
테이블에 관련되어 DML문 insert update delete 이벤트가 발생될때 작동하는 개체
스토어드 프로시저와 작동이 비슷하지만 직접 실행할 수 없고 in, out 매개변수도 사용할 수 없다
DROP TRIGGER IF EXISTS testTrg;
DELIMITER //
CREATE TRIGGER testTrg
AFTER DELETE
ON testtbl
FOR EACH ROW
BEGIN
SET @msg = '가수 그룹이 삭제됨';
END //
DELIMITER ;
SET @msg = '';
INSERT INTO testtbl VALUES (4, '가수1');
SELECT @msg;
DELETE FROM testtbl WHERE id = 4;
SELECT @msg;
alter 트리거
trigger 구문은 before/after insert/update/delete로 바뀌어 다양하게 수행될 수있다.
DROP TRIGGER IF EXISTS backusertbl_updatetrg;
DELIMITER //
CREATE TRIGGER backusertbl_updatetrg
AFTER UPDATE
ON usertbl
FOR EACH ROW
BEGIN
INSERT INTO backup_usertbl
VALUES (
OLD.userId,
OLD.name,
OLD.birthyear,
'수정',
CURDATE(),
CURRENT_USER()
);
END //
DELIMITER ;
DROP TRIGGER IF EXISTS backusertbl_deletetrg;
DELIMITER //
CREATE TRIGGER backusertbl_deletetrg
AFTER DELETE
ON usertbl
FOR EACH ROW
BEGIN
INSERT INTO backup_usertbl
VALUES (
OLD.userId,
OLD.name,
OLD.birthyear,
'삭제',
CURDATE(),
CURRENT_USER()
);
END //
DELIMITER ;
truncate, delete와 같은 효과를 내어 모든 행 데이터를 삭제하나 백업테이블에 삭제된 내용이 들어가지않는다. truncate table은 트리거가작동하지않기 때문이다. delete트리거는 delete문에만작동한다.
truncate from backup_usertbl;
select * from backup)sertbl;
좀더 재미있는 실습을해보자.
사용자가 강제로 발생하는 오류로 롤백을 시켜주는 signal sqlstate '45000'를 이용하자.
누군가가 수정이나 삭제를 시도했다면 경고메세지를 보이도록해 약간의 겁을 줘보자.
DROP TRIGGER IF EXISTS usertbl_inserttrg;
DELIMITER //
CREATE TRIGGER usertbl_inserttrg
AFTER INSERT
ON usertbl
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '데이터 입력을 시도했습니다. 정보가 서버에 기록됩니다.';
END //
DELIMITER ;
before 트리거
사용법은 똑같되 before로 지정될 뿐이다. 출생년도가 이상한 데이터에 대해 트리거 예시로 이해해보자.
DROP TRIGGER IF EXISTS usertbl_beforeinsertrg;
DELIMITER //
CREATE TRIGGER usertbl_beforeinsertrg
BEFORE INSERT ON usertbl
FOR EACH ROW
BEGIN
IF NEW.birthyear < 1900 THEN
SET NEW.birthyear = 0;
ELSEIF NEW.birthyear > YEAR(CURDATE()) THEN
SET NEW.birthyear = YEAR(CURDATE());
END IF;
END //
DELIMITER ;
트리거는 외 하나의 테이블에 동일한 트리거가 여러개 부착되어있는 다중 트리거, 트리거가 또 다른 트리거를 작동하는 중첩 트리거가있다. 하나의 테이블에 여러개의트리거가 부착되어있다면 이 경우 대부분 순서는 큰 의미가 없지만 특별히 순서를 지정해야할 때가 있다.
ordertbl에 insert가 발생되면 물품갯수를 주문한 개수에서 빼고 update하는 ordertrg,
ordertbl에 update가 발생되면 주문개수를 구해서 delivertbl에 배송할 물건과 개수를 입력하는 prodtrg을 살펴보자
FOLLOWS/PRECEDES 로 작동순서를 지정할 수 있다.
DROP TRIGGER IF EXISTS ordertrg;
DELIMITER //
CREATE TRIGGER ordertrg
AFTER INSERT ON ordertbl
FOR EACH ROW
BEGIN
UPDATE prodtbl
SET account = account - NEW.orderamount
WHERE prodname = NEW.prodename;
END //
DELIMITER ;
DROP TRIGGER IF EXISTS prodtrg;
DELIMITER //
CREATE TRIGGER prodtrg
AFTER UPDATE ON prodtbl
FOR EACH ROW
BEGIN
DECLARE orderamount INT;
SET orderamount = OLD.account - NEW.account;
INSERT INTO delivertbl(prodname, account)
VALUES (NEW.prodname, orderamount);
END //
DELIMITER ;
INSERT INTO ordertbl VALUES (NULL, 'john', '배', 5);
SELECT * FROM ordertbl;
SELECT * FROM prodtbl;
SELECT * FROM delivertbl;
전체 텍스트 인덱스 fulltext index
일반적인 인덱스와는 아래의 차이가있다.
- 전체 텍스트 인덱스는 innodb와 myisam 테이블에만 지원한다.
- 전체 텍스트인덱스는 char varchar text 열에만 생성이 가능하다.
- 인덱스 힌트의 사용이 일부 제한된다.
- 여러 개 열에 fulltext 인덱스를 지정할 수 있다.
description에 varchar(1000)데이터가 있을경우의 풀 텍스트인덱스생성 코드를 확인해보자. 이 인덱스의 index_type은 fulltext로 저장된다 .
create fulltext index idx_description on fulltexttbl(description);
show index from fulltexttbl;
전체 텍스트 인덱스는 긴 문장에 대해 인덱스를 생성해 그 양이 커질 수 밖에 없고 실제로 검색에서 무시할 만한 단어들은 아예 전체 텍스트 인덱스로 생성하지 않는 편이 좋다. '이번 선거는 아주 중요한 행사임으로 모두 꼭 참여 바랍니다'에 이번, 아주, 모두, 꼭 등은 검색할 이유가 없다는 소리다. 이것들을 모두 중지단어 stopwords라 부른다.
mysql 8.0은 information_schema.innodb_ft_default_stopword테이블에 a, about, an, are, as, at, be, by등의 36개의 중지단어를 가지고있다.이를 출력했을때 나오는 doc_count는 몇번이나 나왔는지를 말한다. 우리가 생성하고 isnert한 문장에서 적절한 부사등이 입력되어있는것을 확인할 수 있다.
사용자는 별도의 테이블에 중지단어를 추가해 적용시킬 수 있다. improtmation_schema.innodb_ft_index_table를 다시 확인해보면 등록한 중지단어를 제외되어있음을 확인 할 수 있다.
SET GLOBAL innodb_ft_aux_table = 'fulltextdb/fulltexttbl';
SELECT word, doc_count, doc_id, position
FROM information_schema.innodb_ft_index_table;
DROP INDEX idx_description ON fulltexttbl;
CREATE TABLE user_stopword (
value VARCHAR(30)
);
INSERT INTO user_stopword VALUES ('그는'), ('그리고'), ('극에');
SET GLOBAL innodb_ft_server_stopword_table = 'fulltextdb/user_stopword';
SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table';
CREATE FULLTEXT INDEX idx_description
ON fulltexttbl(description);
SELECT word, doc_count, doc_id, position
FROM information_schema.innodb_ft_index_table;
전체 텍스트 인덱스는 select문의 where절에 match(), aginst()를 사용한다.
특별히 옵션을 지정하지않거나 in natural language mode를 붙이면 단어를 검색해주는 자연어 검색을 한다.
SELECT *
FROM newpaper
WHERE MATCH(article) AGAINST ('영화 배우');
불린모드검색 boolean mode
in boolean mode 옵션을 붙이면 +, -, * 연산자를 사용한다.
SELECT *
FROM newspater
WHERE MATCH(article) AGAINST ('영화*' IN BOOLEAN MODE);
SELECT *
FROM newspater
WHERE MATCH(article) AGAINST ('영화배우' IN BOOLEAN MODE);
SELECT *
FROM newspater
WHERE MATCH(article) AGAINST ('+영화배우 +공포' IN BOOLEAN MODE);
SELECT *
FROM newspater
WHERE MATCH(article) AGAINST ('영화배우 -남자' IN BOOLEAN MODE);
파티션 partition
대량의 테이블을 물리적으로 여러 테이블로 쪼개는것. 수십억건의 테이블에 쿼리를 수행할때 인덱스를 사용한다고해도 테이블 대용량으로 인해 상당한 부담이 될 수 있다. 이때 파티션으로 나누 저장되어있다면 그 부담이 1/10로 줄어들수있다. 무조건 효율적으로 되는것이 아니니 데이터 분포 특성이나 잦 사용되는 쿼리문이 무엇인지를 이해하고 사용해야한다.
mysql은 최대 8192개의 파티션을 지원하는데 이는 물리적인 파일로 분리되고 파티션 테이블은 동시에 여러개가 열린다. mysql은 동시에 열수이는 파일객수가 open_file_limit(기본값 5000)에 지정되어있다. 즉 5000개 이상 나눌때는 시스템 변수를 변경해야한다.
테이블 생성시 파티션키를 함께 지정하고 사용자는 파티션키가 몇개이든 상관없이 테이블 하나에만 접근한다고 생각한다. 파티션을 나눴든 그렇지않든 mysql의 내부적인 문제일뿐 사용자는 신경쓰지않아도 된다는 의미이다.
파티션 테이블에는 primary key를 지정하면 안된다. 그열로정렬이 되기 때문이다. 마찬가지로 외래키를 설정할 수 없다. 즉 단독으로 사용되는 테이블에만 파티션을 설정 할 수 있다는 말이다.
스토어드 프로시저, 스토어드 함수, 사용자 변수, 임시테이블 모두 불가하고 파티션리스트에는 maxvalue를 사용하지않고 모든경우 파티션 키에 값을지정해야한다.
creaate시 테이블 정의가 끝나는 부분에 partition by range로 지정한다.
CREATE TABLE parttbl (
userid CHAR(8) NOT NULL,
birthyear INT NOT NULL
)
PARTITION BY RANGE (birthyear) (
PARTITION part1 VALUES LESS THAN (1971),
PARTITION part2 VALUES LESS THAN (1979),
PARTITION part3 VALUES LESS THAN MAXVALUE
);INSERT INTO parttbl VALUES ('user01', 1965);
INSERT INTO parttbl VALUES ('user02', 1975);
INSERT INTO parttbl VALUES ('user03', 1990);.....SELECT table_schema,
table_name,
partition_name,
partition_ordinal_position,
table_rows
FROM information_schema.partitions
WHERE table_name = 'parttbl';
수정은 alter + recognize partition 문을 사용한다.
ALTER TABLE parttbl
REORGANIZE PARTITION part3 INTO (
PARTITION part3 VALUES LESS THAN (1986),
PARTITION part4 VALUES LESS THAN MAXVALUE
);
어떤 파티션을 사용했는지 확인하려면 쿼리문 앞에 explain 문을 사용한다.
EXPLAIN SELECT *
FROM parttbl
WHERE birthyear <= 1965;
파티션삭제 alter + drop partition
이때 파티션에 있던 데이터가 함께 삭제되니 주의하며, 이는 delete문보다 빠르게 작동한다. 다른말로 대량이 데이터를 삭제하려면 delete문보다야 파티션자체를 삭제하는게 더 좋다는 말이 된다.
ALTER TABLE parttbl DROP PARTITION part2;
OPTIMIZE TABLE parttbl;
SELECT * FROM parttbl;'Book' 카테고리의 다른 글
| 이것이 MySQL이다 - part04_MySQL 응용 프로그램 및 공간 데이터 (0) | 2026.06.11 |
|---|---|
| 파이토치 딥러닝 마스터 - 1부 - 2장 사전 훈련된 신경망 (0) | 2026.06.05 |
| 파이토치 딥러닝 마스터 - 1부 - 1장_딥러닝과 파이토치 라이브러리 소개 (0) | 2026.05.31 |
| 이것이 MySQL이다 - part02_MySQL 기본 (0) | 2026.05.30 |
| 혼자 공부하는 파이썬 - Chapter 07 모듈 (0) | 2026.05.26 |
