MySQL 실습

Summary

이 posting은 Database 과목을 수강하며 진행하는 실습 및
Docker && Kubernetes 환경에 익숙해 지기 위해 내용을 다시한번 정리하는 데에 의미가 있음.

docker image를 이용해 설치한 MySQL server에서 MySQL 실습을 진행.

• DML (Data Manuplation Language) (Data CRUD).

• launchpad.net 의 sample database를 이용한 실습.

• w3schools.com 의 ‘Learn SQL’을 통한 실습.

MySQL Container 실행 및 접속

docker engine 위에서 작동하는 container 환경이므로, 이미 다운받은 MySQL image를 이용해 실행시킨 MySQL Container를 재가동 시킨다.

(만약 MySQL 이미지가 없는 상태에서 최초로 돌릴 경우,)
docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=mysql --name=mysql1 mysql5.7

존재하는 Container의 재가동 이므로 docker start [Container명] 으로 Container를 가동시키고, docker exec -it [Container명] [prompt] 로 가동중인 Container에 prompt로 shell에 접속하라는 명령을 내린다.

이 때 -i option은 STDIN 표준 입출력을 열라는 의미이고, -t option은 가상 tty 를 통해 접속하라는 의미이다.

launchpad.net/test-db Download 및 환경 setting

launchpad.net 의 test-db는 employees (회사 직원) 에 대한 약 30만개의 record를 가진 table이다.
이 sample database 를 다운받기 위해서, docker 위의 Container 환경임에 주의하여 다운한다.

$ docker exec -it mysql1 bash
# apt update
# apt install nano
# apt install bzip2
# apt install wget
# wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2     # launchpad.net site의 db down button-우클릭하여 link 복사.
# bzip2 -d employees_db-full-1.0.6.tar.bz2
# tar xvf employees_db-full-1.0.6.tar
# cd employees_db       #반드시 이 employees_db directory 내에서 mysql 실행시켜야 함.
# mysql -uroot -p

mysql > source employees.sql

여기까지 하면 환경 setting이 다 됐다.

이제 employees db로 들어가서 간단한 명령 예제 몇 가지를 살펴보자.

MySQL Retrieve

mysql > show databases;

mysql > use employees;

mysql server에 존재하는 database들을 보여주라는 명령을 내리면, 아까 source명령으로 추가한 employees db가 보인다. use로 employees 사용한다고 지정한다.

mysql > show tables; 

mysql > desc employees; 

employees db 의 table들을 살펴보면, 6가지가 존재한다. desc명령으로 그 중 employees table의 column 정보를 상세히 표시한다.

mysql > select count(*) from employees; 

mysql > select * from employees limit 10; 

count로 employees table의 record 가 몇 개나 되는지 살펴본다. 약 30만개가 존재하므로, 그 중에서 10개만 추려서 가져온다.(limit)

mysql > select dept_name from departments; 

이번엔 departments table을 살펴보자. desc로 departments table column들의 속성을 살펴보고, dept_name (부서명) 만을 select로 가져왔다.

이렇게 dept_name과 같이 특정 column 만을 가져오는 것을 projection 연산 이라 한다.

MySQL Indexing

DataBaseManagamentSystem 은 CRUD(Create, Retrieve, Update, Delete) 중 무엇보다도 Retrieve (검색) 에 최적화 되어 있는 program이다.

어떻게 검색을 빠르게 할 수 있을까? - Index 라는 것을 사용한다.

data를 검색할 때, index를 사용하는 것과 사용하지 않는 것은 시간적으로 매우 큰 차이를 보인다. MySQL에서는 이러한 B-Tree 구조의 index를 사용하여 Query를 하므로써 빠른 retrieve를 제공한다.

mysql > show indexes from employees\G;

employees table의 index를 살펴보면 현재는 primary key(기본키) 인 ‘emp_no’ column만 index로 등록되어 있다. (기본키는 자동으로 index 제공.)

mysql > select * from employees where emp_no=20000;

다음과 같이 index를 사용하는 emp_no 를 기반으로 query 를 하면, 소요시간이 0.00sec 로 거의 걸리지 않는 것을 확인할 수 있다.
(explain으로 실행계획을 출력해 보면 rows 가 1로 표시되면서 단 한번의 검색으로 찾았다는 것을 알 수 있음.)

mysql > select * from employees where first_name='Sumant' and last_name='Peac';

반면, 이렇게 index등록이 되어있지 않은 key (first_name 과 last_name) 으로 검색을 하면 0.09sec 나 소요되는 것을 볼 수 있다. explain으로 실행계획 출력 시, rows는 299468로 거의 모든 record 를 뒤져서 검색한 것을 확인할 수 있다.

따라서 우리는 자주 검색하는 조건들에 대해 index를 붙여줘야 한다.

• index 생성

    mysql > create index <index명> on <table명> (컬럼명1, 컬럼명2, ...);
  

이렇게 first_name 과 last_name 에 대해 name 이라는 index 명으로 index를 생성한 후, 다시 retrieve 했을 때 단 1번만에 매우 빠르게 찾아내는 것을 알 수 있다.

• index 삭제

    mysql > alter table <table명> drop index <index명>;
  

참고로 MySQL에서는 cache 기능 등을 지원하므로 한 번 검색한 data를 다시 검색하면 caching 효과에 의해 검색시간이 줄어들 수 있다. (부정확 할 수 있음)

w3schools.com 에서 web CLI로 sql을 실습해 볼 수 있다.