掌握資料庫操作：索引、視圖、備份和恢復
- PHPz原創
- 2024-08-17 22:31:39617瀏覽
介紹
在本實驗中，我們將學習和練習索引、檢視、備份和還原。這些概念對於資料庫管理員來說非常重要。
學習目標
- 建立索引
- 建立視圖
- 備份與復原
準備
開始之前，我們需要準備好環境。
啟動 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_phonev_age
、
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 -u root database name > backup file name; #backup entire database mysqldump -u root database name table name > backup file name; #backup the entire table使用mysqldump備份的語句：
cd ~/project/ mysqldump -u root mysql_labex > bak.sql;嘗試備份整個資料庫 mysql_labex。將檔案命名為 bak.sql。先按Ctrl+Z退出MySQL控制台，然後開啟終端機輸入指令：
cat bak.sql使用指令“ls”，我們會看到備份檔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中文網其他相關文章！