Введение

В этой лабораторной работе мы изучим и попрактикуемся в индексировании, просмотре, резервном копировании и восстановлении. Эти концепции очень важны для менеджера базы данных.

Цель обучения

• Создать индекс
• Создать представление
• Резервное копирование и восстановление

Подготовка

Прежде чем начать, нам нужно подготовить среду.

Запустите службу MySQL и войдите в систему как root.

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

Загрузить данные в файл. Для создания базы данных вам необходимо ввести команду в консоли MySQL:

source ~/project/init-database.txt

Индекс

Индекс представляет собой структуру, связанную с таблицей. Его роль эквивалентна каталогу книги. Вы можете быстро найти контент по номеру страницы в каталоге.

Если вы хотите запросить таблицу, содержащую большое количество записей и не имеющую индекса, то все записи будут извлечены одна за другой, чтобы соответствовать условиям поиска, и будут возвращены записи, соответствующие условиям. . Это отнимает очень много времени и приводит к большому количеству операций дискового ввода-вывода.

Если в таблице существует индекс, то мы можем быстро найти данные в таблице по значению индекса, что значительно ускоряет процесс запроса.

Существует два способа настроить индекс для определенного столбца:

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

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

Давайте используем эти два оператора для построения индекса.

Создайте индекс idx_id в столбце id таблицы сотрудников:

ALTER TABLE employee ADD INDEX idx_id (id);

создайте индекс idx_name в столбце name в таблице сотрудников

CREATE INDEX idx_name ON employee (name);

Мы используем индекс для ускорения процесса запроса. Когда данных недостаточно, мы не сможем ощутить его волшебную силу. Здесь давайте воспользуемся командой SHOW INDEX FROM table name для просмотра только что созданного индекса.

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 для запроса данных, база данных соответственно извлекает данные из исходной таблицы.
• Поскольку данные в представлении зависят от того, что хранится в исходной таблице, как только данные в таблице изменятся, то, что мы видим в представлении, также изменится.
• Рассматривать представление как таблицу.

Формат оператора, используемый для создания представления:

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

Из оператора мы видим, что последняя часть представляет собой оператор SELECT, что означает, что представление также может быть построено на нескольких таблицах. Все, что нам нужно сделать, это использовать подзапросы или присоединиться к оператору SELECT.

Теперь давайте создадим простое представление с именем v_emp, которое включает три столбца v_name, v_age, v_phone:

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 для резервного копирования. Он создает файл сценария SQL, который содержит все необходимые команды для воссоздания базы данных с нуля, такие как CREATE, INSERT и т. д.

Заявление об использовании резервной копии 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

Этот оператор восстанавливает базу данных mysql_labex из файла import-database.txt.

Есть еще один способ восстановить базу данных, но перед этим нам нужно сначала создать пустую базу данных с именем 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