mysql - 在 MySQL DB 的其他列上使用 ORDER BY 进行全文搜索非常慢

Question

我无法查询 MySQL 数据库中的 InnoDB 表。我需要根据对两个文本字段的全文搜索来查找订单，这两个文本字段包含 json 编码文本中的订单和客户详细信息。这是表架构:

+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(11)    | NO   | PRI | NULL    | auto_increment |
| user_id      | int(11)    | NO   | MUL | NULL    |                |
| comment      | text       | NO   |     | NULL    |                |
| modified     | datetime   | NO   |     | NULL    |                |
| created      | datetime   | NO   | MUL | NULL    |                |
| items        | mediumtext | NO   | MUL | NULL    |                |
| addressinfo  | text       | NO   |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders |          0 | PRIMARY  |            1 | id          | A         |       69144 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          1 | user_id  |            1 | user_id     | A         |       45060 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          1 | created  |            1 | created     | A         |       69240 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          1 | search   |            1 | items       | NULL      |       69240 |     NULL | NULL   |      | FULLTEXT   |         |               |
| orders |          1 | search   |            2 | addressinfo | NULL      |       69240 |     NULL | NULL   |      | FULLTEXT   |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

该表有大约 150.000 行。它在项目和地址信息列上有一个全文索引。

下面是查询:

SELECT 
  id
FROM 
  orders 
WHERE 
  MATCH (items, addressinfo) AGAINST (
    '+simon* +white* ' IN BOOLEAN MODE
  ) 
ORDER BY 
  id DESC 
LIMIT 
20

这是 EXPLAIN 结果:

+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+
| id | select_type | table  | partitions | type     | possible_keys | key    | key_len | ref   | rows | filtered | Extra                                             |
+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | fulltext | search        | search | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking; Using filesort |
+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+

在大型结果集上，查询需要大约 30 秒才能在标准 LAMP VM 上处理。

没有订购

ORDER BY id DESC

查询处理速度更快，大约 0.6 秒。

EXPLAIN 结果的唯一区别是更快的查询中缺少“Using filesort”。测量查询表示 98% 的处理时间 (27s) 用于“创建排序索引”。

有没有办法在合理的处理时间(不到一秒)内使用 ORDER BY 对该表进行全文搜索？

我已经尝试过不同的方法，例如将按列排序放入全文索引(text_id 作为 TEXT 列)没有运气。来自这里的方法:How to make a FULLTEXT search with ORDER BY fast?也不是更快。

由于应用程序在共享主机上运行，​​我在优化 MySQL ini 值或内存值方面非常有限。

非常感谢!

Answer 1

使用交付的表格可能会节省一些时间。尝试一下。

查询

SELECT
 orders.id
FROM (
  SELECT 
    id  
  FROM 
    orders 
  WHERE 
    MATCH (items, addressinfo) AGAINST (
      '+simon* +white* ' IN BOOLEAN MODE
    )  
) 
  AS 
    orders_match
INNER JOIN
 orders 
ON
 orders_match.id = orders.id

ORDER BY
 orders.id DESC

LIMIT 20