MySQL

1. 索引

1.1 什麼是索引

索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表裡所有記錄的引用指針。

索引是一種數據結構。資料庫索引，是資料庫管理系統中一個排序的數據結構，以協助快速查詢、更新資料庫表中數據。索引的實現通常使用B樹及其變種B+樹。

更通俗的說，索引就相當於目錄。為了方便查找書中的內容，通過對內容建立索引形成目錄。索引是一個文件，它是要占據物理空間的。

1.2 索引優缺點

索引的優點

可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。
通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。
索引的缺點

時間方面：創建索引和維護索引要耗費時間，具體地，當對錶中的數據進行增加、刪除和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率；
空間方面：索引需要占物理空間。

1.3 索引類型

• 主鍵索引: 數據列不允許重覆，不允許為NULL，一個表只能有一個主鍵。

  

• 唯一索引: 數據列不允許重覆，允許為NULL值，一個表允許多個列創建唯一索引。

​ 可以通過 ALTER TABLE table_name ADD UNIQUE (column); 創建唯一索引

​ 可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 創建唯一組合索引

​

• 普通索引: 基本的索引類型，沒有唯一性的限制，允許為NULL值。

​ 可以通過ALTER TABLE table_name ADD INDEX index_name (column);創建普通索引

​ 可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創建組合索引

• 全文索引： 是目前搜索引擎使用的一種關鍵技術。

​ 可以通過ALTER TABLE table_name ADD FULLTEXT (column);創建全文索引
​ ### 1.4 B樹

索引的數據結構和具體存儲引擎的實現有關，在MySQL中使用較多的索引有Hash索引，B+樹索引等，而我們經常使用的InnoDB存儲引擎的預設索引實現為：B+樹索引。對於哈希索引來說，底層的數據結構就是哈希表，因此在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其餘大部分場景，建議選擇BTree索引。

mysql通過存儲引擎取數據，基本上90%的人用的就是InnoDB了，按照實現方式分，InnoDB的索引類型目前只有兩種：BTREE（B樹）索引和HASH索引。B樹索引是Mysql資料庫中使用最頻繁的索引類型，基本所有存儲引擎都支持BTree索引。通常我們說的索引不出意外指的就是（B樹）索引（實際是用B+樹實現的，因為在查看表索引時，mysql一律列印BTREE，所以簡稱為B樹索引）

MySQL 跟 B+ 樹沒有直接的關係，真正與 B+ 樹有關係的是 MySQL 的預設存儲引擎 InnoDB，MySQL 中存儲引擎的主要作用是負責數據的存儲和提取，除了 InnoDB 之外，MySQL 中也支持 MyISAM 作為表的底層存儲引擎。

B+tree性質：

1.）n棵子tree的節點包含n個關鍵字，不用來保存數據而是保存數據的索引。

2.）所有的葉子結點中包含了全部關鍵字的信息，及指向含這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大順序鏈接。

3.）所有的非終端結點可以看成是索引部分，結點中僅含其子樹中的最大（或最小）關鍵字。

4.）B+ 樹中，數據對象的插入和刪除僅在葉節點上進行。

5.）B+樹有2個頭指針，一個是樹的根節點，一個是最小關鍵碼的葉節點。

B+樹是B樹的改進，簡單地說是：只有葉子節點才存數據，非葉子節點是存儲的指針；所有葉子節點構成一個有序鏈表

B+樹是B樹的改進，簡單地說是：只有葉子節點才存數據，非葉子節點是存儲的指針；所有葉子節點構成一個有序鏈表

B+樹的內部節點並沒有指向關鍵字具體信息的指針，因此其內部節點相對B樹更小，如果把所有同一內部節點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多，一次性讀入記憶體的需要查找的關鍵字也就越多，相對IO讀寫次數就降低了

B樹只適合隨機檢索，而B+樹同時支持隨機檢索和順序檢索；
B+樹空間利用率更高，可減少I/O次數，磁碟讀寫代價更低。一般來說，索引本身也很大，不可能全部存儲在記憶體中，因此索引往往以索引文件的形式存儲的磁碟上。這樣的話，索引查找過程中就要產生磁碟I/O消耗。B+樹的內部結點並沒有指向關鍵字具體信息的指針，只是作為索引使用，其內部結點比B樹小，盤塊能容納的結點中關鍵字數量更多，一次性讀入記憶體中可以查找的關鍵字也就越多，相對的，IO讀寫次數也就降低了。而IO讀寫次數是影響索引檢索效率的最大因素；
B+樹的查詢效率更加穩定。B樹搜索有可能會在非葉子結點結束，越靠近根節點的記錄查找時間越短，只要找到關鍵字即可確定記錄的存在，其性能等價於在關鍵字全集內做一次二分查找。而在B+樹中，順序檢索比較明顯，隨機檢索時，任何關鍵字的查找都必須走一條從根節點到葉節點的路，所有關鍵字的查找路徑長度相同，導致每一個關鍵字的查詢效率相當。
B-樹在提高了磁碟IO性能的同時並沒有解決元素遍歷的效率低下的問題。B+樹的葉子節點使用指針順序連接在一起，只要遍歷葉子節點就可以實現整棵樹的遍歷。而且在資料庫中基於範圍的查詢是非常頻繁的，而B樹不支持這樣的操作。
增刪文件（節點）時，效率更高。因為B+樹的葉子節點包含所有關鍵字，並以有序的鏈表結構存儲，這樣可很好提高增刪效率。

1.5 hash索引

簡要說下，類似於數據結構中簡單實現的HASH表（散列表）一樣，當我們在mysql中用哈希索引時，主要就是通過Hash演算法（常見的Hash演算法有直接定址法、平方取中法、摺疊法、除數取餘法、隨機數法），將資料庫欄位數據轉換成定長的Hash值，與這條數據的行指針一併存入Hash表的對應位置；如果發生Hash碰撞（兩個不同關鍵字的Hash值相同），則在對應Hash鍵下以鏈表形式存儲。當然這隻是簡略模擬圖。

Hash索引只能用於對等比較，例如=,<=>（相當於=）操作符。由於是一次定位數據，不像BTree索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高於BTree索引。

1.6 索引原則

1） 最左首碼匹配原則，組合索引非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

2）較頻繁作為查詢條件的欄位才去創建索引

3）更新頻繁欄位不適合創建索引

4）若是不能有效區分數據的列不適合做索引列(如性別，男女未知，最多也就三種，區分度實在太低)

5）儘量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。

6）定義有外鍵的數據列一定要建立索引。

7）對於那些查詢中很少涉及的列，重覆值比較多的列不要建立索引。

8）對於定義為text、image和bit的數據類型的列不要建立索引。

2. 事務

2.1 什麼是事務

事務是一個不可分割的資料庫操作序列，也是資料庫併發控制的基本單位，其執行的結果必須使資料庫從一種一致性狀態變到另一種一致性狀態。事務是邏輯上的一組操作，要麼都執行，要麼都不執行。

2.2 四個特性

• 原子性： 事務是最小的執行單位，不允許分割。事務的原子性確保動作要麼全部完成，要麼完全不起作用；
• 一致性： 執行事務前後，數據保持一致，多個事務對同一個數據讀取的結果是相同的；<假設用戶A和用戶B兩者的錢加起來一共是5000，那麼不管A和B之間如何轉賬，轉幾次賬，事務結束後兩個用戶的錢相加起來應該還得是5000，這就是事務的一致性。>
• 隔離性： 併發訪問資料庫時，一個用戶的事務不被其他事務所干擾，各併發事務之間資料庫是獨立的；<對於任意兩個併發的事務T1和T2，在事務T1看來，T2要麼在T1開始之前就已經結束，要麼在T1結束之後才開始，這樣每個事務都感覺不到有其他事務在併發地執行。>
• 持久性： 一個事務被提交之後。它對資料庫中數據的改變是持久的，即使資料庫發生故障也不應該對其有任何影響。

2.3 臟讀，幻讀，不可重覆讀

• 臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由於某些原因，前一個RollBack了操作，則後一個事務所讀取的數據就會是不正確的。
• 不可重覆讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的數據。
• 幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列(Row)數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。

2.4 隔離級別

• READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重覆讀。（一個事務還沒提交，就被別的事務看到了）
• READ-COMMITTED(讀取已提交)： 允許讀取併發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重覆讀仍有可能發生。
• REPEATABLE-READ(可重覆讀)： 對同一欄位的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重覆讀，但幻讀仍有可能發生
• SERIALIZABLE(可串列化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重覆讀以及幻讀。

3. 鎖

當資料庫有併發事務的時候，可能會產生數據的不一致，這時候需要一些機制來保證訪問的次序，鎖機制就是這樣的一個機制。

就像酒店的房間，如果大家隨意進出，就會出現多人搶奪同一個房間的情況，而在房間上裝上鎖，申請到鑰匙的人才可以入住並且將房間鎖起來，其他人只有等他使用完畢才可以再次使用。

MyISAM和InnoDB存儲引擎使用的鎖：

• MyISAM採用表級鎖(table-level locking)。

• InnoDB支持行級鎖(row-level locking)和表級鎖，預設為行級鎖

• 行級鎖是Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共用鎖 和 排他鎖。

  特點：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。

• 表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分MySQL引擎支持。最常使用的MYISAM與INNODB都支持表級鎖定。表級鎖定分為表共用讀鎖（共用鎖）與表獨占寫鎖（排他鎖）。

  特點：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖衝突的概率最高，併發度最低。

• 頁級鎖是MySQL中鎖定粒度介於行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。

共用鎖和排他鎖

共用鎖: 又叫做讀鎖。 當用戶要進行數據的讀取時，對數據加上共用鎖。共用鎖可以同時加上多個。

排他鎖: 又叫做寫鎖。 當用戶要進行數據的寫入時，對數據加上排他鎖。排他鎖只可以加一個，他和其他的排他鎖，共用鎖都相斥。

用戶的行為有兩種，一種是來看房，多個用戶一起看房是可以接受的。 一種是真正的入住一晚，在這期間，無論是想入住的還是想看房的都不可以。

死鎖

死鎖是指兩個或多個事務在同一資源上相互占用，並請求鎖定對方的資源，從而導致惡性迴圈的現象。

常見的解決死鎖的方法

1、如果不同程式會併發存取多個表，儘量約定以相同的順序訪問表，可以大大降低死鎖機會。

2、在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；

3、對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；

如果業務處理不好可以用分散式事務鎖或者使用樂觀鎖

樂觀鎖和悲觀鎖

資料庫管理系統（DBMS）中的併發控制的任務是確保在多個事務同時存取資料庫中同一數據時不破壞事務的隔離性和統一性以及資料庫的統一性。樂觀併發控制（樂觀鎖）和悲觀併發控制（悲觀鎖）是併發控制主要採用的技術手段。

悲觀鎖：假定會發生併發衝突，屏蔽一切可能違反數據完整性的操作。在查詢完數據的時候就把事務鎖起來，直到提交事務。實現方式：使用資料庫中的鎖機制

樂觀鎖：假設不會發生併發衝突，只在提交操作時檢查是否違反數據完整性。在修改數據的時候把事務鎖起來，通過version的方式來進行鎖定。實現方式：樂一般會使用版本號機制或CAS演算法實現。

兩種鎖的使用場景：

從上面對兩種鎖的介紹，我們知道兩種鎖各有優缺點，不可認為一種好於另一種，像樂觀鎖適用於寫比較少的情況下（多讀場景），即衝突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。

但如果是多寫的情況，一般會經常產生衝突，這就會導致上層應用會不斷的進行retry，這樣反倒是降低了性能，所以一般多寫的場景下用悲觀鎖就比較合適。

4. 視圖

4.1 什麼是視圖

為了提高複雜SQL語句的復用性和表操作的安全性，MySQL資料庫管理系統提供了視圖特性。所謂視圖，本質上是一種虛擬表，在物理上是不存在的，其內容與真實的表相似，包含一系列帶有名稱的列和行數據。但是，視圖並不在資料庫中以儲存的數據值形式存在。行和列數據來自定義視圖的查詢所引用基本表，並且在具體引用視圖時動態生成。

視圖使開發者只關心感興趣的某些特定數據和所負責的特定任務，只能看到視圖中所定義的數據，而不是視圖所引用表中的數據，從而提高了資料庫中數據的安全性。

視圖將我們不需要的數據過濾掉，將相關的列名用我們自定義的列名替換。視圖作為一個訪問介面，不管基表的表結構和表名有多複雜。

　如果創建視圖時不明確指定視圖的列名，那麼列名就和定義視圖的select子句中的列名完全相同；

　　如果顯式的指定視圖的列名就按照指定的列名。

4.2 使用場景

簡化sql查詢，提高開發效率。如果說還有另外一個用途那就是相容老的表結構。

下麵是視圖的常見使用場景：

重用SQL語句；

簡化複雜的SQL操作。在編寫查詢後，可以方便的重用它而不必知道它的基本查詢細節；

使用表的組成部分而不是整個表；

保護數據。可以給用戶授予表的特定部分的訪問許可權而不是整個表的訪問許可權；

更改數據格式和表示。視圖可返回與底層表的表示和格式不同的數據。

4.3 視圖優缺點

優點

1. 查詢簡單化。視圖能簡化用戶的操作
2. 數據安全性。視圖使用戶能以多種角度看待同一數據，能夠對機密數據提供安全保護
3. 邏輯數據獨立性。視圖對重構資料庫提供了一定程度的邏輯獨立性

缺點

性能。資料庫必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個複雜的多表查詢所定義，那麼，即使是視圖的一個簡單查詢，資料庫也把它變成一個複雜的結合體，需要花費一定的時間。

修改限制。當用戶試圖修改視圖的某些行時，資料庫必須把它轉化為對基本表的某些行的修改。事實上，當從視圖中插入或者刪除時，情況也是這樣。對於簡單視圖來說，這是很方便的，但是，對於比較複雜的視圖，可能是不可修改的

這些視圖有如下特征：1.有UNIQUE等集合操作符的視圖。2.有GROUP BY子句的視圖。3.有諸如AVG\SUM\MAX等聚合函數的視圖。 4.使用DISTINCT關鍵字的視圖。5.連接表的視圖（其中有些例外）

5. 範式

5.1 什麼是範式

資料庫表的設計依據，教我們怎麼進行資料庫表的設計。

第一範式： 要求任何一張表必須有主鍵，每一個欄位原子性不可再分。
第二範式： 建立在第一範式的基礎之上，要求所有非主鍵欄位完全依賴主鍵，不要產生部分依賴。
第三範式： 建立在第二範式的基礎之上，要求所有非主鍵欄位直接依賴主鍵，不要產生傳遞依賴。

5.2 第一範式

第一範式是最核心，最重要的範式，所有表的設計都需要滿足。必須有主鍵，並且每一個欄位都是原子性不可再分。

+----+----------+-------------------+----------+------+
| id | name     | email             | password | age  |
+----+----------+-------------------+----------+------+
|  1 | ivan     | [email protected]        | 123345   |   23 |
|  2 | lee      | [email protected]     | 123345   |   23 |
|  3 | sdsdslee | [email protected] | 123345   |   23 |
|  4 | regina   | [email protected]     | 123455   |   23 |
+----+----------+-------------------+----------+------+

id就是主鍵，並且每一個欄位不可再分！

6. 觸發器

6.1 什麼是觸發器

觸發器是用戶定義在關係表上的一類由事件驅動的特殊的存儲過程。觸發器是指一段代碼，當觸發某個事件時，自動執行這些代碼。

使用場景

可以通過資料庫中的相關表實現級聯更改。
實時監控某張表中的某個欄位的更改而需要做出相應的處理。
例如可以生成某些業務的編號。
註意不要濫用，否則會造成資料庫及應用程式的維護困難。

6.2 觸發器種類

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

7. 引擎

• Innodb引擎：Innodb引擎提供了對資料庫ACID事務的支持。並且還提供了行級鎖和外鍵的約束。它的設計的目標就是處理大數據容量的資料庫系統。
• MyIASM引擎(原本Mysql的預設引擎)：不提供事務的支持，也不支持行級鎖和外鍵。

本文來自博客園，作者：ivanlee717，轉載請註明原文鏈接：https://www.cnblogs.com/ivanlee717/p/17273042.html