前言

100道MySQL數(shù)據(jù)庫經(jīng)典面試題解析，已經(jīng)上傳github啦

github.com/whx123/Java…

數(shù)據(jù)庫

1. MySQL 索引使用有哪些注意事項(xiàng)呢？

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

索引哪些情況會失效

• 查詢條件包含or，可能導(dǎo)致索引失效
• 如何字段類型是字符串，where時(shí)一定用引號括起來，否則索引失效
• like通配符可能導(dǎo)致索引失效。
• 聯(lián)合索引，查詢時(shí)的條件列不是聯(lián)合索引中的第一個列，索引失效。
• 在索引列上使用mysql的內(nèi)置函數(shù)，索引失效。
• 對索引列運(yùn)算（如，+、-、*、/），索引失效。
• 索引字段上使用（！= 或者 < >，not in）時(shí)，可能會導(dǎo)致索引失效。
• 索引字段上使用is null， is not null，可能導(dǎo)致索引失效。
• 左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣，可能導(dǎo)致索引失效。
• mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引。

索引不適合哪些場景

• 數(shù)據(jù)量少的不適合加索引
• 更新比較頻繁的也不適合加索引
• 區(qū)分度低的字段不適合加索引（如性別）

索引的一些潛規(guī)則

• 覆蓋索引
• 回表
• 索引數(shù)據(jù)結(jié)構(gòu)（B+樹）
• 最左前綴原則
• 索引下推

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

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

• 查看死鎖日志show engine innodb status;
• 找出死鎖Sql
• 分析sql加鎖情況
• 模擬死鎖案發(fā)
• 分析死鎖日志
• 分析死鎖結(jié)果

3. 日常工作中你是怎么優(yōu)化SQL的？

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

• 加索引
• 避免返回不必要的數(shù)據(jù)
• 適當(dāng)分批量進(jìn)行
• 優(yōu)化sql結(jié)構(gòu)
• 分庫分表
• <typo id="typo-762" data-origin="讀" ignoretag="true">讀</typo>寫分離

4. 說說分庫與分表的設(shè)計(jì)

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

分庫分表方案:

• 水平分庫：以字段為依據(jù)，按照一定策略（hash、range等），將一個庫中的數(shù)據(jù)拆分到多個庫中。
• 水平分表：以字段為依據(jù)，按照一定策略（hash、range等），將一個表中的數(shù)據(jù)拆分到多個表中。
• 垂直分庫：以表為依據(jù)，按照業(yè)務(wù)歸屬不同，將不同的表拆分到不同的庫中。
• 垂直分表：以字段為依據(jù)，按照字段的活躍性，將表中字段拆到不同的表（主表和擴(kuò)展表）中。

常用的分庫分表中間件：

• sharding-jdbc（當(dāng)當(dāng)）
• Mycat
• TDDL（淘寶）
• Oceanus(58同城數(shù)據(jù)庫中間件)
• vitess（谷歌開發(fā)的數(shù)據(jù)庫中間件）
• Atlas(Qihoo 360)

分庫分表可能遇到的問題

• 事務(wù)問題：需要用分布式事務(wù)啦
• 跨節(jié)點(diǎn)Join的問題：解決這一問題可以分兩次查詢實(shí)現(xiàn)
• 跨節(jié)點(diǎn)的count,order by,group by以及聚合函數(shù)問題：分別在各個節(jié)點(diǎn)上<typo id="typo-1216" data-origin="得" ignoretag="true">得</typo>到結(jié)果后在應(yīng)用程序端進(jìn)行合并。
• 數(shù)據(jù)遷移，容量規(guī)劃，擴(kuò)容等問題
• ID問題：數(shù)據(jù)庫被切分后，不能再依賴數(shù)據(jù)庫自身的主鍵生成機(jī)制啦，最簡單可以考慮UUID
• 跨分片的排序分頁問題（后臺加大pagesize處理？）

5. InnoDB與MyISAM的區(qū)別

• InnoDB支持事務(wù)，MyISAM不支持事務(wù)
• InnoDB支持外鍵，MyISAM不支持外鍵
• InnoDB 支持 MVCC(多版本并發(fā)控制)，MyISAM 不支持
• select count(*) from table時(shí)，MyISAM更快，因?yàn)樗幸粋€變量保存了整個表的總行數(shù)，可以直接讀取，InnoDB就需要全表掃描。
• Innodb不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）
• InnoDB支持表、行級鎖，而MyISAM支持表級鎖。
• InnoDB表必須有主鍵，而MyISAM可以沒有主鍵
• Innodb表需要更多的內(nèi)存和存儲，而MyISAM可被壓縮，存儲空間較小，。
• Innodb按主鍵大小有序插入，MyISAM記錄插入順序是，按記錄插入順序保存。
• InnoDB 存儲引擎提供了具有提交、回滾、崩潰恢復(fù)能力的事務(wù)安全，與 MyISAM 比 InnoDB 寫的效率差一些，并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引

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

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

為什么不是一般二叉樹？

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

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

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

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

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

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

7. 聚集索引與非聚集索引的區(qū)別

• 一個表中只能擁有一個聚集索引，而非聚集索引一個表可以存在多個。
• 聚集索引，索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序；非聚集索引，索引中索引的邏輯順序與磁盤上行的物理存儲順序不同。
• 索引是通過二叉樹的數(shù)據(jù)結(jié)構(gòu)來描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。而非聚簇索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過有一個指針指向?qū)?yīng)的數(shù)據(jù)塊。
• 聚集索引：物理存儲按照索引排序；非聚集索引：物理存儲不按照索引排序；

何時(shí)使用聚集索引或非聚集索引？

image

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

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

<pre language="javascript" code_block="true">select id，name from employee where id>1000000 limit 10.</pre>

方案二：在業(yè)務(wù)允許的情況下限制頁數(shù)：

建議跟業(yè)務(wù)討論，有沒有必要查這么后的分頁啦。因?yàn)榻^大多數(shù)用戶都不會往后翻太多頁。

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

<pre language="javascript" code_block="true">select id，name from employee order by id limit 1000000，10</pre>

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

<pre language="javascript" code_block="true">SELECT a.* FROM employee a, (select id from employee where 條件 LIMIT 1000000,10 ) b where a.id=b.id</pre>

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

• 數(shù)據(jù)庫自增長序列或字段。
• UUID。
• Redis生成ID
• Twitter的snowflake算法
• 利用zookeeper生成唯一ID
• MongoDB的ObjectId

10. 事務(wù)的隔離級別有哪些？MySQL的默認(rèn)隔離級別是什么？

• 讀未提交（Read Uncommitted）
• <typo id="typo-3236" data-origin="讀" ignoretag="true">讀</typo>已提交（Read Committed）
• 可重復(fù)讀（Repeatable Read）
• 串行化（Serializable）

Mysql默認(rèn)的事務(wù)隔離級別是可重復(fù)讀(Repeatable Read)

11. 什么是幻讀，臟讀，不可重復(fù)讀呢？

• 事務(wù)A、B交替執(zhí)行，事務(wù)A被事務(wù)B干擾到了，因?yàn)槭聞?wù)A讀取到事務(wù)B未提交的數(shù)據(jù),這就是臟讀
• 在一個事務(wù)范圍內(nèi)，兩個相同的查詢，讀取同一條記錄，卻返回了不同的數(shù)據(jù)，這就是不可重復(fù)讀。
• 事務(wù)A查詢一個范圍的結(jié)果集，另一個并發(fā)事務(wù)B往這個范圍中插入/刪除了數(shù)據(jù)，并靜悄悄地提交，然后事務(wù)A再次查詢相同的范圍，兩次讀取得到的結(jié)果集不一樣了，這就是幻讀。

12. 在高并發(fā)情況下，如何做到安全的修改同一行數(shù)據(jù)？

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

使用悲觀鎖

悲觀鎖思想就是，當(dāng)前線程要進(jìn)來修改數(shù)據(jù)時(shí)，別的線程都得拒之門外~ 比如，可以使用select…for update ~

<pre language="javascript" code_block="true">select * from User where name=‘jay’ for update</pre>

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

使用樂觀鎖

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

13. 數(shù)據(jù)庫的樂觀鎖和悲觀鎖。

悲觀鎖：

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

image

樂觀<typo id="typo-4031" data-origin="鎖" ignoretag="true">鎖</typo>：

樂觀鎖的“樂觀情緒”體現(xiàn)在，它認(rèn)為數(shù)據(jù)的變動不會太頻繁。因此，它允許多個事務(wù)同時(shí)對數(shù)據(jù)進(jìn)行變動。實(shí)現(xiàn)方式：樂觀鎖一般會使用版本號機(jī)制或CAS算法實(shí)現(xiàn)。

image

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

• show status 命令了解各種 sql 的執(zhí)行頻率
• 通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 sql 語句
• explain 分析低效 sql 的執(zhí)行計(jì)劃（這點(diǎn)非常重要，日常開發(fā)中用它分析Sql，會大大降低Sql導(dǎo)致的線上事故）

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

select for update 含義

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

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

select for update 加鎖驗(yàn)證

表結(jié)構(gòu)：

<pre language="javascript" code_block="true">//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</pre>

id為主鍵，select for update 1270070這條記錄時(shí)，再開一個事務(wù)對該記錄更新，發(fā)現(xiàn)更新阻塞啦，其實(shí)是加鎖了。如下圖：

image

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

image

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

image

16. MySQL事務(wù)得四大特性以及實(shí)現(xiàn)原理

image

• 原子性： 事務(wù)作為一個整體被執(zhí)行，包含在其中的對數(shù)據(jù)庫的操作要么全部被執(zhí)行，要么都不執(zhí)行。
• 一致性： 指在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)不會被破壞，假如A賬戶給B賬戶轉(zhuǎn)10塊錢，不管成功與否，A和B的總金額是不變的。
• 隔離性： 多個事務(wù)并發(fā)訪問時(shí)，事務(wù)之間是相互隔離的，即一個事務(wù)不影響其它事務(wù)運(yùn)行效果。簡言之，就是事務(wù)之間是進(jìn)水不犯河水的。
• 持久性： 表示事務(wù)完成以后，該事務(wù)對數(shù)據(jù)庫所作的操作更改，將持久地保存在數(shù)據(jù)庫之中。

事務(wù)ACID特性的實(shí)現(xiàn)思想

• 原子性：是使用 undo log來實(shí)現(xiàn)的，如果事務(wù)執(zhí)行過程中出錯或者用戶執(zhí)行了rollback，系統(tǒng)通過undo log日志返回事務(wù)開始的狀態(tài)。
• 持久性：使用 redo log來實(shí)現(xiàn)，只要redo log日志持久化了，當(dāng)系統(tǒng)崩潰，即可通過redo log把數(shù)據(jù)恢復(fù)。
• 隔離性：通過鎖以及MVCC,使事務(wù)相互隔離開。
• 一致性：通過回滾、恢復(fù)，以及并發(fā)情況下的隔離性，從而實(shí)現(xiàn)一致性。

17. 如果某個表有近千萬數(shù)據(jù)，CRUD比較慢，如何優(yōu)化。

分庫分表

某個表有近千萬數(shù)據(jù)，可以考慮優(yōu)化表結(jié)構(gòu)，分表（水平分表，垂直分表），當(dāng)然，你這樣回答，需要準(zhǔn)備好面試官問你的分庫分表相關(guān)問題呀，如

• 分表方案（水平分表，垂直分表，切分規(guī)則hash等）
• 分庫分表中間件（Mycat，sharding-jdbc等）
• 分庫分表一些問題（事務(wù)問題？跨節(jié)點(diǎn)Join的問題）
• 解決方案（分布式事務(wù)等）

索引優(yōu)化

除了分庫分表，優(yōu)化表結(jié)構(gòu)，當(dāng)然還有所以索引優(yōu)化等方案~

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

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

當(dāng)我們創(chuàng)建一個組合索引的時(shí)候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。

<pre language="javascript" code_block="true">select * from table where k1=A AND k2=B AND k3=D </pre>

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

19. mysql中in 和exists的區(qū)別。

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

假設(shè)表A表示某企業(yè)的員工表，表B表示部門表，查詢所有部門的所有員工，很容易有以下SQL:

<pre language="javascript" code_block="true">select * from A where deptId in (select deptId from B);</pre>

這樣寫等價(jià)于：

先查詢部門表B

select deptId from B

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

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

可以抽象成這樣的一個循環(huán)：

<pre language="javascript" code_block="true"> 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;
}
}
}</pre>

顯然，除了使用in，我們也可以用exists實(shí)現(xiàn)一樣的查詢功能，如下：

<pre language="javascript" code_block="true">select * from A where exists (select 1 from B where A.deptId = B.deptId); </pre>

因?yàn)閑xists查詢的理解就是，先執(zhí)行主查詢，獲得數(shù)據(jù)后，再放到子查詢中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果（true或者false），來決定主查詢的數(shù)據(jù)結(jié)果是否得意保留。

那么，這樣寫就等價(jià)于：

select * from A,先從A表做循環(huán)

select * from B where A.deptId = B.deptId,再從B表做循環(huán).

同理，可以抽象成這樣一個循環(huán)：

<pre language="javascript" code_block="true"> 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;
}
}
}</pre>

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

因此，我們要選擇最外層循環(huán)小的，也就是，如果B的數(shù)據(jù)量小于A，適合使用in，如果B的數(shù)據(jù)量大于A，即適合選擇exists，這就是in和exists的區(qū)別。

20. 數(shù)據(jù)庫自增主鍵可能遇到什么問題。

• 使用自增主鍵對數(shù)據(jù)庫做分庫分表，可能出現(xiàn)諸如主鍵重復(fù)等的問題。解決方案的話，簡單點(diǎn)的話可以考慮使用UUID哈
• 自增主鍵會產(chǎn)生表鎖，從而引發(fā)問題
• 自增主鍵可能用完問題。

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

MVCC,多版本并發(fā)控制,它是通過讀取歷史版本的數(shù)據(jù)，來降低并發(fā)事務(wù)沖突，從而提高并發(fā)性能的一種機(jī)制。

MVCC需要關(guān)注這幾個知識點(diǎn)：

• 事務(wù)版本號
• 表的隱藏列
• undo log
• read view

22. 數(shù)據(jù)庫中間件了解過嗎，sharding jdbc，mycat？

• sharding-jdbc目前是基于jdbc驅(qū)動，無需額外的proxy，因此也無需關(guān)注proxy本身的高可用。
• Mycat 是基于 Proxy，它復(fù)寫了 MySQL 協(xié)議，將 Mycat Server 偽裝成一個 MySQL 數(shù)據(jù)庫，而 Sharding-JDBC 是基于 JDBC 接口的擴(kuò)展，是以 jar 包的形式提供輕量級服務(wù)的。

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

嘻嘻，先復(fù)習(xí)一下主從復(fù)制原理吧，如圖：

image

主從復(fù)制分了五個步驟進(jìn)行：

• 步驟一：主庫的更新事件(update、insert、delete)被寫到binlog
• 步驟二：從庫發(fā)起連接，連接到主庫。
• 步驟三：此時(shí)主庫創(chuàng)建一個binlog dump thread，把binlog的內(nèi)容發(fā)送到從庫。
• 步驟四：從庫啟動之后，創(chuàng)建一個I/O線程，讀取主庫傳過來的binlog內(nèi)容并寫入到relay log
• 步驟五：還會創(chuàng)建一個SQL線程，從relay log里面讀取內(nèi)容，從Exec_Master_Log_Pos位置開始執(zhí)行讀取到的更新事件，將更新內(nèi)容寫入到slave的db

主從同步延遲的原因

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

主從同步延遲的解決辦法

• 主服務(wù)器要負(fù)責(zé)更新操作，對安全性的要求比從服務(wù)器要高，所以有些設(shè)置參數(shù)可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設(shè)置等。
• 選擇更好的硬件設(shè)備作為slave。
• 把一臺從服務(wù)器當(dāng)度作為備份使用， 而不提供查詢， 那邊他的負(fù)載下來了， 執(zhí)行relay log 里面的SQL效率自然就高了。
• 增加從服務(wù)器嘍，這個目的還是分散讀的壓力，從而降低服務(wù)器負(fù)載。

24. 說一下大表查詢的優(yōu)化方案

• 優(yōu)化shema、sql語句+索引；
• 可以考慮加緩存，memcached, redis，或者JVM本地緩存；
• 主從復(fù)制，讀寫分離；
• 分庫分表；

25. 什么是數(shù)據(jù)庫連接池?為什么需要數(shù)據(jù)庫連接池呢?

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

應(yīng)用程序和數(shù)據(jù)庫建立連接的過程：

• 通過TCP協(xié)議的三次握手和數(shù)據(jù)庫服務(wù)器建立連接
• 發(fā)送數(shù)據(jù)庫用戶賬號密碼，等待數(shù)據(jù)庫驗(yàn)證用戶身份
• 完成身份驗(yàn)證后，系統(tǒng)可以提交SQL語句到數(shù)據(jù)庫執(zhí)行
• 把連接關(guān)閉，TCP四次揮手告別。

數(shù)據(jù)庫連接池好處：

• 資源重用 (連接復(fù)用)
• 更快的系統(tǒng)響應(yīng)速度
• 新的資源分配手段
• 統(tǒng)一的連接管理，避免數(shù)據(jù)庫連接泄漏

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

先看一下Mysql的邏輯架構(gòu)圖吧~

image

查詢語句：

• 先檢查該語句是否有權(quán)限
• 如果沒有權(quán)限，直接返回錯誤信息
• 如果有權(quán)限，在 MySQL8.0 版本以前，會先查詢緩存。
• 如果沒有緩存，分析器進(jìn)行詞法分析，提取 sql 語句select等的關(guān)鍵元素。然后判斷sql 語句是否有語法錯誤，比如關(guān)鍵詞是否正確等等。
• 優(yōu)化器進(jìn)行確定執(zhí)行方案
• 進(jìn)行權(quán)限校驗(yàn)，如果沒有權(quán)限就直接返回錯誤信息，如果有權(quán)限就會調(diào)用數(shù)據(jù)庫引擎接口，返回執(zhí)行結(jié)果。

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

• 覆蓋索引
• 最左前綴原則
• 索引下推

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

28. 數(shù)據(jù)庫存儲日期格式時(shí)，如何考慮時(shí)區(qū)轉(zhuǎn)換問題？

• datetime類型適合用來記錄數(shù)據(jù)的原始的創(chuàng)建時(shí)間，修改記錄中其他字段的值，datetime字段的值不會改變，除非手動修改它。
• timestamp類型適合用來記錄數(shù)據(jù)的最后修改時(shí)間，只要修改了記錄中其他字段的值，timestamp字段的值都會被自動更新。

如何考慮時(shí)區(qū)轉(zhuǎn)換問題/看一下這個吧： 數(shù)據(jù)庫存儲日期格式時(shí)，如何考慮時(shí)區(qū)轉(zhuǎn)換問題？

29. 一條sql執(zhí)行過長的時(shí)間，你如何優(yōu)化，從哪些方面入手？

• 查看是否涉及多表和子查詢，優(yōu)化Sql結(jié)構(gòu)，如去除冗余字段，是否可拆表等
• 優(yōu)化索引結(jié)構(gòu)，看是否可以適當(dāng)添加索引
• 數(shù)量大的表，可以考慮進(jìn)行分離/分表（如交易流水表）
• 數(shù)據(jù)庫主從分離，讀寫分離
• explain分析sql語句，查看執(zhí)行計(jì)劃，優(yōu)化sql
• 查看mysql執(zhí)行日志，分析是否有其他方面的問題

30. MYSQL數(shù)據(jù)庫服務(wù)器性能分析的方法命令有哪些?

• Show status, 一些值得監(jiān)控的變量值：

Bytes_received和Bytes_sent 和服務(wù)器之間來往的流量。

Com_*服務(wù)器正在執(zhí)行的命令。

Created_*在查詢執(zhí)行期限間創(chuàng)建的臨時(shí)表和文件。

Handler_*存儲引擎操作。

Select_*不同類型的聯(lián)接執(zhí)行計(jì)劃。

Sort_*幾種排序信息。

• Show profiles 是MySql用來分析當(dāng)前會話SQL語句執(zhí)行的資源消耗情況

31. Blob和text有什么區(qū)別？

• Blob用于存儲二進(jìn)制數(shù)據(jù)，而Text用于存儲大字符串。
• Blob值被視為二進(jìn)制字符串（字節(jié)字符串）,它們沒有字符集，并且排序和比較基于列值中的字節(jié)的數(shù)值。
• text值被視為非二進(jìn)制字符串（字符字符串）。它們有一個字符集，并根據(jù)字符集的排序規(guī)則對值進(jìn)行排序和比較。

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

• 貨幣在數(shù)據(jù)庫中MySQL常用Decimal和Numric類型表示，這兩種類型被MySQL實(shí)現(xiàn)為同樣的類型。他們被用于保存與金錢有關(guān)的數(shù)據(jù)。
• salary DECIMAL(9,2)，9(precision)代表將被用于存儲值的總的小數(shù)位數(shù)，而2(scale)代表將被用于存儲小數(shù)點(diǎn)后的位數(shù)。存儲在salary列中的值的范圍是從-9999999.99到9999999.99。
• DECIMAL和NUMERIC值作為字符串存儲，而不是作為二進(jìn)制浮點(diǎn)數(shù)，以便保存那些值的小數(shù)精度。

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

image

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

• 表鎖： 開銷小，加鎖快；鎖定力度大，發(fā)生鎖沖突概率高，并發(fā)度最低;不會出現(xiàn)死鎖。
• 行鎖： 開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度小，發(fā)生鎖沖突的概率低，并發(fā)度高。
• 頁鎖： <typo id="typo-10199" data-origin="開銷" ignoretag="true">開銷</typo>和加鎖速度介于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般

34. Hash索引和B+樹區(qū)別是什么？你在設(shè)計(jì)索引是怎么抉擇的？

• B+樹可以進(jìn)行范圍查詢，Hash索引不能。
• B+樹支持聯(lián)合索引的最左側(cè)原則，Hash索引不支持。
• B+樹支持order by排序，Hash索引不支持。
• Hash索引在等值查詢上比B+樹效率更高。
• B+樹使用like 進(jìn)行模糊查詢的時(shí)候，like后面（比如%開頭）的話可以起到優(yōu)化的作用，Hash索引根本無法進(jìn)行模糊查詢。

35. mysql 的內(nèi)連接、左連接、右連接有什么區(qū)別？

• Inner join 內(nèi)連接，在兩張表進(jìn)行連接查詢時(shí)，只保留兩張表中完全匹配的結(jié)果集
• left join 在兩張表進(jìn)行連接查詢時(shí)，會返回左表所有的行，即使在右表中沒有匹配的記錄。
• right join 在兩張表進(jìn)行連接查詢時(shí)，會返回右表所有的行，即使在左表中沒有匹配的記錄。

36. 說說MySQL 的基礎(chǔ)架構(gòu)圖

image

Mysql邏輯架構(gòu)圖主要分三層：

• 第一層負(fù)責(zé)連接處理，授權(quán)認(rèn)證，安全等等
• 第二層負(fù)責(zé)編譯并優(yōu)化SQL
• 第三層是存儲引擎。

37. 什么是內(nèi)連接、外連接、交叉連接、笛卡爾積呢？

• 內(nèi)連接（inner join）：取得兩張表中滿足存在連接匹配關(guān)系的記錄。
• 外連接（outer join）：取得兩張表中滿足存在連接匹配關(guān)系的記錄，以及某張表（或兩張表）中不滿足匹配關(guān)系的記錄。
• 交叉連接（cross join）：顯示兩張表所有記錄一一對應(yīng)，沒有匹配關(guān)系進(jìn)行篩選，也被稱為：笛卡爾積。

38. 說一下數(shù)據(jù)庫的三大范式

• 第一范式：數(shù)據(jù)表中的每一列（每個字段）都不可以再拆分。
• 第二范式：在第一范式的基礎(chǔ)上，分主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。
• 第三范式：在滿足第二范式的基礎(chǔ)上，表中的非主鍵只依賴于主鍵，而不依賴于其他非主鍵。

39. mysql有關(guān)權(quán)限的表有哪幾個呢？

MySQL服務(wù)器通過權(quán)限表來控制用戶對數(shù)據(jù)庫的訪問，權(quán)限表存放在mysql數(shù)據(jù)庫里，由mysql_install_db腳本初始化。這些權(quán)限表分別user，db，table_priv，columns_priv和host。

• user權(quán)限表：記錄允許連接到服務(wù)器的用戶帳號信息，里面的權(quán)限是全局級的。
• db權(quán)限表：記錄各個帳號在各個數(shù)據(jù)庫上的操作權(quán)限。
• table_priv權(quán)限表：記錄數(shù)據(jù)表級的操作權(quán)限。
• columns_priv權(quán)限表：記錄數(shù)據(jù)列級的操作權(quán)限。
• host權(quán)限表：配合db權(quán)限表對給定主機(jī)上數(shù)據(jù)庫級操作權(quán)限作更細(xì)致的控制。這個權(quán)限表不受GRANT和REVOKE語句的影響。

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

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

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

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

mixed，一種折中的方案，普通操作使用statement記錄，當(dāng)無法使用statement的時(shí)候使用row。

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

• 插入緩沖（insert buffer)
• 二次寫(double write)
• 自適應(yīng)哈希索引(ahi)
• <typo id="typo-11792" data-origin="預(yù)讀" ignoretag="true">預(yù)讀</typo>(read ahead)

42. 索引有哪些優(yōu)缺點(diǎn)？

優(yōu)點(diǎn)：

• 唯一索引可以保證數(shù)據(jù)庫表中每一行的數(shù)據(jù)的唯一性
• 索引可以加快數(shù)據(jù)查詢速度，減少查詢時(shí)間

缺點(diǎn)：

• 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間
• 索引需要占物理空間，除了數(shù)據(jù)表占用數(shù)據(jù)空間之外，每一個索引還要占用一定的物理空間
• 以表中的數(shù)據(jù)進(jìn)行增、刪、改的時(shí)候，索引也要動態(tài)的維護(hù)。

43. 索引有哪幾種類型？

• 主鍵索引: 數(shù)據(jù)列不允許重復(fù)，不允許為NULL，一個表只能有一個主鍵。
• 唯一索引: 數(shù)據(jù)列不允許重復(fù)，允許為NULL值，一個表允許多個列創(chuàng)建唯一索引。
• 普通索引: 基本的索引類型，沒有唯一性的限制，允許為NULL值。
• 全文索引：是目前搜索引擎使用的一種關(guān)鍵技術(shù)，對文本的內(nèi)容進(jìn)行分詞、搜索。
• 覆蓋索引：查詢列要被所建的索引覆蓋，不必讀取數(shù)據(jù)行
• 組合索引：多列值組成一個索引，用于組合搜索，效率大于索引合并

44. 創(chuàng)建索引有什么原則呢？

• 最左前綴匹配原則
• 頻繁作為查詢條件的字段才去創(chuàng)建索引
• 頻繁更新的字段不適合創(chuàng)建索引
• 索引列不能參與計(jì)算，不能有函數(shù)操作
• 優(yōu)先考慮擴(kuò)展索引，而不是新建索引，避免不必要的索引
• 在order by或者group by子句中，創(chuàng)建索引需要注意順序
• 區(qū)分度低的數(shù)據(jù)列不適合做索引列(如性別）
• 定義有外鍵的數(shù)據(jù)列一定要建立索引。
• 對于定義為text、image數(shù)據(jù)類型的列不要建立索引。
• 刪除不再使用或者很少使用的索引

45. 創(chuàng)建索引的三種方式

• 在執(zhí)行CREATE TABLE時(shí)創(chuàng)建索引

<pre language="javascript" code_block="true">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;</pre>

• 使用ALTER TABLE命令添加索引

<pre language="javascript" code_block="true">ALTER TABLE table_name ADD INDEX index_name (column);
復(fù)制代</pre>

• 使用CREATE INDEX命令創(chuàng)建

<pre language="javascript" code_block="true">CREATE INDEX index_name ON table_name (column);</pre>

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

• 我們想要刪除百萬數(shù)據(jù)的時(shí)候可以先刪除索引
• 然后批量刪除其中無用數(shù)據(jù)
• 刪除完成后重新創(chuàng)建索引。

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

最左前綴原則，就是最左優(yōu)先，在創(chuàng)建多列索引時(shí)，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊。當(dāng)我們創(chuàng)建一個組合索引的時(shí)候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。。

48. B樹和B+樹的區(qū)別，數(shù)據(jù)庫為什么使用B+樹而不是B樹？

• 在B樹中，鍵和值即存放在內(nèi)部節(jié)點(diǎn)又存放在葉子節(jié)點(diǎn)；在B+樹中，內(nèi)部節(jié)點(diǎn)只存鍵，葉子節(jié)點(diǎn)則同時(shí)存放鍵和值。
• B+樹的葉子節(jié)點(diǎn)有一條鏈相連，而B樹的葉子節(jié)點(diǎn)各自獨(dú)立的。

B+樹索引的所有數(shù)據(jù)均存儲在葉子節(jié)點(diǎn)，而且數(shù)據(jù)是按照順序排列的，鏈表連著的。那么B+樹使得范圍查找，排序查找，分組查找以及去重查找變得異常簡單。.B+樹非葉子節(jié)點(diǎn)上是不存儲數(shù)據(jù)的，僅存儲鍵值，而B樹節(jié)點(diǎn)中不僅存儲鍵值，也會存儲數(shù)據(jù)。innodb中頁的默認(rèn)大小是16KB，如果不存儲數(shù)據(jù)，那么就會存儲更多的鍵值，相應(yīng)的樹的階數(shù)（節(jié)點(diǎn)的子節(jié)點(diǎn)樹）就會更大，樹就會更矮更胖，如此一來我們查找數(shù)據(jù)進(jìn)行磁盤的IO次數(shù)有會再次減少，數(shù)據(jù)查詢的效率也會更快.

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

• 覆蓋索引： 查詢列要被所建的索引覆蓋，不必從數(shù)據(jù)表中讀取，換句話說查詢列要被所使用的索引覆蓋。
• 回表：二級索引無法直接查詢所有列的數(shù)據(jù)，所以通過二級索引查詢到聚簇索引后，再查詢到想要的數(shù)據(jù)，這種通過二級索引查詢出來的過程，就叫做回表。

網(wǎng)上這篇文章講得很清晰： mysql覆蓋索引與回表

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

在B+樹的索引中，葉子節(jié)點(diǎn)可能存儲了當(dāng)前的key值，也可能存儲了當(dāng)前的key值以及整行的數(shù)據(jù)，這就是聚簇索引和非聚簇索引。 在InnoDB中，只有主鍵索引是聚簇索引，如果沒有主鍵，則挑選一個唯一鍵建立聚簇索引。如果沒有唯一鍵，則隱式的生成一個鍵來建立聚簇索引。當(dāng)查詢使用聚簇索引時(shí)，在對應(yīng)的葉子節(jié)點(diǎn)，可以獲取到整行數(shù)據(jù)，因此不用再次進(jìn)行回表查詢。

51. 何時(shí)使用聚簇索引與非聚簇索引

image

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

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

舉個簡單的例子，假設(shè)我們在學(xué)生表的上建立了索引，那么當(dāng)進(jìn)行select age from student where age < 20的查詢時(shí)，在索引的葉子節(jié)點(diǎn)上，已經(jīng)包含了age信息，不會再次進(jìn)行回表查詢。

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

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

因?yàn)镮nnoDB引擎中的索引策略的最左原則，所以需要注意組合索引中的順序。

54. 什么是數(shù)據(jù)庫事務(wù)？

數(shù)據(jù)庫事務(wù)（簡稱：事務(wù)），是數(shù)據(jù)庫管理系統(tǒng)執(zhí)行過程中的一個邏輯單位，由一個有限的數(shù)據(jù)庫操作序列構(gòu)成，這些操作要么全部執(zhí)行,要么全部不執(zhí)行，是一個不可分割的工作單位。

55. 隔離級別與鎖的關(guān)系

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

可以看我這篇文章哈：一文徹底讀懂MySQL事務(wù)的四大隔離級別

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

image

• 按鎖粒度分有：表鎖，頁鎖，行鎖
• 按鎖機(jī)制分有：樂觀鎖，悲觀鎖

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

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

• 共享鎖: 又叫做讀鎖。當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時(shí)，對數(shù)據(jù)加上共享鎖。共享鎖可以同時(shí)加上多個。
• 排他鎖: 又叫做寫鎖。當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫入時(shí)，對數(shù)據(jù)加上排他鎖。排他鎖只可以加一個，他和其他的排他鎖，共享鎖都相斥。

<typo id="typo-14585" data-origin="鎖" ignoretag="true">鎖</typo>兼容性如下：

image

58. MySQL中InnoDB引擎的行鎖是怎么實(shí)現(xiàn)的？

基于索引來完成行鎖的。

<pre language="javascript" code_block="true">select * from t where id = 666 for update;</pre>

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

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

死鎖是指兩個或多個事務(wù)在同一資源上相互占用，并請求鎖定對方的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象?？磮D形象一點(diǎn)，如下：

image

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

解決死鎖思路，一般就是切斷環(huán)路，盡量避免并發(fā)形成環(huán)路。

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

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

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

如果業(yè)務(wù)處理不好可以用分布式事務(wù)鎖或者使用樂觀鎖

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

有興趣的朋友，可以看我的這篇死鎖分析： 手把手教你分析Mysql死鎖問題

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

為什么要使用視圖？

為了提高復(fù)雜SQL語句的復(fù)用性和表操作的安全性，MySQL數(shù)據(jù)庫管理系統(tǒng)提供了視圖特性。

什么是視圖？

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

61. 視圖有哪些特點(diǎn)？哪些使用場景？

視圖特點(diǎn)：

• 視圖的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關(guān)系。
• 視圖是由基本表(實(shí)表)產(chǎn)生的表(虛表)。
• 視圖的建立和刪除不影響基本表。
• 對視圖內(nèi)容的更新(添加，刪除和修改)直接影響基本表。
• 當(dāng)視圖來自多個基本表時(shí)，不允許添加和刪除數(shù)據(jù)。

視圖用途： 簡化sql查詢，提高開發(fā)效率，兼容老的表結(jié)構(gòu)。

視圖的常見使用場景：

• 重用SQL語句；
• 簡化復(fù)雜的SQL操作。
• 使用表的組成部分而不是整個表；
• 保護(hù)數(shù)據(jù)
• 更改數(shù)據(jù)格式和表示。視圖可返回與底層表的表示和格式不同的數(shù)據(jù)。

62. 視圖的優(yōu)點(diǎn)，缺點(diǎn)，講一下？

• 查詢簡單化。視圖能簡化用戶的操作
• 數(shù)據(jù)安全性。視圖使用戶能以多種角度看待同一數(shù)據(jù)，能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)
• 邏輯數(shù)據(jù)獨(dú)立性。視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性

63. count(1)、count(*) 與 count(列名) 的區(qū)別？

• count(*)包括了所有的列，相當(dāng)于行數(shù)，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會忽略列值為NULL
• count(1)包括了忽略所有列，用1代表代碼行，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會忽略列值為NULL
• count(列名)只包括列名那一列，在統(tǒng)計(jì)結(jié)果的時(shí)候，會忽略列值為空（這里的空不是只空字符串或者0，而是表示null）的計(jì)數(shù)，即某個字段值為NULL時(shí)，不統(tǒng)計(jì)。

64. 什么是游標(biāo)？

游標(biāo)提供了一種對從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手段，就本質(zhì)而言，游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。

65. 什么是存儲過程？有哪些優(yōu)缺點(diǎn)？

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

優(yōu)點(diǎn)：

• 存儲過程是一個預(yù)編譯的代碼塊，執(zhí)行效率比較高
• 存儲過程在服務(wù)器端運(yùn)行，減少客戶端的壓力
• 允許模塊化程序設(shè)計(jì)，只需要創(chuàng)建一次過程，以后在程序中就可以調(diào)用該過程任意次，類似方法的復(fù)用
• 一個存儲過程替代大量T_SQL語句 ，可以降低網(wǎng)絡(luò)通信量，提高通信速率
• 可以一定程度上確保數(shù)據(jù)安全

缺點(diǎn)：

• 調(diào)試麻煩
• 可移植性不靈活
• 重新編譯問題

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

觸發(fā)器，指一段代碼，當(dāng)觸發(fā)某個事件時(shí)，自動執(zhí)行這些代碼。

使用場景：

• 可以通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級聯(lián)更改。
• 實(shí)時(shí)監(jiān)控某張表中的某個字段的更改而需要做出相應(yīng)的處理。
• 例如可以生成某些業(yè)務(wù)的編號。
• 注意不要濫用，否則會造成數(shù)據(jù)庫及應(yīng)用程序的維護(hù)困難。

67. MySQL中都有哪些觸發(fā)器？

MySQL 數(shù)據(jù)庫中有六種觸發(fā)器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

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

• 超鍵：在關(guān)系模式中，能唯一知標(biāo)識元組的屬性集稱為超鍵。
• 候選鍵：是最小超鍵，即沒有冗余元素的超鍵。
• 主鍵：數(shù)據(jù)庫表中對儲存數(shù)據(jù)對象予以唯一和完整標(biāo)識的數(shù)據(jù)列或?qū)傩缘慕M合。一個數(shù)據(jù)列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。
• 外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。。

69. SQL 約束有哪幾種呢？

• NOT NULL: 約束字段的內(nèi)容一定不能為NULL。
• UNIQUE: 約束字段唯一性，一個表允許有多個 Unique 約束。
• PRIMARY KEY: 約束字段唯一，不可重復(fù)，一個表只允許存在一個。
• FOREIGN KEY: 用于預(yù)防破壞表之間連接的動作，也能防止非法數(shù)據(jù)插入外鍵。
• CHECK: 用于控制字段的值范圍。

70. 談?wù)劻N關(guān)聯(lián)查詢，使用場景。

• 交叉連接
• 內(nèi)連接
• 外連接
• 聯(lián)合查詢
• 全連接
• 交叉連接

71. varchar(50)中50的<typo id="typo-16960" data-origin="涵義" ignoretag="true">涵義</typo>

• 字段最多存放 50 個字符
• 如 varchar(50) 和 varchar(200) 存儲 "jay" 字符串所占空間是一樣的，后者在排序時(shí)會消耗更多內(nèi)存

72. mysql中int(20)和char(20)以及varchar(20)的區(qū)別

• int(20) 表示字段是int類型，顯示長度是 20
• char(20)表示字段是固定長度字符串，長度為 20
• varchar(20) 表示字段是可變長度字符串，長度為 20

73. drop、delete與truncate的區(qū)別

image

74. UNION與UNION ALL的區(qū)別？

• Union：對兩個結(jié)果集進(jìn)行并集操作，不包括重復(fù)行，同時(shí)進(jìn)行默認(rèn)規(guī)則的排序；
• Union All：對兩個結(jié)果集進(jìn)行并集操作，包括重復(fù)行，不進(jìn)行排序；
• UNION的效率高于 UNION ALL

75. SQL的生命周期？

服務(wù)器與數(shù)據(jù)庫建立連接

數(shù)據(jù)庫進(jìn)程拿到請求sql

解析并生成執(zhí)行計(jì)劃，執(zhí)行

讀取數(shù)據(jù)到內(nèi)存，并進(jìn)行邏輯處理

通過步驟一的連接，發(fā)送結(jié)果到客戶端

關(guān)掉連接，釋放資源

76. 一條Sql的執(zhí)行順序？

image

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

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

計(jì)劃對列進(jìn)行索引，應(yīng)盡量避免把它設(shè)置為可空，因?yàn)檫@會讓 MySQL 難以優(yōu)化引用了可空列的查詢，同時(shí)增加了引擎的復(fù)雜度

78. 關(guān)心過業(yè)務(wù)系統(tǒng)里面的sql耗時(shí)嗎？統(tǒng)計(jì)過慢查詢嗎？對慢查詢都怎么優(yōu)化過？

• 我們平時(shí)寫Sql時(shí)，都要養(yǎng)成用explain分析的習(xí)慣。
• 慢查詢的統(tǒng)計(jì)，運(yùn)維會定期統(tǒng)計(jì)給我們

優(yōu)化慢查詢：

• 分析語句，是否加載了不必要的字段/數(shù)據(jù)。
• 分析SQl執(zhí)行句話，是否命中索引等。
• 如果SQL很復(fù)雜，優(yōu)化SQL結(jié)構(gòu)
• 如果表數(shù)據(jù)量太大，考慮分

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

如果是單機(jī)的話，選擇自增ID；如果是分布式系統(tǒng)，優(yōu)先考慮UUID吧，但還是最好自己公司有一套分布式唯一ID生產(chǎn)方案吧。

• 自增ID：數(shù)據(jù)存儲空間小，查詢效率高。但是如果數(shù)據(jù)量過大,會超出自增長的值范圍，多庫合并，也有可能有問題。
• uuid：適合大量數(shù)據(jù)的插入和更新操作，但是它無序的，插入數(shù)據(jù)效率慢，占用空間大。

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

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

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

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

82. 如果要存儲用戶的密碼散列，應(yīng)該使用什么字段進(jìn)行存儲？

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

83. Mysql驅(qū)動程序是什么？

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

Mysql驅(qū)動程序主要幫助編程語言與 MySQL服務(wù)端進(jìn)行通信，如連接、傳輸數(shù)據(jù)、關(guān)閉等。

84. 如何優(yōu)化長難的查詢語句？有實(shí)戰(zhàn)過嗎？

• 將一個大的查詢分為多個小的相同的查詢
• 減少冗余記錄的查詢。
• 一個復(fù)雜查詢可以考慮拆成多個簡單查詢
• 分解關(guān)聯(lián)查詢，讓緩存的效率更高。

85. 優(yōu)化特定類型的查詢語句

平時(shí)積累吧：

• 比如使用select 具體字段代替 select *
• 使用count(*) 而不是count(列名)
• 在不影響業(yè)務(wù)的情況，使用緩存
• explain 分析你的SQL

86. MySQL數(shù)據(jù)庫cpu飆升的話，要怎么處理呢？

排查過程：

• 使用top 命令觀察，確定是mysqld導(dǎo)致還是其他原因。
• 如果是mysqld導(dǎo)致的，show processlist，查看session情況，確定是不是有消耗資源的sql在運(yùn)行。
• 找出消耗高的 sql，看看執(zhí)行計(jì)劃是否準(zhǔn)確， 索引是否缺失，數(shù)據(jù)量是否太大。

處理：

• kill 掉這些線程(同時(shí)觀察 cpu 使用率是否下降)，
• 進(jìn)行相應(yīng)的調(diào)整(比如說加索引、改 sql、改內(nèi)存參數(shù))
• 重新跑這些 SQL。

其他情況：

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

87. 讀寫分離常見方案？

• 應(yīng)用程序根據(jù)業(yè)務(wù)邏輯來判斷，增刪改等寫操作命令發(fā)給主庫，查詢命令發(fā)給備庫。
• 利用中間件來做代理，負(fù)責(zé)對數(shù)據(jù)庫的請求識別出讀還是寫，并分發(fā)到不同的數(shù)據(jù)庫中。（如：amoeba，mysql-proxy）

88. MySQL的復(fù)制原理以及流程

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

• 主數(shù)據(jù)庫有個bin-log二進(jìn)制文件，紀(jì)錄了所有增刪改Sql語句。（binlog線程）
• 從數(shù)據(jù)庫把主數(shù)據(jù)庫的bin-log文件的sql語句復(fù)制過來。（io線程）
• 從數(shù)據(jù)庫的relay-log重做日志文件中再執(zhí)行一次這些sql語句。（Sql執(zhí)行線程）

如下圖所示：

image

上圖主從復(fù)制分了五個步驟進(jìn)行：

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

步驟二：從庫發(fā)起連接，連接到主庫。

步驟三：此時(shí)主庫創(chuàng)建一個binlog dump thread，把binlog的內(nèi)容發(fā)送到從庫。

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

步驟五：還會創(chuàng)建一個SQL線程，從relay log里面讀取內(nèi)容，從Exec_Master_Log_Pos位置開始執(zhí)行讀取到的更新事件，將更新內(nèi)容寫入到slave的db

89. MySQL中DATETIME和TIMESTAMP的區(qū)別

存儲精度都為秒

區(qū)別：

• DATETIME 的日期范圍是 1001——9999 年；TIMESTAMP 的時(shí)間范圍是 1970——2038 年
• DATETIME 存儲時(shí)間與時(shí)區(qū)無關(guān)；TIMESTAMP 存儲時(shí)間與時(shí)區(qū)有關(guān)，顯示的值也依賴于時(shí)區(qū)
• DATETIME 的存儲空間為 8 字節(jié)；TIMESTAMP 的存儲空間為 4 字節(jié)
• DATETIME 的默認(rèn)值為 null；TIMESTAMP 的字段默認(rèn)不為空(not null)，默認(rèn)值為當(dāng)前時(shí)間(CURRENT_TIMESTAMP)

90. Innodb的事務(wù)實(shí)現(xiàn)原理？

• 原子性：是使用 undo log來實(shí)現(xiàn)的，如果事務(wù)執(zhí)行過程中出錯或者用戶執(zhí)行了rollback，系統(tǒng)通過undo log日志返回事務(wù)開始的狀態(tài)。
• 持久性：使用 redo log來實(shí)現(xiàn)，只要redo log日志持久化了，當(dāng)系統(tǒng)崩潰，即可通過redo log把數(shù)據(jù)恢復(fù)。
• 隔離性：通過鎖以及MVCC,使事務(wù)相互隔離開。
• 一致性：通過回滾、恢復(fù)，以及并發(fā)情況下的隔離性，從而實(shí)現(xiàn)一致性。

91. 談?wù)凪ySQL的Explain

Explain 執(zhí)行計(jì)劃包含字段信息如下：分別是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 等12個字段。

我們重點(diǎn)關(guān)注的是type，它的屬性排序如下：

<pre language="javascript" code_block="true">system > const > eq_ref > ref > ref_or_null >
index_merge > unique_subquery > index_subquery >
range > index > ALL</pre>

92. Innodb的事務(wù)與日志的實(shí)現(xiàn)方式

有多少種日志

innodb兩種日志redo和undo。

日志的存放形式

• redo：在頁修改的時(shí)候，先寫到 redo log buffer 里面， 然后寫到 redo log 的文件系統(tǒng)緩存里面(fwrite)，然后再同步到磁盤文件（ fsync）。
• Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通過設(shè)置 innodb_undo_tablespaces 參數(shù)把 undo log 存放在 ibdata之外。

事務(wù)是如何通過日志來實(shí)現(xiàn)的

• 因?yàn)槭聞?wù)在修改頁時(shí)，要先記 undo，在記 undo 之前要記 undo 的 redo， 然后修改數(shù)據(jù)頁，再記數(shù)據(jù)頁修改的 redo。 Redo（里面包括 undo 的修改） 一定要比數(shù)據(jù)頁先持久化到磁盤。
• 當(dāng)事務(wù)需要回滾時(shí)，因?yàn)橛?undo，可以把數(shù)據(jù)頁回滾到前鏡像的 狀態(tài)，崩潰恢復(fù)時(shí)，如果 redo log 中事務(wù)沒有對應(yīng)的 commit 記錄，那么需要用 undo把該事務(wù)的修改回滾到事務(wù)開始之前。
• 如果有 commit 記錄，就用 redo 前滾到該事務(wù)完成時(shí)并提交掉。

93. MySQL中TEXT數(shù)據(jù)類型的最大長度

• TINYTEXT：256 bytes
• TEXT：65,535 bytes(64kb)
• MEDIUMTEXT：16,777,215 bytes(16MB)
• LONGTEXT：4,294,967,295 bytes(4GB)

94. 500臺db，在最快時(shí)間之內(nèi)重啟。

• 可以使用批量 ssh 工具 pssh 來對需要重啟的機(jī)器執(zhí)行重啟命令。
• 也可以使用 salt（前提是客戶端有安裝 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多線程工具同時(shí)操作多臺服務(wù)

95. 你是如何監(jiān)控你們的數(shù)據(jù)庫的？你們的慢日志都是怎么查詢的？

監(jiān)控的工具有很多，例如zabbix，lepus，我這里用的是lepus

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

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

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

更換字符集utf8-->utf8mb4

98. MySQL如何獲取當(dāng)前日期？

SELECT CURRENT_DATE();

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

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

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

100. Mysql一條SQL加鎖分析

一條SQL加鎖，可以分9種情況進(jìn)行：

• 組合一：id列是主鍵，RC隔離級別
• 組合二：id列是二級唯一索引，RC隔離級別
• 組合三：id列是二級非唯一索引，RC隔離級別
• 組合四：id列上沒有索引，RC隔離級別
• 組合五：id列是主鍵，RR隔離級別
• 組合六：id列是二級唯一索引，RR隔離級別
• 組合七：id列是二級非唯一索引，RR隔離級別
• 組合八：id列上沒有索引，RR隔離級別
• 組合九：Serializable隔離級別

如果答案整理有錯，歡迎指出哈，感激不盡~