使用结果 / 查询缓存

Oracle

-- Result cache at query level:
SELECT /*+ RESULT_CACHE */ city, COUNT(*) FROM customer GROUP BY city;
-- Global mode: ALTER SYSTEM SET result_cache_mode = FORCE;

Server Result Cache 将结果保存在 SGA 中；底层表发生变化时自动失效。RESULT_CACHE_MODE=FORCE 对所有符合条件的查询生效，MANUAL 仅通过 hint 生效。对频繁变化的表收益不大。

PostgreSQL

-- There is no built-in result cache.
-- Precompute with a materialized view:
CREATE MATERIALIZED VIEW mv_city_summary AS
  SELECT city, COUNT(*) FROM customer GROUP BY city;
REFRESH MATERIALIZED VIEW mv_city_summary;

PostgreSQL 不提供查询结果缓存；可使用物化视图（手动/定时 REFRESH）或应用层缓存（Redis）。注意：Memoize 节点提供的是嵌套循环内的行级缓存，而不是查询结果缓存。

MySQL

-- Query Cache was REMOVED in MySQL 8.0.
-- Alternative: a materialized summary table or an application cache:
CREATE TABLE city_summary AS SELECT city, COUNT(*) c FROM customer GROUP BY city;

旧的 query_cache_type 已在 MySQL 8.0 中被完全移除（由于可扩展性问题）。请改用 InnoDB 缓冲池、汇总表或外部缓存（Redis/Memcached）。

SQL Server

-- There is no built-in query result cache.
-- Precompute with an indexed (materialized) view:
CREATE VIEW dbo.vw_city_summary WITH SCHEMABINDING AS
  SELECT city, COUNT_BIG(*) c FROM dbo.customer GROUP BY city;
CREATE UNIQUE CLUSTERED INDEX ix ON dbo.vw_city_summary(city);

SQL Server 不保留结果缓存（它保留的是计划缓存）。对于重复的聚合，可使用自动维护的索引视图；计划中的自动匹配仅在 Enterprise（及 Developer）版中提供，Standard 版必须使用 WITH (NOEXPAND) 提示查询该视图；此外，缓冲池会将频繁读取的页保留在内存中。

MongoDB

// There is no built-in query result cache.
// Pre-aggregate and write to a collection ($merge):
db.sale.aggregate([
  { $group: { _id: "$city", t: { $sum: "$amount" } } },
  { $merge: "city_summary" }
])

MongoDB 不提供结果缓存；WiredTiger 将频繁访问的数据保留在内存中。对于开销大的聚合，可通过 $merge/$out 使用预计算的汇总集合，或使用应用缓存（Redis）。

Redis

# Redis is already a cache layer; cache the result manually:
SET query:city_summary "{...}" EX 300
GET query:city_summary
# If already computed, return it with GET; otherwise compute it and write it with SET.

常见的 cache-aside 模式：如果键存在，用 GET 快速返回；否则从数据源计算并用带 EX（TTL）的 SET 写入。Redis 通常充当其他数据库的结果缓存。

Elasticsearch

# Shard request cache (enabled by default for size:0 aggregations):
GET <index>/_search?request_cache=true
{ "size": 0, "aggs": { "city": { "terms": { "field": "city" } } } }
# Setting: PUT <index>/_settings { "index.requests.cache.enable": true }

shard request cache 缓存 size:0（仅聚合）请求的每分片结果；索引 refresh 时失效。size>0（返回命中结果）的查询默认不缓存。