sql三张表的搜索要满足5种搜索条件的模糊搜索_面试三轮我倒在了一道 SQL 题上……| 原力计划...
責(zé)編 | 王曉曼
出品 | CSDN博客前言
最近小農(nóng)在找工作,因?yàn)榻衲暌咔榈奶厥庠?#xff0c;導(dǎo)致工作不是特別好找,所以一旦有面試電話,如果可以,都會(huì)去試一試,剛好接到一個(gè)面試邀請(qǐng),感覺公司還不錯(cuò),于是就確定了面試時(shí)間,準(zhǔn)備了一下就去面試了。第一輪面試是小組組長(zhǎng)面試,通過。第二輪是經(jīng)理面試也是通過了。第三輪總監(jiān)面試,前面都還有模有樣,突然畫風(fēng)一轉(zhuǎn),面試官說:“問你最后一個(gè)問題”。面試官:10W條數(shù)據(jù),我要從其中查出100條不連續(xù)的數(shù)據(jù),給你ID,來查Name和Password進(jìn)行展示,如何才能高性能的去使用?我:在ID上建立聚簇索引,然后用 in id 來縮小表搜索范圍,最后 使用條件查詢小于最大ID,大于最小ID,這樣可以讓SQL速度能夠比較快的展示,雖然In的性能比較低心理活動(dòng):雕蟲小技,還最后一個(gè)問題,這樣的問題再來一個(gè)吧。只見面試官緊鎖眉頭,與我心里期待的表情有點(diǎn)不一樣啊,難道是哪個(gè)環(huán)節(jié)出了問題?面試官:這樣的性能不能達(dá)到最優(yōu)化的程度,而且如果我給你的最小ID是1,最大ID是100000呢?你這就有點(diǎn)杠精了啊,那行吧,你是面試官你說了算我:既然ID已經(jīng)給出來了,而且只查詢兩個(gè)字段,用聚簇索引那么查詢數(shù)據(jù)是很快的,用in id應(yīng)該是可以的。面試官:好的,回去等通知吧我。。。。。后知
于是回去后,查詢資料,才知道原來面試官,真正想考的是“覆蓋索引”。什么是覆蓋索引:當(dāng)SQL語句的所求查詢字段(Select列)和查詢條件字段(Where子句)全都包含在一個(gè)索引中(聯(lián)合索引),可以直接使用索引查詢而不需要回表。這就是覆蓋索引,通過使用覆蓋索引,可以減少搜索樹的次數(shù),這就是覆蓋索引,在了解覆蓋索引之前,我們先來看看什么是索引。什么是索引?
我們有一個(gè)主鍵列為ID的表,表中有字段name,并且在name上有索引。表中 t_user? 值分別為(1,張一)、(2,張二)、(3,張三)、(4,張四)、(5,張五)表結(jié)構(gòu)如下:
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=innodbdefault?character?set=utf8?collate=utf8_general_ci
————————————————
版權(quán)聲明:本文為CSDN博主「牧小農(nóng)的夏天」的原創(chuàng)文章,遵循CC 4.0?BY-SA版權(quán)協(xié)議,轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。
原文鏈接:https://blog.csdn.net/qq_14996421/article/details/106098595
- select* from t_user where id=1?? 即主鍵查詢方式,則只需要搜索ID這棵B+樹
- select* from t_user where name=張三???? 即普通索引查詢方式,則需要先搜索name索引樹,得到ID的值為3,再ID引樹搜索一次。這個(gè)過程稱為回表
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=innodbdefault?character?set=utf8?collate=utf8_general_ci
————————————————
版權(quán)聲明:本文為CSDN博主「牧小農(nóng)的夏天」的原創(chuàng)文章,遵循CC 4.0?BY-SA版權(quán)協(xié)議,轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。
原文鏈接:https://blog.csdn.net/qq_14996421/article/details/106098595
最左前綴匹配原則,是非常重要的原則,mysql會(huì)從左向右進(jìn)行匹配。例如我們定義了(name,password)兩個(gè)聯(lián)合索引字段,我們使用 where name = '張三' and password = '2'索引可以生效的,當(dāng)我們是顛倒了他們的順序 使用wherepassword = '1' and name = '王五',索引同樣也是可以生效的,在mysql查詢優(yōu)化器會(huì)判斷糾正這條SQL語句該以什么樣的順序執(zhí)行效率最高,最后才生成真正的執(zhí)行計(jì)劃,我們能盡量的利用到索引時(shí)的查詢順序效率最高,所以mysql查詢優(yōu)化器會(huì)最終以這種順序(where name = '張三' and password = '2')進(jìn)行查詢執(zhí)行,就類似 我們的 order byname,password這樣一種排序規(guī)則,先對(duì)張三的用戶進(jìn)行查詢排序,在對(duì)password進(jìn)行處理。比如我們要查詢姓張的用戶,我們的條件查詢可以為 "where name like ‘張%’",但是不能是 where name like '%張%'或者是 where name like '%張',因?yàn)樗饕梢杂糜诓樵儣l件字段為索引字段,根據(jù)字段值必須是最左若干個(gè)字符進(jìn)行的模糊查詢,也就是需要是 ‘張%’ 這樣的添加才可以使用。索引的復(fù)用能力。因?yàn)榭梢灾С肿钭笄熬Y,所以當(dāng)已經(jīng)有了(name,password)這個(gè)聯(lián)合索引后,一般就不需要單獨(dú)在name上建立索引了。因此,第一原則是,如果通過調(diào)整順序,可以少維護(hù)一個(gè)索引,那么這個(gè)順序往往就是需要優(yōu)先考慮采用的。如果既有聯(lián)合查詢,又有基于name,password各自的查詢呢?查詢條件里面只有password的語句,是無法使用(name,password)這個(gè)聯(lián)合索引的,這時(shí)候你需要同時(shí)維護(hù)(name,password)、(password) 這兩個(gè)索引。創(chuàng)建索引時(shí),我們也要考慮空間代價(jià),使用較少的空間來創(chuàng)建索引。假設(shè)我們現(xiàn)在不需要通過Name查詢Password了,需要通過Nname查詢Age或通過Age查詢Name。1.(name,age)聯(lián)合索引+age單字段索引2.(age,name)聯(lián)合索引+name單字段索引Name字段是比Age字段大的,所以,選擇第一種,索引占用空間較小的一個(gè)索引下推
上面我們說到滿足最左前綴原則的時(shí)候,最左前綴可以用于在索引中定位記錄。那么如果那些不符合最左前綴的部分,會(huì)怎么樣呢?如果現(xiàn)在有一個(gè)需求:檢索出表中“名字第一個(gè)字是張,而且沒有刪除的信息(is_del = 1)。SQL語句如下:mysql>?select?*?from?t_user?where?name?like?‘張%’?and?is_del=1在MySQL5.6之前,只能從匹配的位置一個(gè)個(gè)回表。到主鍵索引上找出數(shù)據(jù)行,再對(duì)比字段值。在MySQL 5.6中 引入的索引下推優(yōu)化(index conditionpushdown), 可以在索引遍歷過程中,對(duì)索引中包含的字段先做判斷,直接過濾掉不滿足條件的記錄,減少回表次數(shù)根據(jù)(username,is_del)聯(lián)合索引查詢所有滿足名稱以“張”開頭的索引,然后回表查詢出相應(yīng)的全行數(shù)據(jù),然后再篩選出未刪除的用戶數(shù)據(jù)。過程如下圖:每一個(gè)虛線箭頭表示回表一次圖一(無索引下推執(zhí)行流程)每一個(gè)虛線箭頭表示回表一次圖二(索引下推執(zhí)行流程)圖1跟圖2的區(qū)別是,InnoDB在(name,is_del)索引內(nèi)部就判斷了數(shù)據(jù)是否邏輯刪除,對(duì)于邏輯刪除的記錄,直接判斷并跳過。在我們的這個(gè)例子中,只需要對(duì)ID1、ID4這兩條記錄回表取數(shù)據(jù)判斷,就只需要回表2次。mysql默認(rèn)啟用索引下推,我們也可以通過修改系統(tǒng)變量optimizer_switch的index_condition_pushdown標(biāo)志來控制SEToptimizer_switch = 'index_condition_pushdown=off';我們也需要注意:
- Innodb引擎的表,索引下推只能用于二級(jí)索引,因?yàn)閕nnodb的主鍵索引樹葉子結(jié)點(diǎn)上保存的是全行數(shù)據(jù),所以這個(gè)時(shí)候索引下推并不會(huì)起到減少查詢?nèi)袛?shù)據(jù)的效果。
- 索引下推一般可用于所求查詢字段(select列)不是/不全是聯(lián)合索引的字段,查詢條件為多條件查詢且查詢條件子句(where/order by)字段全是聯(lián)合索引。
今天的內(nèi)容就到這里了,我們?cè)谏厦婷枋隽藬?shù)據(jù)庫索引的概念,包括了覆蓋索引、聯(lián)合索引、索引下推,那么下次如果有面試官問你剛開始的問題,相信大家可以好好的回(dui)答(ta)一下面試官了,在SQL優(yōu)化中,減少回表次數(shù),或者直接使用覆蓋索引是比較重要的,盡量少地訪問資源也是數(shù)據(jù)庫設(shè)計(jì)的重要原則之一,謝謝大家,加油~版權(quán)聲明:本文為CSDN博主「牧小農(nóng)的夏天」的原創(chuàng)文章,遵循CC 4.0 BY-SA版權(quán)協(xié)議,轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。原文鏈接:https://blog.csdn.net/qq_14996421/article/details/106098595
更多精彩推薦
?華為全球分析師大會(huì):HMS Core全球開發(fā)者應(yīng)用集成的數(shù)量加速增長(zhǎng),打造全場(chǎng)景智慧體驗(yàn)
?騰訊人均月薪 8 萬,恍恍惚惚,又被平均了?
?200 萬年薪請(qǐng)不到!清華姚班到底有多牛?| 原力計(jì)劃
?量子計(jì)算與AI“雙拳”出擊,他們鎖定38種潛在抗疫藥物
?我們已經(jīng)不用AOP做操作日志了!| 原力計(jì)劃
?國(guó)外這三位帥小伙,居然搞了個(gè)用比特幣付款、無人機(jī)運(yùn)送的水培沙拉項(xiàng)目?
你點(diǎn)的每個(gè)“在看”,我都認(rèn)真當(dāng)成了喜歡總結(jié)
以上是生活随笔為你收集整理的sql三张表的搜索要满足5种搜索条件的模糊搜索_面试三轮我倒在了一道 SQL 题上……| 原力计划...的全部?jī)?nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: sql嵌套查询返回多个字段_list4
- 下一篇: python wasm_Python-p