举个索引设计的🌰

1. 背景:

这家公司的项目是一个陌生人社交APP，业务场景较为简单，但是在设计索引时也有许多需要注意的地方。

首先，不知道大家是否玩过陌生人社交APP，市面上有很多这样的应用。可能非单身的朋友玩得比较少，但很多单身的年轻人会去尝试这类APP。它的核心功能就是，用户进入APP后，填写一系列个人信息，之后，APP会通过算法推荐适合的人进行在线交友。当然，也可以通过条件筛选，自己查找符合期望的用户。

这里我们暂时忽略掉APP通过算法自动推荐好友的部分，专注于用户通过筛选条件寻找好友的过程。大家可以想一下，在筛选时，查询的是哪个表的数据？

答案显而易见，是用户信息表，假设我们把它命名为 user_info。

这个表里包含了哪些用户的个人信息呢？一般来说，包括地区（如省份、城市等，这很关键，因为跨城市的交流可能不太有实际意义）、性别、年龄、身高、体重、兴趣爱好、性格特点、照片等。还可能包括最近一次在线时间（否则如果用户半年没有上线，你还要搜索出来做什么呢？）。如果支持交友过程中的评价功能，还可能会有综合评分。

2. where 和 order by 很难同时使用索引

针对这个用户信息表进行查询，绝不仅仅是简单的筛选。我们可以设想，你除了会写

select xx from user_info where xx=xx;

这样的查询语句外，APP肯定支持分页展示结果。所以，你的SQL语句肯定会包含类似 limit xx,xx 这样的分页语句。

更关键的一点是，查询结果肯定会有排序。你希望根据一定的规则排序，把最符合条件的用户展示在前面。你肯定不希望随机排序对吧？所以，最终的SQL语句大致会是：

select xx from user_info where xx=xx order by xx limit xx, xx;

这样，问题就来了：根据我们之前学习的索引使用规则，我们知道，where条件中需要使用联合索引最左侧字段开始的连续字段进行筛选，而排序时也必须使用联合索引最左侧字段开始的连续字段进行排序。

问题是，假设你的SQL查询需要根据年龄进行范围筛选，并且根据用户评分进行排序，比如：

select xx from user_info where age between 20 and 25 order by score;

这时，问题就来了：

如果你的联合索引是 age 排在最左侧，那么在 where 条件中，age 可以利用索引进行筛选，但排序是基于 score 字段的，这时就无法使用索引来排序了。假如你为 age 和 score 分别设计了两个索引，尽管在 age 上可以筛选，但在 score 上的排序就无法利用索引了。

因此，针对这个实际场景，你需要意识到，第一个难题是：在很多这样的SQL查询中，where 筛选和 order by 排序往往无法同时使用索引。正如“鱼与熊掌不可兼得”的说法一样，优化索引设计时常面临这种困境。

除了这个问题，这个业务场景中的查询语句在索引设计上还会遇到其他难点。只要 我们 掌握了这个实际场景中的索引设计要点，相信在实际工作中设计索引时也能游刃有余。

在这个场景中，SQL查询通常包含 where、order by 和 limit，而实际情况下，where 和 order by 很难同时使用索引，这是我们需要注意的第一个问题。

3. where 和 order by 出现索引设计冲突时，应该如何选择

第二个问题：当 where 和 order by 出现索引设计冲突时，应该如何选择？到底是让 where 用上索引，还是让 order by 用上索引？

3.1. 问题的本质：

这个问题的本质是：你是希望通过联合索引来让 where 条件快速筛选出部分数据，然后再进行排序并分页，还是希望通过 order by 使用索引进行排序，在排序过程中再依据 where 的条件进行筛选？

其实，通常情况下，优先考虑让 where 条件利用索引进行快速筛选。这是因为通过索引筛选出来的数据量通常较小，筛选后的数据量不大时，后续的排序和分页操作的开销通常不会太大。

3.2. 设计索引时需要考虑的字段：

那么，假设我们已经决定要让 where 条件使用索引，接下来要考虑的问题就是：在联合索引中，应该包含哪些字段，字段的顺序应该如何安排？

答案是，首先要在联合索引里包含省份、城市和性别这三个字段。这三个字段在搜索时几乎是必定会用到的条件。

为什么这么说呢？因为用户在搜索潜在的好友时，往往会选择和自己在同一个地方的用户（省份、城市），并且会指定某个性别。APP里很可能会把这三个条件设计成必选项，几乎每次查询都需要指定。

但是，有人可能会问：省份、城市和性别的基数较小，为什么要把这些字段放到索引里呢？

这确实是一个很好的问题，答案是：尽管这些字段的基数较小，但它们是频繁查询的字段。如果你不把它们放到联合索引中，那么每次查询时，仍然必须通过联合索引筛选出一些数据，接着再将数据加载到内存中，最后根据这些字段来进行额外的筛选。这会导致多一个步骤，性能会受到影响。

相比之下，如果你将这三个字段放到联合索引的最左侧，那么每次查询时，这些字段就能直接从索引树中筛选出来，避免了额外的内存加载和筛选操作，提升了查询效率。

3.3. 字段顺序的设计：

那联合索引中的字段顺序应该如何安排呢？除了省份、城市和性别之外，我们还需要考虑其他字段。到目前为止，我们已经确定了这三个字段应该放在联合索引的最左侧，那么接下来，还需要设计其他的字段。

4. 联合索引中除了省份、城市和性别之外，还需要哪些字段呢？是否需要为其他字段设计单独的索引？

这里需要注意的是，除了考虑到频繁用作筛选条件的字段外，还需要分析查询时是否有其他的复杂条件，是否需要为其他的查询条件或排序条件设计额外的索引。所有这些因素都会影响最终的索引设计。

上面我们讲到，已经把 省份、城市和性别 这三个频繁查询的字段放入联合索引的最左侧，今天我们继续分析联合索引中应该加入哪些字段。

4.1. 问题分析：

假设查询条件是：where province=xx and city=xx and age between xx and xx，此时 age 不在索引中，索引就无法帮助我们筛选数据。于是，有人提议可以将索引设计成 (province, city, sex, age)，但这时也有问题，因为 age 和 city 之间有一个字段 sex，不符合索引最左侧连续字段的原则，age 就不能直接用索引进行筛选了。

但是其实这个问题并不复杂，如果我们将 age 加入联合索引中，设计成 (province, city, sex, age)，那么查询时可以写成：

where province=xx and city=xx and sex in ('female', 'male') and age >= xx and age <= xx

此时，所有的条件都可以通过索引筛选，因为 province, city, sex 和 age 都是连续的，且遵循了最左侧连续字段的原则。

4.2. 加入兴趣和性格字段：

除了省份、城市、性别和年龄外，兴趣爱好 和 性格特点 也是查询中频繁使用的字段。假设兴趣爱好有枚举值（如：运动、电影、旅游、烹饪），性格特点也有固定值（如：温柔、霸气、御姐等），那么我们完全可以将这些字段加入到联合索引中，形成一个新的联合索引：

(province, city, sex, hobby, character, age)

假设查询时需要根据省份、城市、性别、兴趣、性格和年龄进行筛选，SQL 语句可能是这样的：

where province=xx and city=xx and sex in (xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age >= xx and age <= xx

在这个查询中，即使不按 性别 和 兴趣 进行筛选，你依然可以通过 in 语句将这些枚举值全部列出。这样，province, city, character, age 等字段会通过索引进行筛选，从而提高查询效率。

4.3. 字段顺序的设计：

我们为什么一直强调 age 必须放在联合索引的最后一个位置呢？

这是因为，按照索引使用规则，当 where 条件中有等值匹配和范围匹配时，必须是先对 等值匹配的字段 使用联合索引，最后才是 范围匹配的字段。在我们设计的联合索引中，province, city, sex, hobby, character 都是等值匹配的字段，最后的 age 是范围匹配字段，这样的顺序可以保证索引的最大利用。

举个例子，假设索引是 (province, city, sex, age, hobby, character)，而 SQL 语句是：

where province=xx and city=xx and sex in(xx, xx) and age >= xx and age <= xx and hobby in(xx, xx, xx) and character=xx

在这种情况下，只有 province, city, sex, age 会用到索引，因为一旦一个字段做了范围查询，接下来的字段就不能再利用索引了，这是索引的规则。

因此，如果我们把 age 放在联合索引的中间位置，索引就无法有效地利用到后面的字段。所以，我们必须把经常做 范围查询 的字段放在联合索引的 最后，以确保 SQL 语句中每个字段都能用到索引。

4.4. 总结：

1. 在设计联合索引时，频繁用作查询条件的字段（如省份、城市、性别、年龄）应该根据查询的特点合理排列。

2. 需要考虑索引的使用规则，确保 范围查询字段 放在联合索引的最后，这样才能保证整个查询过程都能最大限度地利用索引。

3. 在处理有 枚举值 的字段时，可以通过 in 语句让这些字段也能利用索引进行筛选。

5. 特殊字段查询

我们再讨论如何在查询条件中加入一些特殊字段，如 最近登录时间，以及如何通过额外的索引解决一些性能问题。

5.1. 问题分析：

假设你要根据 用户最近登录时间 来筛选在过去7天内登录过的用户。通常你会有一个字段 latest_login_time 来记录用户的最后登录时间。如果查询条件是：

where latest_login_time >= '2025-02-01'

由于涉及到计算和函数，这种查询无法直接使用索引。而且如果你再加上 age 字段进行范围查询，那么按照之前提到的规则，只有第一个范围查询的字段（如 age）能利用索引，latest_login_time 是无法用上索引的。

5.2. 解决方案：

为了能够让 latest_login_time 字段在查询时使用索引，我们可以通过 将时间字段转换为枚举值 的方式来解决。具体做法是设计一个新字段 does_login_in_latest_7_days，它的值为 1 或 0，表示用户是否在过去7天内登录过。

• 如果用户在最近7天内登录过，does_login_in_latest_7_days = 1；

• 如果超过7天没登录，则 does_login_in_latest_7_days = 0。

这样，你可以创建一个新的联合索引：

(province, city, sex, hobby, character, does_login_in_latest_7_days, age)

接着，在查询时，你可以写成：

where does_login_in_latest_7_days = 1 and age >= xx and age <= xx

通过这种方式，所有的查询条件都能利用索引，无论是 等值匹配 还是 范围查询，都能通过索引进行筛选。

5.3. 解决低基数字段的性能问题：

有时候你可能会遇到查询条件中只包含 基数特别小的字段，例如性别。比如，你可能要筛选出所有女性用户，然后进行排序和分页：

select * from user_info where sex = 'female' order by score limit xx, xx

在这种情况下，虽然 sex 字段基数非常小，但如果使用之前的联合索引（例如 (province, city, sex, hobby, character, age)），可能会导致性能不佳，因为排序时需要根据 score 字段进行，索引并没有覆盖排序字段。

此时，可以设计一个 辅助索引 来解决这个问题，像这样：

(sex, score)

使用这个索引时，首先会通过 sex = 'female' 进行筛选，然后根据索引顺序 直接读取并排序，无需额外的排序操作。这可以显著提高查询效率。

5.4. 总结：

1. 转换时间字段为枚举值：通过将 latest_login_time 转换为一个 does_login_in_latest_7_days 字段，可以让时间范围查询也能使用索引。

2. 设计辅助索引解决低基数字段问题：对于基数较小的字段（如 sex），可以设计单独的辅助索引，优化查询性能，尤其是当有排序需求时。

3. 多索引结合使用：对于复杂的查询，使用联合索引来覆盖大部分常见查询，同时结合辅助索引来应对一些特殊场景，确保大部分查询都能有效利用索引。
   
   

通过以上分析，你应该能够更灵活地设计联合索引和辅助索引，从而最大限度地提高查询性能，解决不同查询场景下的性能瓶颈。

核心原则是:

尽量使用 一两个复杂的联合索引 来应对 80%以上的查询，然后再通过 少数几个辅助索引 来优化剩余的特殊查询。这样，你就能保证99%以上的查询都能充分利用索引，确保高效的查询性能！