MySQL（十四）管理維護及效能最佳化

標籤：set   撤銷   ack   建立   roc   create   二進位日誌   線程id   list   

關於MySQL的學習，《MySQL必知必會》這本書呢，看完已經兩個月了，一直被工作以及生活的一些瑣事拖著，趁著今晚有空閑，就整理完了最後的幾章學習筆記，接下來的學習計劃呢？

應該是python-web開發以及工作上的關於微服務架構的一些知識點；學習是一種習慣，更是一種狀態，只有很努力，才能看起來毫不費力！

 

一、安全管理

1、存取控制

MySQL伺服器的安全基礎是：使用者對他們需要的資料有適當的訪問權，即需要給使用者所需的許可權且僅提供所需的訪問權，這就是所謂的存取控制它需要建立和系統管理使用者帳號。

注意事項：慎用root登陸的使用；僅在絕對需要時使用它，日常的MySQL操作中，應該使用我們建立的帳號來操作。

2、系統管理使用者

MySQL使用者帳號和資訊儲存在名為mysql的MySQL資料庫中，一般不需要直接存取，但有時需要直接存取（比如需要獲得所有使用者帳號列表時），為此可以使用如下命令：

USE mysql;

select user from user;

mysql資料庫有一個名為user的表，它包含所有使用者帳號；user表有個名為user的列，它儲存使用者登入名稱、

3、使用者帳號

①建立使用者帳號

create user ben IDENTIFIED BY ‘[email protected]$$wOrd‘；

create user建立一個新使用者帳號，其中IEDNTIFIED BY用來指定一個口令，它指定的口令為純文字，MySQL將在儲存到user表之前對其進行加密；為了作為散列值指定

口令，使用IEDNTIFIED BY PASSWORD。

PS:grant和insert into的區別

grant也可以用來建立使用者帳號，但一般來說使用create user是最清楚和簡單的；此外也可以通過插入行到user表來增加使用者，但為了安全起見，不建議這麼做。MySQL用來

儲存使用者帳號資訊的表極為重要，因此相對於直接處理，使用標記和函數來處理這些表更好。

②重新命名使用者帳號

rename user ben to imyalost;

③刪除使用者帳號

刪除使用者帳號（以及相關的許可權），使用drop user語句，如下：

drop user imyalost;

4、設定存取權限

建立使用者帳號後，必須分配存取權限（建立的使用者帳號沒有任何存取權限，可以登入但是無法讀寫）；為了看到賦予帳號的許可權，使用如下語句：

show grants for imyalost;

使用者定義：MySQL的許可權使用者名稱和主機名稱結合定義，如果不指定主機名稱，則使用預設主控件名%（授予使用者存取權限而不管主機名稱）。

設定許可權使用grants語句，要求如下：

①要授與權限

②被授予存取權限的資料庫或表

③使用者名稱

grant select on crashcourse.* to imyalost;

此grant允許使用者在crashcourse.*（crashcourse資料庫的所有表）上使用select，使用者imyalost對crashcourse資料庫中所有資料擁有唯讀許可權。

每個grant添加（更新）使用者的一個許可權，MySQL讀取所有授權，並根據它們確定許可權。

grant的反操作為revoke，用它撤銷特定的許可權，例如：

revoke select on crashcourse.* from imyalost;

解析：這條revoke語句取消上面的賦予使用者imyalost的select許可權；被撤銷的許可權必須存在，否則會出錯。

PS：grant和revoke可在幾個層次上控制存取權限：

①整個伺服器，使用grant all和revoke all;

②整個資料庫，使用on database.*;

③特定的表，使用on database.table;

④特定的列;

⑤特定的預存程序;

PS：具體的可授予或撤銷的MySQL許可權，請自行搜尋；

    在使用grant和revoke時，使用者帳號必須存在，但對所涉及的對象沒有這個要求，這樣的好處是：允許管理員資料庫和表之前設計和實現安全措施；缺點在於，

    當某個資料庫或表被刪除時，相關存取權限仍然存在，而且如果將來重新建立資料庫或表時，這些許可權仍然起作用。

簡化多次授權：可以通過列出各許可權並用逗號分隔，將多餘的grant語句串聯，如下所示：

grant select,insert on crashcourse.* to imyalost;

5、更改口令

更改使用者口令，可使用set password語句，新口令必須如下加密：

set password for imyalost = password(‘n3w [email protected]$$w0rd‘);

解析：set password更新使用者口令，新口令必須傳到password()函數進行加密。

此外，set password還可以設定自己的口令，比如：

set password = password(‘n3w [email protected]$$w0rd‘);

PS:不指定使用者名稱時，set password更新當前登入使用者的口令。

 

二、資料庫維護

1、備份資料

MySQL資料也需要經常備份，由於MySQL資料庫基於磁碟檔案，普通的備份系統就能備份MySQL資料，但由於這些檔案總是處於開啟和使用狀態，普通的檔案副本備份不一定有效。

下面列出一些可行的解決方案：

①使用命令列公用程式mysqldump轉儲所有資料庫內容到某個外部檔案；在進行常規備份前該程式應該正常運行，以便能正確備份轉儲檔案；

②使用命令列公用程式mysqlhotcopy從一個資料庫複寫所有資料（並非所有資料庫引擎都支援該程式）；

③使用MySQL的backup table或select into outfile轉儲所有資料到某個外部檔案；這兩條語句都接受將要建立的系統檔案名稱，此系統檔案必須不存在，否則會報錯。

  資料可用restoretable來複原。

PS：為保證所有資料被寫入磁碟（包括索引資料），需要在備份前使用flush tables語句，重新整理資料。

2、進行資料庫維護

MySQL提供了一系列的語句，可用來保證資料庫正確和正常運行，如下：

①analyze table orders;

此語句用來檢查表鍵是否正確。

②check table orders,orderitems;

check table用來針對許多問題進行檢查，在MyISAM表上還對索引進行檢查。check table支援一系列的用語MyISAM表的方式，如下：

changed檢查自最後一次檢查以來改動過的表；

extended執行最徹底的檢查；fast只檢查未正常關閉的表；

medium檢查所有被刪除的連結並進行鍵檢驗；

quick只進行快速掃描。

③optimize table orders;

當從一個表刪除大量資料，使用此語句來收回所用的空間，從而最佳化表的效能。

3、診斷啟動問題

伺服器啟動問題通常在對MySQL配置或伺服器本身變更時出現，一般在排除系統啟動問題時，首先應盡量手動啟動伺服器；MySQL常用的mysqlid命令列如下：

①- -help顯示協助——————一個選項列表；

②- -safe-mode裝載減去某些最佳配置的伺服器；

③- -verbose顯示全簡訊（為獲得更詳細的協助訊息與- -help聯合使用）；

④- -version顯示版本資訊然後退出。

4、查看記錄檔

MySQL管理員依賴的一系列記錄檔，主要的記錄檔如下：

①錯誤記錄檔：包含啟動和關閉問題以及任意嚴重錯誤的細節。此日誌通常名為hostname.err，位於data目錄中；此日誌名可用- -log-error命令列選項更改；

②查詢日誌：記錄所有的mysql活動，診斷問題時非常有用；由於其可能很快地變得非常大，因此不應長時間使用它；日誌名通常為hostname.log，位於data目錄中，

  此名字可用- -log命令列選項更改；

③二進位日誌：記錄更新過資料的所有語句。通常名為hostname-bin，位於data目錄內，此名字可用- -log-bin命令列選項修改；

④緩慢查詢日誌：記錄執行緩慢的任何查詢。在確定資料庫何處需要最佳化很有用，通常名為hostname-slow.log，位於data目錄中，可使用- -log-slow-querier命令列更改。

PS:使用日誌時，可使用flush logs語句來重新整理和重新開始所有記錄檔。

 

三、效能最佳化

改善資料庫效能需要知道的一些關鍵點：

1、關鍵的生產DBMS應該運行在自己專用的伺服器上；

2、MySQL使用一系列預設的設定預先配置的，但後期很可能需要調整記憶體配置、緩衝區大小等；

3、MySQL是一個多使用者多線程的DBMS，它經常執行多個任務。如果這些任務中某個執行緩慢，則所有的請求都會執行緩慢，如果遇到顯著的效能不良，可使用show processlist

   顯示所有活動進程（以及它們的線程ID和執行時間，還可以用kill命令終結某個特定進程）；

4、編寫select語句的方法很多，應多嘗試連接、並、子查詢等，找出最佳方法；

5、使用explain語句讓MySQL解釋它將如何執行一條select語句；

6、一般情況下，預存程序執行的比一條一條的執行其中的各條MySQL語句要快；

7、總是使用正確的資料類型；

8、絕不要檢索比需要的還多的資料，即：不要使用select *（除非真正需要）；

9、有的操作（包括insert）支援一個可選的delayed關鍵字，如果使用它，將把控制立即返回給調用程式，並且一旦有可能就實際執行該操作；

10、匯入資料時，應關閉自動認可；

11、必須索引資料庫表以改善資料庫效能，應該分析使用select語句以找出重複的where和order by字句（如果一個簡單的where子句返回結果花費時間過長，則可以斷定其中

   使用的列就是需要索引的對象）；

12、使用多條select語句和串連它們的union語句，可以極大的改善效能；

13、索引改善資料檢索的效能，但損害資料插入、刪除和更新的效能。如果有些表收集資料但不經常被搜尋，則在有必要之前不要索引它們（索引可根據需要添加和刪除）；

14、like很慢，一般建議使用fulltext；

15、資料庫是不斷變化的實體；

16、最重要的規則：每條規則在某些條件下都會被打破。

 

MySQL（十四）管理維護及效能最佳化