멋쟁이 사자처럼 24회 ( 04 / 03 )

🦁멋쟁이 사자처럼 15기/4월달 수업 내용 정리

멋쟁이 사자처럼 24회 ( 04 / 03 )

코딩하는 하마 2025. 4. 3. 17:49

[ 학습 목표 ]

1. 데이터베이스 사용자 및 권한 관리 기능을 이해하고, 계정 생성과 권한 부여/ 회수 작업을 수행할 수 있다.
--> 사용자 생성 (create user), 권한 부여 ( grant) , 회수 (revoke) , 역할 (role) 등의 개념을 이해하고 적용할 수 있다.

2. 테이블의 파티션 (partition) 기능을 이해하고, range 파티셔닝을 통해 대용량 데이터를 효과적으로 분산 저장할 수 있다.
--> partition by range, values less than, maxvalue 등을 활용하여 특정 기준 컬럼에 따라 데이터 분할이 가능하다.

3. 파티션된 테이블에서 데이터 분포 상태 및 성능 최적화 여부를 정보 스키마와 실행 계획을 통해 분석할 수 있다.
--> information_schema.partitions, explain,sql_no_cache 등을 활용하여 파티션 효과를 검토하고 비교할 수 있다.

4. 파티션 테이블의 구조 변경 (추가, 삭제 , 제거 등 ) 및 파티션 단위 조회를 통해 관리 능력을 향상시킬 수 있다.
--> alter table ... drop partition, remove partitioning , select from partition 등을 활용해 실전 운영 상황을 관리할 수 있다

PARTITION

1) 파티션이란?

인덱스 또는 테이블에서 논리적 분할하며 테이블의 컬럼 값을 기준으로 데이터를 나누는 방식을 말한다 .

- 날짜 , 범위, 값을 기준으로 분할 작업
- 파티션 키를 설정  -> 파티션 유형[범위, 특정기준_list, hash, key_mysql결정]
-> CREATE TABLE [ALTER TABLE]

2) 파티션의 유형

RANGE 파티셔닝	특정 컬럼의 값 범위를 기준 -날짜, 숫자 등의 연속적인 데이터에 적합. (ex: 연도별, 월별 데이터 분리)
LIST 파티셔닝	특정 컬럼의 값 목록을 기준 -특정 카테고리나 상태 값 등에 적합합니다. (ex: 지역별, 제품 유형별 데이터 분리)
HASH 파티셔닝	파티션 키 컬럼의 해시 값을 이용하여 데이터를 분산, 데이터 분포를 균등하게 하고 싶을 때 유용
KEY 파티셔닝	HASH 파티셔닝과 유사하지만, 해시 함수를 사용자가 직접 지정하는 대신 MySQL 서버가 내부적 관리 -주로 기본 키(Primary Key)나 고유 키(Unique Key)를 기준으로 사용
COLUMNS 파티셔닝 (RANGE COLUMNS , LIST COLUMNS)	여러 개의 컬럼 값을 조합하여 파티셔닝 기준으로 사용 - 복합적인 조건으로 데이터를 분리해야 할 때 유용

ex) RANGE 파티셔닝

-- year_col 기준으로 범위 파티션을 적용해 보기 
CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999),
    PARTITION p3 VALUES LESS THAN (2003),
    PARTITION p4 VALUES LESS THAN (2007)
);

-- 파티션 정보를 확인해보기 
SELECT * FROM information_schema.PARTITIONS 
            WHERE TABLE_NAME = 'T1' AND TABLE_SCHEMA = 'MY_EMP';   
            
-- 테이블에 파티션을 추가 해보기
ALTER TABLE t1 
 ADD PARTITION (PARTITION p5 VALUES LESS THAN (2012));
 
 -- 데이터 추가하기 
INSERT INTO T1 VALUES (1, 1990); 
INSERT INTO T1 VALUES (2, 1993); 
INSERT INTO T1 VALUES (3, 1996); 
INSERT INTO T1 VALUES (4, 2000); 
INSERT INTO T1 VALUES (5, 2005);

위는 RANGE 파티셔닝을 한 것으로 파티션 적용 및 정보 확인 그리고 파티션 추가와 데이터 추가를 해본 것이다.

아래 코드는 실행 비용을 계산할 수 있는 코드이다.

-- 5. 실행 비용을 계산 해보기 
EXPLAIN SELECT * FROM T1;

[실행 결과]

아래 코드는 실제 데이터 실행 시간과 함께 확인이 가능하다.

EXPLAIN SELECT  * FROM T1 WHERE year_col < 1990;
EXPLAIN SELECT  * FROM T1 WHERE year_col < 2004;
EXPLAIN SELECT  * FROM T1 WHERE year_col < 2023;

-- 파티션 객체 이름으로 데이터 추출하기
SELECT * FROM t1 PARTITION (p0);
SELECT * FROM t1 PARTITION (p1);
SELECT * FROM t1 PARTITION (p4);

EXPLAIN SELECT * FROM t1 PARTITION (p0);
EXPLAIN SELECT * FROM t1 PARTITION (p1);
EXPLAIN SELECT * FROM t1 PARTITION (p4);


-- 각 파티션 객체와 데이터 수 체크하기
SELECT PARTITION_NAME , TABLE_ROWS  FROM information_schema.PARTITIONS 
            WHERE TABLE_NAME = 'T1' AND TABLE_SCHEMA = 'MY_EMP';
            
-- t1에 year_col  인덱스을 추가하기  
ALTER TABLE  T1
ADD  INDEX IDX_YEAR_COL(year_col);

ex) LIST 파티셔닝 ( 특정값을 기준으로 데이터 분할 = 특정값들의 집합정의 / DEFAULT 파티션 X)

CREATE TABLE t2 (
    id INT,
    year_col INT
)
PARTITION BY LIST (year_col) (
    PARTITION p0 VALUES IN (1990, 1991, 1992),
    PARTITION p1 VALUES IN (1993, 1994, 1995),
    PARTITION p2 VALUES IN (1996, 1997, 1998)
);

ex) HASH 파티셔닝 ( 균등 자동 분산 )

 CREATE TABLE t3 (
    id INT,
    year_col INT
)
PARTITION BY HASH (year_col) 
PARTITIONS 4;

-- 데이터의 분포 확인하기  
SELECT PARTITION_NAME , TABLE_ROWS  
FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'T3' AND TABLE_SCHEMA = 'MY_EMP';     
            
CALL T3_INSERT(10000);

SELECT  * FROM T3;

-- 데이터 쿼리 성능 확인   : P2의 2483 데이터중 탐색하기  
EXPLAIN SELECT * FROM t3  WHERE YEAR_COL  = 1998;

-- 데이터 추가 햇을 때 HASH 파티션 성능 체크 ,  I/O 관련 파티션 처리 통계  
SHOW SESSION STATUS LIKE  'HANDLER%';   
  
EXPLAIN SELECT * FROM t3 PARTITION (p0);
EXPLAIN SELECT * FROM t3 PARTITION (p1);
EXPLAIN SELECT * FROM t3 PARTITION (p2);
EXPLAIN SELECT * FROM t3 PARTITION (p3); 
  
-- 파티션별 데이터 쿼리  
EXPLAIN SELECT * FROM T3  PARTITION (p0) WHERE YEAR_COL  = 1920;  
SELECT  * FROM  T3 LIMIT 3;

-- 데이터가 존재하는 파티션을 확인하자.  
SELECT  *
FROM T3  PARTITION (p0) WHERE YEAR_COL  = 1920
UNION ALL
SELECT  *
FROM T3  PARTITION (p1) WHERE YEAR_COL  = 1920
UNION ALL
SELECT  *
FROM T3  PARTITION (p2) WHERE YEAR_COL  = 1920
UNION ALL
SELECT  *
FROM T3  PARTITION (p3) WHERE YEAR_COL  = 1920;

ex) KEY 파티셔닝 ( MYSQL 이 해시함수를 이용해서 해시값을 계산 )

CREATE TABLE t4 (
    id INT,
    year_col INT
)
PARTITION BY KEY (year_col) 
PARTITIONS 4;

INDEX

[인덱스의 형태]

UNIQUE = 고유값 , NULL 은 하나만 
SPATIAL = 공간인덱스 

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}  -- 성능 테스트 
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY} 
    -- INPLACE : 테이블의 복사 없이 인덱스를 생성함 ( 유실될 가능성 있음 ) 
    -- COPY : 테이블에 임시 복사본을 만듦 , 복사본 인덱스 생성
                       
lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
    -- NONE : 잠금을 최소화 할 때 사용 , 읽기는 동시 허용 UPDATE , DELETE는 잠금 
    -- SHARED : 쓰기만 차단하는 것 
    -- EXCLUSIVE : 외부에서 다 잠가버림

ex)

-- 함수 기반 인덱스를 활용해 보자. 
CREATE TABLE tbl (
  col1 LONGTEXT,
  INDEX idx1 ((SUBSTRING(col1, 1, 10)))); -- 10문자만 추출해서 인덱스로 사용하겠다.

CREATE TABLE employees (
  data JSON,
  ID INT,
  INDEX ((CAST(data->>'$.name' AS CHAR(30))))
);

TRANSACTION

1) 트랜잭션의 4대 특성 (ACID)

a. 사용자 및 권한 관리 (CREATE USER, GRANT, REVOKE, SHOW GRANTS 등)
b. MySQL 시스템 테이블 구조 (mysql.user, mysql.db 등)
c. 역할(Role) 기반 권한 제어 및 기본 역할 설정
d. 사용자 권한 회수 및 재부여

2) 트랜잭션의 대표적인 4가지 특성

a. 원자성 (Atomicity) : 전부 성공적으로 수행되거나 수행되지 않아야 한다.
-- 트랙잭션이 성공하면 모든 변경사항이 커밋되어 데이터베이스에 반영하고, 실패하면 트랜잭션이 시작된 시점 이전 상태로 돌아간다.

b. 일관성 (Consistency) : 데이터베이스에 무결성 제약조건(외래키, 고유키등)이 트랜잭션 후에도 여전히 유효하다

c. 고립성 (Isolation) : 독립적으로 수행= 격리(고립)수준 + 동시성 제어

d. 지속성 (Durability) : 트랜잭션이 완료된 이후 외부에 의해서 예외가 발생될 경우라도 데이터 손실되지 않는다.

ex)

DELETE FROM EMP_TEST 
WHERE DEPTNO=10;
SELECT  COUNT(*) FROM EMP_TEST;
COMMIT;  -- 11개  

INSERT INTO EMP_TEST(EMPNO) VALUES(1);
INSERT INTO EMP_TEST(EMPNO) VALUES(1);
INSERT INTO EMP_TEST(EMPNO) VALUES(1);  -- 14개
ROLLBACK;  -- 11개 

INSERT INTO EMP_TEST(EMPNO) VALUES(1);
INSERT INTO EMP_TEST(EMPNO) VALUES(1);
INSERT INTO EMP_TEST(EMPNO) VALUES(1);
COMMIT; -- 14개

MYSQL 의 권한 및 사용자 관리

[참고 자료] https://dev.mysql.com/doc/refman/8.4/en/mysqladmin.html

MySQL :: MySQL 8.4 Reference Manual :: 6.5.2 mysqladmin — A MySQL Server Administration Program

6.5.2 mysqladmin — A MySQL Server Administration Program mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more. Invoke mysqladmin li

dev.mysql.com

1) MySQL에서는 데이터베이스, 호스트, 사용자, 테이블, 필드별로 권한을 설정할 수 있음.
2) mysql 데이터베이스에 유저/패스워드/호스트명을 등록하여 관리함.
3) 관리용 테이블: db _INSERT,DELETE , host_호스트제한,
- user_ 사용자 테이블 , tables_priv[SELECT,INSERT,DELETE,UPDATE] , columns_priv.

1) MYSQL 권한 관리 범위

데이터베이스: 특정 DB 전체에 대한 권한 설정 (mydb.*)
테이블 : 특정 테이블에 대한 권한 설정 (mydb.table1)
필드(컬럼): 테이블의 특정 컬럼에 대한 권한 설정 (columns_priv)
사용자 :사용자 계정별로 권한 설정 ('user'@'host')
호스트 : 어떤 클라이언트(호스트)에서 접속 가능한지 설정

2) 사용자 정보

mysql 시스템 데이터베이스(MySQL 서버의 계정, 권한, 인증정보)에 저장된다.

user: 사용자 계정 정보 (이름, 호스트, 비밀번호, 글로벌 권한 등)
db :특정 DB에 대한 권한 설정 정보
host: 호스트별로 DB 접속 제어 가능
tables_priv: 테이블 수준의 세부 권한 (SELECT, INSERT, UPDATE, DELETE)
columns_priv: 열(컬럼) 단위의 권한 제어
procs_priv :저장 프로시저, 함수에 대한 권한

3) 관리자 권한 DCL 구문

GRANT: 권한 부여
REVOKE: 권한 회수
SHOW GRANTS FOR 'user'@'host': 권한 확인
FLUSH PRIVILEGES : 변경된 권한을 즉시 반영

ex)

- 사용자 권한 보기

SELECT host, user FROM mysql.user;

- 사용자별 권한 확인

SHOW GRANTS FOR 'hippo'@'%';

- 시스템 테이블 구조 보기

DESC mysql.user;
DESC mysql.db;

- db 테이블 구조 확인

SHOW FIELDS FROM db;
SELECT * FROM db;

- user 테이블 구조 확인

SHOW FIELDS FROM user;
SELECT * FROM user;

4) 사용자 계정 생성 및 권한 부여

권한(Privileges):  사용자 또는 역할이 어떤 작업(데이터베이스, 테이블, 컬럼, 프로시저)을 할 수 있는지를 제어하는 기능
1. 전역(Global) 권한 :  CREATE USER, SHUTDOWN, FILE, RELOAD, PROCESS, SUPER
    ex)GRANT ALL ON *.* TO 'user'@'host';

2. 데이터베이스(DB) 수준 권한 :특정 데이터베이스 전체에 대한 권한
CREATE, DROP, ALTER, INDEX, SELECT, INSERT, UPDATE, DELETE
ex)GRANT SELECT, INSERT ON mydb.* TO 'user'@'host';

3. 테이블 수준 권한 : 특정 테이블에 대해 부여하는 권한
  SELECT, INSERT, UPDATE, DELETE, REFERENCES
   ex)GRANT SELECT, UPDATE ON mydb.mytable TO 'user'@'host';

4. 컬럼 수준 권한 :테이블 내의 특정 컬럼에 부여하는 권한 SELECT(col1), UPDATE(col2)
ex )GRANT SELECT (col1), UPDATE (col2) ON mydb.mytable TO 'user'@'host';

5. 루틴(프로시저/함수) 권한
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'user'@'host';

ex)

- 새로운 사용자 계정 생성

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';  -- 로컬 호스트 접근 가능 사용자
CREATE USER 'new_user'@'%' IDENTIFIED BY 'new_password';          -- 네트워크 접근 가능 사용자

- 권한 부여

GRANT ALL PRIVILEGES ON my_emp.* TO 'new_user'@'localhost';       -- 특정 데이터베이스에 대한 권한 부여
GRANT ALL PRIVILEGES ON my_emp.* TO 'new_Test'@'%';

- 비밀번호 변경

ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';
ALTER USER 'new_Test'@'localhost' IDENTIFIED BY 'new_1234';

- 권한 추가

GRANT ALL PRIVILEGES ON my_test.* TO 'existing_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON my_emp.table_name TO 'existing_user'@'localhost';

- 관리자 권한으로 생성된 사용자 및 호스트 정보 확인

DESC mysql.user;
SELECT host, user FROM mysql.user;

- 권한 확인

SHOW GRANTS FOR 'mysample'@'%';
SHOW GRANTS FOR 'mysample'@'localhost';

- 특정 데이터베이스 및 테이블에 대한 권한 설정

GRANT ALL PRIVILEGES ON world.* TO 'mysample'@'%';
FLUSH PRIVILEGES;

- 특정 테이블에 대한 권한 설정 ( localhost 사용자)

GRANT ALL PRIVILEGES ON world.city TO 'mysample'@'localhost';
GRANT ALL PRIVILEGES ON world.city TO 'mysample07'@'localhost';
FLUSH PRIVILEGES;

- ROLE 생성

CREATE ROLE 'myselect';
CREATE ROLE 'myupdate';

- ROLE 권한 부여

GRANT SELECT ON *.* TO 'myselect';
GRANT UPDATE ON *.* TO 'myupdate';

- 사용자 계정 생성

CREATE USER 'my_select'@'%' IDENTIFIED BY '111';
CREATE USER 'my_update'@'%' IDENTIFIED BY '111';

- ROLE을 사용자에게 부여

GRANT 'myselect' TO 'my_select'@'%';
GRANT 'myupdate' TO 'my_update'@'%';
FLUSH PRIVILEGES;

- ROLE 권한 설정 및 확인

SET DEFAULT ROLE ALL TO 'my_select'@'%';
SET DEFAULT ROLE ALL TO 'my_update'@'%';

- 권한 확인

SHOW GRANTS FOR 'my_select'@'%';
SHOW GRANTS FOR 'my_update'@'%';

- ROLE 권한 해제

REVOKE 'myselect' FROM 'my_select'@'%';
REVOKE SELECT ON *.* FROM 'my_select'@'%';

TRIGGER

트리거란?

TRIGGER : 특정테이블에서 데이터가 INSERT | UPDATE | DELETE 될때 자동으로 실행되는 SQL 코드 블록이다. 
CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

ex)

-- BEFORE_EMP_INSERT  라는 트리거를 만들어서 봉급이 음수값이 입력되면 오류를 발생하자  
DELIMITER //

USE MY_EMP;
CREATE TRIGGER BEFORE_EMP_INSERT
BEFORE INSERT  
ON EMP FOR EACH ROW 
BEGIN
    IF NEW.SAL < 0 THEN
        SIGNAL SQLSTATE '45000'  
        SET MESSAGE_TEXT = '봉급이 음수잖아';
    END IF;
END; //

DELIMITER ;


INSERT INTO EMP(EMPNO, SAL) VALUES (1,-1000) ;

-- BEFORE_EMP_UPDATE 라는  데이터를 수정할 때 특정 조건을 트리거 하자.  
--  봉급이 수정될 때 ,  이전봉급은 커미션에다 입력하자. 
/*
조건 1: NEW.SAL이 OLD.SAL과 다를 경우 → COMM에 OLD.SAL 저장
조건 2: NEW.SAL < 0일 경우 오류 발생
오류 메시지: '봉급이 음수잖아'
OLD : 이전값
NEW : 새로운 값
*/
USE MY_EMP;
DELIMITER //
CREATE TRIGGER BEFORE_EMP_UPDATE
BEFORE UPDATE 
ON EMP FOR EACH ROW 
BEGIN
    IF NEW.SAL <> OLD.SAL THEN  
        SET NEW.COMM = OLD.SAL;
    END IF;

    IF NEW.SAL < 0 THEN
        SIGNAL SQLSTATE '45000'  
        SET MESSAGE_TEXT = '봉급이 음수잖아';
    END IF;
END; //

DELIMITER ;
SET SQL_SAFE_UPDATES=0;
UPDATE  EMP SET  SAL   = 90;  -- 업데이트 실행 

SELECT  * FROM EMP;   -- 확인

 -- 트리거 확인 및 메타 정보 조회
SHOW CREATE TRIGGER  BEFORE_EMP_UPDATE;

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
       WHERE TRIGGER_SCHEMA='MY_EMP' AND TRIGGER_NAME='BEFORE_EMP_UPDATE';


-- Q4) AFTER_EMP_INSERT 라는 트리거를 생성하시오.
-- EMP 테이블에 INSERT가 발생한 후 새로 입력된 사원의 이름(ENAME)과 봉급(SAL)을 로그 테이블(EMP_LOG)에 기록하시오.
CREATE TABLE IF NOT EXISTS EMP_LOG (
    LOG_ID INT AUTO_INCREMENT PRIMARY KEY,
    LOG_ENAME VARCHAR(50),
    LOG_SAL DECIMAL(10,2),
    LOG_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE TRIGGER AFTER_EMP_UPDAT
AFTER INSERT 
ON EMP FOR EACH ROW 
BEGIN
    INSERT INTO EMP_LOG(LOG_NAME, LOG_SAL)
    VALUES(NEW.ENAME, NEW.SAL);
END; //
DELIMITER ;

INSERT INTO EMP(EMPNO, ENAME , SAL , DEPTNO ) VALUES (13,"홍길동", 1000, 10);
SELECT * FROM EMP_LOG;

-- BEFORE_EMP_DELETE 라는 트리거를 생성하시오.
-- 사원이 삭제되기 전에  삭제 대상의 이름(ENAME)과 봉급(SAL)을 EMP_DELETE_LOG 테이블 저장하자. 
CREATE TABLE IF NOT EXISTS EMP_DELETE_LOG (
    DEL_ID INT AUTO_INCREMENT PRIMARY KEY,
    ENAME VARCHAR(50),
    SAL DECIMAL(10,2),
    DELETED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE TRIGGER BEFORE_EMP_DELETE
BEFORE DELETE 
ON EMP FOR EACH ROW
BEGIN 
   INSERT INTO EMP_DELETE_LOG(ENAME , SAL ) VALUES(OLD.ENAME , OLD.SAL);
END; //
DELIMITER ;

DELETE FROM EMP WHERE EMPNO = 11;
COMMIT;
SELECT * FROM EMP_DELETE_LOG;

'🦁멋쟁이 사자처럼 15기 > 4월달 수업 내용 정리' 카테고리의 다른 글

멋쟁이 사자처럼 27회 ( 04 / 08 ) (0)	2025.04.08
멋쟁이 사자처럼 26회 ( 04 / 07 ) (0)	2025.04.07
멋쟁이 사자처럼 25회 ( 04 / 04 ) (0)	2025.04.04
멋쟁이 사자처럼 24회 ( 04 / 02 ) (0)	2025.04.02
멋쟁이 사자처럼 23회 ( 04 / 01 ) (0)	2025.04.01

현재글멋쟁이 사자처럼 24회 ( 04 / 03 )

codinghippo702

백준 11659, crud, URI, 백준, 구간합, etc thread, 구간합 구하기, 배열과 리스트, 알파벳 개수 (10808), Partition, threadgroup, CallableStatement, MongoDB, 숫자의 합 (11720), Aggregation, 에디터 (1406), DB 연동, nosql, 평균 (1546), Virtual Thread,

Today :
Yesterday :

codinghippo702