66個面試問題，帶你梳理MySQL知識點！

身為SQL Boy，基礎部分不會有人不會吧？面試也不怎麼問，基礎掌握不錯的夥伴可以跳過這部分。當然，可能會現場寫一些SQL語句，SQ語句可以透過牛客、LeetCode、LintCode之類的網站來練習。

1. 什麼是內連結、外連結、交叉連結、笛卡兒積呢？

• 內連接（inner join）：取得兩張表中滿足存在連接匹配關係的記錄。
• 外部連接（outer join）：不只取得兩張表中滿足存在連接匹配關係的記錄，還包括某張表（或兩張表）中不滿足匹配關係的記錄。
• 交叉連接（cross join）：顯示兩張表所有記錄一一對應，沒有匹配關係進行篩選，它是笛卡爾積在SQL中的實現，如果A表有m行，B表有n行，那麼A和B交叉連接的結果就有m*n行。
• 笛卡兒積：是數學中的一個概念，例如集合A={a,b}，集合B={1,2,3}，那麼A✖️B={ ,,,,,,}。

2. 那MySQL 的內連接、左連接、右邊連接有什麼差別？

MySQL的連接主要分為內連接和外連接，外連接常用的有左連接、右連接。

• inner join 內連接，在兩張表進行連接查詢時，只保留兩張表中完全匹配的結果集
• left join 在兩張表進行連接查詢時，會傳回左表所有的行，即使在右表中沒有符合的記錄。
• right join 在兩張表進行連接查詢時，會傳回右表所有的行，即使在左表中沒有符合的記錄。

3.說一下資料庫的三大範式？

• 第一範式：資料表中的每一列（每個欄位）都不可以再拆分。例如用戶表，用戶地址還可以拆分成國家、省份、市，這樣才是符合第一範式的。
• 第二範式：在第一範式的基礎上，非主鍵列完全依賴主鍵，而不能是依賴主鍵的一部分。例如訂單表裡，儲存了商品資訊（商品價格、商品類型），那就需要把商品ID和訂單ID當作聯合主鍵，才滿足第二範式。
• 第三範式：在滿足第二範式的基礎上，表中的非主鍵只依賴主鍵，而不依賴其他非主鍵。例如訂單表，就不能儲存使用者資訊（姓名、地址）。

三大範式的作用是為了控制資料庫的冗餘，是對空間的節省，實際上，一般網路公司的設計都是反範式的，透過冗餘一些數據，避免跨表跨庫，利用空間換時間，提高效能。

4.varchar與char的差別？

char：

• char表示定長字串，長度是固定的;

• 如果插入資料的長度小於char的固定長度時，則用空格填充；
• 因為長度固定，所以訪問速度要比varchar快很多，甚至能快50%，但正因為其長度固定，所以會佔據多餘的空間，是空間換時間的做法；
• 對char來說，最多能存放的字元數為255，和編碼無關

varchar

：

varchar表示可變長字串，長度是可變的；
• 插入的資料是多長，就依照多長來儲存；
• varchar在存取上與char相反，它存取慢，因為長度不固定，但正因如此，不佔據多餘的空間，是時間換空間的做法；

對於varchar來說，最多能存放的字元數為65532

日常的設計，對於長度相對固定的字串，可以使用char，對於長度不確定的，使用varchar更合適一些。

5.blob和text有什麼差別？

0. blob用於儲存二進位數據，而text用於儲存大字串。
blob沒有字元集，text有一個字元集，並且根據字元集的校對規則對值進行排序和比較
2. 6.DATETIME和TIMESTAMP的異同？

相同點：

###兩個資料類型儲存時間的表現格式一致。皆為 ###YYYY-MM-DD HH:MM:SS#########兩個資料型別都包含「日期」和「時間」部分。 ######兩個資料型別都可以儲存微秒的小數秒（秒後6位小數秒）############區別###：###

• 日期範圍：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

• #：DATETIME 的儲存空間為8 位元組；TIMESTAMP 的儲存空間為4 位元組

• 時區相關：DATETIME 儲存時間與時區無關；TIMESTAMP 儲存時間與時區有關，顯示的值也依賴時區

• #預設值：DATETIME 的預設值為null；TIMESTAMP 的欄位預設為空(not null)，預設值為目前時間(CURRENT_TIMESTAMP)

#7. MySQL中in 和exists 的差別？

MySQL中的in語句是把外表和內表作hash 連接，而exists語句是對外表作loop循環，每次loop循環再對內表進行查詢。我們可能認為exists比in語句的效率要高，這種說法其實是不準確的，要區分情景：

• 如果查詢的兩個表大小相當，那麼用in和exists差異不大。

• 如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in。

• not in 和not exists：如果查詢語句使用了not in，那麼內外表都進行全表掃描，沒有用到索引；而not extsts的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in快。

8.MySQL裡記錄貨幣用什麼欄位類型比較好？

貨幣在資料庫中MySQL常用Decimal和Numric類型表示，這兩種類型被MySQL實作為相同的類型。他們被用於保存與貨幣有關的數據。

例如salary DECIMAL(9,2)，9(precision)代表將被用於儲存值的總的小數位數，而2(scale)代表將被用於儲存小數點後的位數。儲存在salary列中的值的範圍是從-9999999.99到99999999.99。

DECIMAL和NUMERIC值作為字串存儲，而不是作為二進制浮點數，以便保存那些值的小數精度。

之所以不使用float或double的原因：因為float和double是以二進位儲存的，所以有一定的誤差。

9.MySQL怎麼儲存emoji?

MySQL可以直接使用字串儲存emoji。

但要注意的，utf8 編碼是不行的，MySQL中的utf8是閹割版的 utf8，它最多只用 3 個位元組儲存字符，所以儲存不了表情。那該怎麼辦？

需要使用utf8mb4編碼。

alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null;

10.drop、delete與truncate的差別？

三者都表示刪除，但三者有一些差異：

##屬於DDL回滾##刪除內容表格結構還在，刪除表的全部或一部分資料行表格結構還在，刪除表中的所有資料從資料庫中刪除表，所有資料行，索引和權限也會被刪除刪除速度刪除速度慢，需要逐行刪除#刪除速度快刪除速度最快

	delete	truncate	drop
類型	屬於DML	##屬於DDL
可回滾	#無法回滾	不可回滾

##########

因此，在不再需要一張表的時候，用drop；在想刪除部分資料行時候，用delete；在保留表而刪除所有資料的時候用truncate。

11.UNION與UNION ALL的差別？

• 如果使用UNION ALL，不會合併重複的記錄行
• 效率UNION 高於UNION ALL

12.count(1)、count (*) 與count(列名) 的差別？

執行效果：

• count(*)包含了所有的列，相當於行數，在統計結果的時候，不會忽略列值為NULL
• count(1)包含了忽略所有列，用1代表程式碼行，在統計結果的時候，不會忽略列值為NULL
• #count(列名)只包含列名那一列，在統計結果的時候，會忽略列值為空（這裡的空不是只空字串或0，而是表示null）的計數，即某個字段值為NULL時，不統計。

執行速度：

• 列名為主鍵，count(列名)會比count(1)快
• #列名不為主鍵，count(1)會比count(列名)快
• 如果表多個列且沒有主鍵，則count（1） 的執行效率優於count（*）
• 如果有主鍵，則select count（主鍵）的執行效率是最優的
• 如果表只有一個字段，則select count（*）最優。

13.一條SQL查詢語句的執行順序？

• FROM：對FROM子句中的左表和右表執行笛卡兒積（Cartesianproduct），產生虛擬表VT1

• ON：對虛擬表VT1應用ON篩選，只有那些符合的行才被插入虛擬表VT2中

• JOIN：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那麼保留表中未匹配的行作為外部行加入到虛擬表VT2中，產生虛擬表VT3。如果FROM子句包含兩個以上表，則對上一個連接產生的結果表VT3和下一個表重複執行步驟1）～步驟3），直到處理完所有的表為止

• WHERE：對虛擬表VT3套用WHERE過濾條件，只有符合的記錄才會被插入虛擬表VT4中

• # GROUP BY：根據GROUP BY子句中的列，將VT4中的記錄進行分組操作，產生VT5

• CUBE|ROLLUP：對錶VT5進行CUBE或ROLLUP操作，產生表VT6

• HAVING：對虛擬表VT6應用HAVING過濾器，只有符合的記錄才會被插入虛擬表VT7中。

• SELECT：第二次執行SELECT操作，選擇指定的列，插入到虛擬表VT8中

• DISTINCT：移除重複數據，產生虛擬表VT9

• #ORDER BY：將虛擬表VT9中的記錄依照進行排序操作，產生虛擬表VT10。11）

• LIMIT：取出指定行的記錄，產生虛擬表VT11，並傳回給查詢使用者

#資料庫架構

14.說說MySQL 的基礎架構?

MySQL邏輯架構圖主要分三層：

• 客戶端：最上層的服務並不是MySQL所獨有的，大多數基於網路的客戶端/伺服器的工具或服務都有類似的架構。例如連線處理、授權認證、安全性等等。
• Server層：大多數MySQL的核心服務功能都在這一層，包括查詢解析、分析、最佳化、快取以及所有的內建函數（例如，日期、時間、數學和加密函數），所有跨儲存引擎的功能都在這一層實現：預存程序、觸發器、視圖等。
• 儲存引擎層：第三層包含了儲存引擎。儲存引擎負責MySQL中資料的儲存和提取。 Server層透過API與儲存引擎通訊。這些介面屏蔽了不同儲存引擎之間的差異，使得這些差異對上層的查詢流程變得透明。

15.一條 SQL 查詢語句在 MySQL 中如何執行的？

• 先檢查該語句是否有權限，如果沒有權限，直接傳回錯誤訊息，如果有權限會先查詢快取 (MySQL8.0 版本之前)。
• 如果沒有緩存，分析器進行語法分析，提取 sql 語句中 select 等關鍵元素，然後判斷 sql 語句是否有語法錯誤，例如關鍵字是否正確等等。
• 語法解析之後，MySQL的伺服器會對查詢的語句進行最佳化，決定執行的方案。
• 完成查詢最佳化後，依照產生的執行計畫呼叫資料庫引擎介面，傳回執行結果。

儲存引擎

16.MySQL有哪些常見儲存引擎？

主要儲存引擎以及功能如下：

##YesYes Yes支援雜湊索引NoYes支援資料快取支援外鍵

MySQL5.5之前，預設儲存引擎是MylSAM，5.5之後變成了InnoDB。

InnoDB支援的雜湊索引是自適應的，InnoDB會根據資料表的使用量自動為資料表產生雜湊索引，不能人為幹預是否在一張表中產生雜湊索引。

MySQL 5.6開始InnoDB支援全文索引。

17.那儲存引擎該怎麼選擇？

大致上可以這麼選擇：

• 大多數情況下，使用預設的InnoDB就夠了。如果要提供提交、回滾和復原的事務安全（ACID 相容）能力，並要求實現並發控制，InnoDB 就是比較靠前的選擇了。
• 如果資料表主要用來插入和查詢記錄，則 MyISAM 引擎提供較高的處理效率。
• 如果只是暫時存放數據，資料量不大，並且不需要較高的資料安全性，可以選擇將資料保存在記憶體的MEMORY 引擎中，MySQL 中使用該引擎作為臨時表，存放查詢的中間結果。

使用哪一種引擎可以根據需要靈活選擇，因為儲存引擎是基於表格的，所以一個資料庫中多個表格可以使用不同的引擎以滿足各種效能和實際需求。使用合適的儲存引擎將會提高整個資料庫的效能。

18.InnoDB和MylSAM主要有什麼差別？

PS:MySQL8.0都開始慢慢流行了，如果不是面試，MylSAM其實可以不用怎麼了解。

1. 儲存結構：每個MyISAM在磁碟上儲存成三個檔案；InnoDB所有的表格都保存在同一個資料檔案中（也可能是多個文件，或是獨立的表空間文件），InnoDB表的大小只受限於作業系統文件的大小，一般為2GB。

2. 事務支援：MyISAM不提供事務支援；InnoDB提供事務支援事務，具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全特性。

3 最小鎖定粒度：MyISAM只支援表格層級鎖定，更新時會鎖定整個表，導致其它查詢和更新都會被阻塞InnoDB支援行級鎖定。

4. 索引類型：MyISAM的索引為叢集索引，資料結構是B樹；InnoDB的索引是非叢集索引，資料結構是B 樹。

5. 主鍵必要：MyISAM允許沒有任何索引和主鍵的表存在；InnoDB如果沒有設定主鍵或非空唯一索引，就會自動產生一個6字節的主鍵(用戶不可見) ，資料是主索引的一部分，附加索引保存的是主索引的值。

6. 表的特定行數：MyISAM儲存了表的總行數，如果select count() from table;會直接取出出該值; InnoDB沒有儲存表的總行數，如果使用select count() from table；就會遍歷整個表;但是在加了wehre條件後，MyISAM和InnoDB處理的方式都一樣。

7. 外鍵支援：MyISAM不支援外鍵；InnoDB支援外鍵。

日誌

19.MySQL日誌檔案有哪些？分別介紹下作用？

MySQL日誌檔案很多，包括：

• #錯誤日誌（error log）：錯誤日誌檔案對MySQL的啟動、運作、關閉過程進行了記錄，能幫助定位MySQL問題。
• 慢查詢日誌（slow query log）：慢查詢日誌是用來記錄執行時間超過 long_query_time 這個變數定義的時長的查詢語句。透過慢查詢日誌，可以找出哪些查詢語句的執行效率很低，以便進行最佳化。
• 一般查詢日誌（general log）：一般查詢日誌記錄了所有對MySQL資料庫請求的信息，無論請求是否正確執行。
• 二進位日誌（bin log）：關於二進位日誌，它記錄了資料庫所有執行的DDL和DML語句（除了資料查詢語句select、show等），以事件形式記錄並儲存在二進位檔案中。

還有兩個InnoDB儲存引擎特有的日誌檔案：

• 重做日誌（redo log）：重做日誌至關重要，因為它們記錄了對於InnoDB儲存引擎的交易日誌。
• 回滾日誌（undo log）：回溯日誌同樣也是InnoDB引擎提供的日誌，顧名思義，回溯日誌的作用就是對資料進行回滾。當交易對資料庫進行修改，InnoDB引擎不僅會記錄redo log，還會產生對應的undo log日誌；如果交易執行失敗或呼叫了rollback，導致交易需要回滾，就可以利用undo log中的資訊將資料回滾到修改之前的樣子。

20.binlog和redo log有什麼差別？

• bin log會記錄所有與資料庫相關的日誌記錄，包括InnoDB、MyISAM等儲存引擎的日誌，而redo log只記InnoDB儲存引擎的日誌。
• 記錄的內容不同，bin log記錄的是關於一個交易的具體操作內容，即該日誌是邏輯日誌。而redo log記錄的是關於每個頁（Page）的變更的物理情況。
• 寫入的時間不同，bin log只在交易提交前提交，也就是只寫磁碟一次。而在事務進行的過程中，卻不斷有redo ertry被寫入redo log中。
• 寫入的方式也不相同，redo log是循環寫入和擦除，bin log是追加寫入，不會覆蓋已經寫好的檔案。

21.一條更新語句怎麼執行的了解嗎？

更新語句的執行是Server層和引擎層配合完成，資料除了要寫入表中，還要記錄對應的日誌。

• 執行器先找引擎取得ID=2這一行。 ID是主鍵，儲存引擎檢索數據，找到這一行。如果ID=2這一行所在的資料頁本來就在內存中，就直接返回給執行器；否則，需要先從磁碟讀入內存，然後再返回。

• 執行器拿到引擎給的行數據，把這個值加上1，例如原來是N，現在就是N 1，得到新的一行數據，再呼叫引擎介面寫入這行新數據。

• 引擎將這行新資料更新到記憶體中，同時將這個更新作業記錄到redo log裡面，此時redo log處於prepare狀態。然後告知執行器執行完成了，隨時可以提交事務。

• 執行器產生這個動作的binlog，並且把binlog寫入磁碟。

• 執行器呼叫引擎的提交事務接口，引擎把剛剛寫入的redo log改成提交（commit）狀態，更新完成。

從上圖可以看出，MySQL在執行更新語句的時候，在服務層進行語句的解析和執行，在引擎層進行資料的提取和儲存；同時在服務層對binlog進行寫入，在InnoDB內進行redo log的寫入。

不僅如此，在對redo log寫入時有兩個階段的提交，一是binlog寫入之前prepare狀態的寫入，二是binlog寫入之後 commit狀態的寫入。

22.那為什麼要兩階段提交呢？

為什麼要兩階段提交？直接提交不行嗎？

我們可以假設不採用兩階段提交的方式，而是採用「單階段」來提交，即要麼先寫入redo log，後寫入binlog；要麼先寫入binlog，然後寫入redo log。這兩種方式的提交都會導致原先資料庫的狀態和恢復後的資料庫的狀態不一致。

先寫入redo log，後寫入binlog：

#在寫完redo log之後，資料此時具有crash-safe能力，因此系統崩潰，資料會恢復成事務開始之前的狀態。但是，若在redo log寫完時候，binlog寫入之前，系統發生了宕機。此時binlog並未對上面的更新語句進行保存，導致當使用binlog進行資料庫的備份或還原時，就少了上述的更新語句。從而使得id=2這一行的資料沒有被更新。

先寫入binlog，然後寫入redo log：

寫完binlog之後，所有的語句都被儲存，所以透過binlog複製或恢復出來的資料庫中id=2這一行的資料會被更新為a=1。但如果在redo log寫入之前，系統崩潰，那麼redo log中記錄的這個事務會無效，導致實際資料庫中id=2這一行的資料並沒有更新。

簡單來說，redo log和binlog都可以用來表示交易的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致。

23.redo log怎么刷入磁盘的知道吗？

redo log的写入不是直接落到磁盘，而是在内存中设置了一片称之为redo log buffer的连续内存空间，也就是redo 日志缓冲区。

什么时候会刷入磁盘？

在如下的一些情况中，log buffer的数据会刷入磁盘：

• log buffer 空间不足时

log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。

• 事务提交时

在事务提交时，为了保证持久性，会把log buffer中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。

• 后台线程输入

有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。

• 正常关闭服务器时
• 触发checkpoint规则

重做日志缓存、重做日志文件都是以块（block） 的方式进行保存的，称之为重做日志块（redo log block） ,块的大小是固定的512字节。我们的redo log它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的log block 组成。

它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。

其中有两个标记位置：

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。

当write_pos追上checkpoint时，表示redo log日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。

所谓的checkpoint规则，就是checkpoint触发后，将buffer中日志页都刷到磁盘。

SQL 优化

24.慢SQL如何定位呢？

慢SQL的监控主要通过两个途径：

• 慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
• 服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。

25.有哪些方式优化慢SQL？

慢SQL的优化，主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。

避免不必要的列

这个是老生常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。

分页优化

在数据量比较大，分页比较深的情况下，需要考虑分页的优化。

例如：

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

优化方案：

• 延迟关联

  先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行

  例如：

  select a.* from table a, 
   (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
   where a.id = b.id

• 书签方式

  书签方式就是找到limit第一个参数对应的主键值，根据这个主键值再去过滤并limit

  例如：

  select * from table where id >
  (select * from table where type = 2 and level = 9 order by id asc limit 190

索引优化

合理地设计和使用索引，是优化慢SQL的利器。

利用覆盖索引

InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引

例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

低版本避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

避免使用 != 或者 操作符

SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

例如，把column’aaa’，改成column>’aaa’ or column，就可以使用索引了

适当使用前缀索引

适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

正确使用联合索引

使用联合索引的时候，注意最左匹配原则。

JOIN优化

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。

比如left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。

 select name from A left join B ;

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

避免使用JOIN关联太多的表

《阿里巴巴Java开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的buffer会占用更多的内存。

如果不可避免要join多张表，可以考虑使用数据异构的方式异构到ES中查询。

排序优化

利用索引扫描做排序

MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

UNION优化

条件下推

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引

最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。

26.怎么看执行计划（explain），如何理解其中各个字段的含义？

explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看一下执行计划，看看是否还有优化的空间。

直接在 select 语句之前增加explain关键字，就会返回执行计划的信息。

0. id 欄位：MySQL會為每個select語句指派一個唯一的id值

1. select_type#列，查詢的類型，根據關聯、union、子查詢等等分類，常見的查詢類型有SIMPLE、PRIMARY。

2. table 欄位：表示 explain 的一行正在存取哪個表。

3. type 欄位：最重要的欄位之一。表示關聯類型或存取類型，即 MySQL 決定如何查找表中的行。

   效能從最優到最差分別是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALLggt; index_subquery > range > index > ALL

   • system

     system： 當表格只有一行記錄時(系統表)，資料量很少，往往不需要進行磁碟IO ，速度非常快

   • const

     const：表示查詢時命中primary key 主鍵或unique 唯一索引，或被連接的部分是一個常數(const)值。這類掃描效率極高，回傳資料量少，速度非常快。

   • eq_ref

     eq_ref：查詢時命中主鍵primary key 或unique key索引， type 是eq_ref。

   • ref_or_null

     ref_or_null：這個連線類型類似ref，差別在於MySQL會額外搜尋包含NULL值的行。

   • index_merge

     index_merge：使用了索引合併最佳化方法，查詢使用了兩個以上的索引。

   • unique_subquery

     unique_subquery：取代下面的 IN子查詢，子查詢傳回不重複的集合。

   • index_subquery

     index_subquery：區別於unique_subquery，用於非唯一索引，可以傳回重複值。

   • range

     range：使用索引選擇行，僅檢索給定範圍內的行。簡單點說就是針對一個有索引的字段，給定範圍檢索資料。在where語句中使用bettween...and、、<code>>、 、<code>in 等條件查詢type 都是range。

   • index

     index：Index 與ALL 其實都是讀全表，區別在於index是遍歷索引樹讀取，而ALL是從硬碟中讀取。

   • ALL

     就不用多說了，全表掃描。

4. possible_keys 欄位：顯示查詢可能使用哪些索引來尋找，使用索引最佳化sql的時候比較重要。

5. key 欄位：這一列顯示 mysql 實際上採用哪個索引來優化對該表的訪問，判斷索引是否失效的時候常用。

6. key_len 欄位：顯示了MySQL使用

7. ref 欄位：ref 欄位展示的就是與索引列作等值相符的值，常見的有：const（常數），func，NULL，欄位名稱。

8. rows 列：這也是一個重要的字段，MySQL查詢最佳化器根據統計信息，估算SQL要查到結果集需要掃描讀取的資料行數，這個值非常直覺顯示SQL的效率好壞，原則上rows越少越好。

9. Extra 列：顯示不適合在其它列的額外信息，雖然叫額外，但是也有一些重要的信息：

##Using index：表示MySQL將使用覆蓋索引，以避免回表
• Using where：表示會在儲存引擎檢索之後再進行過濾
• Using temporary ：表示對查詢結果排序時會使用一個臨時表。

索引

索引可以說是MySQL面試中的重中之重，一定要徹底拿下。

27.能簡單說一下索引的分類嗎？

從三個不同維度對索引分類：

例如從基本使用使用的角度來講：

主鍵索引: InnoDB主鍵是預設的索引，資料列不允許重複，不允許為NULL，一個表只能有一個主鍵。
• 唯一索引: 資料列不允許重複，允許為NULL值，一個資料表允許多個資料列建立唯一索引。
• 普通索引: 基本的索引類型，沒有唯一性的限制，允許為NULL值。
• 組合索引：多列值組成一個索引，用於組合搜索，效率大於索引合併

28.為什麼使用索引會加快查詢？

傳統的查詢方法，是按照表的順序遍歷的，不論查詢幾條數據，MySQL需要將表的數據從頭到尾遍歷一遍。

在我們加入索引之後，MySQL一般透過BTREE演算法產生一個索引文件，在查詢資料庫時，找到索引文件進行遍歷，在比較小的索引資料裡查找，然後對應到對應的數據，能大幅提升查找的效率。

和我們透過書的目錄，去找對應的內容，一樣的道理。

29.建立索引有哪些注意點？

索引雖然是sql效能最佳化的利器，但是索引的維護也是需要成本的，所以建立索引，也要注意：

• 索引應該建在查詢應用頻繁的欄位

  在用於where 判斷、 order 排序和join 的(on)欄位上建立索引。

• 索引的數量應該是適量

  索引需要佔用空間；更新時候也需要維護。

• 區分度低的字段，例如性別，不要建立索引。

  離散度太低的字段，掃描的行數降低的有限。

• 頻繁更新的值，不要作為主鍵或索引

  維護索引檔案需要成本；還會導致頁分裂，IO次數增多。

• 組合索引把雜湊性高(區分度高)的值放在前面

  為了滿足最左前綴匹配原則

• #建立組合索引，而不是修改單列索引。

  組合索引取代多個單列索引（對於單列索引，MySQL基本上只能使用一個索引，所以經常使用多個條件查詢時更適合使用組合索引）

• #過長的字段，使用前綴索引。當字段值比較長的時候，建立索引會消耗很多的空間，搜尋起來也會很慢。我們可以透過截取欄位的前面一部分內容來建立索引，這個就叫前綴索引。

• 不建議以無序的值(例如身分證、UUID )作為索引

  當主鍵具有不確定性，會造成葉子節點頻繁分裂，出現磁碟儲存的碎片化

30.索引哪些情況下會失效呢？

• 查詢條件包含or，可能導致索引失效
• 如果欄位類型是字串，where時一定用引號括起來，否則會因為隱含型別轉換，索引失效
• like通配符可能導致索引失效。
• 聯合索引，查詢時的條件列不是聯合索引中的第一個列，索引失效。
• 在索引列上使用mysql的內建函數，索引失效。
• 對索引列運算（如， 、-、*、/），索引失效。
• 索引欄位上使用（！= 或 ，not in）時，可能會導致索引失效。
• 索引欄位上使用is null， is not null，可能導致索引失效。
• 左連接查詢或右連接查詢查詢關聯的欄位編碼格式不一樣，可能導致索引失效。
• MySQL優化器估計使用全表掃描要比使用索引快,則不使用索引。

31.索引不適合哪些場景？

• 資料量比較少的表不適合加索引
• 更新比較頻繁的欄位也不適合加索引
• 離散低的欄位不適合加索引（如性別）

32.索引是不是建的越多越好？

當然不是。

• 索引會佔據磁碟空間
• 索引雖然會提高查詢效率，但是會降低更新表的效率。例如每次對錶進行增刪改操作，MySQL不僅要保存數據，還有保存或更新對應的索引檔。

33.MySQL索引用的什麼資料結構了解嗎？

MySQL的預設儲存引擎是InnoDB，它採用的是B 樹結構的索引。

• B 樹：只有葉子節點才會儲存數據，非葉子節點只儲存鍵值。葉子節點之間使用雙向指標連接，最底層的葉子節點形成了一個雙向有序鍊錶。

在這張圖裡，有兩個重點：

• 最外面的方塊，的區塊我們稱之為一個磁碟區塊，可以看到每個磁碟區塊包含幾個資料項目（粉紅色所示）和指標（黃色/灰色所示），如根節點磁碟包含資料項17和35，包含指標P1、P2、P3，P1表示小於17的磁碟區塊，P2表示在17和35之間的磁碟區塊，P3表示大於35的磁碟區塊。真實的資料存在於葉子節點即3、4、5……、65。非葉子節點只不儲存真實的數據，只儲存指引搜尋方向的數據項，如17、35並不真實存在於數據表中。
• 葉子節點之間使用雙向指標連接，最底層的葉子節點形成了一個雙向有序鍊錶，可以進行範圍查詢。

34.那一棵B 樹能儲存多少資料呢？

假設索引欄位是 bigint 類型，長度為 8 個位元組。指標大小在 InnoDB 原始碼中設定為 6 字節，這樣共 14 位元組。非葉子節點(一頁)可以儲存 16384/14=1170 個這樣的 單元(鍵值 指標)，代表有 1170 個指標。

樹深度為 2 的時候，有 1170^2 個葉子節點，可以儲存的資料為 1170117016=21902400。

在尋找資料時一次頁的查找代表一次 IO，也就是說，一張 2000 萬左右的表，查詢資料最多需要存取 3 次磁碟。

所以在 InnoDB 中 B 樹深度一般為 1-3 層，它就能滿足千萬級的資料儲存。

35.為什麼要用 B 樹，不用普通二元樹？

可以從幾個維度去看這個問題，查詢是否夠快，效率是否穩定，儲存資料多少，以及查找磁碟次數。

為什麼不用普通二元樹？

普通二元樹有退化的情況，如果它退化成鍊錶，相當於全表掃描。平衡二元樹相比於二元查找樹來說，查找效率更穩定，整體的查找速度也更快。

為什麼不用平衡二元樹呢？

讀取資料的時候，是從磁碟讀到記憶體。如果樹這種資料結構作為索引，那麼每查找一次資料就需要從磁碟中讀取一個節點，也就是一個磁碟區塊，但是平衡二元樹可是每個節點只儲存一個鍵值和資料的，如果是B 樹，可以儲存更多的節點數據，樹的高度也會降低，因此讀取磁碟的次數就降下來啦，查詢效率就快。

36.為什麼用 B 樹而不用 B 樹呢？

B 相比較B樹，有這些優點：

• 它是 B Tree 的變種，B Tree 能解決的問題，它都能解決。

  B Tree 解決的兩大問題：每個節點儲存更多關鍵字；路數更多

• #掃描庫、掃表能力更強

  如果我們要對錶進行全表掃描，只需要遍歷葉子節點就可以了，不需要遍歷整棵B Tree 拿到所有的資料。

• B Tree 的磁碟讀寫能力相對於B Tree 來說更強，IO次數更少

  根節點和枝節點不保存資料區， 所以一個節點可以儲存更多的關鍵字，一次磁碟載入的關鍵字更多，IO次數更少。

• 排序能力更強

  因為葉子節點上有下一個資料區的指針，資料形成了鍊錶。

• 效率更穩定

  B Tree 永遠是在葉子節點拿到數據，所以 IO 次數是穩定的。

37.Hash 索引和 B 樹索引差異是什麼？

• B 樹可以進行範圍查詢，Hash 索引不能。
• B 樹支援聯合索引的最左邊原則，Hash 索引不支援。
• B 樹支援 order by 排序，Hash 索引不支援。
• Hash 索引在等值查詢上比 B 樹更有效率。
• B 樹使用 like 進行模糊查詢的時候，like 後面（例如 % 開頭）的話可以起到優化的作用，Hash 索引根本無法進行模糊查詢。

38.叢集索引與非叢集索引的差異？

首先理解叢集索引不是一種新的索引，而是一種資料儲存方式。聚簇表示資料行和相鄰的鍵值緊密地儲存在一起。我們熟悉的兩種儲存引擎－MyISAM採用的是非叢集索引，InnoDB採用的是叢集索引。

可以這麼說：

• 索引的資料結構是樹，叢集索引的索引和資料儲存在一棵樹上，樹的葉子節點就是數據，非聚集索引索引和資料不在一棵樹上。

#

• 一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。
• 聚簇索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
• 聚簇索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

39.回表了解吗？

在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如:select * from user where name = ‘张三’;

40.覆盖索引了解吗？

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。

比如，select name from user where name = ‘张三’;

41.什么是最左前缀原则/最左匹配原则？

注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。

最左匹配原则：在InnoDB的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。

根据最左匹配原则，我们创建了一个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和 (a1,a2,a3) 三个索引。

为什么不从最左开始查，就无法匹配呢？

比如有一个user表，我们给 name 和 age 建立了一个组合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,age);

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，age 在右边)。

从这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。

这个时候我们使用where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较age。但是如果查询条件没有 name，就不知道下一步应该查哪个 节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引。

42.什么是索引下推优化？

索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL Server，MySQL Server进行过滤条件的判断。
• 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合MySQL Server传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于name使用了范围查询，根据最左匹配原则：

不使用ICP，引擎层查找到name like '张%'的数据，再由Server层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age。

但是，使用了索引下推优化，把where的条件放到了引擎层执行，直接根据name like '张%' and age=10的条件进行过滤，减少了回表的次数。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

锁

43.MySQL中有哪几种锁，列举一下？

如果按锁粒度划分，有以下3种：

• 表鎖定： 開銷小，加鎖快；鎖定力度大，發生鎖定衝突機率高，並發度最低;不會出現死鎖。
• 行鎖： 開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖衝突的機率低，並發度高。
• 頁鎖定： 開銷和加鎖速度介於表鎖定和行鎖之間；會出現死鎖；鎖定粒度介於表鎖和行鎖之間，並發度一般

#如果依照相容性，有兩種，

• 共享鎖定（S Lock）,也叫讀鎖定（read lock），互相不阻塞。
• 排他鎖（X Lock），也叫寫鎖（write lock），排它鎖是阻塞的，在一定時間內，只有一個請求能執行寫入，並阻止其它鎖讀取正在寫入的數據。

44.說說InnoDB裡的行鎖實作?

我們拿這麼一個用戶表來表示行級鎖，其中插入了4行數據，主鍵值分別是1,6,8,12，現在簡化它的叢集索引結構，只保留資料記錄。

InnoDB的行鎖定的主要實作如下：

• #Record Lock 記錄鎖定

記錄鎖定就是直接鎖定某行記錄。當我們使用唯一性的索引(包括唯一索引和叢集索引)進行等值查詢且精確地配對到一筆記錄時，此時就會直接將這條記錄鎖定。例如select * from t where id =6 for update;就會將id=6的記錄鎖定。

• Gap Lock 間隙鎖定

間隙鎖(Gap Locks) 的間隙指的是兩個記錄之間邏輯上尚未填入資料的部分,是一個左開右開空間。

間隙鎖定就是鎖定某些間隙區間的。當我們使用等值查詢或範圍查詢，並且沒有命中任何一個record#，此時就會將對應的間隙區間鎖定。例如select * from t where id =3 for update;或select * from t where id > 1 and id 就會(1,6)區間鎖定。

• Next-key Lock 臨鍵鎖定

#臨鍵指的是間隙加上它右邊的記錄所組成的左開右閉區間。例如上述的(1,6]、(6,8]等。

臨鍵鎖就是記錄鎖(Record Locks)和間隙鎖(Gap Locks)的結合，即除了鎖住記錄本身，還要再鎖住索引之間的間隙。當我們使用範圍查詢，並且命中了部分record記錄，此時鎖住的就是臨鍵區間。注意，臨鍵鎖鎖住的區間會包含最後一個record的右邊的臨鍵區間。例如select * from t where id > 5 and id 會被鎖住(4, 7]、(7, ∞)。mysql預設行鎖類型就是<code>臨鍵鎖(Next-Key Locks)。當使用唯一性索引，等值查詢匹配到一筆記錄的時候，臨鍵鎖(Next-Key Locks)會退化成記錄鎖；沒有匹配到任何記錄的時候，退化成間隙鎖。

##間隙鎖(Gap Locks)和臨鍵鎖定(Next-Key Locks)都是用來解決幻讀問題的，在已提交讀取（READ COMMITTED）隔離等級下，間隙鎖定(Gap Locks)和臨鍵鎖(Next-Key Locks)都會失效！

上面是行鎖的三種實作演算法，除此之外，在行上還存在插入意向鎖定。

• Insert Intention Lock 插入意向鎖定

一個交易在插入一筆記錄時需要判斷插入位置是不是被別的事務加了意向鎖，如果有的話，插入操作需要等待，直到擁有gap鎖的那個事務提交。但是事務在等待的時候也需要在內存中生成一個鎖結構，表明有事務想在某個間隙中插入新記錄，但是現在在等待。這種類型的鎖命名為Insert Intention Locks ，也就是插入意向鎖。

假如我們有個T1事務，給(1,6)區間加上了意向鎖，現在有個T2事務，要插入一個數據，id為4，它會取得一個（1,6）區間的插入意向鎖，又有有個T3事務，想要插入一個數據，id為3，它也會取得一個（1,6）區間的插入意向鎖，但是，這兩個插入意向鎖鎖不會互斥。

45.意向鎖是什麼知道嗎？

意向鎖是一個表級鎖，不要和插入意向鎖搞混。

意向鎖的出現是為了支援InnoDB的多粒度鎖，它解決的是表鎖和行鎖共存的問題。

當我們需要為一個表格加表鎖的時候，我們需要根據去判斷表中有沒有資料行被鎖定，以確定是否能加成功。

假如沒有意向鎖，那麼我們就得遍歷表中所有資料行來判斷有沒有行鎖；

有了意向鎖這個表級鎖定之後，則我們直接判斷一次就知道表中是否有資料行被鎖定了。

有了意向鎖之後，要執行的事務A在申請行鎖（寫鎖）之前，資料庫會自動先給事務A申請表的意向排他鎖。當事務B去申請表的互斥鎖時就會失敗，因為表上有意向排他鎖之後事務B申請表的互斥鎖時會被阻塞。

46.MySQL的樂觀鎖定和悲觀鎖定了解嗎？

• 悲觀鎖定（Pessimistic Concurrency Control）：

悲觀鎖定認為被它保護的資料是極不安全的，每時每刻都有可能被改動，一個事務拿到悲觀鎖後，其他任何事務都不能對該資料進行修改，只能等待鎖被釋放才可以執行。

資料庫中的行鎖，表鎖，讀鎖，寫鎖均為悲觀鎖。

• 樂觀鎖定（Optimistic Concurrency Control）

#樂觀鎖定認為資料的變動不會太頻繁。

樂觀鎖定通常是透過在表中增加一個版本(version)或時間戳(timestamp)來實現，其中，版本最為常用。

交易在從資料庫中取資料時，會將該資料的版本也取出來(v1)，當交易對資料變動完畢想要將其更新到表中時，會將先前取出的版本v1與資料中最新的版本v2相對比，如果v1=v2，那麼說明在資料變動期間，沒有其他事務對資料進行修改，此時，就允許事務對錶中的資料進行修改，並且修改時version會加1，以此來表明數據已被變動。

如果，v1不等於v2，那麼說明資料變動期間，資料被其他事務改動了，此時不允許資料更新到表中，一般的處理辦法是通知使用者讓其重新操作。不同於悲觀鎖，樂觀鎖通常是由開發者實現的。

47.MySQL 有遇過死鎖問題嗎，你是如何解決的？

排查死鎖的一般步驟是這樣的：

（1）看死鎖日誌show engine innodb status;

（2）找出死鎖sql

（3）分析sql 加鎖情況

（4）模擬死鎖案發

（5）分析死鎖日誌

（6）分析死鎖結果

當然，這只是一個簡單的流程說明，實際上生產中的死鎖千奇百怪，排查和解決起來沒那麼簡單。

事務

48.MySQL 事務的四大功能說一下？

• 原子性：事務作為一個整體被執行，包含在其中的對資料庫的操作要麼全部被執行，要麼都不執行。
• 一致性：指在事務開始之前和事務結束以後，資料不會被破壞，假如A 帳戶給B 帳戶轉10 塊錢，不管成功與否，A 和B 的總金額是不變的。
• 隔離性：多個交易並發存取時，交易之間是相互隔離的，即一個交易不影響其它交易運作效果。簡言之，就是事務之間是進水不犯河水的。
• 持久性：表示交易完成以後，該交易對資料庫所做的操作更改，將持久地保存在資料庫之中。

49.那ACID靠什麼保證的呢？

• 交易的隔離性是透過資料庫鎖定的機制實現的。
• 交易的一致性由undo log來保證：undo log是邏輯日誌，記錄了事務的insert、update、deltete操作，回滾的時候做相反的delete、update、 insert操作來恢復資料。
• 事務的原子性和持久性由redo log來保證：redolog被稱作重做日誌，是物理日誌，事務提交的時候，必須先將交易的所有日誌寫入redo log持久化，到交易的提交操作才算完成。

50.交易的隔離等級有哪些？ MySQL 的預設隔離等級是什麼？

• 讀取未提交（Read Uncommitted）
• 讀取已提交（Read Committed）
• 可重複讀取（Repeatable Read )
• 串行化（Serializable）

MySQL預設的交易隔離等級是可重複讀取(Repeatable Read)。

51.什麼是幻讀，髒讀，不可重複讀呢？

• 交易 A、B 交替執行，事務 A 讀取到事務 B 未提交的數據，這就是髒讀。
• 在一個事務範圍內，兩個相同的查詢，讀取同一條記錄，卻返回了不同的數據，這就是不可重複讀取。
• 事務A 查詢一個範圍的結果集，另一個並發事務B 往這個範圍中插入/ 刪除了數據，並靜悄悄地提交，然後事務A 再次查詢相同的範圍，兩次讀取得到的結果集不一樣了，這就是幻讀。

不同的隔離級別，在並發交易下可能會發生的問題：

#功能	MylSAM	MEMORY	InnoDB
。儲存限制	256TB	RAM	64TB
支援交易	No	No	#Yes
支援全文索引	Yes	No	Yes
#支援樹索引
		##Yes
No	N/A	#Yes
No	No	Yes

隔離級別	髒讀	不可重複讀取	幻讀
#Read Uncommited 讀取未提交	#是	是	是
Read Commited 讀取已提交	否		否
Repeatable Read 可重複讀取	否	否	是

########################################################## Serialzable 可串列化######否######否######否##################################

52.事務的各個隔離等級都是如何實現的？

讀取未提交

讀取未提交，就不用多說了，採取的是讀不加鎖原理。

• 交易讀取不加鎖，不阻塞其他交易的讀取和寫入
• 交易寫入阻塞其他交易寫，但不阻塞其他交易讀取；

#讀取已提交&可重複讀取

讀取已提交和可重複讀取層級利用了ReadView和MVCC，也就是每個事務只能讀取它所能看到的版本（ReadView）。

• READ COMMITTED：每次讀取資料前都會產生一個ReadView
• REPEATABLE READ ： 在第一次讀取資料時產生一個ReadView

#串行化

串行化的實現採用的是讀寫都加鎖的原理。

序列化的情況下，對於同一行事務，寫入會加上寫入鎖定，讀取會加上讀鎖。當出現讀寫鎖定衝突的時候，後存取的事務必須等前一個事務執行完成，才能繼續執行。

53.MVCC了解嗎？怎麼實現的？

MVCC(Multi Version Concurrency Control)，中文名稱是多版本並發控制，簡單來說就是透過維護資料歷史版本，從而解決並發存取情況下的讀取一致性問題。關於它的實現，要抓住幾個關鍵點，隱式欄位、undo日誌、版本鏈、快照讀取&目前讀取、Read View。

版本鏈

對於InnoDB儲存引擎，每一行記錄都有兩個隱藏欄位DB_TRX_ID、DB_ROLL_PTR

• #DB_TRX_ID，事務ID，每次修改時，都會把該交易ID複製給DB_TRX_ID;
• DB_ROLL_PTR，回滾指標，指向回滾段的undo日誌。

假如有一張user表，表中只有一行記錄，當時插入的交易id為80。此時，該筆記錄的範例圖如下：

接下來有兩個DB_TRX_ID#分別為100、200的交易對這條記錄進行update操作，整個過程如下：

由於每次變動都會先把 undo日誌記錄下來，並用DB_ROLL_PTR指向undo日誌位址。因此可以認為，對該筆記錄的修改日誌串連起來就形成了一個版本鏈，版本鏈的頭節點就是目前記錄最新的值。如下：

ReadView

#對於Read Committed與Repeatable Read隔離等級來說，都需要讀取已經提交的事務所修改的記錄，也就是說如果版本鏈中某個版本的修改沒有提交，那麼該版本的記錄時不能被讀取的。所以需要確定在Read Committed和Repeatable Read隔離等級下，版本鏈中哪個版本是能被目前交易讀取的。於是就引入了ReadView這個概念來解決這個問題。

Read View就是交易執行快照讀取時，產生的讀取視圖，相當於某時刻表記錄的一個快照，透過這個快照，我們可以取得：

• m_ids ：表示在產生ReadView 時目前系統中活躍的讀寫事務的交易id 清單。
• min_trx_id ：表示在產生 ReadView 時目前系統中活躍的讀寫事務中最小的 事務id ，也就是 m_ids 中的最小值。
• max_trx_id ：表示產生 ReadView 時系統中應該指派給下一個交易的 id 值。
• creator_trx_id ：表示產生該ReadView 的事務的事務id

有了這個ReadView ，這樣在存取某筆記錄時，只需要按照下邊的步驟判斷記錄的某個版本是否可見：

• 如果被存取版本的 DB_TRX_ID 屬性值與 ReadView 中的 creator_trx_id 值相同，表示目前事務在存取它自己修改過的記錄，所以該版本可以被目前事務存取。
• 如果被存取版本的 DB_TRX_ID 屬性值小於 ReadView 中的 min_trx_id 值，表示產生該版本的事務在目前事務產生 ReadView 前已經提交，所以該版本可以被目前事務存取。
• 如果被存取版本的 DB_TRX_ID 屬性值大於 ReadView 中的 max_trx_id 值，表示產生該版本的事務在目前事務產生 ReadView 後才開啟，所以該版本不可以被目前事務存取。
• 如果被存取版本的DB_TRX_ID 屬性值在ReadView 的min_trx_id 和max_trx_id 之間，那就需要判斷一下trx_id 屬性值是不是在m_ids 清單中，如果在，說明建立ReadView 時產生該版本的事務還是活躍的，該版本不可以被存取；如果不在，說明創建ReadView 時產生該版本的事務已經被提交，該版本可以被存取。

如果某個版本的數據對當前事務不可見的話，那就順著版本鏈找到下一個版本的數據，繼續按照上邊的步驟判斷可見性，依此類推，直到版本鏈中的最後一個版本。如果最後一個版本也不可見的話，那麼就表示該筆記錄對該交易完全不可見，查詢結果就不包含該記錄。

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔離等級的一個非常大的差異就是它們產生ReadView的時機不同。

READ COMMITTED 是每次讀取資料前都會產生一個ReadView，這樣就能保證自己每次都能讀到其它事務提交的資料；REPEATABLE READ 是在第一次讀取資料時產生一個ReadView，這樣就能確保後續讀取的結果完全一致。

高可用/效能

54.資料庫讀寫分離了解嗎？

讀寫分離的基本原理是將資料庫讀寫操作分散到不同的節點上，以下是基本架構圖：

##讀寫分離的基本實作是:

資料庫伺服器搭建主從集群，一主一從、一主多從都可以。
• 資料庫主機負責讀寫操作，從機只負責讀取操作。
• 資料庫主機透過複製將資料同步到從機，每台資料庫伺服器都儲存了所有的業務資料。
• 業務伺服器將寫入作業發給資料庫主機，並將讀取操作傳送給資料庫從機。

55.那讀寫分離的分配怎麼實現呢？

將讀寫操作區分開來，然後存取不同的資料庫伺服器，一般有兩種方式：程式碼封裝和中間件封裝。

1、程式碼封裝

程式碼封裝指在程式碼中抽象化一個資料存取層（所以有的文章也稱這種方式為"中間層封裝" ） ，實現讀寫操作分離與資料庫伺服器連線的管理。例如，基於Hibernate 進行簡單封裝，就可以實現讀寫分離：

目前開源的實作方案中，淘寶的TDDL (Taobao Distributed Data Layer, 外號：頭都大了）是比較有名的。

2、中間件封裝

中間件封裝指的是獨立一套系統出來，實現讀寫操作分離和資料庫伺服器連接的管理。中間件提供業​​務伺服器 SQL 相容的協議，業務伺服器無須自行進行讀寫分離。

對於業務伺服器來說，存取中間件和存取資料庫沒有區別，事實上在業務伺服器看來，中間件就是一個資料庫伺服器。

其基本架構是：

56.主從複製原理了解嗎？

master資料寫入，更新binlog
• master建立一個dump線程向slave推送binlog
• slave連接到master的時候，會建立一個IO線程接收binlog，並記錄到relay log中繼日誌中
• slave再開啟一個sql線程讀取relay log事件並在slave執行，完成同步
• slave記錄自己的binglog

57.主從同步延遲怎麼處理？

主從同步延遲的原因

一個伺服器開放Ｎ個連結給客戶端來連接的，這樣有會有大並發的更新操作, 但是從伺服器的里面讀取binlog 的線程僅有一個，當某個SQL 在從伺服器上執行的時間稍長或由於某個SQL 要進行鎖定表就會導致，主伺服器的SQL 大量積壓，並未同步到從伺服器。這就導致了主從不一致， 也就是主從延遲。

主從同步延遲的解決方案

解決主從複製延遲有幾種常見的方法:

• 寫操作後的讀取操作指定發給資料庫主伺服器

例如，註冊帳號完成後，登入時讀取帳號的讀取操作也會發給資料庫主伺服器。這種方式和業務強綁定，對業務的侵入和影響較大，如果哪個新來的程式設計師不知道這樣寫程式碼，就會導致一個bug。

• 讀取從機失敗後再讀一次主機

#這就是通常所說的"二次讀取" ，二次讀取和業務無綁定，只需要對底層資料庫存取的API 進行封裝即可，實現代價較小，不足之處在於如果有很多二次讀取，將大大增加主機的讀取操作壓力。例如，駭客暴力破解帳號，會導致大量的二次讀取操作，主機可能頂不住讀操作的壓力而崩潰。

• 關鍵業務讀寫操作全部指向主機，非關鍵業務採用讀寫分離

#例如，對於一個使用者管理系統來說，註冊登入的業務讀寫操作全部存取主機，使用者的介紹、爰好、等級等業務，可以採用讀寫分離，因為即使使用者改了自己的自我介紹，在查詢時卻看到了自我介紹還是舊的，業務影響與不能登入相比就小很多，還可以忍受。

58.你們通常是怎麼分庫的呢？

• 垂直分庫：以表為依據，依業務歸屬不同，將不同的表拆分到不同的庫中。

• 水平分庫：以欄位為依據，依照某一策略（hash、range 等），將一個庫中的資料拆分到多個個庫中。

59.那你們是怎麼分錶的？

• 水平分錶：以欄位為依據，依照某一策略（hash、range 等），將一個表格中的資料拆分到多個表格中。
• 垂直分錶：以欄位為依據，依照欄位的活躍性，將表格中欄位拆到不同的表（主表和擴充表）。

60.水平分錶有哪幾種路由方式？

什麼是路由呢？就是數據應該分到哪一張表。

水平分錶主要有三種路由方式：

• 範圍路由：選取有序的資料列（例如，整形、時間戳記等） 作為路由的條件，不同分段分散到不同的資料庫表。

我們可以觀察一些支付系統，發現只能查一年範圍內的支付記錄，這可能就是支付公司按照時間進行了分錶。

範圍路由設計的複雜點主要體現在分段大小的選取上，分段太小會導致切分後子表數量過多，增加維護複雜度；分段太大可能會導致單表仍存在效能問題，一般建議分段大小在100 萬至2000 萬之間，具體需要根據業務選取適當的分段大小。

範圍路由的優點是可以隨著資料的增加平滑地擴充新的表。例如，現在的用戶是 100 萬，如果增加到 1000 萬，只需要增加新的表就可以了，原有的數據不需要動。範圍路由的一個比較隱含的缺點是分佈不均勻，假如按照1000 萬來進行分錶，有可能某個分段實際儲存的資料量只有1000 條，而另外一個分段實際儲存的資料量有900萬條。

• Hash 路由：選取某個欄位 （或某幾個欄位組合也可以） 的值進行 Hash 運算，然後根據 Hash 結果分散到不同的資料庫表中。

同樣以訂單id 為例，假如我們一開始就規劃了4個資料庫表，路由演算法可以簡單地用id % 4 的值來表示資料所屬的資料庫表編號，id 為12的訂單放到編號50的子表中，id為13的訂單放到編號61的字表中。

Hash 路由設計的複雜點主要體現在初始表數量的選取上，表數量太多維護比較麻煩，表數量太少又可能導致單表效能有問題。而用了 Hash 路由後，增加子表數量是非常麻煩的，所有資料都要重分佈。 Hash 路由的優缺點和範圍路由基本上相反，Hash 路由的優點是表分佈比較均勻，缺點是擴充新的表很麻煩，所有資料都要重分佈。

• 設定路由：設定路由就是路由表，用一張獨立的表來記錄路由資訊。同樣以訂單id 為例，我們新增一張 order_router 表，這個表包含 orderjd 和 tablejd 兩個欄位 , 根據 orderjd 就可以查詢對應的 table_id。

設定路由設計簡單，使用起來非常靈活，尤其是在擴充表的時候，只需要遷移指定的數據，然後修改路由表就可以了。

配置路由的缺點就是必須多查詢一次，會影響整體效能；而且路由表本身如果太大（例如，數億個資料） ，效能同樣可能成為瓶頸，如果我們再次將路由表分庫分錶，又面臨一個死循環式的路由演算法選擇問題。

61.不停機擴容怎麼實現？

實際上，不停機擴容，實操起來是個非常麻煩而且很有風險的操作，當然，面試回答起來就簡單很多。

• 第一階段：線上雙寫，查詢走舊庫

  • #建立好新的庫表結構，資料寫入久庫的同時，也寫入拆分的新庫

  • 資料遷移，使用資料遷移程序，將舊庫中的歷史資料遷移到新庫

  • 使用定時任務，新舊函式庫的資料對比，把差異補齊

• 第二階段：線上雙寫，查詢走新函式庫

  • #完成了歷史資料的同步與校驗

  • #把對資料的讀取切換到新函式庫

#第三階段：舊函式庫下線

#舊函式庫不再寫入新的資料

• 經過一段時間，確定舊庫沒有請求之後，就可以下線舊庫

• #62.常用的分庫分錶中間件有哪些？

sharding-jdbc

Mycat

#63.那你覺得分庫分錶會帶來什麼問題呢？
• 從分庫的角度來講：

交易的問題

使用關係型資料庫，有很大一點在於它保證事務完整性。

而分庫之後單機事務就用不上了，必須使用分散式事務來解決。

跨庫JOIN 問題

在一個庫中的時候我們還可以利用JOIN 來連表查詢，而跨庫了之後就無法使用JOIN 了。

此時的解決方案就是

在業務代碼中進行關聯

，也就是先把一個表的資料查出來，然後透過得到的結果再去查另一張表，然後利用程式碼來關聯得到最終的結果。
• 這種方式實現起來稍微比較複雜，不過也是可以接受的。
還有可以

適當的冗餘一些欄位

。例如先前的表格就儲存一個關聯 ID，但業務時常要求傳回對應的 Name 或其他欄位。這時候就可以把這些欄位冗餘到目前表中，來移除需要關聯的操作。

還有一種方式就是
• 資料異質，透過binlog同步等方式，把需要跨庫join的資料異構到ES等儲存結構中，透過ES進行查詢。
從分錶的角度來看：

跨節點的count,order by,group by 以及聚合函數問題
只能由業務代碼來實現或用中間件將各表中的資料匯總、排序、分頁然後返回。

###資料遷移，容量規劃，擴容等問題#############資料的遷移，容量如何規劃，未來是否可能再次需要擴容，等等，都是需要考慮的問題。 ############ID 問題############資料庫表被切分後，就不能再依賴資料庫本身的主鍵產生機制，所以需要一些手段來確保全域主鍵唯一。 ###

• 還是自增，只不過自增步長設定一下。例如現在有三張表，步長設定為3，三張表 ID 初始值分別是1、2、3。這樣第一張表的 ID 成長是 1、4、7。第二張表是2、5、8。第三張表是3、6、9，這樣就不會重複了。

• UUID，這種最簡單，但是不連續的主鍵插入會導致嚴重的頁分裂，效能比較差。

• 分散式ID，比較有名的是Twitter 開源的sonwflake 雪花演算法

維運

##64.百萬級別以上的資料如何刪除？

關於索引：由於索引需要額外的維護成本，因為索引檔案是單獨存在的檔案,所以當我們對資料的增加,修改,刪除,都會產生額外的對索引檔案的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執行效率。

所以，在我們刪除資料庫百萬級資料的時候，查詢MySQL官方手冊得知刪除資料的速度和建立的索引數量是成正比的。

• 所以我們想要刪除百萬資料的時候可以先刪除索引

• #然後刪除其中無用資料

• #刪除完成後重新建立索引建立索引也非常快速

65.百萬千萬大表如何新增欄位？

當線上的資料庫資料量到達數百萬、上千萬的時候，加一個欄位就沒那麼簡單，因為可能會長時間鎖定表。

大表添加字段，通常有這些做法：

• 透過中間表轉換過去
  建立一個臨時的新表，把舊表的結構完全複製過去，添加字段，再把舊表資料複製過去，刪除舊表，新表命名為舊表的名稱，這種方式可能回丟掉一些資料。

• 用pt-online-schema-change

  #pt-online-schema-change是percona公司開發的工具，它可以在線修改表結構，它的原理也是透過中間表。

• 先在從庫加入再進行主從切換
  如果一張表資料量大且是熱表（讀寫特別頻繁），則可以考慮先在從庫添加，再進行主從切換，切換後再將其他幾個節點上添加字段。

66.MySQL 資料庫 cpu 飆升的話，要怎麼處理呢？

排查流程：

（1）使用 top 指令觀察，確定是 mysqld 導致還是其他原因。

（2）如果是 mysqld 導致的，show processlist，查看 session 情況，確定是否有消耗資源的 sql 在運作。

（3）找出消耗高的 sql，看看執行計畫是否準確， 索引是否缺失，資料量是否太大。

處理：

（1）kill 掉這些執行緒(同時觀察cpu 使用率是否下降)，

（2）進行對應的調整(比如說加索引、改sql、改記憶體參數)

（3）重新跑這些SQL。

其他情況：

也有可能是每個sql 消耗資源並不多，但是突然之間，有大量的session 連進來導致cpu 飆升，這種情況就需要跟應用一起來分析為何連接數會激增，再做出相應的調整，比如說限制連接數等

【相關推薦：

mysql視頻教程】