电商网站将ElasticSearch作为部分数据库主源的技术问询

Hey Hooman! Great question—since you're building an e-commerce site with MySQL handling your core data (like seller product listings, user accounts) and want to use ElasticSearch only for product search, let's break this down clearly, and address that confusion you had about ES's limitations as a primary store.

First, let's lock in the division of labor to avoid any overlap:

• MySQL will handle all critical data operations: seller product creation/edits, inventory updates, user management, order processing—everything that needs strict consistency and transaction support.
• ElasticSearch will act exclusively as a search acceleration layer: it'll hold a copy of your product data optimized for fast full-text searches, filtering, and sorting. You won't use it for user data at all, which is perfect for your needs.

1. Syncing Product Data from MySQL to ElasticSearch #

The key step is keeping your ES product index in sync with MySQL. Here are two practical approaches:

• Real-time sync (for immediate search updates): Use a CDC (Change Data Capture) tool like Debezium. It listens for changes in your MySQL product table (INSERT/UPDATE/DELETE) and automatically pushes those updates to ES. This is ideal if you want sellers' new products to show up in search right away.

  Quick example flow: Debezium captures MySQL product changes → sends events to Kafka → a Kafka consumer transforms the data and writes it to your ES index.

• Scheduled batch sync (simpler setup): Write a small script (Python, Java, even a shell script with mysql and curl) that runs on a schedule (e.g., every hour) to pull updated products from MySQL and bulk-update ES. Use a updated_at timestamp in your MySQL product table to only sync changes since the last run—this avoids reprocessing all data every time.

2. Designing Your ElasticSearch Product Index #

Tailor your index mapping to what users will actually search for. Focus on these key fields:

• Mark product titles, descriptions, and tags as text types with a good analyzer (for Chinese, use the ik analyzer to handle proper word splitting).
• Use keyword, integer, float, or boolean types for fields you'll filter/sort on: price, category ID, inventory status, "on sale" flag.
• Keep a matching id field to link back to MySQL if you need to pull additional details later.

Here's a simplified example mapping:

{
  "mappings": {
    "properties": {
      "id": {"type": "integer"}, // Matches MySQL product ID
      "title": {"type": "text", "analyzer": "ik_max_word"},
      "description": {"type": "text", "analyzer": "ik_smart"},
      "price": {"type": "float"},
      "category_id": {"type": "integer"},
      "is_on_sale": {"type": "boolean"},
      "created_at": {"type": "date"}
    }
  }
}

3. Building the Search Functionality #

When a user types a query in your site's search bar, your backend will send a request to ES, then return the results to the frontend. Common search patterns you'll use:

• Full-text search: Use the match query to target titles and descriptions.
• Filtered searches: Add filter clauses to narrow results by price range, category, or "on sale" status (filters don't affect relevance scoring, so they're fast).
• Sorting: Add a sort parameter to order results by price, newest listings, etc.

Example ES search request for "wireless headphones under $500":

{
  "query": {
    "bool": {
      "must": [{"match": {"title": "无线耳机"}}],
      "filter": [
        {"range": {"price": {"gte": 100, "lte": 500}}},
        {"term": {"is_on_sale": true}}
      ]
    }
  },
  "sort": [{"price": "asc"}]
}

4. Addressing That ElasticSearch 6 Primary Store Concern #

You mentioned reading about ES6's limitations as a primary data store—and that's totally valid! But here's the thing: you don't need to use ES as a primary store. Since MySQL is handling all your core data operations, ES only needs a copy of product data for search. All those downsides you read about (weak transaction support, data consistency risks) don't apply here—you just need to make sure your sync process keeps ES and MySQL in alignment.

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