20分钟数据库索引设计实战

在后端开发的工作中如何轻松、高效地设计大量数据库索引呢？通过下面这四步，20分钟后你就再也不会为数据库的索引设计而发愁了。

顺畅地阅读这篇文章需要了解数据库索引的组织方式，如果你还不熟悉的话，可以通过另一篇文章来快速了解一下——数据库索引融会贯通。

这篇文章是一系列数据库索引文章中的第三篇，这个系列包括了下面四篇文章：

1. 数据库索引是什么？新华字典来帮你 —— 理解
2. 数据库索引融会贯通 —— 深入
3. 20分钟数据库索引设计实战—— 实战
4. 数据库索引为什么用B+树实现？—— 扩展

这一系列涵盖了数据库索引从理论到实践的一系列知识，一站式解决了从理解到融会贯通的全过程，相信每一篇文章都可以给你带来更深入的体验。

1. 整理查询条件

我们设计索引的目的主要是为了加快查询，所以，设计索引的 第一步 是整理需要用到的查询条件，也就是我们会在 where 子句、 join 连接条件中使用的字段。一般来说会整理程序中除了insert语句之外的所有 SQL 语句，按不同的表分别整理出每张表上的查询条件。也可以根据对业务的理解添加一些暂时还没有使用到的查询条件。

对索引的设计一般会逐表进行，所以按数据表收集查询条件可以方便后面步骤的执行。

2. 分析字段的可选择性

整理出所有查询条件之后，我们需要分析出每个字段的 可选择性 ，那么什么是可选择性呢？

字段的可选择性指的就是字段的值的区分度，例如一张表中保存了用户的手机号、性别、姓名、年龄这几个字段，且一个手机号只能注册一个用户。在这种情况下，像手机号这种唯一的字段就是可选择性最高的一种情况；而年龄虽然有几十种可能，但是区分度就没有手机号那么大了；性别这样的字段则只有几种可能，所以可选择性最差。所以俺可选择性从高到低排列就是：手机号 > 年龄 > 性别。

但是不同字段的值分布是不同的，有一些值的数量是大致均匀的，例如性别为男和女的值数量可能就差别不大，但是像年龄超过100岁这样的记录就非常少了。所以对于年龄这个字段，20-30这样的值就是可选择性很小的，因为每一个年龄都有非常多的记录；但是像100这样的值，那它的可选择性就非常高了。

如果我们在表中添加了一个字段表示用户是否是管理员，那么在查询网站的管理员信息列表时，这个字段的可选择性就非常高。但是如果我们要查询的是非管理员信息列表时，这个字段的可选择性就非常低了。

从经验上来说，我们会把可选择性高的字段放到前面，可选择性低的字段放在后面，如果可选择性非常低，一般不会把这样的字段放到索引里。

3. 合并查询条件

虽然索引可以加快查询的效率，但是索引越多就会导致插入和更新数据的成本变高，因为索引是分开存储的，所有数据的插入和更新操作都要对相关的索引进行修改。所以设计索引时还需要控制索引的数量，不能盲目地增加索引。

一般我们会根据 最左匹配原则 来合并查询条件，尽可能让不同的查询条件使用同一个索引。例如有两个查询条件 where a = 1 and b = 1 和 where b = 1 ，那么我们就可以创建一个索引 idx_eg(b, a) 来同时服务两个查询条件。

同时，因为范围条件会终止使用索引中后续的字段，所以对于使用范围条件查询的字段我们也会尽可能放在索引的后面。

4. 考虑是否需要使用全覆盖索引

最后，我们会考虑是否需要使用全覆盖索引，因为 全覆盖索引 没有 回表 的开销，效率会更高。所以一般我们会在回表成本特别高的情况下考虑是否使用全覆盖索引，例如根据索引字段筛选后的结果需要返回其他字段或者使用其他字段做进一步筛选的情况。

例如，我们有一张用户表，其中有年龄、姓名、手机号三个字段。我们需要查询在指定年龄的所有用户的姓名，已有索引 idx_age_name(年龄, 姓名) ，目前我们使用下面这样的查询语句进行查询：

SELECT *
FROM 用户表
WHERE 年龄 = ?;

一般情况下，将一个索引优化为全覆盖索引有两种方式：

1. 增加索引中的字段，让索引字段覆盖SQL语句中使用的所有字段

   • 在这个例子中，我们可以创建一个同时包含所有字段的索引 idx_all(年龄, 姓名, 手机号) ，以此提高查询的效率。

2. 减少SQL语句中使用的字段，使SQL需要的字段都包含在现有索引中

   • 在这个例子中，其实更好的方法是将 SELECT 子句修改为 SELECT 姓名 ，因为我们的需求只是查询用户的姓名，并不需要手机号字段，去掉 SELECT 子句多余的字段不仅能够满足我们的需求，而且也不用对索引做修改。