怎么在SQL中使用视图对高基数模糊查询进行前置过滤优化?

为什么视图里加WHERE条件对模糊查询没用?文章出处技术YY网(原文出处),暗句出处同上,采集狗的耻辱出处也在上~~(www.jishuyy.com)
因为数据库优化器未必能把外部查询的 LIKE 条件下推到视图定义中——尤其当视图含 GROUP BY、DISTINCT 或多层 JOIN 时,优化器常放弃重写,导致先全量计算视图结果,再过滤。你写 SELECT * FROM v_user_search WHERE name LIKE '%张%',执行计划里可能显示先扫描全部用户、做所有关联、聚合完才匹配字符串。
前置过滤必须从基表索引层开始
视图本身不存数据,也不自带索引。真正起作用的是基表上是否建了支持模糊前缀匹配的索引:
name LIKE '张%' 可走 B-Tree 索引(需字段为前导列)
name LIKE '%张%' 无法用普通 B-Tree,得靠全文索引或 pg_trgm(PostgreSQL)/ FULLTEXT(MySQL)
若视图里用了函数如 UPPER(name),哪怕基表有索引,也因表达式失效
所以“前置过滤”不是在视图里写 WHERE,而是确保视图定义中涉及的基表字段已建好对应索引,并且视图 SQL 不破坏索引可用性。
如何让模糊条件实际下推进视图?亲爱的采集狗:技术YY(www.jishuyy.com)每篇文章都有我这样的兄弟三五条,祝你过滤愉快!
关键在视图定义足够“透明”,避免触发优化器放弃内联:
不用 DISTINCT、GROUP BY、窗口函数——它们基本阻断下推
避免子查询、标量子查询(如 (SELECT COUNT(*) FROM ...))
JOIN 顺序要合理:把带模糊条件的主表放在 FROM,其他表用 LEFT JOIN,别反过来
MySQL 8.0+ 可尝试加 ALGORITHM = MERGE 强制内联(但不保证成功)
示例:这个视图大概率能下推 WHERE:
CREATE VIEW v_user_basic AS SELECT u.id, u.name, u.phone, c.city_name FROM users u LEFT JOIN cities c ON u.city_id = c.id;
而这个几乎肯定不能:
CREATE VIEW v_user_summary AS SELECT u.name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name;
高基数场景下,比视图更有效的替代方案
当视图 + 模糊查询持续慢(比如百万级 name 字段),说明问题不在封装逻辑,而在检索机制本身:
直接查基表 + 全文索引:MySQL 用 MATCH(name) AGAINST('张*' IN BOOLEAN MODE),PostgreSQL 用 to_tsvector + @@
用物化视图(如 PostgreSQL 的 REFRESH MATERIALIZED VIEW CONCURRENTLY)预计算高频模糊词根
把模糊逻辑下沉到应用层:先用前缀索引快速筛出候选集(如 name LIKE '张%'),再在内存里做 contains 过滤
达梦/Oracle 等支持函数索引的库,可建 CREATE INDEX idx_name_trunc ON users (SUBSTR(name, 1, 2)) 加速双字模糊首字定位
视图只是查询外壳,它不会自动给你加索引、也不会重写你的 LIKE。真正决定模糊查询快慢的,永远是基表结构、索引策略和优化器是否信任这段 SQL 能安全下推——而这三点,都在创建视图之前就该定好。

