數(shù)據(jù)庫(kù) --- 索引、觸發(fā)器、事務(wù)（存儲(chǔ)引擎）

一、數(shù)據(jù)庫(kù)  -----   按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)

  主要特點(diǎn)：

• • 實(shí)現(xiàn)數(shù)據(jù)共享；

  • 減少數(shù)據(jù)的冗余度；

  • 數(shù)據(jù)的獨(dú)立性；

  • 數(shù)據(jù)實(shí)現(xiàn)集中控制；

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

  • 故障恢復(fù)。

二、其他相關(guān)

1、索引：作用于表中的某列，并將其進(jìn)行排序，有助于快速地進(jìn)行查詢。

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

    索引可分為聚集索引和非聚集索引；對(duì)于 聚集索引：是按照數(shù)據(jù)存放的物理位置為順序的；而 非聚集索引中，表數(shù)據(jù)存儲(chǔ)順序與索引順序無(wú)關(guān)；一張表上只能創(chuàng)建一個(gè)聚集索引，因?yàn)檎鎸?shí)數(shù)據(jù)的物理順序只可能是一種；如果一張表沒(méi)有聚集索引，那么它被稱為“堆集”，這樣的表中的數(shù)據(jù)行沒(méi)有特定的順序，所有的新行將被添加到表的末尾位置。

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

• （1）唯一索引 ：不允許其中任何兩行具有相同索引值的索引；

        當(dāng)現(xiàn)有數(shù)據(jù)中存在重復(fù)的鍵值時(shí)，大多數(shù)數(shù)據(jù)庫(kù)不允許將新創(chuàng)建的唯一索引與表一起保存。數(shù)據(jù)庫(kù)還可能防止添加將在表中創(chuàng)建重復(fù)鍵值的新數(shù)據(jù)。

使用語(yǔ)句為：

CREATE UNIQUE INDEX 索引名稱
ON 表名稱 (列名稱，如果為多個(gè)列用逗號(hào)隔開(kāi))

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

• （2）主鍵索引

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

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

• （3）聚集索引

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

    雖然說(shuō)建立索引的目的是加快對(duì)表中記錄的查找或排序，但是為表設(shè)置索引要付出代價(jià)的：一是增加了數(shù)據(jù)庫(kù)的存儲(chǔ)空間，二是在插入和修改數(shù)據(jù)時(shí)要花費(fèi)較多的時(shí)間(因?yàn)樗饕惨S之變動(dòng))。

       數(shù)據(jù)庫(kù)索引就是為了提高表的搜索效率而對(duì)某些字段中的值建立的目錄 ；其各有優(yōu)缺點(diǎn)：

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

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

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

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

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

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

    2）缺點(diǎn)：

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

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

• 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

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

    語(yǔ)法格式如下：

2、觸發(fā)器：是數(shù)據(jù)庫(kù)在進(jìn)行某種操作之前或之后進(jìn)行的操作。

（1）觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程，它在指定的表中的數(shù)據(jù)進(jìn)行變化的時(shí)候自動(dòng)生效；觸發(fā)器是一個(gè)特殊的事務(wù)單元，可以引用其他表中的列執(zhí)行特殊的業(yè)務(wù)規(guī)則或數(shù)據(jù)邏輯關(guān)系。

        當(dāng)出現(xiàn)錯(cuò)誤時(shí)，可以執(zhí)行rollback transaction操作將整個(gè)觸發(fā)器以及觸發(fā)它的T-SQL語(yǔ)句一并回滾（不需顯示聲明begin transaction）；喚醒調(diào)用觸發(fā)器以響應(yīng)INSERT、UPDATE 或 DELETE 語(yǔ)句。觸發(fā)器可以查詢其它表，并可以包含復(fù)雜的Transact-SQL語(yǔ)句。將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到嚴(yán)重錯(cuò)誤（例如，磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾，即撤銷。

（2）觸發(fā)器類型【兩種】：

       AFTER觸發(fā)器：  這種觸發(fā)器將在數(shù)據(jù)變動(dòng)（insert、update、delete動(dòng)作）完成以后才觸發(fā)。

對(duì)變動(dòng)的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯(cuò)誤，則拒絕或回滾變動(dòng)的數(shù)據(jù)；

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

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

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

3、事務(wù)：是一個(gè)或一組邏輯單元，由多個(gè)SQL語(yǔ)句組成，可以對(duì)數(shù)據(jù)庫(kù)上的對(duì)象進(jìn)行操作。

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

    （2）相關(guān)屬性：

       ①原子性(Atomicity)：事務(wù)中的所有元素作為一個(gè)整體提交或回滾，事務(wù)的個(gè)元素是不可分的，事務(wù)是一個(gè)完整操作。
         ②一致性(Consistemcy)：事物完成時(shí)，數(shù)據(jù)必須是一致的，也就是說(shuō)，和事物開(kāi)始之前，數(shù)據(jù)存儲(chǔ)中的數(shù)據(jù)處于一致?tīng)顟B(tài)。保證數(shù)據(jù)的無(wú)損。
         ③隔離性(Isolation)：對(duì)數(shù)據(jù)進(jìn)行修改的多個(gè)事務(wù)是彼此隔離的。這表明事務(wù)必須是獨(dú)立的，不應(yīng)該以任何方式以來(lái)于或影響其他事務(wù)。
         ④持久性(Durability)：事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久的，該修改即使出現(xiàn)系統(tǒng)故障也將一直保留，真實(shí)的修改了數(shù)據(jù)庫(kù)。

（3）三種模型：

• 隱式事務(wù)是指每一條數(shù)據(jù)操作語(yǔ)句都自動(dòng)地成為一個(gè)事務(wù)，事務(wù)的開(kāi)始是隱式的，事務(wù)的結(jié)束有明確的標(biāo)記；

• 顯式事務(wù)是指有顯式的開(kāi)始和結(jié)束標(biāo)記的事務(wù)，每個(gè)事務(wù)都有顯式的開(kāi)始和結(jié)束標(biāo)記；

• 自動(dòng)事務(wù)是系統(tǒng)自動(dòng)默認(rèn)的，開(kāi)始和結(jié)束不用標(biāo)記；

（4）使用事務(wù)的語(yǔ)句：

• 開(kāi)始事物：BEGIN  TRANSACTION

• 提交事物：COMMIT  TRANSACTION

• 回滾事務(wù)：ROLLBACK  TRANSACTION

（5）事務(wù)的保存點(diǎn)：

1.           SAVE  TRANSACTION  保存點(diǎn)名稱 ——自定義保存點(diǎn)的名稱和位置

2.           ROLLBACK  TRANSACTION  保存點(diǎn)名稱 ——回滾到自定義的保存點(diǎn)

4、存儲(chǔ)引擎：

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

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

（2）常用的存儲(chǔ)引擎：

   I 、  MyISAM

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

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

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

  II、   InnoDB

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

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

• 最佳實(shí)踐
  主鍵盡可能小，避免給Secondary index帶來(lái)過(guò)大的空間負(fù)擔(dān)
  避免全表掃描，因?yàn)闀?huì)使用表鎖
  盡可能緩存所有的索引和數(shù)據(jù)，提高響應(yīng)速度
  在大批量小插入的時(shí)候，盡量自己控制事務(wù)而不要使用autocommit自動(dòng)提交
  合理設(shè)置innodb_flush_log_at_trx_commit參數(shù)值，不要過(guò)度追求安全性
  避免主鍵更新，因?yàn)檫@會(huì)帶來(lái)大量的數(shù)據(jù)移動(dòng)

   III、    NDBCluster

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

• 適用場(chǎng)景
  具有非常高的并發(fā)需求
  對(duì)單個(gè)請(qǐng)求的響應(yīng)并不是非常的critical
  查詢簡(jiǎn)單，過(guò)濾條件較為固定，每次請(qǐng)求數(shù)據(jù)量較少，又不希望自己進(jìn)行水平Sharding

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

    注：以上三個(gè)存儲(chǔ)引擎是目前相對(duì)主流的存儲(chǔ)引擎，還有其他類似如：Memory，Merge，CSV，Archive等存儲(chǔ)引擎的使用場(chǎng)景都相對(duì)較少。

    查看當(dāng)前數(shù)據(jù)庫(kù)中各表的引擎：

    創(chuàng)建一個(gè)新表時(shí)，可以通過(guò)在CREATE語(yǔ)句中ENGINE或TYPE選項(xiàng)來(lái)告訴MySQL要?jiǎng)?chuàng)建什么類型的表：

如果省略掉ENGINE或TYPE選項(xiàng)，默認(rèn)的存儲(chǔ)引擎被使用。當(dāng)MySQL被用MySQL配置向?qū)О惭b在Windows平臺(tái)上，InnoDB存儲(chǔ)引擎替代MyISAM存儲(chǔ)引擎作為默認(rèn)。當(dāng)不可用的類型被指定時(shí)，自動(dòng)用InnoDB表來(lái)替代。

使用ALTERTABLE語(yǔ)句，把表從一個(gè)類型轉(zhuǎn)到另一個(gè)類型：