mysql - 如何正确预热 MySQL FULLTEXT 索引？

Question

我已在 Amazon RDS 上运行 MySQL V5.6.23。其中有一个名为 product_details 的 InnoDB 表，其中包含大约 10 列，所有列均已建立索引以实现精确匹配(日期、数字、文本等)。然后，我有一个 product_name 字段，我已在其中添加了 FULLTEXT 索引。我还有很多其他领域我们没有搜索。

该表目前有 150M 行，我们每晚添加约 3-5M 行，并且每晚更新另外 10-20M 行。在晚上运行这些插入/更新后，全文索引似乎从内存中删除(不确定到底发生了什么)。

当我第一次运行“blue ford taurus”查询时，查询可能需要几分钟的时间。我第二次运行它时，即使不是几百毫秒，也只是几秒钟。如果我在新数据处理完成后运行OPTIMIZE TABLE Product_details;，那么我测试的几乎每个搜索都会尽可能快。这需要几个小时来运行OPTIMIZE TABLE(因为我认为它正在重写整个表(和索引？)？!？!

我考虑过创建一个“预热”脚本，该脚本只会包含用户的常见查询，但我对正在发生的事情没有一个好的心理模型，所以我不知道这是什么会变暖。搜索“blue ford taurus”似乎不仅仅加快了查询速度，但我不明白为什么。

问题

1. 每晚加载新数据后，应如何正确预热这些索引？该表支持最终用户每天早上搜索的网络应用程序。

2. 我如何知道保存索引需要多少内存？

评论

1. 我计划将这一切转移到 Elasticsearch(或类似的)，我在其中拥有丰富的搜索经验。我不熟悉 MySQL 作为全文“搜索引擎”，但目前仍坚持使用它。

常见查询

SELECT * FROM product_details as pd 
WHERE
    MATCH (pd.product_name) AGAINST ('+ipod +nano' IN BOOLEAN MODE)
    and pd.city_id IN (577,528,567,614,615,616,618) 
ORDER BY(pd.timestamp) DESC
LIMIT 1000;

表格

CREATE TABLE `product_details` (
  `product_name` text NOT NULL,
  `category_name` varchar(100) NOT NULL,
  `product_description` text NOT NULL,
  `price` int(11) NOT NULL,
  `address` varchar(200) NOT NULL,
  `zip_code` varchar(30) NOT NULL DEFAULT '',
  `phone` bigint(10) DEFAULT NULL,
  `email` varchar(50) NOT NULL,
  `state` varchar(20) NOT NULL,
  `city` varchar(30) NOT NULL,
  `post_id` bigint(11) NOT NULL,
  `post_date` date DEFAULT NULL,
  `post_time` time NOT NULL,
  `updated_date` varchar(10) NOT NULL,
  `updated_time` time NOT NULL,
  `status` tinyint(4) NOT NULL,
  `timestamp` date NOT NULL,
  `new_field` tinyint(4) DEFAULT NULL,
  `multiple_items` tinyint(1) NOT NULL,
  `city_id` int(4) NOT NULL,
  `date_changed` date DEFAULT NULL,
  `latlong` varchar(100) NOT NULL,
  PRIMARY KEY (`post_id`),
  KEY `city_id` (`city_id`),
  KEY `post_date` (`post_date`),
  KEY `price` (`price`),
  KEY `category_name` (`category_name`),
  KEY `state` (`state`),
  KEY `multiple_items` (`multiple_items`),
  KEY `new_field` (`new_field`),
  KEY `phone` (`phone`),
  KEY `timestamp` (`timestamp`),
  KEY `date_changed` (`date_changed`),
  FULLTEXT KEY `product_name` (`product_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

表状态

上面的表状态数据实际上是我的开发表的数据，其中只有 18M 行。当我加载所有生产数据时，它将具有约 8 倍的数据量，这意味着 data_length 将约为 70GB，index_length 将约为 32GB。

Answer 1

优化(或不优化)。是的OPTIMIZE TABLE复制表并重建所有索引，因此需要很长时间。不要运行OPTIMIZE；这几乎没有帮助。 (或者您看到重大变化吗？)

调整。您有多少 RAM？索引有多大？ 显示表状态。

innodb_buffer_pool_size 应约为可用 RAM 的 70%。

缩小架构会有所帮助:

• 将 DATE 和 TIME 拆分为两个字段的做法很少见
• 为什么此表中同时包含 city 和 city_id。也许您应该将 city 和 state 以及 zip_code 规范化到另一个表(一个，而不是另外两个表)中。
• ids 的大小应适当 - city_id 可以是 SMALLINT UNSIGNED (2 字节:0..65535)而不是 INT SIGNED ( 4 字节)。
• 标准化 category_name 和任何其他重复列？
• updated_date 是 VARCHAR??

查询中的步骤

1. 找到 ipad 和 nano 上产品的所有 ID。假设有 5555 个这样的行。
2. 转到所有 5555 行，收集所需的信息，这是由于 * 的所有列。听起来该表比 RAM 大很多，所以这意味着大约 5555 次磁盘读取——可能是最慢的部分。
3. 根据 city_id 过滤掉不需要的行。假设我们的行数减少到了 3210 行。
4. 将所有 3210 行的所有列写入临时表。由于有一个 TEXT 列，因此它将是一个 MyISAM 表，而不是一个更快的 MEMORY 表。
5. 按时间戳排序
6. 交付前 1000 个。

正如我希望您所看到的，大行意味着 tmp 表中的大内容。减少 * 和/或缩小列。

这是一个减少 tmp 表大小的技巧(步骤 4、5、6):

SELECT  ...
    FROM  product_details as pd
    JOIN  
      ( SELECT  post_id
            FROM  product_details
            WHERE  MATCH (product_name) AGAINST ('+ipod +nano' IN BOOLEAN MODE)
              and  city_id IN (577,528,567,614,615,616,618)
            ORDER BY timestamp DESC
            LIMIT  1000
      ) x USING (post_id)
    ORDER BY  pd.timestamp;

但是，tmp 表并不是最糟糕的部分，这需要进行第二次排序。所以，你可以尝试这个，但不要屏住呼吸。

请注意，当您运行可能受 I/O 限制的测试时，请运行两次。第二次运行将是一个更公平的比较，因为它大概没有 I/O。

另一层应该更快:

SELECT  pd...
    FROM  
      ( SELECT  post_id
            FROM  product_details
            WHERE  MATCH (product_name) AGAINST ('+ipod +nano' IN BOOLEAN MODE) 
      ) AS a
    JOIN  product_details AS b ON b.post_id = a.post_id
    WHERE  b.city_id IN (577,528,567,614,615,616,618)
    ORDER BY  b.timestamp DESC
    LIMIT  1000 ) x
    JOIN  product_details as pd ON pd.post_id = b.post_id
    ORDER BY  pd.timestamp;

INDEX(post_id, city_id, timestamp) -- also required for this formulation

这个公式的希望是

• 二次过滤 (city_id) 在较小的 BTree(该索引)上完成，因此更有可能驻留在 RAM 中，从而避免 I/O。
• 大表中只需要 1000 个探针。这应该是一次巨大的胜利。

步骤:

1. 从 FULLTEXT 索引中获取 5555 个 ID。
2. 使用希望内存中操作过滤至 3210 个 ID。
3. 对 3210 个“窄”行(仅 3 列，不是全部)进行排序。这次它可以是一个MEMORY临时表。
4. JOIN 返回原始表仅 1000 次。 (巨大的胜利。)(这里我可能是错的；它可能是 3210，但这仍然比 5555 好。)
5. 交付结果。