程序員面試技巧：如何通過索引說數(shù)據(jù)庫優(yōu)化能力

2017.09.25

本文選自《Java Web輕量級開發(fā)面試教程》一書

如果我們需要招個Java方面的高級程序員，一方面看年限（本科3年），具體到數(shù)據(jù)庫方面的技能要求，包括如下三個方面：

第一，是否會基本的增刪改查，存儲過程等技能，是否會用些groupby,having,distinct,exist,in,with等高級點的語句。這點一般都沒問題，甚至一個剛畢業(yè)的大學(xué)生或工作經(jīng)驗2年之內(nèi)的初級程序員也沒問題，也就是說，這個是高級程序員必備的，你會了是應(yīng)該，不會甚至不及畢業(yè)生。

第二，有沒有設(shè)計表的經(jīng)驗，這方面的面試技能也已經(jīng)在數(shù)據(jù)庫方面的面試技巧，如何從建表方面展示自己能力里說了，關(guān)鍵一點，你得結(jié)合實際需求來說。

第三也是關(guān)鍵一點，在數(shù)據(jù)庫優(yōu)化方面，你是否有相關(guān)經(jīng)驗。

這是個開放性的問題，大神們知道，可以從分區(qū)，根據(jù)執(zhí)行計劃優(yōu)化等很多方面來考慮，對高級程序員，我的期望是你至少能說點索引相關(guān)的。但根據(jù)我的面試經(jīng)驗，很少有候選人（特別是高級程序員候選人）能比較深入地敘述。

開場白說了不少，下面進入正文。

索引是數(shù)據(jù)庫優(yōu)化所必需的工具，在面試的時候一般不會問概念性的問題，因為大家都能從教科書上找到答案，所以一般會問以下兩方面的問題：

①索引有什么代價？哪些場景下你需要建索引？或者有時候反過來問，哪些場景下不推薦建索引。

②建好索引之后，怎么才能最高效地利用索引？或者反過來問，請說出一個無法有效利用已建索引的案例。

從結(jié)構(gòu)上來看，索引好比是一棵B樹（也叫B*或者B+），假設(shè)學(xué)生表里只有學(xué)生ID和姓名兩列，該學(xué)生表里有1000個學(xué)生，學(xué)號分別從1到1000，如果針對ID建立索引，大致的結(jié)構(gòu)如下圖所示。

當(dāng)然，在實際的數(shù)據(jù)庫系統(tǒng)中，索引要比這個復(fù)雜得多，但從這個圖里，我們能大致看出索引的工作原理。

索引建好后，如果我們要查找ID為111的學(xué)生，則數(shù)據(jù)庫系統(tǒng)就會走索引，從圖2.1中我們可以看到，根據(jù)根節(jié)點的指引，會找到第二層從左往右第二個數(shù)據(jù)塊，以此類推，會在第四層里得到ID為111的物理地址，然后直接從硬盤里找數(shù)據(jù)。

反過來，如果沒有建索引，數(shù)據(jù)庫系統(tǒng)可能就要從一個大的范圍里逐一定位查找，效率就沒這么高了。

索引的好處大家已經(jīng)看到了，那么為了得到這個“查詢效率高”的好處，我們要付出了什么樣的代價呢？

1 索引需要占硬盤空間，這是空間方面的代價。
2 一旦插入新的數(shù)據(jù)，就需要重新建索引，這是時間上的代價。

關(guān)于索引性能問題，我會細問，你建索引的表規(guī)模多少？不少人直接告訴我表就幾千條，我或者問，索引有什么代價？不少回答是索引是只有好處沒壞處的，也就是說，可以隨便建。

對此我們來詳細分析下（也就是大家在面試時需要說的）：

場景一，數(shù)據(jù)表規(guī)模不大，就幾千行，即使不建索引，查詢語句的返回時間也不長，這時建索引的意義就不大。當(dāng)然，若就幾千行，索引所占的空間也不多，所以這種情況下，頂多屬于“性價比”不高。

場景二，某個商品表里有幾百萬條商品信息，同時每天會在一個時間點，往其中更新大概十萬條左右的商品信息，現(xiàn)在用where語句查詢特定商品時（比如wherename=‘XXX’）速度很慢。為了提升查詢效率可以建索引，但當(dāng)每天更新數(shù)據(jù)時，又會重建索引，這是要耗費時間的。這時就需要綜合考慮，甚至可以在更新前刪除索引，更新后再重建。

場景三，從上圖中可以看到，因為在數(shù)據(jù)表里ID值都不相同，所以索引能發(fā)揮出比較大的作用。相反，如果某個字段重復(fù)率很高，如性別字段，或者某個字段大多數(shù)值是空（null），那么不建議對該字段建索引。

請大家記住，一定是有業(yè)務(wù)需求了才會建索引。比如在一個商品表里，我們經(jīng)常要根據(jù)name做查詢，如果沒有索引，查詢速度會很慢，這時就需要建索引。但在項目開發(fā)中，如果不經(jīng)常根據(jù)商品編號查詢，那么就沒必要對編號建索引。

最后再強調(diào)一次，建索引是要付出代價的，沒事別亂建著玩，同時在一個表上也不能建太多的索引。

下面說下索引建好了該怎么用？畢竟大家花了不少時間和空間代價建了索引，至少得回本吧？

如果出現(xiàn)一些不好的SQL語句，那么索引就白建了。下面通過一些具體的例子來看索引的正確用法。

①語句一：selectnamefrom商品表。不會用到索引，因為沒有where語句。

②語句二：select*from商品表wherename=‘Java書’，會用到索引，如果項目里經(jīng)常用到name來查詢，且商品表的數(shù)據(jù)量很大，而name值的重復(fù)率又不高，那么建議建索引。

③語句三：select*from商品表wherenamelike‘Java%’這是個模糊查詢，會用到索引，請大家記住，用like進行模糊查詢時，如果第一個就是模糊的匹配符，比如wherenamelike‘%java’，那么在查詢時不會走索引。在其他情況下，不論用了多少個%，也不論%的位置，只要不出現(xiàn)在第一個位置，那么都能用到索引。

學(xué)生成績表里有兩個字段：姓名和成績?，F(xiàn)在對成績這個整數(shù)類型的字段建索引。

①第一種情況，當(dāng)數(shù)字型字段遇到非等值操作符時，無法用到索引。比如：

selectnamefrom學(xué)生成績表where成績>95,一旦出現(xiàn)大于符號，就不能用到索引，為了用到索引，我們應(yīng)該改一下SQL語句里的where從句：where成績in(96,97,98,99,100)

②第二種情況，如果對索引字段進行了某種左值操作，那么無法用到索引。

能用到索引的寫法：selectnamefrom學(xué)生成績表where成績=60

不能用到索引的寫法：selectnamefrom學(xué)生成績表where成績+40=100

③第三種情況，如果對索引字段進行了函數(shù)操作，那么無法用到索引。

比如SQL語句：select*from商品表wheresubstr(name)=‘J’，我們希望查詢商品名首字母是J的記錄，可一旦針對name使用函數(shù)，即使name字段上有索引，也無法用到。

關(guān)于索引，當(dāng)然還有位圖索引和復(fù)合索引等，如果大家要應(yīng)聘更高級的崗位（比如有5年經(jīng)驗了），那么就不能止步于此了，但根據(jù)我的面試經(jīng)驗，上述關(guān)于索引的說辭對工作經(jīng)驗3年以下的候選人是有幫助的。

其實我知道，不少程序員平時用過索引，但不知道怎么說，這很吃虧。對于高級程序員而言，如果你這都說不好，那么你的能力比初級的要高多少？對于初級程序員而言，如果你掌握了，而且能在面試中很好地說，那么你和同等能力的人相比，就很占優(yōu)勢。