mysql中索引，觸發器，事務，存儲引擎的理解

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

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

   索引可分為聚集索引和非聚集索引；

聚集索引：是按照數據存放的物理位置為順序的；

非聚集索引中，表數據存儲順序與索引順序無關；一張表上只能創建一個聚集索引，因為真實數據的物理順序只可能是一種；如果一張表沒有聚集索引，那么它被稱為“堆集”，這樣的表中的數據行沒有特定的順序，所有的新行將被添加到表的末尾位置。

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

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

  當現有數據中存在重復的鍵值時，大多數數據庫不允許將新創建的唯一索引與表一起保存。數據庫還可能防止添加將在表中創建重復鍵值的新數據。

使用語句為：

CREATE UNIQUE INDEX 索引名稱

ON 表名稱 (列名稱，如果為多個列用逗號隔開)

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

（2）主鍵索引：數據庫表經常有一列或多列組合，其值唯一標識表中的每一行，該列稱為表的主鍵；

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

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

 雖然說建立索引的目的是加快對表中記錄的查找或排序，但是為表設置索引要付出代價的：一是增加了數據庫的存儲空間，二是在插入和修改數據時要花費較多的時間(因為索引也要隨之變動)。

 數據庫索引就是為了提高表的搜索效率而對某些字段中的值建立的目錄 ；其各有優缺點：

1）優點：

創建索引可以大大提高系統的性能

通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性；

可以大大加快數據的檢索速度，這也是創建索引的最主要的原因；

可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義；

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

通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

2）缺點：

創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加；

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

當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

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

    語法格式如下：

相關命令：

查看一張表上的索引  

Show  index from  表名 \G 是以列來顯示

建立索引 

Alter table 表名   add  [ index  / unique index / fulltext / primary key / ]   【索引名（可選）】  （列名）

Alter table member add index  tel (tel);    普通索引  

Alter table member add unique   (email)  唯一索引  

Alter table member add fulltext (intro)  全文索引   

主鍵  索引  

Alter table member add  primary key   （列名）  不用寫索引名 因為索引就這一個  

Alter  table member add  primary key  (id)  主鍵  

刪除索引  

Alter table  表名 drop index   索引名        最后是寫索引名

Alter table member drop index intro   刪除索引

但是怎么刪除主鍵索引呢  主鍵索引 沒有索引名 直接寫primary key  就好了 

Alter table member drop  primary key

導入導出的時候 就先刪除 表的索引 然后在集中建立索引

索引創建原則 

不要過度索引 

索引盡量散列值

在where條件最頻繁的值上加索引

全文索引

在mysql  默認設定中  對中文意義不大 

如果是大文章的話  沒有用全文索引  找一個詞 要用 like 那是一行一行的找 效率 很低很低的  

全文索引查找方式

Select * from  member  where match(列名)  against('查詢內容');

Select * from member where  match(initr) against('databases');   

一般很多詞 都是停止詞  所以索引不出來   

可以查看某個單詞的匹配度

Select match('intro') against('data')   from   member;

全文索引在默認情況下對中文的意義不大，全文索引是針對文章中每一個詞 做索引的 

2、觸發器：是數據庫在進行某種操作之前或之后進行的操作。

（1）觸發器是一種特殊類型的存儲過程，它在指定的表中的數據進行變化的時候自動生效；觸發器是一個特殊的事務單元，可以引用其他表中的列執行特殊的業務規則或數據邏輯關系。

 當出現錯誤時，可以執行rollback transaction操作將整個觸發器以及觸發它的T-SQL語句一并回滾（不需顯示聲明begin transaction）；喚醒調用觸發器以響應INSERT、UPDATE 或 DELETE 語句。觸發器可以查詢其它表，并可以包含復雜的Transact-SQL語句。將觸發器和觸發它的語句作為可在觸發器內回滾的單個事務對待。如果檢測到嚴重錯誤（例如，磁盤空間不足），則整個事務即自動回滾，即撤銷。

（2）觸發器類型【兩種】：

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

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

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

 (3) 創建觸發器：

3、事務：是一個或一組邏輯單元，由多個SQL語句組成，可以對數據庫上的對象進行操作。

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

（2）相關屬性：

①原子性(Atomicity)：事務中的所有元素作為一個整體提交或回滾，事務的個元素是不可分的，事務是一個完整操作。
②一致性(Consistemcy)：事物完成時，數據必須是一致的，也就是說，和事物開始之前，數據存儲中的數據處于一致狀態。保證數據的無損。
③隔離性(Isolation)：對數據進行修改的多個事務是彼此隔離的。這表明事務必須是獨立的，不應該以任何方式以來于或影響其他事務。
④持久性(Durability)：事務完成之后，它對于系統的影響是永久的，該修改即使出現系統故障也將一直保留，真實的修改了數據庫。

（3）三種模型：

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

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

自動事務是系統自動默認的，開始和結束不用標記；

（4）使用事務的語句：

開始事物：BEGIN  TRANSACTION

提交事物：COMMIT  TRANSACTION

回滾事務：ROLLBACK  TRANSACTION

（5）事務的保存點：

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

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

4、存儲引擎：

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

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

（2）常用的存儲引擎：

I 、  MyISAM

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

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

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

II、   InnoDB

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

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

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

III、    NDBCluster

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

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

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

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

查看當前數據庫中各表的引擎：

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

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

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