MySQL架構與存儲引擎，鎖，事務，設計分析

這篇文章主要講解了“MySQL架構與存儲引擎，鎖，事務，設計分析”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL架構與存儲引擎，鎖，事務，設計分析”吧！

衡量指標

TPS

Transactions Per Second(每秒傳輸到事務處理個數)，這里是指服務器每秒處理事務數，支持事務的存儲引擎如InnoDB等特有等一個性能指標。單位時間內事務執行成功跟事務回滾數
TPS= (COM_COMMIT + COM_ROLLBACK)/UPTIME

QPS

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

順序

MySqlSlap

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

• 創建schema，table，testdata。

• 運行負載測試，可以使用多個并發客戶端連接。

• 測試環境的清理(刪除創建的數據，表格，斷開連接)

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

幾個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 架構圖

連接層1

當MySQL啟動(mysql服務器就是一個進程)，等待客戶端鏈接，每一個客戶端的鏈接請求。 每一個客戶端連接請求服務器都會新建一個線程進行處理(如果服務器端是線程池，則由線程池來分配一個空閑線程)，每哥線程都是獨立的，擁有自己獨立的內存空間，如果這個請求是查詢，沒關系。但是若是修改則兩個線程修改同一個內存會引發數據同步問題，需要引入鎖。

連接層2

客戶連接到服務器，服務器也要對客戶進行驗證，也就是用戶名，IP，密碼，來確定是否可以連接，連接后還要確定是否有執行某個特殊查詢等權限，比如讀寫或指定的table。

引擎層

這一層重要功能是：SQL語句的解析，優化，緩存(緩存查詢過的數據，緩存執行過的SQL)的查詢。
MySQL的內置函數實現，跨存儲引擎功能(所謂的跨存儲引擎就是每個引擎都需要提供的功能(引擎需要對外提供結構))。比如存儲過程，觸發器，視圖等。

1. 如果是查詢語句比如select，會先查詢緩存是否已經有對應結果，有則返回，沒有則進行下一步查詢。

2. 解析查詢，創建一個內部數據結構形式等解析樹，解析樹主要用來SQL語句等語義跟語法縫隙。

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

查詢是否開啟緩存

show variables like '%query_cache_type%'

設定緩存大小

set Global query_cache_size = 4000;

查看數據保存目錄

show variables like '%datadir%'

解析查詢

存儲層

將數據存儲于裸設備的文件系統之上，完成與存儲引擎的交互。

MySQL形象圖

存儲引擎

查詢mysql以提供對存儲引擎；

show engines

查看你對mysql默認存儲引擎

show variables like '%storage_engine%'

重點MyISAM

MySQL5.5之前對默認存儲引擎，MyISAM存儲引擎由

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

特性：

1. 并發性與鎖級別-表級鎖，不支持事務，不適合頻繁修改。

2. 支持全文檢索

3. 支持數據壓縮 myisampack -b -f table.MYI
   使用場景：

• 非事務型應用(數據倉庫，報表，日志數據)

• 只讀類應用

• 空間類應用(自帶空間函數跟坐標)

重點InnoDB

MySQL5.5以后版本默認存儲引擎
存儲對適合有innodb_file_per_table 屬性

ON：獨立對表空間(table.frm，table.ibd)
OFF: 系統表空間(ibdataX)

MySQL5.6以前默認為系統表空間，5.6以后建議跟默認使用獨立表空間

• 系統表空間無法簡單收縮文件大小

• 獨立表空間可以通過 optimize table 收縮系統文件

• 系統表空間存儲所有數據，會產生IO瓶頸

• 獨立表空間可以同時向多個文件刷新數據。

特性：

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

使用場景：

InnoDB適合大多數的OLTP應用(On-Line Transaction Processing聯機事務處理過程(OLTP)，也稱為面向交易的處理過程)

存儲引擎對比

CSV

特點：

1. 以csv格式進行數據存儲

2. 所有列都不嫩為null

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

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

場景：

一般財務等人用

Archive

組成： 以zlib對表數據進行壓縮，磁盤IO更小，
特點：

1. 只支持insert 跟select 語句

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

使用場景：

日志和數據的采集應用

Memory

文件系統存儲特點，也成HEAP存儲引擎，數據保存內存中，斷電則沒。
支持HASH索引跟BTree索引
所有字段都是孤獨長度varchar(10) = char(10)
不支持Blog跟Text等大字段
Memory存儲引擎使用表級索引
最大值由max_heap_table_size參數決定。

內存表(Memory)跟臨時表(Memory)區別

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

Ferderated

默認不開啟，需手動配置開啟。
特點：

1. 提供遠程訪問MySQL服務器上表大方法

2. 本地不存儲數據，數據都在遠程服務器

3. 本地需要保存表結構和遠程服務器連接信息

場景：

偶爾大統計分析及手工查詢

鎖

多線程對同一對象進行操作需加鎖。

1. 鎖鎖計算機協調多個進程或線程并發訪問某一個資源對機制

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

3. 鎖對數據庫而言重要且復雜。

mysql中 鎖類型

1. 表級鎖:開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖沖突的概率最高，并發度最低。多查詢少修改應用

2. 行級鎖:開銷大，加鎖慢;會出現死鎖;鎖定粒度最小,發生鎖沖突的概率最低，并發度也最高。支持并發的查詢跟更新。

3. 頁面鎖:開銷和加鎖時間界于表鎖和行鎖之間;會出現死鎖;鎖定粒度界于表鎖和行鎖之間，并發度一般。

MySQL鎖機制簡單，不同引擎默認支持不同鎖級別，MyISAM支持表級鎖(table-level locking)，InnoDB同時支持行級鎖(row-level locking)跟表級鎖，默認行級鎖。

MySQL 的表級鎖有兩種模式:

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

在mysql中加鎖是后臺自動優化進行添加的，也可以人為的手動添加了解mysql底層特性。

MyISAM 鎖

MyISAM共享讀鎖及獨占寫鎖總結：

1. 對MyISAM表進行讀對時候，不會阻塞其他用戶對同一表對讀操作，但是會阻塞對同一個表對寫操作。

2. 對MyISAM表對讀操作，不會阻塞當前用戶讀表，當對表進行修改也會保存。

3. 一個session 使用LOCK table 給表加讀鎖，這個session可以進行增刪改查，當時當前session不可再訪問或更新其他表。

4. 另外一個session可以查詢表對記錄，但是更新對時候就要等待。

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

6. 對MyISAM表對寫操作，當前session可以對表進行CRUD，但對其他表操作時候會報錯。

讀demo:

1. lock table testmysam READ
   啟動另外一個session select * from testmysam 可以查詢

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

InnoDB 鎖

在MySQL的InnoDB引擎支持行鎖。

1. 共享鎖(讀鎖)，當一個事務對某幾行上讀鎖時，允許其他事務對這幾行進行讀操作，當時不允許對這幾行進行寫操作，也不允許其他事務給這幾行上排它鎖，但可以上讀鎖。

2. 排它鎖(寫鎖)，當一個事務對幾行上寫鎖時，不允許其他事務寫，但允許讀。更不允許其他事務給這幾行上任何鎖，包括寫鎖。

語法：

上共享鎖寫法：lock in share mode

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

InnoDB行鎖

1. 兩個事務不能鎖同一個索引

2. insert，delete，update在事務中自動加上排它鎖。

3. 行鎖必須有索引才可實現，否則自動鎖全表，那就不是行鎖

demo：

1. BEGIN select * from testdemo where id =1 for update
   在另外一個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 在另外一個session中 update testdemo set c1 = ‘1’ where id = 1 等待

3. BEGIN update testdemo set c1 = ‘1’ where c1 = ‘1’ 在另外一個session中 update testdemo set c1 = ‘2’ where c1 = ‘2’ 等待 c1 沒有索引。

InnoDB表鎖 跟MyISAM差別不大，但是開啟一個新事務時候會解鎖表。

延伸：系統允許一段時間，數據量大，系統升級，A表要添加字段，白天晚上并發量都大，如何修改表結構。

1. 創建一個跟A表一樣但信息是空的A1

2. 修改A1的數據，然后A數據copy 到A1里面

3. 在A表中創建一個觸發器，將A表中新增數據自動全部更新到表中來。

4. copy 完畢后自動rename即可。

上述步驟自動化實現可用 工具pt-online-schema-change

事務

MySQL 事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統中，你刪除一個人員，你既需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些數據庫操作語句就構成一個事務！
　隱式事務：事務沒有明顯的開啟或者結束的標志，在mysql中，默認是開啟自動提交的。
查看數據庫下面是否支持事務

show engines

查看mysql當前默認存儲引擎

show variables like ‘%storage_engine%’

查看某個表的存儲引擎

show create table 表名

修改表存儲結構

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

一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

1. 原子性：一個事務（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。

2. 一致性：在事務開始之前和事務結束以后，。這表示寫入的資料必須完數據庫的完整性沒有被破壞全符合所有的預設規則，這包含資料的精確度、串聯性以及后續數據庫可以自發性地完成預定的工作。

3. 隔離性：數據庫允許多個并發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（Serializable）。

4. 持久性：事務處理結束后，對數據的的，即便系統修改就是永久故障也不會丟失。

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

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

2. 不可重復讀，事務A多次讀取同一個數據，事務B在事務A多次讀取讀過程中，對數據做了更新跟提交，導致事務A多次讀取同一個數據時結果不一致。側重修改。只要鎖住滿足條件等行即可。

3. 幻讀：管理員小王將數據庫中學生成績按照分數劃分為ABCDE級別，但是有人在執行時候插入了一條具體分數，導致A修改后發現一條記錄沒有修改完畢，像發生幻覺一樣。側重與新增或刪除，要鎖表！

1. read uncommitted 未提交讀
   所有事務都可以看到沒有提交事務的數據。性能最好，事務性幾乎么有

2. read committed 提交讀
   可能會出現多次讀取不一樣哦！

3. repeatable 重復讀
   同一個事務多個實例讀取數據時，必須等其他操作目前數據等事務完畢才可以進行，mysql默認級別。

4. Serializable可串行化
   強制的進行排序，在每個讀讀數據行上添加共享鎖。會導致大量超時現象和鎖競爭

至于如何避免看 上圖表格即可。不同的事務級別可以對于解決不同的問題。
事務用法：
1、用 BEGIN, ROLLBACK, COMMIT來實現

BEGIN 開始一個事務
ROLLBACK 事務回滾
COMMIT 事務確認

2、直接用 SET 來改變 MySQL 的自動提交模式:

SET AUTOCOMMIT=0 禁止自動提交
SET AUTOCOMMIT=1 開啟自動提交 (系統默認)

業務設計

邏輯設計

范式設計

1. 第一范式

第一范式就是屬性不可分割，每個字段都應該是不可再拆分的

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

2. 第二大范式

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

第二范式只要記住就好了。主鍵約束比如說有一個表是學生表，學生表中有一個值唯一的字段學號，那么學生表中的其他所有字段都可以根據這個學號字段去獲取，依賴主鍵的意思也就是相關的意思，因為學號的值是唯一的，因此就不會造成存儲的信息對不上的問題，即學生001的姓名不會存到學生002那里去。

3. 第三范式

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

通常實現是在通過外鍵去建立關聯，因此第三范式只要記住外鍵約束就好了。比如說有一個表是學生表，學生表中有學號，姓名等字段，那如果要把他的系編號，系主任，系主任也存到這個學生表中，那就會造成數據大量的冗余，一是這些信息在系信息表中已存在，二是系中有1000個學生的話這些信息就要存1000遍。因此第三范式的做法是在學生表中增加一個系編號的字段（外鍵），與系信息表做關聯

范式化設計優缺點

優點：

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

缺點：

對于查詢需要對多個表進行關聯
更難進行索引優化

反范式設計

反范式化是針對范式化而言得，在前面介紹了數據庫設計得范式

所謂得反范式化就是為了性能和讀取效率得考慮而適當得對數據庫設計范式得要求進行違反
允許存在少量得冗余，換句話來說反范式化就是使用空間來換取時間

反范式化設計優缺點

優點：

可以減少表的關聯
可以更好的進行索引優化

缺點：

存在數據冗余及數據維護異常
對數據的修改需要更多的成本

物理設計

根據所選擇的關系型數據庫的特點對邏輯模型進行存儲結構的設計

命名規范

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

• 數據庫、表、字段的命名要遵守表意性原則
  對象的名字應該能夠描述它所表示的對象
  例如：對于表的名稱應該能夠體現表中存儲的數據內容；對于存儲過程存儲過程應該能夠體現存儲過程的功能。

• 數據庫、表、字段的命名要遵守長名原則
  盡可能少使用或者不使用縮寫

存儲引擎規范

根據業務規則選擇合適的存儲引擎，不同引擎的利弊也在面有講，工作中一般也就上InnoDB或者MyISAM。

數據類型規范

為表中字段選擇合適的字節類型，當一個列可以選擇多種數據類型時

優先考慮數字類型
其次是日期、時間類型
最后是字符類型
對于相同級別的數據類型，應該優先選擇占用空間小的數據類型

重點：

1. 對于精度比較高的東西，比如money，用decimal類型，不會考慮float,double,因為他們容易產生誤差。

2. datetime 和 timestamp 的區別與選擇

timestamp 只占 4 個字節,它會自動檢索當前時區并進行轉換，不可存NULL。
datetime以 8 個字節儲存，不會進行時區的檢索，可存NULL

感謝各位的閱讀，以上就是“MySQL架構與存儲引擎，鎖，事務，設計分析”的內容了，經過本文的學習后，相信大家對MySQL架構與存儲引擎，鎖，事務，設計分析這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！