소개

본 실습에서는 인덱스, 뷰, 백업, 복구에 대해 배우고 실습해보겠습니다. 이러한 개념은 데이터베이스 관리자에게 매우 중요합니다.

학습 목표

• 색인 생성
• 뷰 만들기
• 백업 및 복구

준비

시작하기 전에 환경을 준비해야 합니다.

MySQL 서비스를 시작하고 루트로 로그인합니다.

cd ~/project
sudo service mysql start
mysql -u root

파일에 데이터를 로드합니다. 데이터베이스를 구축하려면 MySQL 콘솔에 다음 명령을 입력해야 합니다.

source ~/project/init-database.txt

색인

인덱스는 테이블 관련 구조입니다. 그 역할은 책의 디렉토리와 동일합니다. 디렉토리의 페이지 번호에 따라 콘텐츠를 빠르게 찾을 수 있습니다.

레코드가 많은 테이블을 조회하려고 하는데, 인덱스가 없는 테이블을 조회할 경우 검색 조건에 맞는 모든 레코드를 하나씩 꺼내어 조건에 맞는 레코드를 반환합니다. . 시간이 많이 걸리고 디스크 I/O 작업이 많이 발생합니다.

테이블에 인덱스가 존재하는 경우 인덱스 값으로 테이블의 데이터를 빠르게 찾을 수 있어 쿼리 속도가 크게 향상됩니다.

특정 열에 색인을 설정하는 방법에는 두 가지가 있습니다.

ALTER TABLE table name ADD INDEX index name (column name);

CREATE INDEX index name ON table name (column name);

이 두 문을 사용하여 색인을 작성해 보겠습니다.

직원 테이블의 id 열에 idx_id 인덱스를 만듭니다.

ALTER TABLE employee ADD INDEX idx_id (id);

직원 테이블의 이름 열에 idx_name 인덱스를 구축합니다.

CREATE INDEX idx_name ON employee (name);

우리는 쿼리 프로세스 속도를 높이기 위해 인덱스를 사용합니다. 데이터가 충분하지 않으면 그 마력을 느낄 수 없습니다. 여기서는 SHOW INDEX FROM 테이블 이름 명령을 사용하여 방금 생성한 인덱스를 확인합니다.

SHOW INDEX FROM employee;

MariaDB [mysql_labex]> ALTER TABLE employee ADD INDEX idx_id (id);
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mysql_labex]> SHOW INDEX FROM employee;
 ---------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- 
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
 ---------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- 
| employee |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| employee |          0 | phone    |            1 | phone       | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| employee |          1 | emp_fk   |            1 | in_dpt      | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| employee |          1 | idx_id   |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| employee |          1 | idx_name |            1 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
 ---------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- 
5 rows in set (0.000 sec)

SELECT 문을 사용하여 쿼리하면 WHERE 조건이 자동으로 인덱스 존재 여부를 판단합니다.

보다

뷰는 하나 이상의 테이블에서 파생된 가상 테이블입니다. 데이터베이스의 전체 데이터를 볼 필요 없이 시스템에서 제공하는 특별한 데이터를 볼 수 있는 창과 같습니다. 관심 있는 분야에 집중할 수 있습니다.

"뷰는 가상 테이블입니다"를 어떻게 해석하나요?

• View의 정의만 데이터베이스에 저장되고 해당 데이터는 원본 테이블에 저장됩니다.
• View를 사용하여 데이터를 쿼리하면 데이터베이스는 그에 따라 원본 테이블에서 데이터를 추출합니다.
• View의 데이터는 원본 테이블에 저장된 내용에 따라 달라지므로 테이블의 데이터가 변경되면 View에 표시되는 내용도 변경됩니다.
• 뷰를 테이블로 처리합니다.

뷰를 생성하는 데 사용되는 문 형식:

CREATE VIEW view name (column a, column b, column c) AS SELECT column 1, column 2, column 3 FROM table name;

문에서 후반 부분이 SELECT 문임을 알 수 있습니다. 즉, View를 여러 테이블에 구축할 수도 있습니다. 우리가 해야 할 일은 하위 쿼리를 사용하거나 SELECT 문에 조인하는 것뿐입니다.

이제 v_name, v_age, v_phone: 세 개의 열을 포함하는 v_emp
라는 간단한 뷰를 만들어 보겠습니다.

CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;

그런 다음
를 입력합니다.

SELECT * FROM v_emp;

MariaDB [mysql_labex]> CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql_labex]> SELECT * FROM v_emp;
 -------- ------- --------- 
| v_name | v_age | v_phone |
 -------- ------- --------- 
| Tom    |    26 |  119119 |
| Jack   |    24 |  120120 |
| Jobs   |  NULL |   19283 |
| Tony   |  NULL |  102938 |
| Rose   |    22 |  114114 |
 -------- ------- --------- 
5 rows in set (0.000 sec)

지원

보안상의 이유로 데이터베이스 관리에서는 백업이 매우 중요합니다.

내보낸 파일은 데이터베이스의 데이터만 저장하는 반면, 백업은 데이터, 제약 조건, 인덱스, 뷰 등을 포함한 전체 데이터베이스 구조를 새 파일에 저장합니다.

mysqldump는 MySQL의 백업용 실용적인 프로그램입니다. CREATE, INSERT 등과 같이 데이터베이스를 처음부터 다시 만드는 데 필요한 모든 필수 명령이 포함된 SQL 스크립트 파일을 생성합니다.

mysqldump 백업을 사용하기 위한 명령문:

mysqldump -u root database name > backup file name;   #backup entire database

mysqldump -u root database name table name > backup file name;  #backup the entire table

전체 데이터베이스 mysql_labex를 백업해 보세요. 파일 이름을 bak.sql로 지정합니다. 먼저 Ctrl Z를 눌러 MySQL 콘솔을 종료한 다음 터미널을 열고 다음 명령을 입력합니다.

cd ~/project/
mysqldump -u root mysql_labex > bak.sql;

"ls" 명령을 사용하면 백업 파일 bak.sql이 표시됩니다.

cat bak.sql

-- MariaDB dump 10.19  Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: mysql_labex
-- ------------------------------------------------------
-- Server version       10.6.12-MariaDB-0ubuntu0.22.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

……

회복

이 실습 초반에 백업 파일을 사용하여 데이터베이스를 복구하는 연습을 했습니다. 우리는 다음과 유사한 명령을 사용했습니다:

source ~/project/init-database.txt

이 명령문은 import-database.txt 파일에서 mysql_labex 데이터베이스를 복구합니다.

데이터베이스를 복구하는 또 다른 방법이 있지만 그 전에 먼저 test라는 이름의 빈 데이터베이스를 만들어야 합니다.

mysql -u root
CREATE DATABASE test;

MariaDB [(none)]> CREATE DATABASE test;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| mysql              |
| mysql_labex        |
| performance_schema |
| sys                |
| test               |
 -------------------- 
6 rows in set (0.000 sec)

Ctrl Z를 눌러 MySQL을 종료합니다. bak.sql을 복구하여 데이터베이스를 테스트합니다:

mysql -u root test 



테스트 데이터베이스의 테이블을 보는 명령을 입력하여 복구 성공 여부를 확인할 수 있습니다.


mysql -u root
USE test
SHOW TABLES






MariaDB [(none)]> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> SHOW TABLES;
 ---------------- 
| Tables_in_test |
 ---------------- 
| department     |
| employee       |
| project        |
| table_1        |
 ---------------- 
4 rows in set (0.000 sec)




4개의 테이블이 이미 테스트 데이터베이스로 복구된 것을 확인할 수 있습니다.


  
  
  요약


축하해요! MySQL의 다른 기본 작업에 대한 실습을 완료했습니다. 인덱스, 뷰를 생성하는 방법과 데이터베이스를 백업 및 복구하는 방법을 배웠습니다.



? 지금 연습하세요: 기타 기본 작업




  
  
  더 자세히 알고 싶으십니까?

• ? 최신 MySQL 스킬 트리 알아보기
• ? 더 많은 MySQL 튜토리얼 읽기
• ? Discord에 참여하거나 @WeAreLabEx로 트윗해 주세요.