Mysql未使用正确的索引

Question

我有一个生成SQL的框架。其中一个查询是使用我的索引"A“，并在7秒内返回结果。我知道我可以优化它，于是我创建了一个索引"B“。

现在，如果我运行"explain my query"，它仍然使用我的索引A。但是，如果我强制使用索引B，我会在1秒内得到结果(速度快7倍)。

因此，很明显，我的索引B比索引A快。我不能使用"force index“或"use index”命令，因为我的sql是从不支持它的框架中生成的。

那么，为什么mysql不自然地使用最快的索引呢？有没有一种方法可以告诉mysql始终使用某个索引，而不需要添加" use“或"force”。

查询：

SELECT *
FROM soumission
LEFT OUTER JOIN region_administrative 
ON soumission.region_administrative_oid=region_administrative.oid 
WHERE (soumission.statut=2 
AND ((soumission.telephone LIKE '%007195155134070067132211046052045128049212213255%' 
OR (soumission.autre_telephone LIKE '%007195155134070067132211046052045128049212213255%')) 
OR (soumission.cellulaire LIKE '%007195155134070067132211046052045128049212213255%'))) 
ORDER BY soumission.date_confirmation DESC, soumission.numero;

我在多个列上添加了索引"statut"，“added”，"autre_telephone"，"cellulaire“

如果我强制使用这个索引，我的查询速度会快7倍，但如果我不指定要使用的索引，它会使用另一个索引(仅针对statut字段)，速度会慢7倍

下面是如果我选择了一个大的日期周期(使用了错误的索引)的解释

​

​

下面是我选择一个小日期窗口的时候

​

Answer 1

这似乎就是你正在做的……

SELECT  s.*, ra.*
    FROM  soumission AS s
    LEFT OUTER JOIN  region_administrative AS ra  ON s.region_administrative_oid=ra.oid
    WHERE  s.statut = 2
      AND  (      s.telephone       LIKE '%007195155134070067132211046052045128049212213255%'
              OR  s.autre_telephone LIKE '%007195155134070067132211046052045128049212213255%'
              OR  s.cellulaire      LIKE '%007195155134070067132211046052045128049212213255%' 
           )
    ORDER BY  s.date_confirmation DESC, s.numero;

如果你不需要ra.*，那就去掉LEFT JOIN。

您提出的多列索引是无用的，除非...少于20%的行的statut = 2。在这种情况下，它将只使用索引的第一列。

OR击败了索引。(见下文)

LIKE上的前导通配符击败了索引。您需要前导通配符还是尾随通配符？

在ORDER BY中混合使用DESC和ASC会导致无法使用索引来避免排序。

那么，该怎么办呢？用另外一个表来表示电话号码，而不是只有3个电话号码对应的3列。然后，对于给定的soumission，可以有任意数量的行。由于避免了OR，因此搜索该表可能会更快--但前提是要去掉前导通配符。

(那是一个非常长的电话号码！这是真的吗？)

Answer 2

至于查询本身：

1. 尝试避免前导LIKE通配符(在下面的查询中删除)。
2. 将查询拆分为几个部分，并结合UNION子句，以便可以使用索引。

因此，创建以下索引：

ALTER TABLE `region_administrative` ADD INDEX `region_administrativ_idx_oid` (`oid`);
ALTER TABLE `soumission` ADD INDEX `soumission_idx_statut_oid_cellulaire` (`statut`,`region_administrative_oid`,`cellulaire`);
ALTER TABLE `soumission` ADD INDEX `soumission_idx_statut_oid_telephone` (`statut`,`region_administrative_oid`,`autre_telephone`);
ALTER TABLE `soumission` ADD INDEX `soumission_idx_statut_oid_telephone` (`statut`,`region_administrative_oid`,`telephone`);

然后尝试这个查询：

SELECT
        * 
    FROM
        ((SELECT
            * 
        FROM
            soumission 
        LEFT OUTER JOIN
            region_administrative 
                ON soumission.region_administrative_oid = region_administrative.oid 
        WHERE
            (
                soumission.statut = 2 
                AND (
                    (
                        soumission.cellulaire LIKE '007195155134070067132211046052045128049212213255%'
                    )
                )
            ) 
        ORDER BY
            soumission.date_confirmation DESC,
            soumission.numero) 
    UNION
    DISTINCT (SELECT
        * 
    FROM
        soumission 
    LEFT OUTER JOIN
        region_administrative 
            ON soumission.region_administrative_oid = region_administrative.oid 
    WHERE
        (soumission.statut = 2 
        AND (((soumission.autre_telephone LIKE '007195155134070067132211046052045128049212213255%')))) 
    ORDER BY
        soumission.date_confirmation DESC,
        soumission.numero) 
UNION
DISTINCT (SELECT
    * 
FROM
    soumission 
LEFT OUTER JOIN
    region_administrative 
        ON soumission.region_administrative_oid = region_administrative.oid 
WHERE
    (soumission.statut = 2 
    AND ((soumission.telephone LIKE '007195155134070067132211046052045128049212213255%'))) 
ORDER BY
    soumission.date_confirmation DESC,
    soumission.numero)
) AS union1 
ORDER BY
union1.date_confirmation DESC,
union1.numero