這可能是最全面的MySQL面試八股文了-有解無憂

什么是MySQL

MySQL是一個關系型資料庫，它采用表的形式來存盤資料，你可以理解成是Excel表格，既然是表的形式存盤資料，就有表結構（行和列），行代表每一行資料，列代表該行中的每個值，列上的值是有資料型別的，比如：整數、字串、日期等等，

資料庫的三大范式

第一范式1NF

確保資料庫表欄位的原子性，最全面的Java面試網站

比如欄位 userInfo: 廣東省 10086' ，依照第一范式必須拆分成 userInfo: 廣東省 userTel: 10086兩個欄位，

第二范式2NF

首先要滿足第一范式，另外包含兩部分內容，一是表必須有一個主鍵；二是非主鍵列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分，

舉個例子，假定選課關系表為student_course(student_no, student_name, age, course_name, grade, credit)，主鍵為(student_no, course_name)，其中學分完全依賴于課程名稱，姓名年齡完全依賴學號，不符合第二范式，會導致資料冗余（學生選n門課，姓名年齡有n條記錄）、插入例外（插入一門新課，因為沒有學號，無法保存新課記錄）等問題，

應該拆分成三個表：學生：student(stuent_no, student_name, 年齡)；課程：course(course_name, credit)；選課關系：student_course_relation(student_no, course_name, grade)，

第三范式3NF

首先要滿足第二范式，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴，即不能存在：非主鍵列 A 依賴于非主鍵列 B，非主鍵列 B 依賴于主鍵的情況，

假定學生關系表為Student(student_no, student_name, age, academy_id, academy_telephone)，主鍵為"學號"，其中學院id依賴于學號，而學院地點和學院電話依賴于學院id，存在傳遞依賴，不符合第三范式，

可以把學生關系表分為如下兩個表：學生：(student_no, student_name, age, academy_id)；學院：(academy_id, academy_telephone)，

2NF和3NF的區別？

2NF依據是非主鍵列是否完全依賴于主鍵，還是依賴于主鍵的一部分，
3NF依據是非主鍵列是直接依賴于主鍵，還是直接依賴于非主鍵，

本文已經收錄到Github倉庫，該倉庫包含計算機基礎、Java基礎、多執行緒、JVM、資料庫、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服務、設計模式、架構、校招社招分享等核心知識點，歡迎star~

Github地址

如果訪問不了Github，可以訪問gitee地址，

gitee地址

事務的四大特性？

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

原子性是指事務包含的所有操作要么全部成功，要么全部失敗回滾，
一致性是指一個事務執行之前和執行之后都必須處于一致性狀態，比如a與b賬戶共有1000塊，兩人之間轉賬之后無論成功還是失敗，它們的賬戶總和還是1000，
隔離性，跟隔離級別相關，如read committed，一個事務只能讀到已經提交的修改，
持久性是指一個事務一旦被提交了，那么對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作，

事務隔離級別有哪些？

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

臟讀是指在一個事務處理程序里讀取了另一個未提交的事務中的資料，
不可重復讀是指在對于資料庫中的某行記錄，一個事務范圍內多次查詢卻回傳了不同的資料值，這是由于在查詢間隔，另一個事務修改了資料并提交了，
幻讀是當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄，對幻讀的正確理解是一個事務內的讀取操作的結論不能支撐之后業務的執行，假設事務要新增一條記錄，主鍵為id，在新增之前執行了select，沒有發現id為xxx的記錄，但插入時出現主鍵沖突，這就屬于幻讀，讀取不到記錄卻發現主鍵沖突是因為記錄實際上已經被其他的事務插入了，但當前事務不可見，

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

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

MySQL資料庫為我們提供的四種隔離級別：

Serializable (串行化)：通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題，
Repeatable read (可重復讀)：MySQL的默認事務隔離級別，它確保同一事務的多個實體在并發讀取資料時，會看到同樣的資料行，解決了不可重復讀的問題，
Read committed (讀已提交)：一個事務只能看見已經提交事務所做的改變，可避免臟讀的發生，
Read uncommitted (讀未提交)：所有事務都可以看到其他未提交事務的執行結果，

查看隔離級別：

select @@transaction_isolation;

設定隔離級別：

set session transaction isolation level read uncommitted;

生產環境資料庫一般用的什么隔離級別呢？

生產環境大多使用RC，為什么不是RR呢？

可重復讀(Repeatable Read)，簡稱為RR
讀已提交(Read Commited)，簡稱為RC

緣由一：在RR隔離級別下，存在間隙鎖，導致出現死鎖的幾率比RC大的多！
緣由二：在RR隔離級別下，條件列未命中索引會鎖表！而在RC隔離級別下，只鎖行!

也就是說，RC的并發性高于RR，

并且大部分場景下，不可重復讀問題是可以接受的，畢竟資料都已經提交了，讀出來本身就沒有太大問題！

最全面的Java面試網站

編碼和字符集的關系

我們平時可以在編輯器上輸入各種中文英文字母，但這些都是給人讀的，不是給計算機讀的，其實計算機真正保存和傳輸資料都是以二進制0101的格式進行的，

那么就需要有一個規則，把中文和英文字母轉化為二進制，其中d對應十六進制下的64，它可以轉換為01二進制的格式，于是字母和數字就這樣一一對應起來了，這就是ASCII編碼格式，

它用一個位元組，也就是8位來標識字符，基礎符號有128個，擴展符號也是128個，也就只能表示下英文字母和數字，

這明顯不夠用，于是，為了標識中文，出現了GB2312的編碼格式，為了標識希臘語，出現了greek編碼格式，為了標識俄語，整了cp866編碼格式，

為了統一它們，于是出現了Unicode編碼格式，它用了2~4個位元組來表示字符，這樣理論上所有符號都能被收錄進去，并且它還完全兼容ASCII的編碼，也就是說，同樣是字母d，在ASCII用64表示，在Unicode里還是用64來表示，

但不同的地方是ASCII編碼用1個位元組來表示，而Unicode用則兩個位元組來表示，

同樣都是字母d，unicode比ascii多使用了一個位元組，如下：

D   ASCII:           01100100
D Unicode:  00000000 01100100

可以看到，上面的unicode編碼，前面的都是0，其實用不上，但還占了個位元組，有點浪費，如果我們能做到該隱藏時隱藏，這樣就能省下不少空間，按這個思路，就是就有了UTF-8編碼，

總結一下，按照一定規則把符號和二進制碼對應起來，這就是編碼，而把n多這種已經編碼的字符聚在一起，就是我們常說的字符集，

比如utf-8字符集就是所有utf-8編碼格式的字符的合集，

想看下mysql支持哪些字符集，可以執行 show charset;

給大家分享一個Github倉庫，上面有大彬整理的300多本經典的計算機書籍PDF，包括C語言、C++、Java、Python、前端、資料庫、作業系統、計算機網路、資料結構和演算法、機器學習、編程人生等，可以star一下，下次找書直接在上面搜索，倉庫持續更新中~

Github地址

utf8和utf8mb4的區別

上面提到utf-8是在unicode的基礎上做的優化，既然unicode有辦法表示所有字符，那utf-8也一樣可以表示所有字符，為了避免混淆，我在后面叫它大utf8，

mysql支持的字符集中有utf8和utf8mb4，

先說utf8mb4編碼，mb4就是most bytes 4的意思，從上圖最右邊的Maxlen可以看到，它最大支持用4個位元組來表示字符，它幾乎可以用來表示目前已知的所有的字符，

再說mysql字符集里的utf8，它是資料庫的默認字符集，但注意，此utf8非彼utf8，我們叫它小utf8字符集，為什么這么說，因為從Maxlen可以看出，它最多支持用3個位元組去表示字符，按utf8mb4的命名方式，準確點應該叫它utf8mb3，

utf8 就像是閹割版的utf8mb4，只支持部分字符，比如emoji表情，它就不支持，

而mysql支持的字符集里，第三列，collation，它是指字符集的比較規則，

比如，"debug"和"Debug"是同一個單詞，但它們大小寫不同，該不該判為同一個單詞呢，

這時候就需要用到collation了，

通過SHOW COLLATION WHERE Charset = 'utf8mb4';可以查看到utf8mb4下支持什么比較規則，

如果collation = utf8mb4_general_ci，是指使用utf8mb4字符集的前提下，挨個字符進行比較（general），并且不區分大小寫（_ci，case insensitice），

這種情況下，"debug"和"Debug"是同一個單詞，

如果改成collation=utf8mb4_bin，就是指挨個比較二進制位大小，

于是"debug"和"Debug"就不是同一個單詞，

那utf8mb4對比utf8有什么劣勢嗎？

我們知道資料庫表里，欄位型別如果是char(2)的話，里面的2是指字符個數，也就是說不管這張表用的是什么編碼的字符集，都能放上2個字符，

而char又是固定長度，為了能放下2個utf8mb4的字符，char會默認保留2*4（maxlen=4）= 8個位元組的空間，

如果是utf8mb3，則會默認保留 2 * 3 (maxlen=3) = 6個位元組的空間，也就是說，在這種情況下，utf8mb4會比utf8mb3多使用一些空間，

索引

什么是索引？

索引是存盤引擎用于提高資料庫表的訪問速度的一種資料結構，它可以比作一本字典的目錄，可以幫你快速找到對應的記錄，

索引一般存盤在磁盤的檔案中，它是占用物理空間的，

索引的優缺點？

優點：

加快資料查找的速度
為用來排序或者是分組的欄位添加索引，可以加快分組和排序的速度
加快表與表之間的連接

缺點：

建立索引需要占用物理空間
會降低表的增刪改的效率，因為每次對表記錄進行增刪改，需要進行動態維護索引，導致增刪改時間變長

索引的作用？

資料是存盤在磁盤上的，查詢資料時，如果沒有索引，會加載所有的資料到記憶體，依次進行檢索，讀取磁盤次數較多，有了索引，就不需要加載所有資料，因為B+樹的高度一般在2-4層，最多只需要讀取2-4次磁盤，查詢速度大大提升，

什么情況下需要建索引？

經常用于查詢的欄位
經常用于連接的欄位建立索引，可以加快連接的速度
經常需要排序的欄位建立索引，因為索引已經排好序，可以加快排序查詢速度

什么情況下不建索引？

where條件中用不到的欄位不適合建立索引
表記錄較少，比如只有幾百條資料，沒必要加索引，
需要經常增刪改，需要評估是否適合加索引
參與列計算的列不適合建索引
區分度不高的欄位不適合建立索引，如性別，只有男/女/未知三個值，加了索引，查詢效率也不會提高，

索引的資料結構

索引的資料結構主要有B+樹和哈希表，對應的索引分別為B+樹索引和哈希索引，InnoDB引擎的索引型別有B+樹索引和哈希索引，默認的索引型別為B+樹索引，

B+樹索引

B+ 樹是基于B 樹和葉子節點順序訪問指標進行實作，它具有B樹的平衡性，并且通過順序訪問指標來提高區間查詢的性能，

在 B+ 樹中，節點中的 key 從左到右遞增排列，如果某個指標的左右相鄰 key 分別是 key_i 和 key_i+1，則該指標指向節點的所有 key 大于等于 key_i 且小于等于 key_i+1，

進行查找操作時，首先在根節點進行二分查找，找到key所在的指標，然后遞回地在指標所指向的節點進行查找，直到查找到葉子節點，然后在葉子節點上進行二分查找，找出key所對應的資料項，

MySQL 資料庫使用最多的索引型別是BTREE索引，底層基于B+樹資料結構來實作，

mysql> show index from blog\G;
*************************** 1. row ***************************
        Table: blog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: blog_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

哈希索引

哈希索引是基于哈希表實作的，對于每一行資料，存盤引擎會對索引列進行哈希計算得到哈希碼，并且哈希演算法要盡量保證不同的列值計算出的哈希碼值是不同的，將哈希碼的值作為哈希表的key值，將指向資料行的指標作為哈希表的value值，這樣查找一個資料的時間復雜度就是O(1)，一般多用于精確查找，

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

哈希索引不支持排序，因為哈希表是無序的，
哈希索引不支持范圍查找，
哈希索引不支持模糊查詢及多列索引的最左前綴匹配，
因為哈希表中會存在哈希沖突，所以哈希索引的性能是不穩定的，而B+樹索引的性能是相對穩定的，每次查詢都是從根節點到葉子節點，

為什么B+樹比B樹更適合實作資料庫索引？

由于B+樹的資料都存盤在葉子結點中，葉子結點均為索引，方便掃庫，只需要掃一遍葉子結點即可，但是B樹因為其分支結點同樣存盤著資料，我們要找到具體的資料，需要進行一次中序遍歷按序來掃，所以B+樹更加適合在區間查詢的情況，而在資料庫中基于范圍的查詢是非常頻繁的，所以通常B+樹用于資料庫索引，
B+樹的節點只存盤索引key值，具體資訊的地址存在于葉子節點的地址中，這就使以頁為單位的索引中可以存放更多的節點，減少更多的I/O支出，
B+樹的查詢效率更加穩定，任何關鍵字的查找必須走一條從根結點到葉子結點的路，所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當，

索引有什么分類？

1、主鍵索引：名為primary的唯一非空索引，不允許有空值，

2、唯一索引：索引列中的值必須是唯一的，但是允許為空值，唯一索引和主鍵索引的區別是：唯一索引欄位可以為null且可以存在多個null值，而主鍵索引欄位不可以為null，唯一索引的用途：唯一標識資料庫表中的每條記錄，主要是用來防止資料重復插入，創建唯一索引的SQL陳述句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3、組合索引：在表中的多個欄位組合上創建的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用，使用組合索引時需遵循最左前綴原則，

4、全文索引：只能在CHAR、VARCHAR和TEXT型別欄位上使用全文索引，

5、普通索引：普通索引是最基本的索引，它沒有任何限制，值可以為空，

什么是最左匹配原則？

如果 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無法使用索引，因為遇到了范圍查詢，

如下圖，對(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欄位無法使用索引，

什么是聚集索引？

InnoDB使用表的主鍵構造主鍵索引樹，同時葉子節點中存放的即為整張表的記錄資料，聚集索引葉子節點的存盤是邏輯上連續的，使用雙向鏈表連接，葉子節點按照主鍵的順序排序，因此對于主鍵的排序查找和范圍查找速度比較快，

聚集索引的葉子節點就是整張表的行記錄，InnoDB 主鍵使用的是聚簇索引，聚集索引要比非聚集索引查詢效率高很多，

對于InnoDB來說，聚集索引一般是表中的主鍵索引，如果表中沒有顯示指定主鍵，則會選擇表中的第一個不允許為NULL的唯一索引，如果沒有主鍵也沒有合適的唯一索引，那么InnoDB內部會生成一個隱藏的主鍵作為聚集索引，這個隱藏的主鍵長度為6個位元組，它的值會隨著資料的插入自增，

什么是覆寫索引？

select的資料列只用從索引中就能夠取得，不需要回表進行二次查詢，也就是說查詢列要被所使用的索引覆寫，對于innodb表的二級索引，如果索引能覆寫到查詢的列，那么就可以避免對主鍵索引的二次查詢，

不是所有型別的索引都可以成為覆寫索引，覆寫索引要存盤索引列的值，而哈希索引、全文索引不存盤索引列的值，所以MySQL使用b+樹索引做覆寫索引，

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

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

explain select blog_id from user_like where user_id = 13;

explain結果的Extra列為Using index，查詢的列被索引覆寫，并且where篩選條件符合最左前綴原則，通過索引查找就能直接找到符合條件的資料，不需要回表查詢資料，

explain select user_id from user_like where blog_id = 1;

explain結果的Extra列為Using where; Using index，查詢的列被索引覆寫，where篩選條件不符合最左前綴原則，無法通過索引查找找到符合條件的資料，但可以通過索引掃描找到符合條件的資料，也不需要回表查詢資料，

索引的設計原則？

對于經常作為查詢條件的欄位，應該建立索引，以提高查詢速度
為經常需要排序、分組和聯合操作的欄位建立索引
索引列的區分度越高，索引的效果越好，比如使用性別這種區分度很低的列作為索引，效果就會很差，
避免給"大欄位"建立索引，盡量使用資料量小的欄位作為索引，因為MySQL在維護索引的時候是會將欄位值一起維護的，那這樣必然會導致索引占用更多的空間，另外在排序的時候需要花費更多的時間去對比，
盡量使用短索引，對于較長的字串進行索引時應該指定一個較短的前綴長度，因為較小的索引涉及到的磁盤I/O較少，查詢速度更快，
索引不是越多越好，每個索引都需要額外的物理空間，維護也需要花費時間，
頻繁增刪改的欄位不要建立索引，假設某個欄位頻繁修改，那就意味著需要頻繁的重建索引，這必然影響MySQL的性能
利用最左前綴原則，

索引什么時候會失效？

導致索引失效的情況：

對于組合索引，不是使用組合索引最左邊的欄位，則不會使用索引
以%開頭的like查詢如%abc，無法使用索引；非%開頭的like查詢如abc%，相當于范圍查詢，會使用索引
查詢條件中列型別是字串，沒有使用引號，可能會因為型別不同發生隱式轉換，使索引失效
判斷索引列是否不等于某個值時
對索引列進行運算
查詢條件使用or連接，也會導致索引失效

什么是前綴索引？

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

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

創建前綴索引的關鍵在于選擇足夠長的前綴以保證較高的索引選擇性，索引選擇性越高查詢效率就越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的資料行，

建立前綴索引的方式：

// email列創建前綴索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

索引下推

參考我的另一篇文章：圖解索引下推！

常見的存盤引擎有哪些？

MySQL中常用的四種存盤引擎分別是： MyISAM、InnoDB、MEMORY、ARCHIVE，MySQL 5.5版本后默認的存盤引擎為InnoDB，

InnoDB存盤引擎

InnoDB是MySQL默認的事務型存盤引擎，使用最廣泛，基于聚簇索引建立的，InnoDB內部做了很多優化，如能夠自動在記憶體中創建自適應hash索引，以加速讀操作，

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

缺點：占用的資料空間相對較大，

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

MyISAM存盤引擎

資料以緊密格式存盤，對于只讀資料，或者表比較小、可以容忍修復操作，可以使用MyISAM引擎，MyISAM會將表存盤在兩個檔案中，資料檔案.MYD和索引檔案.MYI，

優點：訪問速度快，

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

適用場景：對事務完整性沒有要求；表的資料都會只讀的，

MEMORY存盤引擎

MEMORY引擎將資料全部放在記憶體中，訪問速度較快，但是一旦系統奔潰的話，資料都會丟失，

MEMORY引擎默認使用哈希索引，將鍵的哈希值和指向資料行的指標保存在哈希索引中，

優點：訪問速度較快，

缺點：

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

ARCHIVE存盤引擎

ARCHIVE存盤引擎非常適合存盤大量獨立的、作為歷史記錄的資料，ARCHIVE提供了壓縮功能，擁有高效的插入速度，但是這種引擎不支持索引，所以查詢性能較差，

MyISAM和InnoDB的區別？

存盤結構的區別，每個MyISAM在磁盤上存盤成三個檔案，檔案的名字以表的名字開始，擴展名指出檔案型別， .frm檔案存盤表定義，資料檔案的擴展名為.MYD (MYData)，索引檔案的擴展名是.MYI (MYIndex)，InnoDB所有的表都保存在同一個資料檔案中（也可能是多個檔案，或者是獨立的表空間檔案），InnoDB表的大小只受限于作業系統檔案的大小，一般為2GB，
存盤空間的區別，MyISAM支持支持三種不同的存盤格式：靜態表(默認，但是注意資料末尾不能有空格，會被去掉)、動態表、壓縮表，當表在創建之后并匯入資料之后，不會再進行修改操作，可以使用壓縮表，極大的減少磁盤的空間占用，InnoDB需要更多的記憶體和存盤，它會在主記憶體中建立其專用的緩沖池用于高速緩沖資料和索引，
可移植性、備份及恢復，MyISAM資料是以檔案的形式存盤，所以在跨平臺的資料轉移中會很方便，在備份和恢復時可單獨針對某個表進行操作，對于InnoDB，可行的方案是拷貝資料檔案、備份 binlog，或者用mysqldump，在資料量達到幾十G的時候就相對麻煩了，
是否支持行級鎖，MyISAM 只支持表級鎖，用戶在操作myisam表時，select，update，delete，insert陳述句都會給表自動加鎖，如果加鎖以后的表滿足insert并發的情況下，可以在表的尾部插入新的資料，而InnoDB 支持行級鎖和表級鎖，默認為行級鎖，行鎖大幅度提高了多用戶并發操作的性能，
是否支持事務和崩潰后的安全恢復， MyISAM 不提供事務支持，而InnoDB 提供事務支持，具有事務、回滾和崩潰修復能力，
是否支持外鍵，MyISAM不支持，而InnoDB支持，
是否支持MVCC，MyISAM不支持，InnoDB支持，應對高并發事務，MVCC比單純的加鎖更高效，
是否支持聚集索引，MyISAM不支持聚集索引，InnoDB支持聚集索引，
全文索引，MyISAM支持 FULLTEXT型別的全文索引，InnoDB不支持FULLTEXT型別的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好，
表主鍵，MyISAM允許沒有任何索引和主鍵的表存在，索引都是保存行的地址，對于InnoDB，如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6位元組的主鍵(用戶不可見)，
表的行數，MyISAM保存有表的總行數，如果select count(*) from table;會直接取出該值，InnoDB沒有保存表的總行數，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了where條件后，MyISAM和InnoDB處理的方式都一樣，

MySQL有哪些鎖？

按鎖粒度分類，有行級鎖、表級鎖和頁級鎖，

行級鎖是mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖，行級鎖能大大減少資料庫操作的沖突，其加鎖粒度最小，但加鎖的開銷也最大，行級鎖的型別主要有三類：
- Record Lock，記錄鎖，也就是僅僅把一條記錄鎖上；
- Gap Lock，間隙鎖，鎖定一個范圍，但是不包含記錄本身；
- Next-Key Lock：Record Lock + Gap Lock 的組合，鎖定一個范圍，并且鎖定記錄本身，
表級鎖是mysql中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實作簡單，資源消耗較少，被大部分mysql引擎支持，最常使用的MyISAM與InnoDB都支持表級鎖定，
頁級鎖是 MySQL 中鎖定粒度介于行級鎖和表級鎖中間的一種鎖，表級鎖速度快，但沖突多，行級沖突少，但速度慢，因此，采取了折衷的頁級鎖，一次鎖定相鄰的一組記錄，

按鎖級別分類，有共享鎖、排他鎖和意向鎖，

共享鎖又稱讀鎖，是讀取操作創建的鎖，其他用戶可以并發讀取資料，但任何事務都不能對資料進行修改（獲取資料上的排他鎖），直到已釋放所有共享鎖，
排他鎖又稱寫鎖、獨占鎖，如果事務T對資料A加上排他鎖后，則其他事務不能再對A加任何型別的封鎖，獲準排他鎖的事務既能讀資料，又能修改資料，
意向鎖是表級鎖，其設計目的主要是為了在一個事務中揭示下一行將要被請求鎖的型別，InnoDB 中的兩個表鎖：

意向共享鎖（IS）：表示事務準備給資料行加入共享鎖，也就是說一個資料行加共享鎖前必須先取得該表的IS鎖；

意向排他鎖（IX）：類似上面，表示事務準備給資料行加入排他鎖，說明事務在一個資料行加排他鎖前必須先取得該表的IX鎖，

意向鎖是 InnoDB 自動加的，不需要用戶干預，

對于INSERT、UPDATE和DELETE，InnoDB 會自動給涉及的資料加排他鎖；對于一般的SELECT陳述句，InnoDB 不會加任何鎖，事務可以通過以下陳述句顯式加共享鎖或排他鎖，

共享鎖：SELECT … LOCK IN SHARE MODE;

排他鎖：SELECT … FOR UPDATE;

MVCC 實作原理？

MVCC(Multiversion concurrency control) 就是同一份資料保留多版本的一種方式，進而實作并發控制，在查詢的時候，通過read view和版本鏈找到對應版本的資料，

作用：提升并發性能，對于高并發場景，MVCC比行級鎖開銷更小，

MVCC 實作原理如下：

MVCC 的實作依賴于版本鏈，版本鏈是通過表的三個隱藏欄位實作，

DB_TRX_ID：當前事務id，通過事務id的大小判斷事務的時間順序，
DB_ROLL_PTR：回滾指標，指向當前行記錄的上一個版本，通過這個指標將資料的多個版本連接在一起構成undo log版本鏈，
DB_ROW_ID：主鍵，如果資料表沒有主鍵，InnoDB會自動生成主鍵，

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

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

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

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

1、初始資料如下，其中DB_ROW_ID和DB_ROLL_PTR為空，

2、事務A對該行資料做了修改，將age修改為12，效果如下：

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

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

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

read view可以理解成將資料在每個時刻的狀態拍成“照片”記錄下來，在獲取某時刻t的資料時，到t時間點拍的“照片”上取資料，

在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：
1. 需要在活躍事務鏈表中查找是否存在ID為DATA_TRX_ID的值的事務，
2. 如果存在，因為在活躍事務鏈表中的事務是未提交的，所以該記錄是不可見的，此時需要通過版本鏈找到上一個版本，然后重新判斷該版本的可見性，
3. 如果不存在，說明事務trx_id 已經提交了，這行記錄是可見的，

總結：InnoDB 的MVCC是通過 read view 和版本鏈實作的，版本鏈保存有歷史版本記錄，通過read view 判斷當前版本的資料是否可見，如果不可見，再從版本鏈中找到上一個版本，繼續進行判斷，直到找到一個可見的版本，

快照讀和當前讀

表記錄有兩種讀取方式，

快照讀：讀取的是快照版本，普通的SELECT就是快照讀，通過mvcc來進行并發控制的，不用加鎖，
當前讀：讀取的是最新版本，UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是當前讀，

快照讀情況下，InnoDB通過mvcc機制避免了幻讀現象，而mvcc機制無法避免當前讀情況下出現的幻讀現象，因為當前讀每次讀取的都是最新資料，這時如果兩次查詢中間有其它事務插入資料，就會產生幻讀，

下面舉個例子說明下：

1、首先，user表只有兩條記錄，具體如下：

2、事務a和事務b同時開啟事務start transaction；

3、事務a插入資料然后提交；

insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);

4、事務b執行全表的update；

update user set user_name = 'a';

5、事務b然后執行查詢，查到了事務a中插入的資料，（下圖左邊是事務b，右邊是事務a，事務開始之前只有兩條記錄，事務a插入一條資料之后，事務b查詢出來是三條資料）

以上就是當前讀出現的幻讀現象，

那么MySQL是如何避免幻讀？

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

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

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

共享鎖和排他鎖

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

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

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

申請排他鎖的前提是，沒有執行緒對該結果集的任何行資料使用排它鎖或者共享鎖，否則申請會受到阻塞，在進行事務操作時，MySQL會對查詢結果集的每行資料添加排它鎖，其他執行緒對這些資料的更改或洗掉操作會被阻塞（只能讀操作），直到該陳述句的事務被commit陳述句或rollback陳述句結束為止，

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

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

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，

bin log和redo log有什么區別？

bin log會記錄所有日志記錄，包括InnoDB、MyISAM等存盤引擎的日志；redo log只記錄innoDB自身的事務日志，
bin log只在事務提交前寫入到磁盤，一個事務只寫一次；而在事務進行程序，會有redo log不斷寫入磁盤，
bin log是邏輯日志，記錄的是SQL陳述句的原始邏輯；redo log是物理日志，記錄的是在某個資料頁上做了什么修改，

講一下MySQL架構？

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

Server 層：主要包括連接器、查詢快取、分析器、優化器、執行器等，所有跨存盤引擎的功能都在這一層實作，比如存盤程序、觸發器、視圖，函式等，還有一個通用的日志模塊 binglog 日志模塊，
存盤引擎：主要負責資料的存盤和讀取，server 層通過api與存盤引擎進行通信，

Server 層基本組件

連接器： 當客戶端連接 MySQL 時，server層會對其進行身份認證和權限校驗，
查詢快取: 執行查詢陳述句的時候，會先查詢快取，先校驗這個 sql 是否執行過，如果有快取這個 sql，就會直接回傳給客戶端，如果沒有命中，就會執行后續的操作，
分析器: 沒有命中快取的話，SQL 陳述句就會經過分析器，主要分為兩步，詞法分析和語法分析，先看 SQL 陳述句要做什么，再檢查 SQL 陳述句語法是否正確，
優化器： 優化器對查詢進行優化，包括重寫查詢、決定表的讀寫順序以及選擇合適的索引等，生成執行計劃，
執行器： 首先執行前會校驗該用戶有沒有權限，如果沒有權限，就會回傳錯誤資訊，如果有權限，就會根據執行計劃去呼叫引擎的介面，回傳結果，

分庫分表

當單表的資料量達到1000W或100G以后，優化索引、添加從庫等可能對資料庫性能提升效果不明顯，此時就要考慮對其進行切分了，切分的目的就在于減少資料庫的負擔，縮短查詢的時間，

資料切分可以分為兩種方式：垂直劃分和水平劃分，

垂直劃分

垂直劃分資料庫是根據業務進行劃分，例如購物場景，可以將庫中涉及商品、訂單、用戶的表分別劃分出成一個庫，通過降低單庫的大小來提高性能，同樣的，分表的情況就是將一個大表根據業務功能拆分成一個個子表，例如商品基本資訊和商品描述，商品基本資訊一般會展示在商品串列，商品描述在商品詳情頁，可以將商品基本資訊和商品描述拆分成兩張表，

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

缺點：

主鍵出現冗余，需要管理冗余列；
會引起表連接JOIN操作，可以通過在業務服務器上進行join來減少資料庫壓力；
依然存在單表資料量過大的問題，

水平劃分

水平劃分是根據一定規則，例如時間或id序列值等進行資料的拆分，比如根據年份來拆分不同的資料庫，每個資料庫結構一致，但是資料得以拆分，從而提升性能，

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

缺點：

分片事務一致性難以解決
跨節點join性能差，邏輯復雜
資料分片在擴容時需要遷移

什么是磁區表？

磁區是把一張表的資料分成N多個區塊，磁區表是一個獨立的邏輯表，但是底層由多個物理子表組成，

當查詢條件的資料分布在某一個磁區的時候，查詢引擎只會去某一個磁區查詢，而不是遍歷整個表，在管理層面，如果需要洗掉某一個磁區的資料，只需要洗掉對應的磁區即可，

磁區一般都是放在單機里的，用的比較多的是時間范圍磁區，方便歸檔，只不過分庫分表需要代碼實作，磁區則是mysql內部實作，分庫分表和磁區并不沖突，可以結合使用，

磁區表型別

range磁區，按照范圍磁區，比如按照時間范圍磁區

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') ),
      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') )
   );

在/var/lib/mysql/data/可以找到對應的資料檔案，每個磁區表都有一個使用#分隔命名的表檔案：

   -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
...

list磁區

list磁區和range磁區相似，主要區別在于list是列舉值串列的集合，range是連續的區間值的集合，對于list磁區，磁區欄位必須是已知的，如果插入的欄位不在磁區時的列舉值中，將無法插入，

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)
   );

hash磁區

可以將資料均勻地分布到預先定義的磁區中，

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;

磁區的問題？

打開和鎖住所有底層表的成本可能很高，當查詢訪問磁區表時，MySQL 需要打開并鎖住所有的底層表，這個操作在磁區過濾之前發生，所以無法通過磁區過濾來降低此開銷，會影響到查詢速度，可以通過批量操作來降低此類開銷，比如批量插入、LOAD DATA INFILE和一次洗掉多行資料，
維護磁區的成本可能很高，例如重組磁區，會先創建一個臨時磁區，然后將資料復制到其中，最后再洗掉原磁區，
所有磁區必須使用相同的存盤引擎，

查詢陳述句執行流程？

查詢陳述句的執行流程如下：權限校驗、查詢快取、分析器、優化器、權限校驗、執行器、引擎，

舉個例子，查詢陳述句如下：

select * from user where id > 1 and name = '大彬';

首先檢查權限，沒有權限則回傳錯誤；
MySQL8.0以前會查詢快取，快取命中則直接回傳，沒有則執行下一步；
詞法分析和語法分析，提取表名、查詢條件，檢查語法是否有錯誤；
兩種執行方案，先查 id > 1 還是 name = '大彬'，優化器根據自己的優化演算法選擇執行效率最好的方案；
校驗權限，有權限就呼叫資料庫引擎介面，回傳引擎的執行結果，

更新陳述句執行程序？

更新陳述句執行流程如下：分析器、權限校驗、執行器、引擎、redo log（prepare狀態）、binlog、redo log（commit狀態）

舉個例子，更新陳述句如下：

update user set name = '大彬' where id = 1;

先查詢到 id 為1的記錄，有快取會使用快取，
拿到查詢結果，將 name 更新為大彬，然后呼叫引擎介面，寫入更新資料，innodb 引擎將資料保存在記憶體中，同時記錄redo log，此時redo log進入 prepare狀態，
執行器收到通知后記錄binlog，然后呼叫引擎介面，提交redo log為commit狀態，
更新完成，

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

假設先寫redo log直接提交，然后寫binlog，寫完redo log后，機器掛了，binlog日志沒有被寫入，那么機器重啟后，這臺機器會通過redo log恢復資料，但是這個時候binlog并沒有記錄該資料，后續進行機器備份的時候，就會丟失這一條資料，同時主從同步也會丟失這一條資料，

exist和in的區別？

exists用于對外表記錄做篩選，exists會遍歷外表，將外查詢表的每一行，代入內查詢進行判斷，當exists里的條件陳述句能夠回傳記錄行時，條件就為真，回傳外表當前記錄，反之如果exists里的條件陳述句不能回傳記錄行，條件為假，則外表當前記錄被丟棄，

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

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

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

子查詢的表比較大的時候，使用exists可以有效減少總的回圈次數來提升速度；當外查詢的表比較大的時候，使用in可以有效減少對外查詢表回圈遍歷來提升速度，

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

int(10)中的10表示的是顯示資料的長度，而char(10)表示的是存盤資料的長度，

truncate、delete與drop區別？

相同點：

truncate和不帶where子句的delete、以及drop都會洗掉表內的資料，
drop、truncate都是DDL陳述句（資料定義語言），執行后會自動提交，

不同點：

truncate 和 delete 只洗掉資料不洗掉表的結構；drop 陳述句將洗掉表的結構被依賴的約束、觸發器、索引；
一般來說，執行速度: drop > truncate > delete，

having和where區別？

二者作用的物件不同，where子句作用于表和視圖，having作用于組，
where在資料分組前進行過濾，having在資料分組后進行過濾，

為什么要做主從同步？

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

什么是MySQL主從同步？

主從同步使得資料可以從一個資料庫服務器復制到其他服務器上，在復制資料時，一個服務器充當主服務器（master），其余的服務器充當從服務器（slave），

因為復制是異步進行的，所以從服務器不需要一直連接著主服務器，從服務器甚至可以通過撥號斷斷續續地連接主服務器，通過組態檔，可以指定復制所有的資料庫，某個資料庫，甚至是某個資料庫上的某個表，

樂觀鎖和悲觀鎖是什么？

資料庫中的并發控制是確保在多個事務同時存取資料庫中同一資料時不破壞事務的隔離性和統一性以及資料庫的統一性，樂觀鎖和悲觀鎖是并發控制主要采用的技術手段，

悲觀鎖：假定會發生并發沖突，會對操作的資料進行加鎖，直到提交事務，才會釋放鎖，其他事務才能進行修改，實作方式：使用資料庫中的鎖機制，
樂觀鎖：假設不會發生并發沖突，只在提交操作時檢查是否資料是否被修改過，給表增加version欄位，在修改提交之前檢查version與原來取到的version值是否相等，若相等，表示資料沒有被修改，可以更新，否則，資料為臟資料，不能更新，實作方式：樂觀鎖一般使用版本號機制或CAS演算法實作，

用過processlist嗎？

show processlist 或 show full processlist 可以查看當前 MySQL 是否有壓力，正在運行的SQL，有沒有慢SQL正在執行，回傳引數如下：

id：執行緒ID，可以用kill id殺死某個執行緒
db：資料庫名稱
user：資料庫用戶
host：資料庫實體的IP
command：當前執行的命令，比如Sleep，Query，Connect 等
time：消耗時間，單位秒
state：執行狀態，主要有以下狀態：
- Sleep，執行緒正在等待客戶端發送新的請求
- Locked，執行緒正在等待鎖
- Sending data，正在處理SELECT查詢的記錄，同時把結果發送給客戶端
- Kill，正在執行kill陳述句，殺死指定執行緒
- Connect，一個從節點連上了主節點
- Quit，執行緒正在退出
- Sorting for group，正在為GROUP BY做排序
- Sorting for order，正在為ORDER BY做排序
info：正在執行的SQL陳述句

MySQL查詢 limit 1000,10 和limit 10 速度一樣快嗎？

兩種查詢方式，對應 limit offset, size 和 limit size 兩種方式，

而其實 limit size ，相當于 limit 0, size，也就是從0開始取size條資料，

也就是說，兩種方式的區別在于offset是否為0，

先來看下limit sql的內部執行邏輯，

MySQL內部分為server層和存盤引擎層，一般情況下存盤引擎都用innodb，

server層有很多模塊，其中需要關注的是執行器是用于跟存盤引擎打交道的組件，

執行器可以通過呼叫存盤引擎提供的介面，將一行行資料取出，當這些資料完全符合要求（比如滿足其他where條件），則會放到結果集中，最后回傳給呼叫mysql的客戶端，

以主鍵索引的limit執行程序為例：

執行select * from xxx order by id limit 0, 10;，select后面帶的是星號，也就是要求獲得行資料的所有欄位資訊，

server層會呼叫innodb的介面，在innodb里的主鍵索引中獲取到第0到10條完整行資料，依次回傳給server層，并放到server層的結果集中，回傳給客戶端，

把offset搞大點，比如執行的是：select * from xxx order by id limit 500000, 10;

server層會呼叫innodb的介面，由于這次的offset=500000，會在innodb里的主鍵索引中獲取到第0到（500000 + 10）條完整行資料，回傳給server層之后根據offset的值挨個拋棄，最后只留下最后面的size條，也就是10條資料，放到server層的結果集中，回傳給客戶端，

可以看出，當offset非0時，server層會從引擎層獲取到很多無用的資料，而獲取的這些無用資料都是要耗時的，

因此，mysql查詢中 limit 1000,10 會比 limit 10 更慢，原因是 limit 1000,10 會取出1000+10條資料，并拋棄前1000條，這部分耗時更大，

高度為3的B+樹，可以存放多少資料？

InnoDB存盤引擎有自己的最小儲存單元——頁（Page），

查詢InnoDB頁大小的命令如下：

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+

可以看出 innodb 默認的一頁大小為 16384B = 16384/1024 = 16kb，

在MySQL中，B+樹一個節點的大小設為一頁或頁的倍數最為合適，因為如果一個節點的大小 < 1頁，那么讀取這個節點的時候其實讀取的還是一頁，這樣就造成了資源的浪費，

B+樹中非葉子節點存的是key + 指標；葉子節點存的是資料行，

對于葉子節點，如果一行資料大小為1k，那么一頁就能存16條資料，

對于非葉子節點，如果key使用的是bigint，則為8位元組，指標在MySQL中為6位元組，一共是14位元組，則16k能存放 16 * 1024 / 14 = 1170 個索引指標，

于是可以算出，對于一顆高度為2的B+樹，根節點存盤索引指標節點，那么它有1170個葉子節點存盤資料，每個葉子節點可以存盤16條資料，一共 1170 x 16 = 18720 條資料，而對于高度為3的B+樹，就可以存放 1170 x 1170 x 16 = 21902400 條資料（兩千多萬條資料），也就是對于兩千多萬條的資料，我們只需要高度為3的B+樹就可以完成，通過主鍵查詢只需要3次IO操作就能查到對應資料，

所以在 InnoDB 中B+樹高度一般為3層時，就能滿足千萬級的資料存盤，

深分頁怎么優化？

還是以上面的SQL為空：select * from xxx order by id limit 500000, 10;

方法一：

從上面的分析可以看出，當offset非常大時，server層會從引擎層獲取到很多無用的資料，而當select后面是*號時，就需要拷貝完整的行資訊，拷貝完整資料相比只拷貝行資料里的其中一兩個列欄位更耗費時間，

因為前面的offset條資料最后都是不要的，沒有必要拷貝完整欄位，所以可以將sql陳述句修改成：

select * from xxx  where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;

先執行子查詢 select id from xxx by id limit 500000, 1, 這個操作，其實也是將在innodb中的主鍵索引中獲取到500000+1條資料，然后server層會拋棄前500000條，只保留最后一條資料的id，

但不同的地方在于，在回傳server層的程序中，只會拷貝資料行內的id這一列，而不會拷貝資料行的所有列，當資料量較大時，這部分的耗時還是比較明顯的，

在拿到了上面的id之后，假設這個id正好等于500000，那sql就變成了

select * from xxx  where id >=500000 order by id limit 10;

這樣innodb再走一次主鍵索引，通過B+樹快速定位到id=500000的行資料，時間復雜度是lg(n)，然后向后取10條資料，

方法二：

將所有的資料根據id主鍵進行排序，然后分批次取，將當前批次的最大id作為下次篩選的條件進行查詢，

select * from xxx where id > start_id order by id limit 10;

通過主鍵索引，每次定位到start_id的位置，然后往后遍歷10個資料，這樣不管資料多大，查詢性能都較為穩定，

大表查詢慢怎么優化？

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

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

合理建立索引，在合適的欄位上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根據EXPLAIN來查看是否用了索引還是全表掃描
索引優化，SQL優化，最左匹配原則等，參考：https://topjavaer.cn/database/mysql.html#什么是覆寫索引
建立磁區，對關鍵欄位建立水平磁區，比如時間欄位，若查詢條件往往通過時間范圍來進行查詢，能提升不少性能
利用快取，利用Redis等快取熱點資料，提高查詢效率
限定資料的范圍，比如：用戶在查詢歷史資訊的時候，可以控制在一個月的時間范圍內
讀寫分離，經典的資料庫拆分方案，主庫負責寫，從庫負責讀
通過分庫分表的方式進行優化，主要有垂直拆分和水平拆分
合理建立索引，在合適的欄位上建立索引，例如在WHERE和ORDERBY命令上涉及的列建立索引

資料異構到es
冷熱資料分離，幾個月之前不常用的資料放到冷庫中，最新的資料比較新的資料放到熱庫中
升級資料庫型別，換一種能兼容MySQL的資料庫（OceanBase、tidb）

MySQL單表多大進行分庫分表？

目前主流的有兩種說法：

MySQL 單表資料量大于 2000 萬行，性能會明顯下降，考慮進行分庫分表，
阿里巴巴《Java 開發手冊》提出單表行數超過 500 萬行或者單表容量超過 2GB，才推薦進行分庫分表，

事實上，這個數值和實際記錄的條數無關，而與 MySQL 的配置以及機器的硬體有關，因為MySQL為了提高性能，會將表的索引裝載到記憶體中，在InnoDB buffer size 足夠的情況下，其能完成全加載進記憶體，查詢不會有問題，但是，當單表資料庫到達某個量級的上限時，導致記憶體無法存盤其索引，使得之后的 SQL 查詢會產生磁盤 IO，從而導致性能下降，當然，這個還有具體的表結構的設計有關，最終導致的問題都是記憶體限制，

因此，對于分庫分表，需要結合實際需求，不宜過度設計，在專案一開始不采用分庫與分表設計，而是隨著業務的增長，在無法繼續優化的情況下，再考慮分庫與分表提高系統的性能，對此，阿里巴巴《Java 開發手冊》補充到：如果預計三年后的資料量根本達不到這個級別，請不要在創建表時就分庫分表，

至于MySQL單表多大進行分庫分表，應當根據機器資源進行評估，

說說count(1)、count(*)和count(欄位名)的區別

嗯，先說說count(1) and count(欄位名)的區別，

兩者的主要區別是

count(1) 會統計表中的所有的記錄數，包含欄位為null 的記錄，
count(欄位名) 會統計該欄位在表中出現的次數，忽略欄位為null 的情況，即不統計欄位為null 的記錄，

接下來看看三者之間的區別，

執行效果上：

count(*)包括了所有的列，相當于行數，在統計結果的時候，不會忽略列值為NULL
count(1)包括了忽略所有列，用1代表代碼行，在統計結果的時候，不會忽略列值為NULL
count(欄位名)只包括列名那一列，在統計結果的時候，會忽略列值為空（這里的空不是只空字串或者0，而是表示null）的計數，即某個欄位值為NULL時，不統計，

執行效率上：

列名為主鍵，count(欄位名)會比count(1)快
列名不為主鍵，count(1)會比count(列名)快
如果表多個列并且沒有主鍵，則 count(1) 的執行效率優于 count(*)
如果有主鍵，則 select count(主鍵)的執行效率是最優的
如果表只有一個欄位，則 select count(*)最優，

MySQL中DATETIME 和 TIMESTAMP有什么區別？

嗯，TIMESTAMP和DATETIME都可以用來存盤時間，它們主要有以下區別：

1.表示范圍

DATETIME：1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
TIMESTAMP：'1970-01-01 00:00:01.000000' UTC 到 '2038-01-09 03:14:07.999999' UTC

TIMESTAMP支持的時間范圍比DATATIME要小，容易出現超出的情況，

2.空間占用

TIMESTAMP ：占 4 個位元組
DATETIME：在 MySQL 5.6.4 之前，占 8 個位元組，之后版本，占 5 個位元組

3.存入時間是否會自動轉換

TIMESTAMP型別在默認情況下，insert、update 資料時，TIMESTAMP列會自動以當前時間（CURRENT_TIMESTAMP）填充/更新，DATETIME則不會做任何轉換，也不會檢測時區，你給什么資料，它存什么資料，

4.TIMESTAMP比較受時區timezone的影響以及MYSQL版本和服務器的SQL MODE的影響，因為TIMESTAMP存的是時間戳，在不同的時區得出的時間不一致，

5.如果存進NULL，兩者實際存盤的值不同，

TIMESTAMP：會自動存盤當前時間 now() ，
DATETIME：不會自動存盤當前時間，會直接存入 NULL 值，

說說為什么不建議用外鍵？

外鍵是一種約束，這個約束的存在，會保證表間資料的關系始終完整，外鍵的存在，并非全然沒有優點，

外鍵可以保證資料的完整性和一致性，級聯操作方便，而且使用外鍵可以將資料完整性判斷托付給了資料庫完成，減少了程式的代碼量，

雖然外鍵能夠保證資料的完整性，但是會給系統帶來很多缺陷，

1、并發問題，在使用外鍵的情況下，每次修改資料都需要去另外一個表檢查資料，需要獲取額外的鎖，若是在高并發大流量事務場景，使用外鍵更容易造成死鎖，

2、擴展性問題，比如從MySQL遷移到Oracle，外鍵依賴于資料庫本身的特性，做遷移可能不方便，

3、不利于分庫分表，在水平拆分和分庫的情況下，外鍵是無法生效的，將資料間關系的維護，放入應用程式中，為將來的分庫分表省去很多的麻煩，

使用自增主鍵有什么好處？

自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁分裂，因此索引更緊湊，在查詢的時候，效率也就更高，

InnoDB的自增值為什么不能回收利用？

主要為了提升插入資料的效率和并行度，

假設有兩個并行執行的事務，在申請自增值的時候，為了避免兩個事務申請到相同的自增 id，肯定要加鎖，然后順序申請，

假設事務 A 申請到了 id=2，事務 B 申請到 id=3，那么這時候表 t 的自增值是 4，之后繼續執行，

事務 B 正確提交了，但事務 A 出現了唯一鍵沖突，

如果允許事務 A 把自增 id 回退，也就是把表 t 的當前自增值改回 2，那么就會出現這樣的情況：表里面已經有 id=3 的行，而當前的自增 id 值是 2，

接下來，繼續執行的其他事務就會申請到 id=2，然后再申請到 id=3，這時，就會出現插入陳述句報錯“主鍵沖突”，

而為了解決這個主鍵沖突，有兩種方法：

每次申請 id 之前，先判斷表里面是否已經存在這個 id，如果存在，就跳過這個 id，但是，這個方法的成本很高，因為，本來申請 id 是一個很快的操作，現在還要再去主鍵索引樹上判斷 id 是否存在，
把自增 id 的鎖范圍擴大，必須等到一個事務執行完成并提交，下一個事務才能再申請自增 id，這個方法的問題，就是鎖的粒度太大，系統并發能力大大下降，

可見，這兩個方法都會導致性能問題，

因此，InnoDB 放棄了“允許自增 id 回退”這個設計，陳述句執行失敗也不回退自增 id，

自增主鍵保存在什么地方？

不同的引擎對于自增值的保存策略不同：

MyISAM引擎的自增值保存在資料檔案中，
在MySQL8.0以前，InnoDB引擎的自增值是存在記憶體中，MySQL重啟之后記憶體中的這個值就丟失了，每次重啟后第一次打開表的時候，會找自增值的最大值max(id)，然后將最大值加1作為這個表的自增值；MySQL8.0版本會將自增值的變更記錄在redo log中，重啟時依靠redo log恢復，

自增主鍵一定是連續的嗎？

不一定，有幾種情況會導致自增主鍵不連續，

1、唯一鍵沖突導致自增主鍵不連續，當我們向一個自增主鍵的InnoDB表中插入資料的時候，如果違反表中定義的唯一索引的唯一約束，會導致插入資料失敗，此時表的自增主鍵的鍵值是會向后加1滾動的，下次再次插入資料的時候，就不能再使用上次因插入資料失敗而滾動生成的鍵值了，必須使用新滾動生成的鍵值，

2、事務回滾導致自增主鍵不連續，當我們向一個自增主鍵的InnoDB表中插入資料的時候，如果顯式開啟了事務，然后因為某種原因最后回滾了事務，此時表的自增值也會發生滾動，而接下里新插入的資料，也將不能使用滾動過的自增值，而是需要重新申請一個新的自增值，

3、批量插入導致自增值不連續，MySQL有一個批量申請自增id的策略：

陳述句執行程序中，第一次申請自增id，分配1個自增id
1個用完以后，第二次申請，會分配2個自增id
2個用完以后，第三次申請，會分配4個自增id
依次類推，每次申請都是上一次的兩倍（最后一次申請不一定全部使用）

如果下一個事務再次插入資料的時候，則會基于上一個事務申請后的自增值基礎上再申請，此時就出現自增值不連續的情況出現，

4、自增步長不是1，也會導致自增主鍵不連續，

MySQL資料如何同步到Redis快取？

參考：https://cloud.tencent.com/developer/article/1805755

有兩種方案：

1、通過MySQL自動同步重繪Redis，MySQL觸發器+UDF函式實作，

程序大致如下：

在MySQL中對要操作的資料設定觸發器Trigger，監聽操作
客戶端向MySQL中寫入資料時，觸發器會被觸發，觸發之后呼叫MySQL的UDF函式
UDF函式可以把資料寫入到Redis中，從而達到同步的效果

2、決議MySQL的binlog，實作將資料庫中的資料同步到Redis，可以通過canal實作，canal是阿里巴巴旗下的一款開源專案，基于資料庫增量日志決議，提供增量資料訂閱&消費，

canal的原理如下：

canal模擬mysql slave的互動協議，偽裝自己為mysql slave，向mysql master發送dump協議
mysql master收到dump請求，開始推送binary log給canal
canal決議binary log物件（原始為byte流），將資料同步寫入Redis，

為什么阿里Java手冊禁止使用存盤程序？

先看看什么是存盤程序，

存盤程序是在大型資料庫系統中，一組為了完成特定功能的SQL 陳述句集，它存盤在資料庫中，一次編譯后永久有效，用戶通過指定存盤程序的名字并給出引數（如果該存盤程序帶有引數）來執行它，

存盤程序主要有以下幾個缺點，

存盤程序難以除錯，存盤程序的開發一直缺少有效的 IDE 環境，SQL 本身經常很長，除錯式要把句子拆開分別獨立執行，非常麻煩，
移植性差，存盤程序的移植困難，一般業務系統總會不可避免地用到資料庫獨有的特性和語法，更換資料庫時這部分代碼就需要重寫，成本較高，
管理困難，存盤程序的目錄是扁平的，而不是檔案系統那樣的樹形結構，腳本少的時候還好辦，一旦多起來，目錄就會陷入混亂，
存盤程序是只優化一次，有的時候隨著資料量的增加或者資料結構的變化，原來存盤程序選擇的執行計劃也許并不是最優的了，所以這個時候需要手動干預或者重新編譯了，

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/551154.html

標籤：其他

上一篇：【Jmeter】按比例分配Api壓測

下一篇：返回列表