在眾多困擾索引使用的原因中，其中最常見的一個(gè)是索引中列的次序秤朗。正確的次序依賴于使用索引的查詢，因此需要考慮怎樣選擇索引次序以便數(shù)據(jù)行的排序火分組能夠從中受益（這個(gè)僅在二叉樹索引有用笔喉，哈希索引和其他類型的索引并沒有像二叉樹索引那樣對(duì)數(shù)據(jù)進(jìn)行排序）取视。
在二叉樹索引中多列的順序意味著會(huì)首先對(duì)最左列進(jìn)行排序，然后才是其他列常挚。因此作谭，為滿足ORDER BY，GROUP BY和DISTINCT的條件的查詢奄毡，索引可能會(huì)按正向或逆向掃描進(jìn)行排序折欠。
結(jié)果就是，索引列的次序在多列索引中極其重要吼过。這個(gè)次序有可能強(qiáng)化或弱化性能锐秦。接下來會(huì)通過很多例子說明這種情況。有一個(gè)古老的值得推薦的原則：將最具篩選性的列放在索引的第一位盗忱。這個(gè)建議多有用酱床？在某些例子中是有用的，但是與避免隨機(jī)I/O和排序相比趟佃，就沒有那么重要了（有很多特殊的例子斤葱，因此沒有一個(gè)普適性的原則。這里只是告訴你這個(gè)原則未必有你想的那么重要）揖闸。
在沒有排序和分組的時(shí)候揍堕，將最具篩選性的列放在第一位會(huì)是一個(gè)好主意，因?yàn)檫@時(shí)候索引僅僅是優(yōu)化WHERE條件的查詢汤纸。在這類場(chǎng)景下衩茸，這樣的索引確實(shí)能夠足夠快地篩選出想要的數(shù)據(jù)。然而贮泞，這不僅僅依賴于列的篩選性楞慈，還同樣依賴于查找數(shù)據(jù)行的值——值的離散性幔烛。這和我們選擇一個(gè)好的前綴索引長度是類似的。你可能會(huì)需要選擇一個(gè)合適的索引列次序去盡可能地滿足最頻繁查詢的篩選性囊蓝。
以下面的查詢?yōu)槔?/p>
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;
你應(yīng)該在(staff_id, customer_id)創(chuàng)建一個(gè)索引或者是以相反的次序創(chuàng)建索引嗎饿悬？我們可以運(yùn)行一些查詢?nèi)z查數(shù)據(jù)表數(shù)據(jù)的離散性來決定哪個(gè)次序更具備篩選性。讓我們將查詢轉(zhuǎn)換一下聚霜，去統(tǒng)計(jì)候選項(xiàng)的數(shù)量：
SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment;
--------------------------------------------------------------
SUM(staff_id = 2):7992
SUM(customer_id = 584): 30
根據(jù)首要原則來看狡恬，我們應(yīng)該將customer_id放在第一位，因?yàn)檫@個(gè)條件匹配的數(shù)據(jù)行更少蝎宇。然后我們?cè)賮砜纯粗付薱ustomer_id后的staff_id的篩選性怎么樣：
SELECT SUM(staff_id = 2) FROM payment WHERE customer_id=584;
--------------------------------------------------------------
SUM(staff_id = 2):17
請(qǐng)慎用這項(xiàng)技巧弟劲，因?yàn)檫@個(gè)結(jié)果是依賴于特定的常量的。如果你對(duì)這個(gè)查詢這樣優(yōu)化你的索引可能其他的查詢并不會(huì)表現(xiàn)得很好姥芥。服務(wù)器的性能可能全部受影響或者部分查詢并不像我們預(yù)期那樣運(yùn)行兔乞。
如果你使用像pt-query-digest的工具來分析最壞的情況，這個(gè)可能是一個(gè)有效的途徑去看看什么索引是最適合你的查詢和數(shù)據(jù)的凉唐。但是庸追，如果你沒有特殊的樣例去運(yùn)行，也許使用那個(gè)古老的首要原則會(huì)更好 —— 這是根據(jù)整體情況來選擇的台囱，而不是單個(gè)查詢锚国。
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
--------------------------------------------------------------
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049
customer_id具有更高的篩選性，因此答案是將這列放在第一位：
ALTER TABLE payment ADD KEY(customer_id, staff_id);
對(duì)于前綴索引玄坦，與正常的基數(shù)相比血筑，特殊值的問題會(huì)更多。例如煎楣，我們會(huì)發(fā)現(xiàn)有些應(yīng)用將未登錄的用戶當(dāng)作游客——在session表中會(huì)有一個(gè)特殊的user ID豺总，而其他地方記錄著這個(gè)用戶的活動(dòng)。包括這樣的user ID的查詢與其他查詢相比可能差別很大择懂。這是因?yàn)橥ǔ?huì)有很多未登錄的session記錄喻喳。我們會(huì)發(fā)現(xiàn)系統(tǒng)賬戶會(huì)導(dǎo)致同樣的問題。一個(gè)應(yīng)用有一個(gè)魔法的管理員賬戶困曙，這并不是真正的用戶表伦，而是整個(gè)網(wǎng)站的每一個(gè)用戶的“好友”——以便這個(gè)賬戶可以發(fā)送狀態(tài)通知和其他消息。這個(gè)用戶有龐大的好友列表慷丽，結(jié)果會(huì)導(dǎo)致網(wǎng)站的性能問題蹦哼。
這在現(xiàn)實(shí)中非常典型。任何系統(tǒng)無關(guān)的用戶要糊，即便它不是應(yīng)用管理的一個(gè)錯(cuò)誤決定纲熏，也會(huì)導(dǎo)致問題。那些真正擁有許多好友、照片局劲、狀態(tài)消息和點(diǎn)贊的用戶勺拣，可能會(huì)面臨假用戶混在一起的麻煩。
以下是我們?cè)?jīng)見到過的一個(gè)真實(shí)案例鱼填。一個(gè)供用戶交流產(chǎn)品體驗(yàn)和經(jīng)驗(yàn)的產(chǎn)品論壇药有，在一些特殊的場(chǎng)景中運(yùn)行十分緩慢。
SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonyous = 0)
ORDERBY priority DESC, modifiedDate DESC;
這個(gè)查詢看似是索引沒有用好苹丸，因此客戶讓我們檢查看看能不能優(yōu)化愤惰，EXPLAIN的結(jié)果如下：
id: 1
select_type: SIMPLE
table:Message
key: ix_groupId_userId
key_len: 18
ref: const, const
rows: 1251162
Extra: Using where
MySQL選擇的索引是(groupId, userId)，在沒有數(shù)據(jù)列的基數(shù)信息時(shí)谈跛，這看起來是十分正確的選擇羊苟。然而塑陵，當(dāng)我們檢查有多少行數(shù)據(jù)匹配那個(gè)user ID和group Id時(shí)感憾，出現(xiàn)了不同的情況：
SELECT COUNT(*), SUM(groupId = 10137),
SUM(userId = 1288826), SUM(anonymous = 0)
FROM Message;
-----------------------------------------------------
count(*): 4142217
sum(groupId = 10137): 4092654
sum(userId = 1288826):1288496
sum(anonymous = 0): 4141934
這樣的結(jié)果顯示這個(gè)分組基本上覆蓋了整個(gè)數(shù)據(jù)表。這個(gè)用戶有130萬行相關(guān)的數(shù)據(jù)令花。這個(gè)案例里阻桅，索引根本沒法解決這樣的問題。這是因?yàn)閿?shù)據(jù)是從別的應(yīng)用遷移過來的兼都，而所有的消息都被賦予了管理員用戶嫂沉，并在在導(dǎo)入過程中分進(jìn)了一個(gè)組。這個(gè)問題的解決方案是修改應(yīng)用的代碼去識(shí)別特殊的用戶和分組扮碧，而不是對(duì)這個(gè)用戶的查詢問題趟章。
這個(gè)小故事要告訴大家的是首要原則是有用的，但是需要小心的是慎王，平均性能并不能代表特殊案例的性能蚓土，而特殊案例可能拖垮整個(gè)應(yīng)用的性能。
最后赖淤，雖然關(guān)于選擇性和基數(shù)的首要原則是十分有吸引力的蜀漆，但其他因素，例如排序咱旱，分組和范圍條件在WHERE條件中也可能會(huì)對(duì)查詢的性能影響很大确丢。
