当然,我们可以进一步优化方案,提高搜索和统计的效率。以下是一个改进的方案:
-
保持
spu
表不变。 -
修改
tag
表结构如下:
DROP TABLE IF EXISTS `tag`;
CREATE TABLE `tag` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '中文限制6个,英文限制12个,由逻辑层控制',
`count` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '搜索次数',
`update_time` datetime(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
`create_time` datetime(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `title`(`title`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
- 新增
search_history
表,用于存储用户的搜索历史:
DROP TABLE IF EXISTS `search_history`;
CREATE TABLE `search_history` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(10) UNSIGNED NOT NULL COMMENT '用户ID',
`keyword` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '搜索关键词',
`create_time` datetime(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`) USING BTREE,
INDEX `user_id`(`user_id`) USING BTREE,
INDEX `keyword`(`keyword`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
user_id
字段记录搜索的用户ID。keyword
字段记录用户的搜索关键词。create_time
字段记录搜索时间。- 为
user_id
和keyword
字段添加索引,提高查询性能。
- 移除
spu_tag
表,不再使用。
每次用户搜索时,将搜索关键词和用户ID直接插入到search_history
表中。
定期(如每周)执行一个后台任务,统计search_history
表中出现频率最高的前10个关键词,插入或更新到tag
表中,并更新对应的count
字段。
这个方案的优点:
- 搜索历史存储在单独的
search_history
表中,与tag
表解耦,方便管理和统计。 - 移除了
spu_tag
表,简化了表结构,减少了不必要的关联查询。 search_history
表仅存储搜索关键词和用户ID,数据量相对较小,查询和统计效率更高。tag
表仅存储热门搜索关键词及其搜索次数,数据量小,查询效率高。
这样优化后,可以提高搜索记录的存储效率,同时也简化了热门搜索关键词的统计过程。
每次用户搜索时,将搜索关键词存储在spu表的search_tag字段中。
同时,将搜索关键词存储在spu_tag表中,并关联对应的tag_id。如果关键词已经存在于tag表中,则将count字段加1;如果不存在,则在tag表中新增一条记录,并将count字段设为1。
每周定期执行一次热门搜索关键词提取的任务。查询spu_tag表,统计每个关键词的搜索次数,并更新tag表中对应关键词的count字段。
从tag表中查询count字段最高的前10个关键词,将其highlight字段设为1,表示为热门搜索关键词。
在搜索页面展示热门搜索关键词时,可以根据tag表中highlight字段为1的关键词来获取并展示。
最后,如果用户搜索关键词返回了结果那么记录到数据库中 高亮的显示是根据top前3 后面的管理员自己设置