MySQL进阶系列-3：MySQL索引，创建高性能索引
本篇内容引导高性能的索引策略
前言
正确地创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引及其对应的优缺点。现在我们一起来看看如何真正地发挥这些索引的优势。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。使用哪个索引，以及如何评估选择不同索引的性能影响的技巧，则需要持续不断地学习。接下来的几个小节将帮助读者理解如何高效地使用索引。 独立的列 我们通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。例如，下面这个查询无法使用actor_id列的索引：mysqlSELECTactor_idFROMsakila.actorWHEREactor_id+1=5;凭肉眼很容易看出WHERE中的表达式其实等价于actor_id=4，但是MySQL无法自动解析这个方程式。这完全是用户行为。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。下面是另一个常见的错误：
mysqlSELECT...WHERETO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)=10;1 前缀索引和索引选择性 有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。在示例数据库Sakila中并没有合适的例子，所以我们从表city中生成一个示例表，这样就有足够的数据进行演示：
CREATETABLEsakila.city_demo(cityVARCHAR(50)NOTNULL);INSERTINTOsakila.city_demo(city)SELECTcityFROMsakila.city;--Repeatthenextstatementfivetimes:city_demo;Nowrandomizethedistribution(inefficientlybutconveniently):UPDATEsakila.city_demoSETcity=(SELECTcityFROMsakila.cityORDERBYRAND()LIMIT1);现在我们有了示例数据集。数据分布当然不是真实的分布；因为我们使用了RAND()，所以你的结果会与此不同，但对这个练习来说这并不重要。首先，我们找到最常见的城市列表：
注意到，上面每个值都出现了45～65次。现在查找到最频繁出现的城市前缀，先从3个前缀字母开始：
每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过实验后发现前缀长度为7时比较合适：
计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性：
通常来说（尽管也有例外情况），这个例子中如果前缀的选择性能够接近0.，基本上就可用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性：
查询显示当前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4或者5的索引已经足够了，但如果数据分布很不均匀，可能就会有陷阱。如果观察前缀为4的最常出现城市的次数，可以看到明显不均匀：如果前缀是4个字节，则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性要低。如果有比这个随机生成的示例更真实的数据，就更有可能看到这种现象。例如在真实的城市名上建一个长度为4的前缀索引，对于以“San”和“New”开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头。在上面的示例中，已经找到了合适的前缀长度，下面演示一下如何创建前缀索引：mysqlALTERTABLEsakila.city_demoADDKEY(city(7));前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDERBY和GROUPBY，也无法使用前缀索引做覆盖扫描。一个常见的场景是针对很长的十六进制唯一ID使用前缀索引。在前面的章节中已经讨论了很多有效的技术来存储这类ID信息，但如果使用的是打包过的解决方案，因而无法修改存储结构，那该怎么办？例如使用vBulletin或者其他基于MySQL的应用在存储网站的会话（SESSION）时，需要在一个很长的十六进制字符串上创建索引。此时如果采用长度为8的前缀索引通常能显著地提升性能，并且这种方法对上层应用完全透明。有时候后缀索引（suffixindex）也有用途（例如，找到某个域名的所有电子邮件地址）。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。参考5.1节中“创建自定义哈希索引”部分的相关内容。1 多列索引 很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。先来看第一个问题，为每个列创建独立的索引，从SHOWCREATETABLE中很容易看到这种情况：
CREATETABLEt(c1INT,c2INT,c3INT,KEY(c1),KEY(c2),KEY(c3));这种索引策略，一般是由于人们听到一些专家诸如“把WHERE条件里面的列都建上索引”这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”（indexmerge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。例如，表film_actor在字段film_id和actor_id上各有一个单列索引。但对于下面这个查询WHERE条件，这两个单列索引都不是好的选择：
mysqlSELECTfilm_id,actor_idFROMsakila.film_actor-WHEREactor_id=1ORfilm_id=1;在老的MySQL版本中，MySQL对这个查询会使用全表扫描。除非改写成如下的两个查询UNION的方式：
mysqlSELECTfilm_id,actor_idFROMsakila.film_actorWHEREactor_id=1-UNIONALL-ANDactor_id1;但在MySQL5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。下面的查询就是使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点：
mysqlEXPLAINSELECTfilm_id,actor_idFROMsakila.film_actor-WHEREactor_id=1ORfilm_id=1\G***************************1.row***************************id:1select_type:SIMPLEtable:film_actortype:index_mergepossible_keys:PRIMARY,idx_fk_film_idkey:PRIMARY,idx_fk_film_idkey_len:2,2ref:NULLrows:29Extra:Usingunion(PRIMARY,idx_fk_film_id);UsingwhereMySQL会使用这类技术优化复杂查询，所以在某些语句的Extra列中还可以看到嵌套操作。索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：
当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
更重要的是，优化器不会把这些计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL4.1或者更早的时代一样，将查询改写成UNION的方式往往更好。
如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNOREINDEX提示让优化器忽略掉某些索引。选择合适的索引列顺序 我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要（顺便说明，本节内容适用于B-Tree索引；哈希或者其他类型的索引并不会像B-Tree索引一样按顺序存储数据）。在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDERBY、GROUPBY和DISTINCT等子句的查询需求。所以多列索引的列顺序至关重要。在Lahdenmaki和Leach的“三星索引”系统中，列顺序也决定了一个索引是否能够成为一个真正的“三星索引”。在本章的后续部分我们将通过大量的例子来说明这一点。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗?在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要）。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。以下面的查询为例：SELECT*FROMpaymentWHEREstaff_id=2ANDcustomer_id=;是应该创建一个（staff_id，customer_id）索引还是应该颠倒一下顺序?可以跑一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。先用下面的查询预测一下，看看各个WHERE条件的分支对应的数据基数有多大：
mysqlSELECTSUM(staff_id=2),SUM(customer_id=)FROMpayment\G***************************1.row***************************SUM(staff_id=2):SUM(customer_id=):30根据前面的经验法则，应该将索引列customer_id放到前面，因为对应条件值的customer_id数量更小。我们再来看看对于这个customer_id的条件值，对应的staff_id列的选择性如何：
mysqlSELECTSUM(staff_id=2)FROMpaymentWHEREcustomer_id=\G***************************1.row***************************SUM(staff_id=2):17这样做有一个地方需要注意，查询的结果非常依赖于选定的具体值。如果按上述办法优化，可能对其他一些条件值的查询不公平，服务器的整体性能可能变得更糟，或者其他某些查询的运行变得不如预期。如果是从诸如pt-query-digest这样的工具的报告中提取“最差”查询，那么再按上述办法选定的索引顺序往往是非常高效的。如果没有类似的具体查询来运行，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：
mysqlSELECTCOUNT(DISTINCTstaff_id)/COUNT(*)ASstaff_id_selectivity,COUNT(DISTINCTcustomer_id)/COUNT(*)AScustomer_id_selectivity,COUNT(*)FROMpayment\G***************************1.row***************************staff_id_selectivity:0.customer_id_selectivity:0.COUNT(*):customer_id的选择性更高，所以答案是将其作为索引列的第一列：mysqlALTERTABLEpaymentADDKEY(customer_id,staff_id);当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。例如，在某些应用程序中，对于没有登录的用户，都将其用户名记录为“guset”，在记录用户行为的会话（session）表和其他记录用户活动的表中“guest”就成为了一个特殊用户ID。一旦查询涉及这个用户，那么和对于正常用户的查询就大不同了，因为通常有很多会话都是没有登录的。系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号，和普通账号不同，它并不是一个具体的用户，系统中所有的其他用户都是这个用户的好友，所以系统往往通过它向网站的所有用户发送状态通知和其他消息。这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。这实际上是一个非常典型的问题。任何的异常用户，不仅仅是那些用于管理应用的设计糟糕的账号会有同样的问题；那些拥有大量好友、图片、状态、收藏的用户，也会有前面提到的系统账号同样的问题。下面是一个我们遇到过的真实案例，在一个用户分享购买商品和购买经验的论坛上，这个特殊表上的查询运行得非常慢：
mysqlSELECTCOUNT(DISTINCTthreadId)ASCOUNT_VALUE-FROMMessage-WHERE(groupId=)AND(userId=)AND(anonymous=0)-ORDERBYpriorityDESC,modifiedDateDESC这个查询看似没有建立合适的索引，所以客户咨询我们是否可以优化。EXPLAIN的结果如下：
id:1select_type:SIMPLEtable:Messagetype:refkey:ix_groupId_userIdkey_len:18ref:const,constrows:Extra:UsingwhereMySQL为这个查询选择了索引（groupId，userId），如果不考虑列的基数，这看起来是一个非常合理的选择。但如果考虑一下userID和groupID条件匹配的行数，可能就会有不同的想法了：
mysqlSELECTCOUNT(*),SUM(groupId=),-SUM(userId=),SUM(anonymous=0)-FROMMessage\G***************************1.row***************************count(*):sum(groupId=):sum(userId=):sum(anonymous=0):从上面的结果来看符合组（groupId）条件几乎满足表中的所有行，符合用户（userId）条件的有万条记录——也就是说索引基本上没什么用。因为这些数据是从其他应用中迁移过来的，迁移的时候把所有的消息都赋予了管理员组的用户。这个案例的解决办法是修改应用程序代码，区分这类特殊用户和组，禁止针对这类用户和组执行这个查询。从这个小案例可以看到经验法则和推论在多数情况是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。最后，尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。
往期推荐
MySQL进阶系列-3：MySQL索引，创建高性能索引
MySQL进阶系列-2：MySQL架构与历史（MVCC和存储引擎）
MySQL进阶系列-1：MySQL架构与历史
SpringBoot解决跨域问题的3种方案！
41张图解elasticsearch原理，你可收好了!
京东把Elasticsearch用得真牛逼！日均5亿订单查询完美解决！
Spring这10个错误，打死都不要犯！
基于Lucene查询原理分析Elasticsearch的性能
专注于技术的分享长按扫描