千万级短链接服务点击数据存储选型：SQL vs Elasticsearch？

Great question—let’s break this down based on your two core workloads for a short link service: fast redirect lookups and storing/analyzing click event data. Neither SQL nor Elasticsearch is a one-size-fits-all here, so a hybrid approach is usually the optimal solution.

1. Use SQL (e.g., MySQL, PostgreSQL) for Core Short Link Metadata #

Your main short link table (storing short codes, target URLs, creation dates, expiration times, etc.) is structured, transactional, and requires ultra-fast point lookups—all areas where SQL databases excel, even at the tens-of-millions scale.

• Key Advantages:

  • Lightning-fast point queries: A primary key index on your short_code (fixed-length, e.g., 6 characters) will return the target URL in single-digit milliseconds, even with 10M+ rows. InnoDB (MySQL) or PostgreSQL’s B-tree indexes are optimized exactly for this workload.
  • Data consistency & integrity: SQL databases enforce uniqueness constraints (critical to avoid duplicate short codes) and support transactions if you need to tie short link creation to other business logic.
  • Mature tooling & scalability: For 10M+ rows, a properly configured single SQL instance will handle traffic easily. If you grow beyond that, sharding by short_code hash is straightforward and well-documented.

• Table Schema Example:

  CREATE TABLE short_links (
      short_code CHAR(6) PRIMARY KEY,
      target_url TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      expired_at TIMESTAMP NULL,
      creator_id INT NULL
  );
  

2. Use Elasticsearch for Click Event Data #

The click logs (IP addresses, UserAgents, timestamps, referrers, etc.) are high-volume, semi-structured, and likely to need analytical queries—this is where Elasticsearch shines.

• Key Advantages:

  • High write throughput: Elasticsearch is built for ingesting massive amounts of time-series data (like click events) without slowing down your redirect workflow.
  • Built-in analytical capabilities: You can easily run aggregations to answer questions like "How many clicks did this short link get from mobile devices in the US this week?" or parse UserAgents to extract browser/device info with built-in processors.
  • Scalability: As your click logs grow to hundreds of millions or billions of rows, you can simply add more Elasticsearch nodes to scale storage and query performance.
  • Efficient time-based management: You can create daily/monthly indexes for click events, making it easy to archive old data or delete expired logs without impacting active data.

• Index Mapping Example:

  {
    "mappings": {
      "properties": {
        "short_code": {"type": "keyword"},
        "ip_address": {"type": "ip"},
        "user_agent": {"type": "text"},
        "click_timestamp": {"type": "date"},
        "referrer": {"type": "keyword"}
      }
    }
  }
  

Why Not Just One Solution? #

• Only SQL for click logs: Storing tens of millions (or more) of click events in SQL will slow down both writes and analytical queries. Sharding can help, but maintaining sharded log tables and running complex aggregations will be far more work than using Elasticsearch.
• Only Elasticsearch for short links: While you could index short codes in ES, you lose the transactional guarantees and strict data consistency that SQL provides. ES is also not optimized for the low-latency, high-concurrency point lookups needed for redirects—you’ll likely see higher latency and more edge cases with duplicate short codes.

Final Takeaway #

Use SQL for your core short link metadata to ensure fast, consistent redirects. Use Elasticsearch for click event storage to handle high volumes and enable flexible analytics. This hybrid approach balances performance, scalability, and maintainability for a short link service at scale.

内容的提问来源于stack exchange，提问作者joshpj1