Table of Contents
1 Mysql checks the data and then writes it to Redis synchronously
2 After Mysql checks the data, it synchronizes Redis in the consumer thread by sending MQ
3 Subscribe to Mysql's Binlog file (can be done with the help of Canal)
4 Delayed double deletion
5 Delayed double writing
Home Database Redis How to synchronize MySQL data to Redis cache

How to synchronize MySQL data to Redis cache

May 27, 2023 am 09:08 AM
mysql redis

1 Mysql checks the data and then writes it to Redis synchronously

Disadvantage 1: It will cause delay to the interface, because synchronous writing to redis itself has delay, and retry is required. If redis If the write fails, you need to try again, which is even more time-consuming.

Disadvantage 2: There is no decoupling. If redis crashes, the thread will be blocked directly.

Disadvantage 3: If someone is the database, it will not be synchronized unless the corresponding database is manually deleted. Redis, but there is a time difference in the process of deleting Redis

2 After Mysql checks the data, it synchronizes Redis in the consumer thread by sending MQ

Disadvantage 1: There are more layers of MQ, that is, it will There is a high probability of causing synchronization delay problems.

Disadvantage 2: Prevent the availability of MQ

Disadvantage 3: If someone is the database, it will not be synchronized

Advantage 1: Can greatly reduce the problem of delayed return of the interface

Advantage 2: MQ itself has a retry mechanism, no need to manually write retry code

Advantage 3: Decoupling, Mysql query and Redis synchronization are completely separated and do not interfere with each other

3 Subscribe to Mysql's Binlog file (can be done with the help of Canal)

CanalServer will pretend to be a MysqlServer slave library to subscribe to the MysqlServer main library Binlog file

When Canal starts, it will configure the corresponding message MQ (RabbitMQ, RocketMQ, Kafka). When it detects changes in the Binlog file, it will convert the changed sql statement into json format and send it as the message content. In the

project in MQ, as long as you monitor the corresponding MQ, you can get the content of Binlog changes. The Json data has a clear operation type (CURD) and the corresponding data. Just synchronize the corresponding data to redis

Disadvantage 1: The entire operation process of canal subscribing to Binlog is single-threaded, so in the face of ultra-high concurrency, the performance may not be excellent. You can deploy multiple Canals and multiple consumers, but you need to pay attention to avoid repeated consumption problems and perform idempotence verification

Advantage 1: Even if the database is modified manually, it will be monitored and synchronized.

Advantage 2: Asynchronous synchronization, no extra delay in interface return

4 Delayed double deletion

Delete the redis data before executing the modified sql

Execute update sql

Delay for a period of time

Delete redis data again

// 延迟双删伪代码
deleteRedisCache(key);   // 删除redis缓存
updateMysqlSql(obj);        // 更新mysql
Thread.sleep(100);           // 延迟一段时间
deleteRedisCache(key);   // 再次删除该key的缓存
Copy after login

Disadvantages: This delay time is difficult to control, how long the delay is, this is very It’s difficult to evaluate

If you don’t use delayed double deletion, you just delete the cache and then modify the MySQL data. What problems will arise if there are only these two steps?

5. Single request, single thread is no problem, but problems will occur under high concurrency and multi-threading

6. If Thread1 thread wants to update data, Thread1 thread will clean up redis at this time

7. At this time, Thread2 thread has come, but Thread1 has not finished updating mysql.

8. Thread2 query redis must be null. At this time, Thread2 will check mysql, and then the found data Write to cache

9. Since Thread1 has not had time to modify the mysql data, the data found by Thread2 at this time is [old data], and Thread2 writes the old data to Redis again

10 . At this time, the Thread3 thread comes, and after querying Redis and finding that there is data, it directly gets the cached data. At this time, [Thread3 finds out the old data] and returns directly with the old data. This is the problem.

11. The second deletion function of delayed double delete is to prevent Thread2 from writing old data again. With delayed double delete, Thread3 will still get null when querying Redis, and will get the latest data from mysql

12. So the normal delay time should be the entire time from Thread2 checking cache to getting mysql data and then saving it to redis, as the delay time of Thread1, but the time of Thread2 process will be affected by many factors. Therefore, it is difficult to determine how long it will take

5 Delayed double writing

// 延迟双写伪代码
updateMysqlSql(obj);        // 更新mysql
addRedis(key);   // 再次删除该key的缓存
Copy after login

The above code defect;

  • Under high concurrency, two threads execute at the same time The above code is modified to mysql, and the modification content is blocked, which may lead to inconsistency between Redis and Mysql data

  • The T1 thread finishes executing updateMysqlSql and releases the row lock. At this time, the T2 thread executes again updateMysqlSql and addRedis, and finally T1 executes addRedis. This situation will cause the database to be changed to the data of the T2 thread, but Redis is the data of the T1 thread

Optimization

// 完美延迟双写伪代码
开启事务
updateMysqlSql(obj);        // 更新mysql
addRedis(key);   // 再次删除该key的缓存
提交事务
Copy after login

Correction of the above code:

Put the two lines of code into a transaction. Only when T1 finishes executing Mysql and Redis, can T2 start executing, thus ensuring data consistency. It is recommended to use distributed lock

Double-write disadvantage: Mysql and Redis are single-threaded. Performance is not good, so it is not recommended to use

The above is the detailed content of How to synchronize MySQL data to Redis cache. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1664
14
PHP Tutorial
1268
29
C# Tutorial
1243
24
MySQL and phpMyAdmin: Core Features and Functions MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

How to use the Redis cache solution to efficiently realize the requirements of product ranking list? How to use the Redis cache solution to efficiently realize the requirements of product ranking list? Apr 19, 2025 pm 11:36 PM

How does the Redis caching solution realize the requirements of product ranking list? During the development process, we often need to deal with the requirements of rankings, such as displaying a...

Redis's Role: Exploring the Data Storage and Management Capabilities Redis's Role: Exploring the Data Storage and Management Capabilities Apr 22, 2025 am 12:10 AM

Redis plays a key role in data storage and management, and has become the core of modern applications through its multiple data structures and persistence mechanisms. 1) Redis supports data structures such as strings, lists, collections, ordered collections and hash tables, and is suitable for cache and complex business logic. 2) Through two persistence methods, RDB and AOF, Redis ensures reliable storage and rapid recovery of data.

Explain the purpose of foreign keys in MySQL. Explain the purpose of foreign keys in MySQL. Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

Compare and contrast MySQL and MariaDB. Compare and contrast MySQL and MariaDB. Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

SQL vs. MySQL: Clarifying the Relationship Between the Two SQL vs. MySQL: Clarifying the Relationship Between the Two Apr 24, 2025 am 12:02 AM

SQL is a standard language for managing relational databases, while MySQL is a database management system that uses SQL. SQL defines ways to interact with a database, including CRUD operations, while MySQL implements the SQL standard and provides additional features such as stored procedures and triggers.

How to safely store JavaScript objects containing functions and regular expressions to a database and restore? How to safely store JavaScript objects containing functions and regular expressions to a database and restore? Apr 19, 2025 pm 11:09 PM

Safely handle functions and regular expressions in JSON In front-end development, JavaScript is often required...

Redis: Understanding Its Architecture and Purpose Redis: Understanding Its Architecture and Purpose Apr 26, 2025 am 12:11 AM

Redis is a memory data structure storage system, mainly used as a database, cache and message broker. Its core features include single-threaded model, I/O multiplexing, persistence mechanism, replication and clustering functions. Redis is commonly used in practical applications for caching, session storage, and message queues. It can significantly improve its performance by selecting the right data structure, using pipelines and transactions, and monitoring and tuning.

See all articles