事務(wù)四大特性

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

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

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

索引

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

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

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

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

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

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

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

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

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

　　所有存儲(chǔ)引擎對(duì)每個(gè)表至少支持16個(gè)索引，總索引長(zhǎng)度至少為256字節(jié)，索引有兩種存儲(chǔ)類型，包括B型樹(shù)索引和哈希索引。

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

索引生效條件

假設(shè)index（a,b,c）

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

檢測(cè)索引的效果：

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

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

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

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

臟讀&不可重復(fù)讀&幻讀

臟讀: 是指事務(wù)T1將某一值修改，然后事務(wù)T2讀取該值，此后T1因?yàn)槟撤N原因撤銷對(duì)該值的修改，這就導(dǎo)致了T2所讀取到的數(shù)據(jù)是無(wú)效的。

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

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

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

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

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

1）InnoDB支持事務(wù)，MyISAM不支持。

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

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

4）從MySQL5.5.5以后，InnoDB是默認(rèn)引擎。

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

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

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

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

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

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

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

應(yīng)用場(chǎng)景：

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

CHAR和VARCHAR的區(qū)別：

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

Mysql中有哪幾種鎖？

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

存儲(chǔ)過(guò)程

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

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

優(yōu)點(diǎn)：

(1).存儲(chǔ)過(guò)程增強(qiáng)了SQL語(yǔ)言的功能和靈活性。存儲(chǔ)過(guò)程可以用流控制語(yǔ)句編寫，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。

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

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

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

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

delete、drop、truncate區(qū)別

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

使用場(chǎng)合：

• 當(dāng)你不再需要該表時(shí)， 用 drop;
• 當(dāng)你仍要保留該表，但要?jiǎng)h除所有記錄時(shí)， 用 truncate;
• 當(dāng)你要?jiǎng)h除部分記錄時(shí)（always with a where clause), 用 delete.
   

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

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論