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

俊达2周前技术文章29

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


相关文章

Hive优化之Spark执行引擎的参数优化(二)

Hive优化之Spark执行引擎的参数优化(二)

        Hive是大数据领域常用的组件之一,主要是大数据离线数仓的运算,关于Hive的性能调优在日常工作和面试中...

Presto开发语句简介

Presto开发语句简介

根据presto中的结构配置,catalog表示连接,主要看presto中catalog文件夹下的配置,一般包含hive、mysql等,其中可以根据业务的不同设置多个配置文件。schema表示连接中的...

Ambari部署

Ambari部署

Ambari 官方资料入口:https://www.cloudera.com/products/open-source/apache-hadoop/apache-ambari.htmlAmbari 相...

CDH实操--集群ip替换

CDH实操--集群ip替换

1 背景恰逢机房迁移,自建CDH集群需要调整ip网段。。。2 操作步骤2.1 停止CDH集群2.1.1 控制台停止集群服务2.1.2 控制台停止Cloudera Management Ser...

CDH实操--kudumaster迁移

CDH实操--kudumaster迁移

1 概述本次kudumaster迁移,中间不需要停kudu集群(会涉及滚动重启kudu角色); 注:若因为任务持续运行导致kudu停止超时可手动一台台停止-启动2 master迁移将cdh2中的ma...

MySQL性能优化(二)优化排序操作

MySQL性能优化(二)优化排序操作

排序是数据库的基本功能。一个例子SELECT * FROM audit_log  WHERE user_id = xxx AND&nb...

发表评论    

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