作為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={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。

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有什么區別？

• blob用于存儲二進制數據，而text用于存儲大字符串。
• blob沒有字符集，text有一個字符集，并且根據字符集的校對規則對值進行排序和比較

6.DATETIME和TIMESTAMP的異同？

相同點：

0. 兩個數據類型存儲時間的表現格式一致。均為 YYYY-MM-DD HH:MM:SS
1. 兩個數據類型都包含「日期」和「時間」部分。
2. 兩個數據類型都可以存儲微秒的小數秒（秒后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到9999999.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的區別？

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

因此，在不再需要一張表的時候，用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子句中的左表<left_table>和右表<right_table>執行笛卡兒積（Cartesianproduct），產生虛擬表VT1

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

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

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

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

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

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

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

• DISTINCT：去除重復數據，產生虛擬表VT9

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

• LIMIT：取出指定行的記錄，產生虛擬表VT11，并返回給查詢用戶

數據庫架構

14.說說 MySQL 的基礎架構?

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

• 客戶端：最上層的服務并不是MySQL所獨有的，大多數基于網絡的客戶端/服務器的工具或者服務都有類似的架構。比如連接處理、授權認證、安全等等。
• Server層：大多數MySQL的核心服務功能都在這一層，包括查詢解析、分析、優化、緩存以及所有的內置函數（例如，日期、時間、數學和加密函數），所有跨存儲引擎的功能都在這一層實現：存儲過程、觸發器、視圖等。
• 存儲引擎層：第三層包含了存儲引擎。存儲引擎負責MySQL中數據的存儲和提取。Server層通過API與存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明。

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

• 先檢查該語句是否有權限，如果沒有權限，直接返回錯誤信息，如果有權限會先查詢緩存 (MySQL8.0 版本以前)。
• 如果沒有緩存，分析器進行語法分析，提取 sql 語句中 select 等關鍵元素，然后判斷 sql 語句是否有語法錯誤，比如關鍵詞是否正確等等。
• 語法解析之后，MySQL的服務器會對查詢的語句進行優化，確定執行的方案。
• 完成查詢優化后，按照生成的執行計劃調用數據庫引擎接口，返回執行結果。

存儲引擎

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

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

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<’aaa’，就可以使用索引了

適當使用前綴索引

適當地使用前綴所云，可以降低索引的空間占用，提高索引的查詢效率。

比如，郵箱的后綴都是固定的“@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會占用