互联网公司面试必问的mysql题目(下)
這是mysql系列的下篇,上篇文章地址我附在文末。
什么是數(shù)據(jù)庫(kù)索引?索引有哪幾種類型?什么是最左前綴原則?索引算法有哪些?有什么區(qū)別?
索引是對(duì)數(shù)據(jù)庫(kù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。一個(gè)非常恰當(dāng)?shù)谋扔骶褪菚?shū)的目錄頁(yè)與書(shū)的正文內(nèi)容之間的關(guān)系,為了方便查找書(shū)中的內(nèi)容,通過(guò)對(duì)內(nèi)容建立索引形成目錄。索引是一個(gè)文件,它是要占據(jù)物理空間的。
主鍵索引:
數(shù)據(jù)列不允許重復(fù),不允許為NULL.一個(gè)表只能有一個(gè)主鍵。
唯一索引:
數(shù)據(jù)列不允許重復(fù),允許為NULL值,一個(gè)表允許多個(gè)列創(chuàng)建唯一索引。
可以通過(guò)
ALTER TABLE table_name ADD UNIQUE (column);
創(chuàng)建唯一索引
可以通過(guò)
ALTER TABLE table_name ADD UNIQUE (column1,column2);
創(chuàng)建唯一組合索引
普通索引:
基本的索引類型,沒(méi)有唯一性的限制,允許為NULL值。
可以通過(guò)ALTER TABLE table_name ADD INDEX index_name (column);創(chuàng)建普通索引
可以通過(guò)ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創(chuàng)建組合索引
全文索引:
是目前搜索引擎使用的一種關(guān)鍵技術(shù)。
可以通過(guò)ALTER TABLE table_name ADD FULLTEXT (column);創(chuàng)建全文索引
最左前綴
- 顧名思義,就是最左優(yōu)先,在創(chuàng)建多列索引時(shí),要根據(jù)業(yè)務(wù)需求,where子句中使用最頻繁的一列放在最左邊。
- 還有一個(gè)就是生效原則 比如
索引算法有 BTree Hash
BTree是最常用的mysql數(shù)據(jù)庫(kù)索引算法,也是mysql默認(rèn)的算法。因?yàn)樗粌H可以被用在=,>,>=,<,<=和between這些比較操作符上,而且還可以用于like操作符,只要它的查詢條件是一個(gè)不以通配符開(kāi)頭的常量,
例如:
select * from user where name like 'jack%';
如果一通配符開(kāi)頭,或者沒(méi)有使用常量,則不會(huì)使用索引,例如:
select * from user where name like '%jack';
Hash
Hash索引只能用于對(duì)等比較,例如=,<=>(相當(dāng)于=)操作符。由于是一次定位數(shù)據(jù),不像BTree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn),最后才能訪問(wèn)到頁(yè)節(jié)點(diǎn)這樣多次IO訪問(wèn),所以檢索效率遠(yuǎn)高于BTree索引。
BTree索引是最常用的mysql數(shù)據(jù)庫(kù)索引算法,也是mysql默認(rèn)的算法。因?yàn)樗粌H可以被用在=,>,>=,<,<=和between這些比較操作符上,而且還可以用于like操作符
例如:
Hash
Hash索引只能用于對(duì)等比較,例如=,<=>(相當(dāng)于=)操作符。由于是一次定位數(shù)據(jù),不像BTree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn),最后才能訪問(wèn)到頁(yè)節(jié)點(diǎn)這樣多次IO訪問(wèn),所以檢索效率遠(yuǎn)高于BTree索引。
索引設(shè)計(jì)的原則?
如何定位及優(yōu)化SQL語(yǔ)句的性能問(wèn)題
對(duì)于低性能的SQL語(yǔ)句的定位,最重要也是最有效的方法就是使用執(zhí)行計(jì)劃。
我們知道,不管是哪種數(shù)據(jù)庫(kù),或者是哪種數(shù)據(jù)庫(kù)引擎,在對(duì)一條SQL語(yǔ)句進(jìn)行執(zhí)行的過(guò)程中都會(huì)做很多相關(guān)的優(yōu)化,對(duì)于查詢語(yǔ)句,最重要的優(yōu)化方式就是使用索引。
而執(zhí)行計(jì)劃,就是顯示數(shù)據(jù)庫(kù)引擎對(duì)于SQL語(yǔ)句的執(zhí)行的詳細(xì)情況,其中包含了是否使用索引,使用什么索引,使用的索引的相關(guān)信息等。
執(zhí)行計(jì)劃包含的信息
id
有一組數(shù)字組成。表示一個(gè)查詢中各個(gè)子查詢的執(zhí)行順序;
- id相同執(zhí)行順序由上至下。
- id不同,id值越大優(yōu)先級(jí)越高,越先被執(zhí)行。
- id為null時(shí)表示一個(gè)結(jié)果集,不需要使用它查詢,常出現(xiàn)在包含union等查詢語(yǔ)句中。
select_type
每個(gè)子查詢的查詢類型,一些常見(jiàn)的查詢類型。
| 1 | SIMPLE | 不包含任何子查詢或union等查詢 |
| 2 | PRIMARY | 包含子查詢最外層查詢就顯示為 PRIMARY |
| 3 | SUBQUERY | 在select或 where字句中包含的查詢 |
| 4 | DERIVED | from字句中包含的查詢 |
| 5 | UNION | 出現(xiàn)在union后的查詢語(yǔ)句中 |
| 6 | UNION RESULT | 從UNION中獲取結(jié)果集,例如上文的第三個(gè)例子 |
table
查詢的數(shù)據(jù)表,當(dāng)從衍生表中查數(shù)據(jù)時(shí)會(huì)顯示 x 表示對(duì)應(yīng)的執(zhí)行計(jì)劃id
partitions
表分區(qū)、表創(chuàng)建的時(shí)候可以指定通過(guò)那個(gè)列進(jìn)行表分區(qū)。 舉個(gè)例子:
type(非常重要,可以看到有沒(méi)有走索引)
訪問(wèn)類型
- ALL 掃描全表數(shù)據(jù)
- index 遍歷索引
- range 索引范圍查找
- index_subquery 在子查詢中使用 ref
- unique_subquery 在子查詢中使用 eq_ref
- ref_or_null 對(duì)Null進(jìn)行索引的優(yōu)化的 ref
- fulltext 使用全文索引
- ref 使用非唯一索引查找數(shù)據(jù)
- eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關(guān)聯(lián)。
possible_keys
可能使用的索引,注意不一定會(huì)使用。查詢涉及到的字段上若存在索引,則該索引將被列出來(lái)。當(dāng)該列為 NULL時(shí)就要考慮當(dāng)前的SQL是否需要優(yōu)化了。
key
顯示MySQL在查詢中實(shí)際使用的索引,若沒(méi)有使用索引,顯示為NULL。
TIPS:查詢中若使用了覆蓋索引(覆蓋索引:索引的數(shù)據(jù)覆蓋了需要查詢的所有數(shù)據(jù)),則該索引僅出現(xiàn)在key列表中
key_length
索引長(zhǎng)度
ref
表示上述表的連接匹配條件,即哪些列或常量被用于查找索引列上的值
rows
返回估算的結(jié)果集數(shù)目,并不是一個(gè)準(zhǔn)確的值。
extra
extra的信息非常豐富,常見(jiàn)的有:
1.Using index 使用覆蓋索引
2.Using where 使用了用where子句來(lái)過(guò)濾結(jié)果集
3.Using filesort 使用文件排序,使用非索引列進(jìn)行排序時(shí)出現(xiàn),非常消耗性能,盡量?jī)?yōu)化。
4.Using temporary 使用了臨時(shí)表
sql優(yōu)化的目標(biāo)可以參考阿里開(kāi)發(fā)手冊(cè)
某個(gè)表有近千萬(wàn)數(shù)據(jù),CRUD比較慢,如何優(yōu)化?分庫(kù)分表了是怎么做的?分表分庫(kù)了有什么問(wèn)題?有用到中間件么?他們的原理知道么?
數(shù)據(jù)千萬(wàn)級(jí)別之多,占用的存儲(chǔ)空間也比較大,可想而知它不會(huì)存儲(chǔ)在一塊連續(xù)的物理空間上,而是鏈?zhǔn)酱鎯?chǔ)在多個(gè)碎片的物理空間上。可能對(duì)于長(zhǎng)字符串的比較,就用更多的時(shí)間查找與比較,這就導(dǎo)致用更多的時(shí)間。
- 可以做表拆分,減少單表字段數(shù)量,優(yōu)化表結(jié)構(gòu)。
- 在保證主鍵有效的情況下,檢查主鍵索引的字段順序,使得查詢語(yǔ)句中條件的字段順序和主鍵索引的字段順序保持一致。
主要兩種拆分 垂直拆分,水平拆分。
垂直分表
也就是“大表拆小表”,基于列字段進(jìn)行的。一般是表中的字段較多,將不常用的, 數(shù)據(jù)較大,長(zhǎng)度較長(zhǎng)(比如text類型字段)的拆分到“擴(kuò)展表“。 一般是針對(duì)那種幾百列的大表,也避免查詢時(shí),數(shù)據(jù)量太大造成的“跨頁(yè)”問(wèn)題。
垂直分庫(kù)針對(duì)的是一個(gè)系統(tǒng)中的不同業(yè)務(wù)進(jìn)行拆分,比如用戶User一個(gè)庫(kù),商品Producet一個(gè)庫(kù),訂單Order一個(gè)庫(kù)。 切分后,要放在多個(gè)服務(wù)器上,而不是一個(gè)服務(wù)器上。為什么? 我們想象一下,一個(gè)購(gòu)物網(wǎng)站對(duì)外提供服務(wù),會(huì)有用戶,商品,訂單等的CRUD。沒(méi)拆分之前, 全部都是落到單一的庫(kù)上的,這會(huì)讓數(shù)據(jù)庫(kù)的單庫(kù)處理能力成為瓶頸。按垂直分庫(kù)后,如果還是放在一個(gè)數(shù)據(jù)庫(kù)服務(wù)器上, 隨著用戶量增大,這會(huì)讓單個(gè)數(shù)據(jù)庫(kù)的處理能力成為瓶頸,還有單個(gè)服務(wù)器的磁盤(pán)空間,內(nèi)存,tps等非常吃緊。 所以我們要拆分到多個(gè)服務(wù)器上,這樣上面的問(wèn)題都解決了,以后也不會(huì)面對(duì)單機(jī)資源問(wèn)題。
數(shù)據(jù)庫(kù)業(yè)務(wù)層面的拆分,和服務(wù)的“治理”,“降級(jí)”機(jī)制類似,也能對(duì)不同業(yè)務(wù)的數(shù)據(jù)分別的進(jìn)行管理,維護(hù),監(jiān)控,擴(kuò)展等。 數(shù)據(jù)庫(kù)往往最容易成為應(yīng)用系統(tǒng)的瓶頸,而數(shù)據(jù)庫(kù)本身屬于“有狀態(tài)”的,相對(duì)于Web和應(yīng)用服務(wù)器來(lái)講,是比較難實(shí)現(xiàn)“橫向擴(kuò)展”的。 數(shù)據(jù)庫(kù)的連接資源比較寶貴且單機(jī)處理能力也有限,在高并發(fā)場(chǎng)景下,垂直分庫(kù)一定程度上能夠突破IO、連接數(shù)及單機(jī)硬件資源的瓶頸。
水平分表
針對(duì)數(shù)據(jù)量巨大的單張表(比如訂單表),按照某種規(guī)則(RANGE,HASH取模等),切分到多張表里面去。 但是這些表還是在同一個(gè)庫(kù)中,所以庫(kù)級(jí)別的數(shù)據(jù)庫(kù)操作還是有IO瓶頸。不建議采用。
水平分庫(kù)分表
將單張表的數(shù)據(jù)切分到多個(gè)服務(wù)器上去,每個(gè)服務(wù)器具有相應(yīng)的庫(kù)與表,只是表中數(shù)據(jù)集合不同。 水平分庫(kù)分表能夠有效的緩解單機(jī)和單庫(kù)的性能瓶頸和壓力,突破IO、連接數(shù)、硬件資源等的瓶頸。
水平分庫(kù)分表切分規(guī)則
0到10000一個(gè)表,10001到20000一個(gè)表;
一個(gè)商場(chǎng)系統(tǒng),一般都是將用戶,訂單作為主表,然后將和它們相關(guān)的作為附表,這樣不會(huì)造成跨庫(kù)事務(wù)之類的問(wèn)題。 取用戶id,然后hash取模,分配到不同的數(shù)據(jù)庫(kù)上。
比如按照華東,華南,華北這樣來(lái)區(qū)分業(yè)務(wù),七牛云應(yīng)該就是如此。
按照時(shí)間切分,就是將6個(gè)月前,甚至一年前的數(shù)據(jù)切出去放到另外的一張表,因?yàn)殡S著時(shí)間流逝,這些表的數(shù)據(jù) 被查詢的概率變小,所以沒(méi)必要和“熱數(shù)據(jù)”放在一起,這個(gè)也是“冷熱數(shù)據(jù)分離”。
分庫(kù)分表后面臨的問(wèn)題
- 事務(wù)支持
分庫(kù)分表后,就成了分布式事務(wù)了。如果依賴數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù),將付出高昂的性能代價(jià); 如果由應(yīng)用程序去協(xié)助控制,形成程序邏輯上的事務(wù),又會(huì)造成編程方面的負(fù)擔(dān)。 跨庫(kù)join
只要是進(jìn)行切分,跨節(jié)點(diǎn)Join的問(wèn)題是不可避免的。但是良好的設(shè)計(jì)和切分卻可以減少此類情況的發(fā)生。解決這一問(wèn)題的普遍做法是分兩次查詢實(shí)現(xiàn)。在第一次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)的id,根據(jù)這些id發(fā)起第二次請(qǐng)求得到關(guān)聯(lián)數(shù)據(jù)。
分庫(kù)分表方案產(chǎn)品跨節(jié)點(diǎn)的count,order by,group by以及聚合函數(shù)問(wèn)題
這些是一類問(wèn)題,因?yàn)樗鼈兌夹枰谌繑?shù)據(jù)集合進(jìn)行計(jì)算。多數(shù)的代理都不會(huì)自動(dòng)處理合并工作。解決方案:與解決跨節(jié)點(diǎn)join問(wèn)題的類似,分別在各個(gè)節(jié)點(diǎn)上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并。和join不同的是每個(gè)結(jié)點(diǎn)的查詢可以并行執(zhí)行,因此很多時(shí)候它的速度要比單一大表快很多。但如果結(jié)果集很大,對(duì)應(yīng)用程序內(nèi)存的消耗是一個(gè)問(wèn)題。數(shù)據(jù)遷移,容量規(guī)劃,擴(kuò)容等問(wèn)題
來(lái)自淘寶綜合業(yè)務(wù)平臺(tái)團(tuán)隊(duì),它利用對(duì)2的倍數(shù)取余具有向前兼容的特性(如對(duì)4取余得1的數(shù)對(duì)2取余也是1)來(lái)分配數(shù)據(jù),避免了行級(jí)別的數(shù)據(jù)遷移,但是依然需要進(jìn)行表級(jí)別的遷移,同時(shí)對(duì)擴(kuò)容規(guī)模和分表數(shù)量都有限制。總得來(lái)說(shuō),這些方案都不是十分的理想,多多少少都存在一些缺點(diǎn),這也從一個(gè)側(cè)面反映出了Sharding擴(kuò)容的難度。- ID問(wèn)題
一旦數(shù)據(jù)庫(kù)被切分到多個(gè)物理結(jié)點(diǎn)上,我們將不能再依賴數(shù)據(jù)庫(kù)自身的主鍵生成機(jī)制。一方面,某個(gè)分區(qū)數(shù)據(jù)庫(kù)自生成的ID無(wú)法保證在全局上是唯一的;另一方面,應(yīng)用程序在插入數(shù)據(jù)之前需要先獲得ID,以便進(jìn)行SQL路由.
一些常見(jiàn)的主鍵生成策略
UUID
使用UUID作主鍵是最簡(jiǎn)單的方案,但是缺點(diǎn)也是非常明顯的。由于UUID非常的長(zhǎng),除占用大量存儲(chǔ)空間外,最主要的問(wèn)題是在索引上,在建立索引和基于索引進(jìn)行查詢時(shí)都存在性能問(wèn)題。
Twitter的分布式自增ID算法Snowflake
在分布式系統(tǒng)中,需要生成全局UID的場(chǎng)合還是比較多的,twitter的snowflake解決了這種需求,實(shí)現(xiàn)也還是很簡(jiǎn)單的,除去配置信息,核心代碼就是毫秒級(jí)時(shí)間41位 機(jī)器ID 10位 毫秒內(nèi)序列12位。
- 跨分片的排序分頁(yè)
般來(lái)講,分頁(yè)時(shí)需要按照指定字段進(jìn)行排序。當(dāng)排序字段就是分片字段的時(shí)候,我們通過(guò)分片規(guī)則可以比較容易定位到指定的分片,而當(dāng)排序字段非分片字段的時(shí)候,情況就會(huì)變得比較復(fù)雜了。為了最終結(jié)果的準(zhǔn)確性,我們需要在不同的分片節(jié)點(diǎn)中將數(shù)據(jù)進(jìn)行排序并返回,并將不同分片返回的結(jié)果集進(jìn)行匯總和再次排序,最后再返回給用戶。如下圖所示:
中間件推薦
mysql中in 和exists 區(qū)別
mysql中的in語(yǔ)句是把外表和內(nèi)表作hash 連接,而exists語(yǔ)句是對(duì)外表作loop循環(huán),每次loop循環(huán)再對(duì)內(nèi)表進(jìn)行查詢。一直大家都認(rèn)為exists比in語(yǔ)句的效率要高,這種說(shuō)法其實(shí)是不準(zhǔn)確的。這個(gè)是要區(qū)分環(huán)境的。
推薦閱讀
互聯(lián)網(wǎng)公司面試必問(wèn)的Redis題目
如果有人問(wèn)你CAP理論是什么,就把這篇文章發(fā)給他。
互聯(lián)網(wǎng)公司面試必問(wèn)的mysql題目(上)
轉(zhuǎn)載于:https://www.cnblogs.com/fishlynn/p/9674793.html
總結(jié)
以上是生活随笔為你收集整理的互联网公司面试必问的mysql题目(下)的全部?jī)?nèi)容,希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇: django 自定义simple_tag
- 下一篇: 焚风现象