高性能索引（下）

た入场券 2022-07-15 12:47 127阅读 0赞

选择合适的索引列顺序

我们遇到的最容易起困惑的问题就是索引列的顺序。正确是顺序一来与使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要（顺便说明，本节内容使用与B-Tree索引；hash或者其他类型的索引并不会像B-Tree索引一样按照顺序顺序存储数据）。

在一个多列的B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等等。所以，索引可以按照升序或者降序搜啊吗，以满足精确符合列顺序的ORDER BY ,GROUP BY 和DISTICT 等子句的查询需求。

所以多列索引的顺序至关重要。在Lahdenmaki和Leach的“三星索引”系统中，列顺序也决定了一个索引是否能够成为一个真正的“三星索引”

对于如何选择索引的顺序有一个经验法则：将选择性最高的列放到索引的最前列。这个建议有用吗？在某些场景中可能有帮助，但通常不如避免随机IO和拍下那么在湖南工业，考虑问题需要全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明这个经验法则可能没有那么重要）。

当不需要考虑拍下和分组是，将选择性最高的列放在前面通常是最好的。这时候索引的作用只是用于优化where条件的查找。在这种情况下，这样设计的索引确实能够最快的过滤掉需要的行，对于在where子句中只使用了索引不发前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也只是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样，可能需要根据那些运行频率更高的查询来调整索引列的顺序，让这种情况下的选择性更高。

以下面的查询为例：

SELECT \* FROM  payment WHERE staff\_id = 2 AND customer\_id =593;

是应该创建一个（staff\_id , customer\_id）索引还是应该点到一下顺序?可以跑一些查询来确定这个表中值的分布情况，并确定那个列的选择性更高。先用下面的查询预测一下，看看哥哥where条件的分支对应的数据技术有多大：

SELECT SUM(staff\_id = 2) ,SUM(CUSTOMER\_ID=593) FROM payment /G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

SUM(STAFF\_ID = 2) :7992

SUM(CUSTOMER\_ID=593):30

根据前面的经验法则,应该讲索引列customer\_id放到前面，因为对应条件值的customer\_id数量更小。我们再来看看对于这个customer\_id的条件值，对应的staff\_id列的选择性如何：

SELECT SUM(staff\_id=2) FROM payment WHERE customer\_id=593 /G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

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 /G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

staff\_id\_selectivity:0.00001

customer\_id\_selectivity:0.0373

count(\*):16049

所以customer\_id的选择性更高，所以答案是将其作为索引列的第一列：

ALTER TABLE payment ADD KEY(customer\_id,staff\_id);

当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。例如：在某些应用中，对于没有登录的用户，都将其用户名记为 “guset”，在记录用户行为的会话表和其他记录用户活动的表中，“guest”就成为了一个特殊用户ID，一旦查询涉及到这个用户，那么和对于正常用户的查询就打不相同了，以为通常有很多会话都是没有登录的。系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号，和普通账号不同，它补补水一个具体的用户，系统中所有的其他用户都是这个用户的好友，所以系统往往通过它像王子的所有用户发送状态通知到和其他消息。这个账号的巨大的好友列表容易导致王子出现服务器性能问题。

这实际山是一个非常典型的问题。任何异常的用户，不仅仅是那些用于管应用的设计糟糕的战壕会有同样的问题；那些拥有大量好友，图片，状态，收藏的用户，也会有前面提到的系统账号同样的问题。

下面是一个我们遇到过的真实案例，在一个用户分享购买商品和购买经验的论坛上，这个特殊表上的查询运行的非常慢：

SELECT COUNT(DISTINCT threadId) as COUNT\_VALUE FROM message WHERE (groupId = 10137) AND (user\_id = 1288826) and (anoymous = 0) ORDER BY priority DESC ,modifiedDate DESC

这个窜稀看似没有建立合适的索引，所以客户咨询我们是否可以优化。EXPLAIN的结果如下：

id：1

select\_type:SIMPLE

table:Message

type:ref

key:ix\_groupId\_userId

key\_len:18

ref:const,const

rows:1251162

Extra:Using Where

MySQL 为这个查询选择了索引（groupid，userid），如果不考虑列的基数，这看起来是一个非常合理的选择。但如果考虑一下userId和groupId条件匹配的行数，可能就会有不同的想法了：

SELECT COUNT(\*) , SUM(group\_id=10137),sum(userId=1288826),sum(anonymous=0) FROM Message /G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

COUNT(\*):4142217

SUM(group\_id=10137):4092654

sum(userid=1288826) :1288496

sum(anoymous=0):414394

从上面看符合组（groupid）条件几乎满足表中的所有行，符合用户（userid）条件有130W条记录------也就是说，索引基本上没神马用。因为这些数据是从其他应用中迁移过来的，迁移的时候吧所有的消息属性都赋予了管理员组的用户。这个案例的解决办法是修改应用程序代码，区分这类特殊用户和组，进制针对这类用户和组执行这个查询。

从这个小案例中可以看到经验法则和推理在多数情况中是有用的，但要注意不要假设平局情况向的性能也能代表特性情况先的系能，特殊情况可能会摧毁整个应用的性能。

最后尽管关于选择上线和技术的经验法则值得去研究和分析，但一定不要忘了WHERE 子句中的排序，分组和范围条件等其他因素，这些因素可能对查询的性能带来非常大的影响。

原文：http://www.cnblogs.com/zhengyanqiu/p/4979547.html