Integrating Solr with MySQL

In modern enterprise applications, data is often scattered across different storage systems. As the dominant relational database, MySQL excels at handling structured transactional data and complex queries; meanwhile, as a high-performance distributed search engine, Solr offers unique advantages in full-text retrieval, fuzzy matching, and metadata analysis. Seamlessly integrating both allows for building hybrid architectures that possess precise business logic processing capabilities alongside powerful search functionality. This article details the core steps and technical considerations for implementing integration between Solr and MySQL.

Data Synchronization Mechanism Selection

Solr itself does not directly connect to databases to execute SQL queries for updating indexes (unless using newer JDBC Connectors); therefore, establishing an integration bridge hinges on data synchronization. Currently, mainstream solutions primarily fall into two categories: script-based ETL tools and direct mapping schemes based on SolrJDBC Connector.

For most production environments, it is recommended to use the SolrJdbcConnector (or similar tools like Apache Nutch/JDBI). This component allows Solr to connect directly to a MySQL database via JDBC URL, automatically executing SELECT, UPDATE, and DELETE operations to maintain consistency between indexes and source data. This approach eliminates the need for complex intermediate synchronization script development while providing real-time incremental update capabilities.

Configuration requires enabling the corresponding plugin in solrconfig.xml:

<plugin name="solr.jdbccolumnindexer" class="org.apache.solr.handler.component.JdbcColumnIndexerHandler"/>

Additionally, field types must be defined in schema.xml to ensure Solr can correctly parse data types returned by MySQL, such as VARCHAR, INT, or DATETIME.

Data Model Mapping Strategy

Another significant challenge for successful integration lies in structural inconsistency. MySQL typically adopts a strict row-based structure, whereas Solr, being based on inverted indexes, is better suited for storing and retrieving columnar or unstructured data. During the integration process, reasonable field mapping rules must be established:

  1. Primary Key Handling: Map MySQL's PRIMARY KEY directly to Solr's unique identifier ID (id) to ensure uniqueness of data entries.
  2. Composite Index Construction: If business requirements necessitate sorting or filtering based on multiple MySQL columns (e.g., "descending by price, ascending by time"), define storedFields and sortField in the Solr schema.xml, even if these fields do not directly participate in inverted indexing during search.
  3. Date and Numeric Optimization: Map DATETIME types from MySQL to Solr's date or parsing_store_date types; integer types correspond to int or long to leverage their efficient range query capabilities.

Incremental Synchronization Implementation Scheme

While full synchronization of data is simple, it is inefficient and unsuitable for high-concurrency scenarios. Timestamp-based incremental synchronization represents the best practice. MySQL tables typically include an updated_at field recording the last update time. SolrJdbcConnector or custom scripts can scan this field upon startup:

  • Initial Load: Read all data where updated_at <= now() and build indexes accordingly.
  • Scheduled Tasks: Utilize cron jobs to run daily at midnight, querying for new or modified records using the condition WHERE updated_at > last_sync_time, updating only the corresponding document IDs in Solr (Update by DocID).

This method not only significantly reduces synchronization load but also ensures the timeliness of search results. At a code level, this logic can be automatically triggered via Java Agent or Spring Boot Starter to achieve "invisible" data state maintenance.

Query Routing and Unified Interface Design

To prevent frontend users from perceiving the complexity of the underlying architecture, it is recommended to adopt a unified REST API layer. This tier is responsible for receiving user search requests; based on business requirements, it decides whether to call Solr Core directly (suitable for full-text retrieval) or connect directly to MySQL (suitable for precise transactional queries), or merge results from both.

For example, when a user inputs "iPhone 15":

  • Solr Side: Execute fuzzy matching *iphone* to retrieve all relevant product descriptions and reviews.
  • MySQL Side: Perform an exact inventory query via primary key ID or SKU code to confirm product existence and real-time pricing.

The backend service can merge the "relevance score" returned by Solr with the "business attributes" (such as stock status, promotional tags) returned by MySQL to generate a final highlighted result list for frontend display. This architecture leverages both the semantic understanding capabilities of search engines and the precise control power of databases.