說到MySQL, 大家都很熟悉，因為這是我們工作中不可避免會使用到的技術，但是你真正的掌握了它嗎？還是每天在重復crud呢！那么怎么樣告別crud呢！來到這里就對了。簡單概念的上的東西我就不提了。直接上技術。

數據庫三大范式

第一范式1NF:

數據表中的字段，必須是不可拆分的最小單元，也就是確保每一個字段的原子性。例如：

 

 

 

那么怎么去設計才是正確的呢？其中 address 可以再分為省、市、地區、街道、名牌號，違反了第一范式。

既然要滿足原子性不可以分割，我們把這些可以拆分的數據都給拆分出來不就行了嗎？以下是筆者拆分后的數據。

 

 

 

第二范式2NF:使用的時候只需與此表關聯即可。

滿足1NF的基礎上，要求：表中的所以列，都必需依賴主鍵，而不能有任何一列與主鍵沒有關系。言下之意就是一個表設計只能描述一件事情，不能把其它

 

 

 

無關的也嵌入進來。第二范式消除了表的無關數據。

那么怎么去設計才是正確的呢？其中 address 可以再分為省、市、地區、街道、名牌號，違反了第一范式。

此表中區域地址和你的用戶心情毫無關系。正確的做法就是建立另外一張描述你情緒的表。

第三范式 3NF:

滿足2NF的基礎上，任何非主字段不依賴與其它非主字段，在2NF基礎上消除傳遞依賴，也就是我們不允許設計的字段不能出現冗余現象。

 

 

 

我們從這幾張表中可以看出province，city,district,street,detailaddress,doorid都是依賴于region_id，所以不滿足第三范式。

筆者再多分享一點給大家，在實際工作當中，我們可能會反3NF,那么什么時候去反3NF呢？舉個實際工作當中的例子吧！例如我們在一個訂單中可能需要關聯到用戶，我們查看訂單的時候需要顯示出用戶名，用戶的其它信息就不用顯示出來了，如果不反三范式我們 查詢都需要關聯用戶表，如果查詢很普遍的話，就會影響到性能，所以我們干脆就可以把用戶名這個字段設計到訂單中。這樣就可以提高性能。在實際工作中靈活運用。

數據庫五大約束

1、主鍵約束（primary key）

唯一性，非null性

2、唯一約束 (unique)

唯一性，可以空，單只能有一個

3、檢查約束 (check)

對該列數據的范圍、格式的限制（如：年齡、性別等）

4、默認約束（default）

該數據的默認值

5、外鍵約束（foreign key）

建立兩表之間的關系

數據庫事務

數據庫事務是什么？

指的是單個邏輯工作單元執行的一系列操作，要么全部執行成功，要么完全不執行。

事務的四大特性

原子性

原子性是指事務包含的所有一系列操作要么全部成功提交，要么全部失敗回滾。它是數據庫事務最本質的特性。

一致性

一致性是指在事務開始之前和結束以后，數據庫的完整性約束沒有被破壞，這是說數據庫事務不能破壞關系數據的完整性以及業務邏輯上的一致性、這里有個容易混淆點，容易和數據一致性混淆。這里更多的強調單機下的事務一致性，必須是一個事務內部。

隔離性

每個事務都有各自的資源單位，事務與事務之間是互相隔離的、不可見，而事務的結果是對其它事務是可見的。這里可以理解為資源粒子度的劃分與隔離。

持久性

持久性確保的是一旦提交了事務，出現系統故障，該事物的更新也不會丟失。可以理解為持久化到磁盤中。

事務的四種隔離級別

隔離級別由低到高分別為

讀未提交（READ_UNCOMMITTED）

就是一個事務A去讀取一個事務B未提交的數據。如果B事務出現回滾，那么事務A就會出現臟讀的問題。

讀已提交 （READ_COMMINTED）

一個事務A讀取一個事務B已提交的數據，解決了臟讀問題，但是在一個事務范圍內兩個相同的查詢卻返回了不同的數據，那么這就是不可重復讀。

可重復讀 (REPETABLE_READ)

事務開啟時，不再允許其它的事務修改數據。這樣就可以無限制讀取沒有被修改的數據，解決了不可重復讀，當有并行插入操作時候就會出現幻讀。

可串行化（SERIALIZABLE）

在可串行化的隔離級別下，將事務串行化順序執行。那么事務不能進行并行操作，也就解決了幻讀的問題。

MYSQL InnoDB 引擎默認事務隔離級別是可重復讀（REPEATABLE_READ） ORACLE 引擎默認事務隔離級別是可串行化（SERIALIZABLE）

InnoDB 索引

innoDB特性

1. 完全的事務支持
2. 基于行存儲的行級鎖
3. 多版本并發控制
4. 原子死鎖檢測
5. 原子崩潰恢復

innodb 架構圖

innodb 邏輯存儲結構

在 innodb下，所有的數據都存儲在一個表空間中，表空間又由段（segment）、 區（extent）、 頁（page）、行（row）組成，頁在有些文檔中也成為塊（block） 1 extent = 64 page

innodb 存儲結構圖

B-tree

定義：B樹滿足如下條件，即可稱之為m階B樹：

1. 每個節點最多可以擁有m棵子樹；
2. 根節點最少擁有2棵子樹（存在子樹的情況下）；
3. 除了根節點以外，其余每個分支節點至少擁有m/2棵子樹；
4. 所有的頁節點都在同一層上；
5. 有k棵子樹的分支節點則存在k-1個關鍵碼，關鍵碼按照遞增次序進行排列；
6. 關鍵子樹量需要滿足ceil(m/2)-1 <= n <= m-1;

b樹圖

B-tree 的特點是每個節點不僅存放鍵值，而且存放數據。

b+tree圖

B+樹特點：1. 所有的葉子節點中包含了全部元素的信息，及指向含這些元素記錄的指針，且葉子節點本身依關鍵字的大小自小而大順序鏈接。2. 所有的中間節點元素都同時存在于子節點，在子節點元素中是最大（或最小）元素。

B+樹的優點：1. 單一節點存儲更多的元素（因為不含有對應的值，僅僅含有鍵），使得查詢的IO次數更少。2. 所有查詢都要從跟節點查找到葉子節點，查詢性能穩定，相對于B樹更加穩定，因為B+樹只有葉子節點存儲了對應的值信息。3. 所有葉子節點形成有序雙向鏈表，對于SQL的范圍查詢以及排序查詢都很方便。4. b/b+樹的共同優點：每個節點有更多的孩子，插入不需要改變樹的高度，從而減少重新平衡的次數，非常適合做數據庫索引這種需要持久化在磁盤，同時需要大量查詢和插入的應用。樹中節點存儲這指向頁的信息，可以快速定位到磁盤對應的頁上面。

Mysql 鎖總結

鎖不僅是資源占有的一種處理機制，更是多線程或并發編程下對數據一致性的一種保證。加鎖和釋放鎖本身也會消耗資源。了解并合理利用鎖機制，能大大提升數據庫的性能。鎖的作用者是事務，也就是說，鎖是針對事務使用而言。單個操作不顯示的開啟和提交/回滾事務，默認情況下每個操作會自動開啟一個事務。

共享鎖

一個事務對數據加共享鎖，也可以允許其它事務對此交集數據加此鎖。但阻止其它事務對此交集數據加排他鎖。

加共享鎖語句：SELECT * FRPM TABLE_NAME WHERE LOCK IN SHARE MODE;

排他鎖

一個事務對數據加排他鎖，會阻止其它事務對此交集數據加任和鎖。

加排他鎖語句：SELECT * FROM TABLE_NAME WHERE FOR UPDATE;

意向鎖

為了允許行鎖和表鎖共存，實現多粒度鎖機制，InoDB還有兩種內部使用的意向鎖，在這里的兩種意向鎖都是表鎖。

意向共享鎖

事務打算給數據行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的意向共享鎖。

意向排他鎖

事務打算給數據行加排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的意向排他鎖。

意向鎖是innodb 自動加的，不需要用戶干預。

表級鎖

每個事務操作會鎖住整張表，粒子度最大，簡單粗暴。優點是加鎖和釋放鎖次數會大大減少。缺點是鎖沖突的概率會大大增加，高并發情況下不可取。

頁級鎖

資源開銷介于行級鎖和表級鎖，會出現死鎖。

行級鎖

每個事務僅會鎖住被影響的行，也就是說，涉及到哪些行記錄，哪些行才會被鎖住，會出現死鎖。優點是鎖沖突概率小，并發度高。缺點是由于鎖離子度小，加鎖和釋放鎖的次數大大增加，資源開銷大。

mysql的行級鎖通過索引項上的索引來實現的，innodb這種行鎖實現特點意味著只有通過索引條件檢索數據，innodb才會使用行級鎖，否則，innodb將使用表鎖。

間隙鎖（Next-Key鎖）

當我們用范圍條件而不是相等條件檢索數據，并請求共享鎖或排他鎖時，innodb會給符合條件的已有數據的索引項加鎖；對于鍵值在條件范圍內但并不存在的記錄，叫做間隙，innodb也會對這個間隙加鎖，這種鎖機制不是所謂的間隙鎖。InnoDb使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關隔離級別的要求，對于上面的例子，要是不使用間隙鎖，如果其它事務插入更改了任何記錄，那么本事務再次執行上述語句，就會發生幻讀；另一方面，是為了滿足其恢復和復制的需要，有關恢復和復制機制的影響，以及不同隔離級別下innodb使用間隙鎖的情況。

很顯然，在使用范圍條件檢索并鎖定記錄時，innodb對這種加鎖機制會阻塞符合條件范圍內鍵值的并發插入，這往往會造成嚴重的鎖等待。因此，在實際開發中，尤其是并發插入比較多的應用，我們盡量優化業務邏輯，盡量使用相等條件來訪問更新數據，避免使用范圍條件。

死鎖

死鎖是指兩個或多個事務在同一個資源上相互占用，并請求鎖定對方占用的資源，從而導致惡性循環。當事務試圖以不同的順序鎖定資源時，就可能產生死鎖。多個事務同時鎖定同一個資源時也可能產生死鎖。

innodb避免死鎖

1. 為了在單個innodb表上執行多個并發寫入操作時避免死鎖，可以在事務開始時通過為預期要修改的每個記錄（行） 使用SELECT ... FOR UPDATE 語句來獲取必要的鎖，即使這些行的更改語句是在之后才執行的。
2. 在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖、更新時再申請排他鎖，因為這時候當用戶在申請排他鎖時，其它事務可能又已經獲取了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖
3. 如果事務需要修改或鎖定多個表，則應在每個事務中以相同的順序使用加鎖語句。在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低死鎖的機會
4. 同過SELECT...LOCK IN SHARE MODE 獲取行的讀鎖后，如果當前事務再需要對該記錄進行更新操作，則很可能造成死鎖。
5. 改變事務隔離級別

如果出現死鎖，可以使用SHOW INNODB STATUS 命令來確定最后一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細信息。如引發的SQL語句，事務已經獲得的鎖，正在等待什么鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。

樂觀鎖

加鎖是為了占用資源，我們上面說過加鎖和釋放鎖都會有資源開銷。在有些不需要加鎖就能獲取資源豈不是更好？樂觀鎖是樂觀的認為在搶占資源是不用加鎖就能獲取資源（因為沒有其它事務搶占資源或者發生的沖突概率小，稍稍嘗試幾次就能成功，美滋滋）。適用沖突概率小的情景下。

悲觀鎖

在沖突概率大的情況下，悲觀的認為搶不到資源或者多次都搶不到資源。只能通過加鎖的方式搶占資源，然后再做處理，最后釋放資源。

SQL優化

優化必備的explain命令

explain命令是用來查詢SQL的執行計劃 用法：explain select filed from table;

會查詢出以下重要字段：

+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | id | selecttype | table | partitions | type | possiblekeys | key | keylen | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t0 | NULL | range | idxtradeid | idxtrade_id | 8 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 1 row in set (0.02 sec)

重要字段說明：

select_type:使用的select查詢類型，比如simple、primary、union、subquery等；

table:關于訪問哪張表，如果是多表，則按訪問的先后排序排列；

type:非常非常重要的指標，表示mysql在表中找到行記錄的方式，又稱訪問類型。訪問類型的性能指標從差到好依次是system > const > eqref > ref > fulltext > refornull ? indexmerge > uniquesubquery > indexsubquery > range > index > ALL, 一般來說，得保證查詢至少達到range級別，最好能達到ref,否則可能出現性能問題；

possible_keys: 可能用到的所以，如果為null，表示沒有可能用到的索引；

key:用到的索引，如果為null，表示沒有使用索引；

key_len: 按字節計算的索引長度，值越小，表示越快；

ref:關聯關系中另一個表的列名稱；

rows: 查詢數據返回的行數；

extra:與關聯操作有關的信息

索引優化

1.禁止無邊界范圍查詢 != , < , > , <= , >= 否則 不會命中索引。

2.禁止無邊界范圍查詢 NOT IN ， 否則不會命中索引

3.禁止左模糊或全模糊查詢，否則不會命中索引

4.字段的默認值不要為null，否則不會命中索引（使用默認約束Default Counstraint）填充數據默認值

5.在字段上計算后，不會命中索引

6.組合索引的最左前綴原則

7.關于number類型的字段不加單引號也會走索引

8.對于多表 JOIN 時的 ON 條件中 字段類型一定要一致，否則也不會命中索引

9.varchar 查詢性能比 bigint 好，因為bigint類型字段上會全表掃描，而在varchar上每個字符判斷會走索引，這樣避免全表掃描。

10.小數類型使用decimal,禁止使用float與double float和double存儲數據時，可能或損失精度，進而判斷的時候導致結果不準，強制使用decimal數據類型。

11.表達是否的概念時，字段使用is_開頭，數據類型使用unsigned tinyint類型， 1表示是 0 表示否。

12.任何非負數都必須聲明為unsigned類型 比如年齡。狀態嗎等，這樣最大容量正值會擴大一倍。

13.如果存儲的字符串長度幾乎相等，必須使用char定長字符串類型 比如手機號碼11位。

14.有時候是不需要建索引 性別字段，狀態，這種不同值很少的字段是不需要建立索引的。

15.單表行數超過500萬行或單表容量超過2G，才推薦分表

16.進行update或delete時，必先select，避免出現誤刪數據

數據庫拆分

數據庫承載的數據以及請求負載較高時，我們就要考慮使用讀寫分離、數據緩存。但隨著業務的增長，數據庫的壓力達到了承載的閥值米就要考慮分庫分表，分解，分攤單個數據庫壓力。

垂直拆分

數據庫的垂直拆分:通常將所有的數據按照不同的業務建立并存儲不同的表（table）,垂直拆分是按照業務將一個數據庫拆分多個數據庫。原來每個業務對應一張表，垂直拆分后，是一個業務對應一個數據庫（當然也有坑可能是多個業務對應一個數據庫）。其核心是專庫專用。達到的結果是將原來一個數據庫系統的壓力按照業務均攤到各個拆分后的數據庫中。垂直拆分也是比較推薦的一種拆分方式。

垂直分片往往需要對架構和設計進行調整。在當前微服務化的進程中，對數據庫的垂直拆分是非常友好的。

數據表的垂直拆分：單表的數據達到2GB或者500萬行記錄就要考慮拆分數據表，垂直拆分表就將熱點列和不經常使用的列表拆分開，降低單表的大小。

水平拆分

當一般垂直拆分遇到瓶頸時，會對數據表進行水平拆分。這種方式與垂直拆分不同的地方是，他不會更改表結構。水平分表是將一個表拆分成多結構相同的多個表；并且這些表分布在不同的數據庫。

分庫分表中間件有兩種 1.代理模式的分庫分表中間件：MyCat; 2.客戶端模式的分庫分表中間件：ShardingJDBC 3.支持事務的分布式數據庫（當然ShardingProxy也是代理分庫分表中間件）

總結： 結合著微服務體系，一般會進行垂直拆分。當微服務中的數據庫出現壓力時，然后進行水平拆分。

歡迎大家評論留言。