摘要：在后端開發(fā)的工作中如何輕松高效地設(shè)計(jì)大量數(shù)據(jù)庫索引呢通過下面這四步，分鐘后你就再也不會(huì)為數(shù)據(jù)庫的索引設(shè)計(jì)而發(fā)愁了。順暢地閱讀這篇文章需要了解數(shù)據(jù)庫索引的組織方式，如果你還不熟悉的話，可以通過另一篇文章來快速了解一下數(shù)據(jù)庫索引融會(huì)貫通。

在后端開發(fā)的工作中如何輕松、高效地設(shè)計(jì)大量數(shù)據(jù)庫索引呢？通過下面這四步，20分鐘后你就再也不會(huì)為數(shù)據(jù)庫的索引設(shè)計(jì)而發(fā)愁了。

順暢地閱讀這篇文章需要了解數(shù)據(jù)庫索引的組織方式，如果你還不熟悉的話，可以通過另一篇文章來快速了解一下——數(shù)據(jù)庫索引融會(huì)貫通。

這篇文章是一系列數(shù)據(jù)庫索引文章中的第三篇，這個(gè)系列包括了下面四篇文章：

數(shù)據(jù)庫索引是什么？新華字典來幫你 —— 理解

數(shù)據(jù)庫索引融會(huì)貫通 —— 深入

20分鐘數(shù)據(jù)庫索引設(shè)計(jì)實(shí)戰(zhàn) —— 實(shí)戰(zhàn)

數(shù)據(jù)庫索引為什么用B+樹實(shí)現(xiàn)？ —— 擴(kuò)展

這一系列涵蓋了數(shù)據(jù)庫索引從理論到實(shí)踐的一系列知識(shí)，一站式解決了從理解到融會(huì)貫通的全過程，相信每一篇文章都可以給你帶來更深入的體驗(yàn)。

我們設(shè)計(jì)索引的目的主要是為了加快查詢，所以，設(shè)計(jì)索引的第一步是整理需要用到的查詢條件，也就是我們會(huì)在where子句、join連接條件中使用的字段。一般來說會(huì)整理程序中除了insert語句之外的所有SQL語句，按不同的表分別整理出每張表上的查詢條件。也可以根據(jù)對業(yè)務(wù)的理解添加一些暫時(shí)還沒有使用到的查詢條件。

對索引的設(shè)計(jì)一般會(huì)逐表進(jìn)行，所以按數(shù)據(jù)表收集查詢條件可以方便后面步驟的執(zhí)行。

整理出所有查詢條件之后，我們需要分析出每個(gè)字段的可選擇性，那么什么是可選擇性呢？

字段的可選擇性指的就是字段的值的區(qū)分度，例如一張表中保存了用戶的手機(jī)號、性別、姓名、年齡這幾個(gè)字段，且一個(gè)手機(jī)號只能注冊一個(gè)用戶。在這種情況下，像手機(jī)號這種唯一的字段就是可選擇性最高的一種情況；而年齡雖然有幾十種可能，但是區(qū)分度就沒有手機(jī)號那么大了；性別這樣的字段則只有幾種可能，所以可選擇性最差。所以俺可選擇性從高到低排列就是：手機(jī)號 > 年齡 > 性別。

但是不同字段的值分布是不同的，有一些值的數(shù)量是大致均勻的，例如性別為男和女的值數(shù)量可能就差別不大，但是像年齡超過100歲這樣的記錄就非常少了。所以對于年齡這個(gè)字段，20-30這樣的值就是可選擇性很小的，因?yàn)槊恳粋€(gè)年齡都有非常多的記錄；但是像100這樣的值，那它的可選擇性就非常高了。

如果我們在表中添加了一個(gè)字段表示用戶是否是管理員，那么在查詢網(wǎng)站的管理員信息列表時(shí)，這個(gè)字段的可選擇性就非常高。但是如果我們要查詢的是非管理員信息列表時(shí)，這個(gè)字段的可選擇性就非常低了。

從經(jīng)驗(yàn)上來說，我們會(huì)把可選擇性高的字段放到前面，可選擇性低的字段放在后面，如果可選擇性非常低，一般不會(huì)把這樣的字段放到索引里。

雖然索引可以加快查詢的效率，但是索引越多就會(huì)導(dǎo)致插入和更新數(shù)據(jù)的成本變高，因?yàn)樗饕欠珠_存儲(chǔ)的，所有數(shù)據(jù)的插入和更新操作都要對相關(guān)的索引進(jìn)行修改。所以設(shè)計(jì)索引時(shí)還需要控制索引的數(shù)量，不能盲目地增加索引。

一般我們會(huì)根據(jù)最左匹配原則來合并查詢條件，盡可能讓不同的查詢條件使用同一個(gè)索引。例如有兩個(gè)查詢條件where a = 1 and b = 1和where b = 1，那么我們就可以創(chuàng)建一個(gè)索引idx_eg(b, a)來同時(shí)服務(wù)兩個(gè)查詢條件。

同時(shí)，因?yàn)榉秶鷹l件會(huì)終止使用索引中后續(xù)的字段，所以對于使用范圍條件查詢的字段我們也會(huì)盡可能放在索引的后面。

最后，我們會(huì)考慮是否需要使用全覆蓋索引，因?yàn)?strong>全覆蓋索引沒有回表的開銷，效率會(huì)更高。所以一般我們會(huì)在回表成本特別高的情況下考慮是否使用全覆蓋索引，例如根據(jù)索引字段篩選后的結(jié)果需要返回其他字段或者使用其他字段做進(jìn)一步篩選的情況。

例如，我們有一張用戶表，其中有年齡、姓名、手機(jī)號三個(gè)字段。我們需要查詢在指定年齡的所有用戶的姓名，已有索引idx_age_name(年齡, 姓名)，目前我們使用下面這樣的查詢語句進(jìn)行查詢：

SELECT *
FROM 用戶表
WHERE 年齡 = ?;

一般情況下，將一個(gè)索引優(yōu)化為全覆蓋索引有兩種方式：

增加索引中的字段，讓索引字段覆蓋SQL語句中使用的所有字段

在這個(gè)例子中，我們可以創(chuàng)建一個(gè)同時(shí)包含所有字段的索引idx_all(年齡, 姓名, 手機(jī)號)，以此提高查詢的效率。

減少SQL語句中使用的字段，使SQL需要的字段都包含在現(xiàn)有索引中

在這個(gè)例子中，其實(shí)更好的方法是將SELECT子句修改為SELECT 姓名，因?yàn)槲覀兊男枨笾皇遣樵冇脩舻男彰?，并不需要手機(jī)號字段，去掉SELECT子句多余的字段不僅能夠滿足我們的需求，而且也不用對索引做修改。