這篇文章主要講解了“MySQL架構(gòu)與存儲(chǔ)引擎，鎖，事務(wù)，設(shè)計(jì)分析”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL架構(gòu)與存儲(chǔ)引擎，鎖，事務(wù)，設(shè)計(jì)分析”吧！

創(chuàng)新互聯(lián)主營(yíng)同安網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都app軟件開發(fā),同安h5微信小程序搭建,同安網(wǎng)站營(yíng)銷推廣歡迎同安等地區(qū)企業(yè)咨詢

衡量指標(biāo)

TPS

Transactions Per Second(每秒傳輸?shù)绞聞?wù)處理個(gè)數(shù))，這里是指服務(wù)器每秒處理事務(wù)數(shù)，支持事務(wù)的存儲(chǔ)引擎如InnoDB等特有等一個(gè)性能指標(biāo)。單位時(shí)間內(nèi)事務(wù)執(zhí)行成功跟事務(wù)回滾數(shù)
TPS= (COM_COMMIT + COM_ROLLBACK)/UPTIME

QPS

Queries Per Second(每秒查詢處理量)，同時(shí)適用于InnoDB跟MyISAM引擎。
QPS= QUESTIONS/UPTIME
等待時(shí)間：執(zhí)行SQL等待返回結(jié)果之間的等待時(shí)間。

順序

MySqlSlap

在mysql5.1.4以后的版本官方就提供了壓力測(cè)試工具。

• 創(chuàng)建schema，table，testdata。

• 運(yùn)行負(fù)載測(cè)試，可以使用多個(gè)并發(fā)客戶端連接。

• 測(cè)試環(huán)境的清理(刪除創(chuàng)建的數(shù)據(jù)，表格，斷開連接)

想要看詳細(xì)的信息可以 man mysqlslap 或者使用 mysqlslap –help 查看

指令	含義
–concurrency	并發(fā)數(shù)量,多個(gè)可以用逗號(hào)隔開
–engines	要測(cè)試的引擎，可以有多個(gè)，用分隔符隔開，如engines=myisam,innodb
–iterations	要運(yùn)行這些測(cè)試多少次
–auto-generate-sql	用系統(tǒng)自己生成的SQL腳本來(lái)測(cè)試
–auto-generate-sql-load-type	要測(cè)試的是讀還是寫還是兩者混合的（read,write,update,mixed）
–number-of-queries	總共要運(yùn)行多少次查詢。每個(gè)客戶運(yùn)行的查詢數(shù)量可以用查詢總數(shù)/并發(fā)數(shù)來(lái)計(jì)算
–debug-info	額外輸出CPU以及內(nèi)存的相關(guān)信息
–number-int-cols	創(chuàng)建測(cè)試表的int型字段數(shù)量
–number-char-cols	創(chuàng)建測(cè)試表的chat型字段數(shù)量
–create-schema	測(cè)試的database
–query 自己的SQL	腳本執(zhí)行測(cè)試
–only-print	如果只想打印看看SQL語(yǔ)句是什么，可以用這個(gè)選項(xiàng)

幾個(gè)demo如下：

1. mysqlslap -umysql -p123 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam --number-of-queries=10 --debug-info

2. mysqlslap -h292.168.3.18 -P4040 --concurrency=100 --iterations=1 --create-schema=‘test’ --query=‘select * from test;’ --number-of-queries=10 --debug-info -umysql -p123

3. mysqlslap -uroot -p123456 --concurrency=100 --iterations=1 --engine=myisam --create-schema=‘haodingdan112’ --query=‘select * From order_boxing_transit where id = 10’ --number-of-queries=1 --debug-info

MySQL 架構(gòu)圖

連接層1

當(dāng)MySQL啟動(dòng)(mysql服務(wù)器就是一個(gè)進(jìn)程)，等待客戶端鏈接，每一個(gè)客戶端的鏈接請(qǐng)求。 每一個(gè)客戶端連接請(qǐng)求服務(wù)器都會(huì)新建一個(gè)線程進(jìn)行處理(如果服務(wù)器端是線程池，則由線程池來(lái)分配一個(gè)空閑線程)，每哥線程都是獨(dú)立的，擁有自己獨(dú)立的內(nèi)存空間，如果這個(gè)請(qǐng)求是查詢，沒(méi)關(guān)系。但是若是修改則兩個(gè)線程修改同一個(gè)內(nèi)存會(huì)引發(fā)數(shù)據(jù)同步問(wèn)題，需要引入鎖。

連接層2

客戶連接到服務(wù)器，服務(wù)器也要對(duì)客戶進(jìn)行驗(yàn)證，也就是用戶名，IP，密碼，來(lái)確定是否可以連接，連接后還要確定是否有執(zhí)行某個(gè)特殊查詢等權(quán)限，比如讀寫或指定的table。

引擎層

這一層重要功能是：SQL語(yǔ)句的解析，優(yōu)化，緩存(緩存查詢過(guò)的數(shù)據(jù)，緩存執(zhí)行過(guò)的SQL)的查詢。
MySQL的內(nèi)置函數(shù)實(shí)現(xiàn)，跨存儲(chǔ)引擎功能(所謂的跨存儲(chǔ)引擎就是每個(gè)引擎都需要提供的功能(引擎需要對(duì)外提供結(jié)構(gòu)))。比如存儲(chǔ)過(guò)程，觸發(fā)器，視圖等。

1. 如果是查詢語(yǔ)句比如select，會(huì)先查詢緩存是否已經(jīng)有對(duì)應(yīng)結(jié)果，有則返回，沒(méi)有則進(jìn)行下一步查詢。

2. 解析查詢，創(chuàng)建一個(gè)內(nèi)部數(shù)據(jù)結(jié)構(gòu)形式等解析樹，解析樹主要用來(lái)SQL語(yǔ)句等語(yǔ)義跟語(yǔ)法縫隙。

3. 優(yōu)化：優(yōu)化SQL語(yǔ)句，例如重寫查詢，決定表的讀取順序，以及選擇需要的索引等，用戶可以進(jìn)行查詢此階段，看到服務(wù)器如何進(jìn)行優(yōu)化的，還會(huì)涉及到存儲(chǔ)引擎，比如某個(gè)操作開銷信息，是否對(duì)特定索引有查詢優(yōu)化。

查詢是否開啟緩存

show variables like '%query_cache_type%'

設(shè)定緩存大小

set Global query_cache_size = 4000;

查看數(shù)據(jù)保存目錄

show variables like '%datadir%'

解析查詢

存儲(chǔ)層

將數(shù)據(jù)存儲(chǔ)于裸設(shè)備的文件系統(tǒng)之上，完成與存儲(chǔ)引擎的交互。

MySQL形象圖

存儲(chǔ)引擎

查詢mysql以提供對(duì)存儲(chǔ)引擎；

show engines

查看你對(duì)mysql默認(rèn)存儲(chǔ)引擎

show variables like '%storage_engine%'

重點(diǎn)MyISAM

MySQL5.5之前對(duì)默認(rèn)存儲(chǔ)引擎，MyISAM存儲(chǔ)引擎由

table.MYD: 存儲(chǔ)數(shù)據(jù)
talbe.MYI:存儲(chǔ)索引
talbe.frm: 存儲(chǔ)表格式(InnoDB也有)

特性：

1. 并發(fā)性與鎖級(jí)別-表級(jí)鎖，不支持事務(wù)，不適合頻繁修改。

2. 支持全文檢索

3. 支持?jǐn)?shù)據(jù)壓縮 myisampack -b -f table.MYI
   使用場(chǎng)景：

• 非事務(wù)型應(yīng)用(數(shù)據(jù)倉(cāng)庫(kù)，報(bào)表，日志數(shù)據(jù))

• 只讀類應(yīng)用

• 空間類應(yīng)用(自帶空間函數(shù)跟坐標(biāo))

重點(diǎn)InnoDB

MySQL5.5以后版本默認(rèn)存儲(chǔ)引擎
存儲(chǔ)對(duì)適合有innodb_file_per_table 屬性

ON：獨(dú)立對(duì)表空間(table.frm，table.ibd)
OFF: 系統(tǒng)表空間(ibdataX)

MySQL5.6以前默認(rèn)為系統(tǒng)表空間，5.6以后建議跟默認(rèn)使用獨(dú)立表空間

• 系統(tǒng)表空間無(wú)法簡(jiǎn)單收縮文件大小

• 獨(dú)立表空間可以通過(guò) optimize table 收縮系統(tǒng)文件

• 系統(tǒng)表空間存儲(chǔ)所有數(shù)據(jù)，會(huì)產(chǎn)生IO瓶頸

• 獨(dú)立表空間可以同時(shí)向多個(gè)文件刷新數(shù)據(jù)。

特性：

InnoDB 是一種事務(wù)性存儲(chǔ)引擎
完全支持事務(wù)的ACID特性
RedoLog和Undo Log
InnoDB支持行級(jí)鎖(并發(fā)成都更高)

使用場(chǎng)景：

InnoDB適合大多數(shù)的OLTP應(yīng)用(On-Line Transaction Processing聯(lián)機(jī)事務(wù)處理過(guò)程(OLTP)，也稱為面向交易的處理過(guò)程)

存儲(chǔ)引擎對(duì)比

CSV

特點(diǎn)：

1. 以csv格式進(jìn)行數(shù)據(jù)存儲(chǔ)

2. 所有列都不嫩為null

3. 不支持索引(不適合大表，不適合在線處理)

4. 可以直接手動(dòng)對(duì)csv文件進(jìn)行編輯(編輯適合記得回車，最后flush tables)

場(chǎng)景：

一般財(cái)務(wù)等人用

Archive

組成： 以zlib對(duì)表數(shù)據(jù)進(jìn)行壓縮，磁盤IO更小，
特點(diǎn)：

1. 只支持insert 跟select 語(yǔ)句

2. 只允許在自增ID上建立索引

使用場(chǎng)景：

日志和數(shù)據(jù)的采集應(yīng)用

Memory

文件系統(tǒng)存儲(chǔ)特點(diǎn)，也成HEAP存儲(chǔ)引擎，數(shù)據(jù)保存內(nèi)存中，斷電則沒(méi)。
支持HASH索引跟BTree索引
所有字段都是孤獨(dú)長(zhǎng)度varchar(10) = char(10)
不支持Blog跟Text等大字段
Memory存儲(chǔ)引擎使用表級(jí)索引
最大值由max_heap_table_size參數(shù)決定。

內(nèi)存表(Memory)跟臨時(shí)表(Memory)區(qū)別

內(nèi)存表，就是放在內(nèi)存中的表，所使用內(nèi)存的大小可通過(guò)My.cnf中的max_heap_table_size指定，如max_heap_table_size=1024M，內(nèi)存表與臨時(shí)表并不相同，臨時(shí)表也是存放在內(nèi)存中，臨時(shí)表最大所需內(nèi)存需要通過(guò)tmp_table_size =128M設(shè)定。當(dāng)數(shù)據(jù)超過(guò)臨時(shí)表的最大值設(shè)定時(shí)，自動(dòng)轉(zhuǎn)為磁盤表，此時(shí)因需要進(jìn)行IO操作，性能會(huì)大大下降，而內(nèi)存表不會(huì)，內(nèi)存表滿后，會(huì)提示數(shù)據(jù)滿錯(cuò)誤
臨時(shí)表和內(nèi)存表都可以人工創(chuàng)建，但臨時(shí)表更多的作用是系統(tǒng)自己創(chuàng)建后，組織數(shù)據(jù)以提升性能，如子查詢，臨時(shí)表在多個(gè)連接之間不能共享。

Ferderated

默認(rèn)不開啟，需手動(dòng)配置開啟。
特點(diǎn)：

1. 提供遠(yuǎn)程訪問(wèn)MySQL服務(wù)器上表大方法

2. 本地不存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)都在遠(yuǎn)程服務(wù)器

3. 本地需要保存表結(jié)構(gòu)和遠(yuǎn)程服務(wù)器連接信息

場(chǎng)景：

偶爾大統(tǒng)計(jì)分析及手工查詢

鎖

多線程對(duì)同一對(duì)象進(jìn)行操作需加鎖。

1. 鎖鎖計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一個(gè)資源對(duì)機(jī)制

2. 在數(shù)據(jù)庫(kù)中，數(shù)據(jù)也是一種提供給許多用戶的共享資源，如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性跟有效性是比解決問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素。

3. 鎖對(duì)數(shù)據(jù)庫(kù)而言重要且復(fù)雜。

mysql中 鎖類型

1. 表級(jí)鎖:開銷小，加鎖快;不會(huì)出現(xiàn)死鎖;鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。多查詢少修改應(yīng)用

2. 行級(jí)鎖:開銷大，加鎖慢;會(huì)出現(xiàn)死鎖;鎖定粒度最小,發(fā)生鎖沖突的概率最低，并發(fā)度也最高。支持并發(fā)的查詢跟更新。

3. 頁(yè)面鎖:開銷和加鎖時(shí)間界于表鎖和行鎖之間;會(huì)出現(xiàn)死鎖;鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

MySQL鎖機(jī)制簡(jiǎn)單，不同引擎默認(rèn)支持不同鎖級(jí)別，MyISAM支持表級(jí)鎖(table-level locking)，InnoDB同時(shí)支持行級(jí)鎖(row-level locking)跟表級(jí)鎖，默認(rèn)行級(jí)鎖。

MySQL 的表級(jí)鎖有兩種模式:

表共享讀鎖(Table Read Lock)
表獨(dú)占寫鎖(Table Write Lock)

在mysql中加鎖是后臺(tái)自動(dòng)優(yōu)化進(jìn)行添加的，也可以人為的手動(dòng)添加了解mysql底層特性。

MyISAM 鎖

MyISAM共享讀鎖及獨(dú)占寫鎖總結(jié)：

1. 對(duì)MyISAM表進(jìn)行讀對(duì)時(shí)候，不會(huì)阻塞其他用戶對(duì)同一表對(duì)讀操作，但是會(huì)阻塞對(duì)同一個(gè)表對(duì)寫操作。

2. 對(duì)MyISAM表對(duì)讀操作，不會(huì)阻塞當(dāng)前用戶讀表，當(dāng)對(duì)表進(jìn)行修改也會(huì)保存。

3. 一個(gè)session 使用LOCK table 給表加讀鎖，這個(gè)session可以進(jìn)行增刪改查，當(dāng)時(shí)當(dāng)前session不可再訪問(wèn)或更新其他表。

4. 另外一個(gè)session可以查詢表對(duì)記錄，但是更新對(duì)時(shí)候就要等待。

5. 對(duì)MyISAM表的寫操作，會(huì)阻塞其他用戶對(duì)同一表對(duì)讀寫操作。

6. 對(duì)MyISAM表對(duì)寫操作，當(dāng)前session可以對(duì)表進(jìn)行CRUD，但對(duì)其他表操作時(shí)候會(huì)報(bào)錯(cuò)。

讀demo:

1. lock table testmysam READ
   啟動(dòng)另外一個(gè)session select * from testmysam 可以查詢

2. insert into testmysam value(2); update testmysam set id=2 where id=1; 報(bào)錯(cuò)
   3.在另外一個(gè)session中 insert into testmysam value(2); 等待
   4.在同一個(gè)session中 insert into testdemo value(2,‘2’,‘3’); 報(bào)錯(cuò) select * from testdemo ; 報(bào)錯(cuò)
   5.在另外一個(gè)session中 insert into testdemo value(2,‘2’,‘3’); 成功
   6.加鎖在同一個(gè)session 中 select s.* from testmysam s 報(bào)錯(cuò)
   lock table 表名 as 別名 read;
   查看 show status LIKE ‘table_locks_waited’ 表被鎖過(guò)幾次
   寫demo:
   1.lock table testmysam WRITE 在同一個(gè)session中 insert testmysam value(3) (OK) ; delete from testmysam where id = 3 (OK) ; select * from testmysam (NO)
   2.對(duì)不同的表操作（報(bào)錯(cuò)） select s.* from testmysam s； insert into testdemo value(2,‘2’,‘3’);
   3.在其他session中 （等待） select * from testmysam

InnoDB 鎖

在MySQL的InnoDB引擎支持行鎖。

1. 共享鎖(讀鎖)，當(dāng)一個(gè)事務(wù)對(duì)某幾行上讀鎖時(shí)，允許其他事務(wù)對(duì)這幾行進(jìn)行讀操作，當(dāng)時(shí)不允許對(duì)這幾行進(jìn)行寫操作，也不允許其他事務(wù)給這幾行上排它鎖，但可以上讀鎖。

2. 排它鎖(寫鎖)，當(dāng)一個(gè)事務(wù)對(duì)幾行上寫鎖時(shí)，不允許其他事務(wù)寫，但允許讀。更不允許其他事務(wù)給這幾行上任何鎖，包括寫鎖。

語(yǔ)法：

上共享鎖寫法：lock in share mode

select * from 表 where 條件 lock in share mode;
上排它鎖寫法：for update
select * from 表 where 條件 for update;

InnoDB行鎖

1. 兩個(gè)事務(wù)不能鎖同一個(gè)索引

2. insert，delete，update在事務(wù)中自動(dòng)加上排它鎖。

3. 行鎖必須有索引才可實(shí)現(xiàn)，否則自動(dòng)鎖全表，那就不是行鎖

demo：

1. BEGIN select * from testdemo where id =1 for update
   在另外一個(gè)session中 update testdemo set c1 = ‘1’ where id = 2 成功 update testdemo set c1 = ‘1’ where id = 1 等待

2. BEGIN update testdemo set c1 = ‘1’ where id = 1 在另外一個(gè)session中 update testdemo set c1 = ‘1’ where id = 1 等待

3. BEGIN update testdemo set c1 = ‘1’ where c1 = ‘1’ 在另外一個(gè)session中 update testdemo set c1 = ‘2’ where c1 = ‘2’ 等待 c1 沒(méi)有索引。

InnoDB表鎖 跟MyISAM差別不大，但是開啟一個(gè)新事務(wù)時(shí)候會(huì)解鎖表。

延伸：系統(tǒng)允許一段時(shí)間，數(shù)據(jù)量大，系統(tǒng)升級(jí)，A表要添加字段，白天晚上并發(fā)量都大，如何修改表結(jié)構(gòu)。

1. 創(chuàng)建一個(gè)跟A表一樣但信息是空的A1

2. 修改A1的數(shù)據(jù)，然后A數(shù)據(jù)copy 到A1里面

3. 在A表中創(chuàng)建一個(gè)觸發(fā)器，將A表中新增數(shù)據(jù)自動(dòng)全部更新到表中來(lái)。

4. copy 完畢后自動(dòng)rename即可。

上述步驟自動(dòng)化實(shí)現(xiàn)可用 工具pt-online-schema-change

事務(wù)

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說(shuō)，在人員管理系統(tǒng)中，你刪除一個(gè)人員，你既需要?jiǎng)h除人員的基本資料，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫(kù)操作語(yǔ)句就構(gòu)成一個(gè)事務(wù)！
　隱式事務(wù)：事務(wù)沒(méi)有明顯的開啟或者結(jié)束的標(biāo)志，在mysql中，默認(rèn)是開啟自動(dòng)提交的。
查看數(shù)據(jù)庫(kù)下面是否支持事務(wù)

show engines

查看mysql當(dāng)前默認(rèn)存儲(chǔ)引擎

show variables like ‘%storage_engine%’

查看某個(gè)表的存儲(chǔ)引擎

show create table 表名

修改表存儲(chǔ)結(jié)構(gòu)

create table () type=InnoDB;
Alter table 表 type=InnoDB;

一般來(lái)說(shuō)，事務(wù)是必須滿足4個(gè)條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨(dú)立性）、持久性（Durability）。

1. 原子性：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來(lái)沒(méi)有執(zhí)行過(guò)一樣。

2. 一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，。這表示寫入的資料必須完數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫(kù)可以自發(fā)性地完成預(yù)定的工作。

3. 隔離性：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。

4. 持久性：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的的，即便系統(tǒng)修改就是永久故障也不會(huì)丟失。

重點(diǎn)：隔離性
在mysql下事務(wù)的隔離級(jí)別有四種且由低到高依次為Read uncommitted 、Read committed 、Repeatable read (默認(rèn))、Serializable ，這四個(gè)級(jí)別中的后三個(gè)級(jí)別可以逐個(gè)解決臟讀 、不可重復(fù)讀 、幻讀這幾類問(wèn)題

1. 臟讀： 事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾了，那么A讀取到到數(shù)據(jù)是臟數(shù)據(jù)。

2. 不可重復(fù)讀，事務(wù)A多次讀取同一個(gè)數(shù)據(jù)，事務(wù)B在事務(wù)A多次讀取讀過(guò)程中，對(duì)數(shù)據(jù)做了更新跟提交，導(dǎo)致事務(wù)A多次讀取同一個(gè)數(shù)據(jù)時(shí)結(jié)果不一致。側(cè)重修改。只要鎖住滿足條件等行即可。

3. 幻讀：管理員小王將數(shù)據(jù)庫(kù)中學(xué)生成績(jī)按照分?jǐn)?shù)劃分為ABCDE級(jí)別，但是有人在執(zhí)行時(shí)候插入了一條具體分?jǐn)?shù)，導(dǎo)致A修改后發(fā)現(xiàn)一條記錄沒(méi)有修改完畢，像發(fā)生幻覺(jué)一樣。側(cè)重與新增或刪除，要鎖表！

1. read uncommitted 未提交讀
   所有事務(wù)都可以看到?jīng)]有提交事務(wù)的數(shù)據(jù)。性能最好，事務(wù)性幾乎么有

2. read committed 提交讀
   可能會(huì)出現(xiàn)多次讀取不一樣哦！

3. repeatable 重復(fù)讀
   同一個(gè)事務(wù)多個(gè)實(shí)例讀取數(shù)據(jù)時(shí)，必須等其他操作目前數(shù)據(jù)等事務(wù)完畢才可以進(jìn)行，mysql默認(rèn)級(jí)別。

4. Serializable可串行化
   強(qiáng)制的進(jìn)行排序，在每個(gè)讀讀數(shù)據(jù)行上添加共享鎖。會(huì)導(dǎo)致大量超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)

至于如何避免看 上圖表格即可。不同的事務(wù)級(jí)別可以對(duì)于解決不同的問(wèn)題。
事務(wù)用法：
1、用 BEGIN, ROLLBACK, COMMIT來(lái)實(shí)現(xiàn)

BEGIN 開始一個(gè)事務(wù)
ROLLBACK 事務(wù)回滾
COMMIT 事務(wù)確認(rèn)

2、直接用 SET 來(lái)改變 MySQL 的自動(dòng)提交模式:

SET AUTOCOMMIT=0 禁止自動(dòng)提交
SET AUTOCOMMIT=1 開啟自動(dòng)提交 (系統(tǒng)默認(rèn))

業(yè)務(wù)設(shè)計(jì)

邏輯設(shè)計(jì)

范式設(shè)計(jì)

1. 第一范式

第一范式就是屬性不可分割，每個(gè)字段都應(yīng)該是不可再拆分的

原子性。比如一個(gè)字段是姓名（NAME），在國(guó)內(nèi)的話通常理解都是姓名是一個(gè)不可再拆分的單位，這時(shí)候就符合第一范式；但是在國(guó)外的話還要分為FIRST NAME和LAST NAME，這時(shí)候姓名這個(gè)字段就是還可以拆分為更小的單位的字段，就不符合第一范式了。

2. 第二大范式

表中要有主鍵，表中其他其他字段都依賴于主鍵

第二范式只要記住就好了。主鍵約束比如說(shuō)有一個(gè)表是學(xué)生表，學(xué)生表中有一個(gè)值唯一的字段學(xué)號(hào)，那么學(xué)生表中的其他所有字段都可以根據(jù)這個(gè)學(xué)號(hào)字段去獲取，依賴主鍵的意思也就是相關(guān)的意思，因?yàn)閷W(xué)號(hào)的值是唯一的，因此就不會(huì)造成存儲(chǔ)的信息對(duì)不上的問(wèn)題，即學(xué)生001的姓名不會(huì)存到學(xué)生002那里去。

3. 第三范式

第三范式就是要求表中不能有其他表中存在的、存儲(chǔ)相同信息的字段，

通常實(shí)現(xiàn)是在通過(guò)外鍵去建立關(guān)聯(lián)，因此第三范式只要記住外鍵約束就好了。比如說(shuō)有一個(gè)表是學(xué)生表，學(xué)生表中有學(xué)號(hào)，姓名等字段，那如果要把他的系編號(hào)，系主任，系主任也存到這個(gè)學(xué)生表中，那就會(huì)造成數(shù)據(jù)大量的冗余，一是這些信息在系信息表中已存在，二是系中有1000個(gè)學(xué)生的話這些信息就要存1000遍。因此第三范式的做法是在學(xué)生表中增加一個(gè)系編號(hào)的字段（外鍵），與系信息表做關(guān)聯(lián)

范式化設(shè)計(jì)優(yōu)缺點(diǎn)

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

可以盡量得減少數(shù)據(jù)冗余
范式化的更新操作比反范式化更快
范式化的表通常比反范式化的表更小

缺點(diǎn)：

對(duì)于查詢需要對(duì)多個(gè)表進(jìn)行關(guān)聯(lián)
更難進(jìn)行索引優(yōu)化

反范式設(shè)計(jì)

反范式化是針對(duì)范式化而言得，在前面介紹了數(shù)據(jù)庫(kù)設(shè)計(jì)得范式

所謂得反范式化就是為了性能和讀取效率得考慮而適當(dāng)?shù)脤?duì)數(shù)據(jù)庫(kù)設(shè)計(jì)范式得要求進(jìn)行違反
允許存在少量得冗余，換句話來(lái)說(shuō)反范式化就是使用空間來(lái)?yè)Q取時(shí)間

反范式化設(shè)計(jì)優(yōu)缺點(diǎn)

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

可以減少表的關(guān)聯(lián)
可以更好的進(jìn)行索引優(yōu)化

缺點(diǎn)：

存在數(shù)據(jù)冗余及數(shù)據(jù)維護(hù)異常
對(duì)數(shù)據(jù)的修改需要更多的成本

物理設(shè)計(jì)

根據(jù)所選擇的關(guān)系型數(shù)據(jù)庫(kù)的特點(diǎn)對(duì)邏輯模型進(jìn)行存儲(chǔ)結(jié)構(gòu)的設(shè)計(jì)

命名規(guī)范

• 數(shù)據(jù)庫(kù)、表、字段的命名要遵守可讀性原則
  使用大小寫來(lái)格式化的庫(kù)對(duì)象名字以獲得良好的可讀性
  例如：使用custAddress而不是custaddress來(lái)提高可讀性。

• 數(shù)據(jù)庫(kù)、表、字段的命名要遵守表意性原則
  對(duì)象的名字應(yīng)該能夠描述它所表示的對(duì)象
  例如：對(duì)于表的名稱應(yīng)該能夠體現(xiàn)表中存儲(chǔ)的數(shù)據(jù)內(nèi)容；對(duì)于存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程應(yīng)該能夠體現(xiàn)存儲(chǔ)過(guò)程的功能。

• 數(shù)據(jù)庫(kù)、表、字段的命名要遵守長(zhǎng)名原則
  盡可能少使用或者不使用縮寫

存儲(chǔ)引擎規(guī)范

根據(jù)業(yè)務(wù)規(guī)則選擇合適的存儲(chǔ)引擎，不同引擎的利弊也在面有講，工作中一般也就上InnoDB或者M(jìn)yISAM。

數(shù)據(jù)類型規(guī)范

為表中字段選擇合適的字節(jié)類型，當(dāng)一個(gè)列可以選擇多種數(shù)據(jù)類型時(shí)

優(yōu)先考慮數(shù)字類型
其次是日期、時(shí)間類型
最后是字符類型
對(duì)于相同級(jí)別的數(shù)據(jù)類型，應(yīng)該優(yōu)先選擇占用空間小的數(shù)據(jù)類型

重點(diǎn)：

1. 對(duì)于精度比較高的東西，比如money，用decimal類型，不會(huì)考慮float,double,因?yàn)樗麄內(nèi)菀桩a(chǎn)生誤差。

2. datetime 和 timestamp 的區(qū)別與選擇

timestamp 只占 4 個(gè)字節(jié),它會(huì)自動(dòng)檢索當(dāng)前時(shí)區(qū)并進(jìn)行轉(zhuǎn)換，不可存NULL。
datetime以 8 個(gè)字節(jié)儲(chǔ)存，不會(huì)進(jìn)行時(shí)區(qū)的檢索，可存NULL

感謝各位的閱讀，以上就是“MySQL架構(gòu)與存儲(chǔ)引擎，鎖，事務(wù)，設(shè)計(jì)分析”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySQL架構(gòu)與存儲(chǔ)引擎，鎖，事務(wù)，設(shè)計(jì)分析這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

分享標(biāo)題：MySQL架構(gòu)與存儲(chǔ)引擎，鎖，事務(wù)，設(shè)計(jì)分析
標(biāo)題來(lái)源：http://jinyejixie.com/article32/ggispc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)建站、品牌網(wǎng)站設(shè)計(jì)、網(wǎng)頁(yè)設(shè)計(jì)公司、虛擬主機(jī)、面包屑導(dǎo)航、網(wǎng)站設(shè)計(jì)公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)