从慢查询实例中分析mysql索引及查询优化（一）

最新推荐文章于 2024-08-16 09:15:00 发布

奥利弗的巨大泰迪

最新推荐文章于 2024-08-16 09:15:00 发布

阅读量1k

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/Olive_ZT/article/details/81416719

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

写这个博客也算是阴差阳错。上次做了个需求：退款列表导出（可以翻翻之前的博客，有写）。虽然事情过了那么久，现在我还是想要吐槽下，这个需求是真的坑。。

这个博客大概是这么分布的：第一篇就根据实际场景来分析下索引及查询上的优化；第二篇和第三篇（如果我还能写得出来第三篇的话。。）来系统地整理下mysql的索引及查询的优化。

———————————————— 接下来是正文 ——————————————————

那么问题来了，有一次客服同学急急忙忙来找我，说导出几条记录都失败了，查看了错误日志显示错误如下：

好了，不要盯着人家的红色框框看了，重点不是那里。

重点是出现了慢查询日志，我知道看图肯定看不出来这条sql是啥，于是我贴心的贴了出来：

select* （这里其实不是这么暴力地拿全列数据，为了方便，就*表示了）

from Refund

FORCE INDEX (idx_sellerUserId_created_status)

WHERE sellerUserId = 40955202 and status IN(800, 900) and created >= 946656000 AND created < 2051193600 order by created desc limit 0, 50

首先来分析下这条sql要做啥，其实就是在Refund（退款表）这个表中查询所有sellerUserId为40955202，并且状态在800及900（800，900等表示退款处于的状态，如退款申请，退款完成等）之间的，并且创建时间大于等于946656000且小于2051193600（这是两个时间戳）的所有记录，并且将所有这些记录根据created（创建时间）进行逆序排序后取开头50条。注意到这个sql有一个 FORCE INDEX (idx_sellerUserId_created_status)，这个意思是让sql强制走idx_sellerUserId_created_status这个索引，至于为什么有时候需要强行走索引，这个涉及到mysql的查询优化，之后会讲到

看了上面这条sql，有没有发现什么问题？可能我们会觉得有问题，但是并不能很清晰地说出来。那么，首先来看下mysql的执行计划吧！

看出问题来了么？在我看来，这条sql至少存在3条问题！以下一条一条来分析：

cerated的时间查询范围过大，946656000转化为北京时间是2000/1/1 0:0:0，2051193600转化为北京时间是2035/1/1 0:0:0。那么这个时间段段含义是什么呢？在我应用的具体执行中，当用户在导出退款列表时没有选择导出的时间段时，就会给定一个默认的搜索时间段，而恰巧我设置了比较大的起止时间。这个时间段本来是不需要这么大的，这个时间段基本将这个用户所有的退款记录都包含了。我们来看下这个用户究竟有多少条退款记录：好家伙，1000多w条记录。。那么有人会说，时间段大也没关系，不是还有其他索引项可以进行约束吗？那么我们来看看第二个问题
索引真的用上了么？为了防止mysql优化器出现石志行为，这条sql特意告诉mysql：您啥也不用想！就用这个索引！那么我们来看下这个索引：idx_sellerUserId_created_status。很明显，这是一个多列索引，是一个由sellerUserId、created、status三列组成的一个联合索引。再来看下这条sql的where条件：WHERE sellerUserId = 40955202 and status IN(800, 900) and created >= 946656000 AND created < 2051193600 order by created。乍一看，好像索引没用上啊？索引的排序是sellerUserId、created、status，而where条件的顺序是sellerUserId、status、created。那索引是不是没用了？当然不是的！mysql如果连这点优化都没有考虑，那和咸鱼有什么区别？？在这里，mysql会将where条件做一个优化，where条件内的顺序是不影响此索引的使用的。那么这个索引真的生效了么？事实是并没有。我们来分析下这个索引，注意看这个条件：created >= 946656000 AND created < 2051193600，很明显这是一个范围查询，而在多列索引的使用中，如果碰到了范围条件，那么存储引擎是不会使用范围条件右边的列的，也就是说，咱们这条sql，索引到了idx_sellerUserId_created就到头了，不会再使用status做索引。强制走的这个索引，失效了！这条查询只用到部分的索引
查询真的有必要那么写么？我们可以看到where条件中限制了created字段的查询范围，而在排序时又需要根据created字段进行逆序排序，之后又只取了最终结果的开头50行。尽管我们很理想化地设想mysql只会返回我们需要的数据，比如在这条sql中我们真的很希望mysql可以只扫描50行就搜索我们需要的那50条数据。然而，在真正的查询中我们惊人地发现mysql读取了超过千万条的数据！一部分的原因在于查询过程中使用了不太合适的索引，另一方面在于我们的查询太累赘了，要想我们的查询不那么累赘，首先要搞清楚我们要查询什么。如上就分析过，我们需要的只是该用户创建的、处于某些状态的、最新的50条记录，那么在这种情况下created字段的范围还有什么意义？

那么针对上面几个问题，接下来我们想办法来优化下这条查询吧！以上的截图和问题都来源于线上，因为接下来的操作可能会涉及到表结构，所以接下来使用一个线下的db来进行模拟

首先看下表的结构：

然后我们来查询下退款数量最多的用户：

我们发现id为93106308的用户退款数量最多，大概有5w多行，那么就用他来做模拟吧！

首先来模拟下最初的sql语句：

explain select* from Refund force index (idx_sellerUserId_created_status) where sellerUserId=93106308 and status IN (700, 900, 1000) and created >= 1278691200 AND created < 1531152000 order by created desc limit 0, 10

如上图所示的执行结果，我们可以看到，mysql确实使用了idx_sellerUserId_created_status这个索引（这其实是废话，你都强制人家走了，，），然后看rows这一行：64867！存储引擎扫描来6w多行才得到我们需要的数据！可以看到在Extra这一列中显示的是：Using index condition，这表示查询部分使用了索引，对于不在索引中的列需要回表查询数据，这也如我们预想的那样

那么我们来试试存储引擎的自动优化吧：

哈哈，存储引擎的查询优化其实并没有想象中的那么好（但也没那么差），去除了强制索引后存储引擎自己找到了一个它认为的最优的索引：idx_created，可以看到Extra这一列中显示的是：Using where，这表示查询需要回表捞数据以后再使用where条件来进行过滤。那么和上面的情况做对比，在性能上其实是毫无差别。

那我们在索引上来优化下吧，为了避免遇到范围查询而使右边索引列失效，我们来将idx_sellerUserId_created_status索引修改为idx_sellerUserId_status_created，并且让存储引擎强制走这个索引：

发现变化了么？查询扫描的行数少了很多！

那接下来再优化下查询语句：

可以看到，因为优化了查询语句，根据created进行逆序排序，导致最后扫描的行数大为减少！

在这里，这条查询的优化并不是在任何场景下都是能保证有很高的查询性能的。我们知道索引在db中维护了一个b+tree 结构（innodb引擎默认结构）。idx_created索引将所有的数据根据created来建立了一个b+tree，当我们要查询的用户数据恰好在b+tree的后方的时候，查询也是需要扫描不少行的：