高性能 SQL 計劃 Day 5

今天繼續(xù)學習《MySQL 必知必會》，今天是23章-30章的內容。

Ch 23.使用存儲過程

所謂的存儲過程，就是在需要針對多個表進行多條SQL語句處理的復雜場合，將多條SQL語句保存成的一個集合。存儲過程相當于一個SQL的集合函數(shù)，封裝了若干條SQL語句。

1.存儲過程的優(yōu)缺點：

存儲過程主要有以下優(yōu)點：

  • 簡單

    • 通過把處理封裝在容易使用的單元中，簡化復雜的操作。
    • 由于不要求反復建立一系列處理步驟，這保證了數(shù)據的完整性。

  • 安全

    簡化對變動的管理。如果表名、列名或業(yè)務邏輯（或別的內容）有變化，只需要更改存儲過程的代碼。使用它的人員甚至不需要知道這些變化。通過存儲過程可以限制對基礎數(shù)據的訪問，減少了數(shù)據訛傳。

  • 高性能

    • 提高性能。因為使用存儲過程比使用單獨的SQL語句要快。
    • 存在一些只能用在單個請求中的MySQL元素和特性，存儲過程可以使用它們來編寫功能更強更靈活的代碼。

存儲過程的缺點也很明顯：編寫存儲過程要比編寫基本的SQL語句復雜很多；存儲過程也往往存在一定的限制，比如只允許用戶使用存儲過程而不允許創(chuàng)建。

2.使用存儲過程

調用存儲過程

MySQL 調用執(zhí)行過程使用的是 CALL關鍵字，如：

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);                    
#CALL 存儲過程名（參數(shù)列表）;

創(chuàng)建存儲過程

以下創(chuàng)建了一個簡單的無參的存儲過程：

CREATE PROCEDURE productpricing()
BEGIN
   SELECT Avg(prod_price) AS priceaverage
   FROM products;
END;

#CREATE PROCEDURE (參數(shù)列表)
#BEGIN
#   SQL 語句
#END;

注意：如果使用的是命令行版的MySQL，存儲過程體中的SQL語句中的;會被作為分隔符使用，造成錯誤。解決措施是臨時臨時更改命令行版MySQL的語句分隔符：

#將分隔符替換為//
DELIMITER //

  CREATE PROCEDURE productpricing()
  BEGIN
     SELECT Avg(prod_price) AS priceaverage
     FROM products;
  END //

  DELIMITER ;
#將分隔符改回;

除了\符號外，其他的符號都可以作為命令行 MySQL 的分隔符。

調用上述存儲過程：

CALL productpricing();

刪除存儲過程

刪除上述存儲過程：

DROP PROCEDURE productpricing；
#存儲過程名后面不需要帶()
#僅存在時刪除：DROP PROCEDURE IF EXISTS

使用參數(shù)

使用存儲過程時，常常將結果存儲到某個變量里面，因此需要在存儲過程中使用參數(shù)傳遞變量：

CREATE PROCEDURE productpricing(
   OUT pl DECIMAL(8,2),
   OUT ph DECIMAL(8,2),
   OUT pa DECIMAL(8,2)
)
BEGIN
   SELECT Min(prod_price)
   INTO pl
   FROM products;
   SELECT Max(prod_price)
   INTO ph
   FROM products;
   SELECT Avg(prod_price)
   INTO pa
   FROM products;
END;

此存儲過程接受3個參數(shù)：pl存儲產品最低價格，ph存儲產品最高價格，pa存儲產品平均價格。每個參數(shù)必須具有指定的類型，這里使用十進制值。關鍵字OUT指出相應的參數(shù)用來從存儲過程傳出一個值（返回給調用者）。MySQL支持IN（傳遞給存儲過程）、OUT（從存儲過程傳出，如這里所用）和INOUT（對存儲過程傳入和傳出）類型的參數(shù)。存儲過程的代碼位于BEGIN和END語句內，如前所見，它們是一系列SELECT語句，用來檢索值，然后保存到相應的變量（通過指定INTO關鍵字）。

注意：使用變量時，尤其是對變量進行賦值時需要注意變量的數(shù)據類型。

調用上述存儲過程，并輸出結果：

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);
SELECT @priceaverage;

注意：所有MySQL變量都需要使用@。

以下是一個使用 IN 和 OUT 變量的存儲過程：

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   OUT ototal DECIMAL(8,2)
)
BEGIN
   SELECT Sum(item_price*quantity)
   FROM orderitems
   WHERE order_num = onumber
   INTO ototal;
END;

調用上述存儲過程并輸出結果：

CALL ordertotal(20005,@total);
SELECT @total;

建立智能存儲過程

以下是聲明了一個復雜存儲過程：

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   IN taxable BOOLEAN,
   OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN

   -- Declare variable for total
   DECLARE total DECIMAL(8,2);
   -- Declare tax percentage
   DECLARE taxrate INT DEFAULT 6;

   -- Get the order total
   SELECT Sum(item_price*quantity)
   FROM orderitems
   WHERE order_num = onumber
   INTO total;

   -- Is this taxable?
   IF taxable THEN
      -- Yes, so add taxrate to the total
      SELECT total+(total/100*taxrate) INTO total;
   END IF;

   -- And finally, save to out variable
   SELECT total INTO ototal;

END;

注意以下幾點：

  • 增加了注釋 --。在編寫復雜的存儲過程時，添加一定的注視非常重要。
  • 添加了另外一個參數(shù)taxable，它是一個布爾值，傳入變量為0或1。
  • 用DECLARE語句定義了兩個局部變量。
  • IF語句檢查taxable是否為真，如果為真，則用另一SELECT語句增加營業(yè)稅到局部變量total。最后，用另一SELECT語句將total（它增加或許不增加營業(yè)稅）保存到ototal。
  • 添加了COMMENT輸出信息，相當于日志的作用，其信息將在SHOW PROCEDURE STATUS的結果中顯示。

調用上述存儲過程，并輸出結果：

CALL ordertotal(20005, 0, @total);
SELECT @total;

檢查存儲過程

查看存儲過程的創(chuàng)建語句：

SHOW CREATE PROCEDURE ordertotal;

輸出存儲過程的詳細信息：

SHOW PROCEDURE STATUS;

以上語句會輸出全部的存儲過程的信息，可以通過以下方式進行過濾：

SHOW PROCEDURE STATUS LIKE 'ordertotal';

Ch 24.使用游標

SQL 檢索語句返回的結果稱為結果集，之前的操作無法完成這樣的操作：獲取下一行，獲取前十行等等。這就要使用游標了。游標主要用于交互式應用，其中用戶需要滾動屏幕上的數(shù)據，并對數(shù)據進行瀏覽或做出更改。不同于其他DBMS，MySQL的游標只能用于存儲過程。

1.使用游標

使用游標涉及幾個明確的步驟：

  • 在能夠使用游標前，必須聲明（定義）它。這個過程實際上沒有檢索數(shù)據，它只是定義要使用的SELECT語句。
  • 一旦聲明后，必須打開游標以供使用。這個過程用前面定義的SELECT語句把數(shù)據實際檢索出來。
  • 對于填有數(shù)據的游標，根據需要取出（檢索）各行。
  • 在結束游標使用時，必須關閉游標。

在聲明游標后，可根據需要頻繁地打開和關閉游標。在游標打開后，可根據需要頻繁地執(zhí)行取操作。

定義游標

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR
   FOR
   SELECT ordernum FROM orders;
END;

游標的命名方式為DECLARE 游標名 CURSOR，需要注意的是，存儲過程執(zhí)行完畢后游標就消失。

打開和關閉游標

#打開游標
OPEN ordernumbers;

#關閉游標
CLOSE ordernumbers;

注意：只有打開游標后才能使用它，在游標使用完畢后需要關閉它以釋放資源。即使不手動關閉游標，當存儲過程執(zhí)行完畢游標也會被 MySQL 關閉。

CREATE PROCEDURE processorders()
BEGIN
   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- Open the cursor
   OPEN ordernumbers;

   -- Close the cursor
   CLOSE ordernumbers;

END;

使用游標數(shù)據

在一個游標被打開后，可以使用FETCH語句分別訪問它的每一行。FETCH指定檢索什么數(shù)據（所需的列），檢索出來的數(shù)據存儲在什么地方。它還向前移動游標中的內部行指針，使下一條FETCH語句檢索下一行（不重復讀取同一行）。

第一個例子是利用FETCH檢索第一行

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE o INT;

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- Open the cursor
   OPEN ordernumbers;

   -- Get order number
   --將檢索出的數(shù)據存放在局部變量o中
   FETCH ordernumbers INTO o;

   -- Close the cursor
   CLOSE ordernumbers;

END;

下一個例子是循環(huán)檢索數(shù)據，從第一行到最后一行

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- Declare continue handler
   -- SQLSTATE '02000'指的是未找到的錯誤，強行類比的話像是NullPointerException
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Open the cursor
   OPEN ordernumbers;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH ordernumbers INTO o;

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE ordernumbers;

END;

注意：DECLARE語句的發(fā)布存在特定的次序。用DECLARE語句定義的局部變量必須在定義任意游標或句柄之前定義，而句柄必須在游標之后定義。不遵守此順序將產生錯誤消息。
接下來是一個復雜的例子，其中ordertotal是上一章定義的一個計算稅率的存儲過程：

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;
   DECLARE t DECIMAL(8,2);

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Create a table to store the results
   CREATE TABLE IF NOT EXISTS ordertotals
      (order_num INT, total DECIMAL(8,2));

   -- Open the cursor
   OPEN ordernumbers;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH ordernumbers INTO o;

      -- Get the total for this order
      CALL ordertotal(o, 1, t);

      -- Insert order and total into ordertotals
      INSERT INTO ordertotals(order_num, total)
      VALUES(o, t);

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE ordernumbers;

END;

在這個存儲過程中，將檢索出來的數(shù)據存儲到新表中。

Ch 25.使用觸發(fā)器

如果想使得某些語句在一些事件發(fā)生后自動執(zhí)行，如每當訂購一個產品時，都從庫存數(shù)量中減去訂購的數(shù)量；無論何時刪除一行，都在某個存檔表中保留一個副本。想要完成這樣的操作，就要使用觸發(fā)器。

觸發(fā)器是MySQL響應以下任意語句而自動執(zhí)行的一組MySQL語句：

  • DELETE；
  • INSERT；
  • UPDATE。

其他MySQL語句不支持觸發(fā)器。

1.創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器需要提供以下信息：

  • 唯一的觸發(fā)器名；
  • 觸發(fā)器關聯(lián)的表；
  • 觸發(fā)器應該響應的活動（DELETE、INSERT或UPDATE）；
  • 觸發(fā)器何時執(zhí)行（處理之前或之后）。

需要注意的是，最好是保持每個數(shù)據庫的觸發(fā)器名唯一。另外，只有表支持觸發(fā)器，視圖不支持、臨時表也不支持。

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

上述語句對 products 定義了一個觸發(fā)器，每當對該表進行插入操作后，對每個插入行顯示“Product added”消息。

MySQL 觸發(fā)器按每個表每個事件每次地定義，每個表每個事件每次只允許一個觸發(fā)器。因此，每個表最多支持6個觸發(fā)器，即INSERT、DELETE、UPDATE的執(zhí)行前和執(zhí)行后。

注意：在MySQL中，如果BEFORE觸發(fā)器失敗，則不會執(zhí)行請求的操作；如果BEFORE觸發(fā)器失敗或者語句本身失敗，也不會執(zhí)行AFTER觸發(fā)器操作。

2.刪除觸發(fā)器

DROP TRIGGER newproduct;

3.使用觸發(fā)器

INSERT觸發(fā)器

INSERT觸發(fā)器在INSERT語句執(zhí)行之前或之后執(zhí)行。需要知道以下幾點：

  • 在INSERT觸發(fā)器代碼內，可引用一個名為NEW的虛擬表，訪問被插入的行；
  • 在BEFORE INSERT觸發(fā)器中，NEW中的值也可以被更新（允許更改被插入的值）；
  • 對于AUTO_INCREMENT列，NEW在INSERT執(zhí)行之前包含0，在INSERT執(zhí)行之后包含新的自動生成值。
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

上述語句定義了一個 neworder 的觸發(fā)器，在對orders表進行插入操作后對于每個插入行顯示該新插入記錄的order_num屬性值。

#運行如下語句
INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);

#可得到如下輸出
+-----------+
| order_num |
+-----------+
|     20010 |
+-----------+

注意：BEFORE觸發(fā)器多用于數(shù)據的驗證和凈化，而AFTER觸發(fā)器多用于顯示提示信息。

DELETE觸發(fā)器

DELETE觸發(fā)器在DELETE語句執(zhí)行之前或之后執(zhí)行。需要知道以下兩點：

  • 在DELETE觸發(fā)器代碼內，你可以引用一個名為OLD的虛擬表，訪問被刪除的行；
  • OLD中的值全都是只讀的，不能更新。

下面的例子演示使用OLD將要被刪除的行保存到一個存檔表中：

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
   INSERT INTO archive_orders(order_num, order_date, cust_id)
   VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
#觸發(fā)器體可以使用BEGIN和END包裹多條SQL語句。

根據前文所提到的，如果BEFORE觸發(fā)器執(zhí)行失敗，則說明存檔失敗，因此不會執(zhí)行刪除操作。

UPDATE觸發(fā)器

UPDATE觸發(fā)器在UPDATE語句執(zhí)行之前或之后執(zhí)行。需要知道以下幾點：

  • 在UPDATE觸發(fā)器代碼中，你可以引用一個名為OLD的虛擬表訪問以前（UPDATE語句前）的值，引用一個名為NEW的虛擬表訪問新更新的值；
  • 在BEFORE UPDATE觸發(fā)器中，NEW中的值可能也被更新（允許更改將要用于UPDATE語句中的值）；
  • OLD中的值全都是只讀的，不能更新。

下面的例子保證州名縮寫總是大寫（不管UPDATE語句中給出的是大寫還是小寫）：

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

注意事項

以下是原書中所寫的觸發(fā)器注意事項：

  • 與其他DBMS相比，MySQL5中支持的觸發(fā)器相當初級。未來的MySQL版本中有一些改進和增強觸發(fā)器支持的計劃。
  • 創(chuàng)建觸發(fā)器可能需要特殊的安全訪問權限，但是，觸發(fā)器的執(zhí)行是自動的。如果INSERT、UPDATE或DELETE語句能夠執(zhí)行，則相關的觸發(fā)器也能執(zhí)行。
  • 應該用觸發(fā)器來保證數(shù)據的一致性（大小寫、格式等）。在觸發(fā)器中執(zhí)行這種類型的處理的優(yōu)點是它總是進行這種處理，而且是透明地進行，與客戶機應用無關。
  • 觸發(fā)器的一種非常有意義的使用是創(chuàng)建審計跟蹤。使用觸發(fā)器，把更改（如果需要，甚至還有之前和之后的狀態(tài)）記錄到另一個表非常容易。
  • 遺憾的是，MySQL觸發(fā)器中不支持CALL語句。這表示不能從觸發(fā)器內調用存儲過程。所需的存儲過程代碼需要復制到觸發(fā)器內。

Ch 26.管理事務處理

正如前文所講的，并非所有的數(shù)據庫引擎都支持事務處理，MySQL最常用的引擎中，InnoDB支持事務而MyISAM不支持。

事務處理（transactionprocessing）可以用來維護數(shù)據庫的完整性，它保證成批的MySQL操作要么完全執(zhí)行，要么完全不執(zhí)行，以保證數(shù)據庫不包含不完整的操作結果。如果沒有錯誤發(fā)生，整組語句提交給（寫到）數(shù)據庫表。如果發(fā)生錯誤，則進行回退（撤銷）以恢復數(shù)據庫到某個已知且安全的狀態(tài)。

首先定義幾個概念：

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

1.控制事務處理

管理事務處理的關鍵在于將SQL語句組分解為邏輯塊，并明確規(guī)定數(shù)據何時應該回退，何時不應該回退。
MySQL使用下面的語句來標識事務的開始：

START TRANSACTION

使用ROLLBACK

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

上述語句開啟事務后將ordertotals表中的記錄全部刪除，然后回滾事務，將數(shù)據庫狀態(tài)恢復至剛剛開啟事務時。

注意：只能回退INSERT、UPDATE、DELETE語句，SELECT語句回退沒有意義。不能回退CREATE和DROP，雖然可以在事務處理塊中使用這兩種語句，但是執(zhí)行回退他們不會被撤銷。

使用COMMIT

一般的MySQL語句都是直接針對數(shù)據庫表執(zhí)行和編寫的。這就是所謂的隱含提交（implicitcommit），即提交（寫或保存）操作是自動進行的。但是，在事務處理塊中，提交不會隱含地進行。為進行明確的提交，使用COMMIT語句。

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

最后的COMMIT語句僅在不出錯時寫出更改。如果第一條DELETE起作用，但第二條失敗，則DELETE不會提交（實際上，它是被自動撤銷的）。

注意：當COMMIT或ROLLBACK語句執(zhí)行后，事務會自動關閉（將來的更改會隱含提交）。

使用保留點

更復雜的事務處理可能需要部分提交或回退，為了支持回退部分事務處理，必須能在事務處理塊中合適的位置放置占位符。這樣，如果需要回退，可以回退到某個占位符。這些占位符稱為保留點。為了創(chuàng)建占位符，可如下使用SAVEPOINT語句：

SAVEPOINT delete1;

#回退到保留點
ROLLBACK TO delete1;

注意：在進行復雜的事務操作時，可以盡可能多地設置保留點，這樣可以靈活回退。保留點在事務完成后會自動釋放，但也可以使用RELEASE SAVEPOINT明確地釋放保留點。

2.更改默認的自動提交

默認的MySQL行為是自動提交所有更改。換句話說，任何時候你執(zhí)行一條MySQL語句，該語句實際上都是針對表執(zhí)行的，而且所做的更改立即生效。為指示MySQL不自動提交更改，需要使用以下語句：

SET autocommit=0;

注意：autocommit標志是針對每個連接而不是服務器的。

Ch 27.全球化和本地化

1.使用字符集和校正順序

查看MySQL所支持字符集的完整列表：

SHOW CHARACTER SET;

查看所支持的校正順序的完整列表：

SHOW COLLATION;

確定所使用的字符集和校正順序：

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

在創(chuàng)建表時指定字符集和校正順序：

CREATE TABLE mytable
(
   columnn1   INT,
   columnn2   VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

一般，MySQL如下確定使用什么樣的字符集和校對:

  • 如果指定CHARACTER SET和COLLATE兩者，則使用這些值。
  • 如果只指定CHARACTER SET，則使用此字符集及其默認的校對（如SHOW CHARACTER SET的結果中所示）。
  • 如果既不指定CHARACTER SET，也不指定COLLATE，則使用數(shù)據庫默認。

MySQL還允許對每個列設置字符集和校正順序：

CREATE TABLE mytable
(
   columnn1   INT,
   columnn2   VARCHAR(10),
   column3    VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

校對在對用ORDER BY子句檢索出來的數(shù)據排序時起重要的作用。如果你需要用與創(chuàng)建表時不同的校對順序排序特定的SELECT語句，可以在SELECT語句自身中進行：

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;

Ch 28.安全管理

1.訪問控制

MySQL服務器的安全基礎是：用戶應該對他們需要的數(shù)據具有適當?shù)脑L問權，既不能多也不能少。

MySQL默認數(shù)據庫的用戶root擁有對數(shù)據庫的絕對管理權限，應該嚴肅對待root登錄的使用。僅在絕對需要時使用它（或許在你不能登錄其他管理賬號時使用）。不應該在日常的MySQL操作中使用root。

2.管理用戶

MySQL用戶賬號和信息存儲在名為mysql的MySQL數(shù)據庫中。可以在其中的user表中查詢到所有的用戶：

USE mysql;
SELECT user FROM user;

創(chuàng)建用戶

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

重命名用戶

RENAME USER ben TO bforta;

刪除用戶

DROP USER bforta;

設置訪問權限

新創(chuàng)建的用戶沒有任何權限，只能登錄服務器，不能操作任何表和數(shù)據庫。

查看權限：

SHOW GRANTS FOR bforta;

#輸出為
+-------------------------------------------------+
| Grants for bforta@%                             |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'              |
+-------------------------------------------------+

ON*.*表示在任意數(shù)據庫和任意表上對任何東西沒有權限。

另外，“MySQL的權限用用戶名和主機名結合定義，即user@host。如果不指定主機名，則使用默認的主機名%（授予用戶訪問權限而不管主機名）。

可以使用 GRANT 語句授予用戶權限，

  • 要授予的權限；
  • 被授予訪問權限的數(shù)據庫或表；
  • 用戶名。

以下例子給出GRANT的用法：

GRANT SELECT ON crashcourse.* TO beforta;

這條語句授予beforta用戶對crashcourse數(shù)據庫中所有的表上使用SELECT語句的權限。

可以使用REVOKE語句來撤銷GRANT授予的權限：

REVOKE SELECT ON crashcourse.* FROM beforta

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

  • 整個服務器，使用GRANT ALL和REVOKE ALL；
  • 整個數(shù)據庫，使用ON database.*；
  • 特定的表，使用ON database.table；
  • 特定的列；
  • 特定的存儲過程。

以下是具體權限內容：

WX20200620-145326@2x.png

可通過列出各權限并用逗號分隔，將多條GRANT語句串在一起，如下所示：

GRANT SELECT, INSERT ON crashcourse.* TO beforta;

更改口令

可以使用下列語句更改口令：

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

新口令必須傳遞到Password()函數(shù)進行加密。

Ch 29.數(shù)據庫維護

1.備份數(shù)據

對數(shù)據庫進行備份主要有以下幾種方式：

  • 在MySQL命令行使用mysqldump將所有數(shù)據庫內容存儲到某個外部文件中；
  • 在MySQL命令行使用mysqlhotcopy從一個數(shù)據庫復制所有數(shù)據（并非所有的數(shù)據庫引擎都支持）；
  • 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE轉儲所有數(shù)據到某個外部文件。數(shù)據可以用RESTORE TABLE來復原。

注意：為了保證所有數(shù)據被寫到磁盤（包括索引數(shù)據），可能需要在進行備份前使用FLUSH TABLES語句。

2.進行數(shù)據庫維護

應該知道以下語句：

ANALYZE TABLE，用來檢查表鍵是否正確。ANALYZE TABLE返回如下所示的狀態(tài)信息：

ANALYZE TABLE orders;

CHECK TABLE用來針對許多問題對表進行檢查。CHECK TABLE支持一系列的用于MyISAM引擎表的方式。CHANGED檢查自最后一次檢查以來改動過的表。EXTENDED執(zhí)行最徹底的檢查，F(xiàn)AST只檢查未正常關閉的表，MEDIUM檢查所有被刪除的鏈接并進行鍵檢驗，QUICK只進行快速掃描。如下所示，CHECK TABLE發(fā)現(xiàn)和修復問題：

CHECK TABLE orders, orderitems;

3.診斷啟動問題

在排除系統(tǒng)啟動問題時，首先應該盡量用手動啟動服務器。MySQL服務器自身通過在命令行上執(zhí)行mysqld啟動。下面是幾個重要的mysqld命令行選項：

  • --help顯示幫助——一個選項列表；
  • --safe-mode裝載減去某些最佳配置的服務器；
  • --verbose顯示全文本消息（為獲得更詳細的幫助消息與--help聯(lián)合使用）；
  • --version顯示版本信息然后退出。

4.查看日志文件

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

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

Ch 30.改善性能

數(shù)據庫管理員把他們生命中的相當一部份時間花在了調整、試驗以改善DBMS性能之上。在診斷應用的滯緩現(xiàn)象和性能問題時，性能不良的數(shù)據庫（以及數(shù)據庫查詢）通常是最常見的禍因。

回顧之前的章節(jié)，總結了一下的幾個性能Tips：

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

以上差不多就是《MySQL 必知必會》這本書的全部內容了，這本書深入淺出對MySQL數(shù)據庫進行了一個整體的介紹。但是我們的高性能SQL的計劃才剛剛開始。

未完待續(xù)······

最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

  • 一、MySQL架構與歷史 A.并發(fā)控制 1.共享鎖（shared lock，讀鎖）：共享的，相互不阻塞的 2.排他...
    ZyBlog閱讀 20,024評論 3贊 177
  • 全書的重點在四五六章：如何建表、如何建索引、如何查詢。第一章講解了一些基本概念：鎖與事物隔離 重中之重：4.1數(shù)據...
    AbrahamW閱讀 1,086評論 0贊 0
  • 今天計劃繼續(xù)，主要內容是《MySQL必知必會》19-22章的內容。 Ch 19.插入數(shù)據 1.插入一行 使用第二種...
    劉點石閱讀 383評論 0贊 0
  • 一、分區(qū)表 分區(qū)表用于把相關數(shù)據放在一起，以分區(qū)為單位進行批量刪除，檢查/修復，備份/恢復等數(shù)據操作。當數(shù)據表非常...
    陳菲TW閱讀 209評論 0贊 1
  • 1.鎖有兩種類型：讀鎖和寫鎖。讀鎖是共享的，或者說是相互不阻塞的，多個客戶在同一時刻可以同時讀取同一資源，而互不干...
    奔跑的Robi閱讀 395評論 0贊 0

友情鏈接更多精彩內容