Mysql

MySql

1. 事務的四大特性？

事務特性ACID：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。

1.原子性是指事務包含的所有操作要么全部成功，要么全部失敗回滾。

2.一致性是指一個事務執行之前和執行之后都必須處于一致性狀態。比如a與b賬戶共有1000塊，兩人之間轉賬之后無論成功還是失敗,

? 它們的賬戶總和還是1000。

3.隔離性。跟隔離級別相關，如read committed，一個事務只能讀到已經提交的修改。

4.持久性是指一個事務一旦被提交了，那么對數據庫中的數據的改變就是永久性的，即便是在數據庫系統遇到故障的情況下也不會丟失提交事務的操作。

2. 事務隔離級別有哪些？

先了解下幾個概念：臟讀、不可重復讀、幻讀。

臟讀是指在一個事務處理過程里讀取了另一個未提交的事務中的數據。

不可重復讀是指在對于數據庫中的某行記錄，一個事務范圍內多次查詢卻返回了不同的數據值，這是由于在查詢間隔，另一個事務修改了數據并提交了。

幻讀是當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄，當之前的事務再次讀取該范圍的記錄時，會產生幻行，就像產生幻覺一樣，這就是發生了幻讀。

不可重復讀和臟讀的區別是，臟讀是某一事務讀取了另一個事務未提交的臟數據，而不可重復讀則是讀取了前一事務提交的數據。

幻讀和不可重復讀都是讀取了另一條已經提交的事務，不同的是不可重復讀的重點是修改，幻讀的重點在于新增或者刪除。

事務隔離就是為了解決上面提到的臟讀、不可重復讀、幻讀這幾個問題。

MySQL數據庫為我們提供的四種隔離級別：

Serializable (串行化)：通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。

Repeatable read (可重復讀)：MySQL的默認事務隔離級別，它確保同一事務的多個實例在并發讀

? 取數據時，會看到同樣的數據行，解決了不可重復讀的問題。

Read committed (讀已提交)：一個事務只能看見已經提交事務所做的改變。可避免臟讀的發生。

Read uncommitted (讀未提交)：所有事務都可以看到其他未提交事務的執行結果。

查看隔離級別：

select @@transaction_isolation;

設置隔離級別：

set session transaction isolation level read uncommitted;

3. 索引

3.1. 什么是索引？

索引是存儲引擎用于提高數據庫表的訪問速度的一種數據結構。

3.2. 索引的優缺點？

優點：

加快數據查找的速度

為用來排序或者是分組的字段添加索引，可以加快分組和排序的速度

加速表與表之間的連接

缺點：

建立索引需要占用物理空間

會降低表的增刪改的效率，因為每次對表記錄進行增刪改，需要進行動態維護索引，導致增刪改時

間變長

3.3. 索引的作用？

數據是存儲在磁盤上的，查詢數據時，如果沒有索引，會加載所有的數據到內存，依次進行檢索，讀取

磁盤次數較多。有了索引，就不需要加載所有數據，因為B+樹的高度一般在2-4層，最多只需要讀取2-4

次磁盤，查詢速度大大提升。

select @@transaction_isolation;

set session transaction isolation level read uncommitted;

3.4. 什么情況下需要建索引？

經常用于查詢的字段
經常用于連接的字段（如外鍵）建立索引，可以加快連接的速度
經常需要排序的字段建立索引，因為索引已經排好序，可以加快排序查詢速度

3.5. 什么情況下不建索引？

where條件中用不到的字段不適合建立索引
表記錄較少
需要經常增刪改
參與列計算的列不適合建索引
區分度不高的字段不適合建立索引，性別等

3.6. 索引的數據結構

索引的數據結構主要有B+樹和哈希表，對應的索引分別為B+樹索引和哈希索引。InnoDB引擎的索引類

型有B+樹索引和哈希索引，默認的索引類型為B+樹索引。

B+****樹索引

B+ 樹是基于B 樹和葉子節點順序訪問指針進行實現，它具有B樹的平衡性，并且通過順序訪問指針來提

高區間查詢的性能。

在 B+ 樹中，節點中的 key 從左到右遞增排列，如果某個指針的左右相鄰 key 分別是 keyi 和 keyi+1，則

該指針指向節點的所有 key 大于等于 keyi 且小于等于 keyi+1。

進行查找操作時，首先在根節點進行二分查找，找到key所在的指針，然后遞歸地在指針所指向的節點進

行查找。直到查找到葉子節點，然后在葉子節點上進行二分查找，找出 key 所對應的數據項。

3.7. Hash索引和B+樹索引的區別？

哈希索引不支持排序，因為哈希表是無序的。

哈希索引不支持范圍查找。

哈希索引不支持模糊查詢及多列索引的最左前綴匹配。

因為哈希表中會存在哈希沖突，所以哈希索引的性能是不穩定的，而B+樹索引的性能是相對穩定

的，每次查詢都是從根節點到葉子節點。

3.8. 為什么B+樹比B樹更適合實現數據庫索引？

由于B+樹的數據都存儲在葉子結點中，葉子結點均為索引，方便掃庫，只需要掃一遍葉子結點即

可，但是B樹因為其分支結點同樣存儲著數據，我們要找到具體的數據，需要進行一次中序遍歷按

序來掃，所以B+樹更加適合在區間查詢的情況，而在數據庫中基于范圍的查詢是非常頻繁的，所以

通常B+樹用于數據庫索引。

B+樹的節點只存儲索引key值，具體信息的地址存在于葉子節點的地址中。這就使以頁為單位的索

引中可以存放更多的節點。減少更多的I/O支出。

B+樹的查詢效率更加穩定，任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查

詢的路徑長度相同，導致每一個數據的查詢效率相當。

3.9. 索引有什么分類？

主鍵索引：名為primary的唯一非空索引，不允許有空值。
唯一索引：索引列中的值必須是唯一的，但是允許為空值。唯一索引和主鍵索引的區別是：

UNIQUE 約束的列可以為null且可以存在多個null值。UNIQUE KEY的用途：唯一標識數據庫表中

的每條記錄，主要是用來防止數據重復插入。創建唯一索引的SQL語句如下：

組合索引：在表中的多個字段組合上創建的索引，只有在查詢條件中使用了這些字段的左邊字段

時，索引才會被使用，使用組合索引時遵循最左前綴原則。

全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT類型字段上使用全文索

引。

3.10. 什么是最左匹配原則？

如果 SQL 語句中用到了組合索引中的最左邊的索引，那么這條 SQL 語句就可以利用這個組合索引去進

行匹配。當遇到范圍查詢(>、<、between、like)就會停止匹配，后面的字段不會用到索引。

對(a,b,c)建立索引，查詢條件使用 a/ab/abc 會走索引，使用 bc 不會走索引。

對(a,b,c,d)建立索引，查詢條件為 a = 1 and b = 2 and c > 3 and d = 4 ，那么，a,b,c三個字段能

用到索引，而d就匹配不到。因為遇到了范圍查詢！

Index_comment:

Visible: YES

Expression: NULL

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);如下圖，對(a, b) 建立索引，a 在索引樹中是全局有序的，而 b 是全局無序，局部有序（當a相等時，會

對b進行比較排序）。直接執行 b = 2 這種查詢條件沒有辦法利用索引。

從局部來看，當a的值確定的時候，b是有序的。例如a = 1時，b值為1，2是有序的狀態。當a=2時候，b

的值為1,4也是有序狀態。因此，你執行 a = 1 and b = 2 是a,b字段能用到索引的。而你執行 a > 1

and b = 2 時，a字段能用到索引，b字段用不到索引。因為a的值此時是一個范圍，不是固定的，在這

個范圍內b值不是有序的，因此b字段用不上索引。

3.11. 什么是聚集索引？

InnoDB使用表的主鍵構造主鍵索引樹，同時葉子節點中存放的即為整張表的記錄數據。聚集索引葉子節

點的存儲是邏輯上連續的，使用雙向鏈表連接，葉子節點按照主鍵的順序排序，因此對于主鍵的排序查

找和范圍查找速度比較快。

聚集索引的葉子節點就是整張表的行記錄。InnoDB 主鍵使用的是聚簇索引。聚集索引要比非聚集索引

查詢效率高很多。

對于InnoDB來說，聚集索引一般是表中的主鍵索引，如果表中沒有顯示指定主鍵，則會選擇表中的第一

個不允許為NULL的唯一索引。如果沒有主鍵也沒有合適的唯一索引，那么innodb內部會生成一個隱藏

的主鍵作為聚集索引，這個隱藏的主鍵長度為6個字節，它的值會隨著數據的插入自增。

3.12. 什么是覆蓋索引？

select的數據列只用從索引中就能夠取得，不需要回表進行二次查詢，換句話說查詢列要被所使用的索

引覆蓋。對于innodb表的二級索引，如果索引能覆蓋到查詢的列，那么就可以避免對主鍵索引的二次查

詢。

不是所有類型的索引都可以成為覆蓋索引。覆蓋索引要存儲索引列的值，而哈希索引、全文索引不存儲

索引列的值，所以MySQL只能使用b+樹索引做覆蓋索引。

對于使用了覆蓋索引的查詢，在查詢前面使用explain，輸出的extra列會顯示為 using index 。

比如 user_like 用戶點贊表，組合索引為(user_id, blog_id)，user_id和blog_id都不為null。

Extra中為 Using index ，查詢的列被索引覆蓋，并且where篩選條件符合最左前綴原則，通過索引查

找就能直接找到符合條件的數據，不需要回表查詢數據。

Extra中為 Using where; Using index ，查詢的列被索引覆蓋，where篩選條件不符合最左前綴原

則，無法通過索引查找找到符合條件的數據，但可以通過索引掃描找到符合條件的數據，也不需要回表

查詢數據。

3.13. 索引的設計原則？

索引列的區分度越高，索引的效果越好。比如使用性別這種區分度很低的列作為索引，效果就會很

差。

盡量使用短索引，對于較長的字符串進行索引時應該指定一個較短的前綴長度，因為較小的索引涉

及到的磁盤I/O較少，并且索引高速緩存中的塊可以容納更多的鍵值，會使得查詢速度更快。

索引不是越多越好，每個索引都需要額外的物理空間，維護也需要花費時間。

利用最左前綴原則。

3.14. 索引什么時候會失效？

導致索引失效的情況：

對于組合索引，不是使用組合索引最左邊的字段，則不會使用索引

以%開頭的like查詢如 %abc ，無法使用索引；非%開頭的like查詢如 abc% ，相當于范圍查詢，會使

用索引

查詢條件中列類型是字符串，沒有使用引號，可能會因為類型不同發生隱式轉換，使索引失效

判斷索引列是否不等于某個值時

explain select blog_id from user_like where user_id = 13;

explain select user_id from user_like where blog_id = 1;對索引列進行運算

查詢條件使用or連接，也會導致索引失效

3.15. 什么是前綴索引？

有時需要在很長的字符列上創建索引，這會造成索引特別大且慢。使用前綴索引可以避免這個問題。

前綴索引是指對文本或者字符串的前幾個字符建立索引，這樣索引的長度更短，查詢速度更快。

創建前綴索引的關鍵在于選擇足夠長的前綴以保證較高的索引選擇性。索引選擇性越高查詢效率就越

高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的數據行。

// email列創建前綴索引 

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

4.常見的存儲引擎

MySQL中常用的四種存儲引擎分別是： MyISAM存儲引擎、innoDB存儲引擎、MEMORY存儲引擎、

ARCHIVE存儲引擎。MySQL 5.5版本后默認的存儲引擎為InnoDB。

InnoDB****存儲引擎

InnoDB是MySQL默認的事務型存儲引擎，使用最廣泛，基于聚簇索引建立的。InnoDB內部做了很多優

化，如能夠自動在內存中創建自適應hash索引，以加速讀操作。

優點：支持事務和崩潰修復能力。InnoDB引入了行級鎖和外鍵約束。

缺點：占用的數據空間相對較大。

適用場景：需要事務支持，并且有較高的并發讀寫頻率。

MyISAM****存儲引擎

數據以緊密格式存儲。對于只讀數據，或者表比較小、可以容忍修復操作，可以使用MyISAM引擎。

MyISAM會將表存儲在兩個文件中，數據文件.MYD和索引文件.MYI。

優點：訪問速度快。

缺點：MyISAM不支持事務和行級鎖，不支持崩潰后的安全恢復，也不支持外鍵。

適用場景：對事務完整性沒有要求；只讀的數據，或者表比較小，可以忍受修復repair操作。

MyISAM特性：

MyISAM對整張表加鎖，而不是針對行。讀取數據時會對需要讀到的所有表加共享鎖，寫入時則對

表加排它鎖。但在讀取表記錄的同時，可以往表中插入新的記錄（并發插入）。

對于MyISAM表，MySQL可以手動或者自動執行檢查和修復操作。執行表的修復可能會導致數據丟

失，而且修復操作非常慢。可以通過 CHECK TABLE tablename 檢查表的錯誤，如果有錯誤執行

REPAIR TABLE tablename 進行修復。

MEMORY****存儲引擎

MEMORY引擎將數據全部放在內存中，訪問速度較快，但是一旦系統奔潰的話，數據都會丟失。

MEMORY引擎默認使用哈希索引，將鍵的哈希值和指向數據行的指針保存在哈希索引中。

優點：訪問速度較快。

缺點：

// email列創建前綴索引

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

哈希索引數據不是按照索引值順序存儲，無法用于排序。
不支持部分索引匹配查找，因為哈希索引是使用索引列的全部內容來計算哈希值的。
只支持等值比較，不支持范圍查詢。
當出現哈希沖突時，存儲引擎需要遍歷鏈表中所有的行指針，逐行進行比較，直到找到符合條件的

行。

ARCHIVE****存儲引擎

該存儲引擎非常適合存儲大量獨立的、作為歷史記錄的數據。ARCHIVE提供了壓縮功能，擁有高效的插

入速度，但是這種引擎不支持索引，所以查詢性能較差。

5. MyISAM和InnoDB的區別？

是否支持行級鎖 : MyISAM 只有表級鎖，而 InnoDB 支持行級鎖和表級鎖，默認為行級鎖。
是否支持事務和崩潰后的安全恢復： MyISAM 注重性能，每次查詢具有原子性，其執行速度比

InnoDB 類型更快，但是不提供事務支持。而 InnoDB 提供事務支持，具有事務、回滾和崩潰修復

能力。

是否支持外鍵： MyISAM 不支持，而 InnoDB 支持。
是否支持MVCC ： MyISAM 不支持， InnoDB 支持。應對高并發事務，MVCC比單純的加鎖更高

效。

MyISAM 不支持聚集索引， InnoDB 支持聚集索引。

MyISAM 引擎主鍵索引和其他索引區別不大，葉子節點都包含索引值和行指針。

innoDB 引擎二級索引葉子存儲的是索引值和主鍵值（不是行指針），這樣可以減少行移動和

數據頁分裂時二級索引的維護工作。

6.MVCC實現原理

MVCC( Multiversion concurrency control ) 就是同一份數據保留多版本的一種方式，進而實現并發

控制。在查詢的時候，通過read view和版本鏈找到對應版本的數據。作用：提升并發性能。對于高并發場景，MVCC比行級鎖更有效、開銷更小。

MVCC 實現原理如下：

MVCC 的實現依賴于版本鏈，版本鏈是通過表的三個隱藏字段實現。

DB_TRX_ID ：當前事務id，通過事務id的大小判斷事務的時間順序。

DB_ROLL_PRT ：回滾指針，指向當前行記錄的上一個版本，通過這個指針將數據的多個版本連接

在一起構成undo log版本鏈。

DB_ROLL_ID ：主鍵，如果數據表沒有主鍵，InnoDB會自動生成主鍵。

每條表記錄大概是這樣的：

使用事務更新行記錄的時候，就會生成版本鏈，執行過程如下：

用排他鎖鎖住該行；
將該行原本的值拷貝到 undo log，作為舊版本用于回滾；
修改當前行的值，生成一個新版本，更新事務id，使回滾指針指向舊版本的記錄，這樣就形成一條

版本鏈。

下面舉個例子方便大家理解。

初始數據如下，其中DB_ROW_ID和DB_ROLL_PTR為空。

事務A對該行數據做了修改，將age修改為12，效果如下：

之后事務B也對該行記錄做了修改，將age修改為8，效果如下：

此時undo log有兩行記錄，并且通過回滾指針連在一起。

接下來了解下read view的概念。

read view 可以理解成對數據在每個時刻的狀態拍成“照片”記錄下來。這樣獲取某時刻的數據時就還是

原來的”照片“上的數據，是不會變的。

在 read view 內部維護一個活躍事務鏈表，表示生成 read view 的時候還在活躍的事務。這個鏈表包

含在創建 read view 之前還未提交的事務，不包含創建 read view 之后提交的事務。

不同隔離級別創建read view的時機不同。

read committed：每次執行select都會創建新的read_view，保證能讀取到其他事務已經提交的修

改。

repeatable read：在一個事務范圍內，第一次select時更新這個read_view，以后不會再更新，后

續所有的select都是復用之前的read_view。這樣可以保證事務范圍內每次讀取的內容都一樣，即

可重復讀。

read view的記錄篩選方式

前提： DATA_TRX_ID 表示每個數據行的最新的事務ID； up_limit_id 表示當前快照中的最先開始的事

務； low_limit_id 表示當前快照中的最慢開始的事務，即最后一個事務。

如果 DATA_TRX_ID < up_limit_id ：說明在創建 read view 時，修改該數據行的事務已提交，該

版本的記錄可被當前事務讀取到。

如果 DATA_TRX_ID >= low_limit_id ：說明當前版本的記錄的事務是在創建 read view 之后生

成的，該版本的數據行不可以被當前事務訪問。此時需要通過版本鏈找到上一個版本，然后重新判

斷該版本的記錄對當前事務的可見性。

如果 up_limit_id <= DATA_TRX_ID < low_limit_i ：

需要在活躍事務鏈表中查找是否存在ID為 DATA_TRX_ID 的值的事務。
如果存在，因為在活躍事務鏈表中的事務是未提交的，所以該記錄是不可見的。此時需要通過

版本鏈找到上一個版本，然后重新判斷該版本的可見性。

如果不存在，說明事務trx_id 已經提交了，這行記錄是可見的。

總結：InnoDB 的 MVCC 是通過 read view 和版本鏈實現的，版本鏈保存有歷史版本記錄，通過 read

view 判斷當前版本的數據是否可見，如果不可見，再從版本鏈中找到上一個版本，繼續進行判斷，直

到找到一個可見的版本。

7. 快照讀和當前讀

表記錄有兩種讀取方式。

快照讀：讀取的是快照版本。普通的SELECT就是快照讀。通過MVCC來進行并發控制的，不用加

鎖。

當前讀：讀取的是最新版本。 UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、

SELECT … FOR UPDATE 是當前讀。

快照讀情況下，InnoDB通過mvcc機制避免了幻讀現象。而mvcc機制無法避免當前讀情況下出現的幻讀

現象。因為當前讀每次讀取的都是最新數據，這時如果兩次查詢中間有其它事務插入數據，就會產生幻

讀。

那么MySQL如何實現避免幻讀？

在快照讀情況下，MySQL通過mvcc來避免幻讀。

在當前讀情況下，MySQL通過next-key來避免幻讀（加行鎖和間隙鎖來實現的）。

next-key包括兩部分：行鎖和間隙鎖。行鎖是加在索引上的鎖，間隙鎖是加在索引之間的。

Serializable 隔離級別也可以避免幻讀，會鎖住整張表，并發性極低，一般不會使用。

8. 共享鎖和排他鎖

SELECT 的讀取鎖定主要分為兩種方式：共享鎖和排他鎖。

這兩種方式主要的不同在于 LOCK IN SHARE MODE 多個事務同時更新同一個表單時很容易造成死鎖。

申請排他鎖的前提是，沒有線程對該結果集的任何行數據使用排它鎖或者共享鎖，否則申請會受到阻

塞。在進行事務操作時，MySQL會對查詢結果集的每行數據添加排它鎖，其他線程對這些數據的更改或

刪除操作會被阻塞（只能讀操作），直到該語句的事務被commit語句或rollback語句結束為止。

SELECT... FOR UPDATE 使用注意事項：

insert into user(user_name, user_password, user_mail, user_state) 

values('tyson', 'a', 'a', 0); 

update user set user_name = 'a'; 

select * from table where id<6 lock in share mode;--共享鎖 

select * from table where id<6 for update;--排他鎖

for update 僅適用于Innodb，且必須在事務范圍內才能生效。
根據主鍵進行查詢，查詢條件為 like或者不等于，主鍵字段產生表鎖。
根據非索引字段進行查詢，name字段產生表鎖。

9. 大表怎么優化？

某個表有近千萬數據，查詢比較慢，如何優化？

當MySQL單表記錄數過大時，數據庫的性能會明顯下降，一些常見的優化措施如下：

限定數據的范圍。比如：用戶在查詢歷史信息的時候，可以控制在一個月的時間范圍內；

讀寫分離：經典的數據庫拆分方案，主庫負責寫，從庫負責讀；

通過分庫分表的方式進行優化，主要有垂直拆分和水平拆分。

10. MySQL 執行計劃了解嗎？

通過 explain 命令獲取 select 語句的執行計劃，了解 select 語句以下信息：

表的加載順序

sql 的查詢類型

可能用到哪些索引，實際上用到哪些索引

讀取的行數

...

詳細內容可以參考我的另一篇文章

11. bin log/redo log/undo log

MySQL日志主要包括查詢日志、慢查詢日志、事務日志、錯誤日志、二進制日志等。其中比較重要的是

bin log（二進制日志）和 redo log（重做日志）和 undo log（回滾日志）。

bin log

二進制日志（bin log）是MySQL數據庫級別的文件，記錄對MySQL數據庫執行修改的所有操作，不會

記錄select和show語句，主要用于恢復數據庫和同步數據庫。

redo log

重做日志（redo log）是Innodb引擎級別，用來記錄Innodb存儲引擎的事務日志，不管事務是否提交都

會記錄下來，用于數據恢復。當數據庫發生故障，InnoDB存儲引擎會使用redo log恢復到發生故障前的

時刻，以此來保證數據的完整性。將參數 innodb_flush_log_at_tx_commit 設置為1，那么在執行

commit時會將redo log同步寫到磁盤。

undo log

除了記錄redo log外，當進行數據修改時還會記錄undo log，undo log用于數據的撤回操作，它保留了

記錄修改前的內容。通過undo log可以實現事務回滾，并且可以根據undo log回溯到某個特定的版本的

數據，實現MVCC。

12. bin log和redo log有什么區別？

bin log會記錄所有日志記錄，包括innoDB、MyISAM等存儲引擎的日志；redo log只記錄innoDB

自身的事務日志。

bin log只在事務提交前寫入到磁盤，一個事務只寫一次；而在事務進行過程，會有redo log不斷寫

入磁盤。

binlog 是邏輯日志，記錄的是SQL語句的原始邏輯；redo log 是物理日志，記錄的是在某個數據頁

上做了什么修改。

13. 講一下MySQL架構？

MySQL主要分為 Server 層和存儲引擎層：

Server 層：主要包括連接器、查詢緩存、分析器、優化器、執行器等，所有跨存儲引擎的功能都

在這一層實現，比如存儲過程、觸發器、視圖，函數等，還有一個通用的日志模塊 binglog 日志模

塊。

存儲引擎：主要負責數據的存儲和讀取。server 層通過api與存儲引擎進行通信。

Server 層基本組件

連接器： 當客戶端連接 MySQL 時，server層會對其進行身份認證和權限校驗。

查詢緩存****: 執行查詢語句的時候，會先查詢緩存，先校驗這個 sql 是否執行過，如果有緩存這個

sql，就會直接返回給客戶端，如果沒有命中，就會執行后續的操作。

分析器****: 沒有命中緩存的話，SQL 語句就會經過分析器，主要分為兩步，詞法分析和語法分析，先

看 SQL 語句要做什么，再檢查 SQL 語句語法是否正確。

優化器： 優化器對查詢進行優化，包括重寫查詢、決定表的讀寫順序以及選擇合適的索引等，生成

執行計劃。

執行器： 首先執行前會校驗該用戶有沒有權限，如果沒有權限，就會返回錯誤信息，如果有權限，

就會根據執行計劃去調用引擎的接口，返回結果。

14. 分庫分表

當單表的數據量達到1000W或100G以后，優化索引、添加從庫等可能對數據庫性能提升效果不明顯，

此時就要考慮對其進行切分了。切分的目的就在于減少數據庫的負擔，縮短查詢的時間。

數據切分可以分為兩種方式：垂直劃分和水平劃分。

垂直劃分

垂直劃分數據庫是根據業務進行劃分，例如購物場景，可以將庫中涉及商品、訂單、用戶的表分別劃分

出成一個庫，通過降低單庫的大小來提高性能，但這種方式并沒有解決高數據量帶來的性能損耗。同樣

的，分表的情況就是將一個大表根據業務功能拆分成一個個子表，例如商品基本信息和商品描述，商品

基本信息一般會展示在商品列表，商品描述在商品詳情頁，可以將商品基本信息和商品描述拆分成兩張

表。

優點：行記錄變小，數據頁可以存放更多記錄，在查詢時減少I/O次數。

缺點：

主鍵出現冗余，需要管理冗余列；

會引起表連接JOIN操作，可以通過在業務服務器上進行join來減少數據庫壓力；

依然存在單表數據量過大的問題。

水平劃分

水平劃分是根據一定規則，例如時間或id序列值等進行數據的拆分。比如根據年份來拆分不同的數據

庫。每個數據庫結構一致，但是數據得以拆分，從而提升性能。

優點：單庫（表）的數據量得以減少，提高性能；切分出的表結構相同，程序改動較少。

缺點：

分片事務一致性難以解決

跨節點join性能差，邏輯復雜

數據分片在擴容時需要遷移

15. 什么是分區表？

分區表是一個獨立的邏輯表，但是底層由多個物理子表組成。

當查詢條件的數據分布在某一個分區的時候，查詢引擎只會去某一個分區查詢，而不是遍歷整個表。在

管理層面，如果需要刪除某一個分區的數據，只需要刪除對應的分區即可。

16. 分區表類型

按照范圍分區。

CREATE TABLE test_range_partition( 

id INT auto_increment, 

createdate DATETIME, 

primary key (id,createdate) 

)

PARTITION BY RANGE (TO_DAYS(createdate) ) ( 

PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ), 

PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ), 

PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ), 

PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ), 

PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ), 

PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),在 /var/lib/mysql/data/ 可以找到對應的數據文件，每個分區表都有一個使用#分隔命名的表文

件：

list分區。對于List分區，分區字段必須是已知的，如果插入的字段不在分區時枚舉值中，將無法插

入。

hash分區，可以將數據均勻地分布到預先定義的分區中。

17. 分區的問題？

打開和鎖住所有底層表的成本可能很高。當查詢訪問分區表時，MySQL需要打開并鎖住所有的底層

表，這個操作在分區過濾之前發生，所以無法通過分區過濾來降低此開銷，會影響到查詢速度。可

以通過批量操作來降低此類開銷，比如批量插入、LOAD DATA INFILE和一次刪除多行數據。

維護分區的成本可能很高。例如重組分區，會先創建一個臨時分區，然后將數據復制到其中，最后

再刪除原分區。

所有分區必須使用相同的存儲引擎。

PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ), 

PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ), 

PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ), 

PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ), 

PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ), 

PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') ) 

);

-rw-r----- 1 MySQL MySQL 65 Mar 14 21:47 db.opt 

-rw-r----- 1 MySQL MySQL 8598 Mar 14 21:50 test_range_partition.frm 

-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 

test_range_partition#P#p201801.ibd 

-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 

test_range_partition#P#p201802.ibd 

-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 

test_range_partition#P#p201803.ibd 

... 

create table test_list_partiotion 

( 

id int auto_increment, 

data_type tinyint, 

primary key(id,data_type) 

)partition by list(data_type) 

( 

partition p0 values in (0,1,2,3,4,5,6), 

partition p1 values in (7,8,9,10,11,12), 

partition p2 values in (13,14,15,16,17) 

); 

create table test_hash_partiotion 

( 

id int auto_increment, 

create_date datetime, 

primary key(id,create_date) 

)partition by hash(year(create_date)) partitions 10;

18. 查詢語句執行流程？

查詢語句的執行流程如下：權限校驗、查詢緩存、分析器、優化器、權限校驗、執行器、引擎。

舉個例子，查詢語句如下：

首先檢查權限，沒有權限則返回錯誤；
MySQL以前會查詢緩存，緩存命中則直接返回，沒有則執行下一步；
詞法分析和語法分析。提取表名、查詢條件，檢查語法是否有錯誤；
兩種執行方案，先查 id > 1 還是 name = '大彬' ，優化器根據自己的優化算法選擇執行效率最

好的方案；

校驗權限，有權限就調用數據庫引擎接口，返回引擎的執行結果。

19. 更新語句執行過程？

更新語句執行流程如下：分析器、權限校驗、執行器、引擎、redo log(prepare 狀態)、binlog、redo

log(commit狀態)

舉個例子，更新語句如下：

先查詢到 id 為1的記錄，有緩存會使用緩存。
拿到查詢結果，將 name 更新為大彬，然后調用引擎接口，寫入更新數據，innodb 引擎將數據保

存在內存中，同時記錄 redo log，此時 redo log 進入 prepare 狀態。

執行器收到通知后記錄 binlog，然后調用引擎接口，提交 redo log 為提交狀態。
更新完成。

為什么記錄完 redo log，不直接提交，先進入prepare狀態？

假設先寫 redo log 直接提交，然后寫 binlog，寫完 redo log 后，機器掛了，binlog 日志沒有被寫入，

那么機器重啟后，這臺機器會通過 redo log 恢復數據，但是這個時候 binlog 并沒有記錄該數據，后續

進行機器備份的時候，就會丟失這一條數據，同時主從同步也會丟失這一條數據。

20. exist和in的區別？

exists 用于對外表記錄做篩選。

exists 會遍歷外表，將外查詢表的每一行，代入內查詢進行判斷。當 exists 里的條件語句能夠返回記錄

行時，條件就為真，返回外表當前記錄。反之如果exists里的條件語句不能返回記錄行，條件為假，則

外表當前記錄被丟棄。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in 是先把后邊的語句查出來放到臨時表中，然后遍歷臨時表，將臨時表的每一行，代入外查詢去查找。

子查詢的表大的時候，使用exists可以有效減少總的循環次數來提升速度；當外查詢的表大的時候，使

用IN可以有效減少對外查詢表循環遍歷來提升速度。

select * from Awhere id in(select id from B)

21. MySQL中int(10)和char(10)的區別？

int(10)中的10表示的是顯示數據的長度，而char(10)表示的是存儲數據的長度。

22. truncate、delete與drop區別？

相同點：

truncate和不帶where子句的delete、以及drop都會刪除表內的數據。
drop、truncate都是DDL語句（數據定義語言），執行后會自動提交。

不同點：

truncate 和 delete 只刪除數據不刪除表的結構；drop 語句將刪除表的結構被依賴的約束、觸發

器、索引；

一般來說，執行速度: drop > truncate > delete。

23. having和where區別？

二者作用的對象不同，where子句作用于表和視圖，having作用于組。

WHERE在數據分組前進行過濾，HAVING在數據分組后進行過濾。

24. 什么是MySQL主從同步？

主從同步使得數據可以從一個數據庫服務器復制到其他服務器上，在復制數據時，一個服務器充當主服

務器（master），其余的服務器充當從服務器（slave）。

因為復制是異步進行的，所以從服務器不需要一直連接著主服務器，從服務器甚至可以通過撥號斷斷續

續地連接主服務器。通過配置文件，可以指定復制所有的數據庫，某個數據庫，甚至是某個數據庫上的

某個表。

25. 為什么要做主從同步？

讀寫分離，使數據庫能支撐更大的并發。
在主服務器上生成實時數據，而在從服務器上分析這些數據，從而提高主服務器的性能。
數據備份，保證數據的安全。

26. 樂觀鎖和悲觀鎖是什么？

數據庫中的并發控制是確保在多個事務同時存取數據庫中同一數據時不破壞事務的隔離性和統一性以及

數據庫的統一性。樂觀鎖和悲觀鎖是并發控制主要采用的技術手段。

悲觀鎖：假定會發生并發沖突，在查詢完數據的時候就把事務鎖起來，直到提交事務。實現方式：

使用數據庫中的鎖機制。

樂觀鎖：假設不會發生并發沖突，只在提交操作時檢查是否數據是否被修改過。給表增加version

字段，在修改提交之前檢查version與原來取到的version值是否相等，若相等，表示數據沒有被修

改，可以更新，否則，數據為臟數據，不能更新。實現方式：樂觀鎖一般使用版本號機制或CAS算

法實現。

27. 用過processlist嗎？

show processlist 或 show full processlist 可以查看當前 MySQL 是否有壓力，正在運行的

sql，有沒有慢 SQL 正在執行。返回參數如下：

id - 線程ID，可以用： kill id; 殺死一個線程，很有用

db - 數據庫

user - 用戶host - 連庫的主機IP

command - 當前執行的命令，比如最常見的：Sleep，Query，Connect 等

time - 消耗時間，單位秒，很有用

state - 執行狀態

sleep，線程正在等待客戶端發送新的請求

query，線程正在查詢或者正在將結果發送到客戶端

Sorting result，線程正在對結果集進行排序

Locked，線程正在等待鎖

info - 執行的SQL語句，很有用

28.鎖

鎖是計算機為了協調多個進程或線程并發訪問某個資源的機制。

按照鎖的粒度分為三類：

全局鎖：鎖定數據庫中的所有表，做全庫的邏輯備份，對所有表進行鎖定
表級鎖：鎖住整張表
行級鎖：鎖住對應的行數據

28.1表級鎖

28.1.1表鎖

表鎖分為兩類：

共享鎖（讀鎖）：允許不同事務加入共享鎖讀取，阻止其他事務修改或加入排他鎖。
排他鎖（寫鎖）：允許獲取排他鎖的事務更新數據，阻止其他事務共享讀鎖和排他寫鎖。

讀鎖不會阻塞其他客戶端的讀，但是會阻塞寫；寫鎖既會阻塞其他客戶端的讀，又會阻塞其他客戶端的寫。

# 加鎖
lock tables table_name read/write;
# 釋放鎖
unlock tables;

28.1.2元數據鎖（MDL）

? MDL加鎖過程是系統自動控制，不需要顯式使用，訪問一張表時會自動加上。MDL鎖主要作用時維護表元數據（表結構）的數據一致性，在表上有活動事務的時候，不可以對元數據進行寫操作。簡單來說，表存在未提交的事務，不可以去修改表的結構。為了避免DML與DDL沖突，保證讀寫的正確性。

? 在MySQL5.5中引入了MDL，當對一個表做 增刪改查操作 的時候，加MDL 讀鎖；當 要對表做結構變更操作的時候，加MDL寫鎖，會阻塞全部。

# 查看元數據鎖
select object_type,object_schema,object_name,lock_type,lock_duration from performance_shema.metadata_locks;

28.1.3意向鎖

? 舉個例子，線程A先開啟事務，執行update操作，然后它會對這一行加上行鎖，緊接著它會對這整張表加上意向鎖。之后，線程B來對這張表進行加表鎖，此時它檢查這張表意向鎖的情況，如果當前加的鎖與意向鎖兼容就會成功，不兼容就會處于阻塞狀態，阻塞到線程A的事務提交，釋放行鎖和意向鎖。

意向共享鎖（IS）：事務有意向對表中的某些行加共享鎖，必須先取得該表的IS鎖。
意向排它鎖（IX）：事務有意向對表中的某些行加排他鎖，必須先取得該表的IX鎖。

	意向共享鎖（IS）	意向排他鎖（IX）
表共享鎖（S）	兼容	互斥
表排它鎖（X）	互斥	互斥

意向鎖不會與行鎖互斥。

# 查看意向鎖及行鎖的加鎖情況
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_shema.data_locks;

意向鎖解決的問題：意向鎖是由InnoDB引擎來完成的，意向鎖的存在使得在加表鎖的過程中，不再需要去對每行數據檢查是否加鎖，使用意向鎖來減少表鎖的檢查。為了避免DML在執行時，加的行鎖與表鎖的沖突。

28.2行級鎖

? InnoDB的數據是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖。InnoDB行鎖分為3種情形。

行鎖（Record Lock）：對索引項加鎖，防止其他事務的update、delete，在RC、RR的隔離級別下支持。
間隙鎖（Gap Lock）：對索引項之間的“間隙”、第一條記錄前的“間隙”或最后一條記錄后的“間隙”加鎖。
臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，對記錄及前面的間隙加鎖，在RR隔離級別下支持。

Gap鎖/Next-key鎖

? 默認情況下，InnoDB在RR隔離級別下運行，InnoDB使用next-key鎖進行搜索和索引掃描，以防止幻讀。

索引上的等值查詢（唯一查詢），給不存在的記錄加鎖時，優化為間隙鎖。
索引上的等值查詢（普通索引），向右遍歷時最后一個值不滿足查詢需求時，next-key lock退化為間隙鎖。
索引上的范圍查詢（唯一索引），會訪問到不滿足條件的第一個值為止。

使用間隙鎖的目的時防止其他事務插入間隙。間隙鎖可以共存，一個事務采用的間隙鎖不會阻止另一個事務在同一間隙上采用間隙鎖。

posted @ 2024-07-07 21:40 二價亞鐵閱讀(96) 評論(0) 收藏舉報

刷新頁面返回頂部

二價亞鐵.