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

標籤：其他

上一篇：云圖說 | MSSI之應用業務模型ABM，搭建業務與技術的資料治理橋梁

下一篇：FlinkSQL自定義函式開發