事務四大特性

原子性：不可分割的操作單元，事務中所有操作，要么全部成功；要么撤回到執(zhí)行事務之前的狀態(tài)
一致性：如果在執(zhí)行事務之前數(shù)據(jù)庫是一致的，那么在執(zhí)行事務之后數(shù)據(jù)庫也還是一致的；
隔離性：事務操作之間彼此獨立和透明互不影響。事務獨立運行。這通常使用鎖來實現(xiàn)。一個事務處理后的結果，影響了其他事務，那么其他事務會撤回。事務的100%隔離，需要犧牲速度。
持久性：事務一旦提交，其結果就是永久的。即便發(fā)生系統(tǒng)故障，也能恢復。

MySQL的事務隔離級別
未提交讀(Read Uncommitted)：允許臟讀，其他事務只要修改了數(shù)據(jù)，即使未提交，本事務也能看到修改后的數(shù)據(jù)值。也就是可能讀取到其他會話中未提交事務修改的數(shù)據(jù)
提交讀(Read Committed)：只能讀取到已經(jīng)提交的數(shù)據(jù)。Oracle等多數(shù)數(shù)據(jù)庫默認都是該級別 (不重復讀)。
可重復讀(Repeated Read)：可重復讀。無論其他事務是否修改并提交了數(shù)據(jù)，在這個事務中看到的數(shù)據(jù)值始終不受其他事務影響。
串行讀(Serializable)：完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞

MySQL數(shù)據(jù)庫(InnoDB引擎)默認使用可重復讀（ Repeatable read)

索引

數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結構，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用 B_TREE。B_TREE 索引加速了數(shù)據(jù)訪問，因為存儲引擎不會再去掃描整張表得到需要的數(shù)據(jù)；相反，它從根節(jié)點開始，根節(jié)點保存了子節(jié)點的指針，存儲引擎會根據(jù)指針快速尋找數(shù)據(jù)。

MyISAM引擎使用B+Tree作為索引結構，葉節(jié)點的data域存放的是數(shù)據(jù)記錄的地址，即：MyISAM索引文件和數(shù)據(jù)文件是分離的，MyISAM的索引文件僅僅保存數(shù)據(jù)記錄的地址。MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應數(shù)據(jù)記錄。MyISAM的索引方式也叫做“非聚集”的。

InnoDB引擎也使用B+Tree作為索引結構，但是InnoDB的數(shù)據(jù)文件本身就是索引文件，葉節(jié)點data域保存了完整的數(shù)據(jù)記錄。這個索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。這種索引叫做“聚焦索引”。InnoDB的輔助索引的data域存儲相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。聚集索引這種實現(xiàn)方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。InnoDB的索引實現(xiàn)后，不建議使用過長的字段作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。在Innodb中也不建議使用非單調(diào)的字段作為主鍵，因為InnoDB數(shù)據(jù)文件本身是一顆B+Tree，非單調(diào)的主鍵會造成在插入新記錄時數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整，十分低效，建議使用自增字段作為主鍵。

MySQL數(shù)據(jù)庫的四類索引:

　　index  ----  普通索引,數(shù)據(jù)可以重復，沒有任何限制。
　　unique   ---- 唯一索引,要求索引列的值必須唯一，但允許有空值；如果是組合索引，那么列值的組合必須唯一。

　　primary key ---- 主鍵索引,是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值，一般是在創(chuàng)建表的同時創(chuàng)建主鍵索引。

　　組合索引 ----  在多個字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段，索引才會被使用。

　　fulltext ---- 全文索引,是對于大表的文本域：char，varchar，text列才能創(chuàng)建全文索引，主要用于查找文本中的關鍵字，并不是直接與索引中的值進行比較。fulltext更像是一個搜索引擎，配合match against操作使用，而不是一般的where語句加like。

　　注:全文索引目前只有MyISAM存儲引擎支持全文索引，InnoDB引擎5.6以下版本還不支持全文索引

　　所有存儲引擎對每個表至少支持16個索引，總索引長度至少為256字節(jié)，索引有兩種存儲類型，包括B型樹索引和哈希索引。

　　索引可以提高查詢的速度，但是創(chuàng)建和維護索引需要耗費時間，同時也會影響插入的速度，如果需要插入大量的數(shù)據(jù)時，最好是先刪除索引，插入數(shù)據(jù)后再建立索引。

索引生效條件

假設index（a,b,c）

1. 最左前綴匹配：模糊查詢時，使用%匹配時：’a%‘會使用索引，’%a‘不會使用索引
2. 條件中有or，索引不會生效
3. a and c，a生效，c不生效
4. b and c，都不生效
5. a and b > 5 and c,a和b生效，c不生效。

檢測索引的效果：

show status like '%handler_read%'越大越好             

sql語句分類：
DDL：數(shù)據(jù)定義語言（create drop）
DML：數(shù)據(jù)操作語句（insert update delete）
DQL：數(shù)據(jù)查詢語句（select ）
DCL：數(shù)據(jù)控制語句，進行授權和權限回收（grant revoke）
TPL：數(shù)據(jù)事務語句（commit collback savapoint）

數(shù)據(jù)庫三范式：

• 第一范式：1NF是對屬性的原子性約束，要求字段具有原子性，不可再分解；(只要是關系型數(shù)據(jù)庫都滿足1NF)
• 第二范式：2NF是在滿足第一范式的前提下，非主鍵字段不能出現(xiàn)部分依賴主鍵；解決：消除復合主鍵就可避免出現(xiàn)部分以來，可增加單列關鍵字。
• 第三范式：3NF是在滿足第二范式的前提下，非主鍵字段不能出現(xiàn)傳遞依賴，比如某個字段a依賴于主鍵，而一些字段依賴字段a，這就是傳遞依賴。解決：將一個實體信息的數(shù)據(jù)放在一個表內(nèi)實現(xiàn)。
   

臟讀&不可重復讀&幻讀

臟讀: 是指事務T1將某一值修改，然后事務T2讀取該值，此后T1因為某種原因撤銷對該值的修改，這就導致了T2所讀取到的數(shù)據(jù)是無效的。

不可重復讀 ：是指在數(shù)據(jù)庫訪問時，一個事務范圍內(nèi)的兩次相同查詢卻返回了不同數(shù)據(jù)。在一個事務內(nèi)多次讀同一數(shù)據(jù)。在這個事務還沒有結束時，另外一個事務也訪問該同一數(shù)據(jù)。那么在第一個事務中的兩次讀數(shù)據(jù)之間，由于第二個事務的修改，第一個事務兩次讀到的的數(shù)據(jù)可能是不一樣的。這樣在一個事務內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復讀。

幻讀: 是指當事務不是獨立執(zhí)行時發(fā)生的一種現(xiàn)象，比如第一個事務對一個表中的數(shù)據(jù)進行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時，第二個事務也修改這個表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么就會發(fā)生，操作第一個事務的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣。

不可重復讀&幻讀區(qū)別:

如果使用鎖機制來實現(xiàn)這兩種隔離級別，在可重復讀中，該sql第一次讀取到數(shù)據(jù)后，就將這些數(shù)據(jù)加鎖，其它事務無法修改這些數(shù)據(jù)，就可以實現(xiàn)可重復讀了。但這種方法卻無法鎖住insert的數(shù)據(jù)，所以當事務A先前讀取了數(shù)據(jù)，或者修改了全部數(shù)據(jù)，事務B還是可以insert數(shù)據(jù)提交，這時事務A就會發(fā)現(xiàn)莫名其妙多了一條之前沒有的數(shù)據(jù)，這就是幻讀，不能通過行鎖來避免。需要Serializable隔離級別 ，讀用讀鎖，寫用寫鎖，讀鎖和寫鎖互斥，這么做可以有效的避免幻讀、不可重復讀、臟讀等問題，但會極大的降低數(shù)據(jù)庫的并發(fā)能力。
不可重復讀重點在于update和delete，而幻讀的重點在于insert。如何通過鎖機制來解決他們產(chǎn)生的問題

存儲引擎 MyISAM和InnoDB區(qū)別：

1）InnoDB支持事務，MyISAM不支持。

2）MyISAM適合查詢以及插入為主的應用，InnoDB適合頻繁修改以及涉及到安全性較高的應用。

3）InnoDB支持外鍵，MyISAM不支持。

4）從MySQL5.5.5以后，InnoDB是默認引擎。

5）MyISAM支持全文類型索引，而InnoDB不支持全文索引。

6）InnoDB中不保存表的總行數(shù)，select count(*) from table時，InnoDB需要掃描整個表計算有多少行，但MyISAM只需簡單讀出保存好的總行數(shù)即可。注：當count(*)語句包含where條件時MyISAM也需掃描整個表。

7）對于自增長的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯(lián)合索引。

8）清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表。MyisAM使用delete語句刪除后并不會立刻清理磁盤空間，需要定時清理，命令：OPTIMIZE table dept;

9）InnoDB支持行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like ‘%lee%’）

10）Myisam創(chuàng)建表生成三個文件：.frm 數(shù)據(jù)表結構 、 .myd 數(shù)據(jù)文件 、 .myi 索引文件，Innodb只生成一個 .frm文件，數(shù)據(jù)存放在ibdata1.log

現(xiàn)在一般都選用InnoDB，主要是MyISAM的全表鎖，讀寫串行問題，并發(fā)效率鎖表，效率低，MyISAM對于讀寫密集型應用一般是不會去選用的。

應用場景：

MyISAM不支持事務處理等高級功能，但它提供高速存儲和檢索，以及全文搜索能力。如果應用中需要執(zhí)行大量的SELECT查詢，那么MyISAM是更好的選擇。
InnoDB用于需要事務處理的應用程序，包括ACID事務支持。如果應用中需要執(zhí)行大量的INSERT或UPDATE操作，則應該使用InnoDB，這樣可以提高多用戶并發(fā)操作的性能。

CHAR和VARCHAR的區(qū)別：

• CHAR和VARCHAR類型在存儲和檢索方面有所不同
• CHAR列長度固定為創(chuàng)建表時聲明的長度，長度值范圍是1到255
• 當CHAR值被存儲時，它們被用空格填充到特定長度，檢索CHAR值時需刪除尾隨空格。
   

Mysql中有哪幾種鎖？

• MyISAM支持表鎖，InnoDB支持表鎖和行鎖，默認為行鎖
• 表級鎖：開銷小，加鎖快，不會出現(xiàn)死鎖。鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)量最低
• 行級鎖：開銷大，加鎖慢，會出現(xiàn)死鎖。鎖力度小，發(fā)生鎖沖突的概率小，并發(fā)度最高
   

存儲過程

我們常用的操作數(shù)據(jù)庫語言SQL語句在執(zhí)行的時候需要要先編譯，然后執(zhí)行，而存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中，用戶通過指定存儲過程的名字并給定參數(shù)（如果該存儲過程帶有參數(shù)）來調(diào)用執(zhí)行它。

一個存儲過程是一個可編程的函數(shù)，它在數(shù)據(jù)庫中創(chuàng)建并保存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程序或平臺上執(zhí)行相同的函數(shù)，或者封裝特定功能時，存儲過程是非常有用的。數(shù)據(jù)庫中的存儲過程可以看做是對編程中面向?qū)ο蠓椒ǖ哪M。它允許控制數(shù)據(jù)的訪問方式。

優(yōu)點：

(1).存儲過程增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的運算。

(2).存儲過程允許標準組件是編程。存儲過程被創(chuàng)建后，可以在程序中被多次調(diào)用，而不必重新編寫該存儲過程的SQL語句。而且數(shù)據(jù)庫專業(yè)人員可以隨時對存儲過程進行修改，對應用程序源代碼毫無影響。

(3).存儲過程能實現(xiàn)較快的執(zhí)行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執(zhí)行，那么存儲過程要比批處理的執(zhí)行速度快很多。因為存儲過程是預編譯的。在首次運行一個存儲過程時查詢，優(yōu)化器對其進行分析優(yōu)化，并且給出最終被存儲在系統(tǒng)表中的執(zhí)行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優(yōu)化，速度相對要慢一些。

(4).存儲過程能過減少網(wǎng)絡流量。針對同一個數(shù)據(jù)庫對象的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織程存儲過程，那么當在客戶計算機上調(diào)用該存儲過程時，網(wǎng)絡中傳送的只是該調(diào)用語句，從而大大增加了網(wǎng)絡流量并降低了網(wǎng)絡負載。

(5).存儲過程可被作為一種安全機制來充分利用。系統(tǒng)管理員通過執(zhí)行某一存儲過程的權限進行限制，能夠?qū)崿F(xiàn)對相應的數(shù)據(jù)的訪問權限的限制，避免了非授權用戶對數(shù)據(jù)的訪問，保證了數(shù)據(jù)的安全。

delete、drop、truncate區(qū)別

truncate 和 delete只刪除數(shù)據(jù)，不刪除表結構 ,drop刪除表結構，并且釋放所占的空間。
刪除數(shù)據(jù)的速度，drop> truncate > delete
delete屬于DML語言，需要事務管理，commit之后才能生效。drop和truncate屬于DDL語言，操作立刻生效，不可回滾。

使用場合：

• 當你不再需要該表時， 用 drop;
• 當你仍要保留該表，但要刪除所有記錄時， 用 truncate;
• 當你要刪除部分記錄時（always with a where clause), 用 delete.
   

注意： 對于有主外鍵關系的表，不能使用truncate而應該使用不帶where子句的delete語句，由于truncate不記錄在日志中，不能夠激活觸發(fā)器

以上就是本文的全部內(nèi)容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。

最新評論