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

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

索引哪些情況會失效

查詢條件包含or含滴，可能導(dǎo)致索引失效

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

like通配符可能導(dǎo)致索引失效谈况。

聯(lián)合索引勺美，查詢時的條件列不是聯(lián)合索引中的第一個列，索引失效碑韵。

在索引列上使用mysql的內(nèi)置函數(shù)赡茸，索引失效。

對索引列運算（如泼诱，+坛掠、-、*治筒、/）屉栓，索引失效。

索引字段上使用（耸袜！= 或者 < >友多，not in）時，可能會導(dǎo)致索引失效堤框。

索引字段上使用is null域滥， is not null，可能導(dǎo)致索引失效蜈抓。

左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣启绰，可能導(dǎo)致索引失效。

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

索引不適合哪些場景

數(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)

分庫分表

讀寫分離

可以看我這篇文章哈：

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

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

分庫分表方案:

水平分庫：以字段為依據(jù)燕少，按照一定策略（hash卡者、range等），將一個庫中的數(shù)據(jù)拆分到多個庫中客们。

水平分表：以字段為依據(jù)崇决，按照一定策略（hash、range等）底挫，將一個表中的數(shù)據(jù)拆分到多個表中嗽桩。

垂直分庫：以表為依據(jù)，按照業(yè)務(wù)歸屬不同凄敢，將不同的表拆分到不同的庫中碌冶。

垂直分表：以字段為依據(jù)，按照字段的活躍性涝缝，將表中字段拆到不同的表（主表和擴展表）中扑庞。

常用的分庫分表中間件：

sharding-jdbc（當(dāng)當(dāng)）

Mycat

TDDL（淘寶）

Oceanus(58同城數(shù)據(jù)庫中間件)

vitess（谷歌開發(fā)的數(shù)據(jù)庫中間件）

Atlas(Qihoo 360)

分庫分表可能遇到的問題

事務(wù)問題：需要用分布式事務(wù)啦

跨節(jié)點Join的問題：解決這一問題可以分兩次查詢實現(xiàn)

跨節(jié)點的count,order by,group by以及聚合函數(shù)問題：分別在各個節(jié)點上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并譬重。

數(shù)據(jù)遷移，容量規(guī)劃罐氨，擴容等問題

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

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

個人覺得網(wǎng)上這兩篇文章不錯塔嬉，小伙伴們可以去看一下哈：

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

InnoDB支持事務(wù)，MyISAM不支持事務(wù)

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

InnoDB 支持 MVCC(多版本并發(fā)控制)谨究，MyISAM 不支持

select count(*) from table時，MyISAM更快泣棋，因為它有一個變量保存了整個表的總行數(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é)點天揖，也就是我們說的一個磁盤塊，但是平衡二叉樹可是每個節(jié)點只存儲一個鍵值和數(shù)據(jù)的跪帝，如果是B樹今膊，可以存儲更多的節(jié)點數(shù)據(jù)，樹的高度也會降低伞剑，因此讀取磁盤的次數(shù)就降下來啦斑唬，查詢效率就快啦。

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

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

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

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

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

聚集索引器紧，索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序；非聚集索引楼眷，索引中索引的邏輯順序與磁盤上行的物理存儲順序不同铲汪。

索引是通過二叉樹的數(shù)據(jù)結(jié)構(gòu)來描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點就是數(shù)據(jù)節(jié)點罐柳。而非聚簇索引的葉節(jié)點仍然是索引節(jié)點掌腰，只不過有一個指針指向?qū)?yīng)的數(shù)據(jù)塊。

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

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

image

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

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

selectid，namefromemployeewhereid>1000000limit10.

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

建議跟業(yè)務(wù)討論昆码，有沒有必要查這么后的分頁啦气忠。因為絕大多數(shù)用戶都不會往后翻太多頁。

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

selectid赋咽，namefromemployee order by id? limit1000000旧噪，10

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

SELECTa.*FROMemployee a,(selectidfromemployeewhere條件LIMIT1000000,10)bwherea.id=b.id

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

數(shù)據(jù)庫自增長序列或字段。

UUID亦镶。

Redis生成ID

Twitter的snowflake算法

利用zookeeper生成唯一ID

MongoDB的ObjectId

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

讀未提交（Read Uncommitted）

讀已提交（Read Committed）

可重復(fù)讀（Repeatable Read）

串行化（Serializable）

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

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

事務(wù)A绊起、B交替執(zhí)行，事務(wù)A被事務(wù)B干擾到了燎斩，因為事務(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ù)兴革，就要保證一個線程在修改時其它線程無法更新這行記錄。一般有悲觀鎖和樂觀鎖兩種方案~

使用悲觀鎖

悲觀鎖思想就是蜜唾，當(dāng)前線程要進(jìn)來修改數(shù)據(jù)時杂曲，別的線程都得拒之門外~

比如，可以使用select…for update ~

select*fromUserwherename=‘jay’forupdate

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

使用樂觀鎖

樂觀鎖思想就是泌霍，有線程過來货抄，先放過去修改，如果看到別的線程沒修改過朱转，就可以修改成功蟹地，如果別的線程修改過，就修改失敗或者重試藤为。實現(xiàn)方式：樂觀鎖一般會使用版本號機制或CAS算法實現(xiàn)怪与。

可以看一下我這篇文章，主要是思路哈~

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

悲觀鎖：

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

image

樂觀鎖：

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

image

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

show status 命令了解各種 sql 的執(zhí)行頻率

通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 sql 語句

explain 分析低效 sql 的執(zhí)行計劃（這點非常重要，日常開發(fā)中用它分析Sql渴析，會大大降低Sql導(dǎo)致的線上事故）

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

select for update 含義

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

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

select for update 加鎖驗證

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

//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=1570068DEFAULT CHARSET=utf8

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

image

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

image

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

image

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

image

原子性： 事務(wù)作為一個整體被執(zhí)行，包含在其中的對數(shù)據(jù)庫的操作要么全部被執(zhí)行庄敛，要么都不執(zhí)行俗壹。

一致性： 指在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)不會被破壞铐姚，假如A賬戶給B賬戶轉(zhuǎn)10塊錢策肝，不管成功與否，A和B的總金額是不變的隐绵。

隔離性： 多個事務(wù)并發(fā)訪問時之众，事務(wù)之間是相互隔離的，即一個事務(wù)不影響其它事務(wù)運行效果依许。簡言之棺禾，就是事務(wù)之間是進(jìn)水不犯河水的。

持久性： 表示事務(wù)完成以后峭跳，該事務(wù)對數(shù)據(jù)庫所作的操作更改膘婶，將持久地保存在數(shù)據(jù)庫之中。

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

原子性：是使用 undo log來實現(xiàn)的蛀醉，如果事務(wù)執(zhí)行過程中出錯或者用戶執(zhí)行了rollback悬襟，系統(tǒng)通過undo log日志返回事務(wù)開始的狀態(tài)。

持久性：使用 redo log來實現(xiàn)拯刁，只要redo log日志持久化了脊岳，當(dāng)系統(tǒng)崩潰，即可通過redo log把數(shù)據(jù)恢復(fù)垛玻。

隔離性：通過鎖以及MVCC,使事務(wù)相互隔離開割捅。

一致性：通過回滾、恢復(fù)帚桩，以及并發(fā)情況下的隔離性亿驾，從而實現(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é)點Join的問題）

解決方案（分布式事務(wù)等）

索引優(yōu)化

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

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

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

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

select*fromtablewherek1=AANDk2=BANDk3=D

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

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

這個猪狈，跟一下demo來看更刺激吧箱沦，啊哈哈

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

select*fromAwheredeptIdin(selectdeptIdfromB);

這樣寫等價于：

先查詢部門表B

select deptId from B

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

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

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

List<>resultSet;for(inti=0;i<B.length;i++){for(intj=0;j<A.length;j++){if(A[i].id==B[j].id){resultSet.add(A[i]);break;}}}

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

select*fromAwhereexists(select1fromBwhereA.deptId=B.deptId);

因為exists查詢的理解就是竹椒，先執(zhí)行主查詢童太，獲得數(shù)據(jù)后，再放到子查詢中做條件驗證胸完，根據(jù)驗證結(jié)果（true或者false）书释，來決定主查詢的數(shù)據(jù)結(jié)果是否得意保留。

那么赊窥，這樣寫就等價于：

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

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

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

List<>resultSet;for(inti=0;i<A.length;i++){for(intj=0;j<B.length;j++){if(A[i].deptId==B[j].deptId){resultSet.add(A[i]);break;}}}

數(shù)據(jù)庫最費勁的就是跟程序鏈接釋放。假設(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ù)等的問題冗恨。解決方案的話，簡單點的話可以考慮使用UUID哈

自增主鍵會產(chǎn)生表鎖，從而引發(fā)問題

自增主鍵可能用完問題揪利。

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

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

MVCC需要關(guā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 接口的擴展荐虐，是以 jar 包的形式提供輕量級服務(wù)的惜犀。

23. MYSQL的主從延遲汽烦，你怎么解決牍颈？

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

image

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

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

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

步驟三：此時主庫創(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í)行的時間稍長 或者由于某個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ù)庫驗證用戶身份

完成身份驗證后蘑志，系統(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)限校驗，如果沒有權(quán)限就直接返回錯誤信息裆熙，如果有權(quán)限就會調(diào)用數(shù)據(jù)庫引擎接口端礼，返回執(zhí)行結(jié)果。

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

覆蓋索引

最左前綴原則

索引下推

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

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

datetime類型適合用來記錄數(shù)據(jù)的原始的創(chuàng)建時間唤崭，修改記錄中其他字段的值拷恨，datetime字段的值不會改變，除非手動修改它谢肾。

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

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

查看是否涉及多表和子查詢分预，優(yōu)化Sql結(jié)構(gòu)，如去除冗余字段薪捍，是否可拆表等

優(yōu)化索引結(jié)構(gòu)笼痹，看是否可以適當(dāng)添加索引

數(shù)量大的表，可以考慮進(jìn)行分離/分表（如交易流水表）

數(shù)據(jù)庫主從分離酪穿，讀寫分離

explain分析sql語句凳干，查看執(zhí)行計劃，優(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)建的臨時表和文件只磷。

Handler_*存儲引擎操作净响。

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

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實現(xiàn)為同樣的類型。他們被用于保存與金錢有關(guān)的數(shù)據(jù)蜈膨。

salary DECIMAL(9,2)屿笼，9(precision)代表將被用于存儲值的總的小數(shù)位數(shù)，而2(scale)代表將被用于存儲小數(shù)點后的位數(shù)翁巍。存儲在salary列中的值的范圍是從-9999999.99到9999999.99驴一。

DECIMAL和NUMERIC值作為字符串存儲，而不是作為二進(jìn)制浮點數(shù)灶壶，以便保存那些值的小數(shù)精度肝断。

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

image

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

表鎖： 開銷小，加鎖快恰响；鎖定力度大趣钱，發(fā)生鎖沖突概率高，并發(fā)度最低;不會出現(xiàn)死鎖渔隶。

行鎖： 開銷大羔挡，加鎖慢；會出現(xiàn)死鎖间唉；鎖定粒度小绞灼，發(fā)生鎖沖突的概率低，并發(fā)度高呈野。

頁鎖： 開銷和加鎖速度介于表鎖和行鎖之間低矮；會出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間被冒，并發(fā)度一般

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

B+樹可以進(jìn)行范圍查詢，Hash索引不能蝗锥。

B+樹支持聯(lián)合索引的最左側(cè)原則跃洛，Hash索引不支持。

B+樹支持order by排序终议，Hash索引不支持汇竭。

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

B+樹使用like 進(jìn)行模糊查詢的時候穴张，like后面（比如%開頭）的話可以起到優(yōu)化的作用细燎，Hash索引根本無法進(jìn)行模糊查詢。

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

Inner join 內(nèi)連接偿枕，在兩張表進(jìn)行連接查詢時璧瞬，只保留兩張表中完全匹配的結(jié)果集

left join 在兩張表進(jìn)行連接查詢時，會返回左表所有的行益老，即使在右表中沒有匹配的記錄彪蓬。

right join 在兩張表進(jìn)行連接查詢時，會返回右表所有的行捺萌，即使在左表中沒有匹配的記錄档冬。

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)限表對給定主機上數(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í)行是有上下文的在刺，因此在保存的時候需要保存相關(guān)的信息逆害，同時還有一些使用了函數(shù)之類的語句無法被記錄復(fù)制。

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

mixed靡馁，一種折中的方案，普通操作使用statement記錄机久，當(dāng)無法使用statement的時候使用row臭墨。

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

插入緩沖（insert buffer)

二次寫(double write)

自適應(yīng)哈希索引(ahi)

預(yù)讀(read ahead)

42.? 索引有哪些優(yōu)缺點膘盖？

優(yōu)點：

唯一索引可以保證數(shù)據(jù)庫表中每一行的數(shù)據(jù)的唯一性

索引可以加快數(shù)據(jù)查詢速度胧弛，減少查詢時間

缺點：

創(chuàng)建索引和維護(hù)索引要耗費時間

索引需要占物理空間，除了數(shù)據(jù)表占用數(shù)據(jù)空間之外侠畔，每一個索引還要占用一定的物理空間

以表中的數(shù)據(jù)進(jìn)行增结缚、刪、改的時候软棺，索引也要動態(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)建索引

索引列不能參與計算粉渠，不能有函數(shù)操作

優(yōu)先考慮擴展索引，而不是新建索引圾另，避免不必要的索引

在order by或者group by子句中霸株，創(chuàng)建索引需要注意順序

區(qū)分度低的數(shù)據(jù)列不適合做索引列(如性別）

定義有外鍵的數(shù)據(jù)列一定要建立索引。

對于定義為text集乔、image數(shù)據(jù)類型的列不要建立索引去件。

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

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

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

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命令創(chuàng)建

CREATE INDEX index_name ON table_name(column);

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

我們想要刪除百萬數(shù)據(jù)的時候可以先刪除索引

然后批量刪除其中無用數(shù)據(jù)

刪除完成后重新創(chuàng)建索引尤溜。

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

最左前綴原則宫莱，就是最左優(yōu)先丈攒，在創(chuàng)建多列索引時，要根據(jù)業(yè)務(wù)需求授霸，where子句中使用最頻繁的一列放在最左邊巡验。

當(dāng)我們創(chuàng)建一個組合索引的時候，如(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é)點又存放在葉子節(jié)點力奋；在B+樹中榜旦，內(nèi)部節(jié)點只存鍵，葉子節(jié)點則同時存放鍵和值景殷。

B+樹的葉子節(jié)點有一條鏈相連溅呢，而B樹的葉子節(jié)點各自獨立的。

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

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

49.? 覆蓋索引霍殴、回表等這些媒惕，了解過嗎？

覆蓋索引： 查詢列要被所建的索引覆蓋繁成，不必從數(shù)據(jù)表中讀取吓笙，換句話說查詢列要被所使用的索引覆蓋。

回表：二級索引無法直接查詢所有列的數(shù)據(jù)巾腕，所以通過二級索引查詢到聚簇索引后面睛，再查詢到想要的數(shù)據(jù)，這種通過二級索引查詢出來的過程尊搬，就叫做回表叁鉴。

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

在B+樹的索引中佛寿，葉子節(jié)點可能存儲了當(dāng)前的key值幌墓，也可能存儲了當(dāng)前的key值以及整行的數(shù)據(jù)，這就是聚簇索引和非聚簇索引冀泻。 在InnoDB中常侣，只有主鍵索引是聚簇索引，如果沒有主鍵弹渔，則挑選一個唯一鍵建立聚簇索引胳施。如果沒有唯一鍵，則隱式的生成一個鍵來建立聚簇索引肢专。

當(dāng)查詢使用聚簇索引時舞肆，在對應(yīng)的葉子節(jié)點搏恤，可以獲取到整行數(shù)據(jù)亿絮，因此不用再次進(jìn)行回表查詢交排。

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

image

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

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

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

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

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

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

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

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

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

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

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

image

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

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

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

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

共享鎖: 又叫做讀鎖捶朵。當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時，對數(shù)據(jù)加上共享鎖狂男。共享鎖可以同時加上多個综看。

排他鎖: 又叫做寫鎖。當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫入時岖食，對數(shù)據(jù)加上排他鎖红碑。排他鎖只可以加一個，他和其他的排他鎖泡垃，共享鎖都相斥析珊。

鎖兼容性如下：

image

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

基于索引來完成行鎖的蔑穴。

select*fromtwhereid=666forupdate;

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

59.? 什么是死鎖奕剃？怎么解決衷旅？

死鎖是指兩個或多個事務(wù)在同一資源上相互占用，并請求鎖定對方的資源纵朋，從而導(dǎo)致惡性循環(huán)的現(xiàn)象柿顶。看圖形象一點操软，如下：

image

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

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

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

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

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

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

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

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

為什么要使用視圖顷编？

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

什么是視圖媳纬？

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

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

視圖特點：

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

視圖是由基本表(實表)產(chǎn)生的表(虛表)抖苦。

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

對視圖內(nèi)容的更新(添加米死，刪除和修改)直接影響基本表。

當(dāng)視圖來自多個基本表時贮庞，不允許添加和刪除數(shù)據(jù)峦筒。

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

視圖的常見使用場景：

重用SQL語句；

簡化復(fù)雜的SQL操作遮斥。

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

保護(hù)數(shù)據(jù)

更改數(shù)據(jù)格式和表示。視圖可返回與底層表的表示和格式不同的數(shù)據(jù)术吗。

62.? 視圖的優(yōu)點尉辑，缺點，講一下较屿？

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

數(shù)據(jù)安全性。視圖使用戶能以多種角度看待同一數(shù)據(jù)隘蝎，能夠?qū)C密數(shù)據(jù)提供安全保護(hù)

邏輯數(shù)據(jù)獨立性购啄。視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性

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

count(*)包括了所有的列狮含，相當(dāng)于行數(shù)，在統(tǒng)計結(jié)果的時候曼振，不會忽略列值為NULL

count(1)包括了忽略所有列几迄，用1代表代碼行，在統(tǒng)計結(jié)果的時候冰评，不會忽略列值為NULL

count(列名)只包括列名那一列乓旗，在統(tǒng)計結(jié)果的時候，會忽略列值為空（這里的空不是只空字符串或者0集索，而是表示null）的計數(shù)，即某個字段值為NULL時汇跨，不統(tǒng)計务荆。

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

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

65.? 什么是存儲過程蚪黑？有哪些優(yōu)缺點盅惜？

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

優(yōu)點：

存儲過程是一個預(yù)編譯的代碼塊屈芜，執(zhí)行效率比較高

存儲過程在服務(wù)器端運行郊愧，減少客戶端的壓力

允許模塊化程序設(shè)計，只需要創(chuàng)建一次過程井佑，以后在程序中就可以調(diào)用該過程任意次属铁，類似方法的復(fù)用

一個存儲過程替代大量T_SQL語句 ，可以降低網(wǎng)絡(luò)通信量躬翁，提高通信速率

可以一定程度上確保數(shù)據(jù)安全

缺點：

調(diào)試麻煩

可移植性不靈活

重新編譯問題

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

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

使用場景：

可以通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改蝶防。

實時監(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的涵義

字段最多存放 50 個字符

如 varchar(50) 和 varchar(200) 存儲 "jay" 字符串所占空間是一樣的，后者在排序時會消耗更多內(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ū)別

deletetruncatedrop

類型DMLDDLDDL

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

刪除內(nèi)容表結(jié)構(gòu)還在搓谆，刪除表的全部或者一部分?jǐn)?shù)據(jù)行表結(jié)構(gòu)還在，刪除表中的所有數(shù)據(jù)從數(shù)據(jù)庫中刪除表豪墅，所有的數(shù)據(jù)行泉手，索引和權(quán)限也會被刪除

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

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

Union：對兩個結(jié)果集進(jìn)行并集操作斩萌，不包括重復(fù)行，同時進(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í)行計劃，執(zhí)行

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

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

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

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

image

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

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

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

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

我們平時寫Sql時鹏氧，都要養(yǎng)成用explain分析的習(xí)慣渤涌。

慢查詢的統(tǒng)計，運維會定期統(tǒng)計給我們

優(yōu)化慢查詢：

分析語句把还，是否加載了不必要的字段/數(shù)據(jù)实蓬。

分析SQl執(zhí)行句話，是否命中索引等吊履。

如果SQL很復(fù)雜安皱，優(yōu)化SQL結(jié)構(gòu)

如果表數(shù)據(jù)量太大，考慮分表

79.? 主鍵使用自增ID還是UUID艇炎，為什么酌伊？

如果是單機的話，選擇自增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)化長難的查詢語句邮弹？有實戰(zhàn)過嗎？

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

減少冗余記錄的查詢蚓聘。

一個復(fù)雜查詢可以考慮拆成多個簡單查詢

分解關(guān)聯(lián)查詢腌乡，讓緩存的效率更高。

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

平時積累吧：

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

使用count(*) 而不是count(列名)

在不影響業(yè)務(wù)的情況或粮，使用緩存

explain 分析你的SQL

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

排查過程：

使用top 命令觀察氯材，確定是mysqld導(dǎo)致還是其他原因渣锦。

如果是mysqld導(dǎo)致的，show processlist氢哮，查看session情況袋毙，確定是不是有消耗資源的sql在運行。

找出消耗高的 sql冗尤，看看執(zhí)行計劃是否準(zhǔn)確听盖， 索引是否缺失，數(shù)據(jù)量是否太大裂七。

處理：

kill 掉這些線程(同時觀察 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ā)起連接觉至，連接到主庫剔应。

步驟三：此時主庫創(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 的時間范圍是 1970——2038 年

DATETIME 存儲時間與時區(qū)無關(guān)船万；TIMESTAMP 存儲時間與時區(qū)有關(guān)，顯示的值也依賴于時區(qū)

DATETIME 的存儲空間為 8 字節(jié)骨田；TIMESTAMP 的存儲空間為 4 字節(jié)

DATETIME 的默認(rèn)值為 null耿导；TIMESTAMP 的字段默認(rèn)不為空(not null)，默認(rèn)值為當(dāng)前時間(CURRENT_TIMESTAMP)

90.? Innodb的事務(wù)實現(xiàn)原理态贤？

原子性：是使用 undo log來實現(xiàn)的舱呻，如果事務(wù)執(zhí)行過程中出錯或者用戶執(zhí)行了rollback，系統(tǒng)通過undo log日志返回事務(wù)開始的狀態(tài)抵卫。

持久性：使用 redo log來實現(xiàn)狮荔，只要redo log日志持久化了，當(dāng)系統(tǒng)崩潰介粘，即可通過redo log把數(shù)據(jù)恢復(fù)殖氏。

隔離性：通過鎖以及MVCC,使事務(wù)相互隔離開。

一致性：通過回滾姻采、恢復(fù)雅采，以及并發(fā)情況下的隔離性，從而實現(xiàn)一致性。

91. 談?wù)凪ySQL的Explain

Explain 執(zhí)行計劃包含字段信息如下：分別是 id婚瓜、select_type宝鼓、table、partitions巴刻、type愚铡、possible_keys、key胡陪、key_len沥寥、ref、rows柠座、filtered邑雅、Extra 等12個字段。

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

system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

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

有多少種日志

innodb兩種日志redo和undo淮野。

日志的存放形式

redo：在頁修改的時候，先寫到 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ù)是如何通過日志來實現(xiàn)的

因為事務(wù)在修改頁時廊营，要先記 undo，在記 undo 之前要記 undo 的 redo萝勤， 然后修改數(shù)據(jù)頁露筒，再記數(shù)據(jù)頁修改的 redo。 Redo（里面包括 undo 的修改） 一定要比數(shù)據(jù)頁先持久化到磁盤敌卓。

當(dāng)事務(wù)需要回滾時慎式，因為有 undo，可以把數(shù)據(jù)頁回滾到前鏡像的 狀態(tài)趟径，崩潰恢復(fù)時瘪吏，如果 redo log 中事務(wù)沒有對應(yīng)的 commit 記錄，那么需要用 undo把該事務(wù)的修改回滾到事務(wù)開始之前蜗巧。

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

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幕屹，在最快時間之內(nèi)重啟蓝丙。

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

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

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

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

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

主從一致性校驗有多種工具 例如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隔離級別