파트 1. MySQL 전체 운영 실습과정 미리 실습하기
1. 데이터베이스 구축 절차
사진을 토대로 MySQL을 이용한 데이터베이스를 구축해보자.
cf) MySQL에서는 대소문자를 구분하지 않는다. 따라서 대문자로 작성해도 소문자랑 똑같다.
테이블 생성
- Table의 이름은 대문자로 주어도 소문자로 저장된다.
- Schemas는 데이터베이스의 다른 말이라고 보면 된다.
- Schemas 창에서 마우스 오른쪽 클릭으로 Create Schemas를 클릭해서 shopdb라는 데이터베이스를 만들고 해당 데이터베이스에서 Table에 오른쪽 클릭으로 Create Table하을 클릭해서 새로운 테이블을 만들어 작성하면 된다.
- NN은 Not Null로 비어서는 안된다는 뜻이다.
데이터 입력
데이터를 입력하고자 하는 테이블을 오른쪽 클릭 -> Select Rows를 클릭하면 해당 테이블을 조회할 수 있다. 조회한 테이블에서 데이터를 입력해주면 된다. 작성하고 오른쪽 아래 Apply 눌러주면 입력이 완료된다.
데이터 활용
테이블은 데이터베이스 안에 들어있고 데이터베이스는 여러 개가 있다. 그렇다면 먼저 어떤 데이터베이스를 사용할 것인지를 지정해야한다. 왼쪽 SCHEMAS 탭에서 사용할 데이터베이스를 더블클릭하면 폰트가 두꺼워지는데 이것의 의미는 해당 데이터베이스를 사용하겠다는 의미로 보면 된다.
위 그림을 보면 shopdb가 굵은 폰트로 변경된 것을 확인할 수 있고, 쿼리 탭에서 문법에 맞는 SQL을 입력한 후에 위쪽에 번개모양을 클릭하거나 ctrl + shift + enter 을 누르면 실행된다. 실행되면 그림과 같이 테이블을 확인할 수 있고 아래쪽에 output 탭에서 4 row(s) returned로 메시지를 확인할 수 있다.
- SELECT * 는 모든 열이 나오게 되는데 특정한 멤버의 이름과 주소만 알고 싶다면 * 대신 필요한 열들을 작성해주면 된다.
- SELECT의 기본 형식은 SELECT 열이름 FROM 테이블이름 [WHERE 조건] 형식을 갖는다.
- 위 그림과 같은 형태에서 실행하면 쿼리 창에 있는 모든 SQL문이 수행되기 때문에 파란 동그라미를 보면 2개가 실행된 것을 볼 수 있다.
- 이를 방지하기 위해 실행하고 싶은 내용만 드래그한 후 실행하면 드래그한 내용만 실행된다.
2. 테이블 외의 데이터베이스 개체의 활용
인덱스
인덱스란 대부분의 책의 제일 뒤에 붙어 있는 찾아보기와 같은 개념이다. 책의 내용 중에서 특정 단어를 찾고자 할 때 책의 첫 페이지부터 마지막까지 한 페이지씩 전부를 찾아보는 것보다 책 뒤의 찾아보기를 활용해 색인에 나와 있는 페이지로 바로 찾아가는 것이 훨씬 빠를 것이다. 간단하게 인덱스를 사용해보자.
실습하기 위해서 위와 같이 shopdb 데이터베이스에서 쿼리를 실행해서 샘플DB인 employees에서 대량의 데이터를 복사해서 500건의 데이터가 있는 indextbl을 생성했다. Mary를 어떻게 찾았는지는 Execution plan창에서 보면 알 수 있다. Full table scan은 쉽게 생각하면 책의 1페이지부터 전체를 찾아봤다는 뜻이다. 이제 인덱스를 넣고 다시 찾아보자.
인덱스를 넣고 Execution plan 를 확인하면 인덱스를 이용해 찾은 것을 알 수 있다. 데이터가 많을수록 인덱스를 활용해서 찾는 것이 훨씬 빠르다는 것을 기억하자.
뷰
뷰란 가상의 테이블이라고 생각하면 된다. 사용자 입장에서는 테이블과 동일하게 보이지만, 뷰는 실제 행데이터를 가지고 있지 않다. 실체는 없는 것이며, 진짜 테이블에 링크된 개념이라고 생각하면 된다. 그래서 뷰를 SELECT하면 결국 진짜 테이블의 데이터를 조회하는 것과 동일한 결과가 된다.
예를 들면, 기존에 구축된 쇼핑몰을 운영하다가 회원의 주소를 대량으로 변경해주는 작업이 필요하다고 가정해보자. 새로운 알바생을 고용하여 회원의 다른 정보는 그대로 두고 주소만 변경하는 일을 시키려고 한다. 그런데 만약 이 알바생에게 회원 테이블을 사용할 권한을 준다면 회원의 주소 외에 다른 중요한 개인정보들을 열람하게 된다. 이 경우에 아이디와 주소만 있는 뷰를 생성하면 해결할 수 있다. 뷰는 실체가 있는 것이 아니라, 회원 테이블의 링크 개념이므로 실제 데이터는 회원 테이블에만 존재하기 때문에 데이터의 중복이 발생되지 않는다. 또한, 알바생은 뷰에만 접근 권한을 줘서 다른 정보들은 볼수 없게 된다.
뷰의 실체는 select문이다. select로 고른 데이터를 뷰로 만든 것이다. 뷰를 만들고 select 뷰를 실행하면 만들어진 뷰를 조회할 수 있다.
스토어드 프로시저
스토어드 프로시저란 MySQL에서 제공해주는 프로그래밍 기능을 말한다. 즉, SQL문을 하나로 묶어서 편리하게 사용하는 기능이다. 물론, SQL을 묶는 개념 외에 다른 프로그래밍 언어와 같은 기능을 담당할 수도 있다. 실무에서는 SQL문(주로 SELECT)을 매번 하나하나 수행하기보다는 스토어드 프로시저로 만들어 놓은 후에 스토어드 프로시저를 호출하는 방식을 많이 사용한다.
DELIMITER는 구분 문자를 의미한다. 뒤에 // 가 나오면 기존의 세미콜론을 //로 대신한다는 의미이다. 이는 CREATE PROCEDURE ~~ END까지 하나의 단락으로 묶어주는 효과를 갖는다 정도로 기억하면 된다. 그리고 마지막 행에서는 세미콜론으로 돌려놓아야 한다.
CALL 프로시저이름 을 사용하면 만들었던 내용을 한 번에 불러올 수 있다.
트리거
트리거란 테이블에 부탁되어서 테이블에 INSERT나 UPDATE, DELETE 작업이 발생되면 실행되는 코드를 말한다.
예를 들어 당탕이가 가입했던 회원에서 탈퇴하는 경우를 생각해보자. 회원에서 탈퇴하면 간단히 회원 테이블에서 당탕이의 정보를 삭제하면 된다.(당탕이의 행 데이터를 지우면 된다.) 그런데 나중에 회원에서 탈퇴한 사람이 누구인지, 당탕이는 이미 데이터베이스에 존재하지 않기 때문에 알 수 있는 방법이 없다. 그래서 당탕이의 행 데이터를 삭제하기 전에 그 내용을 다른 곳에 먼저 복사해 놓으면 된다. 그런데 이 작업을 매번 수작업으로 하기에는 번거롭다. 그래서 회원 테이블에 삭제 작업이 일어날 경우에 삭제되기 전에 미리 다른 곳에 삭제될 데이터를 자동으로 저장해주는 기능이 있다면 편할 것이다. 이것이 트리거의 대표적인 용도이다.
간단히 실습해보자.
회원 테이블에서 delete된 회원 정보를 저장할 테이블을 만들고, 트리커를 회원 테이블에 연결시키면 된다. 이후 특정 회원을 삭제하면 트리거의 내용대로 deleted된 회원 정보를 저장할 테이블에 저장되게 된다.
3. 데이터베이스 백업 및 관리
백업은 현재의 데이터베이스를 다른 매체에 보관하는 작업을 말하며, 복원은 데이터베이스에 문제가 발생했을 때 다른 매체에 백업된 데이터를 이용해서 원상태로 돌려놓는 작업을 말한다.
간단히 실습해보자.
먼저 백업을 하자. Administration 탭에서 Data Export를 누르고 원하는 데이터베이스를 선택하고 원하는 위치를 설정한 뒤 Start Export를 클릭하면 데이터가 백업된다.
백업을 하려면 Administration 탭에서 data import를 누르고 가져올 데이터파일을 선택하고 어느 데이터베이스 위치로 복원할지 선택 후 start import를 누르면 된다.
MySQL에서 제공하는 간단한 백업과 복원을 실습했지만, 실무에서는 MySQL Enterprise Edition에서 더 고급 백업/복원 툴인 MySQL Enterprise Backup 툴을 사용한다는 점을 알아두자.
