MySQL - "Select like"语句未使用索引

Question

我一直在使用MySQL（5.5.24，WinXP）上的索引进行实验，但我无法找到服务器在使用LIKE时为什么不使用一个索引的原因。

例子如下：

我创建了一个测试表：

create table testTable (
  id varchar(50) primary key,
  text1 varchar(50) not null,
  startDate varchar(50) not null
) ENGINE = innodb;

然后，我给 startDate 添加了一个索引。（请不要问为什么这个列是文本而不是日期时间...这只是一个简单的测试）：

create index jeje on testTable(startdate);
analyze table testTable;

之后，我添加了将近20万行数据，其中startDate有3个可能的值。（每个值大约出现了三分之一，即近70,000次）

因此，如果我运行以下命令：EXPLAIN

explain select * from testTable use index (jeje) where startDate = 'aaaaaaaaa';

答案如下：

id = 1
select_type = SIMPLE
type = ref
possible_keys = jeje
key = jeje
rows = 88412
extra = Using where

所以，密钥被使用，并且行数接近200,000/3，所以一切都很好。

问题是，如果我将查询更改为：（只是将“=”更改为“LIKE”）：

explain select * from testTable use index(jeje) where startDate LIKE 'aaaaaaaaa';

在这种情况下，答案是：

id = 1
select_type = SIMPLE
type = ALL
possible_keys = jeje
key = null
rows = 176824
extra = Using where

所以，索引现在没有被使用（键为空，并且接近于完整表...因为type=all建议）。MySQL文档称LIKE确实利用索引。

那么，我在这里看不到什么？问题出在哪里？

感谢您的帮助。

- Diego Buzzalino

Answer 1

如果索引访问超过表行数的30%，MySql将忽略索引。您可以尝试使用FORCE INDEX [index_name]，它将在任何情况下都使用索引。

sysvar_max_seeks_for_key的值也影响是否使用索引：

• http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_seeks_for_key

  尝试将此值更改为较小的数字。

在SO上搜索类似的请求。

- UBIK LOAD PACK

---

感谢Ubik的回答。基于此，我将表格数据更改为接近300k行，startDate列有5个可能的值（每个值占20%）。 - Diego Buzzalino

你的回答没有帮助解决问题，但它确实帮助我以不同的方式思考。到目前为止，我还没有“解决方案”，但有一个关于可能是问题根源的想法。请看我的回答。感谢你的帮助！ - Diego Buzzalino

Answer 2

根据Ubik comment和数据变化，我发现： 这些情况下确实使用了索引：

- explain select * from testTable force index jeje where startDate like 'aaaaaaadsfadsfadsfasafsafsasfsadsfa%';
- explain select * from testTable force index jeje where startDate like 'aaaaaaadsfadsfadsfasafsafsasfsadsfa%';
- explain select * from testTable force index jeje where startDate like 'aaa';

但是，当我使用这个查询时，索引没有被使用：

- explain select * from testTable force index jeje where startDate like 'aaaaaaaaa';

基于 startDate 列中所有值的长度相同（9个字符）这一事实，当我使用 LIKE 命令和一个9个字符的常量进行查询时，也许 MySQL 会优先考虑某些性能算法而不使用索引，直接访问表。

我的担忧是看看我在原始测试中是否犯了什么错误，但现在我认为索引和测试是正确的，MySQL 在某些情况下决定不使用索引... 我将依靠此功能。

对我来说，这是一个已完成的任务。如果有人想在主题上添加什么，欢迎您。

- Diego Buzzalino