第五章 MySQL事務,視圖,索引,備份和恢複

標籤：delete   select   條件   聲明   物理   排序   xxx   where   查詢條件   

第五章 MySQL事務,視圖,索引,備份和恢複一.事務 1.什麼是事務

事務是一種機制，一個操作序列，它包含了一組資料庫操作命令，並且把所有的命令作為一個整體一起向系統提交或撤銷操作請求。要麼都執行，要麼都不執行。

事務是作為最小的控制單元來使用的，特別使用與多使用者同時操作的資料庫系統。

 2.為什麼需要事務

事務（transaction）是指將一系列資料操作捆綁成為一個整體進行統一管理，如果某一事務執行成功，則在該事務中進行的所有資料均會提交，成為資料庫中永久的組成部分。如果事務執行遇到錯誤且必須取消或復原，則資料將全部恢複到操作前的狀態，所有資料的更改均被清除。

 3.事務的特性

事務是作為單個工作邏輯單元執行的一系列操作，一個邏輯工作單元必須要有四個屬性，即：ACID

1）  原子性（Atomicity）。事務是一個完整的操作，事務的各個元素是不可分的（原子性）。事務中的所有元素必須作為一個整體提交或復原。

2）  一致性（consistency）。當事務完成時，資料必須處於一致狀態。也就是說，在事務開始之前，資料庫中儲存的資料處於一致狀態，在進行中的事務中，資料可能處於不一致的狀態，如資料可能有部分修改。然而當事務完成時，資料必須再次回到已知的一致狀態。

3）  隔離性（lsolation）。對資料庫修改的所有並發事務是彼此隔離的，當一個事務正在執行修改時，另一個事務不能同時進行修改，必須等到第一個事務完畢後才能操作，因此表明事務必須時獨立的。

4） 持久性（durability）。事務的持久性是指不管系統是否發生了故障，交易處理的結果都是永久的。

 4.如何執行事務

        MySQL提供了多種儲存引擎支援事務，支援事務的儲存引擎有InnoDB和BDB。InnoDB儲存引擎事務主要時通過UNDO日誌和REDO日誌，MyISAM儲存引擎不支援事務。

         任何一種資料庫，都有各種各樣的日誌，用來記錄資料庫的運行情況，日常操作，錯誤資訊等。

A） UNDO日誌：複製事務執行前的資料，用於事務在發生異常時復原資料。

B） REDO日誌：記錄事務在執行中，每條對資料庫進行更新的操作，當事務被提交時，該內容將被重新整理到磁碟。

執行事務的文法：

（1）      開始事務：begin;或者start  transaction;

（2）      提交事務：commit;

（3）      復原（撤銷）事務：rollback;

                 執行事務的樣本：

                   /*開始事務*/向學生表插入一批學產生績

BEGIN;

INSERT INTO `result` VALUES

(100000,2,NOW(),100),

(100001,1,NOW(),90),

(100002,2,NOW(),80);

SELECT * FROM `result`;#如果用SQLyog會顯示已經在表裡面更新了資料,實際上資料庫並沒有更新資料

COMMIT;#提交

SELECT * FROM `result`;#提交過後資料庫裡面會更新資料

 

BEGIN;

INSERT INTO `result` VALUES

(100003,2,NOW(),110),

(100004,1,NOW(),900);

ROLLBACK;#復原

SELECT * FROM `result`;#執行復原後查詢資料沒有任何變化

 5.使用事務要遵守的原則

1. 事務儘可能簡短。在多使用者系統中，較大的事務會佔用大量的資源，會影響軟體的運行效能，甚至導致系統崩潰。
2. 事務中訪問的資料量盡量最少。當並發執行時，資料操作的資料量越少，事務之間對資料的爭奪就越少。
3. 查詢資料時盡量不要使用事務。查詢操作並不會更新資料庫，反而會浪費系統資源。
4. 在交易處理過程中盡量不要出現等待使用者輸入的操作。如果在事務的執行過程中，如果需要等待使用者輸入資料，那麼該事務會長事件的佔用系統資源，可能造成系統阻塞。

二.視圖 1.為什麼需要視圖

在實際工作中，不同身份的使用者所關注的資料庫資料可能不同，不同職位的人對資料庫中查詢的資料範圍許可權也不同。

資料庫管理系統提供的視圖這類資料庫物件，可以把重複使用的複雜的查詢結果儲存成視圖，也可以設定不同存取範圍的視圖，不同崗位的員工根據自己的許可權來查看相關的資料，以保證資料訪問的安全性。

 2.什麼是視圖

視圖是另一種查看資料庫中一個或多個表中的資料的方法，視圖是一種虛擬表，通常作為一個或多個表的行或列的子集建立的。

A） 視圖通常用來進行的四項操作：

1. 篩選表中的行
2. 防止未經許可的使用者訪問敏感的資料表
3. 將多個物理資料表抽象為一個邏輯資料表
4. 降低資料的複雜程度

B） 視圖對使用者的好處：

1. 結果更加容易理解。建立視圖時可以將列名改為更加有意義的列名，而不會影響物理的資料庫資料。
2. 獲得資料更加容易。可以直接通過視圖查看資料，不需要自己寫查詢語句。

C） 視圖對開發人員的好處：

1. 限制資料檢索更容易。可以有效隱藏某些敏感性資料
2. 維護應用程式更方便。調試視圖比調試查詢更容易。

 3.視圖的使用   1.使用SQL語句建立視圖

文法：CREATE  VIEW  視圖名

                   AS

         <SELECT  語句>;

在SQL語句命名規範中，視圖一般是以view_xxx或v_xxx的樣式來命名的。

   2.使用SQL語句刪除視圖

文法： DROP VIEW [IF EXISTS] 視圖名；

   3.使用SQL語句查看視圖資料

文法：SELECT 欄位1，欄位2…..FROM view_name(視圖名)；

   4.使用視圖的注意事項

1）  每個視圖中可以使用多個表。

2）  與查詢相似，一個視圖可以嵌套另一個視圖，最好不要超過三層。

3）  對視圖資料進行添加，更新和刪除操作直接參考資料表中的資料。

4）  當視圖資料來自多個表時，不允許添加和刪除資料，會影響表中資料的完整性。

   5.使用視圖特別注意事項

1.）使用視圖修改資料會有許多限制和邏輯關係，一般在實際開發中視圖僅用作查詢。

2.）查看所有視圖的文法：

         SELECT * FROM views\G;

三.索引 1.什麼是索引

索引是一種有效組合資料的方式，可以快速尋找到指定的記錄，類似與書籍中目錄。

 2.索引的作用

1）  大大提高資料庫的檢索速度。

2）  改善資料庫的效能。

 3.索引分類

索引的儲存類型可以分為B-樹索引（BTREE）和雜湊表（HASH）。InnoDB和MyISAM儲存引擎支援B-樹索引。

1）  普通索引；最基本索引類型，允許在定義索引的列中插入重複值和空值。它的唯一任務時加快對資料庫的訪問速度。因此，應該只為最常出現的查詢條件（WHERE）或排序條件（ORDER BY）中資料列建立索引。    

2）  唯一索引；唯一索引不允許兩行具有相同的索引值，允許有一個空值。

若是建立了唯一約束，則將自動建立唯一索引。儘管唯一索引有助於找到資訊，但是為了獲得最佳的效能，仍建議使用主鍵約束。

3）  主鍵索引；主鍵索引要求主鍵中的每個值都是非空的，唯一的，它允許快速存取資料。

在資料關係圖中為表定義一個主鍵將自動建立主鍵索引，主鍵索引是唯一索引的特殊類型。

4）  複合索引；將多個列合并作為索引，這種索引稱為複合索引。

需要注意的是：只有在查詢中使用了複合索引最左邊的欄位時，索引才會被使用，即第一個欄位作為首碼的集合。

5）  全文索引；全文索引的作用是支援值的尋找，允許出現空值和重複值。全文索引可以在CHAR,VARCHAR或者TEXT類型的列上建立,全文索引的效率大大高於”LIKE”關鍵字。MySQL5.5中只有MyISAM儲存引擎支援全文索引。

6）  空間索引；空間索引是對空間資料類型的列建立的索引，如GEOMETRY,POINT等，建立空間索引列必須將其聲明為NOT NULL, MySQL5.5中只有MyISAM儲存引擎支援空間索引。

 4.建立索引

文法：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX  index_name

ON  table_name (column_name[length]…………..);

其中：UNIQUE 表示唯一索引，FULLTEXT 表示全文索引， SPATIAL表示空間索引，為選擇性參數。

Index_name表示索引名；

Table_name表示建立索引的表名；

Column_name指定需要建立索引的列；

Length指定索引的長度，選擇性參數，只有字串類型才能指定索引的長度。

 5.刪除索引

文法：

DROP  INDEX  index_name  ON  table_name;

 6.查看索引

文法：

SHOW  INDEX  FROM  table_name;

 7.使用索引的原則

因為索引自身也需要維護，並佔用一定的資源，因此沒有必要為每個列都添加索引，如下條件可添加索引；

1）  頻繁搜尋的列

2）  經常用作查詢的列

3）  經常排序，分組的列

4）  經常用作串連的列（主鍵、外鍵）

請不要給下面的列建立索引：

1. 僅包含幾個不同值的列
2. 包中僅包含幾行
3. 為小型表建立索引不太使用，因為索引中搜尋資料所花的事件比表中逐行搜尋花的時間更長。

四.MySQL的備份和恢複 1.備份資料庫

文法：在DOS環境下運行

Mysqldump  –u username  –h  host  -ppassword

Dbname[tbname1,tabnem2….]>filename.sql

例：

/*匯出資料*/

mysqldump -u root -proot;

myschool student result>d:\bf\myschool.sql;   

 2.恢複資料庫

文法：mysql  –u  username  -p [dbname]<filename.sql;在DOS環境下運行

或： source filename;

例：

/*匯入資料*/

CREATE DATABASE `newMySchool`;#被匯入的資料庫必須存在

mysql -u root -proot newMySchool<d:\bf\myschool.sql;

 3.資料表匯出到文字檔

文法：SELECT  columnlist  FROM  tablename

                   [WHERE contion ]

                   INTO  OUTFILE  `filename`[OPTION];

例:

#匯出科目表的記錄並儲存為.txt檔案

SELECT * FROM `subject`

INTO OUTFILE `d:/beifen/subject.txt`;

 4.文字檔倒入到資料表

文法：LOAD  DATA  INFILE  filename  INTO  TABLE  tablename[OPTION];

例：

DELETE FROM `subject`;#刪除科目表中的資料保證表是空的

LOAD DATA INFILE `d:/beifen/subject.txt`INTO TABLE `subject`;

 

第五章 MySQL事務,視圖,索引,備份和恢複