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

俊达1年前技术文章1110

有时候可能有人会问: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的参数个数限制到几千之内。


相关文章

PG的锁(一)

一、表级锁1.1 表级锁模式常见锁模式以及应用场景:ACCESS SHARE :select操作获取该模式锁资源,通常情况下所有只读取不修改表的查询都会获取该模式锁资源ROW SHARE : sele...

Dockerfile编写指南

Dockerfile编写指南

一、背景  在k8s提出抛弃docker,拥抱其他的cri插件的情况下,为什么还有这篇指南呢?首先Dockerfile作为容器打包的标准,已经存在了很多年了。其中的编写技巧到现在也是一直延续的,所以并...

Prometheus与Zabbix的对比

一、Prometheus与Zabbix的对比对比项PrometheusZabbixPrometheus优势Zabbix优势管理二进制文件启动LNMP+编译轻量级Server,便于迁移和维护-配置配置文...

借助cwRsync工具迁移

借助cwRsync工具迁移

服务端安装服务端软件如下,会自动在系统内创建一个系统用户,用户名为:xxx  ,密码为:xxx查看系统用户,会发现自动创建了如下用户:修改配置文件:rsyncd.confuse chroot = fa...

Linux_ACL权限、mask值

acl权限在什么情况下使用:当要给一个用户与文件的属主、属组、其他人权限都不同的时候使用。也就是说,这个用户对应于这个文件不属于三个身份中的任何一种,是属于第四种身份,那么我们就需要使用acl权限去给...

MySQL运维实战之ProxySQL(9.4)proxysql和后端MySQL自动切换

MySQL运维实战之ProxySQL(9.4)proxysql和后端MySQL自动切换

如上图架构,当后端MySQL主库出现问题,发生主备切换后,如何自动将ProxySQL的读写切换到新的主库上?可以通过mysql_replication_hostgroups表配置实现:insert&n...

发表评论    

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