使用Redis缓存SQL结果 [英] Using Redis to cache SQL result

问题描述

我有一个基于SQL的应用程序，我喜欢使用Redis缓存结果.您可以将应用程序视为具有多个SQL表的通讯录.该应用程序执行以下任务:

40％的时间:

• 创建新记录/更新现有记录
• 批量更新多个记录
• 查看现有记录

60％的时间:

• 根据用户条件搜索记录

这是我目前的方法:

• 创建或更新记录时，系统会缓存一条记录.
• 用户执行搜索时，系统将缓存查询结果.

最重要的是，我有一个Redis查找表(Redis Set)，该表存储MySQL记录ID和Redis缓存键.这样，如果MySQL记录已更改(例如，批量更新)，我可以删除Redis缓存.

如果在系统缓存搜索结果后创建新记录怎么办?如果新记录符合搜索条件，则系统将始终返回旧的缓存(不包括新记录)，直到删除缓存(直到更新缓存中的现有记录时才发生). /p>

搜索由用户驱动，并且搜索条件的组合数不胜数.创建新记录时，无法评估应删除哪个缓存.

到目前为止，唯一的解决方案是在创建记录时删除MySQL表的所有缓存.但是，这不是一个好选择，因为每天都会创建很多记录.

在这种情况下，在MySQL之上实现Redis的最佳方法是什么?

解决方案

在涉及PHP和MySQL时，这是一件令人惊讶的事情(我不确定其他语言)-不将内容缓存到memcached中或Redis实际上更快.快多了.基本上，如果您只是构建自己的应用程序并查询了MySQL，那么您将获得更多的好处.

现在是为什么"部分.

InnoDB(默认引擎)是一个出色的引擎.具体来说，它的内存管理(分配和不分配)优于任何内存存储解决方案.这是事实，您可以查找它或相信我-它的性能至少与Redis一样好.

现在，您的应用程序中会发生什么-您查询MySQL并将结果缓存到redis中.但是，MySQL也足够聪明，可以保留缓存的结果.您刚刚要做的就是创建一个额外的文件描述符，该文件描述符是连接到Redis所必需的.您还使用了一些存储(RAM)来缓存MySQL已经缓存的结果.

这是另一个有趣的部分-提供PHP脚本的首选方式是使用php-fpm-它比那里的任何mod_*都快得多.一直到核心，php-fpm是生成子进程的主管进程.在提供脚本后，它们不会关闭，这意味着它们会缓存与MySQL的连接-连接一次，使用多次.基本上，如果您使用php-fpm提供脚本，它们将重用已经建立的与MySQL的连接，这意味着您不会为每个请求打开和关闭连接-这是非常资源友好的，它使您可以闪电般快速地连接到MySQL的. MySQL具有更高的内存效率并具有缓存结果比Redis快得多.

现在，这一切对您意味着什么-正确的设置可以使您拥有简单，容易，不涉及Redis的小代码，并消除了缓存失效可能带来的所有问题，而并非如此，因此您就赢了.不会浪费您的内存来包含两次相同的数据.

您需要此成分才能起作用:

• php-fpm
基于
• MySQL和InnoDB的表，最重要的是-足够的RAM和经过调整的innodb_buffer_pool_size变量.可以控制InnoDB为其目的分配多少RAM-越大越好.

您从游戏中淘汰了Redis，使代码简单易维护，没有重复数据，没有在游戏中引入其他系统，让需要照顾数据的软件完成了它的工作工作.即使您从头开始编译所有软件，也要以极低的代价进行权衡以获得最大的实用性-安装并运行它不会花费一个多小时左右的时间.

或者，您可以忽略我写的内容，并使用Redis寻找解决方案.

I have a SQL-based application and I like to cache the result using Redis. You can think of the application as an address book with multiple SQL tables. The application performs the following tasks:

40% of the time:

• Create a new record / Update an existing record
• Bulk update multiple records
• Review an existing record

60% of the time:

• Search records based on user's criteria

This is my current approach:

• The system cache a record when a record is created or updated.
• When user performs a search, the system will cache the query result.

On top of that, I have a Redis look-up table (Redis Set) which stores the MySQL record ID and the Redis cache key. That way I can delete the Redis caches if the MySQL record has been changed (e.g., bulk update).

What if a new record is created after the system cache the search result? If the new record matches the search criteria, the system will always return the old cache (which does not include the new record), until the cache is deleted (which won't happen until an existing record in the cache is updated).

The search is driven by the users and the combination of the search condition is countless. It is not possible to evaluate which cache should be deleted when a new record is created.

So far, the only solution is to remove all caches of a MySQL table when a record is created. However this is not a good choice because lots of records are created daily.

In this situation, what's the best way to implement Redis on top of MySQL?

解决方案

Here's a surprising thing when it comes to PHP and MySQL (I am not sure about other languages) - not caching stuff into memcached or Redis is actually faster. Much faster. Basically, if you just built your app and queried MySQL - you'd get more out of it.

Now for the "why" part.

InnoDB, the default engine, is a superb engine. Specifically, it's memory management (allocation and what not) is superior to any memory storage solutions. That's a fact, you can look it up or take my word for it - it will, at least, perform as good as Redis.

Now what happens in your app - you query MySQL and cache the result into redis. However, MySQL is also smart enough to keep cached results. What you just did is create an additional file descriptor that's required to connect to Redis. You also used some storage (RAM) to cache the result that MySQL already cached.

Here comes another interesting part - the preferred way of serving PHP scripts is by using php-fpm - it's much quicker than any mod_* crap out there. Down to the core, php-fpm is a supervisor process that spawns child processes. They don't shut down after the script is served, which means they cache connections to MySQL - connect once, use multiple times. Basically, if you serve scripts using php-fpm, they will reuse the already established connection to MySQL, meaning that you won't be opening and closing connections for each request - this is extremely resource friendly and it lets you have lightning fast connection to MySQL. MySQL, being memory efficient and having the cached result is much quicker than Redis.

Now what does all of this mean for you - having a proper setup lets you have small code that's simple, easy, doesn't involve Redis and eliminates all the problems that you might have with cache invalidation and what not and you won't waste your memory to contain the same data twice.

Ingredients you need for this to work:

• php-fpm
• MySQL and InnoDB based tables and most of all - sufficient RAM and tweaked innodb_buffer_pool_size variable. That one controls how much RAM InnoDB is allowed to allocate for its purposes - the larger the better.

You eliminated Redis from the game, you kept your code simple and easy to maintain, you didn't duplicate data, you didn't introduce additional system to the play and you let software that's meant to take care of data do its job. Pretty cheap trade-off for maximum usefulness, even if you compile all the software from scratch - it won't take more than an hour or so to get it up and running.

Or, you can just ignore what I wrote and look for a solution using Redis.

这篇关于使用Redis缓存SQL结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！