ToB企服应用市场:ToB评测及商务社交产业平台

标题: MYSQL DQL in 到底会不会走索引&in 范围查询引发的思考。 [打印本页]

作者: 农妇山泉一亩田 时间: 2024-7-16 22:41
标题: 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操作进行了优化、但仍旧不能保证。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)