三天吃透MySQL面試八股文_ZenDei技術網路在線

本文已經收錄到Github倉庫，該倉庫包含電腦基礎、Java基礎、多線程、JVM、資料庫、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分散式、微服務、設計模式、架構、校招社招分享等核心知識點，歡迎star~ Github地址：https://github.c ...

本文已經收錄到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 |
+------------------+-------+