分類
發燒車訊

面試三輪我倒在了一道sql題上——sql性能優化

一、前言

最近小農在找工作,因為今年疫情的特殊原因,導致工作不是特別好找,所以一旦有面試電話,如果可以,都會去試一試,剛好接到一個面試邀請,感覺公司還不錯,於是就確定了面試時間,準備了一下就去面試了。

第一輪面試是小組組長面試,通過。
第二輪是經理面試也是通過了。
第三輪總監面試,前面都還有模有樣,突然畫風一轉,面試官說:“問你最後一個問題”

面試官:10W條數據,我要從其中查出100條不連續的數據,給你id,來查name和password進行展示,如何才能高性能的去使用?

我:在id上建立聚簇索引,然後用 in id 來縮小表搜索範圍,最後 使用條件查詢 小於最大id,大於最小id,這樣可以讓sql速度能夠比較快的展示,雖然In的性能比較低
心裏活動:雕蟲小技,還最後一個問題,這樣的問題再來一個吧

只見面試官緊鎖眉頭,與我心裏期待的表情有點不一樣啊,難道是哪個環節出了問題?
面試官:這樣的性能不能達到最優化的程度,而且如果我給你的最小id是1,最大id是100000呢?

你這就有點杠精了啊,那行吧,你是面試官你說了算
我:既然id已經給出來了,而且只查詢兩個字段,用聚簇索引那麼查詢數據是很快的,用in id應該是可以的。

面試官:好的,回去等通知吧
我。。。。。

二、後知

於是回去后,查詢資料,才知道原來面試官,真正想考的是 “覆蓋索引”

什麼是覆蓋索引:

當sql語句的所求查詢字段(select列)和查詢條件字段(where子句)全都包含在一個索引中 (聯合索引),可以直接使用索引查詢而不需要回表。這就是覆蓋索引,通過使用覆蓋索引,可以減少搜索樹的次數,這就是 覆蓋索引,在了解覆蓋索引之前,我們先來看看什麼是索引。

三、什麼是索引?

我們有一個主鍵列為id的表,表中有字段name,並且在name上有索引

表中 t_user 值分別為(1,張一)、(2,張二)、(3,張三)、(4,張四)、(5,張五)

表結構如下:

mysql> create table t_user (
id bigint(20) not null auto_increment ,
name varchar(255) not null,
primary key (id),
index index_name (name) using btree)
engine=innodb
default character set=utf8 collate=utf8_general_ci

兩棵樹的示例示意圖如下:

從圖中不難看出,根據恭弘=叶 恭弘子節點的內容,索引類型分為主鍵索引和二級索引(非主鍵索引)。

主鍵索引: 主鍵索引的恭弘=叶 恭弘子節點保存着主鍵即對應行的全部數據。在InnoDB里,主鍵索引也被稱為聚簇索引(clustered index)。

二級索引(非主鍵索引): 二級索引樹中的恭弘=叶 恭弘子結點保存着索引值和主鍵值,當使用二級索引進行查詢時,需要進行回表操作。在InnoDB里,非主鍵索引也被稱為二級索引(secondary index)

通過上面所講的,我們來看看如何通過sql語句來區分 主鍵索引和普通索引的查詢

  • select * from t_user where id=1 即主鍵查詢方式,則只需要搜索id這棵B+樹
  • select * from t_user where name=張三 即普通索引查詢方式,則需要先搜索name索引樹,得到id的值為3,再到id索引樹搜索一次。這個過程稱為回表

也就是說,基於二級索引(非主鍵索引)的查詢需要多掃描一棵索引樹。因此,我們在應用中應該盡量使用主鍵查詢。

看到這裏如果你看懂了上面的介紹,那麼這裏你會有一個疑問,我直接用in id不就好了嗎,建立id主鍵索引,就可以不用回表了,速度不也就提升了嗎?

如果是 5.5 之前的版本確實不會走索引的,在 5.5 之後的版本,MySQL 做了優化。MySQL 在 2010 年發布 5.5 版本中,優化器對 in 操作符可以自動完成優化,針對建立了索引的列可以使用索引,沒有索引的列還是會走全表掃描,也就是我們所說的回表。

那麼,有沒有可能經過索引優化,避免回表過程呢?答應是有的

四、覆蓋索引

sql語句如下,其中id自增,name為索引:

mysql> create table t_user (
id bigint(20) not null auto_increment ,
name varchar(255) not null,
password varchar(255) ,
primary key (id),
engine=innodb
default character set=utf8 collate=utf8_general_ci

比如有這麼兩句sql

語句A: select id from user_table where name= '張三'
語句B: select password from user_table where name= '張三'

語句A: 因為 name索引樹 的恭弘=叶 恭弘子結點上保存有 name和id的值 ,所以通過 name索引樹 查找到id后,因此可以直接提供查詢結果,不需要回表,也就是說,在這個查詢裏面,索引name 已經 “覆蓋了” 我們的查詢需求,我們稱為 覆蓋索引

語句B: name索引樹 上 找到 name=’張三’ 對應的主鍵id, 通過回表在主鍵索引樹上找到滿足條件的數據

因此我們可以得知,當sql語句的所求查詢字段(select列)和查詢條件字段(where子句)全都包含在一個索引中(聯合索引),可以直接使用索引查詢而不需要回表。這就是覆蓋索引

例如上面的語句B是一個高頻查詢的語句,我們可以建立(name,password)的聯合索引,這樣,查詢的時候就不需要再去回表操作了,可以提高查詢效率。

所以關於上面的面試題我們就可以得出,使用聯合索引就可以很好的回答面試官的問題(id,name,password)這樣的聯合索引就可以調用到覆蓋索引,可以減少樹的搜索次數,不再需要回表查整行記錄,顯著提升查詢性能,所以使用覆蓋索引是一個常用的性能優化手段。

說到了聯合索引我們就不得不說聯合索引中最重要的匹配原則,最左匹配原則了

五、最左匹配原則

最左前綴匹配原則,是非常重要的原則,mysql會從左向右進行匹配。

例如我們定義了(name,password)兩個聯合索引字段,我們 使用 where name = '張三' and password = '2' 索引可以生效的,當我們是顛倒了他們的順序 使用where password = '1' and name = '王五',索引同樣也是可以生效的,在mysql查詢優化器會判斷糾正這條sql語句該以什麼樣的順序執行效率最高,最後才生成真正的執行計劃,我們能盡量的利用到索引時的查詢順序效率最高,所以mysql查詢優化器會最終以這種順序(where name = '張三' and password = '2' )進行查詢執行,就類似 我們的 order by name,password這樣一種排序規則,先對張三的用戶進行查詢排序,在對password進行處理

比如我們要查詢姓張的用戶,我們的條件查詢可以為 "where name like ‘張%’",但是不能是 where name like '%張%'或者是 where name like '%張',因為索引可以用於查詢條件字段為索引字段,根據字段值必須是最左若干個字符進行的模糊查詢,也就是需要是 ‘張%’ 這樣的添加才可以使用。

索引的復用能力。因為可以支持最左前綴,所以當已經有了(name,password)這個聯合索引后,一般就不需要單獨在name上建立索引了。因此,第一原則是,如果通過調整順序,可以少維護一個索引,那麼這個順序往往就是需要優先考慮採用的。

如果既有聯合查詢,又有基於name,password各自的查詢呢?查詢條件裏面只有password的語句,是無法使用(name,password)這個聯合索引的,這時候你需要同時維護(name,password)、(password) 這兩個索引。

創建索引時,我們也要考慮空間代價,使用較少的空間來創建索引
假設我們現在不需要通過name查詢password了,需要通過name查詢age或通過age查詢name

  • 1.(name,age)聯合索引+age單字段索引
  • 2.(age,name)聯合索引+name單字段索引

name字段是比age字段大的,所以,選擇第一種,索引佔用空間較小的一個

六、索引下推

上面我們說到滿足最左前綴原則的時候,最左前綴可以用於在索引中定位記錄。那麼如果那些不符合最左前綴的部分,會怎麼樣呢?

如果現在有一個需求:檢索出表中“名字第一個字是張,而且沒有刪除的信息(is_del = 1)。SQL語句如下:

mysql> select * from t_user where name like ‘張%’ and is_del=1

在MySQL 5.6之前,只能從匹配的位置一個個回表。到主鍵索引上找出數據行,再對比字段值

在MySQL 5.6中 引入的索引下推優化(index condition pushdown), 可以在索引遍歷過程中,對索引中包含的字段先做判斷,直接過濾掉不滿足條件的記錄,減少回表次數

根據(username,is_del)聯合索引查詢所有滿足名稱以“張”開頭的索引,然後回表查詢出相應的全行數據,然後再篩選出未刪除的用戶數據。過程如下圖:

每一個虛線箭頭表示回表一次
圖一(無索引下推執行流程)

每一個虛線箭頭表示回表一次
圖二(索引下推執行流程)

圖1跟圖2的區別是,InnoDB在(name,is_del)索引內部就判斷了數據是否邏輯刪除,對於邏輯刪除的記錄,直接判斷並跳過。在我們的這個例子中,只需要對ID1、ID4這兩條記錄回表取數據判斷,就只需要回表2次

mysql默認啟用索引下推,我們也可以通過修改系統變量optimizer_switch的index_condition_pushdown標誌來控制SET optimizer_switch = 'index_condition_pushdown=off';

我們也需要注意:

  • innodb引擎的表,索引下推只能用於二級索引,因為innodb的主鍵索引樹恭弘=叶 恭弘子結點上保存的是全行數據,所以這個時候索引下推並不會起到減少查詢全行數據的效果
  • 索引下推一般可用於所求查詢字段(select列)不是/不全是聯合索引的字段,查詢條件為多條件查詢且查詢條件子句(where/order by)字段全是聯合索引

六、小結

今天的內容就到這裏了,我們在上面描述了數據庫索引的概念,包括了覆蓋索引、聯合索引、索引下推,那麼下次如果有面試官問你剛開始的問題,相信大家可以好好的回(dui)答(ta)一下面試官了,在sql優化中,減少回表次數,或者直接使用覆蓋索引是比較重要的,盡量少地訪問資源也是數據庫設計的重要原則之一,謝謝大家,加油~

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

台北網頁設計公司這麼多該如何選擇?

※智慧手機時代的來臨,RWD網頁設計為架站首選

※評比南投搬家公司費用收費行情懶人包大公開

※回頭車貨運收費標準

網頁設計最專業,超強功能平台可客製化

※別再煩惱如何寫文案,掌握八大原則!