How does redis realize real-time page updates and automatic online updates?

Description of requirements

Some pages need to be configured with advertisements or event promotion images. Advertisements or activities must be able to go online and offline at any time, automatically go offline after expiration, and automatically go online when the time comes.

For example: The current time is 2019-2-22 16:16:13. You need to configure the reward collection activity on the payment completion page. The activity must be launched on time at 2019-3-10 00:00:00. In 2019- The event ended at 3-30 23:59:59.

Therefore, the desired effect is that after configuring the activity at any time before the activity goes online, the page will automatically go online at the specified time. There may also be multiple other activities or advertisements. The number of advertisements on each page is variable, and the online and offline times may be different for different pages. Other pages also need to implement such functions, and the activities between pages are not necessarily the same.

Requirements Analysis

The requirements are just a few words, so let’s analyze them in detail.

Extract keywords

• Advertising or event promotion pictures

• Go online and offline at any time, automatically offline after expiration, and automatically after expiration Online

• The number of advertisements on each page is variable

• The online and offline time of different advertisements can be different

• Activities between pages are not necessarily the same

Database analysis

1. [Advertising or event promotion pictures]

To be Different pages have different advertisements, and the advertisements on some pages may be the same, that is, the advertisements will be reused, so there must be an advertisement table.

2. [The number of advertisements on each page is variable] [The online and offline times of different advertisements can be different] [Activities between pages are not necessarily the same]

Multiple pages can be configured Each advertisement must have a page configuration table, as well as a relationship table between advertisements and pages, that is, a page advertisement table.

The page configuration table mainly configures the number of advertisements on the page to realize [the number of advertisements on each page is variable]. The page advertisement table mainly configures the online and offline time of each advertisement on the page to realize [different advertisements online and offline] The time can be different】

Based on simple analysis, I came to the following table structure: advertising table (adv), page configuration table (page_config) and page advertising table (page_adv)

Thinking

The advertisements configured on these pages will not change within a period of time. If the number of page requests is high, the number of advertisement queries will be very frequent, causing unnecessary pressure on the database. . Therefore, caching can be introduced to reduce the number of database requests and relieve database pressure. Redis is used here.

When will it be cached?

You can choose to asynchronously load the advertisements that have been in the online and offline time intervals into the cache when the service starts, or choose to fetch the cache when the request is made. If the cache does not exist, then check the library and put it in the cache. Caching time depends on the situation.

The choice here is to asynchronously store the advertising configuration information of eligible pages into Redis when the project starts. Those that have not yet reached the specified time will not be put into Redis first. When the page is accessed to load the advertisement, Redis will be checked first. If not, check the database according to the condition (>=nowtime), and save it in Redis after checking.

After getting the advertisement configuration information in the interface, determine whether the current time is within the configured time interval. Since multiple advertisements are configured on a page, different advertisement times are also different, so it is necessary to iterate and return the ones that match. Mark any expired ones, and then delete the configuration information of the entire page in Redis. (Or do not choose to load at startup, and add the cache when the user requests it. However, the method in step 1 below will be used when refreshing the load, so it cannot be deleted)

Specific implementation

Step 1. When the project is started, first store the page advertisement configuration information in Redis

a, query all pageId

SELECT pageId FROM page_config page_adv WHERE nowtime<p> and connect the two tables to get List<pageid>, and get all It is the pageId configured with advertisements and the advertisements have not expired. </pageid></p><p>b. Query the advertising images and jump links corresponding to pegeId</p><pre class="brush:php;toolbar:false">SELECT 字段名 FROM page_adv adv WHERE begintime<p>Then put the found configuration information List<adv> (no operation will be performed if it is empty), and use pageId as the KEY. into cache. </adv></p><h4>Step 2. Write the interface query page advertisement for the front end</h4><p>Written according to the standard control layer, business layer, and data access layer. The logic in the first step is completed in the business layer. </p><p>Control layer: </p><p>The control layer takes the parameter pageId and calls the business layer to query the advertising information configured on the corresponding page. If it is empty, it will directly return status code 0, that is, there is no advertisement and the front end will not display it. </p><p>If it is not empty, the data will be processed according to the business logic (such as img URL plus domain name), and then status code 1 will be returned, and the front-end will display the advertisement. The control layer can also add logic here to iterate the ad list, return the current time within the ad starting time, and not return the ones that are not there. And as long as an ad expires, the ad list cache of this page will be cleared. The logic is to clear out the expired ones. </p><p>Business layer: </p><p>Get the cache first, then check the database to determine if it is not empty (this page is configured with advertisements), put it into the cache (pageId is KEY), and then return. </p><p>Data access layer: </p><p>SQL:</p><pre class="brush:php;toolbar:false">SELECT 字段名 FROM page_config adv page_adv WHERE begintime<p>Three table joint query, query the advertising activity information configured on the current page based on pageId (already within the advertising activity time)</p><h4>Step 3, Refresh Loading</h4><p>Why use refresh loading? </p><p>因为有这样的场景：给页面A配置了一个广告（当前时间在广告的起始时间内），那么这个页面的广告已经在缓存里了，假如此时A页面要新加一个广告，在后台配置后如果不做其他操作，这个广告不会显示（假设缓存时间较长，为一天），因为库更新了，缓存没有同步更新。</p><p>解决方案</p><p>使用Redis的发布订阅机制实现缓存的刷新加载，使新配置的广告及时能够显示。刷新加载的回调方法即第1步中的方法。</p><h3>进一步优化</h3><p>想一想，目前的实现存在什么问题？</p><p>存在的问题</p><p>假如有页面需要配置广告，但是还没有配（前端已经开发完上线，每次都会调接口查广告信息），那么数据库肯定查不到，缓存也没有。如果这个页面访问量很大，那么缓存没命中就查库，这样对库的压力就会很大，这就是缓存穿透，请求上来了很容易击垮数据库。那怎么办呢？</p><p>解决方案</p><p>当页面没有配置广告时，在缓存存标志，查询时先看标志，在决定是否往下走。</p><p>具体方案</p><p>这时，上面的第1步就要改了。</p><p>1、首先改第1步的步骤a的SQL，把所有的pageId都查询出来。</p><p>使用左连接</p><pre class="brush:php;toolbar:false">SELECT pageId FROM page_config LEFT JOIN page_adv ON ...  GROUP BY pageId

或者干脆查page_config

SELECT pageId FROM page_config

目的是把已在page_config表中配置，但关系表中page_adv未配置广告的pageId也查出来，这样才能给未配置广告的pageId在缓存里放标志

2、第1步的步骤b的SQL改为

SELECT 字段名 FROM page_adv adv WHERE nowtime<p>然后把查到的配置信息放入缓存之前判断【为空时的不做操作】改为【为空时存入一个标志】假如这个标志KEY为pageId+"_EMPTY_FLAG",value为"DB_IS_NULL"</p><p>为什么只判断小于结束时间</p><p>因为如果该页面配置的广告开始时间大于当前时间，那么这个是查不到的，会被处理为DATABASE_IS_NULL，如果在这个标志还没失效之前就到了配置的开始时间了，那么这个广告不会被展示。所有要让未到开始时间的也放入缓存，然后让控制层去判断在不在时间区间。</p><p>3、所以要在第2步也修改一下</p><p>在业务层里取缓存中的广告列表之前，先从缓存取pageId+"EMPTY_FLAG"的value判断为"DB_IS_NULL"直接返回空，这样就能避免缓存穿透的问题了。</p><p>继续修改第2步的业务层，查库的SQL同样要改：</p><pre class="brush:php;toolbar:false">SELECT 字段名 FROM page_config adv page_adv WHERE nowtime<p>然后判断为空的话，同上面的黄字那样处理。</p><p>4、最后，第3步的刷新加载调的是第1步的方法，不用改。<br><br>当然这个缓存穿透的优化方案只是其中一种。还可以这样：</p><p>1、控制层拦截：根据pageId查询page_adv表，查不到说明没配置，直接返回。</p><p>2、page_config 表增加字段，表示当前页面已经配置的广告个数，默认0，每配置一个该字段加1，把大于0的pageId缓存起来，调接口时前判断在不在缓存里。</p>

The above is the detailed content of How does redis realize real-time page updates and automatic online updates?. For more information, please follow other related articles on the PHP Chinese website!