mysql - 使用尾随通配符搜索 first_name 和 last_name 的最佳索引？

Question

我的第一个attempt在一个问题被证明是令人困惑的问题上，我收到了一些混合的答案(可能是由于我的问题令人困惑)。这是一个不同的更好的问题...

假设我的表在 MySQL 中看起来像这样:

CREATE TABLE `people` (
    `person_id` INT(11),
    `alias_num` TINYINT(3),
    `first_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`person_id`,`alias_num`)
  )
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

像这样的数据:

person_id alias_num first_name last_name
--------- --------- ---------- ---------
1         1         John       Smith
2         1         Joe        Smith
3         1         Bill       Smith     # <-- Notice this guy has 3 aliases
3         2         Billy      Smith     # <--
3         3         William    Smith     # <--
4         1         Susan      Thompson
...

假设 jo 和 smi 被输入到一个 HTML 搜索表单中(两个字段都是必需的)，我的查询将始终是这样的:

SELECT person_id FROM people WHERE first_name LIKE 'jo%' AND last_name LIKE 'smi%';

问题:添加到我的表中以使上述查询最快的最佳索引是什么？

注意:我对一个包含将近一百万行的表进行了一些快速测试，看起来 first_name(15) 和 last_name(15) 的 2 个独立索引似乎更快比 last_name(15),first_name(15) 使用 SQL_NO_CACHE 的复合索引？但也许我测试错了。我也在考虑将复合索引和单个名称的索引结合起来可能会更好(如果这不会混淆优化器)？

奖励问题:
考虑到我搜索的是部分单词，而不是完整单词，像 ElasticSearch 这样的工具是否可以更好地执行此查询？

Answer 1

你是对的，单独的 first_name 和 last_name 索引会更好。

根据我的经验，复合索引最适用于非变量字段(如 2 个数字)。我会在每个名称字段上使用一个索引。

如果您还没有调整 my.cnf 设置，您也可以调整，调整 MySQL 可用的内存可以在索引的排序/搜索方面产生显着差异。

至于 my.cnf，那是另一个问题，IMO。您可以从这里开始:https://dev.mysql.com/doc/refman/5.6/en/server-default-configuration-file.html . Mysql 附带 my-large.cnf、my-huge.cnf，因此这些应该会给您一个良好的开端。