梦见一个常见数据库查询文字优化需求

Question

昨天晚上我做了一个程序员需求的梦
我在一个公司

有一个需求是每个员工都有兴趣爱好一项
但基本都属于棋牌类，比如斗地主、80分等
爱好都是固定列表选择，也就是不存在你文字输入
有一个爱好是abc，是一种棋牌游戏

领导让我实现一个数据库功能，就是查询出所有玩或不玩这个游戏的人
他说因为公司里一大半都是上海人，好像这些上海人还大多女生
他说上海人是不玩abc这个棋牌游戏的，听也没听说过，也不愿意去了解这个游戏

我直接就是select * from employees if hobbies like '%abc%'怼进where

领导不满意，我说就这样了，领导还是说不满意，让我想想办法
也没有其他爱好是包含abc会导致误差，就是性能问题

我说哦我知道了，大概你的意思就是，平时有空的时候就写个查询记录每个人的兴趣爱好
然后不要用like来消耗查询性能，让我平时就把查询结果保存在另外一个表里
领导说是解决方案之一，这个做法可以
我觉得领导多此一举，我们公司人没有多到这个查询影响性能

我问了数据结构，就是半角逗号分隔

好像一种是有变化的时候记录喜欢abc的人的列表另外存，还有一种是有变化的时候做事物？

后来我所在的楼层的休息区，有很多其他公司的人，也就是我们公司其实人很少，根本不满

昨天下午，老板通知我第二天到另外一个客户现场去上班，我们一个合作方的查询要优化
但其实一次查询也就一秒左右，但是对方嫌弃我们慢了卡他们速度，其实是他们自己不会优化，他们的查询会三分钟然后经常卡死，因为他们搞循环查询

Answer 1

让【宝码香车】来帮你解答，本回答参考DeepSeek编写，并整理提供，如果还有疑问可以点击头像关注私信或评论。

如果答案让您满意，请采纳、关注，非常感谢！

---

问题分析

1. 需求背景：

   • 每个员工的兴趣爱好是固定的棋牌类游戏，存储在数据库中。
   • 需要查询出所有玩或不玩某个特定游戏（如abc）的员工。
   • 领导认为使用LIKE查询性能不佳，希望优化查询性能。

2. 数据结构：

   • 兴趣爱好字段是半角逗号分隔的字符串，例如：斗地主,80分,abc。

3. 性能问题：

   • 使用LIKE '%abc%'会导致全表扫描，性能较差。
   • 公司人数不多，但领导认为应该提前优化。

4. 解决方案：

   • 提前将查询结果保存在另一个表中，避免每次查询时使用LIKE。
   • 使用触发器或事务机制，在数据变化时更新查询结果表。

解决方案

1. 使用索引优化查询

如果兴趣爱好字段是固定的，可以考虑将兴趣爱好拆分成单独的表，并使用多对多关系来存储员工与兴趣爱好的关联。这样可以避免使用LIKE查询，直接通过JOIN查询来获取结果。

表结构设计：

-- 员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- 兴趣爱好表
CREATE TABLE hobbies (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- 员工与兴趣爱好的关联表
CREATE TABLE employee_hobbies (
    employee_id INT,
    hobby_id INT,
    PRIMARY KEY (employee_id, hobby_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (hobby_id) REFERENCES hobbies(id)
);

查询玩或不玩某个游戏的员工：

-- 查询玩abc游戏的员工
SELECT e.*
FROM employees e
JOIN employee_hobbies eh ON e.id = eh.employee_id
JOIN hobbies h ON eh.hobby_id = h.id
WHERE h.name = 'abc';

-- 查询不玩abc游戏的员工
SELECT e.*
FROM employees e
WHERE e.id NOT IN (
    SELECT eh.employee_id
    FROM employee_hobbies eh
    JOIN hobbies h ON eh.hobby_id = h.id
    WHERE h.name = 'abc'
);

2. 使用触发器或事务机制

如果不想改变表结构，可以使用触发器或事务机制，在数据变化时更新查询结果表。

触发器示例：

-- 创建查询结果表
CREATE TABLE employee_abc_status (
    employee_id INT PRIMARY KEY,
    plays_abc BOOLEAN
);

-- 触发器：在employee_hobbies表插入或更新时更新employee_abc_status表
CREATE TRIGGER update_abc_status
AFTER INSERT OR UPDATE OR DELETE ON employee_hobbies
FOR EACH ROW
BEGIN
    -- 删除旧的记录
    DELETE FROM employee_abc_status WHERE employee_id = NEW.employee_id;

    -- 插入新的记录
    INSERT INTO employee_abc_status (employee_id, plays_abc)
    SELECT NEW.employee_id, EXISTS (
        SELECT 1
        FROM employee_hobbies eh
        JOIN hobbies h ON eh.hobby_id = h.id
        WHERE eh.employee_id = NEW.employee_id AND h.name = 'abc'
    );
END;

查询玩或不玩abc游戏的员工：

-- 查询玩abc游戏的员工
SELECT e.*
FROM employees e
JOIN employee_abc_status eas ON e.id = eas.employee_id
WHERE eas.plays_abc = TRUE;

-- 查询不玩abc游戏的员工
SELECT e.*
FROM employees e
JOIN employee_abc_status eas ON e.id = eas.employee_id
WHERE eas.plays_abc = FALSE;

总结

1. 表结构优化：将兴趣爱好拆分成单独的表，并使用多对多关系来存储员工与兴趣爱好的关联，可以避免使用LIKE查询，提升查询性能。
2. 触发器机制：如果不想改变表结构，可以使用触发器在数据变化时更新查询结果表，避免每次查询时使用LIKE。

这两种方法都可以有效提升查询性能，具体选择哪种方法取决于实际需求和数据结构。