产品过期筛选与管理方法的性能、扩展性优劣分析

Hey folks! Let's dive into the three SQL-based product filtering/management methods you mentioned, break down their pros and cons across performance and scalability, and share some real-world lessons I've picked up over time. Plus, we'll factor in that future plan to switch to a 60-day validity window.

1. Filter by Expiry Date Set at Creation #

The Query #

SELECT * FROM products WHERE expiry_at > NOW();

Pros #

• Intuitive & Direct: The logic is straightforward—you're directly filtering based on the explicit expiry timestamp, so anyone reading the query gets it immediately.
• Strong Performance: If you have an index on expiry_at, this query will fly even with large datasets. Databases optimize range queries on indexed date fields really well.
• No External Dependencies: Unlike the scheduler-based method, this doesn't rely on any external systems to keep data accurate.

Cons #

• Rigidity for Future Changes: If you switch from 30-day to 60-day validity, you'll need to run a bulk update to adjust expiry_at for all existing products. For large tables, this can be resource-heavy (lock contention, long-running transactions) and risky if not done carefully.
• Limited Flexibility: If you need to extend a specific product's validity manually, you have to update its expiry_at field individually—no easy way to apply a global rule without touching every row.

2. Disable Products via Scheduler #

The Query #

SELECT * FROM products WHERE active = '1';

Pros #

• Blazing Fast Queries: The active field (usually a boolean or tinyint) is trivial to index, so filtering for active products is almost instantaneous, even with millions of rows.
• Easy Scalability for Validity Changes: When switching to 60-day validity, you just update your scheduler's logic (e.g., change the interval from 30 to 60 days when marking products as inactive) instead of modifying existing data. No bulk updates needed.
• Flexible State Management: You can manually toggle the active flag for individual products (e.g., reactivate an expired product for a special promotion) without messing with timestamps.

Cons #

• Dependency on External Scheduler: If your scheduler fails (e.g., cron job crashes, cloud function doesn't run), expired products will stay active, leading to data inconsistency. You need monitoring and fallback mechanisms to mitigate this.
• Batch Update Overhead: The scheduler's bulk update (e.g., UPDATE products SET active = '0' WHERE created_at < NOW() - INTERVAL 30 DAY) can cause performance spikes on large tables. Running this during off-peak hours or breaking it into smaller batches (e.g., 1k rows at a time) is a must.
• Lack of Expiry Visibility: The active flag only tells you if a product is live—you can't easily query for products that are about to expire unless you also track an expiry_at or created_at field.

3. Filter by 30-Day Creation Window #

The Query #

SELECT * FROM products WHERE created_at > DATE(NOW() - INTERVAL 30 DAY);

Pros #

• Zero Data Maintenance: No need to store an expiry_at field or run batch updates. When switching to 60-day validity, you just change the 30 to 60 in your query—done.
• Efficient Storage: Saves a column in your table, which adds up with large datasets.
• Reliable Performance: created_at is almost always indexed (since it's a common audit field), so the range query here is well-optimized.

Cons #

• No Custom Validity: This method assumes all products have the same validity window starting at creation. If you need to extend a single product's life or set a custom expiry, you're out of luck—you'd need to add an expiry_at field to override the default.
• Inflexible Logic: If your business ever shifts to calculating validity from a different event (e.g., first purchase date instead of creation date), this query becomes useless without major schema changes.

Practical Takeaways from Real-World Use #

• Combine Methods for Flexibility: For most businesses, a hybrid approach works best. Use expiry_at to track explicit expiry times, a scheduler to toggle the active flag when products expire, and query using active = '1' for performance. This gives you the best of both worlds: fast queries, easy validity adjustments, and visibility into upcoming expirations.
• Index Strategically: Always add indexes to expiry_at, created_at, and active—but be mindful of write overhead. For example, a single composite index on (active, expiry_at) can optimize both active product queries and expiry monitoring.
• Batch Bulk Operations: If you have to run bulk updates (like adjusting expiry_at for a validity change), split them into small chunks and run them during low-traffic periods. Use LIMIT and keyset pagination (instead of OFFSET) to avoid locking the entire table.
• Plan for Future Changes: Anticipate validity adjustments by building flexibility into your schema. For example, add a validity_days column instead of hardcoding 30 or 60 in queries—then your filter becomes created_at + INTERVAL validity_days DAY > NOW(). Just note that you may need a functional index to make this query fast.

内容的提问来源于stack exchange，提问作者Thomas Müller