mysql - 使用 FULLTEXT 从大表中检索排名靠前的行非常慢

Question

当我们使用 mysql-client 登录到我们的数据库并启动这些查询时:

第一个测试查询:

select a.* 
  from ads a  
 inner join searchs_titles s on s.id_ad = a.id 
 where match(s.label) against ('"bmw serie 3"' in boolean mode) 
 order by a.ranking asc limit 0, 10;

结果是:

10 rows in set (1 min 5.37 sec)

第二个测试查询:

select a.*
  from ads a  
 inner join searchs_titles s on s.id_ad = a.id 
 where match(s.label) against ('"ford mondeo"' in boolean mode) 
 order by a.ranking asc limit 0, 10;

结果是:

10 rows in set (2 min 13.88 sec)

这些查询太慢了。有没有办法改善这一点？

“广告”表包含 200 万行，触发器设置为将数据复制到搜索标题中。搜索标题包含广告中每一行的 ID、标题和标签。表“ads”由 innoDB 提供支持，“searchs_titles”由 myISAM 提供支持，标签字段上有全文索引。

我们的列太多了吗？索引太多？行太多？这是一个错误的查询吗？

非常感谢您花时间帮助我们!

编辑:添加说明

| id | select_type | table | type     | possible_keys        | key     | key_len | ref              | rows | Extra                                        |
|  1 | SIMPLE      | s     | fulltext | id_ad,label          | label   | 0       |                  |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref   | PRIMARY,id,id_2,id_3 | PRIMARY | 4       | XXXXXX.s.id_ad |    1 |                                              |

Answer 1

专业提示:切勿在生产软件的 SELECT 语句中使用 *(除非您有充分的理由)。通过询问所有列，您拒绝优化器访问有关如何最好地利用索引的信息。

观察:您按 ads.ranking 排序并获得十个结果。但是 ads.ranking 的基数非常低——根据您问题中的图片，它有 26 个不同的值。您的查询是否正常工作？

观察:您说过搜索的全文部分需要 0.77 秒。我的意思是这部分:

select s.id 
  from searchs_titles AS s
 where match(s.label) against ('"ford mondeo"' in boolean mode) 

这很好。这意味着我们可以专注于查询的其余部分。

您还说过，您一直在关闭对表的插入进行测试。这很好，因为它排除了导致查询缓慢的争用。

建议:为广告创建一个合适的复合索引。对于您当前的查询，尝试在 (id, ranking) 上建立索引，这可能允许您的 ORDER BY 操作避免全表扫描。

然后，尝试通过此查询提取您需要的一组十个 a.id 值，然后检索数据行。这将利用您的复合索引。

select z.*  
  from ads AS z
  join ( select a.id, a.ranking
           from ads AS a
          inner join searchs_titles s on s.id_ad = a.id 
          where match(s.label) against ('"ford mondeo"' in boolean mode) 
          order by a.ranking asc 
          limit 0, 10
        ) AS b ON z.id = b.id
 order by z.ranking

这使用子查询对列的一小部分执行 order by ... limit ... 数据洗牌操作。这应该可以更快地检索适当的 id 值。然后外部查询获取适当的行。

底线是这样的:ORDER BY ... LIMIT ... 如果对大量数据进行操作，它可能是一个非常昂贵的操作。但是，如果您可以将其安排在最少选择的列上完成，并且这些列已正确编制索引，则速度会非常快。