三天吃透MySQL面試八股文

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

Github地址：https://github.com/Tyson0314/Java-learning

事務的四大特性？

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

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

資料庫的三大正規化

第一規格化1NF

確保資料庫表欄位的原子性。

比如欄位 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依據是非主鍵列是直接依賴於主鍵，還是直接依賴於非主鍵。

事務隔離級別有哪些？

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

髒讀是指在一個事務處理過程裡讀取了另一個未提交的事務中的資料。
不可重複讀是指在對於資料庫中的某行記錄，一個事務範圍內多次查詢卻返回了不同的資料值，這是由於在查詢間隔，另一個事務修改了資料並提交了。
幻讀是當某個事務在讀取某個範圍內的記錄時，另外一個事務又在該範圍內插入了新的記錄。對幻讀的正確理解是一個事務內的讀取操作的結論不能支撐之後業務的執行。假設事務要新增一條記錄，主鍵為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。

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

網際網路專案中mysql應該選什麼事務隔離級別

編碼和字元集的關係

我們平時可以在編輯器上輸入各種中文英文字母，但這些都是給人讀的，不是給計算機讀的，其實計算機真正儲存和傳輸資料都是以二進位制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;

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可以有效減少對外查詢表迴圈遍歷來提升速度。

truncate、delete與drop區別？

相同點：

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

不同點：

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

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

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

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條，這部分耗時更大。

深分頁怎麼優化？

還是以上面的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;

mysql

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

高度為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層時，就能滿足千萬級的資料儲存。

參考：https://www.cnblogs.com/leefreeman/p/8315844.html

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

目前主流的有兩種說法：

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

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

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

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

大表查詢慢怎麼優化？

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

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

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

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

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

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

Github地址：https://github.com/Tyson0314/java-books