MySQL高階篇筆記

wang03發表於2022-02-13

MySql筆記

黑馬MySQL筆記

MySQL體系結構

連線層

最上層是一些客戶端和連結服務，主要完成一些類似於連線處理、授權認證、及相關的安全方案。伺服器也會為安全接入的每個客戶端驗證它所具有的操作許可權。
服務層

第二層架構主要完成大多數的核心服務功能，如SQL介面，並完成快取的查詢，SQL的分析和優化，部分內建函式的執行。所有跨存儲引擎的功能也在這一層實現，如過程、函式等。
引擎層

儲存引擎真正的負責了MySQL中資料的儲存和提取，伺服器通過API和儲存引擎進行通訊。不同的儲存引擎具有不同的功能，這樣我們可以根據自己的需要，來選取合適的儲存引擎。
儲存層

主要是將資料儲存在檔案系統之上，並完成與儲存引擎的互動。

show engines檢視當前資料庫支援的儲存引擎

儲存引擎特點

InnoDB底層檔案

xxx.ibd：xxx代表的是表名，innoDB引擎的每張表都會對應這樣一個表空間檔案，儲存該表的表結構（frm、sdi）、資料和索引。

是否使用獨立表空間可以通過innodb_file_per_table 來設定。

在配置檔案（my.cnf）中設定： innodb_file_per_table = 1 #1為開啟，0為關閉

通過show variables like '%per_table%';查詢當前狀態

也可以通過set global innodb_file_per_table =OFF;臨時修改，重啟後失效。

MyISAM底層檔案

xxx.sdi：儲存表結構資訊

xxx.MYD: 儲存資料

xxx.MYI: 儲存索引

索引

慢查詢日誌

#通過 show [session|global] status 命令可以提供伺服器狀態資訊。通過如下指令，可以檢視當前資料庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次：
SHOW GLOBAL STATUS LIKE 'Com_______';

慢查詢日誌記錄了所有執行時間超過引數 long_query_time 設定值並且掃描記錄數不小於 min_examined_row_limit的所有的SQL語句的日誌，預設未開啟。long_query_time 預設為 10 秒，最小為 0，精度可以到微秒。

 #慢查詢日誌
 show variables like 'slow_query_log';

在MySQL的配置檔案（/etc/my.cnf）中配置如下資訊：

#開啟MYSQL慢查詢日誌
slow_query_log=1

#設定慢查詢日誌的時間為2秒,SQL語句執行時間超過2秒，就會被是為慢查詢，記錄慢查詢日誌
long_query_time=2

重啟MYSQL服務生效。

預設慢查詢日誌檔案位置/var/lib/mysql/localhost-slow.log

預設情況下，不會記錄管理語句，也不會記錄不使用索引進行查詢的查詢。可以使用log_slow_admin_statements和更改此行為 log_queries_not_using_indexes，如下所述。

profile詳情

show profiles 能夠在做SQL優化時幫助我們瞭解時間都耗費到哪裡去了。

通過have_profiling引數，能夠看到當前MySQL是否支援。

檢視當前profiling是否已經開啟，預設是0,表示未開啟。

開始profiling

檢視SQL語句的耗時情況

#檢視每一條SQL的耗時
show profiles;

#檢視指定query_id的SQL語句各個階段的耗時情況
show profile for query 2;

#檢視指定query_id的SQL語句各個階段的CPU消耗情況 
show profile cpu for query 2;

explain執行計劃

EXPLAIN 或者 DESC命令獲取 MySQL 如何執行 SELECT 語句的資訊，包括在 SELECT 語句執行過程中表如何連線和連線的順序。

EXPLAIN 執行計劃各欄位含義：

➢ Id: select查詢的序列號，表示查詢中執行select子句或者是操作表的順序(id相同，執行順序從上到下；id不同，值越大，越先執行)。

➢ select_type: 表示 SELECT 的型別，常見的取值有 SIMPLE（簡單表，即不使用表連線或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION 中的第二個或者後面的查詢語句）、SUBQUERY（SELECT/WHERE之後包含了子查詢）等

➢ type: 表示連線型別，效能由好到差的連線型別為NULL、system、const、eq_ref、ref、range、 index、all 。

➢ possible_key: 顯示可能應用在這張表上的索引，一個或多個。

➢ key: 實際使用的索引，如果為NULL，則沒有使用索引。

➢ key_len: 表示索引中使用的位元組數，該值為索引欄位最大可能長度，並非實際使用長度，在不損失精確性的前提下，長度越短越好。

➢ rows: MySQL認為必須要執行查詢的行數，在innodb引擎的表中，是一個估計值，可能並不總是準確的。

➢ filtered: 表示返回結果的行數佔需讀取行數的百分比， filtered 的值越大越好。

索引使用

最左字首法則

如果索引了多列（聯合索引），要遵守最左字首法則。最左字首法則指的是查詢從索引的最左列開始，並且不跳過索引中的列。如果跳躍某一列，索引將部分失效(後面的欄位索引失效)。
範圍查詢

聯合索引中，出現範圍查詢(>,<)，範圍查詢右側的列索引失效
索引列運算

不要在索引列上進行運算操作，索引將失效。
字串不加引號

字串型別欄位使用時，不加引號，索引將失效。
模糊查詢

如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。
or連線的條件

用or分割開的條件，如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。
資料分佈影響

如果MySQL評估使用索引比全表更慢，則不使用索引。
SQL提示

use index：建議使用指定索引

ignore index：忽略指定索引

force index：強制使用指定索引
覆蓋索引

儘量使用覆蓋索引（查詢使用了索引，並且需要返回的列，在該索引中已經全部能夠找到），減少一次主鍵上的select 。
字首索引

當欄位型別為字串（varchar，text等）時，有時候需要索引很長的字串，這會讓索引變得很大，查詢時，浪費大量的磁碟IO，影響查詢效率。此時可以只將字串的一部分字首，建立索引，這樣可以大大節約索引空間，從而提高索引效率。

➢ 語法
```
create index index_name on table_name(clumn(n));
```
➢ 字首長度

可以根據索引的選擇性來決定，而選擇性是指不重複的索引值（基數）和資料表的記錄總數的比值，索引選擇性越高則查詢效率越高，唯一索引的選擇性是1，這是最好的索引選擇性，效能也是最好的
單列索引與聯合索引

單列索引：即一個索引只包含單個列。

聯合索引：即一個索引包含了多個列。

在業務場景中，如果存在多個查詢條件，考慮針對於查詢欄位建立索引時，建議建立聯合索引，而非單列索引。

索引設計原則

針對於資料量較大，且查詢比較頻繁的表建立索引。
針對於常作為查詢條件（where）、排序（order by）、分組（group by）操作的欄位建立索引。
儘量選擇區分度高的列作為索引，儘量建立唯一索引，區分度越高，使用索引的效率越高。
如果是字串型別的欄位，欄位的長度較長，可以針對於欄位的特點，建立字首索引。
儘量使用聯合索引，減少單列索引，查詢時，聯合索引很多時候可以覆蓋索引，節省儲存空間，避免回表，提高查詢效率。
要控制索引的數量，索引並不是多多益善，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率。
如果索引列不能儲存NULL值，請在建立表時使用NOT NULL約束它。當優化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用於查詢。

SQL優化

插入資料

insert優化

➢ 批量插入

➢ 手動提交事務

➢ 主鍵順序插入

大批量插入資料

如果一次性需要插入大批量資料，使用insert語句插入效能較低，此時可以使用MySQL資料庫提供的load指令進行插入。操作如下:

#客戶端連線服務端時，加上引數 --local-infile
 mysql --local-infile -u root -p
#設定全域性引數local_infile為1 ，開啟從本地載入檔案匯入資料的開關
set global local_infile=1;
#執行load指令將準備好的資料，載入到表結構中
load data local infile '/root/load_user_100w_sort.sql'  into table tb_user fields  terminated by ',' lines terminated by '\n';

主鍵順序插入效能高於亂序插入

主要的原因是由於底層資料每一頁在物理磁碟存放是按照主鍵由低到高順序存放的，如果按照主鍵順序插入就類似順序寫入磁碟；如果是亂序插入，就需要調整磁碟上已寫入資料順序。

主鍵優化

資料組織方式

在InnoDB儲存引擎中，表資料都是根據主鍵順序組織存放的，這種儲存方式的表稱為索引組織表(index organized table IOT)。
頁分裂

頁可以為空，也可以填充一半，也可以填充100%。每個頁包含了2-N行資料(如果一行資料多大，會行溢位)，根據主鍵排列。
頁合併

當刪除一行記錄時，實際上記錄並沒有被物理刪除，只是記錄被標記（flaged）為刪除並且它的空間變得允許被其他記錄宣告使用。

當頁中刪除的記錄達到 MERGE_THRESHOLD（預設為頁的50%），InnoDB會開始尋找最靠近的頁（前或後）看看是否可以將兩個頁合併以優化空間使用。
主鍵設計原則

➢ 滿足業務需求的情況下，儘量降低主鍵的長度。

➢ 插入資料時，儘量選擇順序插入，選擇使用AUTO_INCREMENT自增主鍵。

➢ 儘量不要使用UUID做主鍵或者是其他自然主鍵，如身份證號。

➢ 業務操作時，避免對主鍵的修改。

order by優化

① Using filesort:

通過表的索引或全表掃描，讀取滿足條件的資料行，然後在排序緩衝區中完成排序操作，所有不是通過索引直接返回排序結果的排序都叫排序。

② Using index:

通過有序索引順序掃描直接返回有序資料，這種情況即為，不需要額外排序，操作效率高。

➢ 根據排序欄位建立合適的索引，多欄位排序時，也遵循最左字首法則。

➢ 儘量使用覆蓋索引。

➢ 多欄位排序一個升序一個降序，此時需要注意聯合索引在建立時的規則（ASC/DESC ）。

➢ 如果不可避免的出現filesort，大資料量排序時，可以適當增大排序緩衝區大小sort_buffer_size(預設256k) 。

group by優化

➢ 在分組操作時，可以通過索引來提高效率。

➢ 分組操作時，索引的使用也是滿足最左字首法則的。

limit優化

一個常見又非常頭疼的問題就是 limit 2000000,10 ，此時需要MySQL排序前2000010 記錄，僅僅返回2000000 - 2000010 的記錄，其他記錄丟棄，查詢排序的代價非常大。

優化思路: 一般分頁查詢時，通過建立覆蓋索引能夠比較好地提高效能，可以通過覆蓋索引加子查詢形式進行優化。

#這種比較耗時
select * from tb_user limit 10000,10

#這種相對較快
select s.* from tb_user s,(select id from tb_user order by id limit 10000,10) a where s.id=a.id;

count優化

➢ MyISAM 引擎把一個表的總行數存在了磁碟上，因此執行 count(*) 的時候會直接返回這個數，效率很高；

➢ InnoDB 引擎就麻煩了，它執行 count() 的時候，需要把資料一行一行地從引擎裡面讀出來，然後累積計數。

優化思路：自己計數。

count的幾種用法 count優化

➢ count() 是一個聚合函式，對於返回的結果集，一行行地判斷，如果 count 函式的引數不是 NULL，累計值就加 1，否則不加，最後返回累計值。

➢ 用法：count（*）、count（主鍵）、count（欄位）、count（1）
count的幾種用法

➢ count（主鍵）

InnoDB 引擎會遍歷整張表，把每一行的主鍵id 值都取出來，返回給服務層。服務層拿到主鍵後，直接按行進行累加(主鍵不可能為null)

➢ count（欄位）

沒有not null 約束 : InnoDB 引擎會遍歷整張表把每一行的欄位值都取出來，返回給服務層，服務層判斷是否為null，不為null，計數累加。

有not null 約束：InnoDB 引擎會遍歷整張表把每一行的欄位值都取出來，返回給服務層，直接按行進行累加。

➢ count（1）

InnoDB 引擎遍歷整張表，但不取值。服務層對於返回的每一行，放一個數字“1”進去，直接按行進行累加。

➢ count（*）

InnoDB引擎並不會把全部欄位取出來，而是專門做了優化，不取值，服務層直接按行進行累加。

按照效率排序的話，count(欄位) < count(主鍵 id) < count(1) ≈ count(*)，所以儘量使用 count(*)。

update優化

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖 ,並且該索引不能失效，否則會從行鎖升級為表鎖。

這個說法應該是錯誤的，只能說看起來表象是從行鎖升級到了表鎖。具體的細節可以看這裡Innodb到底是怎麼加鎖的 - 掘金 (juejin.cn)

檢視

介紹

檢視（View）是一種虛擬存在的表。檢視中的資料並不在資料庫中實際存在，行和列資料來自定義檢視的查詢中使用的表，並且是在使用視圖時動態生成的。

通俗的講，檢視只儲存了查詢的SQL邏輯，不儲存查詢結果。所以我們在建立檢視的時候，主要的工作就落在建立這條SQL查詢語句上。

建立

#語法
CREATE [ OR REPLACE ] VIEW 檢視名稱 [(列名列表)] AS SELECT語句 [WITH [CASCADED | LOCAL ] CHECK OPTION]

#示例
create or replace view view_tb_user as select id,name from tb_user where id<10;

查詢

#檢視建立檢視語句
SHOW CREATE VIEW 檢視名稱;

#檢視檢視資料
SELECT * FROM 檢視名稱 ...;

修改

#方式一
CREATE [ OR REPLACE ] VIEW 檢視名稱 [(列名列表)] AS SELECT語句 [WITH [CASCADED | LOCAL ] CHECK OPTION]

#方式二
ALTER VIEW 檢視名稱 [(列名列表)] AS SELECT語句 [WITH [CASCADED | LOCAL ] CHECK OPTION]

刪除

DROP VIEW [IF EXISTS] 檢視名稱[,檢視名稱]

檢視的檢查選項

當使用WITH CHECK OPTION子句建立檢視時，MySQL會通過檢視檢查正在更改的每個行，例如插入，更新，刪除，以使其符合檢視的定義。 MySQL允許基於另一個檢視建立檢視，它還會檢查依賴檢視中的規則以保持一致性。

為了確定檢查的範圍，mysql提供了兩個選項： CASCADED 和 LOCAL ，預設值為 CASCADED

檢視的更新

要使檢視可更新，檢視中的行與基礎表中的行之間必須存在一對一的關係。

如果檢視包含以下任何一項，則該檢視不可更新：

聚合函式或視窗函式（SUM()、 MIN()、 MAX()、 COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION 或者 UNION ALL

作用

➢ 簡單

檢視不僅可以簡化使用者對資料的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為檢視，從而使得使用者不必為以後的操作每次指定全部的條件。

➢ 安全

資料庫可以授權，但不能授權到資料庫特定行和特定的列上。通過檢視使用者只能查詢和修改他們所能見到的資料

➢ 資料獨立

檢視可幫助使用者遮蔽真實表結構變化帶來的影響。

系統變數

檢視系統變數

SHOW [SESSION|GLOBAL] VARIABLES; #檢視所有系統變數
SHOW [SESSION|GLOBAL] VARIABLES LIKE '...'; #模糊檢視系統變數
SHOW @@[SESSION|GLOBAL] 系統變數名;           #檢視指定變數的值

設定變數的值

SET [SESSION|GLOBAL] 系統變數名=值；
SET  @@[SESSION|GLOBAL] 系統變數名=值；

注意:

如果沒有指定SESSION/GLOBAL，預設是SESSION，會話變數。

mysql服務重新啟動之後，所設定的全域性引數會失效，要想不失效，可以在 /etc/my.cnf 中配置。

鎖

全域性鎖

全域性鎖就是對整個資料庫例項加鎖，加鎖後整個例項就處於只讀狀態，後續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將被阻塞。

其典型的使用場景是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性檢視，保證資料的完整性。

語法

flush tables with read lock;   #開啟全域性鎖
......                         #只有只讀語句可以執行，所有session會話中的寫的語句都會阻塞
unlock tables;                 #解除全域性鎖

備份資料庫

mysqldump -uroot -p123456 資料庫例項名 >xxx.sql

特點

資料庫中加全域性鎖，是一個比較重的操作，存在以下問題：
1. 如果在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺。
2. 如果在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進位制日誌（binlog），會導致主從延遲。

在InnoDB引擎中，我們可以在備份時加上引數 --single-transaction 引數來完成不加鎖的一致性資料備份。
mysqldump --single-transaction  -uroot -p123456 資料庫例項名 >xxx.sql

表級鎖

表級鎖，每次操作鎖住整張表。鎖定粒度大，發生鎖衝突的概率最高，併發度最低。應用在MyISAM、InnoDB、BDB等儲存引擎中。

對於表級鎖，主要分為以下三類：

表鎖
後設資料鎖（meta data lock，MDL）
意向鎖

表鎖
- 對於表鎖，分為兩類：
  1. 表共享讀鎖（read lock）
  2. 表獨佔寫鎖（write lock）
- 語法：
  1. 加鎖：lock tables 表名... read/write。
  2. 釋放鎖：unlock tables / 客戶端斷開連線。

後設資料鎖（ meta data lock， MDL）

MDL加鎖過程是系統自動控制，無需顯式使用，在訪問一張表的時候會自動加上。MDL鎖主要作用是維護表後設資料的資料一致性，在表上有活動事務的時候，不可以對後設資料進行寫入操作。為了避免DML與DDL衝突，保證讀寫的正確性。

在MySQL5.5中引入了MDL，當對一張表進行增刪改查的時候，加MDL讀鎖(共享)；當對錶結構進行變更操作的時候，加MDL寫鎖(排他)。

對應SQL	鎖型別	說明
lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select ... lock in share mode	SHARED_READ	與SHARED_READ、SHARED_WRITE相容，與EXCLUSIVE互斥
insert 、update、delete、select ... for update	SHARED_WRITE	與SHARED_READ、SHARED_WRITE相容，與EXCLUSIVE互斥
alter table ...	EXCLUSIVE	與其他的MDL都互斥

檢視後設資料鎖

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向鎖

為了避免DML在執行時，加的行鎖與表鎖的衝突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行資料是否加鎖，使用意向鎖來減少表鎖的檢查。
1. 意向共享鎖（IS）：
  
  由語句 select ... lock in share mode新增。
  
  與表鎖共享鎖（read）相容，與表鎖排它鎖（write）互斥。
2. 意向排他鎖（IX）：
  
  由insert、update、delete、select ... for update 新增。
  
  與表鎖共享鎖（read）及排它鎖（write）都互斥。意向鎖之間不會互斥。
檢視意向鎖及行鎖的加鎖情況
```
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
```

行級鎖

行級鎖，每次操作鎖住對應的行資料。鎖定粒度最小，發生鎖衝突的概率最低，併發度最高。應用在InnoDB儲存引擎中。

InnoDB的資料是基於索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖。對於行級鎖，主要分為以下三類：

行鎖（Record Lock）：鎖定單個行記錄的鎖，防止其他事務對此行進行update和delete。在RC、RR隔離級別下都支援。
- 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同資料集的排它鎖。
- 排他鎖（X）：允許獲取排他鎖的事務更新資料，阻止其他事務獲得相同資料集的共享鎖和排他鎖。
預設情況下，InnoDB在 REPEATABLE READ事務隔離級別執行，InnoDB使用 next-key 鎖進行搜尋和索引掃描，以防止幻讀。
1. 針對唯一索引進行檢索時，對已存在的記錄進行等值匹配時，將會自動優化為行鎖。
2. InnoDB的行鎖是針對於索引加的鎖，不通過索引條件檢索資料，那麼InnoDB將對錶中的所有記錄加鎖，此時就會升級為表鎖。
間隙鎖（Gap Lock）：鎖定索引記錄間隙（不含該記錄），確保索引記錄間隙不變，防止其他事務在這個間隙進行insert，產生幻讀。在RR隔離級別下都支援。
臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，同時鎖住資料，並鎖住資料前面的間隙Gap。在RR隔離級別下支援。

預設情況下，InnoDB在 REPEATABLE READ事務隔離級別執行，InnoDB使用 next-key 鎖進行搜尋和索引掃描，以防止幻讀。

索引上的等值查詢(唯一索引)，給不存在的記錄加鎖時, 優化為間隙鎖。

索引上的等值查詢(普通索引)，向右遍歷時最後一個值不滿足查詢需求時，next-key lock 退化為間隙鎖。

索引上的範圍查詢(唯一索引)--會訪問到不滿足條件的第一個值為止。

注意：間隙鎖唯一目的是防止其他事務插入間隙。間隙鎖可以共存，一個事務採用的間隙鎖不會阻止另一個事務在同一間隙上採用間隙鎖。

InnoDB引擎

邏輯儲存結構

架構

MySQL5.5 版本開始，預設使用InnoDB儲存引擎，它擅長事務處理，具有崩潰恢復特性，在日常開發中使用非常廣泛。下面是InnoDB架構圖，左側為記憶體結構，右側為磁碟結構。

架構-記憶體架構

架構-磁碟結構

示例：

執行如下sql：

create tablespace ts_itheima add datafile 'myitheima.ibd'  engine=innodb;
create table a(id int primary key auto_increment,name varchar(32) ) engine=innodb tablespace ts_itheima;

檢視mysql資料檔案目錄，預設在/var/lib/mysql目錄。就會有myitheima.ibd這個檔案

架構-後臺執行緒

檢視innod的狀態資訊

show engine innodb status;

事務原理

事務是一組操作的集合，它是一個不可分割的工作單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要麼同時成功，要麼同時失敗。

特性

• 原子性（Atomicity）：事務是不可分割的最小操作單元，要麼全部成功，要麼全部失敗。

• 一致性（Consistency）：事務完成時，必須使所有的資料都保持一致狀態。

• 隔離性（Isolation）：資料庫系統提供的隔離機制，保證事務在不受外部併發操作影響的獨立環境下執行。

• 永續性（Durability）：事務一旦提交或回滾，它對資料庫中的資料的改變就是永久的。

事務的原子性，一致性，永續性通過redo log、undo log來實現。

事務的隔離性通過鎖，MVCC來實現。

重做日誌，記錄的是事務提交時資料頁的物理修改，是用來實現事務的永續性。

該日誌檔案由兩部分組成：重做日誌緩衝（redo log buffer）以及重做日誌檔案（redo log file）,前者是在記憶體中，後者在磁碟中。當事務提交之後會把所有修改資訊都存到該日誌檔案中, 用於在重新整理髒頁到磁碟,發生錯誤時, 進行資料恢復使用。

回滾日誌，用於記錄資料被修改前的資訊 , 作用包含兩個 : 提供回滾和 MVCC(多版本併發控制) 。

undo log和redo log記錄物理日誌不一樣，它是邏輯日誌。可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。當執行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容並進行回滾。

Undo log銷燬：undo log在事務執行時產生，事務提交時，並不會立即刪除undo log，因為這些日誌可能還用於MVCC。

Undo log儲存：undo log採用段的方式進行管理和記錄，存放在前面介紹的 rollback segment 回滾段中，內部包含1024個undo log segment。

MVCC-基本概念

當前讀

讀取的是記錄的最新版本，讀取時還要保證其他併發事務不能修改當前記錄，會對讀取的記錄進行加鎖。

對於我們日常的操作，如： select ... lock in share mode(共享鎖)，select ... for update、update、insert、delete(排他鎖)都是一種當前讀。
快照讀

簡單的select（不加鎖）就是快照讀，快照讀，讀取的是記錄資料的可見版本，有可能是歷史資料，不加鎖，是非阻塞讀。
- Read Committed：每次select，都生成一個快照讀。
- Repeatable Read：開啟事務後第一個select語句才是快照讀的地方。
- Serializable：快照讀會退化為當前讀。
MVCC

全稱 Multi-Version Concurrency Control，多版本併發控制。指維護一個資料的多個版本，使得讀寫操作沒有衝突，快照讀為MySQL實現 MVCC提供了一個非阻塞讀功能。MVCC的具體實現，還需要依賴於資料庫記錄中的三個隱式欄位、undo log日誌、readView。

MVCC-實現原理

記錄中的隱藏欄位

可以通過idb2sdi命令通過檢視idb檔案(預設在/var/lib/mysql/)，就能看到表結構，其中就有隱藏欄位。比如DB_TRX_ID、DB_ROLL_PTR
ibd2sdi XXX.idb

undo log

回滾日誌，在insert、update、delete的時候產生的便於資料回滾的日誌。

當insert的時候，產生的undo log日誌只在回滾時需要，在事務提交後，可被立即刪除。

而update、delete的時候，產生的undo log日誌不僅在回滾時需要，在快照讀時也需要，不會立即被刪除。

undo log版本鏈

如上圖:

事務2執行時，需要記住修改之前的資料，在回滾時使用。

事務3執行時，由於事務2已經提交，所以它需要在事務2執行結果的基礎上進行操作。所以它的undo log裡面的記錄就是事務2執行的記錄。
事務4執行時，由於事務3已經提交，所以它需要在事務2執行結果的基礎上進行操作。所以它的undo log裡面的記錄就是事務3執行的記錄。

不同事務或相同事務對同一條記錄進行修改，會導致該記錄的undolog生成一條記錄版本連結串列，連結串列的頭部是最新的舊記錄，連結串列尾部是最早的舊記錄。

readview

ReadView（讀檢視）是快照讀 SQL執行時MVCC提取資料的依據，記錄並維護系統當前活躍的事務（未提交的）id。

ReadView中包含了四個核心欄位：

欄位	含義
m_ids	當前活躍的事務ID集合
min_trx_id	最小活躍事務ID
max_trx_id	預分配事務ID，當前最大事務ID+1（因為事務ID是自增的）
creator_trx_id	ReadView建立者的事務ID

上面4個規則，只要滿足1，2，4任何一個就可以訪問對應的資料；不滿足3就要拒絕。

不同的隔離級別，生成ReadView的時機不同：

➢ READ COMMITTED ：在事務中每一次執行快照讀時生成ReadView。

➢ REPEATABLE READ：僅在事務中第一次執行快照讀時生成ReadView，後續複用該ReadView。

如上圖，當前隔離級別是READ COMMITTED時，事務5中的第一次查詢的結果時是事務2提交的記錄。

如上圖，當前隔離界別時READ COMMITTED時，事務5中的第二次查詢的結果時是事務3提交的記錄

當前隔離級別是讀已提交時，事務5中兩次查詢的ReadView是相同的，所以兩次查詢的結果都是事務2提交的記錄。

MySQL管理

Mysql資料庫安裝完成後，自帶了一下四個資料庫，具體作用如下：

資料庫	含義
mysql	儲存MySQL伺服器正常執行所需要的各種資訊（時區、主從、使用者、許可權等）
information_schema	提供了訪問資料庫後設資料的各種表和檢視，包含資料庫，表，欄位型別及訪問許可權等
performance_schema	為MySQL伺服器執行時狀態提供了一個底層監控功能，主要用於手機資料庫伺服器效能引數
sys	包含了一系列方便DBA和開發人員利用performance_schema效能資料庫進行效能調優和診斷的檢視

常用工具:

mysql

該mysql不是指mysql服務，而是指mysql的客戶端工具。

-e選項可以在Mysql客戶端執行SQL語句，而不用連線到MySQL資料庫再執行，對於一些批處理指令碼，這種方式尤其方便。

mysqladmin

可以通過通過mysqladmin --help檢視所有選項

mysqladmin 是一個執行管理操作的客戶端程式。可以用它來檢查伺服器的配置和當前狀態、建立並刪除資料庫等。

mysqlbinlog

由於伺服器生成的二進位制日誌檔案以二進位制格式儲存，所以如果想要檢查這些文字的文字格式，就會使用到mysqlbinlog 日誌管理工具。

如通過mysqlbinlog binlog.000001檢視日誌(在/var/lib/mysql目錄)

mysqlshow

mysqlshow 客戶端物件查詢工具，用來很快地查詢存在哪些資料庫、資料庫中的表、表中的列或者索引。
mysqldump

mysqldump 客戶端工具用來備份資料庫或在不同資料庫之間進行資料遷移。備份內容包含建立表，及插入表的SQL語句。

mysqldump -uroot -p123456 test>test.sql    #備份資料庫test到test.sql
mysqldump -uroot -p123456 -T   /var/lib/mysql-files  test    #將test資料庫的表結構和資料匯出到/var/lib/mysql-files目錄下

mysqlimport/source

mysqlimport 是客戶端資料匯入工具，用來匯入mysqldump 加 -T 引數後匯出的文字檔案。

如果需要匯入sql檔案,可以使用mysql中的source 指令。

MySQL高階學習筆記(二)
2020-11-13
MySql筆記
穀粒商城高階篇筆記1
2020-12-18
筆記
redis學習筆記（詳細）——高階篇
2021-11-11
Redis筆記
MySQL 高階特性篇教程
2022-01-13
MySql
MySQL 優化一（高階篇）
2020-04-10
MySql優化
MySQL 優化（二）（高階篇）
2020-04-10
MySql優化
VIM高階用法筆記【轉】
2013-01-30
筆記
Mssql高階注入筆記(轉)
2007-08-15
SQL筆記
學習筆記|AS入門（五）高階控制元件篇（下）
2017-12-21
筆記控制元件
學習筆記|AS入門（五）高階控制元件篇（中）
2017-12-21
筆記控制元件
學習筆記|AS入門（五）高階控制元件篇（上）
2017-12-21
筆記控制元件
「 MySQL高階篇」MySQL索引原理，設計原則
2022-02-04
MySql索引
MySQL 的主從複製（高階篇）
2020-04-11
MySql
JavaScript高階程式設計筆記
2020-12-17
JavaScript程式設計筆記
《Kafka筆記》3、Kafka高階API
2020-10-20
Kafka筆記API
尚矽谷《MySQL高階特性篇》教程釋出
2022-01-13
MySql
MYSQL 高階
2021-06-01
MySql
MySQL高階
2021-03-07
MySql
mysql總結筆記 -- 索引篇
2022-01-04
MySql筆記索引
React高階實戰 - 我的筆記
2021-09-09
React筆記
swift高階運算子-讀書筆記
2018-09-05
Swift筆記
面試高階演算法梳理筆記
2017-03-24
面試演算法筆記
MySQL 優化三（優化規則）（高階篇）
2020-04-10
MySql優化
高併發設計筆記（續篇）
2019-12-02
筆記
mysql高階特性
2019-01-23
MySql
oracle學習筆記（十一）高階查詢
2019-05-19
Oracle筆記
《JavaScript 高階程式設計》精讀筆記
2019-04-20
JavaScript程式設計筆記
《JavaScript高階程式設計》筆記：DOM（十）
2018-12-21
JavaScript程式設計筆記
Javascript高階程式設計學習筆記
2020-11-07
JavaScript程式設計筆記
js高階物件導向學習筆記
2020-11-24
JS物件筆記
《javascript高階程式設計》筆記：文件模式
2017-08-07
JavaScript程式設計筆記模式
MySQL學習筆記【基礎篇】
2020-08-17
MySql筆記
Mysql學習筆記（安裝篇）
2018-08-25
MySql筆記
nginx高階篇rewrite
2024-03-22
Nginx
《python運維和開發實戰-高階篇》視訊課程筆記
2021-02-20
Python運維筆記
Python學習筆記（進階篇一）
2017-10-19
Python筆記
C#高階程式設計讀書筆記
2018-11-07
C#程式設計筆記
大資料學習筆記（十三）-Hive高階
2017-12-20
大資料筆記Hive

MySQL高階篇筆記

MySQL體系結構

儲存引擎特點

InnoDB底層檔案

MyISAM底層檔案

索引

慢查詢日誌

profile詳情

explain執行計劃

EXPLAIN 執行計劃各欄位含義：

索引使用

最左字首法則

範圍查詢

索引列運算

字串不加引號

模糊查詢

or連線的條件

資料分佈影響

SQL提示

覆蓋索引

字首索引

單列索引與聯合索引