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

codeyuyu發表於2018-09-19

這是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'; 
複製程式碼

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

索引設計的原則?

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

如何定位及優化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 的資訊非常豐富,常見的有:

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

圖片描述

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

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

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

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

圖片描述

垂直分表

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

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

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

水平分表

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

水平分庫分表

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

水平分庫分表切分規則

  1. RANGE從 0到10000一個表,10001到20000一個表;
  2. HASH取模 一個商場系統,一般都是將使用者,訂單作為主表,然後將和它們相關的作為附表,這樣不會造成跨庫事務之類的問題。 取使用者id,然後hash取模,分配到不同的資料庫上。
  3. 地理區域 比如按照華東,華南,華北這樣來區分業務,七牛雲應該就是如此。
  4. 時間 按照時間切分,就是將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語句的效率要高,這種說法其實是不準確的。這個是要區分環境的。

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

推薦閱讀

網際網路公司面試必問的Redis題目

如果有人問你CAP理論是什麼,就把這篇文章發給他。

網際網路公司面試必問的mysql題目(上)

最近,我組建了個群聊。學習Java進階技術乾貨、實踐分享,職位內推,一起聊聊理想。志同道合的朋友,歡迎你的加入。

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

好了,祝大家面試順利!歡迎長按關注~不定期送書

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

相關文章