前言
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í)使用聚集索引或非聚集索引岖是?
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í)行悉默。
樂(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)。
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í)是加鎖了鲜锚。如下圖:
我們?cè)匍_(kāi)一個(gè)事務(wù)對(duì)另外一條記錄1270071更新,發(fā)現(xiàn)更新成功苫拍,因此芜繁,如果查詢條件用了索引/主鍵,會(huì)加行鎖~
我們繼續(xù)一路向北吧绒极,換普通字段balance吧骏令,發(fā)現(xiàn)又阻塞了。因此垄提,沒(méi)用索引/主鍵的話榔袋,select for update加的就是表鎖
16. MySQL事務(wù)得四大特性以及實(shí)現(xiàn)原理
- 原子性: 事務(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ù)制原理吧崖瞭,如圖:
主從復(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)圖吧~
查詢語(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中有哪幾種鎖,列舉一下笋妥?
如果按鎖粒度劃分懊昨,有以下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)圖
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í)使用聚簇索引與非聚簇索引
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鎖算法
- 按鎖粒度分有:表鎖枪向,頁(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>兼容性如下:
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)脆丁,如下:
死鎖有四個(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ū)別
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í)行順序当娱?
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í)行線程)
如下圖所示:
上圖主從復(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ò)疟游,歡迎指出哈,感激不盡~