目錄支持多種類(lèi)型的過(guò)濾避免多個(gè)范圍查詢(xún)優(yōu)化排序

理解索引概念最簡(jiǎn)單的方式是通過(guò)一個(gè)案例來(lái)進(jìn)行，以下就是這樣的一個(gè)案例。

假設(shè)我們需要設(shè)計(jì)一個(gè)在線(xiàn)的約會(huì)網(wǎng)站，這個(gè)網(wǎng)站的用戶(hù)資料有許多列，例如國(guó)籍、省份、城市、性別、年齡、眼睛顏色等等。這個(gè)網(wǎng)站必須支持通過(guò)多種組合方式搜索用戶(hù)資料。同時(shí)，也需要支持支持排序和根據(jù)用戶(hù)最近在線(xiàn)時(shí)間和其他用戶(hù)的評(píng)價(jià)返回有限的結(jié)果等等。對(duì)于這種復(fù)雜場(chǎng)景我們?nèi)绾卧O(shè)計(jì)索引？

有點(diǎn)奇怪，首先要做的事情是要決定我們是否必須使用索引排序，或者檢索后再排序是否能夠接受。索引排序限制了索引和查詢(xún)構(gòu)建的方式。例如，在WHERE age BETWEEN 18 AND 25這樣的查詢(xún)條件和基于其他用戶(hù)評(píng)價(jià)排序的場(chǎng)景中，我們不能使用同一個(gè)索引。如果MySQL在范圍查詢(xún)中使用了一個(gè)索引，那就沒(méi)法在排序中使用另一個(gè)索引。假設(shè)這是一個(gè)最常用的WHERE條件，同時(shí)我們還需要支持大多數(shù)查詢(xún)都可以排序。

支持多種類(lèi)型的過(guò)濾

現(xiàn)在我們需要看看哪些列的值比較分散以及哪些列在WHERE條件中最常出現(xiàn)。數(shù)據(jù)列值比較分散的篩選性很好。這通常會(huì)是一個(gè)好事情，因?yàn)檫@讓MySQL可以將高效過(guò)濾掉不相關(guān)的數(shù)據(jù)行。

國(guó)籍列可能篩選性不太好，但卻可能是最常查詢(xún)的。性別列通常不具備篩選性，但卻也經(jīng)常用于查詢(xún)。基于這樣的認(rèn)識(shí)，我們?yōu)樵S多不同的列的組合創(chuàng)建了一系列的索引，這些索引使用(sex, country)開(kāi)頭。

傳統(tǒng)的認(rèn)知是對(duì)于低篩選性的列構(gòu)建索引是沒(méi)用的。那我們?yōu)槭裁匆诿總€(gè)索引開(kāi)頭都加上不具篩選性的列？ 我們有兩個(gè)理由這么做。第一個(gè)理由是，如前所述，基本每個(gè)查詢(xún)都會(huì)使用性別。我們甚至設(shè)計(jì)了用戶(hù)一次只能搜索一個(gè)性別。但更重要的是，增加這樣的列并沒(méi)有多少缺點(diǎn)，因?yàn)槲覀兪褂昧艘粋€(gè)小招數(shù)。

這是我們的招數(shù)：即便不限制性別查詢(xún)，我們也能夠保證在WHERE語(yǔ)句中加上AND sex IN(’m’, ’f’)讓索引生效。這不會(huì)過(guò)濾掉我們所需要的行，因此與WHERE語(yǔ)句中不包含性別作用相同。然而，因?yàn)镸ySQL會(huì)在更多列的索引中前置這個(gè)列，我們需要包含這個(gè)列。這個(gè)招術(shù)在這樣的場(chǎng)景下有效，但是如果是這個(gè)列具有很多不同的值，那反而不起作用，這是因?yàn)檫@會(huì)導(dǎo)致IN()中的列過(guò)多。

這個(gè)例子闡述了一個(gè)基本的原則：在數(shù)據(jù)表設(shè)計(jì)上保留所有的選項(xiàng)。當(dāng)你設(shè)計(jì)索引的時(shí)候，不要只想著那種查詢(xún)中的那類(lèi)索引，也同時(shí)考慮優(yōu)化查詢(xún)。當(dāng)你需要一個(gè)索引卻發(fā)現(xiàn)其他查詢(xún)可能會(huì)受其影響，你應(yīng)該先問(wèn)問(wèn)自己能否改變查詢(xún)。你應(yīng)該同時(shí)優(yōu)化查詢(xún)和索引去找到解決之道。你不一定需要設(shè)計(jì)完美的索引。

接下來(lái)，我們需要考慮可能用到的其他組合的WHERE條件，然后考慮其中的哪些組合在沒(méi)有合理索引的情況下會(huì)變慢。(sex, country, age)這樣的索引是很明顯的選擇，但我們也可能需要(sex, country, region, age)和(sex, country, region, city, age)這樣的索引。

這會(huì)導(dǎo)致需要建立很多的索引。如果我們能夠重復(fù)利用索引，那就不會(huì)產(chǎn)生過(guò)多的組合。我們可以使用IN()這種小招數(shù)來(lái)去掉(sex, country, age)和(sex, country, region, age)索引。如果這些列在搜索表單中沒(méi)有指定，我們可以使用國(guó)家清單、地區(qū)清單來(lái)保證滿(mǎn)足索引前置的約束（全部國(guó)家，全部地區(qū)和全部性別的組合可能很多）。

這些索引會(huì)滿(mǎn)足指定的大部分搜索查詢(xún)，但我們?nèi)绾卧O(shè)計(jì)那些不那么常見(jiàn)的篩選，例如上傳了圖片(has_pictures)，眼睛顏色（eye_color），頭發(fā)顏色（hair_color）和教育水平（education）？如果這些列不是那么具有篩選性并且不那么常用，我們可以直接跳過(guò)他們，讓MySQL去掃描額外的一些數(shù)據(jù)行。相應(yīng)地，我們可以在age列前增加他們，并且使用IN()技巧去提前描述以處理那種這些列沒(méi)有指定的情況。

你也許注意到我們將age放到了索引的最后面。為什么要特別處理這個(gè)列？我們?cè)谠噲D保證MySQL能夠盡可能多地利用索引列。由于MySQL使用最左匹配規(guī)則，直到遇到第一個(gè)范圍查詢(xún)條件。所有我們提到的列都可以在WHERE語(yǔ)句中使用相等條件，但年齡(age)大概率是范圍查詢(xún)。

我們也能夠?qū)⒎秶樵?xún)改為清單使用IN查詢(xún)，例如age IN(18, 19, 20, 21, 22, 23, 24, 25)來(lái)替代age BETWEEN 18 AND 25，但這并不總是能夠這么做。通用的原則是我們盡量將范圍判決條件放到索引的末尾，因此優(yōu)化器會(huì)盡可能地使用索引。

我們提到你可以使用盡可能多的列使用IN查詢(xún)?nèi)ジ采w那些在WHERE條件中未指定的索引條件。但你可能做得過(guò)頭了導(dǎo)致新的問(wèn)題。使用更多的這樣的IN查詢(xún)清單導(dǎo)致優(yōu)化器需要評(píng)估大量的組合，這反而可能降低查詢(xún)速度。考慮下面的查詢(xún)條件語(yǔ)句：

WHERE eye_color IN(’brown’, ’blue’, ’hazel’)AND hair_colorIN(’black’, ’red’, ’blonde’, ’brown’) AND sex IN(’M’, ’F’)

這個(gè)優(yōu)化器會(huì)轉(zhuǎn)變?yōu)?32=24種組合，WHERE條件會(huì)檢查每一種情況。24還不是一個(gè)很大的組合數(shù)字，但如果數(shù)量達(dá)到了幾千。舊版本的MySQL在IN查詢(xún)中數(shù)量過(guò)多時(shí)可能會(huì)有更多的問(wèn)題。查詢(xún)優(yōu)化器會(huì)執(zhí)行更慢并且消耗很多內(nèi)存。新版本的MySQL會(huì)在組合過(guò)多時(shí)停止評(píng)估，但這會(huì)影響MySQL使用索引。

避免多個(gè)范圍查詢(xún)

讓我們假設(shè)有一個(gè)last_online(最近在線(xiàn)時(shí)間）的列，然后我們需要展示最近一周在線(xiàn)的用戶(hù)：

WHERE eye_colorIN(’brown’, ’blue’, ’hazel’)AND hair_colorIN(’black’, ’red’, ’blonde’, ’brown’) AND sex IN(’M’, ’F’) AND last_online > DATE_SUB(NOW(), INTERVAL 7 DAY) AND ageBETWEEN 18 AND 25

這個(gè)查詢(xún)的問(wèn)題在于它有兩個(gè)范圍查詢(xún)。MySQL可以使用last_online或age條件，但不能同時(shí)使用。 如果last_online約束出現(xiàn)時(shí)沒(méi)有age約束，或last_online比age更有篩選性，我們可能希望增加另一組索引，將last_online放到最后面。但是如果我們不能將age轉(zhuǎn)換為IN查詢(xún)，而我們也希望能夠在同時(shí)有l(wèi)ast_oinline和age范圍查詢(xún)時(shí)提高查詢(xún)速度怎么辦？這個(gè)時(shí)候，我們沒(méi)有直接的方法。但我們可以將一個(gè)范圍轉(zhuǎn)換為相等比較。去這么做的時(shí)候，我們?cè)黾右粋€(gè)預(yù)先計(jì)算的active列，這個(gè)列我們會(huì)定期維護(hù)。如果用戶(hù)登錄后，我們標(biāo)記為1，如果7天內(nèi)沒(méi)有連續(xù)登錄則重新標(biāo)記為0。

這個(gè)方法可以讓MySQL使用如(active, sex, country, age)這樣的索引。這個(gè)列也許沒(méi)那么精準(zhǔn)，但這類(lèi)查詢(xún)也許不需要很高的精準(zhǔn)度。如果我們需要精準(zhǔn)查詢(xún)，我們可以保留last_online在WHERE條件中，但不增加索引。這種技巧與URL查找的情況類(lèi)似。這種條件不會(huì)使用任何索引，因?yàn)樗惶赡軙?huì)將索引命中的行給過(guò)濾掉。增加索引未必能夠讓查詢(xún)收益。

現(xiàn)在，你可以看到這個(gè)模式：如果用戶(hù)想同時(shí)查找活躍和不活躍的結(jié)果，我們可以使用IN查詢(xún)。我們?cè)黾恿撕芏噙@樣的清單查詢(xún)，一個(gè)變通的方式是通過(guò)將各個(gè)組合分開(kāi)的查詢(xún)單獨(dú)建立索引，例如，我們可以使用如下的索引：(active, sex, country, age)，(active, country, age)，(sex, country, age)和(country, age)。雖然這樣的索引對(duì)于特定的查詢(xún)可能是更優(yōu)的選擇，但維護(hù)這些組合的負(fù)面效果，組合所需的額外存儲(chǔ)空間都可能導(dǎo)致是一個(gè)很弱的策略。

這是一個(gè)優(yōu)化器改變后可以真正影響索引優(yōu)化的案例。如果在未來(lái)的MySQL版本中可以真正丟棄索引掃描，它可能能夠在一個(gè)索引上使用多個(gè)范圍條件，此時(shí)我們不再需要通過(guò)IN查詢(xún)這種方式解決此類(lèi)問(wèn)題。

優(yōu)化排序

最后一個(gè)議題是排序。小數(shù)據(jù)量的結(jié)果使用文件排序(filesort)很快，但如果是上百萬(wàn)行數(shù)據(jù)呢？例如，如果只在WHERE條件中指定了性別時(shí)的排序。

對(duì)于這類(lèi)低篩選性的場(chǎng)景，我們可以增加特定的索引用于排序。例如，一個(gè)(sex, rating)的索引可以用于下面的查詢(xún)：

SELECT <cols> FROM profiles WHERE sex=’M’ ORDER BY rating LIMIT 10;

這個(gè)查詢(xún)同時(shí)有排序和LIMIT子句，在沒(méi)有索引的情況下可能很慢。即便是有索引，這個(gè)查詢(xún)?cè)谟脩?hù)界面有分頁(yè)查詢(xún)，而頁(yè)碼不在起始位置附近時(shí)也可能很慢。下面的例子的ORDER BY和LIMIT造成了一個(gè)糟糕的組合：

SELECT <cols> FROM profiles WHERE sex=’M’ ORDER BY rating LIMIT 100000, 10;

即便有索引，這樣的查詢(xún)也可能導(dǎo)致十分嚴(yán)重的問(wèn)題。這是因?yàn)楹芨叩钠茣?huì)導(dǎo)致花費(fèi)大量的時(shí)間掃描大量的數(shù)據(jù)，且這些數(shù)據(jù)會(huì)被丟棄。反范式設(shè)計(jì)，提前計(jì)算和緩存可能能夠解決這類(lèi)查詢(xún)的問(wèn)題。一個(gè)更好的策略是限制用戶(hù)可查詢(xún)的頁(yè)碼。這不太可能會(huì)降低用戶(hù)的體驗(yàn)，因?yàn)閷?shí)際上不會(huì)有人會(huì)關(guān)心第10000頁(yè)的搜索結(jié)果。

另一個(gè)好的策略是使用推斷聯(lián)合查詢(xún)，這是我們利用覆蓋索引去獲取主鍵列后再獲取數(shù)據(jù)行的方式。你可以將需要獲取的列全部聯(lián)合，這會(huì)減少M(fèi)ySQL收集那些需要丟棄的數(shù)據(jù)的工作。下面是一個(gè)例子：

SELECT <cols> FROM profiles INNER JOIN ( SELECT <primary key cols> FROM profiles WHERE x.sex=’M’ ORDER BY rating LIMIT 100000, 10AS x USING(<primary key cols>);

以上就是MySQL如何構(gòu)建數(shù)據(jù)表索引的詳細(xì)內(nèi)容，更多關(guān)于MySQL構(gòu)建數(shù)據(jù)表索引的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！