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

Question

我正在尝试优化Rails应用程序中的一些数据库查询，但有几个让我感到困惑。它们都使用INin WHERE子句，并且都进行了全表扫描，即使适当的索引似乎已经到位。例如：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: simpletype: allextra: using wherepossible_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生成的，因此我可以重新定义我的关系的定义，但是我认为我首先要从数据库级别的潜在修复开始。

Answer 1

在向表中添加额外的2000左右行之后，还要验证MySQL是否仍执行全表扫描user_metrics。在小型表中，按索引访问实际上比表扫描更昂贵（在I / O方式上），MySQL的优化程序可能会考虑到这一点。

与我之前的文章相反，事实证明MySQL也在使用基于成本的优化器，这是一个好消息-也就是说，ANALYZE如果您认为数据库中的数据量足以代表数据库运行了至少一次，将来的日常使用。

在处理基于成本的优化器（Oracle，Postgres等）时，您需要确保ANALYZE在其大小增加10-15％以上时，定期在各种表上运行。（默认情况下，Postgres将自动为您执行此操作，而其他RDBMS将把此职责留给DBA（即您）。）通过统计分析，ANALYZE将有助于优化程序更好地了解I / O量（以及其他相关资源）在各种候选执行计划之间进行选择时，将涉及到诸如CPU之类的，例如用于排序的信息。运行失败ANALYZE可能会导致非常糟糕的规划决策，有时甚至是灾难性的决策（例如，由于s 上的嵌套循环不好，毫秒查询有时会花费数小时JOIN）。

如果运行后性能仍然不能令人满意ANALYZE，则通常可以使用提示来解决该问题，例如FORCE INDEX，而在其他情况下，您可能会偶然发现MySQL错误（例如，这个较旧的错误可能会咬住您的本人）。使用Rails的nested_set）。

现在，由于您使用的是Rails应用程序，因此ActiveRecord使用提示来发出自定义查询而不是继续使用ActiveRecord-生成的查询将很麻烦（并且破坏的目的）。

我曾经提到过，在我们的Rails应用程序中，所有 SELECT查询切换到Postgres后都降至100ms以下，而ActiveRecord由于内部表扫描的嵌套循环，即使使用索引，由MySQL 生成的某些复杂联接有时也可能需要15s甚至更长的时间。可用。没有哪个优化器是完美的，您应该注意这些选择。除了查询计划优化之外，还需要注意其他潜在的性能问题。但是，这超出了您的问题范围。

Answer 2

尝试强制使用此索引：

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'

Answer 3

我知道我迟到了。但是希望我可以帮助其他有类似问题的人。

最近，我遇到了同样的问题。然后，我决定使用自我结合来解决我的问题。问题不是MySQL。问题是我们。子查询的返回类型与我们的表不同。因此，我们必须将子查询的类型转换为选择列的类型。下面是示例代码：

select `user_metrics`.* 

from `user_metrics` um 

join (select `user_metrics`.`user_id` in (N, N, N, N) ) as temp 

on um.`user_id` = temp.`user_id`

或我自己的代码：

旧：（不使用索引：〜4s）

SELECT 

    `jxm_character`.*

FROM

    jxm_character

WHERE

    information_date IN (SELECT DISTINCT

            (information_date)

        FROM

            jxm_character

        WHERE

            information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY))

        AND `jxm_character`.`ranking_type` = 1

        AND `jxm_character`.`character_id` = 3146089;

新：（使用指数：〜0.02s）

SELECT 

    *

FROM

    jxm_character jc

        JOIN

    (SELECT DISTINCT

        (information_date)

    FROM

        jxm_character

    WHERE

        information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY)) AS temp 

        ON jc.information_date = STR_TO_DATE(temp.information_date, '%Y-%m-%d')

        AND jc.ranking_type = 1

        AND jc.character_id = 3146089;

jxm_character：

记录：〜350万

PK：jxm_character（信息日期，ranking_type，character_id）

SHOW VARIABLES LIKE '%version%';

'protocol_version', '10'

'version', '5.1.69-log'

'version_comment', 'Source distribution'

最后说明：确保您了解MySQL索引最左边的规则。

P / s：对不起，我的英语不好。我发布了代码（当然是生产版）以清除我的解决方案：D。