为什么MySQL不会使用我的索引？ [英] Why won't MySQL use my index?

问题描述

如果我有下表：

CREATE TABLE `mytable` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_first_letter` (`name`(1)),
  KEY `name_all` (`name`)
)

MySQL会选择在 name_all 索引上使用 name_first_letter 索引？如果是这样，在什么条件下会发生这种情况？

Will MySQL ever choose to use the name_first_letter index over the name_all index? If so, under what conditions would this happen?

我做了一些快速测试，我不确定MySQL是否会选择 name_first_letter 索引即使使用索引提示：

I have done some quick tests and I'm not sure if MySQL will choose the name_first_letter index even when using index hints:

-- This uses name_all
EXPLAIN SELECT name FROM mytable
WHERE SUBSTRING(name FROM 1 FOR 1) = 'T';

-- This uses no index at all
EXPLAIN SELECT name FROM mytable USE INDEX (name_first_letter)
WHERE SUBSTRING(name FROM 1 FOR 1) = 'T';

任何MySQL专家都可以对此有所了解吗？是否在此列上有 name_first_letter ？

Can any MySQL gurus shed some light on this? Is there even a point to having name_first_letter on this column?

编辑：问题标题不是非常正确。

推荐答案

使用索引进行查询是没有意义的，因为您选择的是全名栏目。这意味着MySQL无法单独使用索引来满足查询。

It will not make sense to use the index for your query, because you are selecting the full name column. That means that MySQL cannot use the index alone to satisfy the query.

此外，我认为MySQL无法理解 SUBSTRING（名称FROM 1） FOR 1）表达式等同于索引。

Further, I believe that MySQL cannot understand that the SUBSTRING(name FROM 1 FOR 1) expression is equivalent to the index.

但是，如果单独的索引可以满足查询，MySQL可能会使用索引。例如：

MySQL might, however, use the index if the index alone can satisfy the query. For example:

select count(*)
  from mytable
 where name like 'T%';

但即使这取决于你的统计数据（暗示应该有效）。

But even that depends on you statistics (hinting should work).

MySQL的部分索引功能旨在节省空间。 （通常）同时具有部分和完整列索引是没有意义的。你通常会放弃较短的一个。可能有一个罕见的情况，它是有道理的，但一般没有意义。

MySQLs partial index feature is intended to save space. It does (usually) not make sense to have both, the partial and the full column index. You would typically drop the shorter one. There might be a rare case where it makes sense, but doesn't make sense in general.

这篇关于为什么MySQL不会使用我的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！