明明加了索引，为啥mysql这么慢？

零、背景

前段时间给团队小伙伴布置了一个项目，其中涉及一个功能：从 DB 中统计最近 N 天满足筛选条件的数据。

小伙伴做完之后就上线了，与合作方调试时，发现统计逻辑直接超时了，找我寻求帮助。

一、问题解决

问 SQL 怎么写的？ 回答如下：

select count(*) from t where a >= v0 and b = 'v1' and c = 'v2';

由于之前我知道小伙伴加了索引，显然，索引加的有问题。
我说索引加错了。

小伙伴反问，我不是加索引了吗？
但不知道为什么还是很慢，超过 5 秒没跑出来，触发超时了。
索引如下：

index (a, b, c)

由于这个项目这周就需要交付，时间紧迫，我只好先直接告诉小伙伴原因了。

我说：将 a 放在索引最后试试。
如下：

index (b, c, a)

小伙伴试了试，速度飞快，由原先的 5秒超时，变成 0.0005 秒，即 5 毫秒返回了。

二、索引原理

首先需要理解索引的本质是什么。

是的，甚至不需要理解 B+ 树，只需要理解二叉树就可以理解索引了。

假设有下面几条数据。

id=1 b=1 c=1
id=2 b=2 c=2
id=3 b=2 c=1
id=4 b=1 c=2

例如加一个单字段索引 index (b)。

我们查找时，该如何才能快速找到满足条件的数据呢？
显然，需要所有数据基于字段 b 排序，这样就可以基于 b 字段来二分查找了。

字段 b 排序后，数据如下：

id=1 b=1 c=1
id=4 b=1 c=2
id=2 b=2 c=2
id=3 b=2 c=1

索引需要将数据单独复制一份出来储存，为了节省内存，一般只储存索引字段与主键字段，如下

id=1 b=1
id=4 b=1
id=2 b=2
id=3 b=2

这时候，我们使用 b 来查询，可以在 log(n)的复杂度内搜索到满足要求的第一个位置。
随后，只需要顺序扫描，就可以将所有满足要求的数据扫描出来了。

那如果要给两个字段加索引呢，例如 index(b,c)
其实就是我们学的多重排序，先保证第一列 b 有序，之后，第二列在 b 相等时内部再次排序。

id=1 b=1 c=1
id=4 b=1 c=2
id=3 b=2 c=1
id=2 b=2 c=2

此时，如果我们使用 b=1 and c=2 来查询，速度依旧是 log(n)的复杂度。

我们想要搜索 b=1 and c>1 的结果，会发现也可以很快的搜索出结果。
因为可以先二分找到满足 b=1的边界范围，此时，这个范围内的字段 c 依旧是有序的。
因此，可以在 b=1的区间内再次二分找到c>1的边界范围。

反过来，如果我们要搜索 b>=1 and c=1 的结果会怎么样呢？
首先可以二分找到 b>=1 的边界范围，之后发现满足 b>=1 的数据里面， c 字段是没有顺序的。

所以此时，字段 c 就无法走索引了。

三、问题分析

问题修复前索引是 index(a,b,c)。

SQL 里字段 a 是大于条件，所以根据上面的原理，字段 b 和 字段c是无法走索引的。
字段a 是日期，每天有上万条数据，几十天就是几百万数据，搜索就超时了。

问题修复后索引是 index(b,c,a)。

字段 b 和 字段c可以精确匹配走索引，找到所有满足条件的数据。
字段 a 也可以再次走索引，因为可以毫秒级查到结果。

为啥小伙伴自测时没这个问题？

小伙伴自测试也是加了索引 index(a,b,c)，也是很慢。
小伙伴没找到原因后，就各种加其他索引尝试解决，当加上索引 index(b,c) 时速度明细快多了。

我帮小伙伴看另一个问题时，看到了这两个索引。
就说两个索引可以合并的，把字段 a 放到索引最后就行了。

小伙伴回答：理解了，我这就合并下。
没想到，小伙伴的合并就是把index(b,c)索引直接删除。
我说的后半句话小伙伴直接忽视了。

四、最后

回顾这个问题，根本原因还是小伙伴不理解 mysql 多列索引的工作原理。

多列索引的原因简单理解就是，前面的索引可以匹配一个数据区间，这个区间内下个索引字段恰好都是有序的，从而可以再次快速搜索。

《完》

-EOF-

本文公众号：天空的代码世界
个人微信号：tiankonguse
公众号ID：tiankonguse-code