[Database] INDEX와 VIEW 생성, 조회, 삭제 방법, 사용자 관리 방법

📌 인덱스

인덱스를 설정하면 특정 컬럼들을 키 값으로 메모리 영역에 트리 구조로 저장

디스크 저장소에 바로 접근하는 대신 메모리 저장소에 있는 인덱스를 먼저 조회해서 빠르게 데이터를 가져올 수 있다.

 

 

- 장점

• 인덱스는 색인과 목차처럼 데이터 검색 속도를 향상시키는데 사용
• 항상 정렬된 상태를 유지하기 때문에 빠르게 데이터를 가져올 수 있다.
• 책의 목차에서 원하는 주제를 찾아 해당 페이지로 바로 이동하는 것처럼 DB는 해당 인덱스를 활용하여 테이블의 전체 레코드를 스캔하지 않고도 필요한 데이터를 빠르게 찾음

- 단점

• 인덱스는 데이터의 복사본을 유지하므로, 테이블 데이터보다 추가공간 필요
• 낮은 카디널리티에 인덱스를 만들면 성능도 떨어지고, 추가 공간도 낭비됨
• INSERT, UPDATE, DELETE와 같은 쓰기 작업에서는 인덱스가 성능 저하를 유발

 

- 인덱스 조회

• SHOW INDEX FROM 테이블명;

- 인덱스 생성 방법

• pk, fk, unique 제약조건 추가시에 해당컬럼에 대해 index 자동생성
• 단일 컬럼 인덱스 생성 : CREATE INDEX 인덱스명 ON 테이블명(컬럼명);
• 복합(다중 컬럼) 인덱스 생성 : CREATE INDEX 인덱스명 ON 테이블명(컬럼1, 컬럼2);

- 인덱스 사용 ⭐

• 인덱스 정보를 활용하여 검색이 되려면, 조회 where 조건에 index 컬럼을 조건으로 걸어줘야 index페이지를 활용하여 검색이 이루어짐
• select * from author where id = 1;
• select * from author where id = 1 and name = ‘abc’;
  • name에 Index가 없고 id에 Index가 있다면, id 인덱스 페이지를 참조
  • name에도 별도로 Index가 있다면, MariaDB엔진에서 최적의 알고리즘 실행
  • id, name에 동시에 index가 걸려있다면 해당 다중 컬럼 index 참조하여 검색

- 인덱스 삭제

• DROP INDEX 인덱스명;

 

- 자료구조에 따른 분류

https://bcp0109.tistory.com/365

MySQL Index 특징 및 유의사항 정리

 

 

 

💻 INDEX 실습 

 

 

-- (1) 단일컬럼 INDEX : author 테이블의 name

 CREATE INDEX one_index ON author(name);

 -- (2) 복합컬럼 INDEX : author 테이블의 name, email

  CREATE INDEX two_index ON author(name, email);

 

 

 

name 하나의 컬럼만 가지는 one_index는 Seq_in_index 값이 1

name, email 두 개의 컬럼을 가지는 two_index는 Seq_in_index 값이 1과 2를 가지는 것을 확인할 수가 있다.

= 1 값을 가지는 name으로 우선 정렬, name이 중복되면 2 값을 가지는 email로 정렬

 

 

 

✅ 카디널리티

데이터의 중복되는 정도

데이터 종류가 많을수록(행이 유일하게 구분될수록) 카디널리티는 높아진다.

ex) 주민등록번호 - 카디널리티가 높다, 성별 - 카디널리티가 낮다

 

 

💡 카디널리티와 인덱스의 관계

카디널리티가 높을수록 branch 탐색 횟수가 적어진다.

= 분류되는 값이 명확하기 때문에 알고자 하는 값으로 빠르게 도달할 수 있다.

= 높은 카디널리티에 인덱스를 설정

= 조회 조건에 걸려 빈번하게 조회되는 컬럼에 인덱스를 설정

 

 

 

 

 

 

📌 VIEW

사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블이다

 

- VIEW 생성

CREATE VIEW 뷰네임 AS

SELECT 컬럼1, 컬럼2, ...

FROM 테이블명;

 

- VIEW 조회

SELECT * FROM author_for_view;

 

- 특징

• 뷰는 가상 테이블이기 때문에 저장장치 내에 물리적으로 존재하지 않음
• 뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터를 안전하게 보호
• 복잡한 쿼리 결과를 뷰로 생성해두면, 이후에는 뷰를 간단한 쿼리로 호출
• 뷰를 사용하여 특정 사용자에게 테이블의 일부 데이터만을 보여주는 것이 가능
  • grant를 통해 뷰에 대한 권한만을 부여
  • GRANT SELECT ON [데이터베이스이름].[뷰이름] TO 'testuser'@'localhost';
  • FLUSH PRIVILEGES; ⭐

 

 

💻 VEIW 실습 

 

1. author의 name, email정도만 알 수 있는 view를 생성

 

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testpw';

 

CREATE VIEW author_for_view AS SELECT name, email FROM author;

 

SELECT * FROM author_for_view;

 

view 조회 결과

 

 

2. test user의 기존 author 관련 view 테이블에 조회권한 부여

 

GRANT SELECT ON board.author_for_view TO 'testuser'@'localhost';

FLUSH PRIVILEGES;

 

 

3. testuser Connection으로 이동해서 조회

 

SELECT * FROM author_for_view;

 

 

 

 

📌 프로시저

✅ 저장 프로시저 (stored procedure)

데이터베이스에 저장되어 실행될 수 있는 하나 이상의 SQL 문의 집합

 

- 특성

• SQL문을 미리 컴파일하여 저장함으로써 데이터베이스 서버의 부하를 줄이고 성능을 향상
• 한 번 작성된 저장 프로시저는 여러 번 재사용
• DB에 프로시저를 많이 저장해두면 DB 교체가 어려움
• 복잡한 조건문 반복문 등을 프로시저에서는 프로시저 문법에 맞게 사용할 수 있음
  • SQL은 본질적으로 선언문으로서 제어문에 대한 유연성 낮음(반복문 불가)
  • 프로시저는 절차적 언어로서 제어문 사용 가능
• 데이터베이스 수준에서 직접 접근 권한을 제어할 수 있으므로 보안을 강화
• 특정 사용자에게 프로시저 실행권한 부여
  • GRANT EXECUTE ON PROCEDURE 데이터베이스명.프로시저명 TO 'testuser'@'localhost';

 

- 프로시저 생성

DELIMITER

CREATE PROCEDURE 프로시저명 (IN 변수명 변수타입)

BEGIN

   --SQL문법

END

DELIMITER ;

 

파라미터는 생략 가능하고, 함수와 같이 parameter를 전달하여 실행하는 것도 가능

마지막 줄에 ;왼쪽에 공백이 있어야 오류가 안난다.

 

- 프로시저 조회

SHOW CREATE PROCEDURE GetUsers;

 

- 프로시저 호출

CALL 프로시저명();

 

- 변수 선언

DECLARE 변수명 변수타입 [DEFAULT default_value];

반드시 프로시저나 함수의 본문 시작 부분, 즉 BEGIN 바로 뒤에 위치

ex) DECLARE abc INT DEFAULT 0;

 

- 변수 수정

SET 변수명 = 수정할 값;

 

- 제어문

• IF문
  • IF 조건식 THEN 조건이 참일 때 실행할 명령;
  • ELSE 조건이 거짓일 때 실행할 명령;
  • END IF;
  • "SELECT 컬럼명 INTO 변수" 문과 함께 많이 사용
• WHILE문
  • WHILE 조건식 DO --조건식이 참일 동안 반복 실행할 명령
  • END WHILE;

 

 

💻 프로시저 실습 

 

 

DELIMITER //

CREATE PROCEDURE getUser(IN userId INT)

BEGIN

   SELECT * FROM author WHERE  id=userId;

END //

DELIMITER ;

CALL getUser(1);

 

 

 

 

💻 프로시저 실습 2

 

1. post 테이블에 쉽게 INSERT 할 수 있는 post 관련 프로시저 생성

사용자에게 title, contents, author_id만 입력받아 INSERT하는 INSERT문 생성

 

DELIMITER //

CREATE PROCEDURE easyInsert(IN title varchar(255), contents varchar(3000), author_id INT)

BEGIN

   INSERT INTO post(title, contents, author_id) VALUES(title, contents, author_id);

END //

DELIMITER ;

CALL easyInsert ('속눈썹펌 정보', '뷰티', 2);

select * from post;

 

 

 

 

💻 프로시저 실습 3 (IF사용)

 

1. IF문 활용하여 post 테이블에 고액 원고료 작가인지 아닌지 조회

 

 

DELIMITER //

CREATE PROCEDURE highPriceAuthor(IN in_author_id INT)

BEGIN

   DECLARE money INT DEFAULT 0;

   SELECT price FROM post WHERE author_id=in_author_id INTO money;

   IF money>=4000 THEN

        SELECT '고액 원고료 작가입니다' AS message;

   ELSE

        SELECT '고액 원고료 작가가 아닙니다' AS message;

   END IF;

END //

DELIMITER ;

CALL highPriceAuthor(4);

 

 

 

 

💻 프로시저 실습 4 (IF사용)

 

1. IF문 활용하여  post 테이블에 작가의 원고료 평균이 3000원 이상이면 고액 원고료 작가, 미만이면 고액 원고료 작가

 

DELIMITER //

CREATE PROCEDURE highAvgPriceAuthor(IN in_author_id INT)

BEGIN

   DECLARE avg_price INT DEFAULT 0;

   SELECT AVG(price) INTO avg_price FROM post WHERE author_id=in_author_id;

   IF avg_price>=3000 THEN

        SELECT '고액 원고료 작가입니다' AS message;

   ELSE

        SELECT '고액 원고료 작가가 아닙니다' AS message;

   END IF;

END //

DELIMITER ;

CALL highAvgPriceAuthor(1);

 

 

 

 

 

💻 프로시저 실습 5 (WHILE 사용)

 

1. author 테이블에 WHILE문을 활용하여 INSERT 100건

 

DELIMITER //

CREATE PROCEDURE insert100Values()

BEGIN

   DECLARE a INT DEFAULT 0;

   DECLARE name varchar(100) DEFAULT '';

   DECLARE email varchar(500) DEFAULT '';

   DECLARE password varchar(255) DEFAULT '신';

   DECLARE role enum('user', 'admin') DEFAULT 'user';

   DECLARE address varchar(255) DEFAULT '';

   DECLARE age INT DEFAULT 0;

   

WHILE a<100 DO

   INSERT INTO author(id, name, email, password, role, address, age) VALUES(a+10, concat(name, a), concat(a, '@naver.com'), concat(password, a), 'user', concat(address, a, '동', a, '호'), a);

   SET a = a+1;

END WHILE;

END //

DELIMITER ;

CALL insert100Values();

 

 

프로시저 호출 전

 

프로시저 호출 후 1

프로시저 호출 후 2