MySQL這樣學(xué)才叫了解！【4】

十一、事務(wù)

11.1、什么是事務(wù)

一個(gè)事務(wù)是一個(gè)i完整的業(yè)務(wù)邏輯單元，不可再分。事務(wù)可以保證多個(gè)操作原子性，要么全成功，要么全失敗。對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)事務(wù)保證批量的DML要么全成功，要么全失敗。

和事務(wù)相關(guān)的語(yǔ)句只有DML語(yǔ)句，因?yàn)樗麄冞@三個(gè)語(yǔ)句都是和數(shù)據(jù)庫(kù)表中的數(shù)據(jù)相關(guān)的。事務(wù)的存在是為了保證數(shù)據(jù)的完整性、安全性。

11.2、開(kāi)啟事務(wù)的原理

假設(shè)我們完成一個(gè)操作，需要先執(zhí)行一條insert，然后再執(zhí)行一條update，最后執(zhí)行一條delete，在Mysql中執(zhí)行流程可以這么理解：

11.3、事務(wù)的特征

事務(wù)具有四個(gè)特征ACID

原子性（Atomicity）

事務(wù)是最小的工作單元，不可再分。整個(gè)事務(wù)中的所有操作，必須作為一個(gè)單元全部完成（取消）。

一致性（Consistency）

事務(wù)必須保證多條DML語(yǔ)句同時(shí)成功或者同時(shí)失敗。

隔離性(Isolation)

一個(gè)事務(wù)不會(huì)影響其他事務(wù)的運(yùn)行。

持久性(Durability)

最終該事務(wù)對(duì)數(shù)據(jù)庫(kù)所作的更改將持久地保存在硬盤文件之中，事務(wù)才算成功。

MySQL事務(wù)默認(rèn)情況下是自動(dòng)提交的，可以通過(guò)命令來(lái)改成手工提交。

start transaction;

11.4、隔離性詳解

11.4.1、并發(fā)訪問(wèn)可能導(dǎo)致的問(wèn)題

一個(gè)事務(wù)開(kāi)始讀取了某行數(shù)據(jù)，但是另外一個(gè)事務(wù)已經(jīng)更新了此數(shù)據(jù)但沒(méi)有能夠及時(shí)提交，這就出現(xiàn)了臟讀取。

在同一個(gè)事務(wù)中，同一個(gè)讀操作對(duì)同一個(gè)數(shù)據(jù)的前后兩次讀取產(chǎn)生了不同的結(jié)果，這就是不可重復(fù)讀。

幻像讀是指在同一個(gè)事務(wù)中以前沒(méi)有的行，由于其他事務(wù)的提交而出現(xiàn)的新行。幻讀強(qiáng)調(diào)的是前后讀的行數(shù)不一樣。

11.4.2、隔離級(jí)別

InnoDB 實(shí)現(xiàn)了四個(gè)隔離級(jí)別，用以控制事務(wù)所做的修改，并將修改通告至其它并發(fā)的事務(wù)。隔離級(jí)別從低往高依次是：

讀未提交（READ UMCOMMITTED）

讀已提交（READ COMMITTED）

可重復(fù)讀（REPEATABLE READ） MySQL默認(rèn)

串行化（SERIALIZABLE）

對(duì)方的事務(wù)還沒(méi)有提交，我們當(dāng)前事務(wù)可以讀取到對(duì)方未提交的數(shù)據(jù)。這種隔離級(jí)別是最低的，讀為未提交存在臟讀現(xiàn)象，表示堵到了臟數(shù)據(jù)。

對(duì)方事務(wù)提交之后的數(shù)據(jù)我們才可以讀到，這種隔離級(jí)別解決了臟讀現(xiàn)象，但是卻出現(xiàn)了不可重復(fù)讀現(xiàn)象。

這個(gè)級(jí)別是oracle的默認(rèn)隔離級(jí)別。

我們無(wú)法看到已提交的事務(wù)了，這種隔離級(jí)別雖然解決了不可重復(fù)讀的問(wèn)題，但是卻帶來(lái)了幻讀的問(wèn)題。比方說(shuō)一個(gè)線程刪除了數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)，但是我們依然讀取的是原來(lái)的數(shù)據(jù)，讀到的是數(shù)據(jù)庫(kù)的備份。

MySQL的默認(rèn)級(jí)別。

將一個(gè)事務(wù)與其他事務(wù)完全地隔離。兩個(gè)事務(wù)不可以并發(fā)，線程之間需要排隊(duì)，也叫作序列化。雖然很安全，但是性能很低且客戶的體驗(yàn)不好。

十二、索引

12.1、什么是索引

索引相當(dāng)于一本書的目錄，通過(guò)目錄可以快速找到對(duì)應(yīng)的資源。索引被用來(lái)快速找出在一個(gè)列上用一特定值的行，索引可以有效地縮小掃描的范圍。添加索引是給某個(gè)字段或者是某些字段添加的。

在數(shù)據(jù)庫(kù)方面，查詢一張表的時(shí)候有兩種檢索方式：

全表掃描

根據(jù)索引檢索（效率高）

索引雖然可以提高檢索的效率，但是不能隨意添加索引，因?yàn)樗饕彩菙?shù)據(jù)庫(kù)中的對(duì)象，也需要數(shù)據(jù)庫(kù)不斷地維護(hù)，維護(hù)需要成本的。比如表中的的數(shù)據(jù)如果經(jīng)常被修改的話就不適合添加索引，因?yàn)閿?shù)據(jù)一旦被修改，索引需要重新排序。

12.2、什么時(shí)候需要?jiǎng)?chuàng)建索引

數(shù)據(jù)量龐大。

該字段很少的DML操作（因?yàn)樽侄芜M(jìn)行修改操作，索引也需要維護(hù)）。

該字段經(jīng)常出現(xiàn)在where子句中（經(jīng)常根據(jù)哪個(gè)字段查詢）

注意：主鍵和具有unique約束的字段會(huì)自動(dòng)添加索引，根據(jù)主鍵查詢的效率高，盡量根據(jù)主鍵索引，我們可以查詢sql語(yǔ)句的執(zhí)行計(jì)劃。他的底層是B+Tree。

explain select * from emp where SAL = 1500;

type字段的值時(shí)ALL表示是全表掃描（沒(méi)有添加索引）。rows表示搜索了14條數(shù)據(jù)。

12.3、添加索引

-- 給emp表的sal字段添加一個(gè)索引，名稱為emp_sal_index create index emp_sal_index on emp(sal); -- 語(yǔ)法格式 create index 索引名稱 on 表名(字段名)

12.4、查看索引

-- 查看索引的語(yǔ)法 show index from emp; -- 語(yǔ)法格式 show index from 表名;

12.5、刪除索引

-- 刪除索引的語(yǔ)法 drop index 索引名稱 on 表名;

12.6、索引的原理

索引底層采用的數(shù)據(jù)結(jié)構(gòu)是B+Tree，通過(guò)B+Tress縮小掃描范圍，底層索引進(jìn)行排序、分區(qū)，索引會(huì)攜帶在表中的物理地址，最終通過(guò)索引檢索到數(shù)據(jù)之后，獲取到關(guān)聯(lián)的物理地址，通過(guò)物理地址定位到表中的數(shù)據(jù)，效率是最高的（不走表，走硬盤）。

select ename from emp where ename = 'SMITH';

通過(guò)索引sql語(yǔ)句會(huì)轉(zhuǎn)換

select ename from emp where 物理地址 = '索引檢索到的物理地址'

12.7、索引的分類

單一索引：給打那個(gè)字段添加索引。

復(fù)合索引：給多個(gè)字段聯(lián)合起來(lái)添加索引。

主鍵索引：主鍵上會(huì)自動(dòng)添加索引。

唯一索引：有unique約束的字段上會(huì)自動(dòng)添加索引。

12.8、索引的失效

在模糊查詢的時(shí)候，如果第一個(gè)通配符使用的是%，這個(gè)索引會(huì)失效，因?yàn)樗恢酪婚_(kāi)始匹配的字符是什么。

十三、視圖

13.1、什么是視圖

視圖是一種根據(jù)查詢（也就是SELECT表達(dá)式）定義的數(shù)據(jù)庫(kù)對(duì)象，用于獲取想要看到和使用的局部數(shù)據(jù)。所以他也稱為虛擬表。

視圖是站在不同的角度看到數(shù)據(jù)，同一張表的數(shù)據(jù)，通過(guò)不同的角度去看待數(shù)據(jù)。

我們可以對(duì)視圖進(jìn)行增刪改查，會(huì)影響到原表的數(shù)據(jù)，通過(guò)視圖來(lái)影響原表數(shù)據(jù)的，并不是直接操作原表。只有DQL語(yǔ)句才可以以視圖對(duì)象的方式創(chuàng)建出來(lái)。

13.2、創(chuàng)建視圖

-- 語(yǔ)法格式 create view 視圖名 as select語(yǔ)句 -- 示范 create view myview as select empo,ename from emp;

13.3、修改視圖

-- 語(yǔ)法格式 update 視圖名 set 列名 = '值' where 條件;

13.4、刪除視圖

-- 語(yǔ)法格式 delete from 視圖名 where 條件; -- 示范 delete from myview where empo = '12134';

13.5、視圖的作用

視圖可以隱藏表的實(shí)現(xiàn)細(xì)節(jié)，保密級(jí)別比較高的系統(tǒng)，數(shù)據(jù)庫(kù)只對(duì)外提供相關(guān)的視圖，面向視圖對(duì)象進(jìn)行CRUD。

十四、數(shù)據(jù)庫(kù)設(shè)計(jì)三范式

設(shè)計(jì)范式是設(shè)計(jì)表的依據(jù)，按照這三個(gè)范式設(shè)計(jì)的表不會(huì)出現(xiàn)數(shù)據(jù)冗余。但是在實(shí)際開(kāi)發(fā)中，根據(jù)客戶的需求，可能會(huì)拿數(shù)據(jù)冗余來(lái)?yè)Q取執(zhí)行速度，拿空間換時(shí)間。

14.1、第一范式

任何一張表都應(yīng)該有主鍵，且每一個(gè)字段原子性不可再分。

14.2、第二范式

建立在第一范式的基礎(chǔ)上，所有非主鍵字段完全依賴于主鍵，不能產(chǎn)生部分依賴。

典型的例子就是解決多對(duì)多的問(wèn)題上，遇到多對(duì)多的時(shí)候，背口訣：多對(duì)多？三張表，關(guān)系表兩外鍵

14.3、第三范式

建立在第二范式的基礎(chǔ)上，所有非主鍵字段直接依賴主鍵，不能產(chǎn)生傳遞依賴。

典型的例子就是一對(duì)多，遇到一對(duì)多問(wèn)題的時(shí)候背口訣：一對(duì)多？?jī)蓮埍恚嗟谋砑油怄I。

14.4、一對(duì)一關(guān)系的設(shè)計(jì)方案

14.4.1、主鍵共享

t_user_login 用戶登錄表

t_user_detail 用戶詳細(xì)信息表

14.4.2、外鍵唯一

t_user_login 用戶登錄表

t_user_detail 用戶詳細(xì)信息表

視圖

-- 語(yǔ)法格式 delete from 視圖名 where 條件; -- 示范 delete from myview where empo = '12134';

13.5、視圖的作用

視圖可以隱藏表的實(shí)現(xiàn)細(xì)節(jié)，保密級(jí)別比較高的系統(tǒng)，數(shù)據(jù)庫(kù)只對(duì)外提供相關(guān)的視圖，面向視圖對(duì)象進(jìn)行CRUD。

十四、數(shù)據(jù)庫(kù)設(shè)計(jì)三范式

設(shè)計(jì)范式是設(shè)計(jì)表的依據(jù)，按照這三個(gè)范式設(shè)計(jì)的表不會(huì)出現(xiàn)數(shù)據(jù)冗余。但是在實(shí)際開(kāi)發(fā)中，根據(jù)客戶的需求，可能會(huì)拿數(shù)據(jù)冗余來(lái)?yè)Q取執(zhí)行速度，拿空間換時(shí)間。

14.1、第一范式

任何一張表都應(yīng)該有主鍵，且每一個(gè)字段原子性不可再分。

14.2、第二范式

建立在第一范式的基礎(chǔ)上，所有非主鍵字段完全依賴于主鍵，不能產(chǎn)生部分依賴。

典型的例子就是解決多對(duì)多的問(wèn)題上，遇到多對(duì)多的時(shí)候，背口訣：多對(duì)多？三張表，關(guān)系表兩外鍵

14.3、第三范式

建立在第二范式的基礎(chǔ)上，所有非主鍵字段直接依賴主鍵，不能產(chǎn)生傳遞依賴。

典型的例子就是一對(duì)多，遇到一對(duì)多問(wèn)題的時(shí)候背口訣：一對(duì)多？?jī)蓮埍恚嗟谋砑油怄I。

14.4、一對(duì)一關(guān)系的設(shè)計(jì)方案

14.4.1、主鍵共享

t_user_login 用戶登錄表

t_user_detail 用戶詳細(xì)信息表

14.4.2、外鍵唯一

t_user_login 用戶登錄表

t_user_detail 用戶詳細(xì)信息表

MySQL 數(shù)據(jù)庫(kù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。