为了账号安全,请及时绑定邮箱和手机立即绑定

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

/ 猿问

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

白板的微信 2019-12-25 14:58:09

我正在尝试优化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: 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生成的,因此我可以重新定义我的关系的定义,但是我认为我首先要从数据库级别的潜在修复开始。


查看完整描述

3 回答

?
慕粉4167745

在向表中添加额外的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甚至更长的时间。可用。没有哪个优化器是完美的,您应该注意这些选择。除了查询计划优化之外,还需要注意其他潜在的性能问题。但是,这超出了您的问题范围。


查看完整回答
反对 2019-12-25
?
犯罪嫌疑人X

尝试强制使用此索引:


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'


查看完整回答
反对 2019-12-25
?
绝地无双

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


最近,我遇到了同样的问题。然后,我决定使用自我结合来解决我的问题。问题不是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。


查看完整回答
反对 2019-12-25

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信