MySQL不使用WHERE IN子句的索引？

我正在尝试优化我的Rails应用程序中的一些数据库查询,我有几个让我难过.他们都IN在WHERE子句中使用an 并且都在进行全表扫描,即使适当的索引似乎已经到位.

例如:

SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))

执行全表扫描并EXPLAIN说:

select_type: simple
type: all
extra: using where
possible_keys: index_user_metrics_on_user_id  (which is an index on the user_id column)
key: (none)
key_length: (none)
ref: (none)
rows: 208

使用IN语句时是否未使用索引或我是否需要以不同方式执行某些操作？这里的查询是由Rails生成的,所以我可以重新审视我的关系是如何定义的,但我想我首先要从数据库级别的潜在修复开始.

请参阅MySQL如何使用索引.

还要验证在向表中添加其他2000行左右的行后,MySQL是否仍执行全表扫描user_metrics.在小型表中,按索引访问实际上比表扫描更昂贵(I/O方式),MySQL的优化器可能会考虑到这一点.

与我之前的帖子相反,事实证明MySQL也在使用基于成本的优化器,这是一个非常好的消息 - 也就是说,ANALYZE如果您认为数据库中的数据量代表了未来的日常使用.

在处理基于成本的优化器(Oracle,Postgres等)时,您需要确保定期运行ANALYZE各种表,因为它们的大小增加了10-15%.(默认情况下,Postgres会自动为您执行此操作,而其他RDBMS会将此责任留给DBA,即您.)通过统计分析,ANALYZE将帮助优化器更好地了解I/O(以及其他相关资源)的数量当在各种候选执行计划之间进行选择时,将涉及例如用于排序的CPU,例如CPU.未能运行ANALYZE可能会导致非常糟糕的,有时甚至是灾难性的计划决策(例如毫秒级查询,有时因为s 上的嵌套循环错误而导致数小时JOIN).

如果在运行后性能仍然不能令人满意ANALYZE,那么你通常可以通过使用提示解决问题,例如FORCE INDEX,在其他情况下,你可能偶然发现了一个MySQL错误(例如,这个较旧的错误,可能会咬你,你是使用Rails' nested_set).

现在,由于您使用的是Rails应用程序,因此ActiveRecord使用提示发出自定义查询而不是继续使用ActiveRecord生成的查询将会很麻烦(并且无法实现目的).

我曾经提到过,在我们的Rails应用程序中,所有 SELECT查询在切换到Postgres之后都降到了100ms以下,而ActiveRecord由于内部表扫描的嵌套循环,甚至在索引时,由MySQL嵌套循环产生的一些复杂连接偶尔会花费15秒或更多是可用的.没有优化器是完美的,你应该知道这些选项.除了查询计划优化之外,要注意的其他潜在性能问题是锁定.但这超出了您的问题范围.

尝试强制使用此索引:

SELECT `user_metrics`.*
FROM `user_metrics` FORCE INDEX (index_user_metrics_on_user_id)
WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))

我只是检查,它确实使用完全相同的查询索引:

EXPLAIN EXTENDED
SELECT * FROM tests WHERE (test IN ('test 1', 'test 2', 'test 3', 'test 4', 'test 5', 'test 6', 'test 7', 'test 8', 'test 9'))

1, 'SIMPLE', 'tests', 'range', 'ix_test', 'ix_test', '602', '', 9, 100.00, 'Using where'

有时MySQL不使用索引,即使有索引也是如此.发生这种情况的一种情况是,优化器估计使用索引将需要MySQL访问表中非常大比例的行.(在这种情况下,表扫描可能会快得多,因为它需要较少的搜索.)

与IN子句匹配的行百分比是多少？