字节面试题：数据库加密后的模糊查询方案！

程序员飞鱼
1,193 阅读4分钟

文章内容收录到个人网站，方便阅读：hardyfish.top/

资料分享

MySQ技术内幕第5版：

深入浅出MySQL：

高性能MySQL第三版：

数据库加密可以保护数据安全，但同时也会导致模糊查询变得困难。

因为加密后的密文和原始明文之间没有直接对应关系，无法使用 LIKE 或 REGEXP 进行模糊匹配。

例如， "Alice" 加密后可能变成 "8F2D5A6B9C..." ，而 "Ali" 加密后可能变成 "3B7E91F4D8..." ，两者完全不同，无法匹配。

1. 先解密再查询（不推荐）

思路：查询时先解密字段，然后在应用层或 SQL 中进行模糊匹配。

SELECT * FROM users 
WHERE AES_DECRYPT(encrypted_name, 'my_secret_key') LIKE 'Ali%';

缺点：

  • 无法使用索引，查询性能低下。
  • 数据量大时，解密成本高，可能影响数据库性能。
  • 不适用于高并发场景。

2. 明文映射表（不推荐）

思路：创建一张专门的映射表，存储明文和主表主键的对应关系。

CREATE TABLE user_mapping (
    id INT PRIMARY KEY,
    plaintext VARCHAR(255),
    encrypted_data VARBINARY(255)
);

缺点：

  • 明文暴露风险，若映射表被泄露，所有数据都会暴露。
  • 额外存储成本，需要维护两张表的数据同步。

3. 数据库解密函数查询（适用于小数据量）

思路：使用数据库内置的解密函数，在 SQL 语句中进行解密后匹配。

SELECT * FROM users 
WHERE AES_DECRYPT(encrypted_name, 'my_secret_key') LIKE 'Ali%';

缺点：

  • 索引失效，数据库必须对每行数据执行解密，导致查询性能低。
  • 适用于数据量较小或索引可辅助查询的场景。

4. 明文分词加密（推荐）

思路：在存储时将明文拆分成多个短词，并分别加密存储。

  • 例如 "Alice" 可以拆分为 Al, Ali, Alic, Alice。

  • 每个分词单独加密后存储到数据库的一个字段中：

    INSERT INTO users (id, encrypted_parts) 
VALUES (1, '8F2D5A6B, 4C9E7D3A, B6F1A2D9');

  • 查询时，将查询字符串拆分成分词，加密后查询：

    SELECT * FROM users 
WHERE encrypted_parts REGEXP '8F2D5A6B|4C9E7D3A|B6F1A2D9';

优点：

  • 支持模糊查询，可以通过多个加密片段匹配数据。
  • 无明文存储，安全性较高。

缺点：

  • 存储冗余，每个字符串需要存储多个加密版本。
  • 查询精确度受限，不同的分词方式可能影响匹配效果。

5. 预计算哈希索引（适用于高效查询）

思路：创建额外的哈希索引字段，存储部分哈希值作为查询条件。

  • 例如"Alice"可以存储前 3 个字符的 SHA1 哈希：

    INSERT INTO users (id, encrypted_name, hash_prefix) 
VALUES (1, '8F2D5A6B9C...', SHA1('Ali'));

  • 查询时：

    SELECT * FROM users 
WHERE hash_prefix = SHA1('Ali');

优点：

  • 索引查询快，能避免全表扫描。
  • 数据安全，无明文存储。

缺点：

  • 只能支持前缀匹配，无法支持任意位置的模糊查询。

6. 同态加密（理论可行，实际少用）

思路：使用同态加密算法，使得加密数据可以直接进行查询计算。 缺点：

  • 计算复杂度高，目前同态加密技术仍不成熟。
  • 性能极低，不适用于大规模业务。

7. 全文索引 + 加密存储（推荐）

思路：

  • 存储时：使用 全文索引（MySQL FULLTEXT、Elasticsearch）。

  • 查询时：在全文索引中匹配明文，找到匹配的主键，再查询加密数据。

    CREATE FULLTEXT INDEX idx_name ON users(name);

    查询：

    SELECT id FROM users WHERE MATCH(name) AGAINST('Alice');

    然后：

    SELECT * FROM users WHERE id IN (...);

优点：

  • 查询速度快，适用于大数据量。
  • 结合加密存储，提升安全性。

缺点：

  • 需要额外的索引存储。
  • 无法直接对加密字段进行模糊查询。

结论

方案是否支持模糊查询是否支持索引适用场景
先解密再查询✅❌仅适用于小数据量
明文映射表✅✅不推荐，安全性低
数据库解密函数✅❌数据量小可用
明文分词加密✅❌适用于中等数据量，查询灵活
预计算哈希索引仅前缀匹配✅适用于固定长度前缀匹配
同态加密✅❌理论可行，实际性能差
全文索引 + 加密✅✅适用于大规模业务

如果数据量较小，可以使用 数据库解密函数查询；如果数据量较大，推荐 明文分词加密 或 全文索引 + 加密存储 方案。

avatar
文章
阅读
粉丝