MySQL性能优化(十)in参数列表过长导致的性能问题

俊达8个月前技术文章703

有时候可能有人会问:where条件中使用in和or有什么区别,哪种写法性能更好?in参数个数有没有限制?


下面就是一个由于in参数列表过长导致的性能问题。

一个例子

当时使用的是mysql 5.6版本

SELECT *
FROM t_exp t
WHERE t.link_id in (x,x,x,…)
and t.com_id = xx
and t.expense in (x,x,x)
and t.link_type= 1 and ledger_status= 1 AND status = 1


索引

 UNIQUE KEY `uk` (`link_id`,`com_id`,`expense`,`link_type`,`link_com_id`,`trans_batch_id`,`status`),


SQL并不复杂,(link_id,com_id, expense,link_type)是索引的前缀,理论上可以用到这个索引,但是SQL的执行就是慢,查看执行计划,是全表扫描。


做了一些测试:

1、加上force index后,还是全表扫描。

2、减少in条件中传入的参数,当in的数量少于临界值时,可以用上索引,查询的性能也没有问题。


使用optimizer trace

当无法理解优化器为什么没有选择索引时,使用optimizer trace来分析

使用索引的执行计划

(图1)


(图2)



(图3)



1、优化器评估了全表扫描的成本。

2、优化器评估使用索引range扫描的成本。评估range成本时,会将in条件中的组合展开。

3、最终选择使用索引range执行计划。


当in条件中的值超过一定数量时,优化器会放弃使用range执行计划。实际上mysql是限制了range执行计划使用的内存。


全表扫描执行计划

当in条件中的值超过限制时,优化器放弃使用range执行计划,最终使用了全表扫描的执行计划:


(图4)


(图5)


从optimizer trace中可以看到,这里只显示了全表扫描的执行计划。range执行计划根本没记录到trace中。


range优化内存限制

mysql 5.6版本中,range优化内存上限无法通过参数来控制。只能通过调整SQL语句或索引来回避这个问题。

mysql 5.7版本引入了参数range_optimizer_max_mem_size,可以通过加大这个参数来解决in参数列表过长无法使用索引的问题。


实际上,在5.7版本中,如果执行SQL遇到in参数列表过长导致无法使用range优化,会有warning信息:

Warning    3170    Memory capacity of N bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.


mysql官方文档上有关于range优化占用内存的描述(https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#range-optimization-memory-use)

1、or 条件每个占有230字节

2、and条件每个占用125字节

3、单个字段的in条件,会转换成or。多个in条件,以组合形式展开:

col in (p1,p2,p3) => col = p1 or col = p2 or col = p3

c1 in (p1,p2) and c2 in (q1,q2) => (c1 = p1 and c2 = q1) or (c1 = p1 and c2 = p2) or (c1 = p2 and c2 = q1) or (c1 = p2 and c2 = q2) ...



回到我们遇到的SQL,有2个in条件会在range优化时展开。当时采用的解决方法是调整索引字段顺序:

 -- 原先的索引
 UNIQUE KEY `uk` (`link_id`,`com_id`,`expense`,`link_type`,`link_com_id`,`trans_batch_id`,`status`),
 

 -- 调整后的索引
  UNIQUE KEY `uk` (`link_id`,`com_id`,`link_type`,`link_com_id`,`expense`, `trans_batch_id`,`status`),

调整之后,由于业务SQL中没有传入link_com_id, 优化器只会使用(link_id, com_id, link_type)这几个字段做range展开,而expense字段无法用到range执行计划中,减少了优化器需要考虑的range个数。


总结

业务上对in参数列表需要做一定的限制,避免一次传入太多的参数。一般我们可以将in的参数个数限制到几千之内。


相关文章

k8s Krew 插件开发详解

前言:前面我们已经了解过krew插件的作用以及一些比较常用的插件,接下来我们讲一下如何开发krew插件。1、熟悉kubens 插件在开发krew插件之前,我们先通过kubens插件来熟悉krew,我们...

flink集成iceberg访问hive catalog任务报错

flink集成iceberg访问hive catalog任务报错

问题现象flink在集成iceberg后访问hive catalog任务无法执行,但flink自身任务正常,iceberg表任务无法执行,报错如下:Caused by: java....

Redis 热 key

1、hotkeys该方案只能针对于 key 的过期淘汰策略为 allkeys-lfu 或者 volatile-lfu 的实例。-----查看淘汰策略 config get maxmemory-pol...

kafka性能关键参数配置指导

本文为kafka调优过程中主要参数以及参数相关释意,在遇到kafak性能问题时可优先调整一下参数1.Broker参数指导KAFKA_HEAP_OPTS:-Xmx6G   ...

kubernetes openelb

1、背景在云服务环境中的 Kubernetes 集群里,通常可以用云服务提供商提供的负载均衡服务来暴露 Service,但是在本地没办法这样操作。而 OpenELB 可以让用户在裸金属服务器、边缘以及...

开源大数据集群部署(二十二)Flink on yarn部署

开源大数据集群部署(二十二)Flink on yarn部署

flink on yarn部署(每个节点)cd  /root/bigdata/ tar -xzvf  flink-1.14.6-bin-scala_2.12.tgz -C /opt/  ln -s...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。