Menguasai Operasi Pangkalan Data: Indeks, Pandangan, Sandaran dan Pemulihan

pengenalan

Dalam makmal ini, kita akan belajar dan mengamalkan indeks, paparan, sandaran dan pemulihan. Konsep ini sangat penting untuk pengurus pangkalan data.

Objektif Pembelajaran

• Buat indeks
• Buat paparan
• Sandaran dan Pemulihan

Persediaan

Sebelum bermula, kita perlu menyediakan persekitaran.

Mulakan perkhidmatan MySQL dan log masuk sebagai root.

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

Muatkan data dalam fail. Anda perlu memasukkan arahan dalam konsol MySQL untuk membina pangkalan data:

source ~/project/init-database.txt

Indeks

Indeks ialah struktur berkaitan jadual. Peranannya setara dengan direktori buku. Anda boleh mencari kandungan dengan cepat mengikut nombor halaman dalam direktori.

Apabila anda ingin menanyakan jadual yang mempunyai bilangan rekod yang banyak, dan ia tidak mempunyai indeks, maka semua rekod akan ditarik keluar untuk memadankan keadaan carian satu demi satu, dan mengembalikan rekod yang sepadan dengan syarat . Ia sangat memakan masa dan menghasilkan sejumlah besar operasi I/O cakera.

Jika indeks wujud dalam jadual, maka kita boleh mencari data dalam jadual dengan cepat mengikut nilai indeks, yang sangat mempercepatkan proses pertanyaan.

Terdapat dua cara untuk menyediakan indeks pada lajur tertentu:

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

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

Mari kita gunakan dua pernyataan ini untuk membina indeks.

Bina indeks idx_id dalam lajur id dalam jadual pekerja:

ALTER TABLE employee ADD INDEX idx_id (id);

bina indeks idx_name dalam lajur nama dalam jadual pekerja

CREATE INDEX idx_name ON employee (name);

Kami menggunakan indeks untuk mempercepatkan proses pertanyaan. Apabila data tidak mencukupi, kami tidak akan dapat merasakan kuasa ajaibnya. Di sini mari kita gunakan arahan SHOW INDEX FROM table name untuk melihat indeks yang baru kita buat.

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)

Apabila kami menggunakan pernyataan SELECT untuk membuat pertanyaan, keadaan WHERE secara automatik akan menilai sama ada terdapat indeks.

Lihat

Pandangan ialah jadual maya yang diperoleh daripada satu atau lebih jadual. Ia seperti tetingkap di mana orang boleh melihat data khas yang disediakan oleh sistem supaya mereka tidak perlu melihat keseluruhan data dalam pangkalan data. Mereka boleh memberi tumpuan kepada yang mereka minati.

Bagaimana untuk mentafsir "Paparan ialah jadual maya"?

• Hanya takrifan View disimpan dalam pangkalan data manakala datanya disimpan dalam jadual asal;
• Apabila kami menggunakan View untuk menanyakan data, pangkalan data akan mengekstrak data daripada jadual asal secara sepadan.
• Memandangkan data dalam Paparan bergantung pada apa yang disimpan dalam jadual asal, setelah data dalam jadual berubah, perkara yang kita lihat dalam Paparan akan berubah juga.
• Anggap View sebagai jadual.

Format pernyataan yang digunakan untuk membuat Paparan:

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

Daripada pernyataan itu, kita dapat melihat bahawa bahagian terakhir ialah pernyataan SELECT, yang bermaksud View juga boleh dibina pada berbilang jadual. Apa yang perlu kita lakukan ialah menggunakan subkueri atau sertai dalam pernyataan PILIH.

Sekarang mari buat Paparan ringkas bernama v_emp yang merangkumi tiga lajur v_name, v_age, v_phone:

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

dan kemudian masukkan

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)

Sandaran

Atas sebab keselamatan, sandaran amat penting dalam pengurusan pangkalan data.

Fail yang dieksport hanya menyimpan data dalam pangkalan data manakala sandaran menyimpan keseluruhan struktur pangkalan data termasuk data, kekangan, indeks, paparan dsb. ke fail baharu.

mysqldump ialah program praktikal dalam MySQL untuk sandaran. Ia menghasilkan fail skrip SQL yang mengandungi semua arahan penting untuk mencipta semula pangkalan data dari awal, seperti CREATE, INSERT dll.

Penyata untuk menggunakan sandaran 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

Cuba sandarkan keseluruhan pangkalan data mysql_labex. Namakan fail kepada bak.sql. Mula-mula tekan Ctrl+Z untuk keluar dari konsol MySQL, kemudian buka terminal dan masukkan arahan:

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

Gunakan arahan "ls" dan kita akan melihat fail sandaran 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 */;

……

Pemulihan

Terdahulu di makmal ini, kami berlatih menggunakan fail sandaran untuk memulihkan pangkalan data. Kami menggunakan arahan yang serupa dengan ini:

source ~/project/init-database.txt

Pernyataan ini memulihkan pangkalan data mysql_labex daripada fail import-database.txt.

Ada cara lain untuk memulihkan pangkalan data, tetapi sebelum itu, kita perlu mencipta pangkalan data kosong bernama ujian dahulu:

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 untuk keluar dari MySQL. Pulihkan bak.sql untuk uji pangkalan data:

mysql -u root test < bak.sql

Kami boleh mengesahkan sama ada pemulihan berjaya atau tidak dengan memasukkan arahan untuk melihat jadual dalam pangkalan data ujian:

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

Atas ialah kandungan terperinci Menguasai Operasi Pangkalan Data: Indeks, Pandangan, Sandaran dan Pemulihan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!