mysql索引怎么设计_MySQL索引（二）如何设计索引

背景

到这一篇文章，我就已经默认你阅读完了前面的MySQL文章。你可能已经知道了索引本质就是一种数据结构，来加快查询效率的。但是索引要怎么设计呢？这就是这一篇文章的目的。

网上有很多博客会讲到最佳实践，比如单表索引数不能超过5个，联合索引中的字段不能超过5个等等。我只能说这些都是扯淡，任何实践都要放在特定的场景才能生效，所以接下来我们来聊聊索引设计吧。

索引设计的误区

单表索引数不超过5个

索引本质上就是一种数据结构，然而我们可以把索引映射到现实生活中，就好比是《深入浅出MySQL》这本书前面的目录。难不成我们说目录的章节不能超过5个吗？显然不可以的，在这么一个前提下，给一个索引增加一个上限值是不恰当的。所以涉及到慢查询的时候该加索引就加索引，不要给它们设置一个上限就好了。

但是我们也不能在设计索引的时候滥用索引，在数据库表增加太多无用的索引也是会带来一些副作用的，比如DML语句会变得很慢。

给高频字段加索引

这条在绝大部分情况下是对的，但是也不完全对。因为没有对应到场景上，所以不能说完全对，下面我们举例来说明一下。

假如你设计了一个APP用来发送消息的模块，由于发送的消息太多，表中已经有1亿的数据量。为了提高发送消息的速度，领导要求你开发一个功能，给发送消息未成功的用户再尝试发送一次消息。

这样我们就可以设计发送的状态status，这个status有三个值，0代表未发送，1代表已经发送，2代表发送失败。

相关的SQL语句如下：

select * from messages where status = 2;

正常情况下，大家都是发送成功的，发送失败的概率非常低。

然后我们从数据库表中做一个统计：

select status, count(*) from messages group by status;

| status | count(*) |

| 0 | 1000 |

| 1 | 99999000 |

| 2 | 24 |

这种情况称之为数据的倾斜度高。

在这种情况下，我们只需要查找status的值为2的情况即可，只要我们不统计status=1的情况，就不用在高频字段加上索引。所以在这个场景下，给高频字段加索引是不对的。

SQL执行顺序

我们已经知道了SQL的执行计划(explain)，那么SQL的执行顺序呢？是不是跟Java代码一样，按照编码的顺序来执行的，让我们来看看吧。

在SQL语言中，执行顺序是按照一个固定的规则来执行的：

8、 SELECT

9、 DISTINCT

1、 FROM

3、 JOIN

2、 ON

4、 WHERE

5、 GROUP BY

6、 WITH {CUBE|ROLLUP}

7、 HAVING

10、ORDER BY

11、LIMIT

从上面的规则得知，SQL执行顺序是11个步骤，最先执行的是FROM子句，最后执行的是LIMIT子句。

在SQL执行过程中，每一个步骤都会产生一个虚拟表(Virtual Table，简称VT)，用来保存SQL的执行结果。下面分析一下SQL执行的整个过程：FROM。经过 FROM 语句得到一个虚拟表 VT1，如果有多表关联，会先执行笛卡尔积运算。

ON。对虚拟表 VT1 执行 ON 条件筛选，筛选出符合 条件的行，结果放入虚拟表 VT2 中。

JOIN。如果是 OUTER JOIN 类型，上述表中未匹配到的行会作为外部行添加到虚拟表 VT2 中，生成虚拟表 VT3。

WHERE。对虚拟表 VT3 应用 WHERE 条件，将符合 条件的行插入到虚拟表 VT4 中。

GROUP BY。根据 子句中的条件，对行记录进行分组处理，生成虚拟表 VT5。

WITH。对表 VT5 进行 CUBE 或 ROLLUP 操作，生成虚拟表 VT6。

HAVING。对虚拟表 VT6 的结果应用 HAVING 过滤，将符合 条件的记录插入到虚拟表 VT7 中。

SELECT。根据 SELECT 中的条件，选出指定的列，生成虚拟表 VT8。

DISTINCT。对虚拟表 VT8 中的条件进行排重，产生虚拟表 VT9。

ORDER BY。对虚拟表 VT9 中的记录，按照 的条件进行排序操作，生成虚拟表 VT10。

LIMIT。最后根据 LIMIT 的条件，取出指定的 LIMIT 区间的行，生成虚拟表 VT11，并将结果返回给用户。

因此，我们在进行SQL调优时，要按照SQL语句执行的顺序进行优化，重点处理执行成本比较高的部分：多表JOIN，先看JOIN的条件是否有索引，避免笛卡尔积的产生。

检查WHERE条件的索引是否合理，尽可能缩小结果集的大小。

检查GROUP BY条件上是否有索引，如果没有索引，MySQL会通过临时表来完成GROUP BY操作

检查ORDER BY条件是否有索引，如果没有索引，MySQL会使用排序算法将结果集放入在临时表进行排序

查询优化器

现在的关系型数据库，基本都使用了基于成本的优化器。

现在估算成本的代价就是CPU代价+IO代价。在《数据库查询优化器的艺术》这本书讲到，MySQL数据库在有GROUP BY或者ORDER BY的操作下，没有索引的情况下会先走WHERE然后走GROUP BY和ORDER BY。反过来就是有索引的情况下，查询优化器会先走GROUP BY和ORDER BY再走WHERE。

因为数据库优化器会认为GROUP BY和ORDER BY不走索引的代价会大于WHERE不走索引的代价，所以在有索引的情况下，优化器会先优化给GROUP BY和ORDER BY走索引操作。

再深入的知识就要等你们自己去看书才能慢慢理解到了。

总结聊了网上的最佳实践有一些不完全对，并且举例说明了它们在某些情况下不正确的事实

SQL执行顺序还有索引优化部分

查询优化器的代价