Mysql詳解

Mysql的介紹

【1】MySQL是一個輕量級關係型資料庫管理系統，將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，就增加了速度並提高了靈活性。

【2】sql語言分類：

【3】索引分類

　　1）聚簇索引（又稱為主鍵索引，本質上數據是存儲在葉子節點上）

　　2）非二級索引（又稱為二級索引，本質上葉子節點只存儲數據的id，需要進行回表獲得數據）

　　【1】類型

　　　　（1）NORMAL（普通索引，也是最常用的）

　　　　（2）FULLTEXT（全文索引）

　　　　（3）SPATIAL（空間索引）

　　　　（4）UNIQUE（唯一索引）

　　【2】方法

　　　　（1）BTREE（也就是B+Tree）

　　　　（2）HASH（也就是hash table結構）

　　【3】欄位個數

　　　　（1）單個

　　　　（2）多個（組合索引又稱為複合索引）

Mysql的列的數據類型詳解

【1】數值

【2】字元串

【3】時間和日期

【4】null，沒有值【註意：使用NULL進行運算，結果為NULL】

Mysql的存儲引擎【存儲引擎生效的單位是表】

【1】展示

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.06 sec)

【2】MyISAM存儲引擎

【2.1】文件說明

//每張表對應會有三個文件
//MyISAM索引文件和數據文件是分離的(非聚集)
user.MYI    //索引存儲的信息
user.MYD    //數據存儲的信息
user.frm    //數據表結構的信息

【2.2】圖示

【3】InnoDB存儲引擎

【3.1】文件說明

//每張表對應會有兩個文件
//表數據文件本身就是按B+Tree組織的一個索引結構文件
//聚集索引-葉節點包含了完整的數據記錄
article.frm    //數據表結構的信息
article.ibd    //數據與索引存儲的信息

【3.2】圖示

【3.3】為什麼建議InnoDB表必須建主鍵，並且推薦使用整型的自增主鍵？

　　1）首先如果不建立的話，它會從數據列中找出全部不同的一列作為主鍵，如果找不到會創建一個隱藏列作為主鍵。那麼既然會有隱藏列列了，乾脆直接創建就好。

　　2）如果使用UUID作為主鍵，首先，UUID不易於存儲，16位元組128位，通常以36長度的字元串表示，很多場景不適用。其次新行的主鍵值不一定比之前的主鍵值大，所以innoDb無法做到總是把新行插入到索引的最後，而需要為新行尋找合適的位置來分配新的空間，這個過程會導致：

【1】寫入的目標頁可能從緩存上移除了，或者還沒有載入到緩存上，innodb寫入之前需要先從磁碟找到目標頁，會產生大量的隨機IO；
【2】因為寫入是亂序的，innoDb 要做頻繁的分頁操作，以便為行產生新的空間，頁分裂導致移動大量的數據，一次插入最少需要修改三個頁以上；
【3】頻繁的頁分裂，頁會變得稀疏並被不規則的填充，最終會導致數據會有碎片；
【4】隨機值（uuid和雪花id）載入到聚簇索引，有時候會需要做一次OPTIMEIZE TABLE來重建表並優化頁的填充，這將又需要一定的時間消耗。

　　3）使用自增主鍵則可以避免上述問題：

【1】自增主鍵值是順序的，所以Innodb把每一條記錄都存儲在一條記錄的後面。當達到頁面的最大填充因數時候(innodb預設的最大填充因數是頁大小的15/16,會留出1/16的空間留作以後的修改)，下一條記錄就會寫入新的頁中；
【2】數據按照順序方式載入，主鍵頁就會近乎於順序的記錄填滿，提升了頁面的最大填充率，不會有頁的浪費；
【3】新插入的行一定會在原有的最大數據行下一行，mysql定位和定址很快，不會為計算新行的位置而做出額外的消耗；
【4】減少了頁分裂和碎片的產生

【3.4】非主鍵索引結構葉子節點存儲的是主鍵值，主要是為了一致性和節省存儲空間。

常用函數記錄

【1】數據函數

ABS(X)        //絕對值
CEILING(X)    //向上取整
FLOOR(X)    //向下取整
ROUND(X)    //如果無參,返回一個0-1之間的隨機數；如果有參，返回參數X的四捨五入的一個整數。
SIGN(X)        //符號函數: 負數返回-1,正數返回1,0返回0

【2】字元串函數

【3】日期和時間函數

【4】聚合函數

count()    //返回滿足查詢（Select）條件的 總和數，如select count(*) [不建議使用，效率低]
min()    //可以為數值欄位、字元欄位或表達式列做統計，返回最小值
max()    //可以為數值欄位，字元欄位或表達式列作統計，返回最大的值
avg()    //返回一列的平均值
sum()    //返回一列的總和

Mysql索引底層數據結構

【1】索引的本質：索引是幫助MySQL高效獲取數據的排好序的數據結構。

【2】索引數據結構：在我們創建索引的時候會給予我們兩個選擇，BTREE【這裡指的是b+tree】與HASH。

【2.1】B+Tree結構

　　1）說明

1.B+Tree是B-Tree的變種
2.非葉子節點不存儲data，只存儲索引(冗餘)，可以放更多的索引
3.葉子節點包含所有索引欄位
4.葉子節點用指針連接，提高區間訪問的性能

　　2）分析能存儲的數據量

　　　　【1】頁是 InnoDB 管理的最小單位，常見的有 FSP_HDR，INODE, INDEX 等類型。頁結構分為文件頭(前38位元組)，頁數據和文件尾(後8位元組)。每個數據頁大小為16kb，查看方法為

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.03 sec)

　　　　【2】系統從磁碟中讀取數據到記憶體時是以磁碟塊（block）為基本單位（4kb【這個與操作系統有關】），位於同一個磁碟塊中的數據會被一次性讀取出來。block大小空間往往沒有16kb大，因此innodb每次io操作時都會將若幹個地址連續的磁碟塊的數據讀入記憶體，從而實現整頁讀入記憶體。

　　　　【3】如果索引欄位為bigint，它會占據8B，而地址指針【指向下一個磁碟塊文件地址】占據的是6B。所以此時，一般能存1170個左右的數據【這個數值其實是沒有減去數據頁的一些其他數據的，只是假設全部用於存儲數據】。如果非葉子節點是兩層的話，那麼就會是136萬的數據量。假設葉子結點存儲的數據是1K，那麼可以存儲16個，數據量便會是2176萬的數據。

　　　　【4】而且Mysql的索引的根結點是常駐記憶體的。

　　3）圖示

【2.2】hash結構

　　1）說明

1.對索引的key進行一次hash計算就可以定位出數據存儲的位置
2.很多時候Hash索引要比B+ 樹索引更高效
3.僅能滿足 “=”，“IN”，不支持範圍查詢
4.hash衝突問題

　　2）圖示

【3】對於那麼多的數據結構，如鏈表，數組，二叉樹，紅黑樹（平衡二叉樹），B-tree（多路平衡二叉樹）為什麼都不選擇？

【3.1】首先對於鏈表與數組，它們自身的缺點很明顯，數組查詢快但是遇到向中間插入數據的情況會涉及到大量的數據遷移，而鏈表涉及的數據遷移幾乎沒有但是查詢效率在大數據量下其實很慢。故是不合適的。

【3.2】對於二叉樹，在極端情況下，它是會存在退化為鏈表結構的。所以才會有平衡二叉樹的存在，但是平衡二叉樹也會存在問題，就是數據量大了之後層級會很多，也不能很好的利用磁碟塊的理念，所以才會出現多路平衡二叉樹。

【3.3】那麼為什麼B-tree也不會被選擇呢？

　　1）說明

1.葉節點具有相同的深度，葉節點的指針為空
2.所有索引元素不重覆
3.節點中的數據索引從左到右遞增排列

　　2）圖示

　　3）理由

　　　　【1】層級依舊會出現很大的情況，如果數據量是1k，那麼地址指針的大小先不算，一個數據頁最多能容納16個數據，兩千多萬的數據便需要7層，遠遠大於B+Tree的層級。一次load節點是一次磁碟IO，是非常慢的，但是我們把它load到記憶體中之後在你記憶體里隨機的找某一個元素是非常快的，跟一次磁碟IO這個時間消耗去比對的話幾乎可以忽略不計。故層級越多涉及的磁碟IO也就越多。

　　　　【2】其次是不好優化，因為BTree中，數據都在節點上，必然會出現層級不一的情況，快慢便是取決於你所在的層級。而B+Tree要獲取數據需要到對應的葉子結點上，保證了經歷的層級數是相同的。

【4】聯合索引的原理

　　1）說明

索引最左首碼原理
1.首先索引是幫助MySQL高效獲取數據的排好序的數據結構，重點是排好序的數據結構。
2.所以基於此情況下它是先將第一個元素排序，然後再第一個元素相同的情況下，進行第二個元素的排序，以此類推。
3.所以在查找過程中需要先匹配到第一個元素，然後在匹配到第二個，再到第三個【當然只匹配前面的也是可以的】，這樣就可以獲取到數據的id。
4.基於id再去聚簇索引找到完整的行記錄【逐一進行回表掃描】，這邊是我們常說的回表操作。
5.其中裡面又涉及了一個叫做索引下推的概念：通過explain查看執行計劃，看到Extra一列里 Using index，這就是用到了索引下推。
6.索引下推：指當第一個元素匹配了，然後還可以用第二個乃至第三個元素進行過濾。因為這種不需要進行回表拿到數據進行過濾，減少了回表的次數。
7.其次，這類索引的好處是，因為你已經是索引的一部分了，所以對應的獲取這部分的數據其實也是不需要進行回表的。

　　2）圖示

Mysql的鎖機制

【1】鎖分類

1）從性能上分為樂觀鎖(用版本對比來實現)和悲觀鎖
2）從對數據操作的粒度分，分為表鎖和行鎖
3）從對資料庫操作的類型分，分為讀鎖和寫鎖(都屬於悲觀鎖)，還有意向鎖

【2】對鎖類型的分析【本質上怎麼說，這些基本不用手動加，資料庫的引擎會自動加】

【2.1】讀鎖（共用鎖，S鎖(Shared)）：針對同一份數據，多個讀操作可以同時進行而不會互相影響，比如：select * from T where id=1 lock in share mode;

【2.2】寫鎖（排它鎖，X鎖(eXclusive)）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖，數據修改操作都會加寫鎖，查詢也可以通過for update加寫鎖，比如：select * from T where id=1 for update;

【2.3】意向鎖（Intention Lock）：又稱I鎖，針對錶鎖，主要是為了提高加表鎖的效率，是mysql資料庫自己加的。當有事務給表的數據行加了共用鎖或排他鎖，同時會給表設置一個標識，代表已經有行鎖了，其他事務要想對錶加表鎖時，就不必逐行判斷有沒有行鎖可能跟表鎖衝突了，直接讀這個標識就可以確定自己該不該加表鎖。特別是表中的記錄很多時，逐行判斷加表鎖的方式效率很低。而這個標識就是意向鎖。

意向鎖主要分為：
意向共用鎖，IS鎖，對整個表加共用鎖之前，需要先獲取到意向共用鎖。
意向排他鎖，IX鎖，對整個表加排他鎖之前，需要先獲取到意向排他鎖

【3】對鎖粒度的分析

【3.1】表鎖：每次操作鎖住整張表。開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，併發度最低；一般用在整表數據遷移的場景。

【3.1.1】基本操作

// 建表SQL
CREATE TABLE `mylock` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

// 手動增加表鎖
lock table 表名稱 read(write),表名稱2 read(write);
// 查看表上加過的鎖
show open tables;
// 刪除表鎖
unlock tables;

//如果加了讀鎖
當前session和其他session都可以讀該表
當前session中插入或者更新鎖定的表都會報錯，其他session插入或更新則會等待

//如果加了寫鎖
當前session對該表的增刪改查都沒有問題，其他session對該表的所有操作被阻塞

【3.1.2】總結

1、對MyISAM表的讀操作(加讀鎖) ,不會阻塞其他進程對同一表的讀請求,但會阻塞對同一表的寫請求。只有當讀鎖釋放後,才會執行其它進程的寫操作。
2、對MylSAM表的寫操作(加寫鎖) ,會阻塞其他進程對同一表的讀和寫操作,只有當寫鎖釋放後,才會執行其它進程的讀寫操作

【3.2】行鎖：每次操作鎖住一行數據。開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度最高。

【3.2.1】一個session開啟事務更新不提交，另一個session更新同一條記錄會阻塞，更新不同記錄不會阻塞

【3.2.2】InnoDB與MYISAM的最大不同有兩點：

InnoDB支持事務（TRANSACTION）
InnoDB支持行級鎖

【3.3】總結：

MyISAM在執行查詢語句SELECT前，會自動給涉及的所有表加讀鎖,在執行update、insert、delete操作會自動給涉及的表加寫鎖。
InnoDB在執行查詢語句SELECT時(非串列隔離級別)，不會加鎖。但是update、insert、delete操作會加行鎖。
簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖則會把讀和寫都阻塞。

Mysql的事務機制

【1】事務的ACID特性

//事務是由一組SQL語句組成的邏輯處理單元,事務具有以下4個屬性,通常簡稱為事務的ACID屬性。
1）原子性(Atomicity) ：事務是一個原子操作單元,其對數據的修改,要麼全都執行,要麼全都不執行。
2）一致性(Consistent) ：在事務開始和完成時,數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用於事務的修改,以保持數據的完整性。
3）隔離性(Isolation) ：資料庫系統提供一定的隔離機制,保證事務在不受外部併發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的,反之亦然。
）持久性(Durable) ：事務完成之後,它對於數據的修改是永久性的,即使出現系統故障也能夠保持。

【2】併發事務處理帶來的問題

【3】事務隔離級別

【4】事務隔離級別註意事項

//資料庫的事務隔離越嚴格,併發副作用越小,但付出的代價也就越大,因為事務隔離實質上就是使事務在一定程度上“串列化”進行,這顯然與“併發”是矛盾的。
//同時,不同的應用對讀一致性和事務隔離程度的要求也是不同的,比如許多應用對“不可重覆讀"和“幻讀”並不敏感,可能更關心數據併發訪問的能力。
常看當前資料庫的事務隔離級別: 
show variables like 'tx_isolation';
設置事務隔離級別：
set tx_isolation='REPEATABLE-READ';

Mysql預設的事務隔離級別是可重覆讀【因為MVCC多版本併發控制機制】，用Spring開發程式時，如果不設置隔離級別預設用Mysql設置的隔離級別，如果Spring設置了就用已經設置的隔離級別。

MVCC多版本併發控制機制

【1】Mysql在讀已提交和可重覆讀隔離級別下都實現了MVCC機制。（至於為什麼要實現）

【2】因為隔離性就是靠MVCC(Multi-Version Concurrency Control)機制來保證的，對一行數據的讀和寫兩個操作預設是不會通過加鎖互斥來保證隔離性，避免了頻繁加鎖互斥，而在串列化隔離級別為了保證較高的隔離性是通過將所有操作加鎖互斥來實現的。

【3】實現這個機制主要是依賴 undo日誌版本鏈與read view機制：

【4】undo日誌版本鏈是指一行數據被多個事務依次修改過後，在每個事務修改完後，Mysql會保留修改前的數據undo回滾日誌，並且用兩個隱藏欄位trx_id和roll_pointer把這些undo日誌串聯起來形成一個歷史記錄版本鏈。

【5】當事務開啟，執行任何查詢sql時會生成當前事務的一致性視圖read-view，該視圖在事務結束之前都不會變化【這裡指在可重覆讀隔離級別下】(如果是讀已提交隔離級別在每次執行查詢sql時都會重新生成)，這個視圖由執行查詢時所有未提交事務id數組（數組裡最小的id為min_id）和已創建的最大事務id（max_id）組成，事務里的任何sql查詢結果需要從對應版本鏈里的最新數據開始逐條跟read-view做比對從而得到最終的快照結果。

【6】圖示：

【7】版本鏈比對規則：

1）如果 row 的 trx_id 落在綠色部分( trx_id<min_id )，表示這個版本是已提交的事務生成的，這個數據是可見的；
2）如果 row 的 trx_id 落在紅色部分( trx_id>max_id )，表示這個版本是由將來啟動的事務生成的，是不可見的(若 row 的 trx_id 就是當前自己的事務是可見的）；
3）如果 row 的 trx_id 落在黃色部分(min_id <=trx_id<= max_id)，那就包括兩種情況
    【1】 若 row 的 trx_id 在視圖數組中，表示這個版本是由還沒提交的事務生成的，不可見(若 row 的 trx_id 就是當前自己的事務是可見的)；
    【2】 若 row 的 trx_id 不在視圖數組中，表示這個版本是已經提交了的事務生成的，可見。

【8】對於刪除的情況可以認為是update的特殊情況，會將版本鏈上最新的數據複製一份，然後將trx_id修改成刪除操作的trx_id，同時在該條記錄的頭信息（record header）里的（deleted_flag）標記位寫上true，來表示當前記錄已經被刪除，在查詢時按照上面的規則查到對應的記錄如果delete_flag標記位為true，意味著記錄已被刪除，則不返回數據。

【註意】begin/start transaction 命令並不是一個事務的起點，在執行到它們之後的第一個修改操作InnoDB表的語句，事務才真正啟動，才會向mysql申請事務id，mysql內部是嚴格按照事務的啟動順序來分配事務id的。

【總結】MVCC機制的實現就是通過read-view機制與undo版本鏈比對機制，使得不同的事務會根據數據版本鏈對比規則讀取同一條數據在版本鏈上的不同版本數據。

Innodb引擎SQL執行的BufferPool緩存機制

【1】圖示

【2】說明（為什麼Mysql不能直接更新磁碟上的數據而設置這麼一套複雜的機制來執行SQL？）

　　1）如果來一個請求就直接對磁碟文件進行隨機讀寫，然後更新磁碟文件里的數據，這樣的效率是很低的。因為磁碟隨機讀寫的性能是非常差的，所以直接更新磁碟文件是不能讓資料庫抗住很高併發的。

　　2）Mysql這套機制看起來複雜，但它可以保證每個更新請求都是更新記憶體BufferPool，然後順序寫日誌文件，同時還能保證各種異常情況下的數據一致性（undoLog針對數據進行恢復，RedoLog對數據進行重做操作）。

　　3）更新記憶體的性能是極高的，然後順序寫磁碟上的日誌文件的性能也是非常高的，要遠高於隨機讀寫磁碟文件。

　　4）正是通過這套機制，才能讓我們的MySQL資料庫在較高配置的機器上每秒可以抗下幾乾甚至上萬的讀寫請求。