Introdução

Neste laboratório, aprenderemos e praticaremos indexação, visualização, backup e recuperação. Esses conceitos são cruciais para um gerenciador de banco de dados.

Objetivo de aprendizagem

• Criar índice
• Criar visualização
• Backup e recuperação

Preparação

Antes de começarmos, precisamos preparar o ambiente.

Inicie o serviço MySQL e faça login como root.

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

Carregar dados no arquivo. Você precisa inserir o comando no console MySQL para construir o banco de dados:

source ~/project/init-database.txt

Índice

O índice é uma estrutura relacionada à tabela. Sua função equivale ao diretório de um livro. Você pode encontrar rapidamente o conteúdo de acordo com o número da página em um diretório.

Quando você deseja consultar uma tabela que possui um grande número de registros e não possui um índice, todos os registros serão extraídos para corresponder às condições de pesquisa, um por um, e retornar os registros que correspondem às condições . É muito demorado e resulta em um grande número de operações de E/S de disco.

Se existir um índice na tabela, podemos encontrar rapidamente os dados na tabela pelo valor do índice, o que acelera bastante o processo de consulta.

Existem duas maneiras de configurar um índice para uma coluna específica:

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

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

Vamos usar essas duas instruções para construir um índice.

Crie um índice idx_id na coluna id da tabela de funcionários:

ALTER TABLE employee ADD INDEX idx_id (id);

crie um índice idx_name na coluna de nome da tabela de funcionários

CREATE INDEX idx_name ON employee (name);

Usamos o índice para acelerar o processo de consulta. Quando não houver dados suficientes, não seremos capazes de sentir o seu poder mágico. Aqui vamos usar o comando SHOW INDEX FROM table name para visualizar o índice que acabamos de criar.

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)

Quando usamos a instrução SELECT para consultar, a condição WHERE julgará automaticamente se existe um índice.

Visualizar

A visualização é uma tabela virtual derivada de uma ou mais tabelas. É como uma janela através da qual as pessoas podem visualizar dados especiais fornecidos pelo sistema para que não tenham que visualizar todos os dados do banco de dados. Eles podem se concentrar naqueles em que estão interessados.

Como interpretar "A visualização é uma tabela virtual"?

• Apenas a definição de View é armazenada no banco de dados enquanto seus dados são armazenados na tabela original;
• Quando usamos View para consultar dados, o banco de dados extrairá os dados da tabela original de forma correspondente.
• Como os dados na Visualização dependem do que está armazenado na tabela original, depois que os dados na tabela forem alterados, o que vemos na Visualização também mudará.
• Trate a visualização como uma tabela.

Formato de instrução usado para criar a Visualização:

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

Na instrução, podemos ver que a última parte é uma instrução SELECT, o que significa que View também pode ser construída em múltiplas tabelas. Tudo o que precisamos fazer é usar subconsultas ou juntar-se à instrução SELECT.

Agora vamos criar uma visualização simples chamada v_emp que inclui três colunas v_name, v_age, v_phone:

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

e depois digite

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)

Backup

Por motivos de segurança, o backup é extremamente importante no gerenciamento de banco de dados.

O arquivo exportado salva apenas os dados em um banco de dados, enquanto o backup salva toda a estrutura do banco de dados, incluindo dados, restrições, índices, visualização, etc., em um novo arquivo.

mysqldump é um programa prático em MySQL para backup. Ele produz um arquivo de script SQL que contém todos os comandos essenciais para recriar um banco de dados do zero, como CREATE, INSERT etc.

Declaração para usar backup do 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

Tente fazer backup de todo o banco de dados mysql_labex. Nomeie o arquivo como bak.sql. Primeiro pressione Ctrl Z para sair do console MySQL, depois abra o terminal e digite o comando:

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

Use o comando "ls" e veremos o arquivo de backup 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 */;

……

Recuperação

No início deste laboratório, praticamos o uso de um arquivo de backup para recuperar o banco de dados. Usamos um comando semelhante a este:

source ~/project/init-database.txt

Esta instrução recupera o banco de dados mysql_labex do arquivo import-database.txt.

Existe outra maneira de recuperar o banco de dados, mas antes disso, precisamos primeiro criar um banco de dados vazio chamado 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 para sair do MySQL. Recupere o bak.sql para testar o banco de dados:

mysql -u root test 



Podemos confirmar se a recuperação foi bem-sucedida ou não inserindo um comando para visualizar tabelas no banco de dados de teste:


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)




Podemos ver que as 4 tabelas já foram recuperadas no banco de dados de teste.


  
  
  Resumo


Parabéns! Você concluiu o laboratório sobre outras operações básicas no MySQL. Você aprendeu como criar índices, visualizações e como fazer backup e recuperar um banco de dados.



? Pratique agora: outras operações básicas




  
  
  Quer saber mais?

• ? Aprenda as últimas árvores de habilidades do MySQL
• ? Leia mais tutoriais sobre MySQL
• ? Junte-se ao nosso Discord ou envie um tweet para nós @WeAreLabEx