《MySQL必知必會》之事務、用戶許可權、資料庫維護和性能

第二十六章管理事務處理本章介紹什麼是事務處理以及如何利用COMMIT和ROLLBACK語句來管理事務處理事務處理並非所有資料庫引擎都支持事務處理常用的InnoDB支持事務處理可以用來維護資料庫的完整性，它保證成批的MySQL操作要麼完全執行，要麼完全不執行。例如給系統添加訂單的過程利用 ...

第二十六章管理事務處理

本章介紹什麼是事務處理以及如何利用COMMIT和ROLLBACK語句來管理事務處理

事務處理

並非所有資料庫引擎都支持事務處理

常用的InnoDB支持

事務處理可以用來維護資料庫的完整性，它保證成批的MySQL操作要麼完全執行，要麼完全不執行。

例如給系統添加訂單的過程利用事務如下：

檢查資料庫中是否存在相應的客戶，如果不存在，添加他/她。
提交客戶信息。
檢索客戶的ID。
添加一行到orders表。
如果在添加行到orders表時出現故障，回退。
檢索orders表中賦予的新訂單ID。
對於訂購的每項物品，添加新行到orderitems表。
如果在添加新行到orderitems時出現故障，回退所有添加的orderitems行和orders行。
提交訂單信息。

在使用事務和事務處理時，有幾個關鍵辭彙反覆出現。下麵是關於事務處理需要知道的幾個術語：

事務（transaction）指一組SQL語句；
回退（rollback）指撤銷指定SQL語句的過程；
提交（commit）指將未存儲的SQL語句結果寫入資料庫表；
保留點（savepoint）指事務處理中設置的臨時占位符（place-holder），你可以對它發佈回退（與回退整個事務處理同）。

控制事務處理

START TRANSACTION;

使用此sql語句來標識事務的開始

使用ROLLBACK

SELECT * FROM ordertitals;
START TRANSCITION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

先檢索該表，然後START TRANSCITION;開啟事務，刪除表中所有內容，然後檢索發現為空，然後ROLLBACK回滾，檢索發現不為空

註意：

你能用事務管理insert、update、delete語句，但是不能回退create和drop語句

使用COMMIT

在編寫一般的sql時都是隱含提交的，即sql的提交操作時自動完成的

而在使用事務時，不能隱含提交，必須明確的提交，使用COMMIT

START TRAMSCATION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

如果第一條成功，第二天失敗了，那麼將自動撤銷，提交失敗

使用保留點

為了支持回退部分事務處理，必須能在事務處理塊中合適的位置放置占位符。這樣，如果需要回退，可以回退到某個占位符

SAVEPOINT delete1;
ROLLBACK TO delete1;

SAVEPOINT來創建占位符

註意：

保留點將在事務處理完之後自動釋放

更改預設的提交行為

預設MySQL行為是自動提交所有更改，為指示MySQL不自動提交更改，需要使用

SET autocommit=0;

第二十八章安全管理

本章將學習mysql的訪問控制和用戶管理

訪問控制

用戶應該對他們需要的數據具有適當的訪問權，既不能多也不能少

考慮以下內容：

多數用戶只需要對錶進行讀和寫，但少數用戶甚至需要能創建和刪除表；
某些用戶需要讀表，但可能不需要更新表；
你可能想允許用戶添加數據，但不允許他們刪除數據；
某些用戶（管理員）可能需要處理用戶賬號的許可權，但多數用戶不需要；
你可能想讓用戶通過存儲過程訪問數據，但不允許他們直接訪問數據；
你可能想根據用戶登錄的地點限制對某些功能的訪問。

不過在現實世界的日常工作中，決不能使用root。應該創建一系列的賬號，有的用於管理，有的供用戶使用，有的供開發人員使用，等等。

管理用戶

MySQL用戶賬號和信息存儲在名為mysql的MySQL資料庫中。一般不需要直接訪問mysql資料庫和表，當想要獲取所有用戶賬號列表時，可以使用以下sql：

USE mysql;
SELECT user FROM user;

創建用戶賬號

CREATE USER ben INDETIFIED BY 'p@$$sw0rd';

CREATE USER創建一個新用戶賬號，IDENTIFIED BY指定散列口令

重命名用戶賬號

RENAME USER ben TO bforta;

刪除用戶賬號

DROP USER bforta;

MySQL 5y以前的版本需要先用REVOKE刪除與賬號相關的許可權，再用DROP USER刪除賬號

設置訪問許可權

新創建的用戶沒有任何許可權，只能登錄mysql

查看用戶賬號許可權

SHOW GRANTS FOR bforta
-- output
+---------------------------------------------+
| Grants for bforta@%                         |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'          |
+---------------------------------------------+

-- 輸出結果顯示用戶bforta有一個許可權USAGE ON *.*。USAGE表示根本沒有許可權，所以，此結果表示在任意資料庫和任意表上對任何東西沒有許可權。

為設置許可權，使用GRANT語句。GRANT要求你至少給出以下信息：

要授予的許可權；
被授予訪問許可權的資料庫或表；
用戶名

GRANT SELECT ON crashcourse.* TO beforta;

-- 此GRANT允許用戶在crashcourse.*（crashcourse資料庫的所有表）上使用SELECT。通過只授予SELECT訪問許可權，用戶bforta對crashcourse資料庫中的所有數據具有隻讀訪問許可權。

SHOW GRANTS FRO bforta;
+--------------------------------------------------+
| Grants for bforta@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'               |
| GRANT SELECT ON 'charshcourse'.* TO 'bforta'@'%' |
+--------------------------------------------------+

撤銷許可權

REVOKE SELECT ON crashcourse.* FROM bforta

這條REVOKE語句取消剛賦予用戶bforta的SELECT訪問許可權。被撤銷的訪問許可權必須存在，否則會出錯

GRANT和REVOKE可在幾個層次上控制訪問許可權：

整個伺服器，使用GRANT ALL和REVOKE ALL；

整個資料庫，使用ON database.*；

特定的表，使用ON database.table；

特定的列；

特定的存儲過程

當需要賦予具體許可權時查表即可

更改口令

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

設置自己的口令

SET PASSWORD = Password('n3w p@$$w0rd');

第二十九章資料庫維護

備份數據

使用命令行實用程式mysqldump轉儲所有資料庫內容到某個外部文件。在進行常規備份前這個實用程式應該正常運行，以便能正確地備份轉儲文件。
可用命令行實用程式mysqlhotcopy從一個資料庫複製所有數據（並非所有資料庫引擎都支持這個實用程式）。
可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE轉儲所有數據到某個外部文件。這兩條語句都接受將要創建的系統文件名，此系統文件必須不存在，否則會出錯。數據可以用RESTORETABLE來複原。
可以使用FLUSH TABLES刷新未寫數據

進行資料庫維護

ANALYZE TABLE檢查表鍵是否正確

ANALYZE TABLE orders;
--output
+-----------------------+-----------+-----------+-----------+
| Table                 | Op        | Msg_type  | Msg_text  |
+-----------------------+-----------+-----------+-----------+
| crashcourse.orders    | analyze   | status    | OK        |
+-----------------------+-----------+-----------+-----------+

CHECK TABLE用來針對許多問題對錶進行檢查。在MyISAM表上還對索引進行檢查。CHECK TABLE支持一系列的用於MyISAM表的方式。CHANGED檢查自最後一次檢查以來改動過的表。EXTENDED執行最徹底的檢查，FAST只檢查未正常關閉的表，MEDIUM檢查所有被刪除的鏈接併進行鍵檢驗，QUICK只進行快速掃描。如下所示，CHECK TABLE發現和修複問題：

CHECK TABLE orders, orderitems;

診斷啟動問題

-- help 顯示幫助
-- safe-mode 裝在減去某些最佳配置的伺服器
-- verbose 顯示全文本消息
-- version顯示版本信息然後推出

查看日誌文件

MySQL維護管理員依賴的一系列日誌文件。主要的日誌文件有以下幾種。

錯誤日誌。它包含啟動和關閉問題以及任意關鍵錯誤的細節。此日誌通常名為hostname.err，位於data目錄中。此日誌名可用 --log-error命令行選項更改。
查詢日誌。它記錄所有MySQL活動，在診斷問題時非常有用。此日誌文件可能會很快地變得非常大，因此不應該長期使用它。此日誌通常名為hostname.log，位於data目錄中。此名字可以用 --log命令行選項更改。
二進位日誌。它記錄更新過數據（或者可能更新過數據）的所有語句。此日誌通常名為hostname-bin，位於data目錄內。此名字可以用–log-bin命令行選項更改。註意，這個日誌文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日誌。
緩慢查詢日誌。顧名思義，此日誌記錄執行緩慢的任何查詢。這個日誌在確定資料庫何處需要優化很有用。此日誌通常名為 hostname-slow.log ，位於 data 目錄中。此名字可以用–log-slow-queries命令行選項更改。

第三十章改善性能

首先，MySQL（與所有DBMS一樣）具有特定的硬體建議。在學習和研究MySQL時，使用任何舊的電腦作為伺服器都可以。但對用於生產的伺服器來說，應該堅持遵循這些硬體建議。
一般來說，關鍵的生產DBMS應該運行在自己的專用伺服器上。
MySQL是用一系列的預設設置預先配置的，從這些設置開始通常是很好的。但過一段時間後你可能需要調整記憶體分配、緩衝區大小等。（為查看當前設置，可使用SHOW VARIABLES;和SHOW STATUS;。）
MySQL一個多用戶多線程的DBMS，換言之，它經常同時執行多個任務。如果這些任務中的某一個執行緩慢，則所有請求都會執行緩慢。如果遇到顯著的性能不良，可使用SHOW PROCESSLIST顯示所有活動進程（以及它們的線程ID和執行時間）。你還可以用KILL命令終結某個特定的進程（使用這個命令需要作為管理員登錄）。
總是有不止一種方法編寫同一條SELECT語句。應該試驗聯結、並、子查詢等，找出最佳的方法。
使用EXPLAIN語句讓MySQL解釋它將如何執行一條SELECT語句。
一般來說，存儲過程執行得比一條一條地執行其中的各條MySQL語句快。
應該總是使用正確的數據類型。
決不要檢索比需求還要多的數據。換言之，不要用SELECT *（除非你真正需要每個列）。
有的操作（包括INSERT）支持一個可選的DELAYED關鍵字，如果使用它，將把控制立即返回給調用程式，並且一旦有可能就實際執行該操作。
在導入數據時，應該關閉自動提交。你可能還想刪除索引（包括 FULLTEXT索引），然後在導入完成後再重建它們。
必須索引資料庫表以改善數據檢索的性能。確定索引什麼不是一件微不足道的任務，需要分析使用的SELECT語句以找出重覆的 WHERE和ORDER BY子句。如果一個簡單的WHERE子句返回結果所花的時間太長，則可以斷定其中使用的列（或幾個列）就是需要索引的對象。
當SELECT語句中有一系列複雜的OR條件時，使用多條SELECT語句和連接它們的UNION語句，可以極大地改進性能。
索引改善數據檢索的性能，但損害數據插入、刪除和更新的性能。如果你有一些表，它們收集數據且不經常被搜索，則在有必要之前不要索引它們。（索引可根據需要添加和刪除。）
LIKE很慢。一般來說，最好是使用FULLTEXT而不是LIKE。
資料庫是不斷變化的實體。一組優化良好的表一會兒後可能就面目全非了。由於表的使用和內容的更改，理想的優化和配置也會改變。
最重要的規則就是，每條規則在某些條件下都會被打破。