這是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è)就是生效原則 比如

index(a,b,c) where a=3 是，只使用了a where a=3 and b=5 是，使用了a,b where a=3 and b=5 and c=4 是，使用了a,b,c where b=3 or where c=4 否 where a=3 and c=4 是，僅使用了a where a=3 and b>10 and c=7 是，使用了a,b where a=3 and b like '%xx%' and c=7 使用了a,b

索引算法有 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操作符
例如：

只要它的查詢條件是一個(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索引。

索引設(shè)計(jì)的原則？

適合索引的列是出現(xiàn)在where子句中的列，或者連接子句中指定的列

基數(shù)較小的類，索引效果較差，沒(méi)有必要在此列建立索引

使用短索引，如果對(duì)長(zhǎng)字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長(zhǎng)度，這樣能夠節(jié)省大量索引空間

不要過(guò)度索引。索引需要額外的磁盤(pán)空間，并降低寫(xiě)操作的性能。在修改表內(nèi)容的時(shí)候，索引會(huì)進(jìn)行更新甚至重構(gòu)，索引列越多，這個(gè)時(shí)間就會(huì)越長(zhǎng)。所以只保持需要的索引有利于查詢即可。

如何定位及優(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)的查詢類型。

idselect_typedescription

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è)例子：

create table tmp (id int unsigned not null AUTO_INCREMENT,name varchar(255),PRIMARY KEY (id) ) engine = innodb partition by key (id) partitions 5;

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ī)則

RANGE從
0到10000一個(gè)表，10001到20000一個(gè)表；

HASH取模
一個(gè)商場(chǎng)系統(tǒng)，一般都是將用戶，訂單作為主表，然后將和它們相關(guān)的作為附表，這樣不會(huì)造成跨庫(kù)事務(wù)之類的問(wèn)題。 取用戶id，然后hash取模，分配到不同的數(shù)據(jù)庫(kù)上。

地理區(qū)域
比如按照華東，華南，華北這樣來(lái)區(qū)分業(yè)務(wù)，七牛云應(yīng)該就是如此。

時(shí)間
按照時(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)境的。

如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。

如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表大的用exists，子查詢表小的用in。

not in 和not exists如果查詢語(yǔ)句使用了not in 那么內(nèi)外表都進(jìn)行全表掃描，沒(méi)有用到索引；而not extsts的子查詢依然能用到表上的索引。所以無(wú)論那個(gè)表大，用not exists都比not in要快。

推薦閱讀

互聯(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)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。