(9)MySQL_기본문법

트랜잭션(Transaction)

• 분할이 불가능한 업무처리의 단위
• 한꺼번에 수행되어야 할 연산 모음
• commit: 모든 작업들을 정상 처리하겠다고 확정하는 명령어로서, 해당 처리 과정을 DB에 영구적으로 저장
•  rollback: 작업 중 문제가 발생되어 트랜잭션의 처리 과정에서 발생한 변경사항을 모두 취소하는 명령어

start transaction
       블록안의 명령어들은 하나의 명령어 처럼 처리됨
        ...
        성공하던지, 실패하던지 둥 중 하나의 결과가 됨
        문제가 발생하면 rollback;
정상적인 처리가 완료되면 commit;

트랜잭션의 특징

• 원자성: 트랜잭션이 데이터베이스에 모두 반영된던가, 아니면 전혀 반영되지 않아야함
• 일관성: 트랜잭션의 작업 처리 결과가 항상 일관성이 있어야 함
• 독립성: 어떤 하나의 트랜잭션이라도, 다른 트랜잭션의 연산에 끼어들 수 없음
• 영구성: 결과는 영구적으로 반영되어야 함

자동 커밋 확인

show variables like '%commit%';

# autocommit: ON -> 자동으로 commit 해줌
# set autocommit = 0 (off), set autocommit = 1 (on)

set autocommit = 0;

select * from product;
start transaction; # 트랜잭션의 시작, commit 또는 rollback으로 끝내야함
insert into product values ('100005','고철','팔아요','100',now());
select * from product;
commit; # 트랜잭션을 DB에 적용

start transaction; # 트랜잭션의 시작, commit 또는 rollback으로 끝내야함
insert into product values ('100006','공병','팔아요','50',now());

select * from product;

rollback; # 트랜잭션을 취소하고 start transaction 실행 전 상태로 롤백함
select * from product;

트랜잭션의 예외

• DDL문(create, drop, alter, rename, truncate)에 대해 예외를 적용 -> rollback 대상이 아님

truncate

• 개별적으로 행을 삭제할 수 없으며, 테이블 내부의 모등 행(데이터)를 삭제
• rollback이 불가능
• 트랜잭션 로그에 한 번만 기록하므로 delete 구문보다 성능 면에서 빠름
• truncate table 테이블명 = delete from 테이블명

select * from product_new;

start transaction;
truncate table product_new;
select * from product_new;
rollback;

• 롤백을 해도 truncate 라서 복구가 안되는 것을 볼 수 있다.
• 다시 on으로 바꾸어주기

set autocommit = 1;
show variables like '%commit%';

인덱스(index)

• 테이블의 동작속도(조회)를 높여주는 자료구조
• 데이터의 위치를 빠르게 찾아주는 역할
• MYI(MySQL Index)파일에 저장
• 인덱스를 설정하지 않으면 Table Full Scan이 일어나 성능이 저하되거나 장애가 발생할 수 있음
• 조회속도는 빨라지지만 update, insert, delete의 속도는 저하될 수 있음
• MySQL에서는 primary key, unique 제약조건을 사용하면 해당 컬럼에 index가 적용됨
• 인덱스는 하나 또는 여러 개의 컬럼에 설정할 수 있음. 
•  where절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하면 성능에 아무런 효과가 없음
• 가급적 update가 안되는 값을 설정하는 것이 좋음

order by, group by와 index

• order by 인덱스컬럼, 일반컬럼: 복수의 키에 대해 order by를 사용한 경우 / 효과 없음
• where 컬림1 = '값' order by 인덱스컬럼: 연속하지 않은 컬럼에 대해 order by를 실행한 경우 / 효과 없음
• order by 인덱스컬럼1 desc, 인덱스컬럼2 asc: desc와 asc를 혼합해서 사용한 경우 / 효과 없음
• group by 일반컬럼1 order by 인덱스컬럼: group by와 order by의 컬럼이 다른 경우 / 효과 없음
• order by 함수(인덱스컬럼): order by 절에 컬럼이 아닌 다른 표현을 사용한 경우

 인덱스 문법

• create index 인데스명 on 테이블명(필드이름)
• create index 인덱스명 on 테이블명(필드이름1, 필드이름2 ...)

인덱스 조회하기

• show index from 테이블명;

인덱스 삭제하기

• alter table 테이블명 drop index 인덱스명;

show index from member;
create index idx_hp on member(hp);

show index from member;
alter table member drop index idx_hp;