mysql 索引案例与索引策略

目录

索引案例学习

支持多种过滤条件

避免多个范围条件

优化排序

分页查询优化

分而治之总是不错的

索引高性能策略

策略总结

独立的列

前缀索引和索引选择性

多列索引

选择合适的索引列顺序

聚簇索引

lnnoDB和MylSAM的数据分布对比

在lnnoDB表中按主键顺序插入行

覆盖索引

使用索引扫描来做排序

压缩（前缀压缩）索引

冗余和重复索引

未使用的索引

索引和锁

---

索引案例学习

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

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

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

支持多种过滤条件

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

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

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

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

这个＂诀窍”就是：如果某个查询不限制性别，那么可以通过在查询条件中新增AND SEX IN ('m','f')来让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(l8, 19, 20, 21, 22, 23, 24, 25), 但不是所有的范围查询都可以转换。这里描述的基本原则是，尽可能 将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

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

避免多个范围条件

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

从EXPLAIN的输出很难区分MySQL是要查询范围值，还是查询列表值。EXPLAIN 使用同样的词"range"来描述这两种情况。例如，从type列来看，MySQL会把 下面这种查询当作是"range"类型：

从EXPLAIN的结果是无法区分这两者的，但可以从值的范围和多个等于条件来得出 不同。在我们看来，笫二个查询就是多个等值条件查询。

我们不是挑剔：这两种访问效率是不同的。对于范围条件查询，MySQL无法再使 用范围列后面的其他索引列了，但是对于“多个等值条件查询”则没有这个限制。

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

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

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

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

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

优化排序

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

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

这个查询同时使用了ORDER BY和LIMIT, 如果没有索引的话会很慢。

即使有索引，如果用户界面上需要翻页，并且翻页翻到比较靠后时查询也可能非常慢。 下面这个查询就通过ORDER BY和LIMIT偏移量的组合翻页到很后面的时候：

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需 要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类

查询的仅有策略。一个更好的办法是限制用户能够翻页的数量，实际上这对用户体验的影响不大，因为用户很少会真正在乎搜索结果的第10 000页。

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

分页查询优化

业务要根据时间范围查询交易记录，接口原始的SQL如下：

select  * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20;

表trade_info上有索引idx_status_create_time(status,create_time)，通过上面分析知道，等价于索引（status,create_time,id)，对于典型的分页limit m, n来说，越往后翻页越慢，也就是m越大会越慢，因为要定位m位置需要扫描的数据越来越多，导致IO开销比较大，这里可以利用辅助索引的覆盖扫描来进行优化，先获取id，这一步就是索引覆盖扫描，不需要回表，然后通过id跟原表trade_info进行关联，改写后的SQL如下：

select * from trade_info a ,

(select  id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b   //这一步走的是索引覆盖扫描，不需要回表
 where a.id = b.id;

很多同学只知道这样写效率高，但是未必知道为什么要这样改写，理解索引特性对编写高质量的SQL尤为重要。

分而治之总是不错的

营销系统有一批过期的优惠卷要失效，核心SQL如下：

-- 需要更新的数据量500w

update coupons set status = 1 where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';

在Oracle里更新500w数据是很快，因为可以利用多个cpu core去执行，但是MySQL就需要注意了，一个SQL只能使用一个cpu core去处理，如果SQL很复杂或执行很慢，就会阻塞后面的SQL请求，造成活动连接数暴增，MySQL CPU 100%，相应的接口Timeout，同时对于主从复制架构，而且做了业务读写分离，更新500w数据需要5分钟，Master上执行了5分钟，binlog传到了slave也需要执行5分钟，那就是Slave延迟5分钟，在这期间会造成业务脏数据，比如重复下单等。

优化思路：先获取where条件中的最小id和最大id，然后分批次去更新，每个批次1000条，这样既能快速完成更新，又能保证主从复制不会出现延迟。

优化如下：

先获取要更新的数据范围内的最小id和最大id（表没有物理delete，所以id是连续的）
mysql> explain select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59'; 
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---
| id | select_type | table | partitions | type  | possible_keys          | key                    | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---
|  1 | SIMPLE      | users | NULL       | range | idx_status_create_time | idx_status_create_time | 6       | NULL | 180300 |   100.00 | Using where; Using index |
Extra=Using where; Using index使用了索引idx_status_create_time，同时需要的数据都在索引中能找到，所以不需要回表查询数据。

以每次1000条commit一次进行循环update，主要代码如下：
current_id = min_id;
for  current_id < max_id do
  update coupons set status = 1 where id >=current_id and id <= current_id + 1000;  //通过主键id更新1000条很快
commit;
current_id += 1000;
done

这两个案例告诉我们，要充分利用辅助索引包含主键id的特性，先通过索引获取主键id走覆盖索引扫描，不需要回表，然后再通过id去关联操作是高效的，同时根据MySQL的特性使用分而治之的思想既能高效完成操作，又能避免主从复制延迟产生的业务数据混乱。

索引高性能策略

策略总结

表的设计：

表的主键最好是递增的数字，而不是随机的（因为聚簇索引的物理结构）

查询语句中：

查询的列要是单独的

如果有and，不要多个单列索引，可以考虑一个多列索引，注意索引顺序

如果有or，可以考虑用union，或者索引合并

如果查询条件里，有不能完全覆盖的，如like,范围查询，但是where之后返回的结果很少的查询条件，可以使用索引查询到对应的主键，然后使用延迟关联，查询到对应的列。

如果有排序，只有排序字段是索引字段前几个字段，才能使用索引排序（除非索引字段中，排序字段前的字段是常量）

如果很多查询都要where的字段，而且选择性很低，可以建立在索引最左边，如果不要时，使用 in(x,y)

可以把范围查询变成in（a,b,c），但是不能滥用

如果有多个范围查询，可以考虑将其中一个转为in（a,b,c），这样两个字段都能使用索引

如果又有查询条件和排序，而且两者不一致，而且limit的范围还很大，可以限制limit的范围，或者使用延迟关联，通过使用覆盖索引（包含查询条件和排序字段）查询返回需要 的主键，再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢 弃的行数

索引及它的字段：

如果是字符串，可以选择前后缀索引，注意索引选择性。

如果字段有很长的字符串，可以考虑使用