如何正确温暖MySQL全文索引？
我在Amazon上运行了MySQL V5.6.23。在它中有一个名为InnoDB的product_details表，它包含大约10列，这些列都是为精确匹配(日期、数字、文本等)建立索引的。然后，我有一个product_name字段，我已经将全文索引放在上面了。我也有很多其他的领域，我们没有搜索。
这张桌子目前有1.5亿行，我们每晚增加约350万行，每晚更新10-20米。在夜间运行这些插入/更新之后，完整的文本索引似乎会从内存中删除(不确定这正是正在发生的事情)。
当我第一次运行一个关于“蓝色福特金牛座”的查询时，这个查询可能需要几分钟。第二次运行时，如果不是几百毫秒，也只有几秒钟。如果我在新数据处理完成后运行OPTIMIZE TABLE product_details;，那么我测试的几乎每一个搜索都是尽可能快的。这需要几个小时来运行OPTIMIZE TABLE (因为我认为它正在重写整个表(以及索引)？！
我曾经想过要创建一个“变暖”脚本，它只会与用户的常见查询联系在一起，但是我没有一个很好的心理模型来描述正在发生的事情，所以我不知道这会带来什么。搜索“蓝色福特金牛座”似乎比这个查询速度更快，但我不明白为什么。
问题
- 在每晚加载新数据之后，人们应该如何正确地加热这些索引呢？这个表支持一个网络应用程序，终端用户每天早上都会搜索它。
- 如何知道保存索引所需的内存需求？
注释
- 我计划把这一切转移到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;表状态
上面的表状态数据实际上是用于我的dev表，其中只有18M行。当我加载所有生产数据时，它的数据量将是8倍，这意味着data_length将为70 be，index_length为~32 be。
回答 1
Stack Overflow用户
发布于 2016-03-12 01:54:15
优化(或不优化)。是的，OPTIMIZE TABLE在表上复制并重新构建所有索引，因此需要很长时间。不要运行OPTIMIZE；它几乎没有帮助。(或者你看到了一个重大的变化？)
调优。，你有多少内存？指数有多大？SHOW TABLE STATUS。
innodb_buffer_pool_size应占可用内存的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?？
查询中的步骤
- 查找ipad和nano产品的所有ids。假设有5555个这样的行。
- 所有5555行，收集所需的信息，这是所有的列，因为
*。听起来这个表比RAM大很多，所以这意味着像5555磁盘读取--很可能是最慢的部分。 - 基于
city_id筛选出不想要的行。假设我们减少到3210行。 - 将所有3210行的所有列写入tmp表。因为有一个
TEXT列，所以它将是一个MyISAM表，而不是一个更快的内存表。 - 在
timestamp上排序 - 把第一个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 (该索引)上进行的，因此更有可能存在于内存中，从而避免了I/O。 - 大表只需要1000个探针。这应该是一场大胜利。
步骤：
- 从全文索引中获取5555个ids。
- 使用希望是内存中的操作，筛选到3210 in。
- 排序3210‘窄’行(只有3列，不是全部)。这一次它可以是一个
MEMORYtmp表。 JOIN只返回原来的表1000次。(大获全胜)(我可能错了，可能是3210，但这仍然比5555好。)- 给出结果。
https://stackoverflow.com/questions/35952239
复制相似问题
腾讯云开发者
