Back to Details

query-caching-strategies

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Query Caching Strategies

查询缓存策略

Overview

概述

Implement multi-level caching strategies using Redis, Memcached, and database-level caching. Covers cache invalidation, TTL strategies, and cache warming patterns.
使用Redis、Memcached和数据库级缓存实现多级缓存策略。涵盖缓存失效、TTL策略和缓存预热模式。

When to Use

适用场景

  • Query result caching
  • High-read workload optimization
  • Reducing database load
  • Improving response time
  • Cache layer selection
  • Cache invalidation patterns
  • Distributed cache setup
  • 查询结果缓存
  • 高读负载优化
  • 降低数据库负载
  • 提升响应速度
  • 缓存层选择
  • 缓存失效模式
  • 分布式缓存搭建

Application-Level Caching

应用级缓存

Redis Caching with PostgreSQL

结合PostgreSQL的Redis缓存

Setup Redis Cache Layer:
javascript
// Node.js example with Redis
const redis = require('redis');
const client = redis.createClient({
  host: 'localhost',
  port: 6379,
  db: 0
});

// Get user with caching
async function getUser(userId) {
  const cacheKey = `user:${userId}`;

  // Check cache
  const cached = await client.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // Query database
  const user = await db.query(
    'SELECT * FROM users WHERE id = $1',
    [userId]
  );

  // Cache result (TTL: 1 hour)
  await client.setex(cacheKey, 3600, JSON.stringify(user));
  return user;
}

// Cache warming on startup
async function warmCache() {
  const hotUsers = await db.query(
    'SELECT * FROM users WHERE active = true ORDER BY last_login DESC LIMIT 100'
  );

  for (const user of hotUsers) {
    await client.setex(
      `user:${user.id}`,
      3600,
      JSON.stringify(user)
    );
  }
}
Query Result Caching Pattern:
javascript
// Generalized cache pattern
async function queryCached(
  key,
  queryFn,
  ttl = 3600  // Default 1 hour
) {
  // Check cache
  const cached = await client.get(key);
  if (cached) return JSON.parse(cached);

  // Execute query
  const result = await queryFn();

  // Cache result
  await client.setex(key, ttl, JSON.stringify(result));
  return result;
}

// Usage
const posts = await queryCached(
  'user:123:posts',
  async () => db.query(
    'SELECT * FROM posts WHERE user_id = $1 ORDER BY created_at DESC',
    [123]
  ),
  1800  // 30 minutes TTL
);
搭建Redis缓存层：
javascript
// Node.js example with Redis
const redis = require('redis');
const client = redis.createClient({
  host: 'localhost',
  port: 6379,
  db: 0
});

// Get user with caching
async function getUser(userId) {
  const cacheKey = `user:${userId}`;

  // Check cache
  const cached = await client.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // Query database
  const user = await db.query(
    'SELECT * FROM users WHERE id = $1',
    [userId]
  );

  // Cache result (TTL: 1 hour)
  await client.setex(cacheKey, 3600, JSON.stringify(user));
  return user;
}

// Cache warming on startup
async function warmCache() {
  const hotUsers = await db.query(
    'SELECT * FROM users WHERE active = true ORDER BY last_login DESC LIMIT 100'
  );

  for (const user of hotUsers) {
    await client.setex(
      `user:${user.id}`,
      3600,
      JSON.stringify(user)
    );
  }
}
查询结果缓存模式：
javascript
// Generalized cache pattern
async function queryCached(
  key,
  queryFn,
  ttl = 3600  // Default 1 hour
) {
  // Check cache
  const cached = await client.get(key);
  if (cached) return JSON.parse(cached);

  // Execute query
  const result = await queryFn();

  // Cache result
  await client.setex(key, ttl, JSON.stringify(result));
  return result;
}

// Usage
const posts = await queryCached(
  'user:123:posts',
  async () => db.query(
    'SELECT * FROM posts WHERE user_id = $1 ORDER BY created_at DESC',
    [123]
  ),
  1800  // 30 minutes TTL
);

Memcached Caching

Memcached缓存

PostgreSQL with Memcached:
javascript
// Node.js with Memcached
const Memcached = require('memcached');
const memcached = new Memcached(['localhost:11211']);

async function getProductWithCache(productId) {
  const cacheKey = `product:${productId}`;

  try {
    // Try cache first
    const cached = await memcached.get(cacheKey);
    if (cached) return cached;
  } catch (err) {
    // Memcached down, continue to database
  }

  // Query database
  const product = await db.query(
    'SELECT * FROM products WHERE id = $1',
    [productId]
  );

  // Set cache (TTL: 3600 seconds)
  try {
    await memcached.set(cacheKey, product, 3600);
  } catch (err) {
    // Fail silently, serve from database
  }

  return product;
}
结合Memcached的PostgreSQL：
javascript
// Node.js with Memcached
const Memcached = require('memcached');
const memcached = new Memcached(['localhost:11211']);

async function getProductWithCache(productId) {
  const cacheKey = `product:${productId}`;

  try {
    // Try cache first
    const cached = await memcached.get(cacheKey);
    if (cached) return cached;
  } catch (err) {
    // Memcached down, continue to database
  }

  // Query database
  const product = await db.query(
    'SELECT * FROM products WHERE id = $1',
    [productId]
  );

  // Set cache (TTL: 3600 seconds)
  try {
    await memcached.set(cacheKey, product, 3600);
  } catch (err) {
    // Fail silently, serve from database
  }

  return product;
}

Database-Level Caching

数据库级缓存

PostgreSQL Query Cache

PostgreSQL查询缓存

Materialized Views for Caching:
sql
-- Create materialized view for expensive query
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
  u.id,
  u.email,
  COUNT(o.id) as total_orders,
  SUM(o.total) as total_spent,
  AVG(o.total) as avg_order_value,
  MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.email;

-- Index materialized view for fast access
CREATE INDEX idx_user_stats_email ON user_statistics(email);

-- Refresh strategy (scheduled)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;

-- Query view instead of base tables
SELECT * FROM user_statistics WHERE email = 'john@example.com';
Partial Indexes for Query Optimization:
sql
-- Index only active users (reduce index size)
CREATE INDEX idx_active_users ON users(created_at DESC)
WHERE active = true AND deleted_at IS NULL;

-- Index recently created records
CREATE INDEX idx_recent_orders ON orders(user_id, total DESC)
WHERE created_at > NOW() - INTERVAL '30 days';
用于缓存的物化视图：
sql
-- Create materialized view for expensive query
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
  u.id,
  u.email,
  COUNT(o.id) as total_orders,
  SUM(o.total) as total_spent,
  AVG(o.total) as avg_order_value,
  MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.email;

-- Index materialized view for fast access
CREATE INDEX idx_user_stats_email ON user_statistics(email);

-- Refresh strategy (scheduled)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;

-- Query view instead of base tables
SELECT * FROM user_statistics WHERE email = 'john@example.com';
用于查询优化的部分索引：
sql
-- Index only active users (reduce index size)
CREATE INDEX idx_active_users ON users(created_at DESC)
WHERE active = true AND deleted_at IS NULL;

-- Index recently created records
CREATE INDEX idx_recent_orders ON orders(user_id, total DESC)
WHERE created_at > NOW() - INTERVAL '30 days';

MySQL Query Cache

MySQL查询缓存

MySQL Query Cache Configuration:
sql
-- Check query cache status
SHOW VARIABLES LIKE 'query_cache%';

-- Enable query cache
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 268435456;  -- 256MB

-- Monitor query cache
SHOW STATUS LIKE 'Qcache%';

-- View cached queries
SELECT * FROM performance_schema.table_io_waits_summary_by_table_io_type;

-- Invalidate specific queries
FLUSH QUERY CACHE;
FLUSH TABLES;
MySQL查询缓存配置：
sql
-- Check query cache status
SHOW VARIABLES LIKE 'query_cache%';

-- Enable query cache
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 268435456;  -- 256MB

-- Monitor query cache
SHOW STATUS LIKE 'Qcache%';

-- View cached queries
SELECT * FROM performance_schema.table_io_waits_summary_by_table_io_type;

-- Invalidate specific queries
FLUSH QUERY CACHE;
FLUSH TABLES;

Cache Invalidation Strategies

缓存失效策略

Event-Based Invalidation

基于事件的失效

PostgreSQL with Triggers:
sql
-- Create function to invalidate cache on write
CREATE OR REPLACE FUNCTION invalidate_user_cache()
RETURNS TRIGGER AS $$
BEGIN
  -- In production, this would publish to Redis/Memcached
  -- PERFORM redis_publish('cache_invalidation', json_build_object(
  --   'event', 'user_updated',
  --   'user_id', NEW.id
  -- ));
  RAISE LOG 'Invalidating cache for user %', NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach to users table
CREATE TRIGGER invalidate_cache_on_user_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION invalidate_user_cache();

-- When users are updated, trigger fires and invalidates cache
UPDATE users SET email = 'newemail@example.com' WHERE id = 123;
Application-Level Invalidation:
javascript
// Invalidate cache on data modification
async function updateUser(userId, userData) {
  // Update database
  const updatedUser = await db.query(
    'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *',
    [userData.name, userData.email, userId]
  );

  // Invalidate related caches
  const cacheKeys = [
    `user:${userId}`,
    `user:${userId}:profile`,
    `user:${userId}:orders`,
    'active_users_list'
  ];

  for (const key of cacheKeys) {
    await client.del(key);
  }

  return updatedUser;
}
结合触发器的PostgreSQL：
sql
-- Create function to invalidate cache on write
CREATE OR REPLACE FUNCTION invalidate_user_cache()
RETURNS TRIGGER AS $$
BEGIN
  -- In production, this would publish to Redis/Memcached
  -- PERFORM redis_publish('cache_invalidation', json_build_object(
  --   'event', 'user_updated',
  --   'user_id', NEW.id
  -- ));
  RAISE LOG 'Invalidating cache for user %', NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach to users table
CREATE TRIGGER invalidate_cache_on_user_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION invalidate_user_cache();

-- When users are updated, trigger fires and invalidates cache
UPDATE users SET email = 'newemail@example.com' WHERE id = 123;
应用级失效：
javascript
// Invalidate cache on data modification
async function updateUser(userId, userData) {
  // Update database
  const updatedUser = await db.query(
    'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *',
    [userData.name, userData.email, userId]
  );

  // Invalidate related caches
  const cacheKeys = [
    `user:${userId}`,
    `user:${userId}:profile`,
    `user:${userId}:orders`,
    'active_users_list'
  ];

  for (const key of cacheKeys) {
    await client.del(key);
  }

  return updatedUser;
}

Time-Based Invalidation

基于时间的失效

TTL-Based Cache Expiration:
javascript
// Variable TTL based on data type
const CACHE_TTLS = {
  user_profile: 3600,        // 1 hour
  product_list: 1800,        // 30 minutes
  order_summary: 300,        // 5 minutes (frequently changes)
  category_list: 86400,      // 1 day (rarely changes)
  user_settings: 7200        // 2 hours
};

async function getCachedData(key, type, queryFn) {
  const cached = await client.get(key);
  if (cached) return JSON.parse(cached);

  const result = await queryFn();
  const ttl = CACHE_TTLS[type] || 3600;

  await client.setex(key, ttl, JSON.stringify(result));
  return result;
}
基于TTL的缓存过期：
javascript
// Variable TTL based on data type
const CACHE_TTLS = {
  user_profile: 3600,        // 1 hour
  product_list: 1800,        // 30 minutes
  order_summary: 300,        // 5 minutes (frequently changes)
  category_list: 86400,      // 1 day (rarely changes)
  user_settings: 7200        // 2 hours
};

async function getCachedData(key, type, queryFn) {
  const cached = await client.get(key);
  if (cached) return JSON.parse(cached);

  const result = await queryFn();
  const ttl = CACHE_TTLS[type] || 3600;

  await client.setex(key, ttl, JSON.stringify(result));
  return result;
}

LRU Cache Eviction

LRU缓存淘汰

Redis LRU Policy:
conf
undefined
Redis LRU策略：
conf
undefined

redis.conf

redis.conf

maxmemory 1gb maxmemory-policy allkeys-lru # Evict least recently used key
maxmemory 1gb maxmemory-policy allkeys-lru # Evict least recently used key

Or other policies:

Or other policies:

volatile-lru: evict any key with TTL (LRU)

volatile-lru: evict any key with TTL (LRU)

allkeys-lfu: evict least frequently used key

allkeys-lfu: evict least frequently used key

volatile-ttl: evict key with shortest TTL

volatile-ttl: evict key with shortest TTL

undefined
undefined

Cache Warming

缓存预热

Pre-load Hot Data:
javascript
// Warm cache on application startup
async function warmApplicationCache() {
  // Warm popular users
  const popularUsers = await db.query(
    'SELECT * FROM users ORDER BY last_login DESC LIMIT 50'
  );

  for (const user of popularUsers) {
    await client.setex(
      `user:${user.id}`,
      3600,
      JSON.stringify(user)
    );
  }

  // Warm top products
  const topProducts = await db.query(
    'SELECT * FROM products ORDER BY sales DESC LIMIT 100'
  );

  for (const product of topProducts) {
    await client.setex(
      `product:${product.id}`,
      1800,
      JSON.stringify(product)
    );
  }

  console.log('Cache warming complete');
}

// Run on server startup
app.listen(3000, warmApplicationCache);
预加载热点数据：
javascript
// Warm cache on application startup
async function warmApplicationCache() {
  // Warm popular users
  const popularUsers = await db.query(
    'SELECT * FROM users ORDER BY last_login DESC LIMIT 50'
  );

  for (const user of popularUsers) {
    await client.setex(
      `user:${user.id}`,
      3600,
      JSON.stringify(user)
    );
  }

  // Warm top products
  const topProducts = await db.query(
    'SELECT * FROM products ORDER BY sales DESC LIMIT 100'
  );

  for (const product of topProducts) {
    await client.setex(
      `product:${product.id}`,
      1800,
      JSON.stringify(product)
    );
  }

  console.log('Cache warming complete');
}

// Run on server startup
app.listen(3000, warmApplicationCache);

Distributed Caching

分布式缓存

Redis Cluster Setup:
bash
undefined
Redis集群搭建：
bash
undefined

Multi-node Redis for distributed caching

Multi-node Redis for distributed caching

redis-server --port 6379 --cluster-enabled yes redis-server --port 6380 --cluster-enabled yes redis-server --port 6381 --cluster-enabled yes
redis-server --port 6379 --cluster-enabled yes redis-server --port 6380 --cluster-enabled yes redis-server --port 6381 --cluster-enabled yes

Create cluster

Create cluster

redis-cli --cluster create localhost:6379 localhost:6380 localhost:6381

**Cross-Datacenter Cache:**

```javascript
// Replicate cache across regions
async function setCacheMultiRegion(key, value, ttl) {
  const regions = ['us-east', 'eu-west', 'ap-south'];

  await Promise.all(
    regions.map(region =>
      redisClients[region].setex(key, ttl, JSON.stringify(value))
    )
  );
}

// Read from nearest cache
async function getCacheNearest(key, region) {
  const value = await redisClients[region].get(key);
  if (value) return JSON.parse(value);

  // Fallback to other regions
  for (const fallbackRegion of ['us-east', 'eu-west', 'ap-south']) {
    const fallbackValue = await redisClients[fallbackRegion].get(key);
    if (fallbackValue) return JSON.parse(fallbackValue);
  }

  return null;
}
redis-cli --cluster create localhost:6379 localhost:6380 localhost:6381

**跨数据中心缓存：**

```javascript
// Replicate cache across regions
async function setCacheMultiRegion(key, value, ttl) {
  const regions = ['us-east', 'eu-west', 'ap-south'];

  await Promise.all(
    regions.map(region =>
      redisClients[region].setex(key, ttl, JSON.stringify(value))
    )
  );
}

// Read from nearest cache
async function getCacheNearest(key, region) {
  const value = await redisClients[region].get(key);
  if (value) return JSON.parse(value);

  // Fallback to other regions
  for (const fallbackRegion of ['us-east', 'eu-west', 'ap-south']) {
    const fallbackValue = await redisClients[fallbackRegion].get(key);
    if (fallbackValue) return JSON.parse(fallbackValue);
  }

  return null;
}

Cache Monitoring

缓存监控

Redis Cache Statistics:
javascript
async function getCacheStats() {
  const info = await client.info('stats');
  return {
    hits: info.keyspace_hits,
    misses: info.keyspace_misses,
    hitRate: info.keyspace_hits / (info.keyspace_hits + info.keyspace_misses)
  };
}

// Monitor hit ratio
setInterval(async () => {
  const stats = await getCacheStats();
  console.log(`Cache hit rate: ${(stats.hitRate * 100).toFixed(2)}%`);
}, 60000);
Redis缓存统计：
javascript
async function getCacheStats() {
  const info = await client.info('stats');
  return {
    hits: info.keyspace_hits,
    misses: info.keyspace_misses,
    hitRate: info.keyspace_hits / (info.keyspace_hits + info.keyspace_misses)
  };
}

// Monitor hit ratio
setInterval(async () => {
  const stats = await getCacheStats();
  console.log(`Cache hit rate: ${(stats.hitRate * 100).toFixed(2)}%`);
}, 60000);

Cache Strategy Selection

缓存策略选择

StrategyBest ForDrawbacks
Application-levelFlexible, fine-grained controlMore code, consistency challenges
Database-levelTransparent, automaticLess flexibility
Distributed cacheHigh throughput, scaleExtra complexity, network latency
Materialized viewsComplex queries, aggregationsManual refresh needed
策略最佳适用场景缺点
应用级缓存灵活性高、细粒度控制代码量增加、一致性挑战
数据库级缓存透明化、自动化灵活性较低
分布式缓存高吞吐量、可扩展额外复杂度、网络延迟
物化视图复杂查询、聚合操作需要手动刷新

Best Practices

最佳实践

✅ DO implement cache warming ✅ DO monitor cache hit rates ✅ DO use appropriate TTLs ✅ DO implement cache invalidation ✅ DO plan for cache failures ✅ DO test cache scenarios
❌ DON'T cache sensitive data ❌ DON'T cache without invalidation strategy ❌ DON'T ignore cache inconsistency risks ❌ DON'T use same TTL for all data
✅ 建议实现缓存预热 ✅ 建议监控缓存命中率 ✅ 建议使用合适的TTL ✅ 建议实现缓存失效机制 ✅ 建议规划缓存故障处理方案 ✅ 建议测试缓存场景
❌ 请勿缓存敏感数据 ❌ 请勿在无失效策略的情况下使用缓存 ❌ 请勿忽略缓存不一致风险 ❌ 请勿对所有数据使用相同的TTL

Resources

参考资源