在众多困扰索引使用的原因中，其中最常见的一个是索引中列的次序。正确的次序依赖于使用索引的查询，因此需要考虑怎样选择索引次序以便数据行的排序火分组能够从中受益（这个仅在二叉树索引有用，哈希索引和其他类型的索引并没有像二叉树索引那样对数据进行排序）。
在二叉树索引中多列的顺序意味着会首先对最左列进行排序，然后才是其他列。因此，为满足ORDER BY，GROUP BY和DISTINCT的条件的查询，索引可能会按正向或逆向扫描进行排序。
结果就是，索引列的次序在多列索引中极其重要。这个次序有可能强化或弱化性能。接下来会通过很多例子说明这种情况。有一个古老的值得推荐的原则：将最具筛选性的列放在索引的第一位。这个建议多有用？在某些例子中是有用的，但是与避免随机I/O和排序相比，就没有那么重要了（有很多特殊的例子，因此没有一个普适性的原则。这里只是告诉你这个原则未必有你想的那么重要）。
在没有排序和分组的时候，将最具筛选性的列放在第一位会是一个好主意，因为这时候索引仅仅是优化WHERE条件的查询。在这类场景下，这样的索引确实能够足够快地筛选出想要的数据。然而，这不仅仅依赖于列的筛选性，还同样依赖于查找数据行的值——值的离散性。这和我们选择一个好的前缀索引长度是类似的。你可能会需要选择一个合适的索引列次序去尽可能地满足最频繁查询的筛选性。
以下面的查询为例：
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;
你应该在(staff_id, customer_id)创建一个索引或者是以相反的次序创建索引吗？我们可以运行一些查询去检查数据表数据的离散性来决定哪个次序更具备筛选性。让我们将查询转换一下，去统计候选项的数量：
SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment;
--------------------------------------------------------------
SUM(staff_id = 2):7992
SUM(customer_id = 584): 30
根据首要原则来看，我们应该将customer_id放在第一位，因为这个条件匹配的数据行更少。然后我们再来看看指定了customer_id后的staff_id的筛选性怎么样：
SELECT SUM(staff_id = 2) FROM payment WHERE customer_id=584;
--------------------------------------------------------------
SUM(staff_id = 2):17
请慎用这项技巧，因为这个结果是依赖于特定的常量的。如果你对这个查询这样优化你的索引可能其他的查询并不会表现得很好。服务器的性能可能全部受影响或者部分查询并不像我们预期那样运行。
如果你使用像pt-query-digest的工具来分析最坏的情况，这个可能是一个有效的途径去看看什么索引是最适合你的查询和数据的。但是，如果你没有特殊的样例去运行，也许使用那个古老的首要原则会更好 —— 这是根据整体情况来选择的，而不是单个查询。
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);
对于前缀索引，与正常的基数相比，特殊值的问题会更多。例如，我们会发现有些应用将未登录的用户当作游客——在session表中会有一个特殊的user ID，而其他地方记录着这个用户的活动。包括这样的user ID的查询与其他查询相比可能差别很大。这是因为通常会有很多未登录的session记录。我们会发现系统账户会导致同样的问题。一个应用有一个魔法的管理员账户，这并不是真正的用户，而是整个网站的每一个用户的“好友”——以便这个账户可以发送状态通知和其他消息。这个用户有庞大的好友列表，结果会导致网站的性能问题。
这在现实中非常典型。任何系统无关的用户，即便它不是应用管理的一个错误决定，也会导致问题。那些真正拥有许多好友、照片、状态消息和点赞的用户，可能会面临假用户混在一起的麻烦。
以下是我们曾经见到过的一个真实案例。一个供用户交流产品体验和经验的产品论坛，在一些特殊的场景中运行十分缓慢。
SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonyous = 0)
ORDERBY priority DESC, modifiedDate DESC;
这个查询看似是索引没有用好，因此客户让我们检查看看能不能优化，EXPLAIN的结果如下：
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)，在没有数据列的基数信息时，这看起来是十分正确的选择。然而，当我们检查有多少行数据匹配那个user ID和group Id时，出现了不同的情况：
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
这样的结果显示这个分组基本上覆盖了整个数据表。这个用户有130万行相关的数据。这个案例里，索引根本没法解决这样的问题。这是因为数据是从别的应用迁移过来的，而所有的消息都被赋予了管理员用户，并在在导入过程中分进了一个组。这个问题的解决方案是修改应用的代码去识别特殊的用户和分组，而不是对这个用户的查询问题。
这个小故事要告诉大家的是首要原则是有用的，但是需要小心的是，平均性能并不能代表特殊案例的性能，而特殊案例可能拖垮整个应用的性能。
最后，虽然关于选择性和基数的首要原则是十分有吸引力的，但其他因素，例如排序，分组和范围条件在WHERE条件中也可能会对查询的性能影响很大。
网友评论