數(shù)據(jù)庫之——索引、觸發(fā)器、事務（存儲引擎）

一. 數(shù)據(jù)庫

    數(shù)據(jù)庫（DataBase）是按照數(shù)據(jù)結構來組織、存儲和管理數(shù)據(jù)的倉庫。其主要特點有如下幾個方面：

1. 實現(xiàn)數(shù)據(jù)共享

   數(shù)據(jù)共享包含所有用戶可同時存取數(shù)據(jù)庫中的數(shù)據(jù)，也包括用戶可以用各種方式通過接口使用數(shù)據(jù)庫，并提供數(shù)據(jù)共享；

2. 減少數(shù)據(jù)的冗余度

   同文件系統(tǒng)相比，由于數(shù)據(jù)庫實現(xiàn)了數(shù)據(jù)共享，從而避免了用戶各自建立應用文件。減少了大量重復數(shù)據(jù)，減少了數(shù)據(jù)冗余，維護了數(shù)據(jù)的一致性；

3. 數(shù)據(jù)的獨立性

   數(shù)據(jù)的獨立性包括邏輯獨立性（數(shù)據(jù)庫中數(shù)據(jù)庫的邏輯結構和應用程序相互獨立）和物理獨立性（數(shù)據(jù)物理結構的變化不影響數(shù)據(jù)的邏輯結構）；

4. 數(shù)據(jù)實現(xiàn)集中控制

   文件管理方式中，數(shù)據(jù)處于一種分散的狀態(tài)，不同的用戶或同一用戶在不同處理中其文件之間毫無關系。利用數(shù)據(jù)庫可對數(shù)據(jù)進行集中控制和管理，并通過數(shù)據(jù)模型表示各種數(shù)據(jù)的組織以及數(shù)據(jù)間的聯(lián)系；

5. 數(shù)據(jù)一致性和可維護性，以確保數(shù)據(jù)的安全性和可靠性

   主要包括：①安全性控制：以防止數(shù)據(jù)丟失、錯誤更新和越權使用；②完整性控制：保證數(shù)據(jù)的正確性、有效性和相容性；③并發(fā)控制：使在同一時間周期內(nèi)，允許對數(shù)據(jù)實現(xiàn)多路存取，又能防止用戶之間的不正常交互作用；

6. 故障恢復

   由數(shù)據(jù)庫管理系統(tǒng)提供一套方法，可及時發(fā)現(xiàn)故障和修復故障，從而防止數(shù)據(jù)被破壞。數(shù)據(jù)庫系統(tǒng)能盡快恢復數(shù)據(jù)庫系統(tǒng)運行時出現(xiàn)的故障，可能是物理上或是邏輯上的錯誤。比如對系統(tǒng)的誤操作造成的數(shù)據(jù)錯誤等；

二. 索引

    索引是對數(shù)據(jù)庫表中一個或多個列的值進行排序的數(shù)據(jù)結構，是用于提高在數(shù)據(jù)庫表中訪問數(shù)據(jù)的速度的數(shù)據(jù)庫對象。其實索引就相當于一本書的目錄，如果沒有索引，要想在數(shù)據(jù)庫中查找某一特定的值就需要遍歷整個數(shù)據(jù)庫表，但是有了索引之后就可以在索引當中查找，有助于更快地獲取信息；

    索引可分為聚集索引和非聚集索引；對于聚集索引：是按照數(shù)據(jù)存放的物理位置為順序的；而非聚集索引中，表數(shù)據(jù)存儲順序與索引順序無關；一張表上只能創(chuàng)建一個聚集索引，因為真實數(shù)據(jù)的物理順序只可能是一種；如果一張表沒有聚集索引，那么它被稱為“堆集”，這樣的表中的數(shù)據(jù)行沒有特定的順序，所有的新行將被添加到表的末尾位置。

    一條索引記錄中包含的基本信息有：鍵值（定義索引時指定的所有字段的值）+邏輯指針（指向數(shù)據(jù)頁或另一索引頁）；根據(jù)數(shù)據(jù)庫的功能，可以在數(shù)據(jù)庫設計器中創(chuàng)建三種索引：

• 唯一索引 

唯一索引是不允許其中任何兩行具有相同索引值的索引；

當現(xiàn)有數(shù)據(jù)中存在重復的鍵值時，大多數(shù)數(shù)據(jù)庫不允許將新創(chuàng)建的唯一索引與表一起保存。數(shù)據(jù)庫還可能防止添加將在表中創(chuàng)建重復鍵值的新數(shù)據(jù)。例如，如果在employee表中職員的姓(lname)上創(chuàng)建了唯一索引，則任何兩個員工都不能同姓；

使用語句為：

CREATE UNIQUE INDEX 索引名稱
ON 表名稱 (列名稱，如果為多個列用逗號隔開)

對于一個簡單索引的創(chuàng)建，只需將唯一索引中的UNIQUE去掉就可以了；

• 主鍵索引

數(shù)據(jù)庫表經(jīng)常有一列或多列組合，其值唯一標識表中的每一行。該列稱為表的主鍵；

在數(shù)據(jù)庫關系圖中為表定義主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。當在查詢中使用主鍵索引時，它還允許對數(shù)據(jù)的快速訪問；

• 聚集索引

在聚集索引中，表中行的物理順序與鍵值的邏輯（索引）順序相同。一個表只能包含一個聚集索引；如果某索引不是聚集索引，則表中行的物理順序與鍵值的邏輯順序不匹配。與非聚集索引相比，聚集索引通常提供更快的數(shù)據(jù)訪問速度。

    雖然說建立索引的目的是加快對表中記錄的查找或排序，但是為表設置索引要付出代價的：一是增加了數(shù)據(jù)庫的存儲空間，二是在插入和修改數(shù)據(jù)時要花費較多的時間(因為索引也要隨之變動)。數(shù)據(jù)庫索引就是為了提高表的搜索效率而對某些字段中的值建立的目錄 ；其各有優(yōu)缺點：

優(yōu)點：創(chuàng)建索引可以大大提高系統(tǒng)的性能；

1. 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性；

2. 可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因；

3. 可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義；

4. 在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間；

5. 第五，通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

缺點：增加索引也有許多不利的方面；

1. 創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加；

2. 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大；

3. 當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。

    因此，對于索引的使用和建立，應該視情況而定，比如對于那些查詢次數(shù)很少或者數(shù)據(jù)值也比較少的列就不必要建立索引，因為不僅不能提高多少查詢速度，反而會耗費一定的空間和降低系統(tǒng)的維護程度。

三. 事務

    事務(Database Transaction) ，是指作為單個邏輯工作單元執(zhí)行的一系列操作，要么完全地執(zhí)行，要么完全地不執(zhí)行。 事務處理可以確保除非事務性單元內(nèi)的所有操作都成功完成，否則不會永久更新面向數(shù)據(jù)的資源。通過將一組相關操作組合為一個要么全部成功要么全部失敗的單元，可以簡化錯誤恢復并使應用程序更加可靠。

    對于事務有如下相關屬性：

• 原子性（Atomic）（Atomicity)

事務必須是原子工作單元；對于其數(shù)據(jù)修改，要么全都執(zhí)行，要么全都不執(zhí)行。通常，與某個事務關聯(lián)的操作具有共同的目標，并且是相互依賴的；如果系統(tǒng)只執(zhí)行這些操作的一個子集，則可能會破壞事務的總體目標，原子性消除了系統(tǒng)處理操作子集的可能性；

• 一致性（Consistent）(Consistency)

事務在完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。在相關數(shù)據(jù)庫中，所有規(guī)則都必須應用于事務的修改，以保持所有數(shù)據(jù)的完整性。事務結束時，所有的內(nèi)部數(shù)據(jù)結構（如 B 樹索引或雙向鏈表）都必須是正確的；

• 隔離性（Insulation）(Isolation)

由并發(fā)事務所作的修改必須與任何其它并發(fā)事務所作的修改隔離。事務查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務修改它之前的狀態(tài)，要么是另一事務修改它之后的狀態(tài)，事務不會查看中間狀態(tài)的數(shù)據(jù)。這稱為隔離性，因為它能夠重新裝載起始數(shù)據(jù)，并且重播一系列事務，以使數(shù)據(jù)結束時的狀態(tài)與原始事務執(zhí)行的狀態(tài)相同。當事務可序列化時將獲得最高的隔離級別。在此級別上，從一組可并行執(zhí)行的事務獲得的結果與通過連續(xù)運行每個事務所獲得的結果相同。由于高度隔離會限制可并行執(zhí)行的事務數(shù)，所以一些應用程序降低隔離級別以換取更大的吞吐量；

• 持久性（Duration）(Durability）

事務完成之后，它對于系統(tǒng)的影響是永久性的。該修改即使出現(xiàn)致命的系統(tǒng)故障也將一直保持；

事務的三種模型：

1. 隱式事務是指每一條數(shù)據(jù)操作語句都自動地成為一個事務，事務的開始是隱式的，事務的結束有明確的標記；

2. 顯式事務是指有顯式的開始和結束標記的事務，每個事務都有顯式的開始和結束標記；

3. 自動事務是系統(tǒng)自動默認的，開始和結束不用標記；

使用事務的語句：

1. 開始事物：BEGIN  TRANSACTION

2. 提交事物：COMMIT  TRANSACTION

3. 回滾事務：ROLLBACK  TRANSACTION

事務的保存點：

1. SAVE  TRANSACTION  保存點名稱 ——自定義保存點的名稱和位置

2. ROLLBACK  TRANSACTION  保存點名稱 ——回滾到自定義的保存點

存儲引擎：

        MySQL中的數(shù)據(jù)用各種不同的技術存儲在文件（或者內(nèi)存）中，這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力，通過選擇不同的技術，能夠獲得額外的速度或者功能，從而改善應用的整體功能。這些不同的技術以及配套的相關功能在MySQL中被稱作存儲引擎(也稱作表類型)；

        MySQL默認配置了許多不同的存儲引擎，可以預先設置或者在MySQL服務器中啟用?？梢赃x擇適用于服務器、數(shù)據(jù)庫和表格的存儲引擎，以便在選擇如何存儲你的信息、如何檢索這些信息以及需要數(shù)據(jù)結合什么性能和功能的時候能提供最大的靈活性。

下面主要介紹幾種常用的存儲引擎：

MyISAM

特性
不支持事務：MyISAM存儲引擎不支持事務，所以對事務有要求的業(yè)務場景不能使用
表級鎖定：其鎖定機制是表級索引，這雖然可以讓鎖定的實現(xiàn)成本很小但是也同時大大降低了其并發(fā)性能
讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身并不會阻塞另外的讀
只會緩存索引：MyISAM可以通過key_buffer緩存以大大提高訪問性能減少磁盤IO，但是這個緩存區(qū)只會緩存索引，而不會緩存數(shù)據(jù)

適用場景
不需要事務支持（不支持）
并發(fā)相對較低（鎖定機制問題）
數(shù)據(jù)修改相對較少（阻塞問題）
以讀為主
數(shù)據(jù)一致性要求不是非常高

最佳實踐
盡量索引（緩存機制）
調整讀寫優(yōu)先級，根據(jù)實際需求確保重要操作更優(yōu)先
啟用延遲插入改善大批量寫入性能
盡量順序操作讓insert數(shù)據(jù)都寫入到尾部，減少阻塞
分解大的操作，降低單個操作的阻塞時間
降低并發(fā)數(shù)，某些高并發(fā)場景通過應用來進行排隊機制
對于相對靜態(tài)的數(shù)據(jù)，充分利用Query Cache可以極大的提高訪問效率
MyISAM的Count只有在全表掃描的時候特別高效，帶有其他條件的count都需要進行實際的數(shù)據(jù)訪問

InnoDB

特性
具有較好的事務支持：支持4個事務隔離級別，支持多版本讀
行級鎖定：通過索引實現(xiàn)，全表掃描仍然會是表鎖，注意間隙鎖的影響
讀寫阻塞與事務隔離級別相關
具有非常高效的緩存特性：能緩存索引，也能緩存數(shù)據(jù)
整個表和主鍵以Cluster方式存儲，組成一顆平衡樹
所有Secondary Index都會保存主鍵信息

適用場景
需要事務支持（具有較好的事務特性）
行級鎖定對高并發(fā)有很好的適應能力，但需要確保查詢是通過索引完成
數(shù)據(jù)更新較為頻繁的場景
數(shù)據(jù)一致性要求較高
硬件設備內(nèi)存較大，可以利用InnoDB較好的緩存能力來提高內(nèi)存利用率，盡可能減少磁盤 IO

最佳實踐
主鍵盡可能小，避免給Secondary index帶來過大的空間負擔
避免全表掃描，因為會使用表鎖
盡可能緩存所有的索引和數(shù)據(jù)，提高響應速度
在大批量小插入的時候，盡量自己控制事務而不要使用autocommit自動提交
合理設置innodb_flush_log_at_trx_commit參數(shù)值，不要過度追求安全性
避免主鍵更新，因為這會帶來大量的數(shù)據(jù)移動

NDBCluster

特性
分布式：分布式存儲引擎，可以由多個NDBCluster存儲引擎組成集群分別存放整體數(shù)據(jù)的一部分
支持事務：和Innodb一樣，支持事務
可與mysqld不在一臺主機：可以和mysqld分開存在于獨立的主機上，然后通過網(wǎng)絡和mysqld通信交互
內(nèi)存需求量巨大：新版本索引以及被索引的數(shù)據(jù)必須存放在內(nèi)存中，老版本所有數(shù)據(jù)和索引必須存在與內(nèi)存中

適用場景
具有非常高的并發(fā)需求
對單個請求的響應并不是非常的critical
查詢簡單，過濾條件較為固定，每次請求數(shù)據(jù)量較少，又不希望自己進行水平Sharding

最佳實踐
盡可能讓查詢簡單，避免數(shù)據(jù)的跨節(jié)點傳輸
盡可能滿足SQL節(jié)點的計算性能，大一點的集群SQL節(jié)點會明顯多余Data節(jié)點
在各節(jié)點之間盡可能使用萬兆網(wǎng)絡環(huán)境互聯(lián)，以減少數(shù)據(jù)在網(wǎng)絡層傳輸過程中的延時

注：以上三個存儲引擎是目前相對主流的存儲引擎，還有其他類似如：Memory，Merge，CSV，Archive等存儲引擎的使用場景都相對較少，這里就不一一分析了。

另外，可以使用SHOW TABLE STATUS FROMDBname來查看當前數(shù)據(jù)庫中各表的引擎；

創(chuàng)建一個新表時，可以通過在CREATE語句中ENGINE或TYPE選項來告訴MySQL要創(chuàng)建什么類型的表：

CREATE TABLE t (i INT) ENGINE = INNODB;

CREATE TABLE t (i INT) TYPE = MEMORY;
如果省略掉ENGINE或TYPE選項，默認的存儲引擎被使用。當MySQL被用MySQL配置向導安裝在Windows平臺上，InnoDB存儲引擎替代MyISAM存儲引擎作為默認。當不可用的類型被指定時，自動用InnoDB表來替代。

還可以把表從一個類型轉到另一個類型，使用ALTERTABLE語句：

ALTER TABLE t ENGINE = MYISAM;

ALTER TABLE t TYPE = BDB;

四. 觸發(fā)器

    觸發(fā)器是一種特殊類型的存儲過程，它在指定的表中的數(shù)據(jù)進行變化的時候自動生效；觸發(fā)器是一個特殊的事務單元，可以引用其他表中的列執(zhí)行特殊的業(yè)務規(guī)則或數(shù)據(jù)邏輯關系。當出現(xiàn)錯誤時，可以執(zhí)行rollback transaction操作將整個觸發(fā)器以及觸發(fā)它的T-SQL語句一并回滾（不需顯示聲明begin transaction）；喚醒調用觸發(fā)器以響應INSERT、UPDATE 或 DELETE 語句。觸發(fā)器可以查詢其它表，并可以包含復雜的Transact-SQL語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個事務對待。如果檢測到嚴重錯誤（例如，磁盤空間不足），則整個事務即自動回滾，即撤銷。

觸發(fā)器類型分為兩種：

AFTER觸發(fā)器：這種觸發(fā)器將在數(shù)據(jù)變動（insert、update、delete動作）完成以后才觸發(fā)。對變動的數(shù)         據(jù)進行檢查，如果發(fā)現(xiàn)錯誤，則拒絕或回滾變動的數(shù)據(jù)；

INSTEAD OF觸發(fā)器：這種觸發(fā)器將在數(shù)據(jù)變動以前被觸發(fā)，并取代變動數(shù)據(jù)的操作（insert、update、            delete操作），轉而去執(zhí)行觸發(fā)器定義的操作；

在建立觸發(fā)器時，還必須指定觸發(fā)操作：insert、update、delete操作，至少指定一種，也可指定多種；

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

CREATE TRIGGER trigger_name//觸發(fā)器名稱
ON { table | view }//在其上執(zhí)行的表或視圖
[ WITH ENCRYPTION ]//可防止觸發(fā)器作為SQL Server的一部分發(fā)布
{
    { 
        { FOR | AFTER | INSTEAD OF } //觸發(fā)器類別，決定是after還是instead of
        { [ INSERT ][,] [ DELETE ][,] [ UPDATE ] }//指定激發(fā)觸發(fā)器的關鍵字
            
            [ WITH APPEND ]
            [ NOT FOR REPLICATION ]//表示當復制進程更改觸發(fā)器所涉及的表時，不應執(zhí)行該觸發(fā)器
            AS//觸發(fā)器要執(zhí)行的操作
            [ { IF UPDATE ( column )//測試在指定的列上進行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。可以指定多列。
            [ { AND | OR } UPDATE ( column ) ]
            [ ...n ]
            | IF ( COLUMNS_UPDATED(){bitwise_operator//位運算符} updated_bitmask )//測試是否插入或更新了提及的列，僅用于UPDATE和INSERT觸發(fā)器中
            {comparison_operator//比較運算符}column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]//SQL Server不支持在觸發(fā)器中包含所有的create語句、DROP語句等
    }
}

《完》