MySQL忽略索引，即使使用索引或强制索引

Question

在这里，我将添加部分查询，因为它是敏感信息，我将使用非实名：

SELECT
  `entity`.`id`,
  `entity`.`requirements`,
  `entity`.`description`,
  `entity`.`status`,
  `entity_videos`.`length`,
  `entity_videos`.`quality`,
  `states`.`name`,
  `uploads`.`id`,
  `uploads`.`name`
    FROM  `entity`
    LEFT JOIN  `states`  ON `states`.`id` = `entity`.`state_id`
    INNER JOIN  `uploads` FORCE INDEX (`uploadable_id_index`)
       ON  `uploads`.`uploadable_type` = 'Entity'
      AND  `uploads`.`category` = 'Icon'
      AND  (`uploads`.`uploadable_id` = `entity`.`id`
              OR  `uploads`.`uploadable_id` = `entity`.`parent_entity_for_icon`
           )
    INNER JOIN  `entity_videos` FORCE INDEX (entity_videos_entity_id_index)
       ON `entity_videos`.`entity_id` = `entity`.`id`
    WHERE  `entity`.`status` = 'active' 

问题是Mysql优化器不想使用uploadable_id_index索引。部分解释：

​

​

如我所知，FORCE INDEX是强制优化器使用索引的，除了优化器不能使用索引的情况之外。我应该怎么做才能强制索引，而不对表进行全面扫描？我试图删除entity_videos_entity_id_index，也尝试将uploads表信息添加到where子句中，但对我没有任何作用。有什么想法吗？非常感谢您的帮助

更新：

在“Barmar”和“PaulSpiegel”的帮助下，我发现这个问题出现在(uploads.uploadable_id = entity.id OR uploads.uploadable_id = entity.parent_entity_for_icon)中。一段时间以来，我发现在我的情况下，最好的解决方案是：

 SELECT
  `entity`.`id`,
  `entity`.`requirements`,
  `entity`.`description`,
  `entity`.`status`,
  `entity_videos`.`length`,
  `entity_videos`.`quality`,
  `states`.`name`,
  `icon`.`id`,
  `icon`.`name`,
  `parent_offer_icon`.`id`,
  `parent_offer_icon`.`name`
    FROM  `entity`
    LEFT JOIN  `states`  ON `states`.`id` = `entity`.`state_id`
    LEFT JOIN  `uploads` as `icon` FORCE INDEX (`uploadable_id_index`)
       ON  `icon`.`uploadable_type` = 'Entity'
      AND  `icon`.`category` = 'Icon'
      AND  `icon`.`uploadable_id` = `entity`.`id`
    LEFT JOIN  `uploads` as `parent_offer_icon` FORCE INDEX (`uploadable_id_index`)
       ON  `parent_offer_icon`.`uploadable_type` = 'Entity'
      AND  `parent_offer_icon`.`category` = 'Icon'
      AND  `parent_offer_icon`.`uploadable_id` = `entity`.`parent_entity_for_icon`
    INNER JOIN  `entity_videos` FORCE INDEX (entity_videos_entity_id_index)
       ON  `entity_videos`.`entity_id` = `entity`.`id`
    WHERE  `entity`.`status` = 'active'
      AND  (parent_offer_icon.id IS NOT NULL
              OR  icon.id IS NOT NULL ) 

我仍然愿意接受其他建议:)

Answer 1

让我们从把讨厌的OR转换成UNION开始

( SELECT  e.id AS eid,
          u.id AS uid,
          u.name AS uname
    FROM `uploads` AS u 
    INNER JOIN  `entity` AS e
       ON  u.`uploadable_id` = e.`id`
    WHERE  u.`uploadable_type` = 'Entity'
      AND  u.`category` = 'Icon'
      AND  e.status = 'active'
) UNION DISTINCT
( SELECT  e.id AS eid,
          u.id AS uid,
          u.name AS uname
    FROM `uploads` AS u 
    INNER JOIN   `entity` AS e
       ON  u.`uploadable_id` = e.`parent_entity_for_icon` 
    WHERE  u.`uploadable_type` = 'Entity'
      AND  u.`category` = 'Icon'
      AND  e.status = 'active'
)

这将需要以下几个索引：

uploads:  INDEX(uploadable_type, category, uploadable_id, id, name)
entity:   INDEX(parent_entity_for_icon, status, id)

(我猜entity有PRIMARY KEY(id)？请提供SHOW CREATE TABLE，这样我就不用猜测了。)如果这么长的索引有问题，请告诉我；我可能会提供一个解决办法。

通过将OR转换为UNION，可以为每个部分使用不同的索引。使用OR，优化器通常会进行一些效率低下的操作。

请验证上述查询运行速度快，并产生合理的输出。然后..。

SELECT  e.`id`, e.`requirements`, e.`description`, e.`status`,
        ev.`length`, ev.`quality`,
        s.`name`,
        uid, uname
    FROM ( the-query-above ) AS i
    JOIN `entity` AS e  ON e.id = i.eid
    LEFT JOIN  `states` AS s  ON s.`id` = e.`state_id`
    INNER JOIN  `entity_videos` AS ev  ON ev.`entity_id` = i.eid

除了每个表上的PRIMARY KEY(id)之外，您还需要

entity_videos:  INDEX(entity_id)  -- good, or
entity_videos:  INDEX(entity_id, length, quality)  -- better ("covering")

不要使用FORCE INDEX。

关于创建索引的更多信息：mysql