MYSQL DQL in 到底会不会走索引&in 范围查询引发的思考。
前情引子
in 会不会走索引？很多人肯定会回答、废话、如果命中了索引、那肯定会走。
其实我和大多数人一样、一开始也是这么想的、直至有一个血淋淋的案子让我有所改观、有所思考。
背景介绍
业务的工单表、我们分了64张、以userId作为分表键、业务实际场景中未使用到搜索引擎、主要是一些B端业务。
业务有一个场景是使用userId作为条件 使用in语句查询工单数据。
这里分析一下、
- 第一个userId作为分表键作为查询条件是合理的
- 第二个、该业务场景下的SQL为userId字段添加了索引、是考虑到的
实际发生问题
该需求上线之后、我们发现个别B端使用人员、他需要查询userId为5w左右的条件查询、经日志查询该查询的耗时大概在35S左右、正常查询都是3S以内。当问题发生的时候、我就在分析、in 到底有没有走索引、如下
- 5w/64张表=781 个 假设按照平均分配 每个表的in包含的个是不足1k
- 第二个每张分表其实都是添加了索引的
- 数据库的监控服务没有查询到有慢SQL出现
综合以上初步判断、这么小的量、如果命中索引、那不该需要查询这么长的时间。
解决方案
既然出现了问题、那肯定是要解决方案的、思考的角度如下：
- 分表情况下、无法使用大家熟悉的explain 语句 直接查询数据库、让数据库告诉你有没有使用索引、当然、如果你指定其中一张分表还是可以使用explain语句的
- 数据库分表、DB的操作实际上是将每张表的查询结果出来之后、全部load到内存聚合之后再返回给实际调用他的Java服务的
- 假设这里命中了索引、基于第二点那慢的另一个因素可能就是DB服务器内存被打满了
这里我基于第三点的假设、对于业务代码进行了改造
使用in条件进行查询
限制了每次查询数据库in所包含的userId个数最多是5000个、即时就是我们经常说的批量查询、这样子做、最大量的5w就会分成10批去查询数据库、结果再聚合。而分到每张表的in包含的个数、按平均情况就只有了78个左右了、改成这种写法、从宏观的角度、就是把DB的一部分压力转移到业务服务器上。
结果如何
新的代码拿到正式环境进行验证之后、使用同样的用户进行测试、in的条件个数仍是5w、但最后的查询结果仅在3S左右就返回了、完成了从35S到3S的质的飞跃的提升。
对于解决问题而言、我们已经是成功的Solver、We are white cat or black cat.
But 这里有仍有两个疑问、
批次的数量具体是哪个值合适2k or 5k、这里我的5k值是与我的正常业务的水平相一致的、所以我说是适合我的、但并不是适合所有场景、所有人。
从最后的结果提升来看、我更倾向于改造后的代码既是走了索引、也为DB减少了压力、才会有这么高的性能提升。
我请教一位现世高人
- 索引的类型和质量：B-TREE、不需要回表查询、完全命中。
- in条件值的分布：分布均匀可能会使用到索引
- 成本估算：MYSQL的查询优化器会基于统计信息对不同的执行计划进行成本估算？全表嫂 or 还是用索引比较合适呢？
- 系统配置和资源限制：innodb-buffer-pool-size？系统的资源使用情况 都会影响执行计划的选择
- 数据库的版本和配置：5.5及以上查询优化器对in操作进行了优化、但仍旧不能保证。
