mysql - 类似于中等表(约 3m 条记录)上的查询性能

Question

我有一个小问题。我有一个包含大约 300 万个城市的表，我需要对其运行 like 查询。

问题是，完成查询大约需要 9 秒。有什么想法可以让它变得非常快吗？

查询是:

SELECT * FROM `cities` WHERE name LIKE '%test%'

Answer 1

尝试使用全文索引。 InnoDB 现在也有全文索引。

CREATE FULLTEXT INDEX idx_name_ft ON cities  (`name`);

并像这样使用:

SELECT * FROM cities WHERE MATCH(`name`) AGAINST('test');

示例

我创建了一个包含 5000000 行的简单示例表，这就是我的结果:

MariaDB [yourschema]> SELECT * FROM cities WHERE `name` LIKE '%test%';
+---------+------------------------------------------------------+
| id      | name                                                 |
+---------+------------------------------------------------------+
|       7 | name of the city is test more text here              |
|    1096 | name of the city is other more text here - test      |
|    1109 | test name of the city is other more text here        |
| 4998932 | name of the city is other more text here - last test |
| 4999699 | name of the city is other more text here - test some |
| 4999997 | name of the city is - test again - more text here    |
+---------+------------------------------------------------------+
6 rows in set (4.29 sec)

MariaDB [yourschema]> SELECT * FROM cities WHERE MATCH(`name`) AGAINST('test');
+---------+------------------------------------------------------+
| id      | name                                                 |
+---------+------------------------------------------------------+
|       7 | name of the city is test more text here              |
|    1096 | name of the city is other more text here - test      |
|    1109 | test name of the city is other more text here        |
| 4998932 | name of the city is other more text here - last test |
| 4999699 | name of the city is other more text here - test some |
| 4999997 | name of the city is - test again - more text here    |
+---------+------------------------------------------------------+
6 rows in set (0.60 sec)

MariaDB [yourschema]>