mysql索引之十：Mysql 索引案例学习

理解索引最好的办法是结合示例，所以这里准备了一个索引的案例。

　　假设要设计一个在线约会网站，用户信息表有很多列，包裹国家，地区，城市，性别，眼睛颜色，等等。完整必须支持上面这些特征的各种组合来搜索用户，还不行一些根据用户的最后在线时间，其他会员对用户的屏风等对用户进行排序并对结果进行限制。如何世界索引满足上面的负载需求呢？

　　出人意料的是第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如，如果希望使用索引做根据其他会员对用户的评分排序，则WHERE 条件中的 age BETWEEN 18 AND 25 就无法使用索引。如果MySQL使用了某个索引进行范围查询，也就无法再使用另一个索引（或者是该索引的后续字段）进行排序了。如果这是很常见的where 条件，那么我们当然就会认为很多查询需要做排序操作。

1、支持多种过滤条件

　　现在需要看看那些列拥有很多不同的取值，那些列在where子句中出现的最繁琐。在有更多不同值的列上创建索引的选择性更好。一般来说这样都是对的，因为可以让MySQL更有效的过滤掉不需要的行。

　　country的选择性通常不高，但是可能很多查询都会用到。sex列的选择性肯定很低，但也会在很多查询中用到。索引考虑到使用的频率还是建议在创建不同组合所以你的时候讲（sex，country） 作为前缀。

　　但根据传统的经验，不是说不应该在选择性地的列上创建索引了吗？那么为什么这里需要将恋歌选择性都很低的字段作为索引的前缀列？我们的脑子坏了？

　　我们的脑子当然没有坏。这么做有两个理由：低一点，如前面所述几乎所有的查询都会用到sex列。前面曾提到，几乎每一个查询都会用到sex列，设置会吧网站设计成每次都只能按照某一种姓名搜索用户。更重要的一点是，索引中加上这一列也没有花痴，即使查询中没有使用sex列也可以通过下面的诀窍绕过。

　　这个诀窍就是：查询不限制性别，那么可以通过在查询条件中增加AND sex in ('f','m')。来让MySQL选择索引。这样写并不会过滤掉如何行，和没有这个条件时返回的结果相同。但是必须添加上这个条件，MySQL才能匹配索引最左前缀。这个诀窍这这类场景中非常有效，但如果有太多不同的值，就会让in() 列表太长，这样就不行了。

　　这个案例显示了一个级别原则：考虑表上素有是选项。当时机索引时，不要指望现有的查询考虑需要哪些需哦因，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引优惠降低另一些查询的效率，那么应该想一想释放能优化原来的查询，那么应该想一想释放能优化原来的查询。应该同时优化查询和索引以找到最贱的平衡，而不是闭门造车去设计完美的索引。

　　接下来，需要考虑其他常见where的组合，并需要了解哪些组合在没有合适索引的情况下就会很慢。（sex,country,age）上的索引就是一个很明显的选择，另外恒友可能还需要（sex，country，region，age）和(sex,country,region,city,age)这样的组合索引。

　　这样就会要要大量的索引。如果想尽可能重用索引而不是建立大量的索引组合，可以使用前面提到的IN（）来巧妙的避免同时需要（sex，country，age）和（sex，country，region，age）的索引。如果没有指定这个字段所说，就需要定义一个全部国家列表，或国家的全部地区列表，来确保前缀索引有同样的约束（组合所有的国家，地区，性别）将是一个非常庞大的条件。

　　这些索引将满足大部分最常见的查询，但是如何为一些生怕的所说条件（比如说has_pictures,eye_color,hair_color和education）来设计索引呢。这些列的选择性高，使用也不频繁，可以选择忽略掉他们，让MySQL多扫描一些额外的行即可。另一个可选的地方是在age列的前面上加上这些列，在查询时使用前面提到过的in（）技术来处理所说是没有指定这些场景。

　　你可能已经注意到了，我们一直讲age列放在索引的最后面。age列有什么特殊的地方吗？为什么要放在索引的最后？我们总是尽可能让MySQL使用更多的索引列，因为在查询中只能使用索引的最左前缀，直到遇到第一个范围条件列。前面提到的列在WHERE 子句中都是等于条件，但是age列则多半是范围查询（例如，18-25岁之间的人）。

　　当然也可以使用in来代替范围查询，例如年龄条件改为in(18,19...25),但不是所有的查询都可以转换。这里秒数的原则是，金坑能将需要做范围查询的列放在索引的最后面，以便游虎丘能使用更多的索引列。

　　前面提到的可以在索引中加入更多的列，并通过in（）的方式覆盖奈尔不在where子句中的列。但这种技巧也不能滥用，否则可能会带来麻烦。因为每额外添加一个in（）条件，优化器需要做的组合将会以指数的形势增加，最终可能会极大的降低查询性能。

　　考虑下面WHERE 子句：

　　WHERE eye_color in('brown','blue','hazel')AND hair_colr in ('black','red','blonde','borwn') AND sex in ('f','m') 优化器会转成3*4*2=24种组合，执行计划需要价差where子句中所有的24种组合。对于MySQL来说，24种组合并不是很夸张，但如果组合数达到上千个则需要特别小心。老板吗的MySQL在in（）组合条件过多时就会有很多问题。查询优化可能花很多时间，并消耗大量的内存。新版本的MySQL在组合数超过一定数量后就不再进行执行计划评估了，可能会导致MySQL不能很好的利用索引。

2、避免多个范围条件

　　假设我们有一个last_online列并希望通过下面的查询显示在过去几周上限过的用户：

WHERE eye_color in('brown','blue','hazel')
　　　　　　AND hair_colr in ('black','red','blonde','borwn')
　　　　　　AND sex in ('f','m')
　　　　　　AND last_online > DATE_SUB(NOW(),INTERVAL 7 DAY) 
　　　　　　AND age between 18 and 25

这个查询有一个问题：他有两个范围条件 last_online 和age列，MySQL可以使用last_onlie 列索引或者age列索引，但无法同时使用它们。

见《mysql索引之四：复合索引之最左前缀原理，索引选择性，索引优化策略之前缀索引》中最左前缀原理中的第6条规则，不能出现多个范围条件。

 

　　如果条件中只有last_onlie而没有age，那么我们可能考虑在索引的后面加上last_onlie列。这里考虑如果我们无法把age字段转换成一个in（）列表，并且人要求对于同时有last_onlie和age这两个维度的范围查询的速度很快，那么该怎么办？答案是，很遗憾，没有一个直接的办法能够解决这个问题。但是我们能够将其中一个范围查询转换成一个简单的等值比较。为了实现这一点，我们需要事先计算好一个active列，这个字段由地市任务来维护。党用户每次登陆时，将对应值设为1，并且将过去连续7天未曾登陆的用户的值置为零。

　　这个方法可以让MySQL使用（active,sex,country,age）索引。active并不是完全精确的，但是对于这个查询来说，对精度的要求没有那么高。如果需要精确的数据，可以吧last_online 列放在where子句中，但不加入到索引中。这和之前通过计算urlhash值来实现url的快速查找类似。所以这个查询无法使用任何索引，但因为这个条件的过滤性不高，即使在索引中加入了该列也没有太大的帮助。换个角度说，缺乏合适的索引对该查询影响也不明显。

　　到目前位置，我们可以看到：如果用户希望同时看到活跃和不活跃的用户，可以在查询中使用in（）列表。我们已经加入了很多这样的列表，但另一个可选的方案只能是位不同组合列创建单独的索引。至少需要建立如下索引（active,sex,country,age）,(active,country,age),(sex,country,age) 和(country,age).这些索引对某个具体的查询来说可能都是更优化的，但是考虑到索引的维护和额外的空间占用的代价，这个可选方案就不是一个好的策略了。

　　这这个案例中，优化器的特性是影响索引策略的一个很重要的因素。如果未来办吗的MySQL能够实现松松索引扫描，就能在一个索引上使用多个范围条件，那也就不需要为上面考虑这类型使用in（）列表了。

3、优化排序

　　在这个学习案例中，最后要介绍的是排序。使用文件排序对小数据集是很快的，但如果一个查询的结果又上百万行的话会怎样？例如where子句中只有sex列，如何排序？

　　对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。例如可以创建（sex,rating）索引用于如下的查询：

　　SELECCT <cols> FROM  profiles WHERE sex='M' ORDER BY rating limit 10;

　　这个查询同时使用了order by 和limit ，如果没有索引的话，会很慢。

　　即使有索引，如果yoghurt界面上需要翻页，并且翻页到比较靠后时，查询也可能非常慢。

　　下面这个查询就可以通过ORDER BY 和LIMIT偏移量的组合翻页到很后面的时候。

　　SELECCT <cols> FROM  profiles WHERE sex='M' ORDER BY rating limit 100000,10;

　　无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的 时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类查询的仅有策略。一个更好的办法是限制用户能够翻页的数量，实际上这对用户的体验不大，因为用户很少真正在乎搜索过的低10000页。

　　优化这类索引的另一个比较好的策略是使用延迟关联，通过使用腹杆索引查询返回需要的主键，在根据这些主键关联原表格所需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询显示了如何高效的使用（sex，rating）索引进行排序和分页

SELECT <cols> FROM profiles INNER JOIN (SELECT <primary key cols> FROM profiles WHERE x.sex = 'M' ORDER BY rating 10000,10) AS X using (<primary key cols>);