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資料庫在較高配置的機器上每秒可以抗下幾干甚至上萬的讀寫請求，

標籤：Java

上一篇：支持JDK19虛擬執行緒的web框架之四：看原始碼，了解quarkus如何支持虛擬執行緒

下一篇：InputStream--使用詳解