INDEX, TRANSACTION, 권한부여 // MYSQL(2-2)

주제

1. 인덱스
2. 트랜젝션
3. 사용자/사용자의 권한 다루기

 

---

인덱스

1) 인덱스란

• 특정 컬럼에 대한 목차를 생성 > WHERE 등으로 검색하거나 ORDER by등을 활용하여 정렬시 그 속도를 가속화할 수 있음
• But 테이블 내용변경(추가,수정,삭제)시 시간을 더 소모함(인덱스라는 추가내용을 달고 있는 거니까)
• 지금(그냥 공부하는 지금)은 잘모르겠지만, 나중에 데이터가 많아지면 최적화가 필요할 것임. 그때 인덱스와 더불어 엔진(innoDB등)에 대해서 공부해서 최적화를 도입하는게 좋음

 

2) 인덱스 사용이 적합한 곳

• 따라서 데이터의 변경이 잦지 않고, WHERE이나 ORDER BY등이 많이 사용되는 컬럼에 적합
• JOIN에 사용되는 컬럼
• B-Tree 인덱스의 경우 CARDINALITY가 높은컬럼에 유리함.
  • Cardinality가 높다 = 중복도가 낮다.
  • 예를 들어 성별은 '남,여'만 반복 > 중복도가 높다 > cardinalty가 높다 > 부적합

 

3)인덱스의 종류

• B-tree
  • 기본인덱스
  • Cardinality가 높은 컬럼에 적합
• FULL-TEXT 인덱스
  • 다수의 단어 등을 포함하는 컬럼에서 단어 및 구문을 검색하기 위함
  • 따라서, CHAR,VARCHAR, TEXT컬럼에만 적용가능하다
  • 데이터를 일정단위로 분할하여 인덱싱
  • SQL엔진이 InnoDB, MYLSM일때만 사용가능하다.
• HASH인덱스
  • 일치 여부(=)만 확인
  • 따라서 인덱스 크기가 작고, 검색이 매우빠름
  • MEMORY,NDB엔진에서만 사용가능
• 비트맵 인덱스
  • Cardinality가 낮은 컬럼에 적합
  • 근데일단 MYSQL에는 없음

 

---

트랜잭션

1) 트랜잭션이란

• 모든 구문이 성공하거나 혹은 전부 실행되지 않거나
  • ex) 은행에서 A가 B한테 송금시, 'A의 계좌에서 돈이 빠져나가는 것'과 'B의 계좌에 돈이 들어오는 것'은 반드시 함께 시행되어야 함. 둘 중 하나라도 실패하면 둘 모두 실패되어야만 함
• 이러한 특성을 '원자성(더이상 쪼갤 수 없음)'이라고 함

-- 트랜잭션시작
START TRANSACTION;

-- 구문1
DELETE FROM sections 
WHERE section_id > 0;

-- 구문2
SELECT * FROM sections;

-- 롤백
ROLLBACK;

SELECT * FROM sections;

• 트랜잭션이 시작되고 구문1과 구문2가 순차적으로 시행됨. 만약 둘중하나라도 에러발생시, ROLLBACK에 의해 모든 작업이 백지화됨

 

2) 트랜잭션의 사용

• 트랜잭션이 선언되고 수행되는 구문은 바로바로 하드나 ssd에서 적용되는게 아니라 메모리에서 수행됨
  • 메모리는 휘발성이 있는 일시적인 곳으로, 작업을 초기화하거나 하는게 수월함.(
  • 위의 예시에서 구문1의 수행내용을 초기화
• savepoint와 commit을 통해 메모리상의 작업(변화)를 저장할 수 있음
  • savepoint는 일종의 중간지점을 만드는 것임. '일단 이까지 한 작업은 반영하겠다~' 는 느낌. 이후에 롤백을 해도 save포인트 이전의 구문들은 초기화되지 않음
  • commit은 트랜잭션의 모든 구문을 영구저장. 모든 작업을 끝내고 저장하는 느낌임 (즉, commit후에는 rollback을 해도 어떤 내용도 롤백되지 않음)
• 보통 sql구문이 아니라 장고나 스프링같은 소프트웨어에서 트랜잭션의 기능을 사용함

---

사용자의 권한

• 일반적으로 root계정이 아니라, 어느정도의 특정 계정에 일부 권한만을 부여해서 사용할 수 있도록 함(악용이나 해킹을 방지하기 위함)
• 현재 사용자를 확인하는 방법은 두가지임
  • ADMINISTRATION > USERS AND Priviliages
  • 혹은 다음을 입력

USE mysql;
SELECT * FROM user;

1)사용자 생성 및 삭제

-- CREATE USER '사용자명'@'접속위치' IDENTIFIED BY '비밀번호';

-- CREATE USER 'user_1'@'12.345.678.90' IDENTIFIED BY 'abcdefg';
-- >12.345.678.90의 아이피에서 user_1이라는 사용자명을 가진 유저가 abcdefg 라는 비번을 입력하고 원격으로 해당 db에 접속가능

-- CREATE USER 'user_1'@'localhost' IDENTIFIED BY 'abcdefg';   
-- >로컬호스트로만 접속

CREATE USER 'user_1'@'%' IDENTIFIED BY 'abcdefg';
--  user_1이라는 사용자 유저가 abcdefg라는 비번을 입력하고 어디서든지(%) 접속가능


-- 사용자 삭제
-- DROP USER 'user_1'@'%';

 

2)사용자에게 권한을 부여

-- 사용자에게 특정 db(혹은 table에 관한)에 대한 조회 권한 추가
GRANT SELECT ON mydatabase.businesses
TO 'user_1'@'%';

-- 사용자에게 특정 db(혹은 table에 관한)에 대한 업데이트,삭제 권한 추가
GRANT UPDATE, DELETE ON mydatabase.businesses
TO 'user_1'@'%';

-- 사용자에게 특정 db(혹은 table에 관한)에 대한 사용자의 권한을 박탈
REVOKE SELECT,UPDATE,DELETE ON mydatabase.businesses
FROM 'user_1'@'%';

-- 특정 사용자 권한 조회
SHOW GRANTS FOR 'user_1'@'%';

-- 데이터베이스의 모든 테이블에 대한 모든 권한 부여
GRANT ALL PRIVILEGES ON mydatabase.*
TO 'user_1'@'%';

• db뿐만 아니라, view에 대한 권한도 줄 수 잇음!!