mysql - 为什么 MySQL 不使用我为它创建的索引？

Question

所以我有这个查询:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS
    u.userid,
    u.firstname,
    u.lastname,
    u.job_title,
    u.email,
    u.org_id
FROM piltools.users u
WHERE
    u.org_id = 'VX3'
    AND (
        u.firstname LIKE 'Chris%'
        OR u.lastname LIKE 'Chris%'
        OR CONCAT(u.firstname, ' ', u.lastname) LIKE 'Chris%'
        OR u.email LIKE 'Chris%'
    )
    AND u.firstname !=''
    AND u.lastname !=''
    AND u.deleted = '0'

每个列都有一个单独的索引，我为 deleted,org_id,lastname,firstname,job_title,email,userid 创建了一个 CUSTOM 索引，特别是对于这个查询，但是当我运行时解释它说:

id 1

select_type 简单

表你

类型引用

possible_keys email,firstname,lastname,org_id,deleted,CUSTOM

key org_id

key_len 17

ref 常量

行数 113967

extra 使用位置

为什么它使用单独的 org_id 索引而不是我的具有所有所需字段的自定义索引？

Answer 1

优化器尝试评估处理每个“可能的索引”查询所需的时间，然后实际使用它假定允许最快执行的那个。

现在，在不知道您的数据概况的情况下，无法确定为什么它决定不使用您精心定制的索引，但是:

• 索引只能用于按照列在声明中的出现顺序进行过滤。要使用 CUSTOM 索引，首先它必须通过 deleted 过滤记录(很好，这是 WHERE 子句的一部分)，然后通过 org_id(也是过滤器的一部分)，然后是 lastname，依此类推。

• 您表中的大多数记录可能符合条件 WHERE deleted = 0，因此首先过滤此字段的好处被认为是“低”

• 那么使用 CUSTOM 过滤 org_id 的好处等同于仅在 org_id 上使用索引的好处

• 那么根据条件 lastname !='' 过滤的好处被认为是“低”的，因为(可能)您的大部分记录确实满足此条件

• 过滤条件 lastname LIKE 'Chris%' 的好处由于它是 OR 运算符的操作数而大大降低了

  <

• 与上面相同，firstname LIKE 'Chris%'

• 剩下的索引列只是不用于过滤

• 您的 CUSTOM 索引很大，因为它主要包含字符串列。优化器可能假设加载该索引的成本与其可能的 yield 相比太高

您可以通过添加 FORCE INDEX 来诱使优化器使用您的索引子句(而不是像关键词所建议的那样强制它)。

但优化器通常知道得更多。如果它选择不使用它，我会相信这个决定。如果仅按此顺序在 (org_id, deleted) 或 (org_id, deleted, lastname, firstname) 上建立索引，您可能会获得更好(更快)的结果。