1. ACID
- 原子性(Atomicity)
原子性是指事務(wù)包含的所有操作要么全部成功重父,要么全部失敗回滾摊沉,因此事務(wù)的操作如果成功就必須要完全應(yīng)用到數(shù)據(jù)庫(kù)室琢,如果操作失敗則不能對(duì)數(shù)據(jù)庫(kù)有任何影響抽诉。 - 一致性(Consistency)
一致性是指事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變換到另一個(gè)一致性狀態(tài)礁凡,也就是說(shuō)一個(gè)事務(wù)執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)庐冯。
拿轉(zhuǎn)賬來(lái)說(shuō)孽亲,假設(shè)用戶A和用戶B兩者的錢(qián)加起來(lái)一共是5000,那么不管A和B之間如何轉(zhuǎn)賬展父,轉(zhuǎn)幾次賬返劲,事務(wù)結(jié)束后兩個(gè)用戶的錢(qián)相加起來(lái)應(yīng)該還得是5000,這就是事務(wù)的一致性栖茉。 - 隔離性(Isolation)
隔離性是當(dāng)多個(gè)用戶并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)篮绿,比如操作同一張表時(shí),數(shù)據(jù)庫(kù)為每一個(gè)用戶開(kāi)啟的事務(wù)吕漂,不能被其他事務(wù)的操作所干擾亲配,多個(gè)并發(fā)事務(wù)之間要相互隔離。
即要達(dá)到這么一種效果:對(duì)于任意兩個(gè)并發(fā)的事務(wù)T1和T2惶凝,在事務(wù)T1看來(lái)吼虎,T2要么在T1開(kāi)始之前就已經(jīng)結(jié)束,要么在T1結(jié)束之后才開(kāi)始苍鲜,這樣每個(gè)事務(wù)都感覺(jué)不到有其他事務(wù)在并發(fā)地執(zhí)行思灰。
關(guān)于事務(wù)的隔離性數(shù)據(jù)庫(kù)提供了多種隔離級(jí)別,稍后會(huì)介紹到混滔。 - 持久性(Durability)
持久性是指一個(gè)事務(wù)一旦被提交了洒疚,那么對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的改變就是永久性的,即便是在數(shù)據(jù)庫(kù)系統(tǒng)遇到故障的情況下也不會(huì)丟失提交事務(wù)的操作遍坟。
2. 隔離問(wèn)題
- 臟讀
臟讀是指在一個(gè)事務(wù)處理過(guò)程里讀取了另一個(gè)未提交的事務(wù)中的數(shù)據(jù)拳亿。
當(dāng)一個(gè)事務(wù)正在多次修改某個(gè)數(shù)據(jù),而在這個(gè)事務(wù)中這多次的修改都還未提交愿伴,這時(shí)一個(gè)并發(fā)的事務(wù)來(lái)訪問(wèn)該數(shù)據(jù)肺魁,就會(huì)造成兩個(gè)事務(wù)得到的數(shù)據(jù)不一致。例如:用戶A向用戶B轉(zhuǎn)賬100元隔节,對(duì)應(yīng)SQL命令如下
update account set money=money+100 where name=’B’; (此時(shí)A通知B)
update account set money=money - 100 where name=’A’;
當(dāng)只執(zhí)行第一條SQL時(shí)鹅经,A通知B查看賬戶寂呛,B發(fā)現(xiàn)確實(shí)錢(qián)已到賬(此時(shí)即發(fā)生了臟讀),而之后無(wú)論第二條SQL是否執(zhí)行瘾晃,只要該事務(wù)不提交贷痪,則所有操作都將回滾,那么當(dāng)B以后再次查看賬戶時(shí)就會(huì)發(fā)現(xiàn)錢(qián)其實(shí)并沒(méi)有轉(zhuǎn)蹦误。
不可重復(fù)讀
不可重復(fù)讀是指在對(duì)于數(shù)據(jù)庫(kù)中的某個(gè)數(shù)據(jù)劫拢,一個(gè)事務(wù)范圍內(nèi)多次查詢卻返回了不同的數(shù)據(jù)值,這是由于在查詢間隔强胰,被另一個(gè)事務(wù)修改并提交了舱沧。
例如事務(wù)T1在讀取某一數(shù)據(jù),而事務(wù)T2立馬修改了這個(gè)數(shù)據(jù)并且提交事務(wù)給數(shù)據(jù)庫(kù)偶洋,事務(wù)T1再次讀取該數(shù)據(jù)就得到了不同的結(jié)果熟吏,發(fā)送了不可重復(fù)讀。
不可重復(fù)讀和臟讀的區(qū)別是玄窝,臟讀是某一事務(wù)讀取了另一個(gè)事務(wù)未提交的臟數(shù)據(jù)牵寺,而不可重復(fù)讀則是讀取了前一事務(wù)提交的數(shù)據(jù)。
在某些情況下恩脂,不可重復(fù)讀并不是問(wèn)題帽氓,比如我們多次查詢某個(gè)數(shù)據(jù)當(dāng)然以最后查詢得到的結(jié)果為主。但在另一些情況下就有可能發(fā)生問(wèn)題东亦,例如對(duì)于同一個(gè)數(shù)據(jù)A和B依次查詢就可能不同杏节,A和B就可能打起來(lái)了……虛讀(幻讀)
幻讀是事務(wù)非獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象。例如事務(wù)T1對(duì)一個(gè)表中所有的行的某個(gè)數(shù)據(jù)項(xiàng)做了從“1”修改為“2”的操作典阵,這時(shí)事務(wù)T2又對(duì)這個(gè)表中插入了一行數(shù)據(jù)項(xiàng)奋渔,而這個(gè)數(shù)據(jù)項(xiàng)的數(shù)值還是為“1”并且提交給數(shù)據(jù)庫(kù)。而操作事務(wù)T1的用戶如果再查看剛剛修改的數(shù)據(jù)壮啊,會(huì)發(fā)現(xiàn)還有一行沒(méi)有修改嫉鲸,其實(shí)這行是從事務(wù)T2中添加的,就好像產(chǎn)生幻覺(jué)一樣歹啼,這就是發(fā)生了幻讀玄渗。
幻讀和不可重復(fù)讀都是讀取了另一條已經(jīng)提交的事務(wù)(這點(diǎn)就臟讀不同),所不同的是不可重復(fù)讀查詢的都是同一個(gè)數(shù)據(jù)項(xiàng)狸眼,而幻讀針對(duì)的是一批數(shù)據(jù)整體(比如數(shù)據(jù)的個(gè)數(shù))藤树。
3. 隔離級(jí)別
MySQL數(shù)據(jù)庫(kù)為我們提供的四種隔離級(jí)別:
① Serializable (串行化):可避免臟讀拓萌、不可重復(fù)讀岁钓、幻讀的發(fā)生。
② Repeatable read (可重復(fù)讀):可避免臟讀屡限、不可重復(fù)讀的發(fā)生品嚣。
③ Read committed (讀已提交):可避免臟讀的發(fā)生钧大。
『渤拧④ Read uncommitted (讀未提交):最低級(jí)別,任何情況都無(wú)法保證啊央。
以上四種隔離級(jí)別最高的是Serializable級(jí)別眶诈,最低的是Read uncommitted級(jí)別,當(dāng)然級(jí)別越高瓜饥,執(zhí)行效率就越低册养。像Serializable這樣的級(jí)別,就是以鎖表的方式(類似于Java多線程中的鎖)使得其他的線程只能在鎖外等待压固,所以平時(shí)選用何種隔離級(jí)別應(yīng)該根據(jù)實(shí)際情況。在MySQL數(shù)據(jù)庫(kù)中默認(rèn)的隔離級(jí)別為Repeatable read (可重復(fù)讀)靠闭。
在MySQL數(shù)據(jù)庫(kù)中帐我,支持上面四種隔離級(jí)別,默認(rèn)的為Repeatable read (可重復(fù)讀)愧膀;而在Oracle數(shù)據(jù)庫(kù)中拦键,只支持Serializable (串行化)級(jí)別和Read committed (讀已提交)這兩種級(jí)別,其中默認(rèn)的為Read committed級(jí)別檩淋。
4. 索引
索引加快了查詢速度芬为,但是要付出代價(jià)。
比如表的插入和刪除速度會(huì)減慢蟀悦,因?yàn)樾枰滤饕?br>
如果表需要不斷更新媚朦,索引很可能會(huì)導(dǎo)致performance問(wèn)題。
還有空間代價(jià)日戈。索引會(huì)占用內(nèi)存或磁盤(pán)空間询张。
單個(gè)索引比表小,因?yàn)樗淮嫠械谋頂?shù)據(jù)浙炼,而是存相應(yīng)的指針份氧。
但表越大,索引通常也會(huì)跟著變大弯屈。
索引的類型
A)聚集索引蜗帜,表數(shù)據(jù)按照索引的順序來(lái)存儲(chǔ)的。對(duì)于聚集索引资厉,葉子結(jié)點(diǎn)即存儲(chǔ)了真實(shí)的數(shù)據(jù)行厅缺,不再有另外單獨(dú)的數(shù)據(jù)頁(yè)。
B)非聚集索引,表數(shù)據(jù)存儲(chǔ)順序與索引順序無(wú)關(guān)店归。對(duì)于非聚集索引阎抒,葉結(jié)點(diǎn)包含索引字段值及指向數(shù)據(jù)頁(yè)數(shù)據(jù)行的邏輯指針,該層緊鄰數(shù)據(jù)頁(yè)消痛,其行數(shù)量與數(shù)據(jù)表行數(shù)據(jù)量一致且叁。
在一張表上只能創(chuàng)建一個(gè)聚集索引,因?yàn)檎鎸?shí)數(shù)據(jù)的物理順序只可能是一種秩伞。如果一張表沒(méi)有聚集索引逞带,那么它被稱為“堆集”(Heap)。這樣的表中的數(shù)據(jù)行沒(méi)有特定的順序纱新,所有的新行將被添加的表的末尾位置展氓。
Hash索引
找姓Smith的人,我們可以建一個(gè)hash表脸爱。hash表的key就是last_name遇汞,value可以是指向數(shù)據(jù)行的指針。
這類索引就叫hash索引簿废。很多數(shù)據(jù)庫(kù)都支持這里索引空入。
但是它不常用。為什么族檬?
考慮另一個(gè)查詢:找所有45歲以下的人歪赢。hash索引可以處理等于關(guān)系,但不處理小于或大于關(guān)系单料。
給你2個(gè)的hash索引埋凯,它無(wú)法判斷那個(gè)值更大,只能判斷它們是否相等扫尖。bitmap索引
它的讀取速度很快白对,但是比較占存儲(chǔ)空間。適用于值稀疏分布的列换怖。B-tree索引
它允許對(duì)數(shù)階復(fù)雜度的查找躏结、插入和刪除。
和hash索引不同之處在于狰域,它存的數(shù)據(jù)是有序的媳拴,這樣能處理小于、大于和前綴的查詢兆览。
非聚集索引與聚集索引相比:
A)葉子結(jié)點(diǎn)并非數(shù)據(jù)結(jié)點(diǎn)
B)葉子結(jié)點(diǎn)為每一真正的數(shù)據(jù)行存儲(chǔ)一個(gè)“鍵-指針”對(duì)
C)葉子結(jié)點(diǎn)中還存儲(chǔ)了一個(gè)指針偏移量屈溉,根據(jù)頁(yè)指針及指針偏移量可以定位到具體的數(shù)據(jù)行。
D)類似的抬探,在除葉結(jié)點(diǎn)外的其它索引結(jié)點(diǎn)子巾,存儲(chǔ)的也是類似的內(nèi)容帆赢,只不過(guò)它是指向下一級(jí)的索引頁(yè)的。
聚集索引是一種稀疏索引线梗,數(shù)據(jù)頁(yè)上一級(jí)的索引頁(yè)存儲(chǔ)的是頁(yè)指針椰于,而不是行指針。而對(duì)于非聚集索引仪搔,則是密集索引瘾婿,在數(shù)據(jù)頁(yè)的上一級(jí)索引頁(yè)它為每一個(gè)數(shù)據(jù)行存儲(chǔ)一條索引記錄。
對(duì)于根與中間級(jí)的索引記錄烤咧,它的結(jié)構(gòu)包括:
A)索引字段值
B)RowId(即對(duì)應(yīng)數(shù)據(jù)頁(yè)的頁(yè)指針+指針偏移量)偏陪。在高層的索引頁(yè)中包含RowId是為了當(dāng)索引允許重復(fù)值時(shí),當(dāng)更改數(shù)據(jù)時(shí)精確定位數(shù)據(jù)行煮嫌。
C)下一級(jí)索引頁(yè)的指針
對(duì)于葉子層的索引對(duì)象笛谦,它的結(jié)構(gòu)包括:
A)索引字段值
B)RowId-
B+Tree 與BTree區(qū)別
結(jié)構(gòu)上- B樹(shù)中關(guān)鍵字集合分布在整棵樹(shù)中,葉節(jié)點(diǎn)中不包含任何關(guān)鍵字信息昌阿,而B(niǎo)+樹(shù)關(guān)鍵字集合分布在葉子結(jié)點(diǎn)中饥脑,非葉節(jié)點(diǎn)只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引;
- B樹(shù)中任何一個(gè)關(guān)鍵字只出現(xiàn)在一個(gè)結(jié)點(diǎn)中懦冰,而B(niǎo)+樹(shù)中的關(guān)鍵字必須出現(xiàn)在葉節(jié)點(diǎn)中好啰,也可能在非葉結(jié)點(diǎn)中重復(fù)出現(xiàn);
性能上
- B+樹(shù)比B樹(shù)更適合實(shí)際應(yīng)用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫(kù)索引
- B+樹(shù)的磁盤(pán)讀寫(xiě)代價(jià)更低儿奶。B+樹(shù)的內(nèi)部結(jié)點(diǎn)并沒(méi)有指向關(guān)鍵字具體信息的指針,其內(nèi)部結(jié)點(diǎn)比B樹(shù)小鳄抒,盤(pán)塊能容納的結(jié)點(diǎn)中關(guān)鍵字?jǐn)?shù)量更多闯捎,一次性讀入內(nèi)存中可以查找的關(guān)鍵字也就越多,相對(duì)的许溅,IO讀寫(xiě)次數(shù)也就降低了瓤鼻。而IO讀寫(xiě)次數(shù)是影響索引檢索效率的最大因素。
- B+樹(shù)的查詢效率更加穩(wěn)定贤重。B樹(shù)搜索有可能會(huì)在非葉子結(jié)點(diǎn)結(jié)束茬祷,越靠近根節(jié)點(diǎn)的記錄查找時(shí)間越短,只要找到關(guān)鍵字即可確定記錄的存在并蝗,其性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找祭犯。而在B+樹(shù)中,順序檢索比較明顯滚停,隨機(jī)檢索時(shí)沃粗,任何關(guān)鍵字的查找都必須走一條從根節(jié)點(diǎn)到葉節(jié)點(diǎn)的路,所有關(guān)鍵字的查找路徑長(zhǎng)度相同键畴,導(dǎo)致每一個(gè)關(guān)鍵字的查詢效率相當(dāng)最盅。
- (數(shù)據(jù)庫(kù)索引采用B+樹(shù)的主要原因是)B-樹(shù)在提高了磁盤(pán)IO性能的同時(shí)并沒(méi)有解決元素遍歷的效率低下的問(wèn)題。B+樹(shù)的葉子節(jié)點(diǎn)使用指針順序連接在一起,只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹(shù)的遍歷涡贱。而且在數(shù)據(jù)庫(kù)中基于范圍的查詢是非常頻繁的咏删,而B(niǎo)樹(shù)不支持這樣的操作,或者說(shuō)效率太低。
5. 范式
第一范式(1NF)是指在關(guān)系模型中问词,對(duì)域添加的一個(gè)規(guī)范要求督函,所有的域都應(yīng)該是原子性的,即數(shù)據(jù)庫(kù)表的每一列都是不可分割的原子數(shù)據(jù)項(xiàng)戏售,而不能是集合侨核,數(shù)組,記錄等非原子數(shù)據(jù)項(xiàng)灌灾。即實(shí)體中的某個(gè)屬性有多個(gè)值時(shí)搓译,必須拆分為不同的屬性。在符合第一范式(1NF)表中的每個(gè)域值只能是實(shí)體的一個(gè)屬性或一個(gè)屬性的一部分锋喜。簡(jiǎn)而言之些己,第一范式就是無(wú)重復(fù)的域。
第二范式(2NF)非碼屬性必須完全依賴于候選碼嘿般,要求數(shù)據(jù)庫(kù)表中的每個(gè)實(shí)例或記錄必須可以被唯一地區(qū)分段标。
第三范式(3NF),非主屬性不依賴于其它非主屬性(在2NF基礎(chǔ)上消除傳遞依賴)炉奴,不得有冗余
6. 引擎對(duì)比
InnoDB:
1.ACID事務(wù)支持逼庞、系統(tǒng)崩潰修復(fù)能力和多版本并發(fā)控制(即MVCC Multi-Version Concurrency Control)的行級(jí)鎖;
2.支持自增長(zhǎng)列(auto_increment),自增長(zhǎng)列的值不能為空瞻赶;
3.該引擎存儲(chǔ)引擎支持外鍵(foreign key)MyISAM:
1.索引和字段管理赛糟;
2.MyISAM強(qiáng)調(diào)了快速讀取操作,主要用于高負(fù)載的select砸逊,這可能也是MySQL深受Web開(kāi)發(fā)的主要原因:在Web開(kāi)發(fā)中進(jìn)行的大量數(shù)據(jù)操作都是讀取操作璧南,所以大多數(shù)虛擬主機(jī)提供商和Internet平臺(tái)提供商(Internet Presence Provider,IPP)只允許使用MyISAM格式
MyISAM類型的表支持三種不同的存儲(chǔ)結(jié)構(gòu):靜態(tài)型师逸、動(dòng)態(tài)型司倚、壓縮型。
靜態(tài)型:指定義的表列的大小是固定(即不含有:xblob篓像、xtext动知、varchar等長(zhǎng)度可變的數(shù)據(jù)類型),這樣MySQL就會(huì)自動(dòng)使用靜態(tài)MyISAM格式员辩。使用靜態(tài)格式的表的性能比較高拍柒,因?yàn)樵诰S護(hù)和訪問(wèn)以預(yù)定格式存儲(chǔ)數(shù)據(jù)時(shí)需要的開(kāi)銷很低;但這種高性能是以空間為代價(jià)換來(lái)的屈暗,因?yàn)樵诙x的時(shí)候是固定的拆讯,所以不管列中的值有多大脂男,都會(huì)以最大值為準(zhǔn),占據(jù)了整個(gè)空間种呐。
動(dòng)態(tài)型:如果列(即使只有一列)定義為動(dòng)態(tài)的(xblob, xtext, varchar等數(shù)據(jù)類型)宰翅,這時(shí)MyISAM就自動(dòng)使用動(dòng)態(tài)型,雖然動(dòng)態(tài)型的表占用了比靜態(tài)型表較少的空間爽室,但帶來(lái)了性能的降低汁讼,因?yàn)槿绻硞€(gè)字段的內(nèi)容發(fā)生改變則其位置很可能需要移動(dòng),這樣就會(huì)導(dǎo)致碎片的產(chǎn)生阔墩,隨著數(shù)據(jù)變化的增多嘿架,碎片也隨之增加,數(shù)據(jù)訪問(wèn)性能會(huì)隨之降低啸箫。
對(duì)于因碎片增加而降低數(shù)據(jù)訪問(wèn)性這個(gè)問(wèn)題耸彪,有兩種解決辦法:
a、盡可能使用靜態(tài)數(shù)據(jù)類型忘苛;
b蝉娜、經(jīng)常使用optimize table table_name語(yǔ)句整理表的碎片,恢復(fù)由于表數(shù)據(jù)的更新和刪除導(dǎo)致的空間丟失扎唾。如果存儲(chǔ)引擎不支持 optimize table table_name則可以轉(zhuǎn)儲(chǔ)并重新加載數(shù)據(jù)召川,這樣也可以減少碎片;
壓縮型:如果在數(shù)據(jù)庫(kù)中創(chuàng)建在整個(gè)生命周期內(nèi)只讀的表胸遇,則應(yīng)該使用MyISAM的壓縮型表來(lái)減少空間的占用荧呐。
7.執(zhí)行順序
select過(guò)程:from->where->group by->having->order by->limit
8.Join
三類:
1.INNER JOIN(內(nèi)連接,或等值連接):取得兩個(gè)表中存在連接匹配關(guān)系的記錄。
2.LEFT JOIN(左連接):取得左表(table1)完全記錄纸镊,即是右表(table2)并無(wú)對(duì)應(yīng)匹配記錄倍阐。
3.RIGHT JOIN(右連接):與 LEFT JOIN 相反,取得右表(table2)完全記錄薄腻,即是左表(table1)并無(wú)匹配對(duì)應(yīng)記錄。
注意:mysql不支持Full join,不過(guò)可以通過(guò)UNION 關(guān)鍵字來(lái)合并 LEFT JOIN 與 RIGHT JOIN來(lái)模擬FULL join.
Mysql Join內(nèi)部實(shí)現(xiàn):NestedLoopJoin
參考:http://www.cnblogs.com/ggjucheng/archive/2012/11/15/2772148.html
顧名思義届案,NestedLoopJoin實(shí)際上就是通過(guò)驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)庵楷,然后一條一條的通過(guò)該結(jié)果集中的數(shù)據(jù)作為過(guò)濾條件到下一個(gè)表中查詢數(shù)據(jù),然后合并結(jié)果楣颠。如果還有第三個(gè)參與Join尽纽,則再通過(guò)前兩個(gè)表的Join結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù),再一次通過(guò)循環(huán)查詢條件到第三個(gè)表中查詢數(shù)據(jù)童漩,如此往復(fù)弄贿。
Join優(yōu)化:
盡可能減少Join語(yǔ)句中的NestedLoop的循環(huán)總次數(shù);如何減少NestedLoop的循環(huán)總次數(shù)矫膨?最有效的辦法只有一個(gè)差凹,那就是讓驅(qū)動(dòng)表的結(jié)果集盡可能的小期奔,這也正是優(yōu)化基本原則之一“永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大的結(jié)果集”。
保證被驅(qū)動(dòng)表上Join條件字段已經(jīng)被索引的目的危尿,正是針對(duì)上面兩點(diǎn)的考慮呐萌,只有讓被驅(qū)動(dòng)表的Join條件字段被索引了,才能保證循環(huán)中每次查詢都能夠消耗較少的資源谊娇,這也正是優(yōu)化內(nèi)層循環(huán)的實(shí)際優(yōu)化方法肺孤。
當(dāng)無(wú)法保證被驅(qū)動(dòng)表的Join條件字段被索引且內(nèi)存資源充足的前提下,不要太吝惜JoinBuffer的設(shè)置济欢;當(dāng)在某些特殊的環(huán)境中赠堵,我們的Join必須是All,Index法褥,range或者是index_merge類型的時(shí)候茫叭,JoinBuffer就會(huì)派上用場(chǎng)了。在這種情況下挖胃,JoinBuffer的大小將對(duì)整個(gè)Join語(yǔ)句的消耗起到非常關(guān)鍵的作用杂靶。