《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，
資料庫是不斷變化的物體，一組優化良好的表一會兒后可能就面目全非了，由于表的使用和內容的更改，理想的優化和配置也會改變，
最重要的規則就是，每條規則在某些條件下都會被打破，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/539806.html

標籤：MySQL

上一篇：MySQL客戶端的進階操作

下一篇：MySQL客戶端的進階操作

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

第二十六章 管理事務處理