網際網路公司面試必問的mysql題目(下）

codeyuyu發表於2018-09-19

面試MySql

這是mysql系列的下篇，上篇文章地址我附在文末。

什麼是資料庫索引？索引有哪幾種型別？什麼是最左字首原則？索引演算法有哪些？有什麼區別？

索引是對資料庫表中一列或多列的值進行排序的一種結構。一個非常恰當的比喻就是書的目錄頁與書的正文內容之間的關係，為了方便查詢書中的內容，通過對內容建立索引形成目錄。索引是一個檔案，它是要佔據物理空間的。

主鍵索引: 資料列不允許重複，不允許為NULL.一個表只能有一個主鍵。

唯一索引: 資料列不允許重複，允許為NULL值，一個表允許多個列建立唯一索引。

可以通過 ALTER TABLE table_name ADD UNIQUE (column); 建立唯一索引

可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 建立唯一組合索引

普通索引: 基本的索引型別，沒有唯一性的限制，允許為NULL值。

可以通過ALTER TABLE table_name ADD INDEX index_name (column);建立普通索引

可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);建立組合索引

全文索引： 是目前搜尋引擎使用的一種關鍵技術。

可以通過ALTER TABLE table_name ADD FULLTEXT (column);建立全文索引

最左字首

顧名思義，就是最左優先，在建立多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。
還有一個就是生效原則比如

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資料庫索引演算法，也是mysql預設的演算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用於like操作符，只要它的查詢條件是一個不以萬用字元開頭的常量，例如：

select * from user where name like 'jack%'; 
如果一萬用字元開頭，或者沒有使用常量，則不會使用索引，例如： 
select * from user where name like '%jack'; 
複製程式碼

Hash Hash索引只能用於對等比較，例如=,<=>（相當於=）操作符。由於是一次定位資料，不像BTree索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高於BTree索引。

BTree索引是最常用的mysql資料庫索引演算法，也是mysql預設的演算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用於like操作符例如：

只要它的查詢條件是一個不以萬用字元開頭的常量
select * from user where name like 'jack%'; 
如果一萬用字元開頭，或者沒有使用常量，則不會使用索引，例如： 
select * from user where name like '%jack'; 
複製程式碼

索引設計的原則？

適合索引的列是出現在where子句中的列，或者連線子句中指定的列
基數較小的類，索引效果較差，沒有必要在此列建立索引
使用短索引，如果對長字串列進行索引，應該指定一個字首長度，這樣能夠節省大量索引空間
不要過度索引。索引需要額外的磁碟空間，並降低寫操作的效能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。所以只保持需要的索引有利於查詢即可。

如何定位及優化SQL語句的效能問題？

對於低效能的SQL語句的定位，最重要也是最有效的方法就是使用執行計劃。我們知道，不管是哪種資料庫，或者是哪種資料庫引擎，在對一條SQL語句進行執行的過程中都會做很多相關的優化，對於查詢語句，最重要的優化方式就是使用索引。而執行計劃，就是顯示資料庫引擎對於SQL語句的執行的詳細情況，其中包含了是否使用索引，使用什麼索引，使用的索引的相關資訊等。

執行計劃包含的資訊 id 有一組數字組成。表示一個查詢中各個子查詢的執行順序;

id相同執行順序由上至下。
id不同，id值越大優先順序越高，越先被執行。
id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢語句中。

select_type 每個子查詢的查詢型別，一些常見的查詢型別。

id	select_type	description
1	SIMPLE	不包含任何子查詢或union等查詢
2	PRIMARY	包含子查詢最外層查詢就顯示為 PRIMARY
3	SUBQUERY	在select或 where字句中包含的查詢
4	DERIVED	from字句中包含的查詢
5	UNION	出現在union後的查詢語句中
6	UNION RESULT	從UNION中獲取結果集，例如上文的第三個例子

table 查詢的資料表，當從衍生表中查資料時會顯示 x 表示對應的執行計劃id partitions 表分割槽、表建立的時候可以指定通過那個列進行表分割槽。舉個例子：

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(非常重要，可以看到有沒有走索引) 訪問型別

ALL 掃描全表資料
index 遍歷索引
range 索引範圍查詢
index_subquery 在子查詢中使用 ref
unique_subquery 在子查詢中使用 eq_ref
ref_or_null 對Null進行索引的優化的 ref
fulltext 使用全文索引
ref 使用非唯一索引查詢資料
eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。

possible_keys 可能使用的索引，注意不一定會使用。查詢涉及到的欄位上若存在索引，則該索引將被列出來。當該列為 NULL時就要考慮當前的SQL是否需要優化了。

key 顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。

TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的資料覆蓋了需要查詢的所有資料)，則該索引僅出現在key列表中

key_length 索引長度

ref 表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

rows 返回估算的結果集數目，並不是一個準確的值。

extra 的資訊非常豐富，常見的有：

Using index 使用覆蓋索引
Using where 使用了用where子句來過濾結果集
Using filesort 使用檔案排序，使用非索引列進行排序時出現，非常消耗效能，儘量優化。
Using temporary 使用了臨時表 sql優化的目標可以參考阿里開發手冊

某個表有近千萬資料，CRUD比較慢，如何優化？分庫分表了是怎麼做的？分表分庫了有什麼問題？有用到中介軟體麼?他們的原理知道麼？

資料千萬級別之多，佔用的儲存空間也比較大，可想而知它不會儲存在一塊連續的物理空間上，而是鏈式儲存在多個碎片的物理空間上。可能對於長字串的比較，就用更多的時間查詢與比較，這就導致用更多的時間。

可以做表拆分，減少單表欄位數量，優化表結構。
在保證主鍵有效的情況下，檢查主鍵索引的欄位順序，使得查詢語句中條件的欄位順序和主鍵索引的欄位順序保持一致。

主要兩種拆分垂直拆分，水平拆分。

垂直分表

也就是“大表拆小表”，基於列欄位進行的。一般是表中的欄位較多，將不常用的，資料較大，長度較長（比如text型別欄位）的拆分到“擴充套件表“。一般是針對那種幾百列的大表，也避免查詢時，資料量太大造成的“跨頁”問題。

垂直分庫針對的是一個系統中的不同業務進行拆分，比如使用者User一個庫，商品Producet一個庫，訂單Order一個庫。切分後，要放在多個伺服器上，而不是一個伺服器上。為什麼？我們想象一下，一個購物網站對外提供服務，會有使用者，商品，訂單等的CRUD。沒拆分之前，全部都是落到單一的庫上的，這會讓資料庫的單庫處理能力成為瓶頸。按垂直分庫後，如果還是放在一個資料庫伺服器上，隨著使用者量增大，這會讓單個資料庫的處理能力成為瓶頸，還有單個伺服器的磁碟空間，記憶體，tps等非常吃緊。所以我們要拆分到多個伺服器上，這樣上面的問題都解決了，以後也不會面對單機資源問題。

資料庫業務層面的拆分，和服務的“治理”，“降級”機制類似，也能對不同業務的資料分別的進行管理，維護，監控，擴充套件等。資料庫往往最容易成為應用系統的瓶頸，而資料庫本身屬於“有狀態”的，相對於Web和應用伺服器來講，是比較難實現“橫向擴充套件”的。資料庫的連線資源比較寶貴且單機處理能力也有限，在高併發場景下，垂直分庫一定程度上能夠突破IO、連線數及單機硬體資源的瓶頸。

水平分表

針對資料量巨大的單張表（比如訂單表），按照某種規則（RANGE,HASH取模等），切分到多張表裡面去。但是這些表還是在同一個庫中，所以庫級別的資料庫操作還是有IO瓶頸。不建議採用。

水平分庫分表

將單張表的資料切分到多個伺服器上去，每個伺服器具有相應的庫與表，只是表中資料集合不同。水平分庫分表能夠有效的緩解單機和單庫的效能瓶頸和壓力，突破IO、連線數、硬體資源等的瓶頸。

水平分庫分表切分規則

RANGE從 0到10000一個表，10001到20000一個表；
HASH取模一個商場系統，一般都是將使用者，訂單作為主表，然後將和它們相關的作為附表，這樣不會造成跨庫事務之類的問題。取使用者id，然後hash取模，分配到不同的資料庫上。
地理區域比如按照華東，華南，華北這樣來區分業務，七牛雲應該就是如此。
時間按照時間切分，就是將6個月前，甚至一年前的資料切出去放到另外的一張表，因為隨著時間流逝，這些表的資料被查詢的概率變小，所以沒必要和“熱資料”放在一起，這個也是“冷熱資料分離”。

分庫分表後面臨的問題

事務支援 分庫分表後，就成了分散式事務了。如果依賴資料庫本身的分散式事務管理功能去執行事務，將付出高昂的效能代價；如果由應用程式去協助控制，形成程式邏輯上的事務，又會造成程式設計方面的負擔。
跨庫join

只要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實現。在第一次查詢的結果集中找出關聯資料的id,根據這些id發起第二次請求得到關聯資料。分庫分表方案產品
跨節點的count,order by,group by以及聚合函式問題 這些是一類問題，因為它們都需要基於全部資料集合進行計算。多數的代理都不會自動處理合並工作。解決方案：與解決跨節點join問題的類似，分別在各個節點上得到結果後在應用程式端進行合併。和join不同的是每個結點的查詢可以並行執行，因此很多時候它的速度要比單一大表快很多。但如果結果集很大，對應用程式記憶體的消耗是一個問題。
資料遷移，容量規劃，擴容等問題 來自淘寶綜合業務平臺團隊，它利用對2的倍數取餘具有向前相容的特性（如對4取餘得1的數對2取餘也是1）來分配資料，避免了行級別的資料遷移，但是依然需要進行表級別的遷移，同時對擴容規模和分表數量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。
ID問題
一旦資料庫被切分到多個物理結點上，我們將不能再依賴資料庫自身的主鍵生成機制。一方面，某個分割槽資料庫自生成的ID無法保證在全域性上是唯一的；另一方面，應用程式在插入資料之前需要先獲得ID,以便進行SQL路由. 一些常見的主鍵生成策略

UUID 使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由於UUID非常的長，除佔用大量儲存空間外，最主要的問題是在索引上，在建立索引和基於索引進行查詢時都存在效能問題。 Twitter的分散式自增ID演算法Snowflake 在分散式系統中，需要生成全域性UID的場合還是比較多的，twitter的snowflake解決了這種需求，實現也還是很簡單的，除去配置資訊，核心程式碼就是毫秒級時間41位機器ID 10位毫秒內序列12位。

跨分片的排序分頁 般來講，分頁時需要按照指定欄位進行排序。當排序欄位就是分片欄位的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序欄位非分片欄位的時候，情況就會變得比較複雜了。為了最終結果的準確性，我們需要在不同的分片節點中將資料進行排序並返回，並將不同分片返回的結果集進行彙總和再次排序，最後再返回給使用者。如下圖所示：

中介軟體推薦

mysql中in 和exists 區別

mysql中的in語句是把外表和內表作hash 連線，而exists語句是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。一直大家都認為exists比in語句的效率要高，這種說法其實是不準確的。這個是要區分環境的。

如果查詢的兩個表大小相當，那麼用in和exists差別不大。
如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in。
not in 和not exists如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；而not extsts的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。