前言

無論是運(yùn)維、開發(fā)、測(cè)試，還是架構(gòu)師，數(shù)據(jù)庫技術(shù)都是一個(gè) 必備加薪神器，那么，一直說學(xué)習(xí)數(shù)據(jù)庫、學(xué) MySQL，到底是要學(xué)習(xí)它的哪些東西呢？

所有DBA的案頭必備書籍，全面的MySQL性能優(yōu)化書籍，幫助讀者深刻理解新的MySQL8調(diào)優(yōu)方法！

一、如何快速掌握 MySQL?

培養(yǎng)興趣

興趣是最好的老師，不論學(xué)習(xí)什么知識(shí)，興趣都可以極大地提高學(xué)習(xí)效率。不管學(xué)習(xí) MySQL5.7 還是 MySQL8.0 都不例外！

夯實(shí) SQL 基礎(chǔ)

計(jì)算機(jī)領(lǐng)域的技術(shù)非常強(qiáng)調(diào)基礎(chǔ)，剛開始學(xué)習(xí)可能還認(rèn)識(shí)不到這一點(diǎn)。隨著技術(shù)應(yīng)用的深 入，只有有著扎實(shí)的基礎(chǔ)功底，才能在技術(shù)的道路上走得更快、更遠(yuǎn)。對(duì)于 MySQL 的學(xué)習(xí)來說， SQL 語句 是其中最為基礎(chǔ)的部分，很多操作都是通過 SQL 語句來實(shí)現(xiàn)的。所以在學(xué)習(xí)的過程中， 讀者要多編寫 SQL 語句，對(duì)于同一個(gè)功能，使用不同的實(shí)現(xiàn)語句來完成，從而深刻理解其不同之處。

?? 這里可以參考文章：基礎(chǔ)篇：數(shù)據(jù)庫 SQL 入門教程

及時(shí)學(xué)習(xí)新知識(shí)

正確、有效地利用搜索引擎，可以搜索到很多關(guān)于 MySQL 的相關(guān)知識(shí)。同時(shí)，參考別 人解決問題的思路，也可以吸取別人的經(jīng)驗(yàn)，及時(shí)獲取最新的技術(shù)資料。

多實(shí)踐操作

數(shù)據(jù)庫系統(tǒng)具有極強(qiáng)的操作性，需要多動(dòng)手上機(jī)操作。在實(shí)際操作的過程中才能發(fā)現(xiàn)問題， 并思考解決問題的方法和思路，只有這樣才能提高實(shí)戰(zhàn)的操作能力。

二、技巧分享

下面分享學(xué)習(xí) MySQL 的 28 個(gè)不得不知道的小技巧！

1、MySQL 中如何使用特殊字符？

諸如單引號(hào) '，雙引號(hào) "，反斜線 \ 等符號(hào)，這些符號(hào)在 MySQL 中不能直接輸入使用，否則會(huì)產(chǎn)生意料之外的結(jié)果。

舉例：

假設(shè) Lucifer 表中需要存入一行記錄，值為 lucifer's dog，其中的單引號(hào) ' 號(hào)，如果不做轉(zhuǎn)義，則無法成功執(zhí)行：

mysql> create table lucifer (id int,name char(100));Query OK, 0 rows affected (0.02 sec)mysql> insert into lucifer values (1,'lucifer's dog');
    '> 
    '> mysql> ^C
mysql>

在 MySQL 中，這些特殊字符稱為轉(zhuǎn)義字符，在輸入時(shí)需要以反斜線符號(hào) \ 開頭，所以在使用單引號(hào)和雙引號(hào)時(shí)應(yīng)分別輸入 \' 或者 \"，輸入反斜線時(shí)應(yīng)該輸入 \\，其他特殊字符還有回車符 \r，換行符 \n，制表符 \tab，退格符 \b 等。

mysql> create table lucifer (id int,name char(100));Query OK, 0 rows affected (0.03 sec)mysql> insert into lucifer values (1,'lucifer\'s dog');Query OK, 1 row affected (0.00 sec)mysql> select * from lucifer;+------+---------------+| id   | name          |+------+---------------+|    1 | lucifer's dog |+------+---------------+1 row in set (0.00 sec)mysql>

?? 注意： 在向數(shù)據(jù)庫中插入這些特殊字符時(shí)，一定要進(jìn)行轉(zhuǎn)義處理。

2、MySQL 中可以存儲(chǔ)文件嗎？

答案當(dāng)然是可以的！

MySQL 中的 BLOB 和 TEXT 字段類型可以存儲(chǔ)數(shù)據(jù)量較大的文件，可以使用這些數(shù)據(jù)類型 存儲(chǔ)圖像、聲音或者是大容量的文本內(nèi)容，例如網(wǎng)頁或者文檔。

mysql> create table view(id int unsigned NOT NULL AUTO_INCREMENT, catid int,title varchar(256),picture MEDIUMBLOB, content TEXT,PRIMARY KEY (id));Query OK, 0 rows affected (0.03 sec)mysql> show fields from view;+---------+--------------+------+-----+---------+----------------+| Field   | Type         | Null | Key | Default | Extra          |+---------+--------------+------+-----+---------+----------------+| id      | int unsigned | NO   | PRI | NULL    | auto_increment || catid   | int          | YES  |     | NULL    |                || title   | varchar(256) | YES  |     | NULL    |                || picture | mediumblob   | YES  |     | NULL    |                || content | text         | YES  |     | NULL    |                |+---------+--------------+------+-----+---------+----------------+5 rows in set (0.00 sec)mysql>

雖然使用 BLOB 或者 TEXT 可 以存儲(chǔ)大容量的數(shù)據(jù)，但是對(duì)這些字段的處理會(huì)降低數(shù)據(jù)庫的性能。

?? 注意： 如果并非必要，可以選擇只儲(chǔ)存文件的路徑。

3、MySQL 中如何執(zhí)行區(qū)分大小寫的字符串比較？

MySQL 是 不區(qū)分大小寫 的，因此字符串比較函數(shù)也不區(qū)分大小寫。

mysql> select 'TRUE' from dual where 'DOG' = 'dog';+------+| TRUE |+------+| TRUE |+------+1 row in set (0.00 sec)

如果想執(zhí)行區(qū)分大小寫的比較，可以在字符串前面添加 BINARY 關(guān)鍵字。

mysql> select 'TRUE' from dual where BINARY'DOG' = 'dog';Empty set (0.00 sec)mysql>

例如默認(rèn)情況下，’DOG'=’dog' 返回結(jié)果為 TRUE，如果使用 BINARY 關(guān)鍵字，BINARY’DOG’='dog’ 結(jié)果為 FALSE，在區(qū)分大小寫的情況下，’DOG’ 與 ’dog’ 并不相同。

4、如何從日期時(shí)間值中獲取年、月、日等部分日期或時(shí)間值？

MySQL 中，日期時(shí)間值以字符串形式存儲(chǔ)在數(shù)據(jù)表中，因此可以使用字符串函數(shù)分別截取日期時(shí)間值的不同部分。

mysql> create table lucifer(date date);Query OK, 0 rows affected (0.04 sec)mysql> show fields from lucifer;+-------+------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------+------+-----+---------+-------+| date  | date | YES  |     | NULL    |       |+-------+------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> insert into lucifer values (now());Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from lucifer;+------------+| date       |+------------+| 2021-11-25 |+------------+1 row in set (0.00 sec)

例如某個(gè)名稱為 date 的字段有值 2021-11-25，如果只需要獲得年值，可以輸入 LEFT(date, 4)，這樣就獲得了字符串左邊開始長度為 4 的子字符串，即 YEAR 部分的值；

mysql> select LEFT(date, 4) from lucifer;+---------------+| LEFT(date, 4) |+---------------+| 2021          |+---------------+1 row in set (0.00 sec)

如果要獲取月份值，可以輸入 MID(date,6,2)，字符串第 6 個(gè)字符開始，長度為 2 的子字符串正好為 date 中的月份值。同理，讀者可以根據(jù)其他日期和時(shí)間的位置，計(jì)算并獲取相應(yīng)的值。

mysql> select MID(date,6,2) from lucifer;+---------------+| MID(date,6,2) |+---------------+| 11            |+---------------+1 row in set (0.00 sec)

5、如何改變默認(rèn)的字符集？

CONVERT() 函數(shù)改變指定字符串的默認(rèn)字符集！

MySQL 的安裝和配置過程中，其中的一個(gè)步驟是可以選擇 MySQL 的默認(rèn)字符集。但是，如果只改變字符集，沒有必要把配置過程重新執(zhí)行一遍，在這里，一個(gè)簡單的方式是 修改配置文件。

讀者可以在修改字符集時(shí)使用 SHOW VARIABLES LIKE 'character_set_%'; 或者 status 命令查看當(dāng)前字符集，以進(jìn)行對(duì)比。

mysql> SHOW VARIABLES LIKE 'character_set_%';+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | latin1                     || character_set_connection | latin1                     || character_set_database   | utf8mb3                    || character_set_filesystem | binary                     || character_set_results    | latin1                     || character_set_server     | utf8mb4                    || character_set_system     | utf8mb3                    || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)mysql> status--------------mysql  Ver 8.0.26-0ubuntu0.21.04.3 for Linux on aarch64 ((Ubuntu))Connection id:          10Current database:Current user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         8.0.26-0ubuntu0.21.04.3 (Ubuntu)Protocol version:       10Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sockBinary data as:         Hexadecimal
Uptime:                 36 min 55 sec

Threads: 2  Questions: 325  Slow queries: 0  Opens: 181  Flush tables: 3  Open tables: 69  Queries per second avg: 0.146--------------mysql>

MySQL 配置文件名稱為 my.cnf，該文件在 MySQL 的安裝目錄下面。修改配置文件中的 default-character-set 和 character-set-server 參數(shù)值，將其改為想要的字符集名稱，如 gbk、gb2312、latinl 等，修改完之后重新啟動(dòng) MySQL 服務(wù)，即可生效。

## 找到 my.cnf 位置root@modb:~# find /etc -iname my.cnf -print/etc/alternatives/my.cnf
/etc/mysql/my.cnf## 修改字符集在[client ]下面加入
default-character-set=utf8
在[ mysqld ] 下面加character_set_server=utf8## 重啟 mysql 生效service mysql restart

此時(shí)，登錄 MySQL 后使用 SHOW VARIABLES LIKE 'character_set_%'; 或者 status 命令查看修改結(jié)果！

6、DISTINCT 可以應(yīng)用于所有的列嗎？

查詢結(jié)果中，如果需要對(duì)列進(jìn)行降序排序，可以使用 DESC，這個(gè)關(guān)鍵字只能對(duì)其前面的列 進(jìn)行降序排列。

mysql> select * from lucifer;+------+----------+| id   | name     |+------+----------+|    1 | lucifer  ||    2 | lucifer1 ||    3 | lucifer2 |+------+----------+3 rows in set (0.00 sec)mysql> select * from lucifer order by id desc;+------+----------+| id   | name     |+------+----------+|    3 | lucifer2 ||    2 | lucifer1 ||    1 | lucifer  |+------+----------+3 rows in set (0.00 sec)

例如，要對(duì)多列都進(jìn)行降序排序，必須要在每一列的列名后面加 DESC 關(guān)鍵字。

mysql> select * from lucifer order by id desc,name desc;+------+----------+| id   | name     |+------+----------+|    3 | lucifer2 ||    2 | lucifer1 ||    1 | lucifer  |+------+----------+3 rows in set (0.00 sec)

而 DISTINCT 不同，DISTINCT 不能部分使用。換句話說，DISTINCT 關(guān)鍵字應(yīng)用于所有列而不僅是它后面的第一個(gè)指定列。

例如，查詢 2 個(gè)字段 sex，age，如果不同記錄的這 2 個(gè)字段的組合值都不同，則所有記錄都會(huì)被查詢出來。

mysql> select * from lucifer;+------+-----------+--------+------+| id   | name      | sex    | age  |+------+-----------+--------+------+|    1 | xiaoli    | male   |   20 ||    1 | xiaoliu   | female |   21 ||    1 | xiaozhang | female |   21 ||    1 | xiaowu    | female |   21 |+------+-----------+--------+------+4 rows in set (0.00 sec)mysql> select distinct sex,age from lucifer;+--------+------+| sex    | age  |+--------+------+| male   |   20 || female |   21 |+--------+------+2 rows in set (0.00 sec)mysql>

7、ORDER BY 可以和 LIMIT 混合使用嗎？

在使用 ORDER BY 子句時(shí)，應(yīng)保證其位于 FROM 子句之后，如果使用 LIMIT，則必須位于 ORDER BY 之后，如果子句順序不正確，MySQL 將產(chǎn)生錯(cuò)誤消息。

? 正確用法：

mysql> select * from lucifer order by age desc limit 2,4;+------+--------+--------+------+| id   | name   | sex    | age  |+------+--------+--------+------+|    1 | xiaowu | female |   21 ||    1 | xiaoli | male   |   20 |+------+--------+--------+------+2 rows in set (0.00 sec)

? 錯(cuò)誤用法：

mysql> select * from lucifer limit 2,4 order by age desc;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by age desc' at line 1mysql>

8、什么時(shí)候使用引號(hào)？

在查詢的時(shí)候，會(huì)看到在 WHERE 子句中使用條件，有的值加上了單引號(hào)，而有的值未加。

mysql> select * from lucifer where sex = 'female';+------+-----------+--------+------+| id   | name      | sex    | age  |+------+-----------+--------+------+|    1 | xiaoliu   | female |   21 ||    1 | xiaozhang | female |   21 ||    1 | xiaowu    | female |   21 |+------+-----------+--------+------+3 rows in set (0.00 sec)mysql>

單引號(hào)用來限定字符串，如果將值與字符串類型列進(jìn)行比較，則需要限定引號(hào)；而用來與數(shù)值進(jìn)行比較則不需要用引號(hào)。

mysql> select * from lucifer where age = 20;+------+--------+------+------+| id   | name   | sex  | age  |+------+--------+------+------+|    1 | xiaoli | male |   20 |+------+--------+------+------+1 row in set (0.00 sec)mysql>

9、在 WHERE子句中 AND 和 OR 必須使用圓括號(hào)嗎？

任何時(shí)候使用具有 AND 和 OR 操作符的 WHERE 子句，都應(yīng)該使用圓括號(hào)明確操作順序。

mysql> select * from lucifer where (age = 20 or sex = 'female') and name != 'xiaowu';+------+-----------+--------+------+| id   | name      | sex    | age  |+------+-----------+--------+------+|    1 | xiaoli    | male   |   20 ||    1 | xiaoliu   | female |   21 ||    1 | xiaozhang | female |   21 |+------+-----------+--------+------+mysql> 3 rows in set (0.00 sec)

如果條件較多，即使能確定計(jì)算次序，默認(rèn)的計(jì)算次序也可能會(huì)使 SQL 語句不易理解，因此使 用括號(hào)明確操作符的次序，是一個(gè)好的習(xí)慣。

10、更新或者刪除表時(shí)必須指定 WHERE子 句嗎？

個(gè)人建議所有的 UPDATE 和 DELETE 語句全都在 WHERE 子句中指定條件。

mysql> update lucifer set age = 22 where name = 'xiaoliu';Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from lucifer where name = 'xiaoliu';+------+---------+--------+------+| id   | name    | sex    | age  |+------+---------+--------+------+|    1 | xiaoliu | female |   22 |+------+---------+--------+------+1 row in set (0.00 sec)mysql>

如果省略 WHERE 子句，則 UPDATE 或 DELETE 將被應(yīng)用到表中所有的行。

mysql> update lucifer set age = 22;Query OK, 3 rows affected (0.01 sec)Rows matched: 4  Changed: 3  Warnings: 0mysql> select * from lucifer;+------+-----------+--------+------+| id   | name      | sex    | age  |+------+-----------+--------+------+|    1 | xiaoli    | male   |   22 ||    1 | xiaoliu   | female |   22 ||    1 | xiaozhang | female |   22 ||    1 | xiaowu    | female |   22 |+------+-----------+--------+------+4 rows in set (0.00 sec)mysql>

因此，除非確實(shí)打算更新或者刪除所有記錄，否則要注意使用不帶 WHERE 子句的 UPDATE 或 DELETE 語句。

?? 注意： 建議在對(duì)表進(jìn)行更新和刪除操作之前，使用 SELECT 語句確認(rèn)需要?jiǎng)h除的記錄，以免造成無法挽回的結(jié)果。

11、索引對(duì)數(shù)據(jù)庫性能如此重要，應(yīng)該如何使用它？

索引的優(yōu)點(diǎn)：

• 通過創(chuàng)建唯一索引可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

• 可以給所有的 MySQL 列類型設(shè)置索引。

• 可以大大加快數(shù)據(jù)的查詢速度，這是使用索引最主要的原因。

• 在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面可以加速表與表之間的連接。

• 在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時(shí)也可以顯著減少查詢中分組和排序的時(shí)間

缺點(diǎn)：

• 創(chuàng)建和維護(hù)索引組要耗費(fèi)時(shí)間，并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會(huì)增加。

• 索引需要占磁盤空間，除了數(shù)據(jù)表占數(shù)據(jù)空間以外，每一個(gè)索引還要占一定的物理空間。如果有大量的索引，索引文件可能比數(shù)據(jù)文件更快達(dá)到最大文件尺寸。

• 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

使用索引時(shí)，需要綜合考慮索引的優(yōu)點(diǎn)和缺點(diǎn)。

為數(shù)據(jù)庫選擇正確的索引是一項(xiàng)復(fù)雜的任務(wù)。如果索引列較少，則需要的磁盤空間和維護(hù)開銷 都較少。如果在一個(gè)大表上創(chuàng)建了多種組合索引，索引文件也會(huì)膨脹很快。

而另一方面，索引較多 可覆蓋更多的查詢?？赡苄枰囼?yàn)若干不同的設(shè)計(jì)，才能找到最有效的索引?？梢蕴砑?、修改和刪 除索引而不影響數(shù)據(jù)庫架構(gòu)或應(yīng)用程序設(shè)計(jì)。

因此，應(yīng)嘗試多個(gè)不同的索引從而建立最優(yōu)的索引。

12、盡量使用短索引（前綴索引）

對(duì)字符串類型的字段進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長度。

例如，如果有一個(gè) CHAR(255) 的列，如果在前 10 個(gè)或 30 個(gè)字符內(nèi)，多數(shù)值是惟一的，則不需要對(duì)整個(gè)列進(jìn)行索引。

mysql> select * from lucifer;+------+-----------+--------+------+| id   | name      | sex    | age  |+------+-----------+--------+------+|    1 | xiaoli    | male   |   22 ||    1 | xiaoliu   | female |   22 ||    1 | xiaozhang | female |   22 ||    1 | xiaowu    | female |   22 |+------+-----------+--------+------+4 rows in set (0.00 sec)mysql> create index idx_lucifer_name on lucifer (name(4));Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from lucifer;+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| lucifer |          1 | idx_lucifer_name |            1 | name        | A         |           1 |        4 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+1 row in set (0.01 sec)mysql>

短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間、減少 I/O 操作。

13、MySQL 存儲(chǔ)過程和函數(shù)有什么區(qū)別？

在本質(zhì)上它們都是存儲(chǔ)程序。

函數(shù)：

• 只能通過 return 語句返回單個(gè)值或者表對(duì)象；

• 限制比較多，不能用臨時(shí)表，只能用表變量，還有一些函數(shù)都不可用等等；

• 可以嵌入在 SQL 語句中使用，可以在 SELECT 語句中作為查詢語句的一個(gè)部分調(diào)用；

存儲(chǔ)過程：

• 不允許執(zhí)行 return，但是可以通過 out 參數(shù)返回多個(gè)值；

• 限制相對(duì)就比較少；

• 一般是作為一個(gè)獨(dú)立的部分來執(zhí)行；

14、存儲(chǔ)過程中的內(nèi)容可以改變嗎？

不可以！

目前，MySQL 還不提供對(duì)已存在的存儲(chǔ)過程代碼的修改，如果必須要修改存儲(chǔ)過程，必須使用 DROP 語句刪除之后，再重新編寫代碼，或者創(chuàng)建一個(gè)新的存儲(chǔ)過程。

不得不說，這方面還是 Oracle 做的比較好。

15、存儲(chǔ)過程中可以調(diào)用其他存儲(chǔ)過程嗎？

可以！

存儲(chǔ)過程包含用戶定義的 SQL 語句集合，可以使用 CALL 語句調(diào)用存儲(chǔ)過程，當(dāng)然在存儲(chǔ)過程中也可以使用 CALL 語句調(diào)用其他存儲(chǔ)過程，但是不能使用 DROP 語句刪除其他存儲(chǔ)過程。

16、存儲(chǔ)過程的參數(shù)不要與數(shù)據(jù)表中的字段名相同。

在定義存儲(chǔ)過程參數(shù)列表時(shí)，應(yīng)注意把參數(shù)名與數(shù)據(jù)庫表中的字段名區(qū)別開來，否則將出 現(xiàn)無法預(yù)期的結(jié)果。

17、存儲(chǔ)過程的參數(shù)可以使用中文嗎？

一般情況下，可能會(huì)出現(xiàn)存儲(chǔ)過程中傳入中文參數(shù)的情況，例如某個(gè)存儲(chǔ)過程根據(jù)用戶的 名字查找該用戶的信息，傳入的參數(shù)值可能是中文。這時(shí)需要在定義存儲(chǔ)過程的時(shí)候，在后面加 上 character set gbk,不然調(diào)用存儲(chǔ)過程使用中文參數(shù)會(huì)出錯(cuò)，比如定義 userInfo 存儲(chǔ)過程，代碼 如下：

CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)

18、MySQL 中視圖和表的區(qū)別以及聯(lián)系是什么？

兩者的區(qū)別：

• 視圖是已經(jīng)編譯好的 SQL 語句，是基于 SQL 語句的結(jié)果集的可視化的表，而表不是；

• 視圖沒有實(shí)際的物理記錄，而基本表有；

• 表是內(nèi)容，視圖是窗口；

• 表占用物理空間而視圖不占用物理空間，視圖只是邏輯概念的存在，表可以及時(shí)對(duì)它進(jìn)行修改，但視圖只能用創(chuàng)建的語句來修改；

• 視圖是查看數(shù)據(jù)表的一種方法，可以查詢數(shù)據(jù)表中某些字段構(gòu)成的數(shù)據(jù)，只是一些SQL 語句的集合。從安全的角度來說，視圖可以防止用戶接觸數(shù)據(jù)表，因而用戶不知道表結(jié)構(gòu)；

• 表屬于全局模式中的表，是實(shí)表；視圖屬于局部模式的表，是虛表；

• 視圖的建立和刪除只影響視圖本身，不影響對(duì)應(yīng)的基本表；

兩者的聯(lián)系：

視圖（view）是在基本表之上建立的表，它的結(jié)構(gòu)（即所定義的列）和內(nèi)容（即所有記錄） 都來自基本表，它依據(jù)基本表存在而存在。

一個(gè)視圖可以對(duì)應(yīng)一個(gè)基本表，也可以對(duì)應(yīng)多個(gè)基本表。

視圖是基本表的抽象和在邏輯意義上建立的新關(guān)系。

19、使用觸發(fā)器時(shí)須特別注意！

在使用觸發(fā)器的時(shí)候需要注意，對(duì)于相同的表，相同的事件只能創(chuàng)建一個(gè)觸發(fā)器。

mysql> create trigger lucifer_tri before insert on lucifer for each row set NEW.id=NEW.id+1;Query OK, 0 rows affected (0.01 sec)mysql> mysql> mysql> select * from lucifer;+------+-----------+--------+------+| id   | name      | sex    | age  |+------+-----------+--------+------+|    1 | xiaoli    | male   |   22 ||    1 | xiaoliu   | female |   22 ||    1 | xiaozhang | female |   22 ||    1 | xiaowu    | female |   22 ||    1 | lucifer   | male   |   20 ||    1 | lucifer   | male   |   20 |+------+-----------+--------+------+6 rows in set (0.00 sec)mysql> insert into lucifer values(1,'lucifer','male',20);Query OK, 1 row affected (0.00 sec)mysql> select * from lucifer;+------+-----------+--------+------+| id   | name      | sex    | age  |+------+-----------+--------+------+|    1 | xiaoli    | male   |   22 ||    1 | xiaoliu   | female |   22 ||    1 | xiaozhang | female |   22 ||    1 | xiaowu    | female |   22 ||    1 | lucifer   | male   |   20 ||    1 | lucifer   | male   |   20 ||    2 | lucifer   | male   |   20 |+------+-----------+--------+------+7 rows in set (0.00 sec)

比如對(duì)表 lucifer 創(chuàng)建了一個(gè) BEFORE INSERT 觸發(fā)器，那么如果對(duì)表 lucifer 再次創(chuàng)建一個(gè) BEFORE INSERT 觸發(fā)器，MySQL 將會(huì)報(bào)錯(cuò)，此時(shí)，只可以在表 lucifer 上創(chuàng)建 AFTER INSERT 或者 BEFORE UPDATE 類型的觸發(fā)器。

mysql> create trigger lucifer_tri before insert on lucifer for each row set NEW.id=NEW.id+1;ERROR 1359 (HY000): Trigger already existsmysql>

靈活的運(yùn)用觸發(fā)器將為操作省去很多麻煩。

20、及時(shí)刪除不再需要的觸發(fā)器

觸發(fā)器定義之后，每次執(zhí)行觸發(fā)事件，都會(huì)激活觸發(fā)器并執(zhí)行觸發(fā)器中的語句。

如果需求發(fā)生變化，而觸發(fā)器沒有進(jìn)行相應(yīng)的改變或者刪除，則觸發(fā)器仍然會(huì)執(zhí)行舊的語句，從而會(huì)影響新的數(shù)據(jù)的完整性。

mysql> drop trigger lucifer_tri;Query OK, 0 rows affected (0.03 sec)mysql>

因此，要將不再使用的觸發(fā)器及時(shí)刪除。

21、應(yīng)該使用哪種方法創(chuàng)建用戶？（3種方式）

創(chuàng)建用戶有 3 種方法：

• 使用 CREATE USER 語句創(chuàng)建用戶

• 在 mysql.user 表中添加用戶

• 使用 GRANT 語句創(chuàng)建用戶（僅限 MySQL 8 版本以下使用）

一般情況， 最好使用 GRANT 或者 CREATE USER 語句，而不要直接將用戶信息插入 user 表，因?yàn)?user 表中存儲(chǔ)了全局級(jí)別的權(quán)限以及其他的賬戶信息，如果意外破壞了 user 表中的記錄，則可能會(huì)對(duì) MySQL 服務(wù)器造成很大影響。

-- 使用 CREATE USER 語句創(chuàng)建用戶mysql> create user 'lucifer'@'localhost' identified by 'lucifer';Query OK, 0 rows affected (0.01 sec)mysql> -- 在 mysql.user 表中添加用戶mysql> select MD5('lucifer');+----------------------------------+| MD5('lucifer')                   |+----------------------------------+| cae33a0264ead2ddfbc3ea113da66790 |+----------------------------------+1 row in set (0.00 sec)mysql> mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, ssuex509_i09_sr, x5ubject) VALUES ('lohoscalt',uci 'lfer MD5('1',lucifer'), '', '',; '')Query OK, 1 row affected (0.01 sec)mysql> -- 使用 GRANT 語句創(chuàng)建用戶mysql> GRANT SELECT ON*.* TO 'lucifer2'@localhost IDENTIFIED BY 'lucifer';ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'lucifer'' at line 1mysql>

?? 注意： 由于測(cè)試使用的是 MySQL 8 版本，已經(jīng)不支持 GRANT 直接創(chuàng)建用戶，5.7 版本依然是支持的。

22、mysqldump 備份的文件只能在 MySQL 中使用嗎？

邏輯備份工具，適用于所有的存儲(chǔ)引擎，支持溫備、完全備份、部分備份、對(duì)于 InnoDB 存儲(chǔ)引擎支持熱備。

mysqldump 備份的文本文件實(shí)際是數(shù)據(jù)庫的一個(gè)副本，使用該文件不僅可以在 MySQL 中恢復(fù)數(shù)據(jù)庫，而且通過對(duì)該文件的簡單修改，可以使用該文件在 SQL Server 或者 Sybase 等其他數(shù)據(jù)庫中恢復(fù)數(shù)據(jù)庫。

root@modb:~# mysqldump -uroot -p hr > /root/hr.dbEnter password: 
root@modb:~# root@modb:~# ll hr.db -rw-r--r-- 1 root root 25327 Nov 26 08:52 hr.db

這在某種程度上實(shí)現(xiàn)了數(shù)據(jù)庫之間的遷移。

23、如何選擇備份工具？

根據(jù)備份的方法（是否需要數(shù)據(jù)庫離線）可以將備份分為：

• 熱備（Hot Backup）

• 冷備（Cold Backup）

• 溫備（Warm Backup）

MySQL 中進(jìn)行不同方式的備份還要考慮存儲(chǔ)引擎是否支持，如 MyISAM 不支持熱備，支持溫備和冷備。而 InnoDB 支持熱備、溫備和冷備。

一般情況下，我們需要備份的數(shù)據(jù)分為以下幾種：

• 表數(shù)據(jù)

• 二進(jìn)制日志、InnoDB 事務(wù)日志

• 代碼（存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器、事件調(diào)度器）

• 服務(wù)器配置文件

下面是幾種常用的備份工具：

• mysqldump：邏輯備份工具，適用于所有的存儲(chǔ)引擎，支持溫備、完全備份、部分備份、對(duì)于 InnoDB 存儲(chǔ)引擎支持熱備。

• cp、tar 等歸檔復(fù)制工具：物理備份工具，適用于所有的存儲(chǔ)引擎、冷備、完全備份、部分備份。

• lvm2 snapshot：借助文件系統(tǒng)管理工具進(jìn)行備份。

• mysqlhotcopy：名不副實(shí)的一個(gè)工具，僅支持 MyISAM 存儲(chǔ)引擎。

• xtrabackup：一款由 percona 提供的非常強(qiáng)大的 InnoDB/XtraDB 熱備工具，支持完全備份、增量備份。

直接復(fù)制數(shù)據(jù)文件是最為直接、快速的備份方法，但缺點(diǎn)是基本上不能實(shí)現(xiàn)增量備份。備份時(shí)必須確保沒有使用這些表。如果在復(fù)制一個(gè)表的同時(shí)服務(wù)器正在修改它，則復(fù)制無效。備份 文件時(shí)，最好關(guān)閉服務(wù)器，然后重新啟動(dòng)服務(wù)器。

24、平時(shí)應(yīng)該打開哪些日志？

日志既會(huì)影響 MySQL 的性能，又會(huì)占用大量磁盤空間。因此，如果不必要，應(yīng)盡可能少地 開啟日志。

根據(jù)不同的使用環(huán)境，可以考慮開啟不同的日志。

例如，在開發(fā)環(huán)境中優(yōu)化查詢效率低的語句，可以開啟慢查詢?nèi)罩荆?/p>

開啟慢查詢?nèi)罩荆?/strong> 可以讓MySQL記錄下查詢超過指定時(shí)間的語句，通過定位分析性能的瓶頸，才能更好的優(yōu)化數(shù)據(jù)庫系統(tǒng)的性能。

-- 檢查是否開啟慢查詢mysql> show variables like 'slow_query%';+---------------------+------------------------------+| Variable_name       | Value                        |+---------------------+------------------------------+| slow_query_log      | OFF                          || slow_query_log_file | /var/lib/mysql/modb-slow.log |+---------------------+------------------------------+2 rows in set (0.00 sec)mysql> show variables like 'long_query_time';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.01 sec)-- 開啟慢查詢?nèi)罩緈ysql> set global slow_query_log='ON'; Query OK, 0 rows affected (0.00 sec)-- 設(shè)置查詢超過10秒就記錄mysql> set global long_query_time=10;Query OK, 0 rows affected (0.00 sec)-- 再次檢查是否開啟mysql> show variables like 'slow_query%';mysql> +---------------------+------------------------------+| Variable_name       | Value                        |+---------------------+------------------------------+| slow_query_log      | ON                           || slow_query_log_file | /var/lib/mysql/modb-slow.log |+---------------------+------------------------------+2 rows in set (0.00 sec)

mysql> show variables like 'general_log%';+------------------+-------------------------+| Variable_name    | Value                   |+------------------+-------------------------+| general_log      | OFF                     || general_log_file | /var/lib/mysql/modb.log |+------------------+-------------------------+2 rows in set (0.00 sec)-- 開啟通用查詢?nèi)罩緈ysql> SET GLOBAL general_log=1; Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'general_log%';+------------------+-------------------------+| Variable_name    | Value                   |+------------------+-------------------------+| general_log      | ON                      || general_log_file | /var/lib/mysql/modb.log |+------------------+-------------------------+2 rows in set (0.00 sec)

mysql> show variables like 'log_bin%';+---------------------------------+-----------------------------+| Variable_name                   | Value                       |+---------------------------------+-----------------------------+| log_bin                         | ON                          || log_bin_basename                | /var/lib/mysql/binlog       || log_bin_index                   | /var/lib/mysql/binlog.index || log_bin_trust_function_creators | OFF                         || log_bin_use_v1_row_events       | OFF                         |+---------------------------------+-----------------------------+5 rows in set (0.00 sec)mysql>

root@modb:/var/lib/mysql# ls binlog*binlog.000001  binlog.000002  binlog.index
root@modb:/var/lib/mysql# mysqlbinlog binlog.000001 | mysql -u root -p                                            Enter password: 
root@modb:/var/lib/mysql#

[mysqld]query_cache_size= 512M 
query_cache_type= 1query_cache_type=1