MySQL數(shù)據(jù)庫(kù)經(jīng)典面試題解析(收藏版)一定要收藏砚哆!

前言

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

github.com/whx123/Java…

數(shù)據(jù)庫(kù)

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

可以從三個(gè)維度回答這個(gè)問(wèn)題:索引哪些情況會(huì)失效,索引不適合哪些場(chǎng)景疾层,索引規(guī)則

索引哪些情況會(huì)失效

  • 查詢條件包含or将饺,可能導(dǎo)致索引失效
  • 如何字段類型是字符串,where時(shí)一定用引號(hào)括起來(lái)痛黎,否則索引失效
  • like通配符可能導(dǎo)致索引失效予弧。
  • 聯(lián)合索引,查詢時(shí)的條件列不是聯(lián)合索引中的第一個(gè)列湖饱,索引失效掖蛤。
  • 在索引列上使用mysql的內(nèi)置函數(shù),索引失效井厌。
  • 對(duì)索引列運(yùn)算(如蚓庭,+、-旗笔、*彪置、/),索引失效蝇恶。
  • 索引字段上使用(拳魁!= 或者 < >,not in)時(shí)撮弧,可能會(huì)導(dǎo)致索引失效潘懊。
  • 索引字段上使用is null姚糊, is not null,可能導(dǎo)致索引失效授舟。
  • 左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣救恨,可能導(dǎo)致索引失效。
  • mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引释树。

索引不適合哪些場(chǎng)景

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

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

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

2. MySQL 遇到過(guò)死鎖問(wèn)題嗎肠槽,你是如何解決的?

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

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

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

可以從這幾個(gè)維度回答這個(gè)問(wèn)題:

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

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

分庫(kù)分表方案秸仙,分庫(kù)分表中間件,分庫(kù)分表可能遇到的問(wèn)題

分庫(kù)分表方案:

  • 水平分庫(kù):以字段為依據(jù)桩盲,按照一定策略(hash寂纪、range等),將一個(gè)庫(kù)中的數(shù)據(jù)拆分到多個(gè)庫(kù)中赌结。
  • 水平分表:以字段為依據(jù)捞蛋,按照一定策略(hash、range等)柬姚,將一個(gè)表中的數(shù)據(jù)拆分到多個(gè)表中拟杉。
  • 垂直分庫(kù):以表為依據(jù),按照業(yè)務(wù)歸屬不同伤靠,將不同的表拆分到不同的庫(kù)中捣域。
  • 垂直分表:以字段為依據(jù),按照字段的活躍性宴合,將表中字段拆到不同的表(主表和擴(kuò)展表)中焕梅。

常用的分庫(kù)分表中間件:

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

分庫(kù)分表可能遇到的問(wèn)題

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

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

  • InnoDB支持事務(wù),MyISAM不支持事務(wù)
  • InnoDB支持外鍵蚤霞,MyISAM不支持外鍵
  • InnoDB 支持 MVCC(多版本并發(fā)控制)酗失,MyISAM 不支持
  • select count(*) from table時(shí),MyISAM更快昧绣,因?yàn)樗幸粋€(gè)變量保存了整個(gè)表的總行數(shù)规肴,可以直接讀取,InnoDB就需要全表掃描。
  • Innodb不支持全文索引拖刃,而MyISAM支持全文索引(5.7以后的InnoDB也支持全文索引)
  • InnoDB支持表删壮、行級(jí)鎖,而MyISAM支持表級(jí)鎖兑牡。
  • InnoDB表必須有主鍵央碟,而MyISAM可以沒(méi)有主鍵
  • Innodb表需要更多的內(nèi)存和存儲(chǔ),而MyISAM可被壓縮均函,存儲(chǔ)空間較小亿虽,。
  • Innodb按主鍵大小有序插入苞也,MyISAM記錄插入順序是经柴,按記錄插入順序保存。
  • InnoDB 存儲(chǔ)引擎提供了具有提交墩朦、回滾、崩潰恢復(fù)能力的事務(wù)安全翻擒,與 MyISAM 比 InnoDB 寫(xiě)的效率差一些氓涣,并且會(huì)占用更多的磁盤(pán)空間以保留數(shù)據(jù)和索引

6. 數(shù)據(jù)庫(kù)索引的原理,為什么要用 B+樹(shù)陋气,為什么不用二叉樹(shù)劳吠?

可以從幾個(gè)維度去看這個(gè)問(wèn)題,查詢是否夠快巩趁,效率是否穩(wěn)定宠进,存儲(chǔ)數(shù)據(jù)多少媚狰,以及查找磁盤(pán)次數(shù),為什么不是二叉樹(shù),為什么不是平衡二叉樹(shù)整吆,為什么不是B樹(shù),而偏偏是B+樹(shù)呢芍锚?

為什么不是一般二叉樹(shù)逝嚎?

如果二叉樹(shù)特殊化為一個(gè)鏈表,相當(dāng)于全表掃描炉菲。平衡二叉樹(shù)相比于二叉查找樹(shù)來(lái)說(shuō)堕战,查找效率更穩(wěn)定,總體的查找速度也更快拍霜。

為什么不是平衡二叉樹(shù)呢嘱丢?

我們知道,在內(nèi)存比在磁盤(pán)的數(shù)據(jù)祠饺,查詢效率快得多越驻。如果樹(shù)這種數(shù)據(jù)結(jié)構(gòu)作為索引,那我們每查找一次數(shù)據(jù)就需要從磁盤(pán)中讀取一個(gè)節(jié)點(diǎn),也就是我們說(shuō)的一個(gè)磁盤(pán)塊伐谈,但是平衡二叉樹(shù)可是每個(gè)節(jié)點(diǎn)只存儲(chǔ)一個(gè)鍵值和數(shù)據(jù)的烂完,如果是B樹(shù),可以存儲(chǔ)更多的節(jié)點(diǎn)數(shù)據(jù)诵棵,樹(shù)的高度也會(huì)降低抠蚣,因此讀取磁盤(pán)的次數(shù)就降下來(lái)啦,查詢效率就快啦履澳。

那為什么不是B樹(shù)而是B+樹(shù)呢嘶窄?

1)B+樹(shù)非葉子節(jié)點(diǎn)上是不存儲(chǔ)數(shù)據(jù)的,僅存儲(chǔ)鍵值距贷,而B(niǎo)樹(shù)節(jié)點(diǎn)中不僅存儲(chǔ)鍵值柄冲,也會(huì)存儲(chǔ)數(shù)據(jù)。innodb中頁(yè)的默認(rèn)大小是16KB忠蝗,如果不存儲(chǔ)數(shù)據(jù)现横,那么就會(huì)存儲(chǔ)更多的鍵值,相應(yīng)的樹(shù)的階數(shù)(節(jié)點(diǎn)的子節(jié)點(diǎn)樹(shù))就會(huì)更大阁最,樹(shù)就會(huì)更矮更胖戒祠,如此一來(lái)我們查找數(shù)據(jù)進(jìn)行磁盤(pán)的IO次數(shù)有會(huì)再次減少,數(shù)據(jù)查詢的效率也會(huì)更快速种。

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

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

  • 一個(gè)表中只能擁有一個(gè)聚集索引救拉,而非聚集索引一個(gè)表可以存在多個(gè)。
  • 聚集索引舍沙,索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序近上;非聚集索引,索引中索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同拂铡。
  • 索引是通過(guò)二叉樹(shù)的數(shù)據(jù)結(jié)構(gòu)來(lái)描述的壹无,我們可以這么理解聚簇索引:索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。而非聚簇索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)感帅,只不過(guò)有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊斗锭。
  • 聚集索引:物理存儲(chǔ)按照索引排序;非聚集索引:物理存儲(chǔ)不按照索引排序失球;

何時(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ù)允許的情況下限制頁(yè)數(shù):

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

方案三: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)化超多分頁(yè)場(chǎng)景音瓷。(先快速定位需要獲取的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ù)庫(kù)自增長(zhǎng)序列或字段绳慎。
  • UUID纵竖。
  • Redis生成ID
  • Twitter的snowflake算法
  • 利用zookeeper生成唯一ID
  • MongoDB的ObjectId

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

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

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

11. 什么是幻讀磨确,臟讀,不可重復(fù)讀呢声邦?

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

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

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

使用悲觀鎖

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

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

以上這條sql語(yǔ)句會(huì)鎖定了User表中所有符合檢索條件(name=‘jay’)的記錄宗收。本次事務(wù)提交之前,別的線程都無(wú)法修改這些記錄亚兄。

使用樂(lè)觀鎖

樂(lè)觀鎖思想就是混稽,有線程過(guò)來(lái),先放過(guò)去修改儿捧,如果看到別的線程沒(méi)修改過(guò)荚坞,就可以修改成功,如果別的線程修改過(guò)菲盾,就修改失敗或者重試颓影。實(shí)現(xiàn)方式:樂(lè)觀鎖一般會(huì)使用版本號(hào)機(jī)制或CAS算法實(shí)現(xiàn)。

13. 數(shù)據(jù)庫(kù)的樂(lè)觀鎖和悲觀鎖懒鉴。

悲觀鎖:

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

image

樂(lè)觀<typo id="typo-4031" data-origin="鎖" ignoretag="true">鎖</typo>:

樂(lè)觀鎖的“樂(lè)觀情緒”體現(xiàn)在城豁,它認(rèn)為數(shù)據(jù)的變動(dòng)不會(huì)太頻繁。因此抄课,它允許多個(gè)事務(wù)同時(shí)對(duì)數(shù)據(jù)進(jìn)行變動(dòng)唱星。實(shí)現(xiàn)方式:樂(lè)觀鎖一般會(huì)使用版本號(hào)機(jī)制或CAS算法實(shí)現(xiàn)。

image

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

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

15. select for update有什么含義,會(huì)鎖表還是鎖行還是其他僵蛛。

select for update 含義

select查詢語(yǔ)句是不會(huì)加鎖的尚蝌,但是select for update除了有查詢的作用外,還會(huì)加鎖呢充尉,而且它是悲觀鎖哦驼壶。至于加了是行鎖還是表鎖,這就要看是不是用了索引/主鍵啦喉酌。

沒(méi)用索引/主鍵的話就是表鎖热凹,否則就是是行鎖泵喘。

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í)纪铺,再開(kāi)一個(gè)事務(wù)對(duì)該記錄更新,發(fā)現(xiàn)更新阻塞啦碟渺,其實(shí)是加鎖了鲜锚。如下圖:

image

我們?cè)匍_(kāi)一個(gè)事務(wù)對(duì)另外一條記錄1270071更新,發(fā)現(xiàn)更新成功苫拍,因此芜繁,如果查詢條件用了索引/主鍵,會(huì)加行鎖~

image

我們繼續(xù)一路向北吧绒极,換普通字段balance吧骏令,發(fā)現(xiàn)又阻塞了。因此垄提,沒(méi)用索引/主鍵的話榔袋,select for update加的就是表鎖

image

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

image
  • 原子性: 事務(wù)作為一個(gè)整體被執(zhí)行,包含在其中的對(duì)數(shù)據(jù)庫(kù)的操作要么全部被執(zhí)行铡俐,要么都不執(zhí)行凰兑。
  • 一致性: 指在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后,數(shù)據(jù)不會(huì)被破壞审丘,假如A賬戶給B賬戶轉(zhuǎn)10塊錢(qián)吏够,不管成功與否,A和B的總金額是不變的滩报。
  • 隔離性: 多個(gè)事務(wù)并發(fā)訪問(wèn)時(shí)稿饰,事務(wù)之間是相互隔離的,即一個(gè)事務(wù)不影響其它事務(wù)運(yùn)行效果露泊。簡(jiǎn)言之,就是事務(wù)之間是進(jìn)水不犯河水的旅择。
  • 持久性: 表示事務(wù)完成以后惭笑,該事務(wù)對(duì)數(shù)據(jù)庫(kù)所作的操作更改,將持久地保存在數(shù)據(jù)庫(kù)之中生真。

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

  • 原子性:是使用 undo log來(lái)實(shí)現(xiàn)的沉噩,如果事務(wù)執(zhí)行過(guò)程中出錯(cuò)或者用戶執(zhí)行了rollback,系統(tǒng)通過(guò)undo log日志返回事務(wù)開(kāi)始的狀態(tài)柱蟀。
  • 持久性:使用 redo log來(lái)實(shí)現(xiàn)川蒙,只要redo log日志持久化了,當(dāng)系統(tǒng)崩潰长已,即可通過(guò)redo log把數(shù)據(jù)恢復(fù)畜眨。
  • 隔離性:通過(guò)鎖以及MVCC,使事務(wù)相互隔離開(kāi)昼牛。
  • 一致性:通過(guò)回滾、恢復(fù)康聂,以及并發(fā)情況下的隔離性贰健,從而實(shí)現(xiàn)一致性。

17. 如果某個(gè)表有近千萬(wàn)數(shù)據(jù)恬汁,CRUD比較慢伶椿,如何優(yōu)化。

分庫(kù)分表

某個(gè)表有近千萬(wàn)數(shù)據(jù)氓侧,可以考慮優(yōu)化表結(jié)構(gòu)脊另,分表(水平分表,垂直分表)约巷,當(dāng)然偎痛,你這樣回答,需要準(zhǔn)備好面試官問(wèn)你的分庫(kù)分表相關(guān)問(wèn)題呀载庭,如

  • 分表方案(水平分表看彼,垂直分表,切分規(guī)則hash等)
  • 分庫(kù)分表中間件(Mycat囚聚,sharding-jdbc等)
  • 分庫(kù)分表一些問(wèn)題(事務(wù)問(wèn)題靖榕?跨節(jié)點(diǎn)Join的問(wèn)題)
  • 解決方案(分布式事務(wù)等)

索引優(yōu)化

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

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

復(fù)合索引,也叫組合索引谓松,用戶可以在多個(gè)列上建立索引,這種索引叫做復(fù)合索引星压。

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

<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ū)別。

這個(gè)避乏,跟一下demo來(lái)看更刺激吧爷耀,啊哈哈

假設(shè)表A表示某企業(yè)的員工表,表B表示部門(mén)表拍皮,查詢所有部門(mén)的所有員工歹叮,很容易有以下SQL:

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

這樣寫(xiě)等價(jià)于:

先查詢部門(mén)表B

select deptId from B

再由部門(mén)deptId跑杭,查詢A的員工

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

可以抽象成這樣的一個(gè)循環(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)屑迂,來(lái)決定主查詢的數(shù)據(jù)結(jié)果是否得意保留浸策。

那么,這樣寫(xiě)就等價(jià)于:

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

select * from B where A.deptId = B.deptId,再?gòu)腂表做循環(huán).

同理惹盼,可以抽象成這樣一個(gè)循環(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ù)庫(kù)最費(fèi)勁的就是跟程序鏈接釋放庸汗。假設(shè)鏈接了兩次,每次做上百萬(wàn)次的數(shù)據(jù)集查詢手报,查完就走蚯舱,這樣就只做了兩次;相反建立了上百萬(wàn)次鏈接掩蛤,申請(qǐng)鏈接釋放反復(fù)重復(fù)枉昏,這樣系統(tǒng)就受不了了。即mysql優(yōu)化原則揍鸟,就是小表驅(qū)動(dòng)大表兄裂,小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集,從而讓性能更優(yōu)阳藻。

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

20. 數(shù)據(jù)庫(kù)自增主鍵可能遇到什么問(wèn)題冒萄。

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

21. MVCC熟悉嗎崖技,它的底層原理逻住?

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

MVCC需要關(guān)注這幾個(gè)知識(shí)點(diǎn):

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

22. 數(shù)據(jù)庫(kù)中間件了解過(guò)嗎,sharding jdbc吁恍,mycat?

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

23. MYSQL的主從延遲尼啡,你怎么解決?

嘻嘻询微,先復(fù)習(xí)一下主從復(fù)制原理吧崖瞭,如圖:

image

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

  • 步驟一:主庫(kù)的更新事件(update、insert拓提、delete)被寫(xiě)到binlog
  • 步驟二:從庫(kù)發(fā)起連接读恃,連接到主庫(kù)。
  • 步驟三:此時(shí)主庫(kù)創(chuàng)建一個(gè)binlog dump thread代态,把binlog的內(nèi)容發(fā)送到從庫(kù)寺惫。
  • 步驟四:從庫(kù)啟動(dòng)之后,創(chuàng)建一個(gè)I/O線程蹦疑,讀取主庫(kù)傳過(guò)來(lái)的binlog內(nèi)容并寫(xiě)入到relay log
  • 步驟五:還會(huì)創(chuàng)建一個(gè)SQL線程西雀,從relay log里面讀取內(nèi)容,從Exec_Master_Log_Pos位置開(kāi)始執(zhí)行讀取到的更新事件歉摧,將更新內(nèi)容寫(xiě)入到slave的db

主從同步延遲的原因

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

主從同步延遲的解決辦法

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

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

  • 優(yōu)化shema、sql語(yǔ)句+索引黄痪;
  • 可以考慮加緩存紧帕,memcached, redis,或者JVM本地緩存桅打;
  • 主從復(fù)制是嗜,讀寫(xiě)分離;
  • 分庫(kù)分表挺尾;

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

連接池基本原理: 數(shù)據(jù)庫(kù)連接池原理:在內(nèi)部對(duì)象池中鹅搪,維護(hù)一定數(shù)量的數(shù)據(jù)庫(kù)連接,并對(duì)外暴露數(shù)據(jù)庫(kù)連接的獲取和返回方法遭铺。

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

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

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

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

26. 一條SQL語(yǔ)句在MySQL中如何執(zhí)行的涂召?

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

image

查詢語(yǔ)句:

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

27. InnoDB引擎中的索引策略,了解過(guò)嗎既绩?

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

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

28. 數(shù)據(jù)庫(kù)存儲(chǔ)日期格式時(shí)救欧,如何考慮時(shí)區(qū)轉(zhuǎn)換問(wèn)題衰粹?

  • datetime類型適合用來(lái)記錄數(shù)據(jù)的原始的創(chuàng)建時(shí)間,修改記錄中其他字段的值笆怠,datetime字段的值不會(huì)改變铝耻,除非手動(dòng)修改它。
  • timestamp類型適合用來(lái)記錄數(shù)據(jù)的最后修改時(shí)間蹬刷,只要修改了記錄中其他字段的值瓢捉,timestamp字段的值都會(huì)被自動(dòng)更新。

如何考慮時(shí)區(qū)轉(zhuǎn)換問(wèn)題/看一下這個(gè)吧: 數(shù)據(jù)庫(kù)存儲(chǔ)日期格式時(shí)办成,如何考慮時(shí)區(qū)轉(zhuǎn)換問(wèn)題泡态?

29. 一條sql執(zhí)行過(guò)長(zhǎng)的時(shí)間,你如何優(yōu)化迂卢,從哪些方面入手某弦?

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

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

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

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

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

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

Handler_*存儲(chǔ)引擎操作。

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

Sort_*幾種排序信息蚁滋。

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

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

  • Blob用于存儲(chǔ)二進(jìn)制數(shù)據(jù),而Text用于存儲(chǔ)大字符串辕录。
  • Blob值被視為二進(jìn)制字符串(字節(jié)字符串),它們沒(méi)有字符集睦霎,并且排序和比較基于列值中的字節(jié)的數(shù)值。
  • text值被視為非二進(jìn)制字符串(字符字符串)走诞。它們有一個(gè)字符集副女,并根據(jù)字符集的排序規(guī)則對(duì)值進(jìn)行排序和比較。

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

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

33. Mysql中有哪幾種鎖,列舉一下笋妥?

image

如果按鎖粒度劃分懊昨,有以下3種:

  • 表鎖: 開(kāi)銷小,加鎖快春宣;鎖定力度大酵颁,發(fā)生鎖沖突概率高,并發(fā)度最低;不會(huì)出現(xiàn)死鎖月帝。
  • 行鎖: 開(kāi)銷大躏惋,加鎖慢;會(huì)出現(xiàn)死鎖嚷辅;鎖定粒度小簿姨,發(fā)生鎖沖突的概率低,并發(fā)度高簸搞。
  • 頁(yè)鎖: <typo id="typo-10199" data-origin="開(kāi)銷" ignoretag="true">開(kāi)銷</typo>和加鎖速度介于表鎖和行鎖之間扁位;會(huì)出現(xiàn)死鎖;鎖定粒度介于表鎖和行鎖之間趁俊,并發(fā)度一般

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

  • B+樹(shù)可以進(jìn)行范圍查詢寺擂,Hash索引不能暇务。
  • B+樹(shù)支持聯(lián)合索引的最左側(cè)原則泼掠,Hash索引不支持。
  • B+樹(shù)支持order by排序垦细,Hash索引不支持择镇。
  • Hash索引在等值查詢上比B+樹(shù)效率更高。
  • B+樹(shù)使用like 進(jìn)行模糊查詢的時(shí)候括改,like后面(比如%開(kāi)頭)的話可以起到優(yōu)化的作用沐鼠,Hash索引根本無(wú)法進(jìn)行模糊查詢。

35. mysql 的內(nèi)連接叹谁、左連接、右連接有什么區(qū)別乘盖?

  • Inner join 內(nèi)連接焰檩,在兩張表進(jìn)行連接查詢時(shí),只保留兩張表中完全匹配的結(jié)果集
  • left join 在兩張表進(jìn)行連接查詢時(shí)订框,會(huì)返回左表所有的行析苫,即使在右表中沒(méi)有匹配的記錄。
  • right join 在兩張表進(jìn)行連接查詢時(shí)穿扳,會(huì)返回右表所有的行衩侥,即使在左表中沒(méi)有匹配的記錄。

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

image

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

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

37. 什么是內(nèi)連接履羞、外連接峦萎、交叉連接、笛卡爾積呢忆首?

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

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

  • 第一范式:數(shù)據(jù)表中的每一列(每個(gè)字段)都不可以再拆分唇聘。
  • 第二范式:在第一范式的基礎(chǔ)上,分主鍵列完全依賴于主鍵柱搜,而不能是依賴于主鍵的一部分雳灾。
  • 第三范式:在滿足第二范式的基礎(chǔ)上,表中的非主鍵只依賴于主鍵冯凹,而不依賴于其他非主鍵谎亩。

39. mysql有關(guān)權(quán)限的表有哪幾個(gè)呢炒嘲?

MySQL服務(wù)器通過(guò)權(quán)限表來(lái)控制用戶對(duì)數(shù)據(jù)庫(kù)的訪問(wèn),權(quán)限表存放在mysql數(shù)據(jù)庫(kù)里匈庭,由mysql_install_db腳本初始化夫凸。這些權(quán)限表分別user,db阱持,table_priv夭拌,columns_priv和host。

  • user權(quán)限表:記錄允許連接到服務(wù)器的用戶帳號(hào)信息衷咽,里面的權(quán)限是全局級(jí)的鸽扁。
  • db權(quán)限表:記錄各個(gè)帳號(hào)在各個(gè)數(shù)據(jù)庫(kù)上的操作權(quán)限。
  • table_priv權(quán)限表:記錄數(shù)據(jù)表級(jí)的操作權(quán)限镶骗。
  • columns_priv權(quán)限表:記錄數(shù)據(jù)列級(jí)的操作權(quán)限桶现。
  • host權(quán)限表:配合db權(quán)限表對(duì)給定主機(jī)上數(shù)據(jù)庫(kù)級(jí)操作權(quán)限作更細(xì)致的控制。這個(gè)權(quán)限表不受GRANT和REVOKE語(yǔ)句的影響鼎姊。

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

有三種格式哈相寇,statement慰于,row和mixed。

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

row楣导,不記錄sql語(yǔ)句上下文相關(guān)信息废境,僅保存哪條記錄被修改。記錄單元為每一行的改動(dòng)筒繁,基本是可以全部記下來(lái)但是由于很多操作噩凹,會(huì)導(dǎo)致大量行的改動(dòng)(比如alter table),因此這種模式的文件保存的信息太多毡咏,日志量太大驮宴。

mixed,一種折中的方案呕缭,普通操作使用statement記錄堵泽,當(dāng)無(wú)法使用statement的時(shí)候使用row修己。

41. InnoDB引擎的4大特性,了解過(guò)嗎

  • 插入緩沖(insert buffer)
  • 二次寫(xiě)(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ù)庫(kù)表中每一行的數(shù)據(jù)的唯一性
  • 索引可以加快數(shù)據(jù)查詢速度睬愤,減少查詢時(shí)間

缺點(diǎn):

  • 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間
  • 索引需要占物理空間,除了數(shù)據(jù)表占用數(shù)據(jù)空間之外纹安,每一個(gè)索引還要占用一定的物理空間
  • 以表中的數(shù)據(jù)進(jìn)行增尤辱、刪、改的時(shí)候厢岂,索引也要?jiǎng)討B(tài)的維護(hù)光督。

43. 索引有哪幾種類型?

  • 主鍵索引: 數(shù)據(jù)列不允許重復(fù)塔粒,不允許為NULL结借,一個(gè)表只能有一個(gè)主鍵。
  • 唯一索引: 數(shù)據(jù)列不允許重復(fù)窗怒,允許為NULL值,一個(gè)表允許多個(gè)列創(chuàng)建唯一索引蓄拣。
  • 普通索引: 基本的索引類型扬虚,沒(méi)有唯一性的限制,允許為NULL值球恤。
  • 全文索引:是目前搜索引擎使用的一種關(guān)鍵技術(shù)辜昵,對(duì)文本的內(nèi)容進(jìn)行分詞、搜索咽斧。
  • 覆蓋索引:查詢列要被所建的索引覆蓋堪置,不必讀取數(shù)據(jù)行
  • 組合索引:多列值組成一個(gè)索引,用于組合搜索张惹,效率大于索引合并

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

  • 最左前綴匹配原則
  • 頻繁作為查詢條件的字段才去創(chuàng)建索引
  • 頻繁更新的字段不適合創(chuàng)建索引
  • 索引列不能參與計(jì)算,不能有函數(shù)操作
  • 優(yōu)先考慮擴(kuò)展索引宛逗,而不是新建索引坎匿,避免不必要的索引
  • 在order by或者group by子句中,創(chuàng)建索引需要注意順序
  • 區(qū)分度低的數(shù)據(jù)列不適合做索引列(如性別)
  • 定義有外鍵的數(shù)據(jù)列一定要建立索引雷激。
  • 對(duì)于定義為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. 百萬(wàn)級(jí)別或以上的數(shù)據(jù)屎暇,你是如何刪除的承桥?

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

47. 什么是最左前綴原則根悼?什么是最左匹配原則凶异?

最左前綴原則蜀撑,就是最左優(yōu)先,在創(chuàng)建多列索引時(shí)唠帝,要根據(jù)業(yè)務(wù)需求屯掖,where子句中使用最頻繁的一列放在最左邊。當(dāng)我們創(chuàng)建一個(gè)組合索引的時(shí)候襟衰,如(k1,k2,k3)贴铜,相當(dāng)于創(chuàng)建了(k1)瀑晒、(k1,k2)和(k1,k2,k3)三個(gè)索引苔悦,這就是最左匹配原則轩褐。把介。

48. B樹(shù)和B+樹(shù)的區(qū)別,數(shù)據(jù)庫(kù)為什么使用B+樹(shù)而不是B樹(shù)蟋座?

  • 在B樹(shù)中向臀,鍵和值即存放在內(nèi)部節(jié)點(diǎn)又存放在葉子節(jié)點(diǎn)券膀;在B+樹(shù)中芹彬,內(nèi)部節(jié)點(diǎn)只存鍵舒帮,葉子節(jié)點(diǎn)則同時(shí)存放鍵和值兵迅。
  • B+樹(shù)的葉子節(jié)點(diǎn)有一條鏈相連矫限,而B(niǎo)樹(shù)的葉子節(jié)點(diǎn)各自獨(dú)立的临庇。

B+樹(shù)索引的所有數(shù)據(jù)均存儲(chǔ)在葉子節(jié)點(diǎn)淮蜈,而且數(shù)據(jù)是按照順序排列的梧田,鏈表連著的裁眯。那么B+樹(shù)使得范圍查找穿稳,排序查找旦袋,分組查找以及去重查找變得異常簡(jiǎn)單疤孕。.B+樹(shù)非葉子節(jié)點(diǎn)上是不存儲(chǔ)數(shù)據(jù)的胰柑,僅存儲(chǔ)鍵值崩瓤,而B(niǎo)樹(shù)節(jié)點(diǎn)中不僅存儲(chǔ)鍵值却桶,也會(huì)存儲(chǔ)數(shù)據(jù)嗅剖。innodb中頁(yè)的默認(rèn)大小是16KB信粮,如果不存儲(chǔ)數(shù)據(jù)强缘,那么就會(huì)存儲(chǔ)更多的鍵值赏胚,相應(yīng)的樹(shù)的階數(shù)(節(jié)點(diǎn)的子節(jié)點(diǎn)樹(shù))就會(huì)更大觉阅,樹(shù)就會(huì)更矮更胖,如此一來(lái)我們查找數(shù)據(jù)進(jìn)行磁盤(pán)的IO次數(shù)有會(huì)再次減少痴柔,數(shù)據(jù)查詢的效率也會(huì)更快.

49. 覆蓋索引咳蔚、回表等這些,了解過(guò)嗎糯耍?

  • 覆蓋索引: 查詢列要被所建的索引覆蓋,不必從數(shù)據(jù)表中讀取舵鳞,換句話說(shuō)查詢列要被所使用的索引覆蓋蜓堕。
  • 回表:二級(jí)索引無(wú)法直接查詢所有列的數(shù)據(jù),所以通過(guò)二級(jí)索引查詢到聚簇索引后背伴,再查詢到想要的數(shù)據(jù)以清,這種通過(guò)二級(jí)索引查詢出來(lái)的過(guò)程掷倔,就叫做回表。

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

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

在B+樹(shù)的索引中,葉子節(jié)點(diǎn)可能存儲(chǔ)了當(dāng)前的key值至非,也可能存儲(chǔ)了當(dāng)前的key值以及整行的數(shù)據(jù)荒椭,這就是聚簇索引和非聚簇索引。 在InnoDB中味悄,只有主鍵索引是聚簇索引猾瘸,如果沒(méi)有主鍵牵触,則挑選一個(gè)唯一鍵建立聚簇索引袜腥。如果沒(méi)有唯一鍵羹令,則隱式的生成一個(gè)鍵來(lái)建立聚簇索引酒来。當(dāng)查詢使用聚簇索引時(shí),在對(duì)應(yīng)的葉子節(jié)點(diǎn)翘鸭,可以獲取到整行數(shù)據(jù),因此不用再次進(jìn)行回表查詢档址。

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

image

52. 非聚簇索引一定會(huì)回表查詢嗎?

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

舉個(gè)簡(jiǎn)單的例子废登,假設(shè)我們?cè)趯W(xué)生表的上建立了索引,那么當(dāng)進(jìn)行select age from student where age < 20的查詢時(shí)羽戒,在索引的葉子節(jié)點(diǎn)上疚俱,已經(jīng)包含了age信息,不會(huì)再次進(jìn)行回表查詢梁钾。

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

組合索引,用戶可以在多個(gè)列上建立索引,這種索引叫做組合索引瓣赂。

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

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

數(shù)據(jù)庫(kù)事務(wù)(簡(jiǎn)稱:事務(wù))操禀,是數(shù)據(jù)庫(kù)管理系統(tǒng)執(zhí)行過(guò)程中的一個(gè)邏輯單位斤寂,由一個(gè)有限的數(shù)據(jù)庫(kù)操作序列構(gòu)成遍搞,這些操作要么全部執(zhí)行,要么全部不執(zhí)行钩杰,是一個(gè)不可分割的工作單位。

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

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

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

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

image
  • 按鎖粒度分有:表鎖枪向,頁(yè)鎖秘蛔,行鎖
  • 按鎖機(jī)制分有:樂(lè)觀鎖负蠕,悲觀鎖

57. 從鎖的類別角度講绣的,MySQL都有哪些鎖呢?

從鎖的類別上來(lái)講盼理,有共享鎖和排他鎖。

  • 共享鎖: 又叫做讀鎖。當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時(shí)抓艳,對(duì)數(shù)據(jù)加上共享鎖。共享鎖可以同時(shí)加上多個(gè)。
  • 排他鎖: 又叫做寫(xiě)鎖位他。當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫(xiě)入時(shí),對(duì)數(shù)據(jù)加上排他鎖。排他鎖只可以加一個(gè)醒串,他和其他的排他鎖育苟,共享鎖都相斥。

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

image

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

基于索引來(lái)完成行鎖的。

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

for update 可以根據(jù)條件來(lái)完成行鎖鎖定躺率,并且 id 是有索引鍵的列良狈,如果 id 不是索引鍵那么InnoDB將實(shí)行表鎖薪丁。

59. 什么是死鎖?怎么解決梧税?

死鎖是指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用,并請(qǐng)求鎖定對(duì)方的資源,從而導(dǎo)致惡性循環(huán)的現(xiàn)象尸执“砘澹看圖形象一點(diǎn)脆丁,如下:

image

死鎖有四個(gè)必要條件:互斥條件跟压,請(qǐng)求和保持條件,環(huán)路等待條件躲庄,不剝奪條件查剖。

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

如果不同程序會(huì)并發(fā)存取多個(gè)表效览,盡量約定以相同的順序訪問(wèn)表无切,可以大大降低死鎖機(jī)會(huì)。

在同一個(gè)事務(wù)中丐枉,盡可能做到一次鎖定所需要的所有資源哆键,減少死鎖產(chǎn)生概率;

對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分瘦锹,可以嘗試使用升級(jí)鎖定顆粒度籍嘹,通過(guò)表級(jí)鎖定來(lái)減少死鎖產(chǎn)生的概率;

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

死鎖與索引密不可分弯院,解決索引問(wèn)題辱士,需要合理優(yōu)化你的索引,

有興趣的朋友听绳,可以看我的這篇死鎖分析: 手把手教你分析Mysql死鎖問(wèn)題

60. 為什么要使用視圖颂碘?什么是視圖?

為什么要使用視圖椅挣?

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

什么是視圖?

視圖是一個(gè)虛擬的表峡竣,是一個(gè)表中的數(shù)據(jù)經(jīng)過(guò)某種篩選后的顯示方式靠抑,視圖由一個(gè)預(yù)定義的查詢select語(yǔ)句組成。

61. 視圖有哪些特點(diǎn)适掰?哪些使用場(chǎng)景颂碧?

視圖特點(diǎn):

  • 視圖的列可以來(lái)自不同的表,是表的抽象和在邏輯意義上建立的新關(guān)系攻谁。
  • 視圖是由基本表(實(shí)表)產(chǎn)生的表(虛表)稚伍。
  • 視圖的建立和刪除不影響基本表弯予。
  • 對(duì)視圖內(nèi)容的更新(添加戚宦,刪除和修改)直接影響基本表。
  • 當(dāng)視圖來(lái)自多個(gè)基本表時(shí)锈嫩,不允許添加和刪除數(shù)據(jù)受楼。

視圖用途: 簡(jiǎn)化sql查詢,提高開(kāi)發(fā)效率呼寸,兼容老的表結(jié)構(gòu)艳汽。

視圖的常見(jiàn)使用場(chǎng)景:

  • 重用SQL語(yǔ)句;
  • 簡(jiǎn)化復(fù)雜的SQL操作对雪。
  • 使用表的組成部分而不是整個(gè)表河狐;
  • 保護(hù)數(shù)據(jù)
  • 更改數(shù)據(jù)格式和表示。視圖可返回與底層表的表示和格式不同的數(shù)據(jù)瑟捣。

62. 視圖的優(yōu)點(diǎn)馋艺,缺點(diǎn),講一下迈套?

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

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

  • count(*)包括了所有的列率拒,相當(dāng)于行數(shù),在統(tǒng)計(jì)結(jié)果的時(shí)候禁荒,不會(huì)忽略列值為NULL
  • count(1)包括了忽略所有列猬膨,用1代表代碼行,在統(tǒng)計(jì)結(jié)果的時(shí)候圈浇,不會(huì)忽略列值為NULL
  • count(列名)只包括列名那一列寥掐,在統(tǒng)計(jì)結(jié)果的時(shí)候靴寂,會(huì)忽略列值為空(這里的空不是只空字符串或者0,而是表示null)的計(jì)數(shù)召耘,即某個(gè)字段值為NULL時(shí)百炬,不統(tǒng)計(jì)。

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

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

65. 什么是存儲(chǔ)過(guò)程德澈?有哪些優(yōu)缺點(diǎn)?

存儲(chǔ)過(guò)程固惯,就是一些編譯好了的SQL語(yǔ)句梆造,這些SQL語(yǔ)句代碼像一個(gè)方法一樣實(shí)現(xiàn)一些功能(對(duì)單表或多表的增刪改查),然后給這些代碼塊取一個(gè)名字葬毫,在用到這個(gè)功能的時(shí)候調(diào)用即可镇辉。

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

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

缺點(diǎn):

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

66. 什么是觸發(fā)器汛骂?觸發(fā)器的使用場(chǎng)景有哪些罕模?

觸發(fā)器,指一段代碼香缺,當(dāng)觸發(fā)某個(gè)事件時(shí)手销,自動(dòng)執(zhí)行這些代碼。

使用場(chǎng)景:

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

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

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

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

68. 超鍵适袜、候選鍵柄错、主鍵、外鍵分別是什么?

  • 超鍵:在關(guān)系模式中售貌,能唯一知標(biāo)識(shí)元組的屬性集稱為超鍵给猾。
  • 候選鍵:是最小超鍵,即沒(méi)有冗余元素的超鍵颂跨。
  • 主鍵:數(shù)據(jù)庫(kù)表中對(duì)儲(chǔ)存數(shù)據(jù)對(duì)象予以唯一和完整標(biāo)識(shí)的數(shù)據(jù)列或?qū)傩缘慕M合敢伸。一個(gè)數(shù)據(jù)列只能有一個(gè)主鍵,且主鍵的取值不能缺失恒削,即不能為空值(Null)池颈。
  • 外鍵:在一個(gè)表中存在的另一個(gè)表的主鍵稱此表的外鍵。钓丰。

69. SQL 約束有哪幾種呢躯砰?

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

70. 談?wù)劻N關(guān)聯(lián)查詢掖举,使用場(chǎng)景快骗。

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

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

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

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

  • int(20) 表示字段是int類型塔次,顯示長(zhǎng)度是 20
  • char(20)表示字段是固定長(zhǎng)度字符串方篮,長(zhǎng)度為 20
  • varchar(20) 表示字段是可變長(zhǎng)度字符串,長(zhǎng)度為 20

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

image

74. UNION與UNION ALL的區(qū)別藕溅?

  • Union:對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作,不包括重復(fù)行继榆,同時(shí)進(jìn)行默認(rèn)規(guī)則的排序巾表;
  • Union All:對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作,包括重復(fù)行略吨,不進(jìn)行排序集币;
  • UNION的效率高于 UNION ALL

75. SQL的生命周期?

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

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

解析并生成執(zhí)行計(jì)劃翠忠,執(zhí)行

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

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

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

76. 一條Sql的執(zhí)行順序当娱?

image

77. 列值為NULL時(shí)吃既,查詢是否會(huì)用到索引?

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

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

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

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

優(yōu)化慢查詢:

  • 分析語(yǔ)句溃槐,是否加載了不必要的字段/數(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ù)存儲(chǔ)空間小抚恒,查詢效率高。但是如果數(shù)據(jù)量過(guò)大,會(huì)超出自增長(zhǎng)的值范圍络拌,多庫(kù)合并俭驮,也有可能有問(wèn)題。
  • uuid:適合大量數(shù)據(jù)的插入和更新操作春贸,但是它無(wú)序的混萝,插入數(shù)據(jù)效率慢,占用空間大祥诽。

80. mysql自增主鍵用完了怎么辦譬圣?

自增主鍵一般用int類型,一般達(dá)不到最大值雄坪,可以考慮提前分庫(kù)分表的厘熟。

81. 字段為什么要求定義為not null屯蹦?

null值會(huì)占用更多的字節(jié),并且null有很多坑的绳姨。

82. 如果要存儲(chǔ)用戶的密碼散列登澜,應(yīng)該使用什么字段進(jìn)行存儲(chǔ)?

密碼散列飘庄,鹽脑蠕,用戶身份證號(hào)等固定長(zhǎng)度的字符串,應(yīng)該使用char而不是varchar來(lái)存儲(chǔ)跪削,這樣可以節(jié)省空間且提高檢索效率谴仙。

83. Mysql驅(qū)動(dòng)程序是什么?

這個(gè)jar包: mysql-connector-java-5.1.18.jar

Mysql驅(qū)動(dòng)程序主要幫助編程語(yǔ)言與 MySQL服務(wù)端進(jìn)行通信碾盐,如連接晃跺、傳輸數(shù)據(jù)、關(guān)閉等毫玖。

84. 如何優(yōu)化長(zhǎng)難的查詢語(yǔ)句掀虎?有實(shí)戰(zhàn)過(guò)嗎?

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

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

平時(shí)積累吧:

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

86. MySQL數(shù)據(jù)庫(kù)cpu飆升的話二打,要怎么處理呢?

排查過(guò)程:

  • 使用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)整(比如說(shuō)加索引翁逞、改 sql肋杖、改內(nèi)存參數(shù))
  • 重新跑這些 SQL洗做。

其他情況:

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

87. 讀寫(xiě)分離常見(jiàn)方案振定?

  • 應(yīng)用程序根據(jù)業(yè)務(wù)邏輯來(lái)判斷,增刪改等寫(xiě)操作命令發(fā)給主庫(kù)肉拓,查詢命令發(fā)給備庫(kù)后频。
  • 利用中間件來(lái)做代理,負(fù)責(zé)對(duì)數(shù)據(jù)庫(kù)的請(qǐng)求識(shí)別出讀還是寫(xiě)暖途,并分發(fā)到不同的數(shù)據(jù)庫(kù)中卑惜。(如:amoeba,mysql-proxy)

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

主從復(fù)制原理驻售,簡(jiǎn)言之残揉,就三步曲,如下:

  • 主數(shù)據(jù)庫(kù)有個(gè)bin-log二進(jìn)制文件芋浮,紀(jì)錄了所有增刪改Sql語(yǔ)句抱环。(binlog線程)
  • 從數(shù)據(jù)庫(kù)把主數(shù)據(jù)庫(kù)的bin-log文件的sql語(yǔ)句復(fù)制過(guò)來(lái)。(io線程)
  • 從數(shù)據(jù)庫(kù)的relay-log重做日志文件中再執(zhí)行一次這些sql語(yǔ)句纸巷。(Sql執(zhí)行線程)

如下圖所示:

image

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

步驟一:主庫(kù)的更新事件(update镇草、insert、delete)被寫(xiě)到binlog

步驟二:從庫(kù)發(fā)起連接瘤旨,連接到主庫(kù)梯啤。

步驟三:此時(shí)主庫(kù)創(chuàng)建一個(gè)binlog dump thread,把binlog的內(nèi)容發(fā)送到從庫(kù)存哲。

步驟四:從庫(kù)啟動(dòng)之后因宇,創(chuàng)建一個(gè)I/O線程,讀取主庫(kù)傳過(guò)來(lái)的binlog內(nèi)容并寫(xiě)入到relay log

步驟五:還會(huì)創(chuàng)建一個(gè)SQL線程祟偷,從relay log里面讀取內(nèi)容察滑,從Exec_Master_Log_Pos位置開(kāi)始執(zhí)行讀取到的更新事件,將更新內(nèi)容寫(xiě)入到slave的db

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

存儲(chǔ)精度都為秒

區(qū)別:

  • DATETIME 的日期范圍是 1001——9999 年修肠;TIMESTAMP 的時(shí)間范圍是 1970——2038 年
  • DATETIME 存儲(chǔ)時(shí)間與時(shí)區(qū)無(wú)關(guān)贺辰;TIMESTAMP 存儲(chǔ)時(shí)間與時(shí)區(qū)有關(guān),顯示的值也依賴于時(shí)區(qū)
  • DATETIME 的存儲(chǔ)空間為 8 字節(jié)嵌施;TIMESTAMP 的存儲(chǔ)空間為 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來(lái)實(shí)現(xiàn)的吃靠,如果事務(wù)執(zhí)行過(guò)程中出錯(cuò)或者用戶執(zhí)行了rollback,系統(tǒng)通過(guò)undo log日志返回事務(wù)開(kāi)始的狀態(tài)足淆。
  • 持久性:使用 redo log來(lái)實(shí)現(xiàn)巢块,只要redo log日志持久化了礁阁,當(dāng)系統(tǒng)崩潰,即可通過(guò)redo log把數(shù)據(jù)恢復(fù)夕冲。
  • 隔離性:通過(guò)鎖以及MVCC,使事務(wù)相互隔離開(kāi)氮兵。
  • 一致性:通過(guò)回滾、恢復(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個(gè)字段。

我們重點(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:在頁(yè)修改的時(shí)候,先寫(xiě)到 redo log buffer 里面修陡, 然后寫(xiě)到 redo log 的文件系統(tǒng)緩存里面(fwrite)沧侥,然后再同步到磁盤(pán)文件( fsync)。
  • Undo:在 MySQL5.5 之前魄鸦, undo 只能存放在 ibdata文件里面宴杀, 5.6 之后,可以通過(guò)設(shè)置 innodb_undo_tablespaces 參數(shù)把 undo log 存放在 ibdata之外号杏。

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

  • 因?yàn)槭聞?wù)在修改頁(yè)時(shí)婴氮,要先記 undo,在記 undo 之前要記 undo 的 redo盾致, 然后修改數(shù)據(jù)頁(yè),再記數(shù)據(jù)頁(yè)修改的 redo荣暮。 Redo(里面包括 undo 的修改) 一定要比數(shù)據(jù)頁(yè)先持久化到磁盤(pán)庭惜。
  • 當(dāng)事務(wù)需要回滾時(shí),因?yàn)橛?undo穗酥,可以把數(shù)據(jù)頁(yè)回滾到前鏡像的 狀態(tài)护赊,崩潰恢復(fù)時(shí)惠遏,如果 redo log 中事務(wù)沒(méi)有對(duì)應(yīng)的 commit 記錄,那么需要用 undo把該事務(wù)的修改回滾到事務(wù)開(kāi)始之前骏啰。
  • 如果有 commit 記錄节吮,就用 redo 前滾到該事務(wù)完成時(shí)并提交掉。

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

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

94. 500臺(tái)db判耕,在最快時(shí)間之內(nèi)重啟透绩。

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

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

監(jiān)控的工具有很多,例如zabbix草丧,lepus狸臣,我這里用的是lepus

96. 你是否做過(guò)主從一致性校驗(yàn),如果有昌执,怎么做的烛亦,如果沒(méi)有,你打算怎么做懂拾?

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

97. 你們數(shù)據(jù)庫(kù)是否支持emoji表情存儲(chǔ)委粉,如果不支持呜师,如何操作?

更換字符集utf8-->utf8mb4

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

SELECT CURRENT_DATE();

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

1、如果A表TID是自增長(zhǎng),并且是連續(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隔離級(jí)別
  • 組合二:id列是二級(jí)唯一索引扁藕,RC隔離級(jí)別
  • 組合三:id列是二級(jí)非唯一索引,RC隔離級(jí)別
  • 組合四:id列上沒(méi)有索引疚脐,RC隔離級(jí)別
  • 組合五:id列是主鍵亿柑,RR隔離級(jí)別
  • 組合六:id列是二級(jí)唯一索引,RR隔離級(jí)別
  • 組合七:id列是二級(jí)非唯一索引棍弄,RR隔離級(jí)別
  • 組合八:id列上沒(méi)有索引望薄,RR隔離級(jí)別
  • 組合九:Serializable隔離級(jí)別

如果答案整理有錯(cuò)疟游,歡迎指出哈,感激不盡~

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末痕支,一起剝皮案震驚了整個(gè)濱河市颁虐,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌卧须,老刑警劉巖另绩,帶你破解...
    沈念sama閱讀 217,185評(píng)論 6 503
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場(chǎng)離奇詭異故慈,居然都是意外死亡板熊,警方通過(guò)查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,652評(píng)論 3 393
  • 文/潘曉璐 我一進(jìn)店門(mén)察绷,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)干签,“玉大人,你說(shuō)我怎么就攤上這事拆撼∪堇停” “怎么了?”我有些...
    開(kāi)封第一講書(shū)人閱讀 163,524評(píng)論 0 353
  • 文/不壞的土叔 我叫張陵闸度,是天一觀的道長(zhǎng)竭贩。 經(jīng)常有香客問(wèn)我,道長(zhǎng)莺禁,這世上最難降的妖魔是什么留量? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 58,339評(píng)論 1 293
  • 正文 為了忘掉前任,我火速辦了婚禮哟冬,結(jié)果婚禮上楼熄,老公的妹妹穿的比我還像新娘。我一直安慰自己浩峡,他們只是感情好可岂,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,387評(píng)論 6 391
  • 文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著翰灾,像睡著了一般缕粹。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上纸淮,一...
    開(kāi)封第一講書(shū)人閱讀 51,287評(píng)論 1 301
  • 那天平斩,我揣著相機(jī)與錄音,去河邊找鬼咽块。 笑死双戳,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的糜芳。 我是一名探鬼主播飒货,決...
    沈念sama閱讀 40,130評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼峭竣!你這毒婦竟也來(lái)了塘辅?” 一聲冷哼從身側(cè)響起,我...
    開(kāi)封第一講書(shū)人閱讀 38,985評(píng)論 0 275
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤皆撩,失蹤者是張志新(化名)和其女友劉穎扣墩,沒(méi)想到半個(gè)月后,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體扛吞,經(jīng)...
    沈念sama閱讀 45,420評(píng)論 1 313
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡呻惕,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,617評(píng)論 3 334
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了滥比。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片亚脆。...
    茶點(diǎn)故事閱讀 39,779評(píng)論 1 348
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖盲泛,靈堂內(nèi)的尸體忽然破棺而出濒持,到底是詐尸還是另有隱情,我是刑警寧澤寺滚,帶...
    沈念sama閱讀 35,477評(píng)論 5 345
  • 正文 年R本政府宣布柑营,位于F島的核電站,受9級(jí)特大地震影響村视,放射性物質(zhì)發(fā)生泄漏官套。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,088評(píng)論 3 328
  • 文/蒙蒙 一蚁孔、第九天 我趴在偏房一處隱蔽的房頂上張望奶赔。 院中可真熱鬧,春花似錦勒虾、人聲如沸纺阔。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 31,716評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)笛钝。三九已至,卻和暖如春愕宋,著一層夾襖步出監(jiān)牢的瞬間玻靡,已是汗流浹背。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 32,857評(píng)論 1 269
  • 我被黑心中介騙來(lái)泰國(guó)打工中贝, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留囤捻,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 47,876評(píng)論 2 370
  • 正文 我出身青樓邻寿,卻偏偏與公主長(zhǎng)得像蝎土,于是被迫代替她去往敵國(guó)和親视哑。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,700評(píng)論 2 354