掌握数据库操作：索引、视图、备份和恢复

介绍

在本实验中，我们将学习和练习索引、视图、备份和恢复。这些概念对于数据库管理员来说非常重要。

学习目标

• 创建索引
• 创建视图
• 备份与恢复

准备

开始之前，我们需要准备好环境。

启动 MySQL 服务并以 root 身份登录。

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);

让我们使用这两个语句来构建索引。

在employee表的id列建立idx_id索引：

ALTER TABLE employee ADD INDEX idx_id (id);

在employee表的name列建立idx_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是一个虚拟表”？

• 数据库中只存储View的定义，而其数据存储在原表中；
• 当我们使用View查询数据时，数据库会相应地从原表中提取数据。
• 由于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 语句中使用子查询或 join。

现在让我们创建一个名为 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

此语句从 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 < bak.sql

我们可以通过输入命令查看测试数据库中的表来确认恢复是否成功：

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)

We can see that the 4 tables have already been recovered to the test database.

Summary

Congratulations! You've completed the lab on other basic operations in MySQL. You've learned how to create indexes, views, and how to backup and recover a database.

---

? Practice Now: Other Basic Operations

---

Want to Learn More?

• ? Learn the latest MySQL Skill Trees
• ? Read More MySQL Tutorials
• ? Join our Discord or tweet us @WeAreLabEx

以上是掌握数据库操作：索引、视图、备份和恢复的详细内容。更多信息请关注PHP中文网其他相关文章！