What are the solutions for MySQL data synchronization with Elasticsearch?

Product retrieval

Everyone should have searched for products on various e-commerce websites. How do you usually search for products? Search engine Elasticsearch.

Then the question comes. When a product is put on the shelves, the data is generally written into the MySQL database. So how is the data used for retrieval synchronized to Elasticsearch?

MySQL synchronization ES

1. Synchronous double writing

This is the most direct way imaginable. When writing to MySQL, Directly and synchronously write a copy of data to ES.

Synchronous dual writing

For this method:

Advantages: Simple implementation

Disadvantages:

• Business coupling, coupling a large amount of data synchronization code in product management

• affects performance, writing to two storages, and the response time becomes longer

• Inconvenient to expand: Search may have some personalized requirements, which requires aggregation of data. This method is inconvenient to implement

2. Asynchronous double writing

It is also easy for us to think of the method of asynchronous double writing. When listing products, we first throw the product data into MQ. In order to understand the coupling, we usually split a search service, and the search service subscribes to the news of product changes. to complete synchronization.

Asynchronous double writing

As mentioned earlier, what should we do if some data needs to be aggregated into a structure similar to a wide table? For example, the product category, spu, and sku tables of the product library are separated, but the query is cross-dimensional. It will be less efficient to aggregate it again in ES. It is best to aggregate the product data and use a similar large-scale method in ES. It is stored in the form of a wide table, so that the query efficiency is higher.

Multi-dimensional multi-condition query

There is actually no good way to do this. Basically, you still have to search the service to check the database directly, or call it remotely, and then query the product database again, which is the so-called back check. .

Looking back to complete aggregation

This way:

Advantages:

• Solution Coupling, product services do not need to pay attention to data synchronization

• The real-time performance is better, using MQ, under normal circumstances, the synchronization is completed in seconds

Disadvantages :

• Introduces new components and services, increasing complexity

3. Scheduled tasks

If we want Quickly check, the amount of data is not that big, what should I do? Scheduled tasks are also available.

Scheduled tasks

The most troublesome thing about scheduled tasks is that the frequency is difficult to choose. If the frequency is high, it will unnaturally form business peaks, resulting in The storage CPU and memory usage increases in peaks. If the frequency is low, the real-time performance is poor, and there are also peaks.

This method:

Advantages: relatively simple implementation

Disadvantages:

• real-time performance is difficult to guarantee

• High pressure on storage

4. Data subscription

There is another way, which is the most popular data subscription.

MySQL achieves master-slave synchronization through binlog subscription. Various data subscription frameworks such as canal use this principle to disguise the client component as a slave library to implement data subscription.

MySQL master-slave synchronization

We take the most widely used canal as an example. canal supports a variety of functions through canal-adapter Adapters, including the ES adapter, can directly synchronize MySQL data to ES after starting through some configurations. This process is zero-code.

canal synchronization data

Although we follow the boss's suggestion and use canal for synchronization work, in fact we still need to write code. why?

Due to the limited support of canal, the data aggregation of multiple tables mentioned above still needs to be implemented through review. At this time, it is not appropriate to use canal-adapter. You need to implement canal-client yourself, monitor and aggregate data, and write to ES:

Data subscription review

This looks similar to asynchronous double writing, but firstly it reduces the coupling of products and services, and secondly the real-time nature of the data is better.

So use data subscription:

Advantages:

• Less business intrusion

• More real-time good

As for the selection of data subscription framework, the mainstream ones are generally these:

##Open Source PartyAlibabaZendeskCommunityDevelopment languageJavaJavaPythonActivityActiveActiveActiveHigh availabilitySupport SupportedNot supportedClientJava/Go/PHP/Python/Rust NonePythonMessage landingKafka/RocketMQ, etc.Kafka/RabbitNQ/Redis, etc. CustomMessage formatCustomJSONCustomDocument detailsDetailsDetailsDetailsBoostrapNot supportedSupportedNot supported

	Cancal	Maxwell	Python-Mysql-Rplication

MySQL synchronizes to other data stores, such as HBase, basically using similar methods.

The above is the detailed content of What are the solutions for MySQL data synchronization with Elasticsearch?. For more information, please follow other related articles on the PHP Chinese website!