MySQL索引（二）如何设计索引

小鱼儿 2022-11-22 07:36 212阅读 0赞

## 背景 ##

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

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

## 索引设计的误区 ##

### 1、单表索引数不超过5个 ###

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

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

### 2、给高频字段加索引 ###

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

假如你设计了一个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 <select_list>
    1、 FROM <left_table>
    3、 <join_type> JOIN <right_table>
    2、 ON <join_condition>
    4、 WHERE <where_condition>
    5、 GROUP BY <group_by_list>
    6、 WITH {CUBE|ROLLUP}
    7、 HAVING <having_condition>
    10、ORDER BY <order_by_condition>
    11、LIMIT <limit_number>

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

在SQL执行过程中，每一个步骤都会产生一个虚拟表（Virtual Table，简称VT），用来保存SQL的执行结果。下面分析一下SQL执行的整个过程：

1.  FROM。经过 FROM 语句得到一个虚拟表 VT1，如果有多表关联，会先执行笛卡尔积运算
2.  ON。对虚拟表 VT1 执行 ON 条件筛选，筛选出符合 <join\_condition> 条件的行，结果放入虚拟表 VT2 中。
3.  JOIN。如果是 OUTER JOIN 类型，上述表中未匹配到的行会作为外部行添加到虚拟表 VT2 中，生成虚拟表 VT3。
4.  WHERE。对虚拟表 VT3 应用 WHERE 条件，将符合 <where\_condition> 条件的行插入到虚拟表 VT4 中。
5.  GROUP BY。根据 <group\_by\_list> 子句中的条件，对行记录进行分组处理，生成虚拟表 VT5。
6.  WITH。对表 VT5 进行 CUBE 或 ROLLUP 操作，生成虚拟表 VT6。
7.  HAVING。对虚拟表 VT6 的结果应用 HAVING 过滤，将符合 <having\_condition> 条件的记录插入到虚拟表 VT7 中。
8.  SELECT。根据 SELECT 中的条件，选出指定的列，生成虚拟表 VT8。
9.  DISTINCT。对虚拟表 VT8 中的条件进行排重，产生虚拟表 VT9。
10. ORDER BY。对虚拟表 VT9 中的记录，按照 <order\_by\_condition> 的条件进行排序操作，生成虚拟表 VT10。
11. 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执行顺序还有索引优化部分
 *  查询优化器的代价

## 参考 ##

*  《数据库查询优化器的艺术》