MySQL性能优化（十）in参数列表过长导致的性能问题
有时候可能有人会问：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的参数个数限制到几千之内。
