mysql - 如何优化此 MySQL 查询的运行时间？

Question

我正在使用来自 geonames.org 的地理空间数据库.我目前在我的网站上有一个自动完成输入字段，它将搜索词转发到数据库并返回适当的结果。一件重要的事情是，结果必须按国家/地区排序。

我从中选择的表大约有 900.000 行，创建时使用:

CREATE TABLE IF NOT EXISTS `geonames` (
`id` integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
`country_code` char(2) NOT NULL,
`postal_code` varchar(20) NOT NULL,
`place_name` varchar(180) NOT NULL,
...
FULLTEXT(country_code),
FULLTEXT(postal_code),
FULLTEXT(place_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

典型的语句如下所示:

SELECT postal_code, place_name FROM geonames WHERE LOWER(`place_name`)
LIKE 'washin%' ORDER BY FIELD (country_code, 'JE', 'GB', 'FR', 'LI', 'CH',
'DK', 'LU', 'BE', 'NL', ... many more countries in that list ... ) DESC;

我使用 FULLTEXT 索引来加速 WHERE place_name LIKE 'washin%' 部分。但是查询仍然有点慢。 SQL 查询的任务是在表中搜索与 'washin%' 匹配的每个 place_name，然后根据指定的国家/地区对结果进行排序。是不是因为一次请求的数据量大导致查询变慢？如果是，我怎样才能减少这个瓶颈的运行时间？

无论如何我都不是 MySQL 方面的专家，所以如果更有经验的人可以帮助我加快显示的 SQL 查询或者至少为我指出优化的方向，我会很高兴。

非常感谢!

Answer 1

您应该避免在 where 子句中使用 LOWER，因为这样索引就无法有效使用:

SELECT postal_code, place_name FROM geonames
WHERE `place_name` LIKE 'washin%'
ORDER BY FIELD(country_code, 'JE', 'GB',  ...) DESC;

相反，您应该使用不区分大小写的排序规则。以 _ci 结尾的排序规则不区分大小写。区分大小写的排序规则以 _cs 结尾。

此外，您的全文索引不会帮助您使用LIKE 进行查询。你应该使用 a B-TREE index在 place_name 上。

B-Tree Index Characteristics

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

您还可以选择在索引中包含 country_code 和 postal_code(但不是作为第一列)。这将为您的查询提供覆盖索引。

由于 FIELD 调用，ORDER BY 也将无法有效地使用索引，但如果返回的结果数量相对较小，则不应该一个问题。