無論是運(yùn)維、開發(fā)、測(cè)試，還是架構(gòu)師，數(shù)據(jù)庫(kù)技術(shù)都是一個(gè) 必備加薪神器，那么，一直說學(xué)習(xí)數(shù)據(jù)庫(kù)、學(xué) MySQL，到底是要學(xué)習(xí)它的哪些東西呢？
所有DBA的案頭必備書籍，全面的MySQL性能優(yōu)化書籍，幫助讀者深刻理解新的MySQL8調(diào)優(yōu)方法！
培養(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ù)庫(kù) SQL 入門教程
及時(shí)學(xué)習(xí)新知識(shí)
正確、有效地利用搜索引擎，可以搜索到很多關(guān)于 MySQL 的相關(guān)知識(shí)。同時(shí)，參考別 人解決問題的思路，也可以吸取別人的經(jīng)驗(yàn)，及時(shí)獲取最新的技術(shù)資料。
多實(shí)踐操作
數(shù)據(jù)庫(kù)系統(tǒng)具有極強(qiáng)的操作性，需要多動(dòng)手上機(jī)操作。在實(shí)際操作的過程中才能發(fā)現(xiàn)問題， 并思考解決問題的方法和思路，只有這樣才能提高實(shí)戰(zhàn)的操作能力。
下面分享學(xué)習(xí) MySQL 的 28 個(gè)不得不知道的小技巧！
諸如單引號(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ù)庫(kù)中插入這些特殊字符時(shí)，一定要進(jìn)行轉(zhuǎn)義處理。
答案當(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ù)庫(kù)的性能。
?? 注意： 如果并非必要，可以選擇只儲(chǔ)存文件的路徑。
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’ 并不相同。
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)，這樣就獲得了字符串左邊開始長(zhǎng)度為 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è)字符開始，長(zhǎng)度為 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)
CONVERT() 函數(shù)改變指定字符串的默認(rèn)字符集！
MySQL 的安裝和配置過程中，其中的一個(gè)步驟是可以選擇 MySQL 的默認(rèn)字符集。但是，如果只改變字符集，沒有必要把配置過程重新執(zhí)行一遍，在這里，一個(gè)簡(jiǎn)單的方式是 修改配置文件。
讀者可以在修改字符集時(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é)果！
查詢結(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>
在使用 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>
在查詢的時(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>
任何時(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í)慣。
個(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é)果。
索引的優(yōu)點(diǎn)：
通過創(chuàng)建唯一索引可以保證數(shù)據(jù)庫(kù)表中每一行數(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ù)庫(kù)選擇正確的索引是一項(xiàng)復(fù)雜的任務(wù)。如果索引列較少，則需要的磁盤空間和維護(hù)開銷 都較少。如果在一個(gè)大表上創(chuàng)建了多種組合索引，索引文件也會(huì)膨脹很快。
而另一方面，索引較多 可覆蓋更多的查詢。可能需要試驗(yàn)若干不同的設(shè)計(jì)，才能找到最有效的索引?？梢蕴砑?、修改和刪 除索引而不影響數(shù)據(jù)庫(kù)架構(gòu)或應(yīng)用程序設(shè)計(jì)。
因此，應(yīng)嘗試多個(gè)不同的索引從而建立最優(yōu)的索引。
對(duì)字符串類型的字段進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。
例如，如果有一個(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 操作。
在本質(zhì)上它們都是存儲(chǔ)程序。
函數(shù)：
只能通過 return 語句返回單個(gè)值或者表對(duì)象；
限制比較多，不能用臨時(shí)表，只能用表變量，還有一些函數(shù)都不可用等等；
可以嵌入在 SQL 語句中使用，可以在 SELECT 語句中作為查詢語句的一個(gè)部分調(diào)用；
存儲(chǔ)過程：
不允許執(zhí)行 return，但是可以通過 out 參數(shù)返回多個(gè)值；
限制相對(duì)就比較少；
一般是作為一個(gè)獨(dú)立的部分來執(zhí)行；
不可以！
目前，MySQL 還不提供對(duì)已存在的存儲(chǔ)過程代碼的修改，如果必須要修改存儲(chǔ)過程，必須使用 DROP 語句刪除之后，再重新編寫代碼，或者創(chuàng)建一個(gè)新的存儲(chǔ)過程。
不得不說，這方面還是 Oracle 做的比較好。
可以！
存儲(chǔ)過程包含用戶定義的 SQL 語句集合，可以使用 CALL 語句調(diào)用存儲(chǔ)過程，當(dāng)然在存儲(chǔ)過程中也可以使用 CALL 語句調(diào)用其他存儲(chǔ)過程，但是不能使用 DROP 語句刪除其他存儲(chǔ)過程。
在定義存儲(chǔ)過程參數(shù)列表時(shí)，應(yīng)注意把參數(shù)名與數(shù)據(jù)庫(kù)表中的字段名區(qū)別開來，否則將出 現(xiàn)無法預(yù)期的結(jié)果。
一般情況下，可能會(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)
兩者的區(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)系。
在使用觸發(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ā)器將為操作省去很多麻煩。
觸發(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í)刪除。
創(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 版本依然是支持的。
邏輯備份工具，適用于所有的存儲(chǔ)引擎，支持溫備、完全備份、部分備份、對(duì)于 InnoDB 存儲(chǔ)引擎支持熱備。
mysqldump 備份的文本文件實(shí)際是數(shù)據(jù)庫(kù)的一個(gè)副本，使用該文件不僅可以在 MySQL 中恢復(fù)數(shù)據(jù)庫(kù)，而且通過對(duì)該文件的簡(jiǎn)單修改，可以使用該文件在 SQL Server 或者 Sybase 等其他數(shù)據(jù)庫(kù)中恢復(fù)數(shù)據(jù)庫(kù)。
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ù)庫(kù)之間的遷移。
根據(jù)備份的方法（是否需要數(shù)據(jù)庫(kù)離線）可以將備份分為：
熱備（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ù)器。
日志既會(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ù)庫(kù)系統(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)
如果需要記錄用戶的所有查詢操作，可以開啟通用查詢?nèi)罩荆?/p>
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)
如果需要記錄數(shù)據(jù)的變更，可以開啟二進(jìn)制日志；錯(cuò)誤日志是默認(rèn)開啟的。
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>
二進(jìn)制日志主要用來記錄數(shù)據(jù)變更。
如果需要記錄數(shù)據(jù)庫(kù)的變化，可以開啟二進(jìn)制日志。 基于二進(jìn)制日志的特性，不僅可以用來進(jìn)行數(shù)據(jù)恢復(fù)，還可用于數(shù)據(jù)復(fù)制。
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#
在數(shù)據(jù)庫(kù)定期備份的 情況下，如果出現(xiàn)數(shù)據(jù)丟失，可以先用備份恢復(fù)大部分?jǐn)?shù)據(jù)，然后使用二進(jìn)制日志恢復(fù)最近備份后變更的數(shù)據(jù)。在雙機(jī)熱備情況下，可以使用 MySQL 的二進(jìn)制日志記錄數(shù)據(jù)的變更，然后將變更部分復(fù)制到備份服務(wù)器上。
慢查詢?nèi)罩局饕脕碛涗洸樵儠r(shí)間較長(zhǎng)的日志。
在開發(fā)環(huán)境下，可以開啟慢查詢?nèi)罩緛碛涗洸樵儠r(shí)間較長(zhǎng)的查詢語句，然后對(duì)這些語句進(jìn)行優(yōu)化。
root@modb:/var/lib/mysql# cat /var/lib/mysql/modb-slow.log/usr/sbin/mysqld, Version: 8.0.26-0ubuntu0.21.04.3 ((Ubuntu)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument root@modb:/var/lib/mysql#
通過配 long_query_time 的值，可以靈活地掌握不同程度的慢查詢語句。
合理的索引可以提高查詢的速度，但不是索引越多越好。
在執(zhí)行插入語句的時(shí)候，MySQL 要為新插入的記錄建立索引。所以過多的索引會(huì)導(dǎo)致插入操作變慢。原則上是只有查詢用的字段才建立索引。
使用索引時(shí)，需要綜合考慮索引的優(yōu)點(diǎn)和缺點(diǎn)。
查詢緩沖區(qū)可以提高查詢的速度，但是這種方式只適合查詢語句比較多、更新語句比較少 的情況。
默認(rèn)情況下查詢緩沖區(qū)的大小為 0，也就是不可用。可以修改 queiy_cache_size 以調(diào)整查詢緩沖區(qū)大??；修改 query_cache_type 以調(diào)整查詢緩沖區(qū)的類型。
在 my.cnf 中修改 query_cache_size 和 query_cache_type 的值如下所示：
[mysqld]query_cache_size= 512M query_cache_type= 1query_cache_type=1
表示開啟查詢緩沖區(qū)。
只有在查詢語句中包含 SQL_NO_CACHE 關(guān)鍵字時(shí)，才不會(huì)使用查詢緩沖區(qū)?？梢允褂?FLUSH QUERY CACHE 語句來刷新緩沖區(qū)，清理查詢緩沖區(qū)中的碎片。
聯(lián)系客服
