數據庫

1. MySQL索引使用有哪些注意事項呢？

可以從三個維度回答這個問題：索引哪些情況會失效，索引不適合哪些場景，索引規則

索引哪些情況會失效

查詢條件包含or，可能導致索引失效

如何字段類型是字符串，where時一定用引號括起來，否則索引失效

like通配符可能導致索引失效。

聯合索引，查詢時的條件列不是聯合索引中的第一個列，索引失效。

在索引列上使用mysql的內置函數，索引失效。

對索引列運算（如，+、-、*、/），索引失效。

索引字段上使用（！= 或者 《 》，not in）時，可能會導致索引失效。

索引字段上使用is null， is not null，可能導致索引失效。

左連接查詢或者右連接查詢查詢關聯的字段編碼格式不一樣，可能導致索引失效。

mysql估計使用全表掃描要比使用索引快，則不使用索引。

索引不適合哪些場景

數據量少的不適合加索引

更新比較頻繁的也不適合加索引

區分度低的字段不適合加索引（如性別）

索引的一些潛規則

覆蓋索引

回表

索引數據結構（B+樹）

最左前綴原則

索引下推

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

我排查死鎖的一般步驟是醬紫的：

查看死鎖日志show engine innodb status;

找出死鎖Sql

分析sql加鎖情況

模擬死鎖案發

分析死鎖日志

分析死鎖結果

3. 日常工作中你是怎么優化SQL的？

可以從這幾個維度回答這個問題：

加索引

避免返回不必要的數據

適當分批量進行

優化sql結構

分庫分表

讀寫分離

4. 說說分庫與分表的設計

分庫分表方案，分庫分表中間件，分庫分表可能遇到的問題

分庫分表方案：

水平分庫：以字段為依據，按照一定策略（hash、range等），將一個庫中的數據拆分到多個庫中。

水平分表：以字段為依據，按照一定策略（hash、range等），將一個表中的數據拆分到多個表中。

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

垂直分表：以字段為依據，按照字段的活躍性，將表中字段拆到不同的表（主表和擴展表）中。

常用的分庫分表中間件：

sharding-jdbc（當當）

Mycat

TDDL（淘寶）

Oceanus（58同城數據庫中間件）

vitess（谷歌開發的數據庫中間件）

Atlas（Qihoo 360）

分庫分表可能遇到的問題

事務問題：需要用分布式事務啦

跨節點Join的問題：解決這一問題可以分兩次查詢實現

跨節點的count，order by，group by以及聚合函數問題：分別在各個節點上得到結果后在應用程序端進行合并。

數據遷移，容量規劃，擴容等問題

ID問題：數據庫被切分后，不能再依賴數據庫自身的主鍵生成機制啦，最簡單可以考慮UUID

跨分片的排序分頁問題（后臺加大pagesize處理？）

5. InnoDB與MyISAM的區別

InnoDB支持事務，MyISAM不支持事務

InnoDB支持外鍵，MyISAM不支持外鍵

InnoDB 支持 MVCC（多版本并發控制），MyISAM 不支持

select count（*） from table時，MyISAM更快，因為它有一個變量保存了整個表的總行數，可以直接讀取，InnoDB就需要全表掃描。

Innodb不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）

InnoDB支持表、行級鎖，而MyISAM支持表級鎖。

InnoDB表必須有主鍵，而MyISAM可以沒有主鍵

Innodb表需要更多的內存和存儲，而MyISAM可被壓縮，存儲空間較小，。

Innodb按主鍵大小有序插入，MyISAM記錄插入順序是，按記錄插入順序保存。

InnoDB 存儲引擎提供了具有提交、回滾、崩潰恢復能力的事務安全，與 MyISAM 比 InnoDB 寫的效率差一些，并且會占用更多的磁盤空間以保留數據和索引

6. 數據庫索引的原理，為什么要用B+樹，為什么不用二叉樹？

可以從幾個維度去看這個問題，查詢是否夠快，效率是否穩定，存儲數據多少，以及查找磁盤次數，為什么不是二叉樹，為什么不是平衡二叉樹，為什么不是B樹，而偏偏是B+樹呢？

為什么不是一般二叉樹？

如果二叉樹特殊化為一個鏈表，相當于全表掃描。平衡二叉樹相比于二叉查找樹來說，查找效率更穩定，總體的查找速度也更快。

為什么不是平衡二叉樹呢？

我們知道，在內存比在磁盤的數據，查詢效率快得多。如果樹這種數據結構作為索引，那我們每查找一次數據就需要從磁盤中讀取一個節點，也就是我們說的一個磁盤塊，但是平衡二叉樹可是每個節點只存儲一個鍵值和數據的，如果是B樹，可以存儲更多的節點數據，樹的高度也會降低，因此讀取磁盤的次數就降下來啦，查詢效率就快啦。

那為什么不是B樹而是B+樹呢？

1）B+樹非葉子節點上是不存儲數據的，僅存儲鍵值，而B樹節點中不僅存儲鍵值，也會存儲數據。innodb中頁的默認大小是16KB，如果不存儲數據，那么就會存儲更多的鍵值，相應的樹的階數（節點的子節點樹）就會更大，樹就會更矮更胖，如此一來我們查找數據進行磁盤的IO次數有會再次減少，數據查詢的效率也會更快。

2）B+樹索引的所有數據均存儲在葉子節點，而且數據是按照順序排列的，鏈表連著的。那么B+樹使得范圍查找，排序查找，分組查找以及去重查找變得異常簡單。

7. 聚集索引與非聚集索引的區別

一個表中只能擁有一個聚集索引，而非聚集索引一個表可以存在多個。

聚集索引，索引中鍵值的邏輯順序決定了表中相應行的物理順序；非聚集索引，索引中索引的邏輯順序與磁盤上行的物理存儲順序不同。

索引是通過二叉樹的數據結構來描述的，我們可以這么理解聚簇索引：索引的葉節點就是數據節點。而非聚簇索引的葉節點仍然是索引節點，只不過有一個指針指向對應的數據塊。

聚集索引：物理存儲按照索引排序；非聚集索引：物理存儲不按照索引排序；

何時使用聚集索引或非聚集索引？

8. limit 1000000加載很慢的話，你是怎么解決的呢？

方案一：如果id是連續的，可以這樣，返回上次查詢的最大記錄（偏移量），再往下limit

select id，name from employee where id》1000000 limit 10.

方案二：在業務允許的情況下限制頁數：

建議跟業務討論，有沒有必要查這么后的分頁啦。因為絕大多數用戶都不會往后翻太多頁。

方案三：order by + 索引（id為索引）

select id，name from employee order by id limit 1000000，10

方案四：利用延遲關聯或者子查詢優化超多分頁場景。（先快速定位需要獲取的id段，然后再關聯）

SELECT a.* FROM employee a， （select id from employee where 條件 LIMIT 1000000，10 ） b where a.id=b.id

9. 如何選擇合適的分布式主鍵方案呢？

數據庫自增長序列或字段。

UUID。

Redis生成ID

Twitter的snowflake算法

利用zookeeper生成唯一ID

MongoDB的ObjectId

10. 事務的隔離級別有哪些？MySQL的默認隔離級別是什么？

讀未提交（Read Uncommitted）

讀已提交（Read Committed）

可重復讀（Repeatable Read）

串行化（Serializable）

Mysql默認的事務隔離級別是可重復讀（Repeatable Read）

11. 什么是幻讀，臟讀，不可重復讀呢？

事務A、B交替執行，事務A被事務B干擾到了，因為事務A讀取到事務B未提交的數據，這就是臟讀

在一個事務范圍內，兩個相同的查詢，讀取同一條記錄，卻返回了不同的數據，這就是不可重復讀。

事務A查詢一個范圍的結果集，另一個并發事務B往這個范圍中插入/刪除了數據，并靜悄悄地提交，然后事務A再次查詢相同的范圍，兩次讀取得到的結果集不一樣了，這就是幻讀。

12. 在高并發情況下，如何做到安全的修改同一行數據？

要安全的修改同一行數據，就要保證一個線程在修改時其它線程無法更新這行記錄。一般有悲觀鎖和樂觀鎖兩種方案~

使用悲觀鎖

悲觀鎖思想就是，當前線程要進來修改數據時，別的線程都得拒之門外~比如，可以使用select…for update ~

select * from User where name=‘jay’ for update

以上這條sql語句會鎖定了User表中所有符合檢索條件（name=‘jay’）的記錄。本次事務提交之前，別的線程都無法修改這些記錄。

使用樂觀鎖

樂觀鎖思想就是，有線程過來，先放過去修改，如果看到別的線程沒修改過，就可以修改成功，如果別的線程修改過，就修改失敗或者重試。實現方式：樂觀鎖一般會使用版本號機制或CAS算法實現。

13. 數據庫的樂觀鎖和悲觀鎖。

悲觀鎖：

悲觀鎖她專一且缺乏安全感了，她的心只屬于當前事務，每時每刻都擔心著它心愛的數據可能被別的事務修改，所以一個事務擁有（獲得）悲觀鎖后，其他任何事務都不能對數據進行修改啦，只能等待鎖被釋放才可以執行。

樂觀鎖：

樂觀鎖的“樂觀情緒”體現在，它認為數據的變動不會太頻繁。因此，它允許多個事務同時對數據進行變動。實現方式：樂觀鎖一般會使用版本號機制或CAS算法實現。

14. SQL優化的一般步驟是什么，怎么看執行計劃（explain），如何理解其中各個字段的含義。

show status 命令了解各種 sql 的執行頻率

通過慢查詢日志定位那些執行效率較低的 sql 語句

explain 分析低效 sql 的執行計劃（這點非常重要，日常開發中用它分析Sql，會大大降低Sql導致的線上事故）

15. select for update有什么含義，會鎖表還是鎖行還是其他。

select for update 含義

select查詢語句是不會加鎖的，但是select for update除了有查詢的作用外，還會加鎖呢，而且它是悲觀鎖哦。至于加了是行鎖還是表鎖，這就要看是不是用了索引/主鍵啦。

沒用索引/主鍵的話就是表鎖，否則就是是行鎖。

select for update 加鎖驗證

表結構：

//id 為主鍵，name為唯一索引

CREATE TABLE `account` （

`id` int（11） NOT NULL AUTO_INCREMENT，

`name` varchar（255） DEFAULT NULL，

`balance` int（11） DEFAULT NULL，

PRIMARY KEY （`id`），

KEY `idx_name` （`name`） USING BTREE

） ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8

id為主鍵，select for update 1270070這條記錄時，再開一個事務對該記錄更新，發現更新阻塞啦，其實是加鎖了。如下圖：

我們再開一個事務對另外一條記錄1270071更新，發現更新成功，因此，如果查詢條件用了索引/主鍵，會加行鎖~

我們繼續一路向北吧，換普通字段balance吧，發現又阻塞了。因此，沒用索引/主鍵的話，select for update加的就是表鎖

16. MySQL事務得四大特性以及實現原理

原子性：事務作為一個整體被執行，包含在其中的對數據庫的操作要么全部被執行，要么都不執行。

一致性：指在事務開始之前和事務結束以后，數據不會被破壞，假如A賬戶給B賬戶轉10塊錢，不管成功與否，A和B的總金額是不變的。

隔離性：多個事務并發訪問時，事務之間是相互隔離的，即一個事務不影響其它事務運行效果。簡言之，就是事務之間是進水不犯河水的。

持久性：表示事務完成以后，該事務對數據庫所作的操作更改，將持久地保存在數據庫之中。

事務ACID特性的實現思想

原子性：是使用 undo log來實現的，如果事務執行過程中出錯或者用戶執行了rollback，系統通過undo log日志返回事務開始的狀態。

持久性：使用 redo log來實現，只要redo log日志持久化了，當系統崩潰，即可通過redo log把數據恢復。

隔離性：通過鎖以及MVCC，使事務相互隔離開。

一致性：通過回滾、恢復，以及并發情況下的隔離性，從而實現一致性。

17. 如果某個表有近千萬數據，CRUD比較慢，如何優化。

分庫分表

某個表有近千萬數據，可以考慮優化表結構，分表（水平分表，垂直分表），當然，你這樣回答，需要準備好面試官問你的分庫分表相關問題呀，如

分表方案（水平分表，垂直分表，切分規則hash等）

分庫分表中間件（Mycat，sharding-jdbc等）

分庫分表一些問題（事務問題？跨節點Join的問題）

解決方案（分布式事務等）

索引優化

除了分庫分表，優化表結構，當然還有所以索引優化等方案~

18. 如何寫sql能夠有效的使用到復合索引。

復合索引，也叫組合索引，用戶可以在多個列上建立索引，這種索引叫做復合索引。

當我們創建一個組合索引的時候，如（k1，k2，k3），相當于創建了（k1）、（k1，k2）和（k1，k2，k3）三個索引，這就是最左匹配原則。

select * from table where k1=A AND k2=B AND k3=D

有關于復合索引，我們需要關注查詢Sql條件的順序，確保最左匹配原則有效，同時可以刪除不必要的冗余索引。

19. mysql中in 和exists的區別。

這個，跟一下demo來看更刺激吧，啊哈哈

假設表A表示某企業的員工表，表B表示部門表，查詢所有部門的所有員工，很容易有以下SQL：

select * from A where deptId in （select deptId from B）;

這樣寫等價于：

先查詢部門表B

select deptId from B

再由部門deptId，查詢A的員工

select * from A where A.deptId = B.deptId

可以抽象成這樣的一個循環：

List《》 resultSet ;

for（int i=0;i《B.length;i++） {

for（int j=0;j《A.length;j++） {

if（A［i］.id==B［j］.id） {

resultSet.add（A［i］）;

break;

}

}

}

顯然，除了使用in，我們也可以用exists實現一樣的查詢功能，如下：

select * from A where exists （select 1 from B where A.deptId = B.deptId）;

因為exists查詢的理解就是，先執行主查詢，獲得數據后，再放到子查詢中做條件驗證，根據驗證結果（true或者false），來決定主查詢的數據結果是否得意保留。

那么，這樣寫就等價于：

select * from A，先從A表做循環

select * from B where A.deptId = B.deptId，再從B表做循環。

同理，可以抽象成這樣一個循環：

List《》 resultSet ;

for（int i=0;i《A.length;i++） {

for（int j=0;j《B.length;j++） {

if（A［i］.deptId==B［j］.deptId） {

resultSet.add（A［i］）;

break;

}

}

}

數據庫最費勁的就是跟程序鏈接釋放。假設鏈接了兩次，每次做上百萬次的數據集查詢，查完就走，這樣就只做了兩次；相反建立了上百萬次鏈接，申請鏈接釋放反復重復，這樣系統就受不了了。即mysql優化原則，就是小表驅動大表，小的數據集驅動大的數據集，從而讓性能更優。

因此，我們要選擇最外層循環小的，也就是，如果B的數據量小于A，適合使用in，如果B的數據量大于A，即適合選擇exists，這就是in和exists的區別。

20. 數據庫自增主鍵可能遇到什么問題。

使用自增主鍵對數據庫做分庫分表，可能出現諸如主鍵重復等的問題。解決方案的話，簡單點的話可以考慮使用UUID哈

自增主鍵會產生表鎖，從而引發問題

自增主鍵可能用完問題。

21. MVCC熟悉嗎，它的底層原理？

MVCC，多版本并發控制，它是通過讀取歷史版本的數據，來降低并發事務沖突，從而提高并發性能的一種機制。

MVCC需要關注這幾個知識點：

事務版本號

表的隱藏列

undo log

read view

22. 數據庫中間件了解過嗎，sharding jdbc，mycat？

sharding-jdbc目前是基于jdbc驅動，無需額外的proxy，因此也無需關注proxy本身的高可用。

Mycat 是基于 Proxy，它復寫了 MySQL 協議，將 Mycat Server 偽裝成一個 MySQL 數據庫，而 Sharding-JDBC 是基于 JDBC 接口的擴展，是以 jar 包的形式提供輕量級服務的。

23. MYSQL的主從延遲，你怎么解決？

嘻嘻，先復習一下主從復制原理吧，如圖：

主從復制分了五個步驟進行：

步驟一：主庫的更新事件（update、insert、delete）被寫到binlog

步驟二：從庫發起連接，連接到主庫。

步驟三：此時主庫創建一個binlog dump thread，把binlog的內容發送到從庫。

步驟四：從庫啟動之后，創建一個I/O線程，讀取主庫傳過來的binlog內容并寫入到relay log

步驟五：還會創建一個SQL線程，從relay log里面讀取內容，從ExecMasterLog_Pos位置開始執行讀取到的更新事件，將更新內容寫入到slave的db

主從同步延遲的原因

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

主從同步延遲的解決辦法

主服務器要負責更新操作，對安全性的要求比從服務器要高，所以有些設置參數可以修改，比如syncbinlog=1，innodbflushlogattrxcommit = 1 之類的設置等。

選擇更好的硬件設備作為slave。

把一臺從服務器當度作為備份使用， 而不提供查詢， 那邊他的負載下來了， 執行relay log 里面的SQL效率自然就高了。

增加從服務器嘍，這個目的還是分散讀的壓力，從而降低服務器負載。

24. 說一下大表查詢的優化方案

優化shema、sql語句+索引；

可以考慮加緩存，memcached， redis，或者JVM本地緩存；

主從復制，讀寫分離；

分庫分表；

25. 什么是數據庫連接池？為什么需要數據庫連接池呢？

連接池基本原理：數據庫連接池原理：在內部對象池中，維護一定數量的數據庫連接，并對外暴露數據庫連接的獲取和返回方法。

應用程序和數據庫建立連接的過程：

通過TCP協議的三次握手和數據庫服務器建立連接

發送數據庫用戶賬號密碼，等待數據庫驗證用戶身份

完成身份驗證后，系統可以提交SQL語句到數據庫執行

把連接關閉，TCP四次揮手告別。

數據庫連接池好處：

資源重用 （連接復用）

更快的系統響應速度

新的資源分配手段

統一的連接管理，避免數據庫連接泄漏

26. 一條SQL語句在MySQL中如何執行的？

先看一下Mysql的邏輯架構圖吧~

查詢語句：

先檢查該語句是否有權限

如果沒有權限，直接返回錯誤信息

如果有權限，在 MySQL8.0 版本以前，會先查詢緩存。

如果沒有緩存，分析器進行詞法分析，提取 sql 語句select等的關鍵元素。然后判斷sql 語句是否有語法錯誤，比如關鍵詞是否正確等等。

優化器進行確定執行方案

進行權限校驗，如果沒有權限就直接返回錯誤信息，如果有權限就會調用數據庫引擎接口，返回執行結果。

27. InnoDB引擎中的索引策略，了解過嗎？

覆蓋索引

最左前綴原則

索引下推

索引下推優化是 MySQL 5.6 引入的， 可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

28. 數據庫存儲日期格式時，如何考慮時區轉換問題？

datetime類型適合用來記錄數據的原始的創建時間，修改記錄中其他字段的值，datetime字段的值不會改變，除非手動修改它。

timestamp類型適合用來記錄數據的最后修改時間，只要修改了記錄中其他字段的值，timestamp字段的值都會被自動更新。

29. 一條sql執行過長的時間，你如何優化，從哪些方面入手？

查看是否涉及多表和子查詢，優化Sql結構，如去除冗余字段，是否可拆表等

優化索引結構，看是否可以適當添加索引

數量大的表，可以考慮進行分離/分表（如交易流水表）

數據庫主從分離，讀寫分離

explain分析sql語句，查看執行計劃，優化sql

查看mysql執行日志，分析是否有其他方面的問題

30. MYSQL數據庫服務器性能分析的方法命令有哪些？

Show status， 一些值得監控的變量值：

Bytesreceived和Bytessent 和服務器之間來往的流量。

Com_*服務器正在執行的命令。

Created_*在查詢執行期限間創建的臨時表和文件。

Handler_*存儲引擎操作。

Select_*不同類型的聯接執行計劃。

Sort_*幾種排序信息。

Show profiles 是MySql用來分析當前會話SQL語句執行的資源消耗情況

31. Blob和text有什么區別？

Blob用于存儲二進制數據，而Text用于存儲大字符串。

Blob值被視為二進制字符串（字節字符串），它們沒有字符集，并且排序和比較基于列值中的字節的數值。

text值被視為非二進制字符串（字符字符串）。它們有一個字符集，并根據字符集的排序規則對值進行排序和比較。

32. mysql里記錄貨幣用什么字段類型比較好？

貨幣在數據庫中MySQL常用Decimal和Numric類型表示，這兩種類型被MySQL實現為同樣的類型。他們被用于保存與金錢有關的數據。

salary DECIMAL（9，2），9（precision）代表將被用于存儲值的總的小數位數，而2（scale）代表將被用于存儲小數點后的位數。存儲在salary列中的值的范圍是從-9999999.99到9999999.99。

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

33. Mysql中有哪幾種鎖，列舉一下？

如果按鎖粒度劃分，有以下3種：

表鎖：開銷小，加鎖快；鎖定力度大，發生鎖沖突概率高，并發度最低;不會出現死鎖。

行鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖沖突的概率低，并發度高。

頁鎖：開銷和加鎖速度介于表鎖和行鎖之間；會出現死鎖；鎖定粒度介于表鎖和行鎖之間，并發度一般

34. Hash索引和B+樹區別是什么？你在設計索引是怎么抉擇的？

B+樹可以進行范圍查詢，Hash索引不能。

B+樹支持聯合索引的最左側原則，Hash索引不支持。

B+樹支持order by排序，Hash索引不支持。

Hash索引在等值查詢上比B+樹效率更高。

B+樹使用like 進行模糊查詢的時候，like后面（比如%開頭）的話可以起到優化的作用，Hash索引根本無法進行模糊查詢。

35. mysql 的內連接、左連接、右連接有什么區別？

Inner join 內連接，在兩張表進行連接查詢時，只保留兩張表中完全匹配的結果集

left join 在兩張表進行連接查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄。

right join 在兩張表進行連接查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄。

36. 說說MySQL 的基礎架構圖

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

第一層負責連接處理，授權認證，安全等等

第二層負責編譯并優化SQL

第三層是存儲引擎。

37. 什么是內連接、外連接、交叉連接、笛卡爾積呢？

內連接（inner join）：取得兩張表中滿足存在連接匹配關系的記錄。

外連接（outer join）：取得兩張表中滿足存在連接匹配關系的記錄，以及某張表（或兩張表）中不滿足匹配關系的記錄。

交叉連接（cross join）：顯示兩張表所有記錄一一對應，沒有匹配關系進行篩選，也被稱為：笛卡爾積。

38. 說一下數據庫的三大范式

第一范式：數據表中的每一列（每個字段）都不可以再拆分。

第二范式：在第一范式的基礎上，分主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。

第三范式：在滿足第二范式的基礎上，表中的非主鍵只依賴于主鍵，而不依賴于其他非主鍵。

39. mysql有關權限的表有哪幾個呢？

MySQL服務器通過權限表來控制用戶對數據庫的訪問，權限表存放在mysql數據庫里，由mysqlinstalldb腳本初始化。這些權限表分別user，db，tablepriv，columnspriv和host。

user權限表：記錄允許連接到服務器的用戶帳號信息，里面的權限是全局級的。

db權限表：記錄各個帳號在各個數據庫上的操作權限。

table_priv權限表：記錄數據表級的操作權限。

columns_priv權限表：記錄數據列級的操作權限。

host權限表：配合db權限表對給定主機上數據庫級操作權限作更細致的控制。這個權限表不受GRANT和REVOKE語句的影響。

40. Mysql的binlog有幾種錄入格式？分別有什么區別？

有三種格式哈，statement，row和mixed。

statement，每一條會修改數據的sql都會記錄在binlog中。不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能。由于sql的執行是有上下文的，因此在保存的時候需要保存相關的信息，同時還有一些使用了函數之類的語句無法被記錄復制。

row，不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。記錄單元為每一行的改動，基本是可以全部記下來但是由于很多操作，會導致大量行的改動（比如alter table），因此這種模式的文件保存的信息太多，日志量太大。

mixed，一種折中的方案，普通操作使用statement記錄，當無法使用statement的時候使用row。

41. InnoDB引擎的4大特性，了解過嗎

插入緩沖（insert buffer）

二次寫（double write）

自適應哈希索引（ahi）

預讀（read ahead）

42. 索引有哪些優缺點？

優點：

唯一索引可以保證數據庫表中每一行的數據的唯一性

索引可以加快數據查詢速度，減少查詢時間

缺點：

創建索引和維護索引要耗費時間

索引需要占物理空間，除了數據表占用數據空間之外，每一個索引還要占用一定的物理空間

以表中的數據進行增、刪、改的時候，索引也要動態的維護。

43. 索引有哪幾種類型？

主鍵索引： 數據列不允許重復，不允許為NULL，一個表只能有一個主鍵。

唯一索引： 數據列不允許重復，允許為NULL值，一個表允許多個列創建唯一索引。

普通索引： 基本的索引類型，沒有唯一性的限制，允許為NULL值。

全文索引：是目前搜索引擎使用的一種關鍵技術，對文本的內容進行分詞、搜索。

覆蓋索引：查詢列要被所建的索引覆蓋，不必讀取數據行

組合索引：多列值組成一個索引，用于組合搜索，效率大于索引合并

44. 創建索引有什么原則呢？

最左前綴匹配原則

頻繁作為查詢條件的字段才去創建索引

頻繁更新的字段不適合創建索引

索引列不能參與計算，不能有函數操作

優先考慮擴展索引，而不是新建索引，避免不必要的索引

在order by或者group by子句中，創建索引需要注意順序

區分度低的數據列不適合做索引列（如性別）

定義有外鍵的數據列一定要建立索引。

對于定義為text、image數據類型的列不要建立索引。

刪除不再使用或者很少使用的索引

45. 創建索引的三種方式

在執行CREATE TABLE時創建索引

CREATE TABLE `employee` （

`id` int（11） NOT NULL，

`name` varchar（255） DEFAULT NULL，

`age` int（11） DEFAULT NULL，

`date` datetime DEFAULT NULL，

`sex` int（1） DEFAULT NULL，

PRIMARY KEY （`id`），

KEY `idx_name` （`name`） USING BTREE

） ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用ALTER TABLE命令添加索引

ALTER TABLE table_name ADD INDEX index_name （column）;

使用CREATE INDEX命令創建

CREATE INDEX index_name ON table_name （column）;

46. 百萬級別或以上的數據，你是如何刪除的？

我們想要刪除百萬數據的時候可以先刪除索引

然后批量刪除其中無用數據

刪除完成后重新創建索引。

47. 什么是最左前綴原則？什么是最左匹配原則？

最左前綴原則，就是最左優先，在創建多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。

當我們創建一個組合索引的時候，如（k1，k2，k3），相當于創建了（k1）、（k1，k2）和（k1，k2，k3）三個索引，這就是最左匹配原則。。

48. B樹和B+樹的區別，數據庫為什么使用B+樹而不是B樹？

在B樹中，鍵和值即存放在內部節點又存放在葉子節點；在B+樹中，內部節點只存鍵，葉子節點則同時存放鍵和值。

B+樹的葉子節點有一條鏈相連，而B樹的葉子節點各自獨立的。

B+樹索引的所有數據均存儲在葉子節點，而且數據是按照順序排列的，鏈表連著的。那么B+樹使得范圍查找，排序查找，分組查找以及去重查找變得異常簡單。。

B+樹非葉子節點上是不存儲數據的，僅存儲鍵值，而B樹節點中不僅存儲鍵值，也會存儲數據。innodb中頁的默認大小是16KB，如果不存儲數據，那么就會存儲更多的鍵值，相應的樹的階數（節點的子節點樹）就會更大，樹就會更矮更胖，如此一來我們查找數據進行磁盤的IO次數有會再次減少，數據查詢的效率也會更快。

49. 覆蓋索引、回表等這些，了解過嗎？

覆蓋索引：查詢列要被所建的索引覆蓋，不必從數據表中讀取，換句話說查詢列要被所使用的索引覆蓋。

回表：二級索引無法直接查詢所有列的數據，所以通過二級索引查詢到聚簇索引后，再查詢到想要的數據，這種通過二級索引查詢出來的過程，就叫做回表。

50. B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數據？

在B+樹的索引中，葉子節點可能存儲了當前的key值，也可能存儲了當前的key值以及整行的數據，這就是聚簇索引和非聚簇索引。在InnoDB中，只有主鍵索引是聚簇索引，如果沒有主鍵，則挑選一個唯一鍵建立聚簇索引。如果沒有唯一鍵，則隱式的生成一個鍵來建立聚簇索引。

當查詢使用聚簇索引時，在對應的葉子節點，可以獲取到整行數據，因此不用再次進行回表查詢。

51. 何時使用聚簇索引與非聚簇索引

52. 非聚簇索引一定會回表查詢嗎？

不一定，如果查詢語句的字段全部命中了索引，那么就不必再進行回表查詢（哈哈，覆蓋索引就是這么回事）。

舉個簡單的例子，假設我們在學生表的上建立了索引，那么當進行select age from student where age 《 20的查詢時，在索引的葉子節點上，已經包含了age信息，不會再次進行回表查詢。

53. 組合索引是什么？為什么需要注意組合索引中的順序？

組合索引，用戶可以在多個列上建立索引，這種索引叫做組合索引。

因為InnoDB引擎中的索引策略的最左原則，所以需要注意組合索引中的順序。

54. 什么是數據庫事務？

數據庫事務（簡稱：事務），是數據庫管理系統執行過程中的一個邏輯單位，由一個有限的數據庫操作序列構成，這些操作要么全部執行，要么全部不執行，是一個不可分割的工作單位。

55. 隔離級別與鎖的關系

回答這個問題，可以先闡述四種隔離級別，再闡述它們的實現原理。隔離級別就是依賴鎖和MVCC實現的。

56. 按照鎖的粒度分，數據庫鎖有哪些呢？鎖機制與InnoDB鎖算法

按鎖粒度分有：表鎖，頁鎖，行鎖

按鎖機制分有：樂觀鎖，悲觀鎖

57. 從鎖的類別角度講，MySQL都有哪些鎖呢？

從鎖的類別上來講，有共享鎖和排他鎖。

共享鎖： 又叫做讀鎖。當用戶要進行數據的讀取時，對數據加上共享鎖。共享鎖可以同時加上多個。

排他鎖： 又叫做寫鎖。當用戶要進行數據的寫入時，對數據加上排他鎖。排他鎖只可以加一個，他和其他的排他鎖，共享鎖都相斥。

鎖兼容性如下：

58. MySQL中InnoDB引擎的行鎖是怎么實現的？

基于索引來完成行鎖的。

select * from t where id = 666 for update;

for update 可以根據條件來完成行鎖鎖定，并且 id 是有索引鍵的列，如果 id 不是索引鍵那么InnoDB將實行表鎖。

59. 什么是死鎖？怎么解決？

死鎖是指兩個或多個事務在同一資源上相互占用，并請求鎖定對方的資源，從而導致惡性循環的現象。看圖形象一點，如下：

死鎖有四個必要條件：互斥條件，請求和保持條件，環路等待條件，不剝奪條件。

解決死鎖思路，一般就是切斷環路，盡量避免并發形成環路。

如果不同程序會并發存取多個表，盡量約定以相同的順序訪問表，可以大大降低死鎖機會。

在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；

對于非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；

如果業務處理不好可以用分布式事務鎖或者使用樂觀鎖

死鎖與索引密不可分，解決索引問題，需要合理優化你的索引，

60. 為什么要使用視圖？什么是視圖？

為什么要使用視圖？

為了提高復雜SQL語句的復用性和表操作的安全性，MySQL數據庫管理系統提供了視圖特性。

什么是視圖？

視圖是一個虛擬的表，是一個表中的數據經過某種篩選后的顯示方式，視圖由一個預定義的查詢select語句組成。

61. 視圖有哪些特點？哪些使用場景？

視圖特點：

視圖的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關系。

視圖是由基本表（實表）產生的表（虛表）。

視圖的建立和刪除不影響基本表。

對視圖內容的更新（添加，刪除和修改）直接影響基本表。

當視圖來自多個基本表時，不允許添加和刪除數據。

視圖用途：簡化sql查詢，提高開發效率，兼容老的表結構。

視圖的常見使用場景：

重用SQL語句；

簡化復雜的SQL操作。

使用表的組成部分而不是整個表；

保護數據

更改數據格式和表示。視圖可返回與底層表的表示和格式不同的數據。

62. 視圖的優點，缺點，講一下？

查詢簡單化。視圖能簡化用戶的操作

數據安全性。視圖使用戶能以多種角度看待同一數據，能夠對機密數據提供安全保護

邏輯數據獨立性。視圖對重構數據庫提供了一定程度的邏輯獨立性

63. count（1）、count（*） 與 count（列名） 的區別？

count（*）包括了所有的列，相當于行數，在統計結果的時候，不會忽略列值為NULL

count（1）包括了忽略所有列，用1代表代碼行，在統計結果的時候，不會忽略列值為NULL

count（列名）只包括列名那一列，在統計結果的時候，會忽略列值為空（這里的空不是只空字符串或者0，而是表示null）的計數，即某個字段值為NULL時，不統計。

64. 什么是游標？

游標提供了一種對從表中檢索出的數據進行操作的靈活手段，就本質而言，游標實際上是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。

65. 什么是存儲過程？有哪些優缺點？

存儲過程，就是一些編譯好了的SQL語句，這些SQL語句代碼像一個方法一樣實現一些功能（對單表或多表的增刪改查），然后給這些代碼塊取一個名字，在用到這個功能的時候調用即可。

優點：

存儲過程是一個預編譯的代碼塊，執行效率比較高

存儲過程在服務器端運行，減少客戶端的壓力

允許模塊化程序設計，只需要創建一次過程，以后在程序中就可以調用該過程任意次，類似方法的復用

一個存儲過程替代大量T_SQL語句 ，可以降低網絡通信量，提高通信速率

可以一定程度上確保數據安全

缺點：

調試麻煩

可移植性不靈活

重新編譯問題

66. 什么是觸發器？觸發器的使用場景有哪些？

觸發器，指一段代碼，當觸發某個事件時，自動執行這些代碼。

使用場景：

可以通過數據庫中的相關表實現級聯更改。

實時監控某張表中的某個字段的更改而需要做出相應的處理。

例如可以生成某些業務的編號。

注意不要濫用，否則會造成數據庫及應用程序的維護困難。

67. MySQL中都有哪些觸發器？

MySQL 數據庫中有六種觸發器：

Before Insert

After Insert

Before Update

After Update

Before Delete

After Delete

68. 超鍵、候選鍵、主鍵、外鍵分別是什么？

超鍵：在關系模式中，能唯一知標識元組的屬性集稱為超鍵。

候選鍵：是最小超鍵，即沒有冗余元素的超鍵。

主鍵：數據庫表中對儲存數據對象予以唯一和完整標識的數據列或屬性的組合。一個數據列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。

外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。。

69. SQL 約束有哪幾種呢？

NOT NULL： 約束字段的內容一定不能為NULL。

UNIQUE： 約束字段唯一性，一個表允許有多個 Unique 約束。

PRIMARY KEY： 約束字段唯一，不可重復，一個表只允許存在一個。

FOREIGN KEY： 用于預防破壞表之間連接的動作，也能防止非法數據插入外鍵。

CHECK： 用于控制字段的值范圍。

70. 談談六種關聯查詢，使用場景。

交叉連接

內連接

外連接

聯合查詢

全連接

交叉連接

71. varchar（50）中50的涵義

字段最多存放 50 個字符

如 varchar（50） 和 varchar（200） 存儲 “jay” 字符串所占空間是一樣的，后者在排序時會消耗更多內存

72. mysql中int（20）和char（20）以及varchar（20）的區別

int（20） 表示字段是int類型，顯示長度是 20

char（20）表示字段是固定長度字符串，長度為 20

varchar（20） 表示字段是可變長度字符串，長度為 20

73. drop、delete與truncate的區別

類型DMLDDLDDL

回滾可回滾不可回滾不可回滾

刪除內容表結構還在，刪除表的全部或者一部分數據行表結構還在，刪除表中的所有數據從數據庫中刪除表，所有的數據行，索引和權限也會被刪除

刪除速度刪除速度慢，逐行刪除刪除速度快刪除速度最快

deletetruncatedrop

74. UNION與UNION ALL的區別？

Union：對兩個結果集進行并集操作，不包括重復行，同時進行默認規則的排序；

Union All：對兩個結果集進行并集操作，包括重復行，不進行排序；

UNION的效率低于 UNION ALL

75. SQL的生命周期？

服務器與數據庫建立連接

數據庫進程拿到請求sql

解析并生成執行計劃，執行

讀取數據到內存，并進行邏輯處理

通過步驟一的連接，發送結果到客戶端

關掉連接，釋放資源

76. 一條Sql的執行順序？

77. 列值為NULL時，查詢是否會用到索引？

列值為NULL也是可以走索引的

計劃對列進行索引，應盡量避免把它設置為可空，因為這會讓 MySQL 難以優化引用了可空列的查詢，同時增加了引擎的復雜度

78. 關心過業務系統里面的sql耗時嗎？統計過慢查詢嗎？對慢查詢都怎么優化過？

我們平時寫Sql時，都要養成用explain分析的習慣。

慢查詢的統計，運維會定期統計給我們

優化慢查詢：

分析語句，是否加載了不必要的字段/數據。

分析SQl執行句話，是否命中索引等。

如果SQL很復雜，優化SQL結構

如果表數據量太大，考慮分表

79. 主鍵使用自增ID還是UUID，為什么？

如果是單機的話，選擇自增ID；如果是分布式系統，優先考慮UUID吧，但還是最好自己公司有一套分布式唯一ID生產方案吧。

自增ID：數據存儲空間小，查詢效率高。但是如果數據量過大，會超出自增長的值范圍，多庫合并，也有可能有問題。

uuid：適合大量數據的插入和更新操作，但是它無序的，插入數據效率慢，占用空間大。

80. mysql自增主鍵用完了怎么辦？

自增主鍵一般用int類型，一般達不到最大值，可以考慮提前分庫分表的。

81. 字段為什么要求定義為not null？

null值會占用更多的字節，并且null有很多坑的。

82. 如果要存儲用戶的密碼散列，應該使用什么字段進行存儲？

密碼散列，鹽，用戶身份證號等固定長度的字符串，應該使用char而不是varchar來存儲，這樣可以節省空間且提高檢索效率。

83. Mysql驅動程序是什么？

這個jar包：mysql-connector-java-5.1.18.jar

Mysql驅動程序主要幫助編程語言與 MySQL服務端進行通信，如連接、傳輸數據、關閉等。

84. 如何優化長難的查詢語句？有實戰過嗎？

將一個大的查詢分為多個小的相同的查詢

減少冗余記錄的查詢。

一個復雜查詢可以考慮拆成多個簡單查詢

分解關聯查詢，讓緩存的效率更高。

85. 優化特定類型的查詢語句

平時積累吧：

比如使用select 具體字段代替 select *

使用count（*） 而不是count（列名）

在不影響業務的情況，使用緩存

explain 分析你的SQL

86. MySQL數據庫cpu飆升的話，要怎么處理呢？

排查過程：

使用top 命令觀察，確定是mysqld導致還是其他原因。

如果是mysqld導致的，show processlist，查看session情況，確定是不是有消耗資源的sql在運行。

找出消耗高的 sql，看看執行計劃是否準確， 索引是否缺失，數據量是否太大。

處理：

kill 掉這些線程（同時觀察 cpu 使用率是否下降），

進行相應的調整（比如說加索引、改 sql、改內存參數）

重新跑這些 SQL。

其他情況：

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

87. 讀寫分離常見方案？

應用程序根據業務邏輯來判斷，增刪改等寫操作命令發給主庫，查詢命令發給備庫。

利用中間件來做代理，負責對數據庫的請求識別出讀還是寫，并分發到不同的數據庫中。（如：amoeba，mysql-proxy）

88. MySQL的復制原理以及流程

主從復制原理，簡言之，就三步曲，如下：

主數據庫有個bin-log二進制文件，紀錄了所有增刪改Sql語句。（binlog線程）

從數據庫把主數據庫的bin-log文件的sql語句復制過來。（io線程）

從數據庫的relay-log重做日志文件中再執行一次這些sql語句。（Sql執行線程）

上圖主從復制分了五個步驟進行：

步驟一：主庫的更新事件（update、insert、delete）被寫到binlog

步驟二：從庫發起連接，連接到主庫。

步驟三：此時主庫創建一個binlog dump thread，把binlog的內容發送到從庫。

步驟四：從庫啟動之后，創建一個I/O線程，讀取主庫傳過來的binlog內容并寫入到relay log

步驟五：還會創建一個SQL線程，從relay log里面讀取內容，從ExecMasterLog_Pos位置開始執行讀取到的更新事件，將更新內容寫入到slave的db

89. MySQL中DATETIME和TIMESTAMP的區別

存儲精度都為秒

區別：

DATETIME 的日期范圍是 1001——9999 年；TIMESTAMP 的時間范圍是 1970——2038 年

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

DATETIME 的存儲空間為 8 字節；TIMESTAMP 的存儲空間為 4 字節

DATETIME 的默認值為 null；TIMESTAMP 的字段默認不為空（not null），默認值為當前時間（CURRENT_TIMESTAMP）

90. Innodb的事務實現原理？

原子性：是使用 undo log來實現的，如果事務執行過程中出錯或者用戶執行了rollback，系統通過undo log日志返回事務開始的狀態。

持久性：使用 redo log來實現，只要redo log日志持久化了，當系統崩潰，即可通過redo log把數據恢復。

隔離性：通過鎖以及MVCC，使事務相互隔離開。

一致性：通過回滾、恢復，以及并發情況下的隔離性，從而實現一致性。

91. 談談MySQL的Explain

Explain 執行計劃包含字段信息如下：分別是 id、selecttype、table、partitions、type、possiblekeys、key、key_len、ref、rows、filtered、Extra 等12個字段。

我們重點關注的是type，它的屬性排序如下：

system 》 const 》 eq_ref 》 ref 》 ref_or_null 》

index_merge 》 unique_subquery 》 index_subquery 》

range 》 index 》 ALL

92. Innodb的事務與日志的實現方式

有多少種日志

innodb兩種日志redo和undo。

日志的存放形式

redo：在頁修改的時候，先寫到 redo log buffer 里面， 然后寫到 redo log 的文件系統緩存里面（fwrite），然后再同步到磁盤文件（ fsync）。

Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通過設置 innodbundotablespaces 參數把 undo log 存放在 ibdata之外。

事務是如何通過日志來實現的

因為事務在修改頁時，要先記 undo，在記 undo 之前要記 undo 的 redo， 然后修改數據頁，再記數據頁修改的 redo。Redo（里面包括 undo 的修改） 一定要比數據頁先持久化到磁盤。

當事務需要回滾時，因為有 undo，可以把數據頁回滾到前鏡像的 狀態，崩潰恢復時，如果 redo log 中事務沒有對應的 commit 記錄，那么需要用 undo把該事務的修改回滾到事務開始之前。

如果有 commit 記錄，就用 redo 前滾到該事務完成時并提交掉。

93. MySQL中TEXT數據類型的最大長度

TINYTEXT：256 bytes

TEXT：65，535 bytes（64kb）

MEDIUMTEXT：16，777，215 bytes（16MB）

LONGTEXT：4，294，967，295 bytes（4GB）

94. 500臺db，在最快時間之內重啟。

可以使用批量 ssh 工具 pssh 來對需要重啟的機器執行重啟命令。

也可以使用 salt（前提是客戶端有安裝 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多線程工具同時操作多臺服務

95. 你是如何監控你們的數據庫的？你們的慢日志都是怎么查詢的？

監控的工具有很多，例如zabbix，lepus，我這里用的是lepus

96. 你是否做過主從一致性校驗，如果有，怎么做的，如果沒有，你打算怎么做？

主從一致性校驗有多種工具 例如checksum、mysqldiff、pt-table-checksum等

97. 你們數據庫是否支持emoji表情存儲，如果不支持，如何操作？

更換字符集utf8--》utf8mb4

98. MySQL如何獲取當前日期？

SELECT CURRENT_DATE（）;

99. 一個6億的表a，一個3億的表b，通過外間tid關聯，你如何最快的查詢出滿足條件的第50000到第50200中的這200條數據記錄。

1、如果A表TID是自增長，并且是連續的，B表的ID為索引select * from a，b where a.tid = b.id and a.tid》500000 limit 200;

2、如果A表的TID不是連續的，那么就需要使用覆蓋索引.TID要么是主鍵，要么是輔助索引，B表ID也需要有索引。select * from b ， （select tid from a limit 50000，200） a where b.id = a .tid;

100. Mysql一條SQL加鎖分析

一條SQL加鎖，可以分9種情況進行哈：

組合一：id列是主鍵，RC隔離級別

組合二：id列是二級唯一索引，RC隔離級別

組合三：id列是二級非唯一索引，RC隔離級別

組合四：id列上沒有索引，RC隔離級別

組合五：id列是主鍵，RR隔離級別

組合六：id列是二級唯一索引，RR隔離級別

組合七：id列是二級非唯一索引，RR隔離級別

組合八：id列上沒有索引，RR隔離級別

組合九：Serializable隔離級別

原文標題：100 道 MySQL 數據庫經典面試題解析

文章出處：【微信公眾號：Linux愛好者】歡迎添加關注！文章轉載請注明出處。

責任編輯：haq