介绍

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

学习目标

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

准备

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

启动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 语句中使用子查询或联接。

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



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


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